ExcelReader workbook.setMissingCellPolicy（Row.CREA

我试图使用Apache POI读取Excel文件（XLS）文件。 在于，在读行如果细胞缺失（cellIterator）被跳过该小区，并把下一个值不同的报头。

ABC

1 2 3

4坯料6

另外，在上述情况下，它是把6“B”列在空白单元格，我需要乙作为空白字符串。

`package com.howtodoinjava.demo.poi;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class ReadExcelDemo {

    Integer rowNum;
    Iterator<Row> rowIterator;
    HSSFWorkbook workbook;
    HSSFSheet sheet;
    FileInputStream file;

    public ReadExcelDemo(File file1) throws IOException{
         this.file = new FileInputStream(file1);

        // Create Workbook instance holding reference to .xlsx file
        this.workbook = new HSSFWorkbook(file);
        workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK);

        // Get first/desired sheet from the workbook
     this.sheet = workbook.getSheetAt(0);
    }

    public static void main(String[] args) throws IOException {


        for(int i =0;i<5;i++) {
            List<String> rowData = new ReadExcelDemo(new File(
                    "howtodoinjava_demo_xls.xls")).readRow();
            System.out.println(rowData);
        }

    }

    private List<String> readRow() throws IOException {
        List<String> rowData = new ArrayList<String>();

            // Iterate through each rows one by one
            rowIterator = sheet.iterator();
            if (getNext()) {
                Row row = rowIterator.next();
                // For each row, iterate through all the columns
                Iterator<Cell> cellIterator = row.cellIterator();

                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    // Check the cell type and format accordingly
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        rowData.add(String.valueOf(cell.getNumericCellValue()));
                        System.out.print(cell.getNumericCellValue() + "\t");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        rowData.add(cell.getStringCellValue());
                        System.out.print(cell.getStringCellValue() + "\t");
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        rowData.add("");
                        System.out.println("");
                    }
                }
                System.out.println("");
            }
            rowNum++;
            close();


        return rowData;
    }

    private void close() throws IOException {
        file.close();
    }

    private boolean getNext() {
        // TODO Auto-generated method stub
        if (null == rowNum) {
            rowNum = 0;
        }
        return rowIterator.hasNext();
    }
}
`

这是代码片段。 我试图workbook.setMissingCellPolicy（Row.CREATE_NULL_AS_BLANK）; 但它不工作。 任何建议它为什么发生？

Answer 1:

我已经使用了迭代器行一个读取行一个，使得rowIterator一流水平，然后使用循环遍历列和接管行数据的全面控制和策略设置为“创建空的空白”。

final Row row = this.sheet.getRow(rowNum);

if (null != row) {

    int lastColumn = row.getLastCellNum();
    // Removing cellIterator as it was not supporting
    // MissingCellPolicy and doing the column iteration through for
    // loop
    for (int cn = Constants.EMPTY_INT; cn < lastColumn; cn++) {
        Cell cell = row.getCell(cn, Row.CREATE_NULL_AS_BLANK);

        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_NUMERIC:
            addNumericCell(rowData, cell);
            break;
        case Cell.CELL_TYPE_STRING:
            rowData.add(cell.getStringCellValue());
            break;
        case Cell.CELL_TYPE_BLANK:
            rowData.add(Constants.EMPTY_STRING);
            break;
        default:
            break;

        }
    }
}

有用的链接Apache的POI 。

Answer 2:

简化的代码中使用POI 3.10作品。 下面的代码返回片数据作为列表和适用于具有NULL值细胞。

/**
     * Read XLSx and return sheet data as List
     * 
     * @param inputFile
     * @param sheetNo
     * @return
     * @throws FileNotFoundException
     * @throws IOException
     */
    public static List<String> readXlsxAsList(File inputFile, int sheetNo) throws FileNotFoundException,
            IOException {

        List<String> sheetAsList = new ArrayList<String>();
        /**
         * Get workbook
         */
        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(inputFile));
        /**
         * Get sheet
         */
        XSSFSheet sheet = wb.getSheetAt(sheetNo);
        Iterator<Row> rowIterator = sheet.iterator();
        /**
         * Iterate Rows
         */
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            StringBuffer sb = new StringBuffer();
            for (int col = 0; col < row.getLastCellNum(); col++) {
                /**
                 * Create cell to force as BLANK when NULL
                 */
                Cell cell = row.getCell(col, Row.CREATE_NULL_AS_BLANK);
                /**
                 * Force cell type as String
                 */
                cell.setCellType(Cell.CELL_TYPE_STRING);
                /**
                 * Add to buffer
                 */
                sb.append(cell.getStringCellValue() + "|");
            }
            /**
             * Add buffer to list
             */
            sheetAsList.add(sb.toString());
        }
        return sheetAsList;
    }

文章来源: ExcelReader workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK) is not working