Hello,
unfortunately i can not post the excel-sheet because it is from a customer and contains sensible data.
Also the software is confidential and complex. I know it is hard for you to help me in this situation, but i try to gather all strange events:
If i manipulate ANY cell in the sheet and calling InsertRow all formulas with =SUMIF('Listungsveränderung'!R22C5:R621C5,'Customer_monthly'!RC3,'Listungsveränderung'!R22C16:R621C16))
will turn into:
=(SUMIF('Listungsveränderung'!R
405C5:R621C5,'Customer_monthly'!RC3,'Listungsveränderung'!R22C16:R621C16))
the row will set to the last filled cell in the row?!
So why does changing a cell in the worksheet a different cell too?
i tried everything from repairing the excel fill to downgrading the Spire.XLS library to different versions (currently using the latest stable via NuGet).
I also noticed that Spire.XLS can not parse R1C1 formulas without row index for example:
- Code: Select all
2018-01-18 15:04:16,523 [6] DEBUG Smitty.Services.Impl.Mutations.AddProductCustomerMonthly [(null)] - Kopiere R1C1 Column=Z: =+IFERROR(VLOOKUP(RC3,'ACT18'!C6:C19,MATCH('Customer_monthly'!R15C[-5],'ACT18'!R5C6:R5C19,False),False),0)
Exception thrown: 'spr2087' in Spire.XLS.dll
2018-01-18 15:04:16,535 [6] ERROR Smitty.Services.Impl.Mutations.AddProductCustomerMonthly [(null)] - Formula-Error
spr2087: C6:C19 is not valid named range
bei spr4785.0(Int32 A_0, String A_1, ParseParameters A_2, Dictionary`2 A_3, Int32 A_4, ParseFormulaOptions A_5)
bei spr4785.0(String A_0, ParseParameters A_1, Dictionary`2 A_2, Int32 A_3, ParseFormulaOptions A_4, Int32 A_5)
bei spr4785.0(String A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions A_3, ParseParameters A_4)
bei spr4785.0(Priority A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions& A_3, ParseParameters A_4)
bei spr4785.0(Priority A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions A_3, ParseParameters A_4)
bei spr4785.0(ParseFormulaOptions A_0, Dictionary`2 A_1, ParseParameters A_2, ExcelFunction A_3)
bei spr4785.0(ExcelFunction A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions A_3, ParseParameters A_4)
bei spr4785.0(Dictionary`2 A_0, Int32 A_1, ParseFormulaOptions A_2, ParseParameters A_3)
bei spr4785.0(Priority A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions& A_3, ParseParameters A_4)
bei spr4785.0(Priority A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions A_3, ParseParameters A_4)
bei spr4785.0(ParseFormulaOptions A_0, Dictionary`2 A_1, ParseParameters A_2, ExcelFunction A_3)
bei spr4785.0(ExcelFunction A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions A_3, ParseParameters A_4)
bei spr4785.0(Dictionary`2 A_0, Int32 A_1, ParseFormulaOptions A_2, ParseParameters A_3)
bei spr4785.0(Priority A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions& A_3, ParseParameters A_4)
bei spr4785.0(Priority A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions A_3, ParseParameters A_4)
bei spr4785.0(Priority A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions& A_3, ParseParameters A_4)
bei spr4785.0(Priority A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions A_3, ParseParameters A_4)
bei spr4785.1(String A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions A_3, ParseParameters A_4)
bei Spire.Xls.Core.Spreadsheet.FormulaUtil.0(String A_0, IWorksheet A_1, Dictionary`2 A_2, Int32 A_3, Int32 A_4, Boolean A_5)
bei Spire.Xls.Core.Spreadsheet.XlsWorksheet.0(Int32 A_0, Int32 A_1, String A_2, Boolean A_3)
bei Spire.Xls.Core.Spreadsheet.XlsRange.set_FormulaR1C1(String value)