How to: Use Java for automated reporting via Excel

Automated reporting is a game-changer for any company that uses large amounts of data.

Can you guess what piece of technology medical research, financial engineering, railways, banks, professional services (and basically any field you can think of) all have in common? If you thought “Microsoft Excel,” then you’re right: the “green X” is the common denominator for all things reporting, regardless of industry.

Yes, we know Excel can be (is?) boring. There’s nothing spectacular in rows and columns of data, cell formulas, line and bar graphs, pie charts, pivots – you name it, it’s all boring. But we strongly believe that there are fewer pieces of software in your toolbox that are as powerful and as easy to use as Excel.

Especially when working with loads of data, Excel makes it pretty easy to order, filter, calculate, visualize and report. Expanding into loads^2 of data and adding automation on top is also relatively simple: just couple Excel with Java to do the hard work of mining databases and delivering clean sheets.

So here we go: if you’re an entrepreneur, C-suit executive, product manager, financial “engineer,” or in any other position having to do with reporting (and/or databases), you might want to talk to your software development team about using the power of Java for automated reporting.

(Also: although we now refer to Excel only, please note that you can automate Word via the same technology, too.)

How does automated reporting work?

The whole pipeline of “Java to Excel” is no more than four steps:

• get your data (/database) in order;
• use Apache POI (“the Java API for Microsoft Documents”) to integrate your data with Java;
• have Java pick, compute and format what you need (see functions and code samples below);
• report to Excel (possibly: “download to Excel” if you’re doing everything in a web browser).

Before we proceed to technical details, a reminder that Apache POI is “a project run by the Apache Software Foundation, and previously a sub-project of the Jakarta Project, [that] provides pure Java libraries for reading and writing files in Microsoft Office formats, such as Word, PowerPoint, and Excel.” (via Wikipedia)

Back to Apache POI: short of doing the laundry, there are a lot of things it can solve for you:

• Fonts down to cell level
• Colors (Background and text) down to cell level
• Shading down to cell level
• Cell patterns down to cell level
• Cell initialization
• Freeze Panes
• Passwords
• Images / Pictures, both static and dynamic
• Headings
• Page breaks
• Sheet breaks
• Text insertion and much more
• Functions/Formula
• Merge cells
• Row Height
• Cell text alignment
• Text Rotation
• E-mail the spreadsheet
• Spreadsheets can be archived so that historical spreadsheets can be retrieved

Specifics

Our experience with the Apache POI – Java – Excel ecosystem is positive, as we create complex Excel files on a regular basis. To show how easy it is, let’s look into some (not all) specific functions:

1. Create an Excel file

Apache POI works with all the Excel elements: the file itself, spreadsheets, rows and columns etc.

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("New Sheet");
final File excelFile = new File("myExcelFile.xls");
FileOutputStream fileOut = new FileOutputStream(excelFile, false);
workbook.write(fileOut);
workbook.close();

2. Resize and populate

It is also possible to resize columns and row sizes according to the data. Creating rows, columns, cells and filling them with data is easy:

HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);
cell.setCellValue("Text value");

3. Style editing

Using Apache POI, you can do cell-level editing:

• set the background color of cells,
• borders type and color,
• font type, dimension or color,
• define styles and use them as needed (e.g. use the same style for more cells without redefining it).

final HSSFFont arialRed8Font = workbook.createFont();
arialRed8Font.setFontHeightInPoints((short) 8);
arialRed8Font.setFontName( "Arial");
arialRed8Font.setColor(IndexedColors.RED.getIndex());
arialRed8Font.setBold(true);
 
final HSSFCellStyle cellWithThinBordersStyle = workbook.createCellStyle();
cellWithThinBordersStyle.setBorderBottom(BorderStyle.MEDIUM);
cellWithThinBordersStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
cellWithThinBordersStyle.setBorderRight(BorderStyle.MEDIUM);
cellWithThinBordersStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
cellWithThinBordersStyle.setBorderLeft(BorderStyle.MEDIUM);
cellWithThinBordersStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
cellWithThinBordersStyle.setBorderTop(BorderStyle.MEDIUM);
cellWithThinBordersStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
 
cellWithThinBordersStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
cellWithThinBordersStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellWithThinBordersStyle.setFont(arialRed8Font);
cellWithStyle.setCellStyle( cellWithThinBordersStyle);
cellWithStyle.setCellValue( "Defined cellstyle");

Of course, you can have multiple fonts in a single cell, as in our example:

• the first part is [color: red], [font name: Arial Black], [height: 10 points];
• the rest of the text is [color: black], [font name: Arial], [height: 8 points], [bold].

HSSFFont firstFont = workbook.createFont();
firstFont.setFontHeightInPoints((short) 10);
firstFont.setFontName("Arial Black");
firstFont.setColor(IndexedColors.RED.getIndex());
firstFont.setBold(false);
 
HSSFFont secondFont = workbook.createFont();
secondFont.setFontHeightInPoints((short) 8);
secondFont.setFontName("Arial");
secondFont.setColor(IndexedColors.BLACK.getIndex());
secondFont.setBold(true);
 
final HSSFCell cellWith2fonts = firstRow.createCell(0);
final String font1Text = "Validation failed! ";
final String font2Text = " Please fill all required data!";
final String allText = font1Text + font2Text;
 
final HSSFRichTextString cellValue = new HSSFRichTextString(allText);
cellValue.applyFont(0, font1Text.length(), firstFont);
cellValue.applyFont(font1Text.length(), (allText).length(), secondFont);
 
cellWith2fonts.setCellValue(cellValue);

4. Include comments

HSSFPatriarch hpt = sheet.createDrawingPatriarch();
HSSFComment comment = hpt.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 12, 7));
comment.setString(new HSSFRichTextString("The comment added to the cell." + System.getProperty("line.separator") + " New information..."));
newCell.setCellComment(comment);
newCell.setCellValue("This cell has a note!");

5. Include formulas

You can create programmatically complex formula-cells, using values from other cells. For example:

sumCell.setCellFormula("SUM(" + columnLetter + firstRow + ":" + columnLetter + lastRow + ")");

String formula = "IF("+ columnLetter + estimationRow + "=0,0,("
        + columnLetter + cumulatedRow + "/" + columnLetter + estimationRow
        + ")*100)";
percentCell.setCellFormula(formula);

6. Add hyperlinks

These can point to a web URL, a file, an e-mail application, or to any cell within the file (on any spreadsheet of the same Excel file)

HSSFCell cell = row4.createCell((short) 0);
CreationHelper createHelper = workbook.getCreationHelper();
Hyperlink link = createHelper.createHyperlink(HyperlinkType.URL);
link.setAddress("https://berg-software.com/");
cell.setHyperlink(link);
cell.setCellValue("URL Link - to our company site");
 
cell = sheet.createRow(5).createCell((short) 0);
cell.setCellValue("File Link");
link = createHelper.createHyperlink(HyperlinkType.FILE);
link.setAddress("myFile.doc");
cell.setHyperlink(link);
 
cell = sheet.createRow(6).createCell((short) 0);
cell.setCellValue("Email Link");
link = createHelper.createHyperlink(HyperlinkType.EMAIL);
link.setAddress("mailto:contact@bergsoftprod.wpengine.com?" + "subject = Hyperlink");
cell.setHyperlink(link);
cell.setCellStyle(hlinkstyle);

7. Security

You can set a password to protect an entire sheet from changes. This way, you can make sure that operations like deleting, hiding, and editing cell content can only be done by authorized personnel. However, if needed, some cells can be unlocked altogether.

protected_sheet.protectSheet( "mypassword");
 
 
CellStyle unlockedCellStyle = workbook.createCellStyle();
unlockedCellStyle.setLocked(false);
 
row.createCell( 2).setCellValue("Cell value protected !");
 
final HSSFCell oneCell = row.createCell(4);
oneCell.setCellValue("This cell value can be changed !");
oneCell.setCellStyle(unlockedCellStyle);

8. Freezing panes

You can freeze the first rows or first columns (i.e. both vertical and horizontal freezing are available). For example:

sheet3.createFreezePane( 2, 2 );

9. Collapsing group for columns or rows

It is also very simple to do:

sheet.groupColumn(2, 5);
sheet.groupRow(7, 9);

10. Cell validation

It is possible to set data validations cells. In the example below, we use one writable cell to check if the user has set one of the values 10, 20 or 30, and we do not allow another value to be set. The user will be informed via a message prompt (that can also be either defined or default).

sheet.createRow(25).createCell((short) 3).setCellValue("10");
CellRangeAddressList addressList = new CellRangeAddressList(
        25, 25, 3, 3);
DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(
        new String[]{"10", "20", "30"});
 
DataValidation dataValidation = new HSSFDataValidation
        (addressList, dvConstraint);
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation.createErrorBox("Validation Error", "The accepted values are: 10, 20 and 30!");
 
sheet.addValidationData(dataValidation);

11. Header and footer are not difficult

You can set left, center or right headers (or all of them) with custom fonts:

Header header = sheet.getHeader();
header.setCenter("Center Header");
header.setLeft("Left Header");
header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") +
        HSSFHeader.fontSize((short) 16) + "Right Header - special font");
 
sheet.getFooter().setCenter( "Center Footer");
sheet.getFooter().setLeft( "Left Footer");
sheet.getFooter().setRight("Right Footer");

12. Merge cells

You only need one command to specify the range of cells to be merged:

sheet.addMergedRegion(new CellRangeAddress(firstRowNumber, lastRowNumber, mergedCellFirstCol, mergedCellLastCol));

13. Graphs

Apache POI can help creating different charts (e.g. line, bar, pie charts etc.), including their 3D variants:

Limitations

You have probably figured it out by now that you can perform any Excel functionalities via Java / Apache POI. However, when two special settings apply to the same set of cells, there might be limitations.

For example:

• You can add a picture or hide/collapse cells. But when doing both at the same time (insert a picture on a row that we later collapsed), the image was not shown completely. As a workaround, you can set the image anchor with the count of visible rows/columns – it will keep the full image visible, regardless of expanding/collapsing cells.
• When using collapsible columns, it can be difficult to adjust the comments anchor.
• There is a limitation to 256 columns per sheet when using HSSF.

—

When working with large sets of data that need any level of automation, we strongly recommend using Java to create, edit, compute and download associated Excel files. For most of the functions, you only need a few lines of code. But then, having an Excel file that you can further tinker with is priceless. The benefits of automated reporting are endless.

—

Do you need further ideas or guidance on “Java to Excel”? Let us know!