Introduction to Apache POI - 1: January 2019

Monday, January 21, 2019

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/

Friday, January 18, 2019

Introduction to Apache POI - 1

Introduction to Apache POI - 1

Many times it is required to generate reports for an application or an application can receive data in excel files. In such situations, it required to either read or write to excel files.

There are many software which help you achieve it such as Apache POI, GrapeCity, Aspose Cells, JExcel, JXL …

Apache POI developed by Apache Software Foundation, is an open sourced library used by many Java developers as it provides easy way to design, modify Office files. It helps you almost imitate your original Office files.

Components of Apache POI:

Commonly used and supported file formats:

HSSF : excel files (.xls)
HSLF : power point (.ppt)
HWPF : word documents (.doc)
XSSF : excel files(.xlsx)

Excel files formats:

There are two major formats Excel 93-2003 workbook- it follows binary file format of .xls extension
Excel 2007+ - it follows XML based file format of .xlsx extension

Apache POI has options to format both file formats. HSSF can be used for excel 93-2003 workbooks. XSSF can be used for excel 2007+ workbooks.

Apache POI provided us with another streaming API SXSSF which helps us work with vert large spreadsheets while using very less memory.

The following table synopsizes the comparative features of POI’s spreadsheet API:

Working with Workbooks:

Maven dependency:

<dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi</artifactId>

<version>4.0.1</version>

</dependency>

<dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

<version>4.0.1</version>

</dependency>

If you are not using maven, then you need to add the following jars:

poi-3.17.jar
poi-ooxml-3.17.jar
poi-ooxml-schemas-3.17.jar
curvesapi-1.04.jar
xmlbeans-2.6.0.jar
commons-codec-1.10.jar
commons-collections4-4.1.jar

When you start using excel, you hear common words like workbook, sheet, row, cell, formula…

The same has been implemented in Apache POI.

Workbook: It is the high level representation of a excel workbook. User create this object to read or write a workbook. HSSFWorkbook and XSSFWorkbook class implement this class to read or write compatible formats.

Creating a Workbook:

Workbook wb = new HSSFWorkbook ();

Workbook wb = new XSSFWorkbook ();

Copying one Workbook to another:

Workbook wb = new HSSFWorkbook ();

…

try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {

wb.write(fileOut);

}

Workbook wb = new XSSFWorkbook ();

try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {

wb.write(fileOut);

}

Sheet: It is the high level representation for worksheet. Sheets are the central structures of a workbook where user does most of his spreadsheet work.

Creating a new Sheet in a workbook:

Workbook wb = new XSSFWorkbook ();

wb.createSheet(String sheetname);

Get existing sheet in a workbook:

Workbook wb = new XSSFWorkbook (file name);

wb.getSheet(string sheetname);

Row: High level representation of a row in spreadsheet.

Creating a new row:

Row row=sheet.createRow(int rownum);

Getting a created row:

Row row=sheet.getRow(int rownum);

Cell: High level representation of a cell in a row of a spreadsheet.

Cells can be numeric, formula-based or string-based (text). The cell type specifies this. String cells cannot contain numbers and numeric cells cannot contain strings (at least according to our model). Client apps should do the conversions themselves. Formula cells have the formula string, as well as the formula result, which can be numeric or string.

Cells should have their number (0 based) before being added to a row.

Creating a cell:

Cell cell=row.createCell(int cellnumber);

cell.setValue(“creating a new cell”);

CellStyle: High level representation for formatting or styling the information for a cell in excel.

References:

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

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