|
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
|
|
|
|
|
Hi,
I came across th follwing Exception from HRESULT: 0x800A03EC - COM Exception Unhandled.
Please find below the code I have used..
using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
namespace EXPORT_DATASET_EXCEL_II
{
static class ExportingCellByCellMethod
{
public static void ExportToExcel(DataSet dataSet, string outputPath)
{
ApplicationClass excelApp = new ApplicationClass();
Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);
int sheetIndex = 0;
foreach (System.Data.DataTable dt in dataSet.Tables)
{
Worksheet excelSheet = (Worksheet)excelWorkbook.Sheets.Add(
excelWorkbook.Sheets.get_Item(++sheetIndex),
Type.Missing, 1, XlSheetType.xlWorksheet);
excelSheet.Name = dt.TableName;
for (int col = 0; col < dt.Columns.Count; col++)
{
excelSheet.Cells[1, col + 1] = dt.Columns[col].ColumnName;
}
for (int col = 0; col < dt.Columns.Count; col++)
{
for (int row = 0; row < dt.Rows.Count; row++)
{
excelSheet.Cells[row + 2, col + 1] =
dt.Rows[row].ItemArray[col];
((Range)excelSheet.Rows[1,
Type.Missing]).AutoFit();
}
((Range)excelSheet.Columns[1,
Type.Missing]).AutoFit();
((Range)excelSheet.Columns[2,
Type.Missing]).AutoFit();
((Range)excelSheet.Columns[3,
Type.Missing]).AutoFit();
}
}
excelWorkbook.SaveAs(outputPath,
XlFileFormat.xlWorkbookNormal, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
XlSaveAsAccessMode.xlExclusive,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing);
excelWorkbook.Close(true, Type.Missing, Type.Missing);
excelWorkbook = null;
excelApp.Quit();
excelApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Configuration;
namespace EXPORT_DATASET_EXCEL_II
{
public partial class Form1 : Form
{
System.Data.OleDb.OleDbConnection conn;
OleDbDataAdapter DA;
string S;
DataSet source;
public Form1()
{
InitializeComponent();
conn = new System.Data.OleDb.OleDbConnection();
conn.ConnectionString =
ConfigurationManager.ConnectionStrings["MSAccess"].ToString();
}
private DataSet getDemoDataSet()
{
S = "Select Emp_ID as [ID], Emp_Name as NAME,Reason as REASON,
Submitted_Date as [SUBMITTED DATE] from LEAVEMASTER_TABLE";
source = new DataSet();
DA = new OleDbDataAdapter(S, conn);
DA.Fill(source);
source.Tables[0].TableName = "sample";
return source;
}
private void button2_Click(object sender, EventArgs e)
{
DataSet demoDataSet = this.getDemoDataSet();
string savepath;
saveFileDialog1 = new SaveFileDialog();
savepath = saveFileDialog1.FileName + ".xls";
saveFileDialog1.Filter = "xls files (*.xls)|*.xls";
if (saveFileDialog1.ShowDialog() ==
System.Windows.Forms.DialogResult.OK
&& saveFileDialog1.FileName.Length > 0)
{
ExportingCellByCellMethod.ExportToExcel(demoDataSet,
savepath);
}
MessageBox.Show("Data Exported successfully");
}
}
}
Can you pls help me out in solving this? Replies are highly appreciated. TIA.
Regards,
Karthik
|
|
|
|
|
|
Hi
I add object refferance for excel 2007 (office 12). your code works but while it is writing to file (at SaveAs) Compatibility Checker opens and ask me to countunie. i Used excelApp.DisplayAlerts = false; but when i open excel document, i saw just 65536 rows. I think excel try to support bacward compatibility and limit row sizes.
any suggestion to solve this problem?
|
|
|
|
|
I solved problem, for excel 2007 code as folloving, i just exported 242417 rows.
Important:outputPath file extention must be xls. like "Test.xls"
public static void ExportToExcel(DataSet dataSet, string outputPath)
{
System.Type moAppType;
moAppType = System.Type.GetTypeFromProgID("Excel.Application.12");
Application excelApp = (Microsoft.Office.Interop.Excel.Application)System.Activator.CreateInstance(moAppType);
excelApp.DisplayAlerts = false;
Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);
int sheetIndex = 0;
foreach (System.Data.DataTable dt in dataSet.Tables)
{
object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];
for (int col = 0; col < dt.Columns.Count; col++)
{
rawData[0, col] = dt.Columns[col].ColumnName;
}
for (int col = 0; col < dt.Columns.Count; col++)
{
for (int row = 0; row < dt.Rows.Count; row++)
{
rawData[row + 1, col] = dt.Rows[row].ItemArray[col];
}
}
string finalColLetter = string.Empty;
string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
int colCharsetLen = colCharset.Length;
if (dt.Columns.Count > colCharsetLen)
{
finalColLetter = colCharset.Substring(
(dt.Columns.Count - 1) / colCharsetLen - 1, 1);
}
finalColLetter += colCharset.Substring(
(dt.Columns.Count - 1) % colCharsetLen, 1);
Worksheet excelSheet = (Worksheet)excelWorkbook.Sheets.Add(
excelWorkbook.Sheets.get_Item(++sheetIndex),
Type.Missing, 1, XlSheetType.xlWorksheet);
excelSheet.Name = dt.TableName;
string excelRange = string.Format("A1:{0}{1}",
finalColLetter, dt.Rows.Count + 1);
excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;
((Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;
}
excelWorkbook.SaveAs(outputPath, XlFileFormat.xlExcel12, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excelWorkbook.Close(true, Type.Missing, Type.Missing);
excelWorkbook = null;
excelApp.Quit();
excelApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
|
|
|
|
|
HI
i tested your code and it worked fine but when i added the module to my applicaiton it is geneating an error "Exception from HRESULT: 0x800A03EC" and in the output window i am getting this message
A first chance exception of type \'System.Runtime.InteropServices.COMException\'
i added the needed DLLs thanks for your help
Mohammad Al Hoss
Development To Me Is A Pleasure more than a Job
|
|
|
|
|
Hi,
It's hard to find the problem, because don't have too much details about your specific case.
But we've found some issues when the DataTable has a field with a string larger than 911 characters.
Can you check if this is your case?
Can you try to truncate this string and see if this avoid this exception?
Regards,
|
|
|
|
|
This solved my problem......thanks
|
|
|
|
|
Glad to hear that.
|
|
|
|
|