Examples with HSSFCell org.apache.poi.hssf.usermodel.HSSFCell used on opensource projects

Example 16 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project poi by apache.

the class TestFormulaParser method testMultiSheetReference.

@Test
public void testMultiSheetReference() throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    wb.createSheet("Cash_Flow");
    wb.createSheet("Test Sheet");
    HSSFSheet sheet = wb.createSheet("Test");
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = row.createCell(0);
    String formula;
    // References to a single cell:
    // One sheet
    cell.setCellFormula("Cash_Flow!A1");
    formula = cell.getCellFormula();
    assertEquals("Cash_Flow!A1", formula);
    // Then the other
    cell.setCellFormula("\'Test Sheet\'!A1");
    formula = cell.getCellFormula();
    assertEquals("\'Test Sheet\'!A1", formula);
    // Now both
    cell.setCellFormula("Cash_Flow:\'Test Sheet\'!A1");
    formula = cell.getCellFormula();
    assertEquals("Cash_Flow:\'Test Sheet\'!A1", formula);
    // References to a range (area) of cells:
    // One sheet
    cell.setCellFormula("Cash_Flow!A1:B2");
    formula = cell.getCellFormula();
    assertEquals("Cash_Flow!A1:B2", formula);
    // Then the other
    cell.setCellFormula("\'Test Sheet\'!A1:B2");
    formula = cell.getCellFormula();
    assertEquals("\'Test Sheet\'!A1:B2", formula);
    // Now both
    cell.setCellFormula("Cash_Flow:\'Test Sheet\'!A1:B2");
    formula = cell.getCellFormula();
    assertEquals("Cash_Flow:\'Test Sheet\'!A1:B2", formula);
    wb.close();
}

Example 17 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project poi by apache.

the class TestRVA method data.

@Parameters(name = "{1}")
public static Collection<Object[]> data() throws Exception {
    poifs = new NPOIFSFileSystem(HSSFTestDataSamples.getSampleFile("testRVA.xls"), true);
    workbook = new HSSFWorkbook(poifs);
    sheet = workbook.getSheetAt(0);
    List<Object[]> data = new ArrayList<Object[]>();
    for (int rowIdx = 0; true; rowIdx++) {
        HSSFRow row = sheet.getRow(rowIdx);
        if (row == null) {
            break;
        }
        HSSFCell cell = row.getCell(0);
        if (cell == null || cell.getCellTypeEnum() == CellType.BLANK) {
            break;
        }
        String formula = cell.getCellFormula();
        data.add(new Object[] { cell, formula });
    }
    return data;
}

Example 18 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project poi by apache.

the class TestSheet method testMisplacedMergedCellsRecords_bug45699.

@Test
public void testMisplacedMergedCellsRecords_bug45699() throws Exception {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("ex45698-22488.xls");
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow row = sheet.getRow(3);
    HSSFCell cell = row.getCell(4);
    if (cell == null) {
        fail("Identified bug 45699");
    }
    assertEquals("Informations", cell.getRichStringCellValue().getString());
    wb.close();
}

Example 19 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project poi by apache.

the class HSSFReadWrite method main.

/**
     * Method main
     *
     * Given 1 argument takes that as the filename, inputs it and dumps the
     * cell values/types out to sys.out.<br/>
     *
     * given 2 arguments where the second argument is the word "write" and the
     * first is the filename - writes out a sample (test) spreadsheet
     * see {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br/>
     *
     * given 2 arguments where the first is an input filename and the second
     * an output filename (not write), attempts to fully read in the
     * spreadsheet and fully write it out.<br/>
     *
     * given 3 arguments where the first is an input filename and the second an
     * output filename (not write) and the third is "modify1", attempts to read in the
     * spreadsheet, deletes rows 0-24, 74-99.  Changes cell at row 39, col 3 to
     * "MODIFIED CELL" then writes it out.  Hence this is "modify test 1".  If you
     * take the output from the write test, you'll have a valid scenario.
     */
public static void main(String[] args) {
    if (args.length < 1) {
        System.err.println("At least one argument expected");
        return;
    }
    String fileName = args[0];
    try {
        if (args.length < 2) {
            HSSFWorkbook wb = HSSFReadWrite.readFile(fileName);
            try {
                System.out.println("Data dump:\n");
                for (int k = 0; k < wb.getNumberOfSheets(); k++) {
                    HSSFSheet sheet = wb.getSheetAt(k);
                    int rows = sheet.getPhysicalNumberOfRows();
                    System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s).");
                    for (int r = 0; r < rows; r++) {
                        HSSFRow row = sheet.getRow(r);
                        if (row == null) {
                            continue;
                        }
                        System.out.println("\nROW " + row.getRowNum() + " has " + row.getPhysicalNumberOfCells() + " cell(s).");
                        for (int c = 0; c < row.getLastCellNum(); c++) {
                            HSSFCell cell = row.getCell(c);
                            String value;
                            if (cell != null) {
                                switch(cell.getCellTypeEnum()) {
                                    case FORMULA:
                                        value = "FORMULA value=" + cell.getCellFormula();
                                        break;
                                    case NUMERIC:
                                        value = "NUMERIC value=" + cell.getNumericCellValue();
                                        break;
                                    case STRING:
                                        value = "STRING value=" + cell.getStringCellValue();
                                        break;
                                    case BLANK:
                                        value = "<BLANK>";
                                        break;
                                    case BOOLEAN:
                                        value = "BOOLEAN value-" + cell.getBooleanCellValue();
                                        break;
                                    case ERROR:
                                        value = "ERROR value=" + cell.getErrorCellValue();
                                        break;
                                    default:
                                        value = "UNKNOWN value of type " + cell.getCellTypeEnum();
                                }
                                System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value);
                            }
                        }
                    }
                }
            } finally {
                wb.close();
            }
        } else if (args.length == 2) {
            if (args[1].toLowerCase(Locale.ROOT).equals("write")) {
                System.out.println("Write mode");
                long time = System.currentTimeMillis();
                HSSFReadWrite.testCreateSampleSheet(fileName);
                System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time");
            } else {
                System.out.println("readwrite test");
                HSSFWorkbook wb = HSSFReadWrite.readFile(fileName);
                try {
                    FileOutputStream stream = new FileOutputStream(args[1]);
                    try {
                        wb.write(stream);
                    } finally {
                        stream.close();
                    }
                } finally {
                    wb.close();
                }
            }
        } else if (args.length == 3 && args[2].equalsIgnoreCase("modify1")) {
            // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!"
            HSSFWorkbook wb = HSSFReadWrite.readFile(fileName);
            try {
                HSSFSheet sheet = wb.getSheetAt(0);
                for (int k = 0; k < 25; k++) {
                    HSSFRow row = sheet.getRow(k);
                    sheet.removeRow(row);
                }
                for (int k = 74; k < 100; k++) {
                    HSSFRow row = sheet.getRow(k);
                    sheet.removeRow(row);
                }
                HSSFRow row = sheet.getRow(39);
                HSSFCell cell = row.getCell(3);
                cell.setCellValue("MODIFIED CELL!!!!!");
                FileOutputStream stream = new FileOutputStream(args[1]);
                try {
                    wb.write(stream);
                } finally {
                    stream.close();
                }
            } finally {
                wb.close();
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

Example 20 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project poi by apache.

the class BigExample method main.

public static void main(String[] args) throws IOException {
    int rownum;
    // create a new workbook
    HSSFWorkbook wb = new HSSFWorkbook();
    try {
        // create a new sheet
        HSSFSheet s = wb.createSheet();
        // declare a row object reference
        HSSFRow r = null;
        // declare a cell object reference
        HSSFCell c = null;
        // create 3 cell styles
        HSSFCellStyle cs = wb.createCellStyle();
        HSSFCellStyle cs2 = wb.createCellStyle();
        HSSFCellStyle cs3 = wb.createCellStyle();
        // create 2 fonts objects
        HSSFFont f = wb.createFont();
        HSSFFont f2 = wb.createFont();
        //set font 1 to 12 point type
        f.setFontHeightInPoints((short) 12);
        //make it red
        f.setColor(HSSFColorPredefined.RED.getIndex());
        // make it bold
        //arial is the default font
        f.setBold(true);
        //set font 2 to 10 point type
        f2.setFontHeightInPoints((short) 10);
        //make it the color at palette index 0xf (white)
        f2.setColor(HSSFColorPredefined.WHITE.getIndex());
        //make it bold
        f2.setBold(true);
        //set cell stlye
        cs.setFont(f);
        //set the cell format see HSSFDataFromat for a full list
        cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
        //set a thin border
        cs2.setBorderBottom(BorderStyle.THIN);
        //fill w fg fill color
        cs2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // set foreground fill to red
        cs2.setFillForegroundColor(HSSFColorPredefined.RED.getIndex());
        // set the font
        cs2.setFont(f2);
        // set the sheet name to HSSF Test
        wb.setSheetName(0, "HSSF Test");
        // create a sheet with 300 rows (0-299)
        for (rownum = 0; rownum < 300; rownum++) {
            // create a row
            r = s.createRow(rownum);
            // on every other row
            if ((rownum % 2) == 0) {
                // make the row height bigger  (in twips - 1/20 of a point)
                r.setHeight((short) 0x249);
            }
            // create 50 cells (0-49) (the += 2 becomes apparent later
            for (int cellnum = 0; cellnum < 50; cellnum += 2) {
                // create a numeric cell
                c = r.createCell(cellnum);
                // do some goofy math to demonstrate decimals
                c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000)));
                // on every other row
                if ((rownum % 2) == 0) {
                    // set this cell to the first cell style we defined
                    c.setCellStyle(cs);
                }
                // create a string cell (see why += 2 in the
                c = r.createCell(cellnum + 1);
                // set the cell's string value to "TEST"
                c.setCellValue("TEST");
                // make this column a bit wider
                s.setColumnWidth(cellnum + 1, (int) ((50 * 8) / ((double) 1 / 20)));
                // on every other row
                if ((rownum % 2) == 0) {
                    // set this to the white on red cell style
                    // we defined above
                    c.setCellStyle(cs2);
                }
            }
        }
        //draw a thick black border on the row at the bottom using BLANKS
        // advance 2 rows
        rownum++;
        rownum++;
        r = s.createRow(rownum);
        // define the third style to be the default
        // except with a thick black border at the bottom
        cs3.setBorderBottom(BorderStyle.THICK);
        //create 50 cells
        for (int cellnum = 0; cellnum < 50; cellnum++) {
            //create a blank type cell (no value)
            c = r.createCell(cellnum);
            // set it to the thick black border style
            c.setCellStyle(cs3);
        }
        //end draw thick black border
        // demonstrate adding/naming and deleting a sheet
        // create a sheet, set its title then delete it
        wb.createSheet();
        wb.setSheetName(1, "DeletedSheet");
        wb.removeSheetAt(1);
        //end deleted sheet
        // create a new file
        FileOutputStream out = new FileOutputStream("workbook.xls");
        // write the workbook to the output stream
        // close our file (don't blow out our file handles
        wb.write(out);
        out.close();
    } finally {
        wb.close();
    }
}