A very common problem in winform applications occurs when data should be exported from DatagridView to Excel, there's some commercial workarounds to solve this problem. This article describe a simple versatile and free way to do.
The work to exporting to Excel is made by "Office XP Primary Interop Assemblies (PIAs)"
Initially, the data is obtained from datagridview's datasource, formated and then exported to a excel file throw Excel COM Interiop.
The Datagridview datasource could be :
- ArrayList of Entities
- Dataset with at least 1 datatable
Data should be stored in a DataTable before beign exported to excel, if DataGridView's DataSource is an ArrayList, then the ArrayList is converted to a DataTable using the method ArrayListToDataTable
ArrayList arr = (ArrayList)dataGridView.DataSource;
dt = ArrayListToDataTable(arr);
Once the data is stored in the datatable is exported to excel using the method dataTable2Excel
dataTable2Excel(dt, dataGridView, pFullPath_toExport, nameSheet);
Using the code
You should only copy the following 2 files to your project
Use the DataGridView in you project and export to excel using the next code
Tools.ExportToExcel.ExportToExcel exp = new Tools.ExportToExcel.ExportToExcel();
exp.dataGridView2Excel(this.dgvDataToExport, path, "NameSheet");
Points of Interest
Please note that the titles in excel file was obtained from DataGridView headers, not from DataTable name columns.
There's a bug, titles of columns are not exported correct, i am working on it.
15/10/2008 First Release