|
Works fantastic. Thanks a lot.
|
|
|
|
|
If you wonder why the fast export method never exits the Excel.exe process properly even when calling the .Close() and .Quit() methods, this is because you have to release all com objects properly
A very interesting thread about this here
So the below code is the Fast Export method, arranged to close all com objects with excel visibility support, I have commented the calls to the GC's methods because they are not required (and dirty), the process exits fine here on XP and Windows 7 with hideWorkbook to True or False and without GC's methods
Private Shared Sub ReleaseComObjects(ByVal objList As List(Of Object))
For Each obj In objList
If obj Is Nothing Then Continue For
Try
Select Case True
Case TypeOf obj Is Microsoft.Office.Interop.Excel.Workbook
obj.Close()
Case TypeOf obj Is Microsoft.Office.Interop.Excel.ApplicationClass
obj.Quit()
End Select
Catch
End Try
Runtime.InteropServices.Marshal.FinalReleaseComObject(obj)
Next
End Sub
Public Sub InteropExportDatasetToExcel(ByVal dataSet As DataSet, ByVal outputPath As String, Optional ByVal hideWorkbook As Boolean = False)
Dim excelApp As Microsoft.Office.Interop.Excel.ApplicationClass = Nothing
Dim excelWorkbooks As Microsoft.Office.Interop.Excel.Workbooks = Nothing
Dim excelWorkbook As Microsoft.Office.Interop.Excel.Workbook = Nothing
Dim excelSheets As Microsoft.Office.Interop.Excel.Sheets = Nothing
Const colCharset As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim finalColLetter As String = String.Empty
Dim colCharsetLen As Integer = colCharset.Length
Try
Threading.Thread.CurrentThread.CurrentCulture = Globalization.CultureInfo.CreateSpecificCulture("en-US")
excelApp = New Microsoft.Office.Interop.Excel.ApplicationClass
excelApp.DisplayAlerts = False
excelWorkbooks = excelApp.Workbooks
excelWorkbook = excelWorkbooks.Add(Type.Missing)
excelSheets = excelWorkbook.Sheets
Dim sheetIndex As Integer = 0
Dim col, row As Integer
Dim excelSheetTmp As Object
Dim excelSheet As Object
Dim excelRange As Object
Dim excelRangeFont As Object
Dim excelFont As Object
For Each dt As DataTable In dataSet.Tables
sheetIndex += 1
Dim rawData(dt.Rows.Count, dt.Columns.Count - 1) As Object
For col = 0 To dt.Columns.Count - 1
rawData(0, col) = dt.Columns(col).ColumnName
Next
For col = 0 To dt.Columns.Count - 1
For row = 0 To dt.Rows.Count - 1
rawData(row + 1, col) = dt.Rows(row).ItemArray(col)
Next
Next
If dt.Columns.Count > colCharsetLen Then finalColLetter = colCharset.Substring((dt.Columns.Count - 1) \ colCharsetLen - 1, 1)
finalColLetter += colCharset.Substring((dt.Columns.Count - 1) Mod colCharsetLen, 1)
excelSheetTmp = excelSheets(sheetIndex)
excelSheet = excelSheets.Add(excelSheetTmp, Type.Missing, 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet)
excelSheet.Name = dt.TableName
excelRange = excelSheet.Range(String.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1), Type.Missing)
excelRange.Value2 = rawData
excelRangeFont = excelSheet.Rows(1, Type.Missing)
excelFont = excelRangeFont.Font
excelFont.Bold = True
ReleaseComObjects(New List(Of Object) From {excelFont, excelRangeFont, excelRange, excelSheet, excelSheetTmp})
Next
excelWorkbook.SaveAs(outputPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, _
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
Catch ex As Exception
ex.Data(0) = outputPath
Throw
Finally
If Not hideWorkbook Then
excelApp.Visible = True
excelWorkbook.Activate()
AddHandler excelApp.WorkbookBeforeClose, Sub()
Try
excelApp.Visible = False
ReleaseComObjects(New List(Of Object) From {excelSheets, excelWorkbooks, excelApp})
Catch ex As Exception
Throw
End Try
End Sub
Else
ReleaseComObjects(New List(Of Object) From {excelSheets, excelWorkbook, excelWorkbooks, excelApp})
End If
End Try
End Sub
modified 6-Mar-12 18:28pm.
|
|
|
|
|
Very good! Very interesting!
|
|
|
|
|
Thank you so much for this code.
|
|
|
|
|
|
Great ! I've solved a big problem with your code.
|
|
|
|
|
As an alternative, you can try this Excel C# / VB.NET component.
It doesn't use Excel Interop so it can be used both in Windows applications and Excel ASP.NET applications.
Here is a sample Excel C# code how to export DataSet to Excel:
var ef = new ExcelFile();
foreach (DataTable dataTable in dataSet.Tables)
ef.Worksheets.Add(dataTable.TableName).InsertDataTable(dataTable, 0, 0, true);
ef.SaveXls(dataSet.DataSetName + ".xls");
|
|
|
|
|
But Excel Interop doesn't export to specific cell ranges and isn't free...
|
|
|
|
|
works like a charm...thank you
|
|
|
|
|
When I've attempted to run the code in this article, the program gets stuck at this line of code:
excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;
Does anyone have any ideas as to why after waiting for 20 minutes the program is still on this line of code? TIA.
|
|
|
|
|
|
Muy buen ejemplo de código para exportar grandes cantidades de datos a una hoja de excel,
me ha ayudado mucho.
GRACIAS Peter!
|
|
|
|
|
First at all thank you Peter!
Be careful if an string value starts with the equal symbol because you will receive this error: "Exception from HRESULT : 0x800A03EC"
That is because Excel thinks it is a function...
To solve the problem just add a single quote character at the beginning of the string and the problem is solved.
Cheers!
|
|
|
|
|
Excelent article. Be careful if a cell starts with equal. Thanks!
|
|
|
|
|
Why it cant supports Excel 2007 format (xls)?
|
|
|
|
|
Thank you for the very fast code!
I am saving excel files about 40,000 rows long, and it takes about 2 seconds to complete. This is totaly awesome!
|
|
|
|
|
My excel export speed improved a lot, when using "range copy" instead of "cell copy".
|
|
|
|
|
Hey its really great one. it solved my issue, thanks a lot
|
|
|
|
|
hw 2 get the pop up window such as "Open" "Save" "Cancel" when v use the above code..
Can any 1 comw with a idea so that while exporting it should ask for Save?Open/Cancel option.
Darshan M R
|
|
|
|
|
Hi
This is obviously a great piece of code. thanks for the same.. but i found that. when i export my dataTable which contains some date fields.. its giving me some numbers in its place.. e.g. 37130 in place of 12/02/2000 12:00:00 AM .
Please help.
thanks
Joe.
|
|
|
|
|
Hi, what you described is correct. If you check the code, I only put the values into the cells, but no format is set to the cells. In this case, all of the cells have the default format "General".
For example, open Excel and enter a date value into a cell. Now, change the format for the cell from Date (Excel set this format automatically when you entered manually), and set the format for this cell to General. You'll see a number. This is exactly what happened with my code.
You have two solutions:
1. If you see, I create a new Excel Sheet, but you can have a previously template Excel sheet with all the format set. Then the code only copy the values preserving the format of your template.
2. The another solution is to set the format manually. For example, if the values for the column H are dates, you can use the following code to set the right format:
VB: CType(excelSheet.Columns("H:H"), Range).NumberFormat = "mm/dd/yyyy"
C#: ((Range) excelSheet.Rows["H:H", Type.Missing]).NumberFormat = "mm/dd/yyyy";
Good luck!
|
|
|
|
|
Hi PeterMoon,
Thanks for the help... saved my day. I made it a bit more generic as i cant have pre-build excel.
Hope this helps someone in need.
foreach (DataColumn dcDateTime in dt.Columns)
{
if (dcDateTime.DataType == typeof(System.DateTime))
{
int index = dcDateTime.Ordinal + 1;
string columnLetter = string.Empty;
if (index > colCharsetLen)
{
columnLetter = colCharset.Substring(index / colCharsetLen -1, 1);
}
columnLetter += colCharset.Substring((index) % colCharsetLen -1, 1);
columnLetter += ":" + columnLetter;
((Range)excelSheet.Columns[columnLetter, Type.Missing]).NumberFormat = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern;
}
}
Regards
Joe Augustine.
|
|
|
|
|
Hi,
First thanks for making this great work available to us all - it has made achieving this task so much easier!
My question relates to your point No. 1 above - updating an existing excel sheet. What changes do I need to make to the code to achieve this? I google how to do it and it's amazing how many results come up without being able to find a proper solution like yours above.
What I'm trying to achieve is:
1. Have existing template Excel files existing on the server, complete with formatting & other sheets and/or formulas.
2. Program opens existing Excel file and adds SQL query results to the worksheet
3. Saves a copy of the Excel file to a specified location (not over the top of the original template) OR opens the report on screen for the user to review and save if they choose to.
Cheers,
Steve.
modified on Thursday, October 8, 2009 1:48 AM
|
|
|
|
|
Try this
rawData[row + 1, col] = " " + dt.Rows[row].ItemArray[col] + " ";
it exports all columns as needed.
Thanks to Uricka for showing this to me.
|
|
|
|
|
Hi All. I upgraded the capabilities of this module for my own purposes and thought i'd share it in case it helps anyone else out. Thanks to the original author! Ignore/rename my custom module name, of course.
Imports Microsoft.Office.Interop
Module DataCenterExport
Friend Sub ExportDataSetToExcel(ByVal dataSet As DataSet, Optional ByVal outputPath As String = "", Optional ByVal ShowWorkBook As Boolean = True, Optional ByVal OutputFileFormat As Excel.XlFileFormat = Excel.XlFileFormat.xlWorkbookNormal)
' Create the Excel Application object
Dim excelApp As New Excel.Application
excelApp.DefaultSaveFormat = OutputFileFormat
excelApp.DefaultFilePath = outputPath
' Create a new Excel Workbook
Dim excelWorkbook As Excel.Workbook = excelApp.Workbooks.Add
Dim sheetIndex As Integer = 0
Dim col, row As Integer
Dim excelSheet As Excel.Worksheet
' Copy each DataTable as a new Sheet
For Each dt As System.Data.DataTable In dataSet.Tables
sheetIndex += 1
' Copy the DataTable to an object array
Dim rawData(dt.Rows.Count, dt.Columns.Count - 1) As Object
' Copy the column names to the first row of the object array
For col = 0 To dt.Columns.Count - 1
rawData(0, col) = dt.Columns(col).ColumnName
Next
' Copy the values to the object array
For col = 0 To dt.Columns.Count - 1
For row = 0 To dt.Rows.Count - 1
rawData(row + 1, col) = dt.Rows(row).ItemArray(col)
Next
Next
' Calculate the final column letter
Dim finalColLetter As String = String.Empty
Dim colCharset As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim colCharsetLen As Integer = colCharset.Length
If dt.Columns.Count > colCharsetLen Then
finalColLetter = colCharset.Substring((dt.Columns.Count - 1) \ colCharsetLen - 1, 1)
End If
finalColLetter += colCharset.Substring((dt.Columns.Count - 1) Mod colCharsetLen, 1)
' Create a new Sheet
excelSheet = CType(excelWorkbook.Sheets.Add(excelWorkbook.Sheets(sheetIndex), Type.Missing, 1, Excel.XlSheetType.xlWorksheet), Excel.Worksheet)
excelSheet.Name = dt.TableName & sheetIndex.ToString()
' Fast data export to Excel
Dim excelRange As String = String.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1)
excelSheet.Range(excelRange, Type.Missing).Value = rawData
' Mark the first row as BOLD
CType(excelSheet.Rows(1, Type.Missing), Excel.Range).Font.Bold = True
excelSheet = Nothing
Next
'Delete extra sheets
Dim sheetCount As Integer = excelWorkbook.Sheets.Count
Dim sheetDeleteAt As Integer = sheetIndex + 1
For x = sheetDeleteAt To sheetCount
CType(excelWorkbook.Sheets(sheetDeleteAt), Excel.Worksheet).Delete()
Next
'Save it
If Not String.IsNullOrEmpty(outputPath) Then
Try
excelWorkbook.SaveAs(outputPath, OutputFileFormat, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
Catch ex As System.Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Excel File Save Failed")
End Try
End If
'Show it or close it out
If ShowWorkBook Then
excelApp.Visible = True
excelWorkbook.Activate()
excelWorkbook = Nothing
excelApp = Nothing
Else
excelWorkbook.Close(True, Type.Missing, Type.Missing)
excelWorkbook = Nothing
excelApp.Quit()
excelApp = Nothing
End If
' Collect the unreferenced objects
GC.Collect()
GC.WaitForPendingFinalizers()
End Sub
End Module
|
|
|
|
|