Exception thrown while executing Worksheet.InsertRow

Sep 5, 2011 at 11:06 AM
Edited Sep 5, 2011 at 2:03 PM

I am using a templated xlsx with a formula like =SUM(A12:A15)

While trying to insert a row between 13th & 14th row, Worksheet.InsertRow is throwing the exception :

System.ArgumentOutOfRangeException : Start cell Address must be less or equal to End cell address

   à OfficeOpenXml.ExcelAddressBase.Validate()
   à OfficeOpenXml.ExcelAddressBase.SetAddress(String address)
   à OfficeOpenXml.ExcelAddress.set_Address(String value)
   à OfficeOpenXml.ExcelRange.get_Item(String Address)
   à OfficeOpenXml.ExcelWorksheet.AddFormulas(List`1 added, Int32 position, Int32 rows)
   à OfficeOpenXml.ExcelWorksheet.FixSharedFormulasRows(Int32 position, Int32 rows)
   à OfficeOpenXml.ExcelWorksheet.InsertRow(Int32 rowFrom, Int32 rows, Int32 copyStylesFromRow)
   à OfficeOpenXml.ExcelWorksheet.InsertRow(Int32 rowFrom, Int32 rows)

 

Could I avoid this annoying error ?

Thanks for all !

 

EDIT :

I cannot reproduce this error on a simple example. See this link to reproduce this error

Sep 8, 2011 at 7:21 AM

Solved my problem creating my own formula shifting algorithm.

- Copy formulas

- Clear formulas from the sheet

- Insert rows doing ExcelRange.Copy then ExcelRange.Clear

- Parse and shift formulas (the tricky part)

- Paste formulas

 

For your information : There where also some issues with shared formulas.
i.e. : If you have a formula in a cell (lets call it A) referencing another formula which is a B cell, then if you clear B cell, the A cell formula will be transformed to match the old B's one...
This was a pretty unexpected behavior which has barely turned me mad !

Sep 8, 2011 at 1:47 PM

I'm having a lot of problems with formulas too.  The code for handling inserts in EPPlus is pretty buggy when updating formulas.  Would you be willing to post the code to your solution?  I did take a look at the code with the intention of fixing it, but it was too much work for the time I had.  This is going to be a major problem for me soon, currently I'm ignoring it while I work on other areas of my project...

Sep 8, 2011 at 2:24 PM

Of course...

Its a simple and dirty solution which may involve many problems for complex cases such as graphs, very big workbooks and others...

But it works great for me.

Usage :

first, you should add a "using Reports.Extend;"
Then , use : "myExcelWorksheet.InsertRowsAfter(rowNumber,rowCount)"

Formulas are quickly parsed to find references to moved cells, deleted then recreated with updated formulas (after row insertion)

here are the sources : http://icareo.free.fr/Extend.zip

Sep 8, 2011 at 5:44 PM

btw ... there was also an issue related to cell merging : I could achieve to unmerge cells, but merging (via ws.Cells[string].Merged = true)  throws an exception (sth like "cells already merged" ... i dont remember)

Coordinator
Sep 20, 2011 at 7:40 PM

Thanks for reporting it. I'll have a look.