updating an excel file with apache metamodel

Question

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.

Here's the xlsx file:

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".

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.

Answer 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

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

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

to

    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.

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

Answer 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