如何使用 POI 读取 Excel 文件的空单元格

java apache-poi

2022-09-04 07:14:51

我尝试使用POI读取Excel文件，然后我想将该数据放入.JTable

这是excel文件，

enter image description here

如您所见，上表中有两个空单元格，一旦我将上述数据读入我得到的结果，JTable

enter image description here

在我的，空单元格已移动到错误的位置，我使用以下代码来获得此结果，请帮助我获得正确的结果，JTable

private XLSContainer xLSContainer;
    Vector cellVectorHolder;
    private int noOfCells=0;

    public XLSContainer readXLS(XLSFile xLSFile) {
        cellVectorHolder = new Vector();

        try {

            FileInputStream inputStream = new FileInputStream(xLSFile.getFileName());

            POIFSFileSystem pOIFSFileSystem = new POIFSFileSystem(inputStream);

            HSSFWorkbook hSSFWorkbook = new HSSFWorkbook(pOIFSFileSystem);

            HSSFSheet hSSFSheet = hSSFWorkbook.getSheetAt(0);

            Iterator rowIter = hSSFSheet.rowIterator();


            while (rowIter.hasNext()) {
                HSSFRow row = (HSSFRow) rowIter.next();
                if(row.getRowNum()==0){
                    noOfCells = row.getLastCellNum();
                }
                Iterator cellIter = row.cellIterator();

                Vector cellStoreVector = new Vector();

                while (cellIter.hasNext()) {
                    HSSFCell hSSFCell = (HSSFCell) cellIter.next();
                    //System.out.println(hSSFCell.getCellNum());
                    cellStoreVector.addElement(hSSFCell);
                }
                cellVectorHolder.addElement(cellStoreVector);

            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        feedXLSContainer();
        return xLSContainer;
    }//readXLS

    private void feedXLSContainer() {

        xLSContainer = new XLSContainer();


        for (int i = 0; i < cellVectorHolder.size(); i++) {
            Vector cellStoreVector = (Vector) cellVectorHolder.elementAt(i);
            Vector item = new Vector();
            for (int j = 0; j < cellStoreVector.size(); j++) {
                HSSFCell cell = (HSSFCell) cellStoreVector.elementAt(j);
                item.add(cell.toString());
            }
            if (i == 0) {
                xLSContainer.addHeader(item);
            } else {
                xLSContainer.addRow(item);
            }

        }

    }

我上面所做的是将标题和数据行放入一个名为的类中的单独向量中，然后将这些向量放入.xLSContainerJTable

这是我在更多的谷歌搜索后如何解决它：-）

private XLSContainer xLSContainer;

    public XLSContainer readXLS(XLSFile xLSFile) {
        try {

            WorkbookSettings ws = new WorkbookSettings();
            ws.setLocale(new Locale("en", "EN"));
            Workbook workbook = Workbook.getWorkbook(new File(xLSFile.getFileName()), ws);
            Sheet s = workbook.getSheet(0);
            System.out.println("Sheet Content::" + s.getName());
            readDataSheet(s);
            workbook.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        return xLSContainer;

    }

    private void readDataSheet(Sheet s) {
        xLSContainer = new XLSContainer();

        int noOfRows = s.getRows();
        int noOfCols = s.getColumns();



        for (int i = 0; i < noOfRows; i++) {
            Vector item = new Vector();
            for (int j = 0; j < noOfCols; j++) {

                if (s.getCell(j, i).getContents() == "") {
                    item.add("");

                } else {
                    item.add(s.getCell(j, i).getContents());

                }
            }

            if (i == 0) {
                xLSContainer.addHeader(item);
            }else{
                xLSContainer.addRow(item);
            }
        }
    }

答案 1

迭代器将返回文件中实际存在的单元格。如果你试图复制它们的位置，那几乎肯定不是你想要的，相反，你会想要依次检查每个单元格。

您可能需要类似这样的代码：

workbook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
DataFormatter fmt = new DataFormatter();

for(int sn=0; sn<workbook.getNumberOfSheets(); sn++) {
   Sheet sheet = workbook.getSheetAt(sn);
   for (int rn=sheet.getFirstRowNum(); rn<=sheet.getLastRowNum(); rn++) {
      Row row = sheet.getRow(rn);
      if (row == null) {
         // There is no data in this row, handle as needed
      } else {
         // Row "rn" has data
         for (int cn=0; cn<row.getLastCellNum(); cn++) {
            Cell cell = row.getCell(cn);
            if (cell == null) {
              // This cell is empty/blank/un-used, handle as needed
            } else {
               String cellStr = fmt.formatCell(cell);
               // Do something with the value
            }
         }
      }
   }
}

此代码将允许您依次获取每个单元格，并且还将正确设置单元格的格式（以便将数字格式化为与Excel中一样）

答案 2

我就是这样做的。希望这有帮助。确保导入必要的内容。在您的项目中。

            FileInputStream excelFile = new FileInputStream(new File(path));
            XSSFWorkbook wb = new XSSFWorkbook(excelFile);
            XSSFSheet sheet = wb.getSheetAt(0);
            XSSFRow row;
            XSSFCell cell;
            Iterator rows = sheet.rowIterator();
            //int count =0;
            DataFormatter df= new DataFormatter();
            while(rows.hasNext()) {
                row = (XSSFRow) rows.next();
                for(int i =1; i< 14; ++i) {
                    cell = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    if(cell==null || cell.getCellTypeEnum()== CellType.BLANK) {
                        //count++;
                        //out.println("okay " + count );
                        if(i==1) {
                        declarationDate.add("");
                        }else if(i==2){
                            poNumber.add("");
                        }
                    }else {
                        String str = df.formatCellValue(cell);
                        if(i==1) {
                        declarationDate.add(str);
                        }else if(i==2) {
                            poNumber.add(str);
                        }
                    }
                }
            }