Deleting rows using xlsx4j (which has formulas)

[mehul149]

Hello

I am trying to delete rows from an excel file using this code

Code: Select all

if (centerList.contains(center))
                   {
                  calculationList.add(new Long(r.getR()));
                  rows.remove(r);
                  i--;
                  total_rows--;
                  old_R--;
                  removal = true;
                  centerList.remove(center);
               }

by rows.remove(r) line.

But this only clears the row.
So I also have to move the cells to one row above after remove, which I do by this.

Code: Select all

for (Cell cr : r.getC())
               {
                  String str = cr.getR();
                  str = str.replaceAll("[0-9]+", new Long(old_R+1).toString());
                  cr.setR(str);
               }
               r.setR(old_R+1);


But In the end I end up losing a lot of formulas in the end rows. My file has 40k rows and formula is retained in randomly 55-65 rows in the top.
How do I take care of the formulas of the rows that I want to maintain?

[schlebe]

I use the same technic and I have the same behavior.

The row that I will remove is only CLEARED but it is always there.

Question: How to remove a row using XLS4J ?

My code is simple

Code: Select all

org.xlsx4j.sml.Row oFirstRow = null;
... init oFirstRow in a loop
data.getSheetData().getRow().remove(oFirstRow);


Why to call a function REMOVE when it done a CLEAR ?

Why oFirstRow.remove() doesn't exist ! That will be more simple

[schlebe]

Sorry, but the DELETE row function defined in XLSX4J work well.

In Excel (on screen) the lines are NOT deleted but in sheet1.xml file the lines ARE deleted.

The problem is that the cells defined after deleted rows continue to point to same row.

Each <c> defined in <row> has an attribut R that defines the cell's position and contains a value like "C200" where C is the column letter and 200 is the row number.

If you delete the row 180, the cells R="C200" continue to be C200 because the remove row XLSX4J doesn't reduce the cell position to C199 !

I think that it is the responsability of developer to reduce the row position of each row defines after deleted row using the following algorithm

For each row defined after deleted row, row number defined in R attribut of <c> tag must be decremented by 1.

CAUTION: the R attribute of <row> tag must also be decremented !