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() 给出最后一个 defined 单元格，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 在空电子表格中看到列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！