Hi all,
What is the best (free or paid for) to read Excel files (XLS and XLSX) using C# and populate a datagrid, I would also like to have any changes made in the datagrid be written back to the file.
I should also add that it needs to be high performing as the datagrid can contain a significant number of rows (10,000+).
This would be used for a commercial application so it would need to be distributable.
This is a WinForm application.
Thanks Lee
You don't define whether you speak about web or fat client application.
You can use Open XML SDK 2.0 for Microsoft Office for reading XLSX file. (see http://www.microsoft.com/downloads/details.aspx?FamilyID=c6e744e5-36e9-45f5-8d8c-331df206e0d0&displaylang=en and http://msdn.microsoft.com/en-us/library/cc850837(v=office.14).aspx for some examples and also on http://openxmldeveloper.org/default.aspx.)
You can display and allow to modify the content in a grid like jqGrid (see http://www.trirand.com/blog/?page_id=6) if you use a Web application. This grid is the best jQuery plugin which has a lot of different features and allow implement practical every scenario.
After all you can use Open XML SDK to save result in a modified XLSX file.
I recommend databinding the datagrid to a dataset/datatable populated via the ADO.NET OleDb provider.
You'll find an example at Read from Excel using OleDb in a Windows Service?
I also found How To Use ADO with Excel Data from Visual Basic or VBA and How To Use ADOX with Excel Data from Visual Basic or VBA to be useful in knowing how to create a table (sheet) and access sheets and ranges.
It works very well for xls files. I haven't tried using it for xlsx.
It support read/write xls/xlsx files and can import/export workbook datas to/from a datagrid.
You can read a XLS file with Myxls library, is free and open source, you can see an example in the this url: Myxls example other alternative is NPOI what is pretty cool.