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

Example 11 with HSSFSheet

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

the class CellTypes method main.

public static void main(String[] args) throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    try {
        HSSFSheet sheet = wb.createSheet("new sheet");
        HSSFRow row = sheet.createRow(2);
        row.createCell(0).setCellValue(1.1);
        row.createCell(1).setCellValue(new Date());
        row.createCell(2).setCellValue("a string");
        row.createCell(3).setCellValue(true);
        row.createCell(4).setCellType(CellType.ERROR);
        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("workbook.xls");
        wb.write(fileOut);
        fileOut.close();
    } finally {
        wb.close();
    }
}

Example 12 with HSSFSheet

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

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

the class AddDimensionedImage method main.

/**
     * The main entry point to the program. It contains code that demonstrates
     * one way to use the program.
     *
     * Note, the code is not restricted to use on new workbooks only. If an
     * image is to be inserted into an existing workbook. just open that
     * workbook, gat a reference to a sheet and pass that;
     *
     *      AddDimensionedImage addImage = new AddDimensionedImage();
     *
     *      File file = new File("....... Existing Workbook .......");
     *      FileInputStream fis = new FileInputStream(file);
     *      HSSFWorkbook workbook = new HSSFWorkbook(fis);
     *      HSSFSheet sheet = workbook.getSheetAt(0);
     *      addImage.addImageToSheet("C3", sheet, "image.jpg", 30, 20,
     *          AddDimensionedImage.EXPAND.ROW);
     *
     * @param args the command line arguments
     */
public static void main(String[] args) {
    String imageFile = null;
    String outputFile = null;
    FileOutputStream fos = null;
    HSSFSheet sheet = null;
    try {
        if (args.length < 2) {
            System.err.println("Usage: AddDimensionedImage imageFile outputFile");
            return;
        }
        imageFile = args[0];
        outputFile = args[1];
        HSSFWorkbook workbook = new HSSFWorkbook();
        try {
            sheet = workbook.createSheet("Picture Test");
            new AddDimensionedImage().addImageToSheet("A1", sheet, imageFile, 125, 125, AddDimensionedImage.EXPAND_ROW_AND_COLUMN);
            fos = new FileOutputStream(outputFile);
            workbook.write(fos);
        } finally {
            workbook.close();
        }
    } catch (FileNotFoundException fnfEx) {
        System.out.println("Caught an: " + fnfEx.getClass().getName());
        System.out.println("Message: " + fnfEx.getMessage());
        System.out.println("Stacktrace follows...........");
        fnfEx.printStackTrace(System.out);
    } catch (IOException ioEx) {
        System.out.println("Caught an: " + ioEx.getClass().getName());
        System.out.println("Message: " + ioEx.getMessage());
        System.out.println("Stacktrace follows...........");
        ioEx.printStackTrace(System.out);
    } finally {
        try {
            if (fos != null) {
                fos.close();
                fos = null;
            }
        } catch (IOException ioEx) {
        // I G N O R E
        }
    }
}

Example 14 with HSSFSheet

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

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

the class Hyperlinks method main.

public static void main(String[] args) throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFCreationHelper helper = wb.getCreationHelper();
    //cell style for hyperlinks
    //by default hyperlinks are blue and underlined
    HSSFCellStyle hlink_style = wb.createCellStyle();
    HSSFFont hlink_font = wb.createFont();
    hlink_font.setUnderline(Font.U_SINGLE);
    hlink_font.setColor(HSSFColorPredefined.BLUE.getIndex());
    hlink_style.setFont(hlink_font);
    HSSFCell cell;
    HSSFSheet sheet = wb.createSheet("Hyperlinks");
    //URL
    cell = sheet.createRow(0).createCell(0);
    cell.setCellValue("URL Link");
    HSSFHyperlink link = helper.createHyperlink(HyperlinkType.URL);
    link.setAddress("http://poi.apache.org/");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);
    //link to a file in the current directory
    cell = sheet.createRow(1).createCell(0);
    cell.setCellValue("File Link");
    link = helper.createHyperlink(HyperlinkType.FILE);
    link.setAddress("link1.xls");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);
    //e-mail link
    cell = sheet.createRow(2).createCell(0);
    cell.setCellValue("Email Link");
    link = helper.createHyperlink(HyperlinkType.EMAIL);
    //note, if subject contains white spaces, make sure they are url-encoded
    link.setAddress("mailto:poi@apache.org?subject=Hyperlinks");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);
    //link to a place in this workbook
    //create a target sheet and cell
    HSSFSheet sheet2 = wb.createSheet("Target Sheet");
    sheet2.createRow(0).createCell(0).setCellValue("Target Cell");
    cell = sheet.createRow(3).createCell(0);
    cell.setCellValue("Worksheet Link");
    link = helper.createHyperlink(HyperlinkType.DOCUMENT);
    link.setAddress("'Target Sheet'!A1");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);
    FileOutputStream out = new FileOutputStream("hssf-links.xls");
    wb.write(out);
    out.close();
    wb.close();
}