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/