java 在 Apache POI 中跳过空白 Excel 单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10405557/
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
Skipping Blank Excel Cells in Apache POI
提问by JHizzal
I'm new to the Apache POI, but what I want to do is read though an Excel file (.xls) and put that into a ArrayList to store so that I can later manipulate. I can get the whole sheet, but my problem is just that: I get the whole dang sheet (~54183 rows).
我是 Apache POI 的新手，但我想要做的是通过 Excel 文件 (.xls) 读取并将其放入 ArrayList 进行存储，以便我以后可以进行操作。我可以得到整张表，但我的问题是：我得到了整个 dang 表（~54183 行）。
I want to skip over the cells that are blank, which is of type 3. For some reason, when I system.out.print the ArrayList, it has all the blank cells in there.
我想跳过类型为 3 的空白单元格。出于某种原因，当我 system.out.print ArrayList 时，它包含所有空白单元格。
Is there a way to skip over those and not add them to the ArrayList I'm trying to create?
有没有办法跳过这些而不将它们添加到我正在尝试创建的 ArrayList 中？
I have the following bit of code:
我有以下代码：
public ArrayList readExcelFile(String filePath) throws IOException {
ArrayList cellVectorHolder = new ArrayList();
try {
FileInputStream inputFile = new FileInputStream(filePath);
POIFSFileSystem myFileSystem = new POIFSFileSystem(inputFile);
HSSFWorkbook wkbk = new HSSFWorkbook(myFileSystem);
wb = wkbk;
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
HSSFSheet wkSheet = wkbk.getSheetAt(i);
for (Row row : wkSheet) {
ArrayList cellVector = new ArrayList();
for (Cell cell : row) {
if(cell.getCellType() != 3){
cellVector.add(cell);
}
}
cellVectorHolder.add(cellVector);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return cellVectorHolder;
}
Don't mind the ArrayList names...I was using Vectors until I finally discovered they were depreciated since 1.2 or something like that.
不要介意 ArrayList 名称...我一直在使用 Vectors，直到我最终发现它们从 1.2 或类似的版本开始折旧。
采纳答案by Luciano Fiandesio
Before adding the cell to the List, you can check the cell value. Something like:
在将单元格添加到列表之前，您可以检查单元格值。就像是：
if (cell.getStringCellValue() != null && cell.getStringCellValue().length() != 0) {
cellVector.add(cell);
}
You only add the cell if there is some content.
只有在有内容时才添加单元格。
回答by esej
I think you should iterate over the rows and cells, that way POI won't even give you the empty cells.
我认为您应该遍历行和单元格，这样 POI 甚至不会给您空单元格。
Checkout the HSSFSheet.iterator()which iterates over all the Rows in a Sheet.
And then Row.cellIterator()which iterates over the Cells in that row.
CheckoutHSSFSheet.iterator()迭代工作表中的所有行。然后Row.cellIterator()迭代该行中的单元格。
And you are bascially done.
你已经基本完成了。
If you are intent on keeping your old result variables your two inner loops becomes:
如果您打算保留旧的结果变量，则您的两个内部循环将变为：
for (Iterator<Row> rit = wkSheet.iterator();rit.hasNext();) {
ArrayList cellVector = new ArrayList();
for(Iterator<Cell> cit = rit.next().cellIterator(); cit.hasNext(); ) {
cellVector.add(cit.next());
}
cellVectorHolder.add(cellVector);
}
回答by Praveen
Try this
试试这个
List cellDataList = new ArrayList
HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
HSSFSheet hssfSheet = workBook.getSheetAt(0);
Iterator rowIterator = hssfSheet.rowIterator();
int lineNumber = 0;
while (rowIterator.hasNext())
{
HSSFRow hssfRow = (HSSFRow) rowIterator.next();
lineNumber++;
if(lineNumber==1){continue;}
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){
}
else{
int loop = current-next;
for(int k=0;k<loop+1;k++){
cellTempList.add(null);
next = next + 1;
}
}
cellTempList.add(hssfCell);
next = next + 1;
}
cellDataList.add(cellTempList);
}
回答by user9370262
Try the following code with excel as
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class excelDataReader {
public static void main(String[] args) {
List<Info> infoList = extractInfo();
for (Info info : infoList) {
System.out.println(info);
}
}
public static List<Info> extractInfo() {
List<Info> infoList = new ArrayList<Info>();
Workbook wb = null;
try {
wb = new XSSFWorkbook(new FileInputStream(new
File("C:\Users\rrr\Downloads\contacts.xlsx")));
Sheet sheet = wb.getSheetAt(0);
boolean skipHeader = true;
for (Row row : sheet) {
if (skipHeader) {
skipHeader = false;
continue;
}
List<Cell> cells = new ArrayList<Cell>();
int lastColumn = Math.max(row.getLastCellNum(), 5);// because my
// excel
// sheet has
// max 5
// columns,
// in case
// last
// column is
// empty
// then
// row.getLastCellNum()
// will
// return 4
for (int cn = 0; cn < lastColumn; cn++) {
Cell c = row.getCell(cn, Row.RETURN_BLANK_AS_NULL);
cells.add(c);
}
Info info = extractInfoFromCell(cells);
infoList.add(info);
}
} catch (IOException e) {
e.printStackTrace();
} finally {
if (wb != null) {
try {
wb.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return infoList;
}
private static Info extractInfoFromCell(List<Cell> cells) {
Info info = new Info();
Cell nameCell = cells.get(0);
if (nameCell != null) {
nameCell.setCellType(Cell.CELL_TYPE_STRING);
info.setName(nameCell.getStringCellValue());
}
Cell mobileCell = cells.get(1);
if (mobileCell != null) {
mobileCell.setCellType(Cell.CELL_TYPE_STRING);
info.setMobile(mobileCell.getStringCellValue());
}
Cell phoneCell = cells.get(2);
if (phoneCell != null) {
phoneCell.setCellType(Cell.CELL_TYPE_STRING);
info.setPhone(phoneCell.getStringCellValue());
}
Cell permAddressCell = cells.get(3);
if (permAddressCell != null) {
permAddressCell.setCellType(Cell.CELL_TYPE_STRING);
info.setPermAddress(permAddressCell.getStringCellValue());
}
Cell commAddressCell = cells.get(4);
if (commAddressCell != null) {
commAddressCell.setCellType(Cell.CELL_TYPE_STRING);
info.setCommAddress(commAddressCell.getStringCellValue());
}
return info;
}
} package cmprocesser.cmprocesser;
包 cmprocesser.cmprocesser;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
public class Info {
private String name;
private String mobile;
private String phone;
private String permAddress;
private String commAddress;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getMobile() {
return mobile;
}
public void setMobile(String mobile) {
this.mobile = mobile;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getPermAddress() {
return permAddress;
}
public void setPermAddress(String permAddress) {
this.permAddress = permAddress;
}
public String getCommAddress() {
return commAddress;
}
public void setCommAddress(String commAddress) {
this.commAddress = commAddress;
}
@Override
public String toString() {
return "Info [Name=" + name + ", Mobile=" + mobile + ", Phone=" + phone + ", Permanent Address=" + permAddress
+ ", Communication Address=" + commAddress + "]";
}
}
