Hi,
if dataFormatIndex is <
HSSFDataFormat.getNumberOfBuiltinBuiltinFormats() you can use "new
DecimalFormat(HSSFDataFormat.getBuiltinFormat(dataFormatNr))". If not
you can try to get the format string with
this.workbook.createDataFormat().getFormat(dataFormatNr) and apply it to
a DecimalFormat object.
I have done it like this:
short dataFormatNr = cell.getCellStyle().getDataFormat();
DecimalFormat df = null;
if (dataFormatNr > 0 && dataFormatNr <
HSSFDataFormat.getNumberOfBuiltinBuiltinFormats()) {
df = new DecimalFormat(HSSFDataFormat.getBuiltinFormat(dataFormatNr));
}
else {
df = new DecimalFormat();
String formatStr = workbook.createDataFormat().getFormat(dataFormatNr);
if (!formatStr.toUpperCase().equals("GENERAL")) {
try
{
df.applyPattern(formatStr);
}
catch (IllegalArgumentException e) {
// the formatStr is not a decimal format (or
not supported).
log.debug("Excel format pattern not supported
by java. Now using standard format.");
}
}
}
Larry Reeder wrote:
Hi,
I've got a spreadsheet with values that are displayed for the user like
this:
1 2 foo 3.00
4.00 5 bar 3.5
The formatting for each cell is not static, but is up to the person
generating the spreadsheet, so it could look like this:
1 2.0 foo 3
4.00 5.00 bar 3.5
I'd like to write something that would convert this to a CSV file
(actually I want to convert it to XML, but CSV makes the example easier),
with the same formatting as displayed to the user. I've scanned the POI
javadocs and the mailing list archive without finding a solution. Here's
my example code:
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import java.io.FileInputStream;
import java.io.IOException;
public class ReaderWriter {
public static void main(String[] args) throws IOException {
ReaderWriter readerWriter = new ReaderWriter();
readerWriter.readWrite();
}
public void readWrite() throws IOException {
POIFSFileSystem fs =
new POIFSFileSystem(new FileInputStream("workbook.xls"));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
int numRows = 2;
int numCols = 4;
for(int i=0; i < numRows; i++) {
HSSFRow row = sheet.getRow(i);
for(int j=0; j<numCols; j++) {
HSSFCell cell = row.getCell((short)j);
if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
System.out.print(cell.getStringCellValue());
System.out.print(",");
}
else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
double cellValue = cell.getNumericCellValue();
short dataFormatIndex = cell.getCellStyle().getDataFormat();
//Not sure what to do with this. It's not a valid java
numberformat, and
//customer formats give an ArrayIndexOutOfBoundsException
String builtInDataFormats =
HSSFDataFormat.getBuiltinFormat(dataFormatIndex);
//so fall back on String.valueOf
System.out.print(String.valueOf(cellValue));
System.out.print(",");
}
}
System.out.println();
}
}
}
I don't see a general way to use the HSSF dataformat to generate a Java
number format string, so I'm stuck with String.valueOf, which, in the case
of the top spreadsheet example, gives me this:
1.0,2.0,foo,3.0,
4.0,5.0,bar,3.5
Do I need my own custom dictionary to convert from HSSFDataFormat to a
Java DecimalFormat, or is there some utility I missed in the javadoc?
Also, how do I get customer formats out? The constructor for
HSSFDataFormat takes a Workbook object. How do I get that from an
HSSFWorkbook?
I'm using poi-bin-2.5.1-final-20040804
Thanks............. Larry
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
