DataGridView to Excel
The CarlosAg Excel Xml Writer Library does not require Excel in order to generate Excel Workbooks. I already presented a method to print a DataGridView (here) and now i present you a method that allows you to export a DataGridView to an Excel Workbook: DataGridViewToExcel.zip.
i have problem with generate excel file from datagridview in vb.net(window app). Do you mind to show me how it code?
thanks
May 14th, 2007 at 11:37
Just compile my code, use Reflector to decompile to vb and you’re set…
May 14th, 2007 at 11:49
Hallo,
I’m searching something compareable like your Datagrid to Excel Code vor VB.NET.
All of this stuff is new for me and I have to find a quickly sollution. Can you help me with this? Sure, you will get paid for your work.
Yours
marcus (germany)
May 28th, 2007 at 13:02
Apparently the reflector export code addin isn’t flawless.. Anyway, here is a VB version:
http://www.timvw.be/wp-content/code/vb/DataGridViewToExcel.zip
May 28th, 2007 at 16:10
Thanks!
Excel 2007 complains about the format, but will open it ok when prompt is ignored.
June 28th, 2007 at 19:05
Hello Tim,
I am also trying to export my datagridview ro excel in VB.Net 2005.
i downloaded your example program but it won’t open propperly?!
it says…..
Could not find type ‘DataGridViewToExcel.ExcelButton’. Please make sure that the assembly that contains this type is referenced. If this type is a part of your development project, make sure that the project has been successfully built.
how can i fix this so i can see the program running and get an idea of how i can acheive the same results in my program!!
thanks in advance,
Iain
August 15th, 2007 at 10:25
Ok, i’m able to reproduce the problem: If you open the solution, and open Form1.vb in the designer.
Solution: Rebuild the project and then open the designer again.
August 15th, 2007 at 10:29
brilliant!!
thanks alot, hopefuly i’ll be able to finish my program now!!
August 15th, 2007 at 10:31
hello again,
how do i use that code in my program? can i just copy the class excell generator into my project??
also how do i import carlosAg.ExcelXmlWriter from??
sorry to be a pain!
and once again,
thank you
August 15th, 2007 at 10:38
*) You could copy-paste the code into your project or you could add another project, dedicated for the control (and then add a reference to that new project)
Adding references (or importing as you named it) is explained in http://www.deitel.com/books/NET/AddingReferences.pdf
August 15th, 2007 at 11:08
brilliant I have it working!!!!!
thank you very much for your help its much appreciated!!!
August 15th, 2007 at 14:52
Great effort! I really appreciate your work.
It is working amazing!
September 9th, 2007 at 13:07
Good work. Really found this useful.
Writing because I’m having a problem expanding your code to process an array of datagridview. If you have time, please take a look at the code and tell me what’s wrong. I keep getting an error on style. Thanks in advance.
Public Shared Function Generate(ByVal dataGridArray() As DataGridView) As Workbook
Dim workbook As New Workbook
For Each dataGridView As DataGridView In dataGridArray
Dim worksheet As Worksheet = workbook.Worksheets.Add(dataGridView.Name) ‘workbook.Worksheets.Add(“Sheet 1″)
Dim worksheetRow As New WorksheetRow
Dim dataGridViewColumn As DataGridViewColumn
For Each dataGridViewColumn In dataGridView.Columns
worksheet.Table.Columns.Add(New WorksheetColumn(dataGridViewColumn.Width))
worksheetRow.Cells.Add(New WorksheetCell(dataGridViewColumn.HeaderText))
Next
worksheet.Table.Rows.Insert(0, worksheetRow)
Dim worksheetDefaultStyle As WorksheetStyle = ExcelGenerator.GetWorksheetStyle(dataGridView.DefaultCellStyle, “Default”)
workbook.Styles.Add(worksheetDefaultStyle)
Dim rowIndex As Integer
For rowIndex = 0 To dataGridView.RowCount – 1
worksheetRow = worksheet.Table.Rows.Add
Dim columnIndex As Integer
For columnIndex = 0 To dataGridView.ColumnCount – 1
Dim cell As DataGridViewCell = dataGridView.Item(columnIndex, rowIndex)
Dim cellStyle As WorksheetStyle = ExcelGenerator.GetWorksheetStyle(cell.InheritedStyle, String.Concat(New Object() {“column”, columnIndex, “row”, rowIndex}))
If (Not cellStyle Is Nothing) Then
workbook.Styles.Add(cellStyle)
Else
cellStyle = worksheetDefaultStyle
End If
Dim dataType As DataType = ExcelGenerator.GetDataType(cell.ValueType)
worksheetRow.Cells.Add(cell.FormattedValue.ToString, dataType, cellStyle.ID)
Next columnIndex
Next rowIndex
Next dataGridView
Return workbook
End Function
October 1st, 2007 at 16:47
As already obvious, i don’t really like doing VB.Net
Anyway, i would refactor the Generate method so that it accepts a workbook instance (instead of creating one) inside the method.. This way you can create the Workbook, foreach DataGridView Generate(dataGridView, workBook and be done with it
October 1st, 2007 at 19:51
Tim-
The problem turns out to be that once you have defined the worksheet style the first time, the program throws if you try and define it again (for the next worksheet), even if it’s the same style. I simply defined the style with the first worksheet, and let the other worksheets use the format of the first one. Thanks again for the advice.
October 1st, 2007 at 21:51
Tim-
Don’t know if you care, but I found out my error. It comes down to adding new styles to the worksheet. Since I kept looping through all the data grid views I wanted to add to the worksheets, I inadvertently kept giving the new cell styles the same id (“column” + columnIndex + “row” + rowIndex) for cells on different worksheets.
October 3rd, 2007 at 20:19
All feedback is welcome
For a customer i’ve written a version that does the “id” generation a bit better (since “columnXXRowYY” makes your excel file grow enormously…
And as you already mentionned, reusing styles is another tweak that can lead to serious improvements…
October 3rd, 2007 at 21:38
Tim,
This code works great for me for smaller files, but I doesnt seem to save properly when theres hundreds of records. It seems to save fine, but when I try to open it in Excel it hangs. Any idea whats wrong?
October 18th, 2007 at 16:34
SteveMcD,
Your GridView has some formating like ALternateRowStyle which has different colour.So same formatting is taken by excel and so it takes time to open…
You use the default settings of the excel and try to open…
November 10th, 2007 at 12:05
I’m having trouble with the exported file. I’m getting an error saying that problems came up while loading the table area.
The example program works fine, and I just copied the code over with no modifications. So I’m not getting why it’s not working for me.
I’m guessing that there is some character in my datagridview that is throwing off the xml formatting. But since I don’t know XML it’s hard for me to pinpoint that as the problem.
Any suggestions?
November 21st, 2007 at 21:37
I will like show only the columns of DataGridView visibles in the excel.. is this possible?
thanks
December 13th, 2007 at 07:39
Hi Tim.
I really like what you have done, but I have a problem using it.
My DataGridView has ~200 rows and ~200 columns and not all cells contain data. I try to generete excel file but it has 38MB.
Is there a way you can help with this problem?
December 19th, 2007 at 18:49
hi,
i really need some help. I’ve made a short test-project and implemented your solution for excel export, and it worked fine.
In the project I’m currently work on, I got the error msg “problem while loading” “during the loading process errors occur in the following areas: tables” (translated from german, so might differ in english version;).
this is the way, I do it in my app (I’ve also included the excelgenerator file and the carlosag lib):
private void pRICKSTANDARDBindingNavigatorSaveItem_Click(object sender, EventArgs e)
{
using (SaveFileDialog saveFileDialog = GetExcelSaveFileDialog())
{
if (saveFileDialog.ShowDialog(this) == DialogResult.OK)
{
string fileName = saveFileDialog.FileName;
Workbook workbook = ExcelGenerator.Generate(this.STANDARDDataGridView);
workbook.Save(fileName);
Process.Start(fileName);
}
}
January 12th, 2008 at 18:55
sorry, forgot to say, the error occurs when excel is trying to open the file
January 12th, 2008 at 18:56
sorry again, but could it be, that the problem is in the datagridview itself? The original table is with some testdata (personID, date, valueA, valueB, …) I have a relation in it to a second table, where I get a client name from, which is not in the original table. So the gridview shows personID, date, lastname, firstname, valueA, valueB, …), doesn’t this work out?
January 12th, 2008 at 19:01
me again
it does work in the way, that the xml file is generated, and the data is in the file. but excel can’t open it?!
January 14th, 2008 at 21:14
Hello Mike,
I don’t have time to check it out but i remember that i’ve experienced the same problem with a couple of colors that excel didn’t support… So you may want to debug the generated xml file to figure out where exactly it goes wrong..
January 15th, 2008 at 11:57
Tim
Hi, very good post, it was very helpful, ewven thou I have a problem. when I export to excel double values that had been added to the Gridview it appear in excel as string and with a small green triangle on the superior left corner of each cell. How can I export with the field type properly.
Thank You very much
January 17th, 2008 at 23:03
Hello Bokzer,
The problem is that excel depends on the local to figure out what the decimal separator is.. In order to circumvent that problem i had decided to map all .Net double values to Excel string values…
If you want you can change the code in the Generate method:
DataType dataType = GetDataType( cell.ValueType ); string value = cell.FormattedValue.ToString(); if( cell.ValueType == typeof( double ) ) { if( value == string.Empty ) { dataType = DataType.String; } else { Regex regex = new Regex( "[a-zA-Z]" ); if( regex.IsMatch( value ) ) { dataType = DataType.String; } } value = value.Replace( " ", string.Empty ); } worksheetRow.Cells.Add( value, dataType, cellStyle.ID );January 18th, 2008 at 09:06
hi tim
well, I’ve opened the xml file with visual studio, and it shows no error … can’t find the problem
January 21st, 2008 at 09:57
Hi Tim
I have another question. Before I exporte I use the gv.AutoResizeRows() and gv.AutoRizeColumns() so everything fit perfectly but when the gridview is exported to excel the columns width change and become bigger. How can I fix it so the columns width are not bigger than the content length and fit perfectly?
Thank you very much again
January 22nd, 2008 at 22:31
I am exporting a gridview to excel, and when I get the Open or Save prompt, the save works great, but the open goes to excel and I get a message saying that the file can’t be found in the internet temporary folder. Any idea why the file is not being saved in the temporary folder?
January 28th, 2008 at 23:10
Dear Tim. I use your DataGridViewToExcel but in some cases not work. The excel leave a Exception when raise the .xls generated. Can you help me? Can you giveme your email and I send de xls generate.
Thanks!!
February 11th, 2008 at 18:59
Hello
I´m using your code to export a datagriview to excel and works fine.
The problem is, when I try to import the xls file to the datagridview I get this error:
External table is not in the expected format.
I alter your code to don’t set the style but it didn’t work.
If I copy the content of the xls file to another xls file and try to import, it works fine .
Can any one help me
February 20th, 2008 at 16:16
Hello tim.
i Will Source CarlosAg.ExcelXmlWriter.
you can find.
April 6th, 2008 at 09:43
this promgram work well with datagridviews conected to table that have small amounts of data!
when the data..etc are large it creates an excel file that never opens!…please help what can i do?
April 18th, 2008 at 14:27
You sir are a genius! Thanks for this – just what i was looking 4
April 22nd, 2008 at 00:00
Thanks this helped more than you know…..
August 6th, 2008 at 19:36
Question……
Now that I can save the Excel file my open thing don’t work anymore it ses that the file is in use by another program…
Even if I re-run the program that saved the file in the first place…..
Is there a way to add something to the code I got from you to tell it to close the file so the programs open thing works…..
here is the code I used to open the file:
this.dataGridView1.DataSource = null;
this.dataGridView1.DataBindings.Clear();
this.dataGridView1.ClearSelection();
this.dataGridView1.Rows.Clear();
this.dataGridView1.Columns.Clear();
OleDbConnection cnn = new OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + FileName + “;Extended Properties=\”Excel 8.0;HDR=No;IMEX=1\”;”);
OleDbDataAdapter da;
DataSet ds;
da = new OleDbDataAdapter(“Select * from [" + SheetName1 + "]“, cnn);
ds = new DataSet(“TestExcel”);
da.Fill(ds, “TestExcel”);
dataGridView1.DataSource = ds.Tables[0];
~OR~
Is there a better way to make my Excel opener to open the file your code in my program saved….
(I’ll be watching the form sight so if an answer is posted I’ll see it…)
Thank you for your time,
zBuster
August 6th, 2008 at 19:55
ALSO~~~~~~~ALSO~~~~~~~ALSO~~~~~~~ALSO~~~~~~~ALSO~~~~~~~ALSO~~~~~~~ALSO~~~~~~~ALSO~~~~~~~ALSO~~~~~~~ALSO~~~~~~~ALSO~~~~~~~
I restarted the computer and did some things to my code…
AND>>>>>>>
The program said that the excel file was not in the correct format….
So, could you tell me of a way to be able to open a file that your code saved with code from the same program….
AND>>>>>>>
Could you tell me how to also make it to where the program is still able to open regular excel files too….(Like it knows the difference between the two…)
~~~~~~~~(I’ll be watching this page to see if any new posts come up about my three posts…)~~~~~~~~
August 6th, 2008 at 21:01
Hello,so far your code to export datgridview(dgv)to Excel is wrking fine.My issue is “Is it possible to save(export) 2 separate dgv in same Excel sheet with dgv1 in sheet1 and dgv2 in sheet2 of same Excel”? Kindly reply as soon as possible.Thnx
August 18th, 2008 at 12:07
@pyar:
It’s certainly possible but you’ll have to write it yourself…
August 18th, 2008 at 15:10
Would like to know if I could export the results of a query in the dataset directly to excel without using datagridview and Run my macro.
Thanks in Advance!
Shreya
September 9th, 2008 at 01:00
Yes you can, and if you perform a websearch you will find plenty of resources that carefully explain the process.
September 9th, 2008 at 07:37
well
gr8 job guys
October 23rd, 2008 at 13:00
The datagridview to Excel works great. I’ve even managed to change the sheet name. Can you give me any pointers on how to include more than one sheet in an excel workbook.?
Much appreciated.
October 25th, 2008 at 01:10
It is a matter of calling:
book.Worksheets.Add("name of sheet");For the consumer you could implement a custom type editor that allows the user to pick one or more datagridviews that exist on the designer form.. And in the generate method iterate over these selected instances (I have used that technique succesfully at one customer for a couple of years now.)
October 25th, 2008 at 07:17
i don t no why but this promgram work well with datagridviews conected to table that have small amounts of data!
when the data..etc are large it creates an excel file that never opens!what can i do?
November 4th, 2008 at 14:26
Hello Tim,
I have the same problem as some posters.. i get the message that the excel file was not in the correct format….
do you have a proper solution for this?
thanks in advance!
December 1st, 2008 at 15:36
very good,,
congratulations, the class is unique and very good
Thank You
Atte.
Omar
December 2nd, 2008 at 22:39
Thank you very much, I have been searching for a way to implement this using other methods, but this one really does the trick.
March 9th, 2009 at 13:37
thank you very much, my extension isn’t supports color so this the best support for me
March 19th, 2009 at 14:35
Thanks a lot a wonderful application…. many and many hours saved (not to speak of headache)
July 1st, 2009 at 15:46