|
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.
|
|
|
|
|
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
|
|
|
|
|
|