poi空单元格格式_apache poi cellIterator跳过空白单元格，但不在第一行

I am creating a java program to read an excel sheet and create a comma separated file. When I run my sample excel file, with blank columns, The first row works perfectly, but the rest of the rows skip the blank cells.

I have read about the code changes required to insert blank cells into the rows, but my question is why does the first row work ????

public ArrayList OpenAndReadExcel(){

FileInputStream file = null;

HSSFWorkbook workBook = null;

ArrayList rows = new ArrayList();

//open the file

try {

file = new FileInputStream(new File("Fruity.xls"));

} catch (FileNotFoundException e) {

// TODO Auto-generated catch block

System.out.println("Could not open Input File");

e.printStackTrace();

}

// open the input stream as a workbook

try {

workBook = new HSSFWorkbook(file);

} catch (IOException e) {

// TODO Auto-generated catch block

System.out.println("Can't Open HSSF workbook");

e.printStackTrace();

}

// get the sheet

HSSFSheet sheet = workBook.getSheetAt(0);

// add an iterator for every row and column

Iterator rowIter = sheet.rowIterator();

while (rowIter.hasNext())

{

String rowHolder = "";

HSSFRow row = (HSSFRow) rowIter.next();

Iterator cellIter = row.cellIterator();

Boolean first =true;

while ( cellIter.hasNext())

{

if (!first)

rowHolder = rowHolder + ",";

HSSFCell cell = (HSSFCell) cellIter.next();

rowHolder = rowHolder + cell.toString() ;

first = false;

}

rows.add(rowHolder);

}

return rows;

}

public void WriteOutput(ArrayList rows) {

// TODO Auto-generated method stub

PrintStream outFile ;

try {

outFile = new PrintStream("fruity.txt");

for(String row : rows)

{

outFile.println(row);

}

outFile.close();

} catch (FileNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

-----

my Input in .xls file (Sorry don't know how to insert an excel table here )

Name >>>>>>>>>> Country of Origin >>>>>>>>> State of origin >>>>>>> Grade>>>>>> No of months

Apple >>>>>>>> USA >>>>>>>>>>>>>>>>>>>>>> Washington >>>>>>>>>>>>>> A >>>>>>>>> 6

orange >>>>>> USA >>>>>>>>>>>>>>>>>>>>>> Florida >>>>>>>>>>>>>>>>> A >>>>>>>>> 9

pineapple>>>>> USA >>>>>>>>>>>>>>>>>>>>>> Hawaii >>>>>>>>>>>>>>>>>> B >>>>>>>>> 10

strawberry>>>> USA >>>>>>>>>>>>>>>>>>>>>> New Jersey>>>>>>>>>>>>>> C >>>>>>>>>> 3

my output text file

Name ,Country of Origin,State of origin,,,Grade,No of months

Apple,USA,Washington,A,6.0

orange,USA,Florida,A,9.0

pineapple,USA,Hawaii,B,10.0

strawberry,USA,New Jersey,C,3.0

Notice the two extra commas before the Grade column... This is because I have two blank columns there.

These extra commas are missing in the rest of the output.

I am using Apache Poi-3.9-20121203.jar

解决方案

You should have a read through the Iterating Over Rows and Cells documentation on the Apache POI website.

The CellIterator will only return cells that have been defined in the file, which largely means ones with either values or formatting. The excel file format is sparse, and doesn't bother storing cells which have neither values nor formatting.

For your case, you must have formatting applied to the first row, which causes them to show up.

You need to read through the documentation and switch to lookups by index. That will also allow you full control over how blank vs never used cells are handled in your code.