poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Anthony Andrews <pythonadd...@yahoo.com>
Subject Re: Applying a data format to a style for one cell affects other cells?
Date Fri, 09 Jan 2009 13:12:41 GMT
OK Mark, here is one very simple example - well in the attached files there is.

If you look first at the Excel temple file, you will see that it contains five columns each
with a title in the topmost cell. The cell in row two of each column has been formatted -
a date, an integer, a floating point number, a piece of text and a currency value; I have
kept the formats simple but there is nothing preventing you from changing font, colour, etc,
etc. Nothing at all surprising there. I have attached it as it illustrates that part of the
solution lies with the templates. If they are well ordered and predictable then that is more
than half the battle won.

Next have a look at the Java code - sorry about the name of the class by the way; I was making
use of some test code put together for a different purpose.

The first thing to note is that the data is supplied by a two dimensional array of Strings.
So, we know what sort of data will be coming from each element of the array. If this does
not match your case then you will need to look at ways to identify the data's type; Regular
Expressions are probably the best technique IMO.

Overall, the technique is very simple, I open the template file, read the second row as it
contains cells with formatting data. Next, the code iterates through the cells on the second
row and stores references to the associated HSSFCellStyle into one ArrayList and the integral
value that determines the cells type into a second ArrayList.

Nested for loops are used to populate the worksheet. The first simply creates new rows and
gets the data from the array. The inner for loop is where more of the action takes place.
Using the index number of the for loop, it is possible to create a new cell, get the type
of the cell and it's style from the ArrayLists and it's data from the array. Note that the
String can be converted appropriately - in the switch statement.

Hope this helps a little. If you need an example that makes use of Regular Expressions, just
let me know. I was working on some demonstration code that used specifications contained within
an xml file to identify the data type and set the format for the cell. It was intended to
be a part of an application that created workbooks from CSV files but I never submitted it.

--- On Thu, 1/8/09, Mark Hansen <nabble@mehconsulting.com> wrote:
From: Mark Hansen <nabble@mehconsulting.com>
Subject: Applying a data format to a style for one cell affects other cells?
To: user@poi.apache.org
Date: Thursday, January 8, 2009, 3:24 PM

I'm using POI 3.2-FINAL on Windows/XP SP3.
My program is opening an Excel template file (.xlt - 2003 version) and
creating rows/cells to fill the work sheet with data which comes from a
separate system.
The feature allows my customer to export there data to .xls, so the customer
creates the Excel template file, which includes some column headers (in row
1) as well as some background colors/boarders on cells, etc.

What I want to do is go through my customer's data and create a row in the
worksheet for each row of data.

When the data is alphanumeric, I just use "setCellValue()" to set the
and it comes out fine. When the data is a date value, I want to apply a
Date-based data format to the cell. So that I don't lose the background
color (and other formatting) applied to the template by the customer, I get
the cell's style (using getCellStyle()), apply my Date format to it, then
set it back, as follows:

HSSFDataFormat dataFormat = wb.createDataFormat();
short dateFormat = dataFormat.getFormat("m/d/yy");
HSSFCell cell = ... use row.getCell(cell-number) to get the cell
cell.setCellValue(my java.util.Date value);
HSSFStyle localStyle = cell.getCellStyle()

The above code does set the date value into the cell, and sets the format
for the cell to Date
with a format of "m/d/yy" as desired. However, subsequent cells in
the row
which contain
numeric values end up with the same Date formatting.

When I have a numeric value, I just use cell.setCellValue(my double value) -
because it looks to me like setCellValue(double) will set the cell type

Why when I set a style on one cell, it is picked-up by a subsequent column?

I can't just create a generic style for use by all cells, because the
customer will set cell-specific styles (like background color, etc.) on
individual cells, and I don't want to overwrite those.

Can anyone explain what I need to do?


View this message in context:
Sent from the POI - User mailing list archive at Nabble.com.

To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

View raw message