When does apache poi override Excel formatting and when does it not?
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:
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 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
No comments:
Post a Comment