When does apache poi override Excel formatting and when does it not?

0
votes

I would like to generate an Excel using Apache POI in which I can display about 2000 records, where each record comprises a date and a value.

I would like this Excel to be formatted properly, colouring the cell backgrounds, and applying appropriate number formats.

I can do both of these tasks, but I can not do the formatting as efficiently as I want.

The 3 methods I have tried for applying the formatting are as follows: All three methods involve using a pre-formatted Excel template. The question is, however, how much formatting to do in Excel (and how to apply the formatting) and how much to do in Java.

Method 1: Formatting one row in Excel itself, and copying the formatting using Java code. For instance:

Row existingRow = mySheet.getRow(4);
Cell existingCell = existingRow.getCell(0);
CellStyle currentStyle = existingCell.getCellStyle();

for (int w = 0; w < refData.size(); w++) {
    MyValues aa = refData.get(w);
    Row r = CellUtil.getRow(w + 4, mySheet);
    CellUtil.getCell(r, 0).setCellValue(aa.getMarketDate());
    if (w>0) {
         CellUtil.getCell(r, 0).setCellStyle(currentStyle);
    }

Method 2: Select the cells containing the required format in Excel and paste over the region I require (2000 rows) and then just fill in the data using Apache POI

Method 3: Apply the formatting to the columns using Excel, and then just fill in the data using Apache POI.

The third method is by far preferable for me, because (a) I do not need to start programming Java code when I can just pre-format in Excel [note that my real-life problem includes tens of columns and not just one column] (b) Applying a format to a column is highly advantageous in terms of memory used by the workbook.

The only problem is that when Apache POI writes to cells where the format is copied and pasted, then they are displayed fine. When it writes to cells where the format has been applied to the column, then it removes the formatting before pasting.

Is there any way of getting around this? I assume that there isn't because Apache POI works by considering each row individually. For instance, to apply a format to a column, one needs to apply the format to each cell individually in the column

2
javaexcelapache-poi
The other way around is to load from a pre-formatted template excel, fill and save it to a desired location. – Ravinder Reddy
Hi @Ravinder. All of my 3 suggested solutions involved using a pre-formatted template. I have edited my question to make that clearer. Thank you – gordon613

2 Answers

0
votes

One way would be to use a VBA macro to apply the formatting to the column upon opening the workbook. 

Private Sub Workbook_Open()
   'column formatting
End Sub

However obviously this has the disadvantage that the user would need to enable macros

0
votes

You need to read the style from the "guide" cell and apply it to the column. This way new cells will get it as their style.

eg. (Once before you start writing values)

Row guideRow = mySheet.getRow(0);
for (int ii = 0 ; ii < row.getNumColumns; ++ii) {
  CellStyle currentStyle = row.getCell(ii).getCellStyle();
  mySheet.setDefaultColumnStyle(ii, currentStyle);
}