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.
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: