ExcelReader workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK) 不起作用 [英] ExcelReader workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK) is not working

问题描述

我正在尝试使用 apache poi 读取 excel 文件(xls)文件.其中，在读取行期间，如果缺少单元格 (cellIterator) 将跳过该单元格并将下一个值放入不同的标题.

I am trying to read excel file(xls) file using apache poi. In that, during read row if a cell is missing (cellIterator) is skipping that cell and putting the next value to different header.

A B C

1 2 3

4 空白 6

在上述情况下，它将 6 放在空白单元格的B"列中，我需要 B 作为空白字符串.

In above case it is putting 6 in 'B' column at blank cell and i need B as blank String.

`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);但它不起作用.有什么建议为什么会这样吗??

This is the code snippet. I tried workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK); but it is not working. Any suggestion why it is happening ??

推荐答案

我已经使用 iterator for row 逐行读取行，使 rowIterator 类级别，然后使用 for 循环遍历列并获取完整控制行数据并将策略设置为将 null 创建为空白".

I have used iterator for row to read the row one by one,making the rowIterator class level and then using for loop to iterate over columns and to take the full control over row data and set the policy to 'create null as blank'.

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 的有用链接.

这篇关于ExcelReader workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK) 不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！