使用apache元模型更新excel文件

I'm trying to incorporate Apache MetaModel into a project and keep running into a weird problem. I update an Excel spreadsheet row in code. The code finds the right row, deletes it, then appends the row (with my update) to the bottom of the spreadsheet. I'd like the update to happen in-place, with the same data staying in the same row. I thought it was something I was doing wrong, then set up a stupid simple project to duplicate the behavior. Unfortunately, the problem remains.

我试图将Apache元模型合并到一个项目中，并继续遇到一个奇怪的问题。我用代码更新Excel电子表格行。代码找到正确的行，删除它，然后将行(与我的更新)追加到电子表格的底部。我希望更新发生在适当的位置，相同的数据保持在同一行。我认为这是我做错了的事情，然后建立了一个愚蠢的简单项目来复制这种行为。不幸的是,问题依然存在。

Here's the xlsx file:

这是xlsx文件:

Name    Address           City          State   Zip
Bob     123 Main St.      Norman        OK      11111
Fred    989 Elm Street    Chicago       IL      22222
Mary    555 First Street  San Francisco CA      33333

Now, I want to update Bob's Zip to "None".

现在，我想将Bob的Zip更新为“None”。

package MMTest;
import java.io.File;
import org.apache.metamodel.UpdateableDataContext;
import org.apache.metamodel.excel.ExcelDataContext;
import org.apache.metamodel.schema.Column;
import org.apache.metamodel.schema.Schema;
import org.apache.metamodel.schema.Table;
import org.apache.metamodel.update.Update;
public class MMTest {
    public static void main(String[] args) {
    UpdateableDataContext excel = new ExcelDataContext(new File("C:/test/test.xlsx"));
    Schema schema = excel.getDefaultSchema();
    Table[] tables = schema.getTables();
    assert tables.length == 1;
    Table table = schema.getTables()[0];
    Column Name = table.getColumnByName("Name");
    Column Zip = table.getColumnByName("Zip");
    excel.executeUpdate(new Update(table).where(Name).eq("Bob").value(Zip, "None"));
    }
}

Pretty simple right? Nope. This is the result:

很简单对吧?不。这是由于:

Name    Address           City          State   Zip
<blank line>                
Fred    989 Elm Street    Chicago       IL      22222
Mary    555 First Street  San Francisco CA      33333
Bob     123 Main St.      Norman        OK      None

Am I missing something simple? The documentation is pretty sparse, but I've read everything the internet has to offer on this package. I appreciate your time.

我错过了什么简单的东西吗?文档非常少，但是我已经阅读了internet提供的关于这个包的所有内容。我很欣赏你的时间。

2 个解决方案

#1

Late to the party, but I've recently bumped into this issue and haven't spotted an answer elsewhere yet. The actual deleting takes place in ExcelDeleteBuilder.java

聚会迟到了，但是我最近遇到了这个问题，还没有在别的地方找到答案。实际删除发生在ExcelDeleteBuilder.java中。

If you aren't concerned about maintaining row order, you could change

如果您不关心维护行顺序，您可以更改。

    for (Row row : rowsToDelete) {
        sheet.removeRow(row);
    }

来

    for (Row row : rowsToDelete) {
        int rowNum = row.getRowNum() + 1;
        sheet.removeRow(row);
        sheet.shiftRows(rowNum, sheet.getLastRowNum(), -1);
    }

See Apache POI docs for a better understanding of shiftRows(). As Adi pointed out, you'll still end up with the "updated" row being moved to the bottom, but in my use case the empty row is successfully removed.

请参阅Apache POI文档，了解shiftRows()。正如Adi所指出的，您最终仍将“更新”行移动到底部，但在我的用例中，空行被成功删除。

N.B. I'm working from Apache Metamodel 4.5.4

我在Apache Metamodel 4.5.4工作。

#2

You are not missing anything. The ExcelDataContext is not providing it's own update behavior. It is defaulting to use apache meta-model's default store agnostic implementation for updating the data. That implementation of UpdateCallback uses DeleteAndInsertCallback which is causing the behavior you are observing. It picks the row to be updated, updates it with a new value in memory, deletes the original row and inserts the updated row(which ends up in the bottom which is ExcelDataContext behavior). You can open an issue at https://issues.apache.org/jira/browse/METAMODEL Attach your sample code and data. Best would be a failing unit test in https://git-wip-us.apache.org/repos/asf/metamodel.git

你没有错过任何东西。ExcelDataContext没有提供它自己的更新行为。默认使用apache元模型的默认存储不可知实现来更新数据。UpdateCallback的实现使用DeleteAndInsertCallback，它会导致您正在观察的行为。它选择要更新的行，在内存中使用一个新值对其进行更新，删除原始行并插入更新后的行(最后在底部出现ExcelDataContext行为)。您可以在https://issues. apache.org/jirae./browse/metamodel上打开一个问题，附加您的示例代码和数据。在https://git-wip-us.apache.org/repos/asf/metamodel.git中，最好是失败的单元测试。

#1