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

Example 31 with HSSFSheet

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

the class TestCFRuleRecord method testBug53691.

@Test
public void testBug53691() throws IOException {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet();
    CFRuleRecord record = CFRuleRecord.create(sheet, ComparisonOperator.BETWEEN, "2", "5");
    CFRuleRecord clone = record.clone();
    byte[] serializedRecord = record.serialize();
    byte[] serializedClone = clone.serialize();
    assertArrayEquals(serializedRecord, serializedClone);
    workbook.close();
}

Example 32 with HSSFSheet

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

the class TestCFRuleRecord method testWrite.

@Test
public void testWrite() throws IOException {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet();
    CFRuleRecord rr = CFRuleRecord.create(sheet, ComparisonOperator.BETWEEN, "5", "10");
    PatternFormatting patternFormatting = new PatternFormatting();
    patternFormatting.setFillPattern(PatternFormatting.BRICKS);
    rr.setPatternFormatting(patternFormatting);
    byte[] data = rr.serialize();
    assertEquals(26, data.length);
    assertEquals(3, LittleEndian.getShort(data, 6));
    assertEquals(3, LittleEndian.getShort(data, 8));
    int flags = LittleEndian.getInt(data, 10);
    assertEquals("unused flags should be 111", 0x00380000, flags & 0x00380000);
    // Otherwise Excel gets unhappy
    assertEquals("undocumented flags should be 0000", 0, flags & 0x03C00000);
    // check all remaining flag bits (some are not well understood yet)
    assertEquals(0x203FFFFF, flags);
    workbook.close();
}

Example 33 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 34 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 35 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
        }
    }
}