ClosedXML odd behavior
-
Firstly, I am not asking for help on a programming issue here. I'm mostly just trying to see if anyone else here is or has in the past been experiencing any problems using the ClosedXML .Net library to open/read Excel files. I have a simple process that has worked every day flawlessly for over 2 years, then out of the blue, started failing. :confused: Basically, ClosedXML was choking trying to open an Excel (*.xlsx) file. By choking, I mean it was an IO exception reporting that the file was corrupted. The weird thing is, I can copy that file to my desktop, open it in Excel, save it, copy it back to the server, and it works fine. :wtf: I am aware that a new version of OpenXML was released (right around the time that my process began failing???) with quite a few breaking changes. Coincidence?...I don't know yet. What's new in the Open XML SDK | Microsoft Learn[^] What I've tried: 0: Go to GitHub and get the latest ClosedXML libs. So this required a .net framework upgrade to 4.6+. No problem...compiles, go to open a spreadsheet and it complains about the XMLDocument version...go to GitHub, get that version and try again. It compiles fine, go to open a spreadsheet and now it whines about a netstandard library that it can't find. (sure this is an indication of inccompatibility) I tried different versions/combinations but the only way to get it working again was by reverting back to the original framework and original libraries. Back to square one. :doh: 1: Plead with the new IT guy responsible for scheduling that job to please change the format to CSV! :laugh: (the previous IT guy was on a power trip and refused to change it despite numerous requests) 2: Investigating the idea of simply extracting the sheet1.xml file from the archive and parsing it out. Then I wonder why if it was that easy, why there are so few solutions that mention this approach. It's possible that I'm looking at an extremely simple/limited structure (no formulas/formatting/etc.) in this particular file, but it looks feasible. In the event that #1 fails, this will probably be the next path of attack. 3: Install Excel on the customer's server. Ya know, they did give me an admin account so in theory, I can install anything required to get the job done. Also, I have an old Office 2007 disk around here that
This is why I avoided ClosedXML and work directly with OpenXML. I didn't like the idea of an update breaking something. This is an alternate suggestion if it feasible for you, I used the following snippet to build my own function: https://stackoverflow.com/questions/7208914/how-do-you-convert-excel-to-csv-using-openxml-sdk[^]
// TODO: Insert something here
Top ten reasons why I'm lazy 1.
-
This is why I avoided ClosedXML and work directly with OpenXML. I didn't like the idea of an update breaking something. This is an alternate suggestion if it feasible for you, I used the following snippet to build my own function: https://stackoverflow.com/questions/7208914/how-do-you-convert-excel-to-csv-using-openxml-sdk[^]
// TODO: Insert something here
Top ten reasons why I'm lazy 1.
-
Firstly, I am not asking for help on a programming issue here. I'm mostly just trying to see if anyone else here is or has in the past been experiencing any problems using the ClosedXML .Net library to open/read Excel files. I have a simple process that has worked every day flawlessly for over 2 years, then out of the blue, started failing. :confused: Basically, ClosedXML was choking trying to open an Excel (*.xlsx) file. By choking, I mean it was an IO exception reporting that the file was corrupted. The weird thing is, I can copy that file to my desktop, open it in Excel, save it, copy it back to the server, and it works fine. :wtf: I am aware that a new version of OpenXML was released (right around the time that my process began failing???) with quite a few breaking changes. Coincidence?...I don't know yet. What's new in the Open XML SDK | Microsoft Learn[^] What I've tried: 0: Go to GitHub and get the latest ClosedXML libs. So this required a .net framework upgrade to 4.6+. No problem...compiles, go to open a spreadsheet and it complains about the XMLDocument version...go to GitHub, get that version and try again. It compiles fine, go to open a spreadsheet and now it whines about a netstandard library that it can't find. (sure this is an indication of inccompatibility) I tried different versions/combinations but the only way to get it working again was by reverting back to the original framework and original libraries. Back to square one. :doh: 1: Plead with the new IT guy responsible for scheduling that job to please change the format to CSV! :laugh: (the previous IT guy was on a power trip and refused to change it despite numerous requests) 2: Investigating the idea of simply extracting the sheet1.xml file from the archive and parsing it out. Then I wonder why if it was that easy, why there are so few solutions that mention this approach. It's possible that I'm looking at an extremely simple/limited structure (no formulas/formatting/etc.) in this particular file, but it looks feasible. In the event that #1 fails, this will probably be the next path of attack. 3: Install Excel on the customer's server. Ya know, they did give me an admin account so in theory, I can install anything required to get the job done. Also, I have an old Office 2007 disk around here that
There's also the option to use Excel spreadsheet library for .NET Framework/Core - EPPlus Software[^] . There's a FAQ about licensing, depending on your requirements YMMV.
Luca The Price of Freedom is Eternal Vigilance. -- Wing Commander IV En Það Besta Sem Guð Hefur Skapað, Er Nýr Dagur. (But the best thing God has created, is a New Day.) -- Sigur Ròs - Viðrar vel til loftárása
-
Why not use something that is supported: Microsoft.Office.Interop.Excel Namespace | Microsoft Learn[^] or Working with MS Excel(xls / xlsx) Using MDAC and Oledb[^]?
Avoid Interop.
-
You can't install Interop on a server because of licencing. Well you can or used to be able to, but it took couple of registry hacks.
// TODO: Insert something here
Top ten reasons why I'm lazy 1.
Or in our case it wasn't allowed.
-
There's also the option to use Excel spreadsheet library for .NET Framework/Core - EPPlus Software[^] . There's a FAQ about licensing, depending on your requirements YMMV.
Luca The Price of Freedom is Eternal Vigilance. -- Wing Commander IV En Það Besta Sem Guð Hefur Skapað, Er Nýr Dagur. (But the best thing God has created, is a New Day.) -- Sigur Ròs - Viðrar vel til loftárása
Unless "they" who control the data centers don't allow it on the servers.
-
Firstly, I am not asking for help on a programming issue here. I'm mostly just trying to see if anyone else here is or has in the past been experiencing any problems using the ClosedXML .Net library to open/read Excel files. I have a simple process that has worked every day flawlessly for over 2 years, then out of the blue, started failing. :confused: Basically, ClosedXML was choking trying to open an Excel (*.xlsx) file. By choking, I mean it was an IO exception reporting that the file was corrupted. The weird thing is, I can copy that file to my desktop, open it in Excel, save it, copy it back to the server, and it works fine. :wtf: I am aware that a new version of OpenXML was released (right around the time that my process began failing???) with quite a few breaking changes. Coincidence?...I don't know yet. What's new in the Open XML SDK | Microsoft Learn[^] What I've tried: 0: Go to GitHub and get the latest ClosedXML libs. So this required a .net framework upgrade to 4.6+. No problem...compiles, go to open a spreadsheet and it complains about the XMLDocument version...go to GitHub, get that version and try again. It compiles fine, go to open a spreadsheet and now it whines about a netstandard library that it can't find. (sure this is an indication of inccompatibility) I tried different versions/combinations but the only way to get it working again was by reverting back to the original framework and original libraries. Back to square one. :doh: 1: Plead with the new IT guy responsible for scheduling that job to please change the format to CSV! :laugh: (the previous IT guy was on a power trip and refused to change it despite numerous requests) 2: Investigating the idea of simply extracting the sheet1.xml file from the archive and parsing it out. Then I wonder why if it was that easy, why there are so few solutions that mention this approach. It's possible that I'm looking at an extremely simple/limited structure (no formulas/formatting/etc.) in this particular file, but it looks feasible. In the event that #1 fails, this will probably be the next path of attack. 3: Install Excel on the customer's server. Ya know, they did give me an admin account so in theory, I can install anything required to get the job done. Also, I have an old Office 2007 disk around here that
Never used it. What are you trying to do with it? Because I only needed to read it, I rolled my own XLSX reader.
-
Firstly, I am not asking for help on a programming issue here. I'm mostly just trying to see if anyone else here is or has in the past been experiencing any problems using the ClosedXML .Net library to open/read Excel files. I have a simple process that has worked every day flawlessly for over 2 years, then out of the blue, started failing. :confused: Basically, ClosedXML was choking trying to open an Excel (*.xlsx) file. By choking, I mean it was an IO exception reporting that the file was corrupted. The weird thing is, I can copy that file to my desktop, open it in Excel, save it, copy it back to the server, and it works fine. :wtf: I am aware that a new version of OpenXML was released (right around the time that my process began failing???) with quite a few breaking changes. Coincidence?...I don't know yet. What's new in the Open XML SDK | Microsoft Learn[^] What I've tried: 0: Go to GitHub and get the latest ClosedXML libs. So this required a .net framework upgrade to 4.6+. No problem...compiles, go to open a spreadsheet and it complains about the XMLDocument version...go to GitHub, get that version and try again. It compiles fine, go to open a spreadsheet and now it whines about a netstandard library that it can't find. (sure this is an indication of inccompatibility) I tried different versions/combinations but the only way to get it working again was by reverting back to the original framework and original libraries. Back to square one. :doh: 1: Plead with the new IT guy responsible for scheduling that job to please change the format to CSV! :laugh: (the previous IT guy was on a power trip and refused to change it despite numerous requests) 2: Investigating the idea of simply extracting the sheet1.xml file from the archive and parsing it out. Then I wonder why if it was that easy, why there are so few solutions that mention this approach. It's possible that I'm looking at an extremely simple/limited structure (no formulas/formatting/etc.) in this particular file, but it looks feasible. In the event that #1 fails, this will probably be the next path of attack. 3: Install Excel on the customer's server. Ya know, they did give me an admin account so in theory, I can install anything required to get the job done. Also, I have an old Office 2007 disk around here that
I've used [NPOI](https://github.com/dotnetcore/NPOI) for years without any problems
In a closed society where everybody's guilty, the only crime is getting caught. In a world of thieves, the only final sin is stupidity. - Hunter S Thompson - RIP
-
Avoid Interop.
-
Firstly, I am not asking for help on a programming issue here. I'm mostly just trying to see if anyone else here is or has in the past been experiencing any problems using the ClosedXML .Net library to open/read Excel files. I have a simple process that has worked every day flawlessly for over 2 years, then out of the blue, started failing. :confused: Basically, ClosedXML was choking trying to open an Excel (*.xlsx) file. By choking, I mean it was an IO exception reporting that the file was corrupted. The weird thing is, I can copy that file to my desktop, open it in Excel, save it, copy it back to the server, and it works fine. :wtf: I am aware that a new version of OpenXML was released (right around the time that my process began failing???) with quite a few breaking changes. Coincidence?...I don't know yet. What's new in the Open XML SDK | Microsoft Learn[^] What I've tried: 0: Go to GitHub and get the latest ClosedXML libs. So this required a .net framework upgrade to 4.6+. No problem...compiles, go to open a spreadsheet and it complains about the XMLDocument version...go to GitHub, get that version and try again. It compiles fine, go to open a spreadsheet and now it whines about a netstandard library that it can't find. (sure this is an indication of inccompatibility) I tried different versions/combinations but the only way to get it working again was by reverting back to the original framework and original libraries. Back to square one. :doh: 1: Plead with the new IT guy responsible for scheduling that job to please change the format to CSV! :laugh: (the previous IT guy was on a power trip and refused to change it despite numerous requests) 2: Investigating the idea of simply extracting the sheet1.xml file from the archive and parsing it out. Then I wonder why if it was that easy, why there are so few solutions that mention this approach. It's possible that I'm looking at an extremely simple/limited structure (no formulas/formatting/etc.) in this particular file, but it looks feasible. In the event that #1 fails, this will probably be the next path of attack. 3: Install Excel on the customer's server. Ya know, they did give me an admin account so in theory, I can install anything required to get the job done. Also, I have an old Office 2007 disk around here that
kmoorevs wrote:
.net framework upgrade to 4.6+ ... whines about a netstandard library that it can't find
Which version did you actually upgrade to? .NET Standard 2.0 sort-of works with 4.6.1+, but it has several issues. Microsoft recommend using at least 4.7.2: .NET Standard - .NET | Microsoft Learn[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
kmoorevs wrote:
.net framework upgrade to 4.6+ ... whines about a netstandard library that it can't find
Which version did you actually upgrade to? .NET Standard 2.0 sort-of works with 4.6.1+, but it has several issues. Microsoft recommend using at least 4.7.2: .NET Standard - .NET | Microsoft Learn[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
I've used [NPOI](https://github.com/dotnetcore/NPOI) for years without any problems
In a closed society where everybody's guilty, the only crime is getting caught. In a world of thieves, the only final sin is stupidity. - Hunter S Thompson - RIP
-
Richard Deeming wrote:
Which version did you actually upgrade to?
4.6.1 first then 4.7.2. Neither worked. Back to 4.5.2. :)
"Go forth into the source" - Neal Morse "Hope is contagious"
Any particular reason? 4.7.2 will make your life much easier when you want to reference a .NET Standard 2.0 library, and I don't recall seeing any breaking changes. I think the only thing that "broke" for me was an ambiguous reference error for a custom extension method I'd written, which had the same name as one they added to the BCL in 4.7.2. :)
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Not saying it doesn't.
-
Not saying it doesn't.
-
No it isn't.
-
No it isn't.
-
Exactly what I said.
-
Exactly what I said.
-
Nope, just offering unsolicited advice.
