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

Example 16 with HSSFRow

use of org.apache.poi.hssf.usermodel.HSSFRow 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 17 with HSSFRow

use of org.apache.poi.hssf.usermodel.HSSFRow 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();
    }
}

Example 18 with HSSFRow

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

the class HyperlinkFormula method main.

public static void main(String[] args) throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = row.createCell(0);
    cell.setCellType(CellType.FORMULA);
    cell.setCellFormula("HYPERLINK(\"http://127.0.0.1:8080/toto/truc/index.html?test=aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa\", \"test\")");
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

Example 19 with HSSFRow

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

the class ExcelToHtmlConverter method processSheet.

protected void processSheet(HSSFSheet sheet) {
    processSheetHeader(htmlDocumentFacade.getBody(), sheet);
    final int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
    if (physicalNumberOfRows <= 0)
        return;
    Element table = htmlDocumentFacade.createTable();
    htmlDocumentFacade.addStyleClass(table, cssClassPrefixTable, "border-collapse:collapse;border-spacing:0;");
    Element tableBody = htmlDocumentFacade.createTableBody();
    final CellRangeAddress[][] mergedRanges = ExcelToHtmlUtils.buildMergedRangesMap(sheet);
    final List<Element> emptyRowElements = new ArrayList<Element>(physicalNumberOfRows);
    int maxSheetColumns = 1;
    for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) {
        HSSFRow row = sheet.getRow(r);
        if (row == null)
            continue;
        if (!isOutputHiddenRows() && row.getZeroHeight())
            continue;
        Element tableRowElement = htmlDocumentFacade.createTableRow();
        htmlDocumentFacade.addStyleClass(tableRowElement, cssClassPrefixRow, "height:" + (row.getHeight() / 20f) + "pt;");
        int maxRowColumnNumber = processRow(mergedRanges, row, tableRowElement);
        if (maxRowColumnNumber == 0) {
            emptyRowElements.add(tableRowElement);
        } else {
            if (!emptyRowElements.isEmpty()) {
                for (Element emptyRowElement : emptyRowElements) {
                    tableBody.appendChild(emptyRowElement);
                }
                emptyRowElements.clear();
            }
            tableBody.appendChild(tableRowElement);
        }
        maxSheetColumns = Math.max(maxSheetColumns, maxRowColumnNumber);
    }
    processColumnWidths(sheet, maxSheetColumns, table);
    if (isOutputColumnHeaders()) {
        processColumnHeaders(sheet, maxSheetColumns, table);
    }
    table.appendChild(tableBody);
    htmlDocumentFacade.getBody().appendChild(table);
}

Example 20 with HSSFRow

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

the class ExcelToFoConverter method processSheet.

protected float processSheet(HSSFWorkbook workbook, HSSFSheet sheet, Element flow) {
    final int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
    if (physicalNumberOfRows <= 0)
        return 0;
    processSheetName(sheet, flow);
    Element table = foDocumentFacade.createTable();
    table.setAttribute("table-layout", "fixed");
    Element tableBody = foDocumentFacade.createTableBody();
    final CellRangeAddress[][] mergedRanges = ExcelToHtmlUtils.buildMergedRangesMap(sheet);
    final List<Element> emptyRowElements = new ArrayList<Element>(physicalNumberOfRows);
    int maxSheetColumns = 1;
    for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) {
        HSSFRow row = sheet.getRow(r);
        if (row == null)
            continue;
        if (!isOutputHiddenRows() && row.getZeroHeight())
            continue;
        Element tableRowElement = foDocumentFacade.createTableRow();
        tableRowElement.setAttribute("height", row.getHeight() / 20f + "pt");
        int maxRowColumnNumber = processRow(workbook, mergedRanges, row, tableRowElement);
        if (tableRowElement.getChildNodes().getLength() == 0) {
            Element emptyCellElement = foDocumentFacade.createTableCell();
            emptyCellElement.appendChild(foDocumentFacade.createBlock());
            tableRowElement.appendChild(emptyCellElement);
        }
        if (maxRowColumnNumber == 0) {
            emptyRowElements.add(tableRowElement);
        } else {
            if (!emptyRowElements.isEmpty()) {
                for (Element emptyRowElement : emptyRowElements) {
                    tableBody.appendChild(emptyRowElement);
                }
                emptyRowElements.clear();
            }
            tableBody.appendChild(tableRowElement);
        }
        maxSheetColumns = Math.max(maxSheetColumns, maxRowColumnNumber);
    }
    float tableWidthIn = processColumnWidths(sheet, maxSheetColumns, table);
    if (isOutputColumnHeaders()) {
        processColumnHeaders(sheet, maxSheetColumns, table);
    }
    table.appendChild(tableBody);
    flow.appendChild(table);
    return tableWidthIn;
}