Thank you for the quick response. I can achieve very close to what I want if I add a record listener for blank record types. This is preferable because I need to reproduce the data in a delimited format for insertion into a Topic, so my output now looks like this:
SHID; Carrier; Origin City; Origin County; Origin State; Origin Zip; Destination City; Destination County; Destination State; 2267.0; ADTS; ; ; IL; 600; ; ; MI; 2267.0; ADTS; ; ; IL; 600; ; ; MI; 2267.0; ADTS; ; ; IL; 600; ; ; ; 2267.0; ADTS; ; ; IL; 600; ; ; MI; 2267.0; ADTS; ; ; IL; 600; ; ; MI; 2267.0; ADTS; ; ; IL; 600; ; ; MI; ; ; ; ; ; ; ; This is great!!! It gives me what I need and it is pretty clean. However, I'd like to draw your attention to the last line. In this situation the last line is a group of cells that I applied a format but no data to. I agree with you, the XLS format is <insert another word for horrible>. I do think that pre-processing the excel file as another format is prefered, but it adds an extra step with a lot of overhead. We are trying to implement a solution using poi as part of our asynchronus messaging system, as such if there is any way to limit the amount of overhead or synchronus processing of the file that would be great. If I can just find a way to not to process that last row(s) I'll have a good working solution. -Levi -----Original Message----- From: Andrew C. Oliver [mailto:[EMAIL PROTECTED] Sent: Monday, April 16, 2007 4:20 PM To: POI Users List Subject: Re: problem with HSSF eventusermodel Theoretically col and row records can appear anywhere in that section out of order (but don't when coming from excel). You have to either write them in some kind of better format to disk -- such as a temporary db file -- and then read that (to avoid storing it all in memory) in a guaranteed sort... or keep it in memory in a map/list structure of some sort. Which gets you back into the memory problem. XLS format BLOWS! -Andy Levi Strope wrote: > I'm trying to read in an XLS and output the contents of each row > exactly as they are read in by the file. > > I have to do this with the evenusermodel because of memory > limitations, however it is proving to be very difficult. > > In short, my problem is I need to keep track of how many columns there > are in a row, so that I can start a new line for the next row of data. > All I am doing is outputting this to the console. If I use > getLastCol() it returns the correct number, BUT if the row has 1 less > records there is no way for me to tell it to go to the next row and my > ouput of the next row bleeds into the prior row. > > Here is my output: > > run: > SHID; Carrier; Origin City; Origin County; Origin State; Origin Zip; > Destination City; Destination County; Destination State; 2267.0; ADTS; > IL; 600; MI; 2267.0; ADTS; IL; 600; MI; 2267.0; ADTS; IL; 600; 2267.0; > ADTS; IL; 600; MI; 2267.0; ADTS; IL; 600; MI; 2267.0; ADTS; IL; 600; > MI; > > ********** Finished Processing File *********** > > As you can see, the 3rd row is longer than the rest. The third row > does not contain information in the last column, and so the next row > starts there when it should be on a new line. This would seem simple > to overcome but I'm finding it difficult to do so within the > eventusermodel. > > I am not asking for anyone to give me code. If anyone has ideas on > how I could get around this I would love to hear them. > > Here is my code: > > [BEGIN CODE] > import org.apache.poi.poifs.filesystem.*; > import org.apache.poi.hssf.eventusermodel.*; > import org.apache.poi.hssf.record.*; > import java.io.*; > > /** > * > * @author lstrope > */ > public class PoiXLStest implements HSSFListener { > private SSTRecord sstrec; > int rowRecLen[] = new int[RowRecord.MAX_ROW_NUMBER]; > int rowNum = 0; > > > public PoiXLStest() > { > } > > public PoiXLStest(InputStream in) > { > PoiXLStest noargs = new PoiXLStest(); > HSSFRequest req = new HSSFRequest(); > req.addListener(noargs, SSTRecord.sid); > req.addListener(noargs, LabelSSTRecord.sid); > req.addListener(noargs, RowRecord.sid); > req.addListener(noargs, NumberRecord.sid); > HSSFEventFactory factory = new HSSFEventFactory(); > > try{ > > factory.processEvents(req, in); > > } > catch(IOException E){ > System.out.println("Problem in constructor"); > } > } > > public void processRecord(Record record) > { > short sidVal; > sidVal = record.getSid(); > > if(sidVal == RowRecord.sid){ > RowRecord rowRec = (RowRecord) record; > rowRecLen[rowRec.getRowNumber()] = rowRec.getLastCol(); > // Setting array to hold the row at it's physical position with its > Column length. > } > > if(sidVal == SSTRecord.sid){ > sstrec = (SSTRecord) record; > } > > if(sidVal == LabelSSTRecord.sid){ > LabelSSTRecord lrec = (LabelSSTRecord) record; > if(lrec.getColumn() < (rowRecLen[lrec.getRow()] - 1)){ > //using array to determine the max columns for comparison > System.out.print(sstrec.getString(lrec.getSSTIndex()) > + "; "); > } > else{ > System.out.print(sstrec.getString(lrec.getSSTIndex()) > + ";\n"); //if it is the last column start a new line. > } > > } > > if(sidVal == NumberRecord.sid){ > NumberRecord nrec = (NumberRecord) record; > if(nrec.getColumn() < rowRecLen[nrec.getRow()] -1){ > System.out.print(nrec.getValue() + "; "); > } > else{ > System.out.print(nrec.getValue() + ";\n"); > } > } > } > > public static void main(String[] args) throws IOException, > FileNotFoundException > { > POIFSFileSystem wbook = new POIFSFileSystem(new > FileInputStream("c:\\test.xls")); > InputStream docIn = > wbook.createDocumentInputStream("Workbook"); > //if the file has a 'read only recommendation' this will fail. > PoiXLStest start = new PoiXLStest(docIn); > > docIn.close(); > > System.out.println("\n********** Finished Processing File > ***********"); > > } > } > [END CODE] > > > ---------------------------------------------------------------------- > -- > > --------------------------------------------------------------------- > 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/ -- >From Windows/Exchange to Linux/Meldware Buni Meldware Communication Suite Email, Calendaring, ease of configuration/administration http://buni.org --------------------------------------------------------------------- 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/ --------------------------------------------------------------------- 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/
