如何使用Apache POI处理空行？ [英] How To handle Null Row using Apache POI?

问题描述

我使用Apache POI读取 xlsx 文件，效果很好。当找到行为null时，我有问题，我怎么能处理它？我的文件包含500行，但它显示105667行，其余行找到null。

I am using Apache POI to read xlsx file, it works well. I have question to you when row is found null, how I'm able to handle it? My file contain 500 row, but it show 105667 row, rest of row found null.

使用过的代码：

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 *
 * @author SAMEEK
 */
public class readXLSXFile {
public int getNumberOfColumn(String fileName, int sheetIndex) throws FileNotFoundException, IOException {
    File inputFile = null;
    FileInputStream fis = null;
    XSSFWorkbook workbook = null;
    XSSFSheet sheet = null;
    XSSFRow row = null;
    int lastRowNum = 0;
    int lastCellNum = 0;


    // Open the workbook
    inputFile = new File(fileName);
    fis = new FileInputStream(inputFile);
    workbook = new XSSFWorkbook(fis);
    sheet = workbook.getSheetAt(sheetIndex);
    lastRowNum = sheet.getLastRowNum();

    for (int i = 0; i < lastRowNum; i++) {

        row = sheet.getRow(i);
        if (row != null) {
            if (row.getLastCellNum() > lastCellNum) {
                lastCellNum = row.getLastCellNum();
            }
        }
    }

    return lastCellNum;
}

public int getNumberOfRow(String fileName, int sheetIndex) throws FileNotFoundException, IOException {
    File inputFile = null;
    FileInputStream fis = null;
    XSSFWorkbook workbook = null;
    XSSFSheet sheet = null;
    int lastRowNum = 0;

    // Open the workbook
    inputFile = new File(fileName);
    fis = new FileInputStream(inputFile);
    workbook = new XSSFWorkbook(fis);
    sheet = workbook.getSheetAt(sheetIndex);
    lastRowNum = sheet.getLastRowNum();
    return lastRowNum;
}

public String[] getSheetName(String fileName) throws FileNotFoundException, IOException {
    int totalsheet = 0;
    int i = 0;
    String[] sheetName = null;
    File inputFile = null;
    FileInputStream fis = null;
    XSSFWorkbook workbook = null;

    // Open the workbook
    inputFile = new File(fileName);
    fis = new FileInputStream(inputFile);
    workbook = new XSSFWorkbook(fis);
    totalsheet = workbook.getNumberOfSheets();
    sheetName = new String[totalsheet];
    while (i < totalsheet) {
        sheetName[i] = workbook.getSheetName(i);
        i++;
    }

    return sheetName;
}

public int getNumberOfSheet(String fileName) throws FileNotFoundException, IOException {
    int totalsheet = 0;
    File inputFile = null;
    FileInputStream fis = null;
    XSSFWorkbook workbook = null;
    XSSFSheet sheet = null;
    int lastRowNum = 0;

    // Open the workbook
    inputFile = new File(fileName);
    fis = new FileInputStream(inputFile);
    workbook = new XSSFWorkbook(fis);
    totalsheet = workbook.getNumberOfSheets();
    return totalsheet;
}

public String[][] getSheetData(String fileName, int sheetIndex) throws FileNotFoundException, IOException, InvalidFormatException {
    String[][] data = null;
    int i = 0;
    int j = 0;Cell cell=null;
    long emptyrowcount = 0;
    InputStream inputStream = new FileInputStream(
            fileName);
    // Create a workbook object.
    Workbook wb = WorkbookFactory.create(inputStream);
    wb.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK);
    Sheet sheet = wb.getSheetAt(sheetIndex);
    // Iterate over all the row and cells
    int noOfColumns = getNumberOfColumn(fileName, sheetIndex);
    System.out.println("noOfColumns::" + noOfColumns);
    int noOfRows = getNumberOfRow(fileName, sheetIndex) + 1;
    System.out.println("noOfRows::" + noOfRows);
    data = new String[noOfRows][noOfColumns];

    for (int k = 0; k < noOfRows; k++) {
        Row row = sheet.getRow(k);
        if (row == null) {


        } else {
            j = 0;
            for (int l = 0; l < noOfColumns; l++) {
                // Cell cell = cit.next();
                cell = row.getCell(j);


                if (cell.getCellType() == cell.CELL_TYPE_BLANK) {
                    cell = row.getCell(j, Row.CREATE_NULL_AS_BLANK);
                }

                data[i][j] = getCellValueAsString(cell);
                j++;

            }
            i++;

        }
    }

    return data;
}

/**
 * This method for the type of data in the cell, extracts the data and
 * returns it as a string.
 */
public static String getCellValueAsString(Cell cell) {
    String strCellValue = null;
    if (cell != null) {
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                strCellValue = cell.toString();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat dateFormat = new SimpleDateFormat(
                            "dd/MM/yyyy");
                    strCellValue = dateFormat.format(cell.getDateCellValue());
                } else {
                    Double value = cell.getNumericCellValue();
                    Long longValue = value.longValue();
                    strCellValue = new String(longValue.toString());
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                strCellValue = new String(new Boolean(
                        cell.getBooleanCellValue()).toString());
                break;
            case Cell.CELL_TYPE_BLANK:
                strCellValue = "";
                break;

        }
    }

    return strCellValue;
}

public static void main(String s[]) {
    try {
        readXLSXFile readXLSxFile = new readXLSXFile();
        String[][] sheetData = readXLSxFile.getSheetData("F:/work.xlsx", 0);

        int columnLength = 0;
        columnLength = readXLSxFile.getNumberOfColumn("F:/work.xlsx", 0);
        int rowLength = 0;
        rowLength = readXLSxFile.getNumberOfRow("F:/work.xlsx", 0);


        int h = 0;
        int j = 0;
        while (j < rowLength) {
            h = 0;
            while (h < columnLength) {
                System.out.print("\t     " + sheetData[j][h]);
                h++;
            }
            System.out.println("");
            j++;
        }

    } catch (InvalidFormatException ex) {
        Logger.getLogger(readXLSFile.class.getName()).log(Level.SEVERE, null, ex);
    } catch (FileNotFoundException ex) {
        Logger.getLogger(readXLSFile.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(readXLSFile.class.getName()).log(Level.SEVERE, null, ex);


       }
    }
}

<请帮我解决如何处理excel表中的空行？

Please help me how to handle null row in excel sheet?

推荐答案

如果你拿一行，然后回来 null ，那意味着该行的文件中没有存储数据 - 它完全空白。

If you fetch a row, and get back null, then that means there is no data stored in the file for that row - it's completely blank.

默认情况下的POI给你文件中的内容。使用单元格，您可以设置 MissingCellPolicy 控制丢失和空白单元格的处理方式。在 Apache POI文档中有一些使用它的示例。对于行，它们是否存在，因此在获取行时需要检查空值。

POI by default gives you what's in the file. With Cells, you can set a MissingCellPolicy to control how missing and blank cells are handled. There's some examples of using this in the Apache POI docs. With rows, they're either there or not, so you need to check for nulls when fetching a row.

这篇关于如何使用Apache POI处理空行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！