 | | | Subject: RE: There has got to be a way | Subject: RE: There has got to be a way 2007-05-02 - By David Henry
Back
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:lstrope@(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
*
*
*
* @(protected) 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
***********");
}
}
|
|
|