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 <[email protected]> wrote:
From: Mark Hansen <[email protected]>
Subject: Applying a data format to a style for one cell affects other cells?
To: [email protected]
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
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,
--
View this message in context:
http://www.nabble.com/Applying-a-data-format-to-a-style-for-one-cell-affects-other-cells--tp21362472p21362472.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]
package borkedformula;
import org.apache.poi.hssf.usermodel.*;
import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.text.DateFormat;
import java.text.ParseException;
/**
*/
public class BorkedFormula
{
public static void main(String[] args)
{
File infile = null;
FileOutputStream fos = null;
HSSFWorkbook workbook = null;
HSSFSheet sheet = null;
HSSFRow row = null;
HSSFCell cell = null;
ArrayList<HSSFCellStyle> styles = new ArrayList<HSSFCellStyle>();
ArrayList<Integer> types = new ArrayList<Integer>();
Iterator cellIterator = null;
DateFormat dateFormat = null;
String[] rowData = null;
String[][] data = {{"12/10/2008", "1234", "123.567", "Row 1", "0.67"},
{"04/01/2006", "4567889", "1234567.567", "Row 2", "556123.67"},
{"11/10/1996", "0987", "0.567", "Row 3", "123123.67"},};
try {
dateFormat = DateFormat.getDateInstance(DateFormat.SHORT);
// Get the template
infile = new File("C:\\temp\\Format Template.xlt");
workbook = new HSSFWorkbook(new java.io.FileInputStream(infile));
sheet = workbook.getSheetAt((short)0);
//
// I know that the second row contains empty cells where
// formats and types have been set by the user as they
// built the template. As a result, I can simply iterate
// through those cells and store the associated style and
// type objects into ArrayLists.
//
row = sheet.getRow(1);
cellIterator = row.cellIterator();
while(cellIterator.hasNext()) {
cell = (HSSFCell)cellIterator.next();
styles.add(cell.getCellStyle());
types.add(new Integer(cell.getCellType()));
}
//
// I know the type of each item of data by it's position
// in the array. Therefore, it is a simple matter to
// get each item of data, recover tha style and type information
// from the ArrayLists using the index number of the inner for
// loop. Next, a switch statement is called to ensure that
// the data is converted properly and then written away to the
// cell.
//
for(int i = 0; i < data.length; i++) {
rowData = data[i];
row = sheet.createRow(i + 1);
System.out.println(rowData);
for(int j = 0; j < rowData.length; j++) {
cell = row.createCell((short)j);
cell.setCellType(types.get(j).intValue());
cell.setCellStyle(styles.get(j));
switch(j) {
case 0:
System.out.println("Date");
cell.setCellValue(dateFormat.parse(rowData[j]));
break;
case 1:
System.out.println("Integer");
cell.setCellValue(Integer.parseInt(rowData[j]));
break;
case 2:
System.out.println("Float");
cell.setCellValue(Double.parseDouble(rowData[j]));
break;
case 3:
System.out.println("Text");
cell.setCellValue(new HSSFRichTextString(rowData[j]));
break;
case 4:
System.out.println("Currency");
cell.setCellValue(Double.parseDouble(rowData[j]));
break;
}
}
}
//
// Save file away.
//
fos = new FileOutputStream(new File("C:\\temp\\template merge.xls"));
workbook.write(fos);
}
catch(java.io.IOException ioEx) {
System.out.println("Caught an: " + ioEx.getClass().getName());
System.out.println("Message : " + ioEx.getMessage());
System.out.println("Stacktrace foillows: ");
ioEx.printStackTrace(System.out);
}
catch(ParseException pEx) {
System.out.println("Caught an: " + pEx.getClass().getName());
System.out.println("Message : " + pEx.getMessage());
System.out.println("Stacktrace foillows: ");
pEx.printStackTrace(System.out);
}
finally {
if(fos != null) {
try {
fos.flush();
fos.close();
}
catch(Exception ex) {
// IGNORE //
}
}
}
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
