apache poi cellIterator 跳过空白单元格但不在第一行 [英] apache poi cellIterator skips blank cells but not in first row

问题描述

我正在创建一个 Java 程序来读取 Excel 工作表并创建一个逗号分隔的文件.当我运行带有空白列的示例 excel 文件时，第一行工作正常，但其余行跳过空白单元格.我已经阅读了将空白单元格插入行所需的代码更改，但我的问题是为什么第一行有效????

I am creating a java program to read an excel sheet and create a comma separated file. When I run my sample excel file, with blank columns, The first row works perfectly, but the rest of the rows skip the blank cells. I have read about the code changes required to insert blank cells into the rows, but my question is why does the first row work ????

public ArrayList OpenAndReadExcel(){
    FileInputStream file = null;
    HSSFWorkbook workBook = null;
    ArrayList <String> rows = new ArrayList();

    //open the file

    try {
         file = new FileInputStream(new File("Fruity.xls"));
    } catch (FileNotFoundException e) {
        // TODO Auto-generated catch block
        System.out.println("Could not open Input File");
        e.printStackTrace();
    }

    //  open the input stream as a workbook

        try {
             workBook = new HSSFWorkbook(file);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            System.out.println("Can't Open HSSF workbook");
            e.printStackTrace();
        }

        // get the sheet
        HSSFSheet sheet = workBook.getSheetAt(0);

        // add an iterator for every row and column
        Iterator<Row> rowIter = sheet.rowIterator();

        while (rowIter.hasNext())
        {

            String rowHolder = "";
            HSSFRow row = (HSSFRow) rowIter.next();
            Iterator<Cell> cellIter = row.cellIterator();
            Boolean first =true;
            while ( cellIter.hasNext())
            {
                if (!first)
                    rowHolder = rowHolder + ",";

                HSSFCell cell = (HSSFCell) cellIter.next();

                rowHolder = rowHolder + cell.toString() ;
                first = false;
            }

            rows.add(rowHolder);

        }

    return rows;

}
public void WriteOutput(ArrayList<String> rows) {

    // TODO Auto-generated method stub
    PrintStream outFile ;
    try {


        outFile = new PrintStream("fruity.txt");
        for(String row : rows)
        {   
            outFile.println(row);
        }
        outFile.close();

    } catch (FileNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

}
-----
我在 .xls 文件中的输入(抱歉不知道如何在此处插入 excel 表)

名称 >>>>>>>>>> 原产国 >>>>>>>>> 原产国 >>>>>>> 等级>>>>>> 月数
苹果 >>>>>>>> 美国 >>>>>>>>>>>>>>>>>>>>>> 华盛顿 >>>>>>>>>>>>>> A >>>>>>>>> 6
橙色 >>>>>> 美国 >>>>>>>>>>>>>>>>>>>> 佛罗里达 >>>>>>>>>>>>>>>>> A >>>>>>>>> 9
菠萝>>>>> 美国>>>>>>>>>>>>>>>>>>>>>> 夏威夷>>>>>>>>>>>>>>>>>> B >>>>>>>>> 10
草莓>>>> 美国>>>>>>>>>>>>>>>>>>>>>> 新泽西>>>>>>>>>>>>>> C >>>>>>>>>> 3

我的输出文本文件
姓名、原产国、原产国、、、等级、月数
苹果，美国，华盛顿，A，6.0
橙色，美国，佛罗里达，A，9.0
菠萝，美国，夏威夷，B，10.0
草莓，美国，新泽西，C，3.0

}
-----
my Input in .xls file (Sorry don't know how to insert an excel table here )

Name >>>>>>>>>> Country of Origin >>>>>>>>> State of origin >>>>>>> Grade>>>>>> No of months
Apple >>>>>>>> USA >>>>>>>>>>>>>>>>>>>>>> Washington >>>>>>>>>>>>>> A >>>>>>>>> 6
orange >>>>>> USA >>>>>>>>>>>>>>>>>>>>>> Florida >>>>>>>>>>>>>>>>> A >>>>>>>>> 9
pineapple>>>>> USA >>>>>>>>>>>>>>>>>>>>>> Hawaii >>>>>>>>>>>>>>>>>> B >>>>>>>>> 10
strawberry>>>> USA >>>>>>>>>>>>>>>>>>>>>> New Jersey>>>>>>>>>>>>>> C >>>>>>>>>> 3

my output text file
Name ,Country of Origin,State of origin,,,Grade,No of months
Apple,USA,Washington,A,6.0
orange,USA,Florida,A,9.0
pineapple,USA,Hawaii,B,10.0
strawberry,USA,New Jersey,C,3.0

Notice the two extra commas before the Grade column... This is because I have two blank columns there.<br/>

输出的其余部分中缺少这些额外的逗号.

These extra commas are missing in the rest of the output.

我使用的是 Apache Poi-3.9-20121203.jar

I am using Apache Poi-3.9-20121203.jar

推荐答案

您应该通读一下 Apache POI 网站上的迭代行和单元格文档.

CellIterator 将只返回文件中已定义的单元格，这主要意味着具有值或格式的单元格.excel 文件格式稀疏，无需存储既没有值也没有格式的单元格.

The CellIterator will only return cells that have been defined in the file, which largely means ones with either values or formatting. The excel file format is sparse, and doesn't bother storing cells which have neither values nor formatting.

对于您的情况，您必须将格式应用于第一行，这会导致它们显示出来.

For your case, you must have formatting applied to the first row, which causes them to show up.

您需要通读文档并切换到查找指数.这还可以让您完全控制如何在代码中处理空白单元格和从未使用过的单元格.

You need to read through the documentation and switch to lookups by index. That will also allow you full control over how blank vs never used cells are handled in your code.

这篇关于apache poi cellIterator 跳过空白单元格但不在第一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！