|
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.
|
|
|
|
|
Hi,
I am getting the error "The type or namespace name 'ApplicationClass' could not be found",
Also i included Microsoft Office 11.0 Object Library reference
i don't have the namespace "Microsoft.Office.Interop.Excel;".
It was giving error on the line "using Microsoft.Office.Interop.Excel;"
and when i checked i can see this is available "Microsoft.Office.Core;".
Kindly let me know what i am missing.
Regards
Ravindra
|
|
|
|
|
Add a reference to "Microsoft Excel 11.0 Object Library" and then try again.
Regards,
|
|
|
|
|
If the data is in DataSet format, we still need to have the O(n^2) nested for loops to copy the data into the array format. Otherwise, we cannot assign the DataSet straight to the Value2 or using set_Value method. Any ideas?
|
|
|
|
|
Hello,
if you need to export DataSet to Excel, check out this Excel .NET library.
Here is a sample Excel C# code how to export DataSet to Excel:
var ef = new ExcelFile();
foreach (DataTable dataTable in dataSet.Tables)
{
var ws = ef.Worksheets.Add(dataTable.TableName);
ws.InsertDataTable(dataTable, "A1", true);
}
ef.SaveXls("DataSet.xls");
|
|
|
|
|
I changed the line:
excelSheet.get_Range("A1:H25", Type.Missing).Value2 =
bidimensionalObjectArray;
to:
excelSheet.get_Range("A1:H25", Type.Missing).set_Value(Type.Missing, bidimensionalObjectArray);
Value2 converts date and currency to its floating point representation, which is not always preferrable.
Besides that, great code/article!
|
|
|
|
|
Brilliant!
can I just make one amendment? In order for your change to be added directly to the original code by Peter, without having to think
you should replace...
excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;
with...
excelSheet.get_Range(excelRange, Type.Missing).set_Value(Type.Missing, rawData);
I was confused that you had used the term 'bidimensionalObjectArray', instead of referring to the object that was created in the code 'rawdata'.
Substituting this new line directly makes this an amazingly powerful piece of code.
I was previously using the Copyformrecordset function but this is not supported in DataSets (or DataTables) so I was 'casting' the Dataset to an ADODB.Recordset. I got this code from:
http://www.codeproject.com/KB/database/DataTableToRecordset.aspx
Unfortunately this meant that there were at least warnings if not errors when the rowcount went over 65532.
I can now do 1 million row outputs (should I choose to).
Well done guys, this just made my life a whole lot easier
|
|
|
|
|
when i run this project this error occured :
" Old format or invalid type library.(Exception from HRESULT: 0*80028018(TYPE_E_INVDATAREAD)) "
i debug the project and find line of error , error occur in "ExportingCellByCellMethod.cs" class
and on this line : "Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);"
please help me ! thx
|
|
|
|
|
Which version of Excel do you have installed?
The demo program has a reference to Microsoft Excel 11 Object Library. This means that Excel 2003 is required on the machine where the program is running.
Regards,
|
|
|
|
|
hi
i installed both of office 2003 & 2007
but program dont run. help me. i need it.
thx
|
|
|
|
|
|
Who works with 150 rows?
mn.yarar
|
|
|
|
|
As xls has limitaion of 65k rows i have a requirement to write 8 lakh records to excel 12.0 i.e. xlsx file.
i have following code to export data to excel 2003 file.
System.IO.StringWriter sw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(sw);
hw.RenderBeginTag(System.Web.UI.HtmlTextWriterTag.Html);
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AppendHeader("content-disposition",strVal);
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.Write("<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">");
HttpContext.Current.Response.Write("\r\n");
HttpContext.Current.Response.Write(@"<style>.NumberString {mso-number-format:\@;}</style>");
tblExcelExport.RenderControl(hw);
hw.RenderEndTag();
HttpContext.Current.Response.Write(sw.ToString());
Can you help me modify it to export data into excel 12.0 file format.
Also if you have any other method do let me know.
thanks in advance.
Sameer
|
|
|
|
|
I read the article on how to Export the datatable to Excel fastly, OOPs great , But one thing author forget to say is , if the data exceeds more than 60,000 rows will this code works ? . As we know excel can accomodate a maximum of 60,000 rows . if we want to insert more that that we have to split the data .
|
|
|
|
|
>> As we know excel can accomodate a maximum of 60,000 rows
You're right about this limit for the maximum number of rows in an Excel Sheet.
The point of the article was not to provide an Excel-exporting library. Instead of that, I wanted to show a better mechanism to export data to Excel. several considerations and validations must be done by the programmer to consider this limits and others.
After this comment and others, I'll soon write an Excel-exporting library, which consider this row or column limits, export with format, and so on.
Thanks for your comments!
|
|
|
|
|
Excel 2007 has significantly expanded almost all of the previous limits. They now support 1,048,576 rows by 16,384 columns.
|
|
|
|
|
Does MS Excel need to be installed on the end user's machine in order for this to work? If so, what is the earliest version of Excel that can open these files?
|
|
|
|
|
>>Does MS Excel need to be installed on the end user's machine in order for this to work?
Yes. The demo program has a reference to Microsoft Excel 11 Object Library. This means that Excel 2003 is installed on the machine where the program is running.
>>what is the earliest version of Excel that can open these files?
The demo program generates an standard Excel workbook, which can be opened using Excel 2003, or Excel 2007. For the last one, conversion will be made if the client choose to upgrade the file format.
|
|
|
|
|