java 如何使用poi读取excel文件中的空单元格以及如何将此空单元格添加到数组列表中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5699936/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to read empty cell in excel file using poi and how to add this empty cell to array list?
提问by veda
public void readExcel(String fileName)
try
{
FileInputStream myInput = new FileInputStream(fileName);
POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
HSSFSheet mySheet = myWorkBook.getSheetAt(0);
Iterator rowIter = mySheet.rowIterator();
while(rowIter.hasNext())
{
HSSFRow myRow = (HSSFRow) rowIter.next();
Iterator cellIter = myRow.cellIterator();
ArrayList cellStoreVector=new ArrayList();
String header_name = null;
while(cellIter.hasNext())
{
HSSFCell myCell = (HSSFCell) cellIter.next();
// if it is empty cell in my excel file its not added to
// array List
cellStoreVector.add(myCell);
}
cellVectorHolder.add(cellStoreVector);
}
}catch (Exception e)
{
e.printStackTrace();
}
saveToDatabase(cellVectorHolder);
}
public void saveToDatabase(ArrayList array)
{
for (int i=0;i<array.size(); i++)
{
ArrayList cellStoreVector=(ArrayList)array.get(i);
for (int j=0; j < cellStoreVector.size();j++)
{
HSSFCell myCell = (HSSFCell)cellStoreVector.get(j);
String st = myCell.toString();
System.out.println("values "+st);
}
}
}
The above code is my sample code for reading excel file and print values into console. Here I have a problem when reading blank cells and this blank cells are not displayed in the console. So please give me solution for reading blank cell and print this blank in console.
上面的代码是我的示例代码，用于读取 excel 文件并将值打印到控制台。在这里，我在读取空白单元格时遇到问题，并且此空白单元格未显示在控制台中。所以请给我阅读空白单元格的解决方案并在控制台中打印这个空白。
回答by Thomas
From the HSSFRow#cellIteratorJavaDoc:
来自HSSFRow#cellIteratorJavaDoc：
Note that the 4th element might well not be cell 4, as the iterator will not return un-defined (null) cells. Call getCellNum() on the returned cells to know which cell they are.
请注意，第四个元素很可能不是单元格 4，因为迭代器不会返回未定义（空）的单元格。对返回的单元格调用 getCellNum() 以了解它们是哪个单元格。
This means you'd have to store the current and last cell number and if you get a difference greater that 1 you have blank/undefined cells in between, i.e. something like int numBlankCells = (curCellNum - lastCellNum) - 1;
这意味着您必须存储当前和最后一个单元格编号，如果差异大于 1，则中间有空白/未定义的单元格，即类似 int numBlankCells = (curCellNum - lastCellNum) - 1;
Another aproach could be:
另一种方法可能是：
short minColIndex = row.getFirstCellNum();
short maxColIndex = row.getLastCellNum();
for(short colIndex = minColIndex; colIndex < maxColIndex; colIndexx++) {
HSSFCell cell = row.getCell(colIndex);
if(cell == null) {
//blank/undefined cell
}
else {
//defined cell which still could be of type HSSFCell.CELL_TYPE_BLANK or contain an empty string
}
}
回答by Praveen
List cellDataList = new ArrayList();
int lineNumber = 0;
while (rowIterator.hasNext())
{
HSSFRow hssfRow = (HSSFRow) rowIterator.next();
//System.out.println("Befor If");
lineNumber++;
if(lineNumber==1){continue;}
//System.out.println("Out side if ");
Iterator iterator = hssfRow.cellIterator();
List cellTempList = new ArrayList();
int current = 0, next = 1;
while (iterator.hasNext())
{
HSSFCell hssfCell = (HSSFCell) iterator.next();
current = hssfCell.getColumnIndex();
if(current<next)
{
System.out.println("Condition Satisfied");
}
else
{
int loop = current-next;
System.out.println("inside else Loop value : "+(loop));
for(int k=0;k<loop+1;k++)
{
System.out.println("Adding nulls");
cellTempList.add(null);
next = next + 1;
}
}
cellTempList.add(hssfCell);
next = next + 1;
System.out.println("At End next value is : "+next);
}
cellDataList.add(cellTempList);
}
Try this code it will works.
试试这个代码它会起作用。
回答by bastianwegge
if(myCell.cellType == NPOI.SS.UserModel.CellType.BLANK)
Have you modified or overwritten HSSFCell? If not, this should work fine.
你有修改或覆盖HSSFCell吗？如果没有，这应该可以正常工作。
