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

推荐答案

您应该通读在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屋！