You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by lr...@freevoice.org on 2005/08/01 18:05:44 UTC
Re: Create text output with same format that appears in spreadsheet
Jochen,
This appears to work quite well. I guess I'll have to write my own
formatting code for GENERAL formats, but this will keep me going for a
while. Thanks!
-Larry
On Thu, 28 Jul 2005, Jochen Welle wrote:
> 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: poi-user-unsubscribe@jakarta.apache.org
> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/