Levi, I haven't dug through all your code and spreadsheet, but have you considered what the definition of "blank" is? In many similar circumstances I have found that relying upon isBlank, for example, may not be sufficient if you have a mix of true blank (i.e. <null> value) cells and cells with zero length data (i.e. looks "blank" but, by Excel's reckoning contains a value). Sometimes something as simple as tabbing to the cell may be sufficient to change a "blank" to a zero-length value. You may have already taken this into consideration, but thought I'd mention it as a first check. - David
________________________________ From: Levi Strope [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 01, 2007 10:11 PM To: POI Users List Subject: There has got to be a way Please look at the attached excel spreadsheet. I would really appreciate some input. All I need to do is read in a row of data, just like the spreadsheet that is attached, and parse the information reliably. There are many blank cells here, but I need to account for them. My business problem is this: We are accepting messages where columns of information are not required, but the column MUST be represented in the file as we process it. At minimum I'd like to be able to read in these excel files and output them in a CSV format. Essentially as I am streaming the excel file out, I am appending comma's between the values because our messaging system expects the values to be comma delimited. Unfortunately we accpet XLS files so we cannot just ask them to save it as a CSV. This can happen in 1 of 2 ways, I can stream the file and append the commas between the cells as I am sending it to our messaging system, or I can stream it out to a file and build an actual CSV, and then tell the messaging system to pick it up. When I read in an excel file I am finding it very difficult to determine when cells are blank, and account for them. For some reason the BlankRecordListener doesn't pickup all blank records. I've even tried applying formatting to all cells and running my code, it still does not work. I put code in the blank record listener code to tell me the column. Here is what I get: Blank record encounterred. Column= 3 Blank record encounterred. Column= 4 Blank record encounterred. Column= 7 Blank record encounterred. Column= 8 Blank record encounterred. Column= 10 Blank record encounterred. Column= 18 Blank record encounterred. Column= 20 Blank record encounterred. Column= 30 Blank record encounterred. Column= 34 There is no conditional formatting in this code, all I'm doing is listening for a blank record and doing a System.out. If you look at the attached spreadsheet you can see that columns 21, 22, 24, 32, and 33 are blank as well. There are 14 blank records in this spreadsheet but the blankrecord listener only reports 9. I need help. I need someone to tell me what kind of record listener I need to use. How can I account for these cells? Here is my code. At present it skips the blankcells when writing it out to a CSV. /* * * Created on April 5, 2007, 11:49 AM * * * * @author lstrope */ package poitest; import java.io.*; import org.apache.poi.poifs.filesystem.*; import org.apache.poi.hssf.eventusermodel.*; import org.apache.poi.hssf.record.*; public class PoiXLStest implements HSSFListener { private SSTRecord sstrec; int rowRecLen[] = new int[RowRecord.MAX_ROW_NUMBER]; PrintWriter CSV = null; // int rowNum = 0; int colNum = 0; public PoiXLStest() { this("c:\\ExceltoCSV.csv"); } public PoiXLStest(String F) { try{ CSV = new PrintWriter(new BufferedWriter((new FileWriter(F)))); } catch(FileNotFoundException E){} catch(IOException E){} } 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); // req.addListener(noargs, BlankRecord.sid); req.addListenerForAllRecords(noargs); 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; if(rowRec.getRecordSize() > 0){ rowRecLen[rowRec.getRowNumber()] = rowRec.getLastCol(); // Setting array to hold the row at it's physical position with its Column length. //System.out.println(rowRec.getLastCol()); } } if(sidVal == SSTRecord.sid){ sstrec = (SSTRecord) record; } if(sidVal == LabelSSTRecord.sid){ LabelSSTRecord lrec = (LabelSSTRecord) record; if(lrec.getColumn() < (rowRecLen[lrec.getRow()] - 1) && !(lrec.getColumn() < colNum)){ //System.out.print(lrec.getColumn()); CSV.print(sstrec.getString(lrec.getSSTIndex()) + ","); colNum++; } else if(lrec.getColumn() < colNum){ //System.out.print(lrec.getColumn()); CSV.print("\n" + sstrec.getString(lrec.getSSTIndex()) + ","); colNum = 1; } else{ //System.out.print(lrec.getColumn()); CSV.print(sstrec.getString(lrec.getSSTIndex()) + ",\n"); colNum = 0; } } if(sidVal == NumberRecord.sid){ NumberRecord nrec = (NumberRecord) record; if(nrec.getColumn() < (rowRecLen[nrec.getRow()] - 1) && !(nrec.getColumn() < colNum)){ //System.out.print(nrec.getColumn()); CSV.print(nrec.getValue() + ","); colNum++; } else if(nrec.getColumn() < colNum){ //System.out.print(nrec.getColumn()); CSV.print("\n" + nrec.getValue() + ","); colNum = 1; } else{ //System.out.print(nrec.getColumn()); CSV.print(nrec.getValue() + ",\n"); colNum = 0; } } if(sidVal == BlankRecord.sid){ BlankRecord brec = (BlankRecord) record; System.out.println("Blank record encounterred. Column= " + (brec.getColumn()+ 1)); } CSV.flush(); } public static void main(String[] args) throws IOException, FileNotFoundException { POIFSFileSystem wbook = new POIFSFileSystem(new FileInputStream("c:\\evensmallertest.xls")); InputStream docIn = new BufferedInputStream(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 ***********"); } }
