Introduction to Apache POI - 2

Introduction to Apache POI - 2

In the previous post we have seen how to create a workbook, now let’s see, how to add text, apply styles, formula, formats…

Creating My first Workbook….

XSSFWorkbook workbook = new XSSFWorkbook (); // Creating a XSSFWorkbook

Sheet sheet = workbook. createSheet("My First Excel"); // You can give a name to sheet

Row row = sheet.createRow(0); //creating a row

                  

Font font = workbook.createFont(); //creating a font for data

font.setBold(true);

font.setFontHeightInPoints((short) 20);

font.setColor(IndexedColors.WHITE.getIndex());

font.setFontName(ApiConstants.CENTURY_FONT);

font.setItalic(false);

CellStyle style = workbook.createCellStyle(); //creating a new style

style.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex());

style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

style.setFont(font);

                  

row.createCell(0).setCellStyle(style); //set created style to the cell

row.getCell(0).setCellValue("Hi!!"); //set value to cell

We can set type of data that is in cell i.e. the value of the cell is of numeric or currency or date type, so on.

Example:

            row.getCell(0).setCellType(CellType.NUMERIC);

we can also set the type of format using the style,

            style.setDataFormat((short) 0x2c); //accounting format

We can almost imitate all the functionalities of an excel using Apache POI

Merged Cell:

Apache POI allows us to merge cells into a single cell. To do so, it provides methods which takes cell indexes as argument and merge the cells into a single large cell.

 Row row1 = sheet.createRow(1); //creating a row

 row1.createCell(0).setCellValue("Merged Cells.............!");

 row1.getCell(0).setCellStyle(style);

 CellRangeAddress mergedCellRange = new CellRangeAddress(1, 1, 0,3); //adding merged range

 sheet.addMergedRegion(mergedCellRange);

Hiding Rows:

It is possible to hide a row using Apache POI, all we need to is set height of the row to zero,

row.setZeroHeight(true);

In the Image, we can use that row 1 is not seen…

Formula:

We can set formulas for a cell using Apache POI,

XSSFWorkbook workbook = new XSSFWorkbook();

Sheet sheet = workbook.createSheet("My First Excel");

Font font = workbook.createFont();font.setBold(true);

font.setFontHeightInPoints((short) 13);

font.setColor(IndexedColors.WHITE.getIndex());

font.setFontName(ApiConstants.CENTURY_FONT);

font.setItalic(false);

CellStyle style = workbook.createCellStyle();

style.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex());

style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

style.setFont(font);

style.setDataFormat((short)0x2c);

                       

Font textFont = workbook.createFont();

textFont.setBold(true);

textFont.setFontHeightInPoints((short) 13);

textFont.setColor(IndexedColors.BLUE.getIndex());

textFont.setFontName(ApiConstants.CENTURY_FONT);

textFont.setItalic(true);

CellStyle textStyle = workbook.createCellStyle(); // creating a new style

textStyle.setFont(textFont);

Row row = sheet.createRow(0);

row.createCell(0).setCellStyle(textStyle);

row.getCell(0).setCellValue("Creating Formula");

                       

Row row1 = sheet.createRow(1);

row1.createCell(0).setCellStyle(style);

row1.getCell(0).setCellValue(1000);

                       

Row row2 = sheet.createRow(2);

row2.createCell(0).setCellStyle(style);

row2.getCell(0).setCellValue(2000);

                       

Row formulaRow = sheet.createRow(3);

formulaRow.createCell(0).setCellStyle(style);

formulaRow.getCell(0).setCellType(CellType.FORMULA); formulaRow.getCell(0).setCellFormula("SUM(A2+A3)");

try (OutputStream fileOut = new FileOutputStream("C:/Export/workbook.xlsx")) {

            workbook.write(fileOut);

}

Fit Sheet to One Page:

These two properties allows us to fit the data of excel sheet into one single page.

sheet.getPrintSetup().setFitWidth((short) 1);

sheet.getPrintSetup().setFitHeight((short)1);

References:

https://poi.apache.org/components/

https://poi.apache.org/apidocs/4.0/