Apache POI看到空电子表格中的列?

问题描述

我有一个空的电子表格，但是当我用Apache POI(版本3.10)访问它时，它说它有1024列和20物理列.
我确实删除了所有单元格，只保留了一些格式，但没有内容.
而且，如果我使用LibreOffice Calc(4.1.3.2版)删除了一些列，则列数只会增加！这是怎么回事?
有没有可靠的方法来获取实际的列数(或一行中的单元格)?

I have an empty spreadsheet, but when I'm accessing it with Apache POI (version 3.10), it says it has 1024 columns and 20 physical columns.
I really deleted all the cells, only some formatting remains, but no content.
And if I delete some columns with LibreOffice Calc (version 4.1.3.2), the number of columns only increases! What's going on?
Is there a reliable way to get the real number of columns (or cells in a row)?

import java.net.URL;
import org.apache.poi.ss.usermodel.*;

public class Test {
    public static void main(final String... args) throws Exception {
        final URL url = new URL("http://aditsu.net/empty.xlsx");
        final Workbook w = WorkbookFactory.create(url.openStream());
        final Row r = w.getSheetAt(0).getRow(0);
        System.out.println(r.getLastCellNum());
        System.out.println(r.getPhysicalNumberOfCells());
    }
}

推荐答案

经过更多调查后，我认为我已经知道发生了什么事.

After some more investigation, I think I figured out what's happening.

首先，来自POI的一些术语:电子表格中实际上根本不存在一些单元格-这些单元格称为丢失或未定义/未定义.然后，有一些已定义但没有值的单元格-称为空白单元格.两种类型的单元格在电子表格程序中均显示为空，无法通过视觉区分.

First, some terminology from POI: there are some cells that don't actually exist at all in the spreadsheet - those are called missing, or undefined/not defined. Then there are some cells that are defined, but have no value - those are called blank cells. Both types of cells appear empty in a spreadsheet program and can't be distinguished visually.

我的电子表格中，LibreOffice在行末添加了一些空白单元格(可能是一个错误).当我删除列时，LibreOffice似乎将随后的单元格(包括空白单元格)向左移动，并在末尾添加了更多的空白单元格(最多1024个).

My spreadsheet has some blank cells that LibreOffice added at the end of the row (possibly a bug). When I delete columns, LibreOffice seems to shift the subsequent cells (including the blank ones) to the left, and adds more blank cells at the end (up to 1024).

现在是关键部分:getLastCellNum()和getPhysicalNumberOfCells()都不会忽略空白单元格. getLastCellNum()给出最后一个定义的单元格，而getPhysicalNumberOfCells()给出定义的单元格的数量，均包括空白单元格.似乎没有任何方法可以跳过空白单元格. getPhysicalNumberOfCells()的javadoc在某种程度上具有误导性-如果仅第0、4、5列具有值，那么就会有3"，但是它实际上也在计算空白单元格，而这些单元格实际上没有值.

And now the key part: neither getLastCellNum() nor getPhysicalNumberOfCells() ignore blank cells. getLastCellNum() gives the last defined cell, and getPhysicalNumberOfCells() gives the number of defined cells, both including blank cells. There doesn't seem to be any method available that skips blank cells. The javadoc for getPhysicalNumberOfCells() is somewhat misleading - "if only columns 0,4,5 have values then there would be 3", but it's actually counting blank cells too, which don't really have values.

所以我发现的唯一解决方案是遍历单元格并检查它们是否为空.

So the only solution I found is to loop through the cells and check if they are blank.

旁注:getLastRowNum()和getFirstCellNum()基于0，但是getLastCellNum()基于1，wtf?

Side note: getLastRowNum() and getFirstCellNum() are 0-based but getLastCellNum() is 1-based, wtf?

这篇关于Apache POI看到空电子表格中的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！