#1

We’ve consistently been having trouble with LibreOffice Calc when modifying a workbook which we use for onboarding clients. Our internal tool uses EPPlus to read the file and generate user profiles. If I open the file in LibreOffice, make a change, and save the file, our internal tool throws an exception (see below). MS Excel also complains about the file after saving from LibreOffice, but does at least open it.

Can anyone offer any advice, or if a modification needs to be made to LibreOffice? I am not able to attach files to this post because I don’t have enough points. :confused:

Please note that we do not use EPPlus for modifying the workbook – that is not the source of the error.

If no changes are made, EPPlus accepts the file just fine.
If changes are made with MS Excel, EPPlus accepts the file just fine.

I am using LibreOffice 5.2.2.2 (x64) on Windows 10 version 1607, build 14393.321, but we have seen it occur in multiple other versions of LibreOffice and Windows.

System.ArgumentException was unhandled
HResult=-2147024809
Message=An item with the same key has already been added.
Source=mscorlib
StackTrace:
at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)
at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
at OfficeOpenXml.ExcelNamedRangeCollection.AddName(String Name, ExcelNamedRange item)
at OfficeOpenXml.ExcelNamedRangeCollection.Add(String Name, ExcelRangeBase Range)
at OfficeOpenXml.ExcelWorkbook.GetDefinedNames()
at OfficeOpenXml.ExcelPackage.get_Workbook()
at WorkbookImporter.Program.Main(String[] args) in C:\git\MediView\Utilities\WorkbookImporter\Program.cs:line 51
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:

#2

It might be helpful if you added details of what exactly Excel complains about and makes EPPlus stumble. The “System.ArgumentException was unhandled HResult=-2147024809 Message=An item with the same key has already been added.” unfortunately doesn’t tell anything except that HResult=-2147024809 is an odd value… It seems to be about some duplicated named range/expression, but that’s all one can deduce.

#3

Upvoted so now you should be able to attach example file. Remove any personal info first.

#4

Thanks @mark_t! Here are links to the Before and After workbooks.

My Workbook: Before

My Workbook: After

Also the error from MS Excel reads: We found a problem with some content in ‘MyWorkbook_After.xlsx’.Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes. After that, it does open.

#5

From the “After” files provided it seems that Excel removes the named ranges that have a file reference in the named range.

Example.
Before Opened by Excel:-
“Admin_Route” with Scope “Role Based Access”

='C:\Users\brucegraham\Dropbox\Flagstaff Fire Dept (AZ)\[Flagstaff Fire (AZ) - MediView Implementation workbook (R6).xlsx]BLT Use Only'!$B$1:$B$32

And “Admin_Route” with Scope “Workbook”

='BLT Use Only'!$B$1:$B$32

Before Opened by LibreOffice:-
“Admin_Route” with Scope “Role Based Access”

'file:///C:/Users/Mark/AppData/Local/Temp/1'#$'BLT Use Only'.$B$1:$B$32

And “Admin_Route” with Scope “Workbook”

$'BLT Use Only'.$B$1:$B$32

After opened by Excel no longer includes “Admin_Route” with Scope “Role Based Access”, but opened with LibreOffice still has:-

'file:///C:/Users/Mark/AppData/Local/Temp/2'#$'BLT Use Only'.$B$1:$B$32

Suspect that LibreOffice may not be saving the file reference correctly in a format that can be read correctly by Excel.

I suspect that you don’t intend to refer to a different workbook within the scope of “Role Based Access” and this might have been intruduced while copying from an older revision of your file into the current revision file. If you edit the named ranges and remove those that are specifying a file path then this might fix your problem.

#6

Just to clarify, the Before was generated in Excel by our colleague. I used LibreOffice to edit and save the document. And correct, do not want those references to old workbook. I guess I’m not sure if you’re saying that the cause of the error within EPPlus?

#7

I realise before was created by Excel, somehow some of the named ranges were duplicated and referenced within the scope of specific sheets and specifying links to the filename and sheet instead of just the sheet within the current workbook. If you edit those named ranges to remove the limited scope file name references then you should no longer have a problem when they are modified and then saved by LibreOffice.

#8

From what I see in your before and after files you could just open your master copy with LibreOffice, then save it again without changes. Open it with excel, let excel make corrections and then save the file from excel. That file should then work correctly when opened and saved by LibreOffice and then opened again by Excel. Hopefully the problem with EPPlus is also the same issue and will also be fixed.

#9

I’ve seen this kind of mess caused by Excel when copying between revisions of the same document, where both contain the same named range but excel decides you want a unique scope for the copied range. It was probably done to a sheet in a different workbook and then the worksheet moved into the same workbook that it had external references into.

#10

Permit me a newbie question… Where should I go to remove those references? Somehow the documentation seems to only say how to add rather than modify the definitions. Thanks very much for your help. Edit: Sorry I was typing this comment in between when you were typing yours. I’ll try with Excel.

#11

In LibreOffice you can manage named expressions in the menut “Insert”, “Named Expressions”, “Manage…”. But as you are using Excel for your master document I’d recommend using Excel to remove those file references.

#12

This is actually two bugs:

  1. When loading the original ‘Before’ file during compilation of the named expressions/ranges the external links mapping is not available, so in '[1]BLT Use Only'!$B$1:$B$32 the ID 1 of [1] is not resolved to a known external, hence the resulting file name ‘1’ (prepended with the document’s location path) that makes no sense in this context.
  2. When saving, for sheet names that have to be quoted the file ID is prepended but Excel expects it within the quoted sheet name, the result is [1]'BLT Use Only'!$B$1:$B$32 instead of '[1]BLT Use Only'!$B$1:$B$32.

Submitted as tdf#103530 and tdf#103531.

#13

And fixed for 5.2.4 and 5.3.0

#14