Hi Mark, As far as I understand, style is available in workbook level, and hence when you're doing "HSSFStyle localStyle = cell.getCellStyle()" you're actually getting the style reference from the workbook that is applied to the particular cell. So If you get the cell style and modify it, you're actually modifying the style of all cells in the workbook that is using that style.
Cheers, Winarto -----Original Message----- From: Mark Hansen [mailto:[email protected]] Sent: Friday, 9 January 2009 07:46 To: [email protected] Subject: Re: Applying a data format to a style for one cell affects other cells? Mark Hansen wrote: > > 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 > value 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: > > [QUOTE] > ... > 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() > localStyle.setDataFormat(dateFormat); > cell.setCellStyle(localStyle); > [/QUOTE] > > 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 > appropriately. > > 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? > > Thanks, > > By the way, I did see the following in the HOW TO under the topic of 'Creating Date Cells': // we style the second cell as a date (and time). It is important to // create a new cell style from the workbook otherwise you can end up // modifying the built in style and effecting not only this cell but other cells. So I guess my code is resulting in the style being applied to all cells? I can't create a new style for every cell because: 1. It results in too many styles and I get an error, and 2. I lose the existing formatting made to the cell by the author of the Excel template file Is there a way I can apply a format to an individual cell without the above two problems? Thanks, -- View this message in context: http://www.nabble.com/Applying-a-data-format-to-a-style-for-one-cell-aff ects-other-cells--tp21362472p21363304.html Sent from the POI - User mailing list archive at Nabble.com. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
