C# and Excel
-
Hi. I'm working with Visual Studio 2022 and I have an application that uses excel. I'd like to know your opinion on the interfaces available: Interop, ClosedXML or any other. Which one is the best? Thank you.
-
Hi. I'm working with Visual Studio 2022 and I have an application that uses excel. I'd like to know your opinion on the interfaces available: Interop, ClosedXML or any other. Which one is the best? Thank you.
There is no "best", only most appropriate, and what's most appropriate depends on your application type and what exactly you're going to be doing with either Excel or an Excel sheet.
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles. Dave Kreskowiak
-
Hi. I'm working with Visual Studio 2022 and I have an application that uses excel. I'd like to know your opinion on the interfaces available: Interop, ClosedXML or any other. Which one is the best? Thank you.
Dave is exactly right - it depends on what you are trying to do with the Excel file. But ... if you are trying to use an Excel file as a database, then one word applies: "DON'T!" Excel is a spreadsheet, and while it can work quite well as a database for single user small scale projects, as soon as the data size becomes in any way significant performance drops off a cliff. Part of this is the way .XLSX files work: they are a zip file containing a bunch of XML files. And XML files are text based - human readable if you are patient. So making any change to an XLSX file means opening the zip, decompressing it, reading the whole XML file, making changes, writing the XML back, then zip compressing it all back to an XLSX. That's fine for a small dataset, but once it grows ... life is too short. Then there ios the fun if two or more users want to work with the same data: Excel won't allow that, so you have to open the Excel connection in your code, wait for the file to be free, make your changes, and close the connection. If you want a DB, use a DB: SQLite, Access, MSSQL, MYSQL, ... depending on what exactly you have available, and are doing. But don't use a spreadsheet - it will just cause you massive problems later on!
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
-
There is no "best", only most appropriate, and what's most appropriate depends on your application type and what exactly you're going to be doing with either Excel or an Excel sheet.
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles. Dave Kreskowiak
Hi, Dave. I search for an easy to use interface with a relation of the available commands. I know Excel reasonably well and want to create visually good sheets. Do you have any suggestion?
-
Dave is exactly right - it depends on what you are trying to do with the Excel file. But ... if you are trying to use an Excel file as a database, then one word applies: "DON'T!" Excel is a spreadsheet, and while it can work quite well as a database for single user small scale projects, as soon as the data size becomes in any way significant performance drops off a cliff. Part of this is the way .XLSX files work: they are a zip file containing a bunch of XML files. And XML files are text based - human readable if you are patient. So making any change to an XLSX file means opening the zip, decompressing it, reading the whole XML file, making changes, writing the XML back, then zip compressing it all back to an XLSX. That's fine for a small dataset, but once it grows ... life is too short. Then there ios the fun if two or more users want to work with the same data: Excel won't allow that, so you have to open the Excel connection in your code, wait for the file to be free, make your changes, and close the connection. If you want a DB, use a DB: SQLite, Access, MSSQL, MYSQL, ... depending on what exactly you have available, and are doing. But don't use a spreadsheet - it will just cause you massive problems later on!
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
Hi, OriginalGriff. I don't have the intention of using Excel as a DB. In fact I use SQL Server. Excel is only a way of showing results in a pleasant way. At this time I use Interop Excel interface, but it's very difficult to find the commands and its variations. If you know a way of getting the library of these commands, I'd appreciate to know it. Thanks.
-
Hi, OriginalGriff. I don't have the intention of using Excel as a DB. In fact I use SQL Server. Excel is only a way of showing results in a pleasant way. At this time I use Interop Excel interface, but it's very difficult to find the commands and its variations. If you know a way of getting the library of these commands, I'd appreciate to know it. Thanks.
Excel as a display control seems somewhat overkill - especially as it requires Excel to be fully installed (and licenced) on the target machine. What does it do for your "pleasant display" that a standard DataGridView doesn't?
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
-
Excel as a display control seems somewhat overkill - especially as it requires Excel to be fully installed (and licenced) on the target machine. What does it do for your "pleasant display" that a standard DataGridView doesn't?
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
I know Excel reasonably well, but I know only the basic of DataGridView. Excel is installed, and also, if I want other people to see the data, Excel is more appropriate.
-
Hi, Dave. I search for an easy to use interface with a relation of the available commands. I know Excel reasonably well and want to create visually good sheets. Do you have any suggestion?
The OpenXML SDK can create workbooks and sheets and do it a lot faster than Excel Interop. The ClosedXML SDK can do the same thing, but it's a third-party library. The problem with using either is the learning curve. You're going to have to learn how workbooks and sheets are built and rewrite your code to do the same thing.
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles. Dave Kreskowiak
-
The OpenXML SDK can create workbooks and sheets and do it a lot faster than Excel Interop. The ClosedXML SDK can do the same thing, but it's a third-party library. The problem with using either is the learning curve. You're going to have to learn how workbooks and sheets are built and rewrite your code to do the same thing.
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles. Dave Kreskowiak
Thanks, Dave. I'll consider it.
