Hi Everyone…
I have been exporting data from dataset to excel using nested loops, excel object, accessing each cell of excel sheet and each cell of dataset and so on and so forth until I find this……
Before writing about it a special thanks to Peter A. Bromberg for writing such a nice article which really made at least my life easy….:). In his article he wrote a very simple function to export data without using excel object and other messy stuff. XSL Transformation is applied to dataset and XML for excel is generated.
Below is the complete function which will write an Excel file to your local disk. You have to pass it the DataSet to export and path to where file should be generated.
public static void CreateWorkbook(DataSet ds, String path)
{
XmlDataDocument xmlDataDoc = new XmlDataDocument(ds);
XslTransform xt = new XslTransform();
StreamReader reader =new StreamReader(typeof (WorkbookEngine).Assembly.GetManifestResourceStream(typeof (WorkbookEngine), “Excel.xsl”));
XmlTextReader xRdr = new XmlTextReader(reader);
xt.Load(xRdr, null, null);
StringWriter sw = new StringWriter();
xt.Transform(xmlDataDoc, null, sw, null);
StreamWriter myWriter = new StreamWriter (path + “\\Report.xls“);
myWriter.Write (sw.ToString());
myWriter.Close ();
}
and thats it. Your DataSet has been exported to an Excel file which is saved at the path passed to this function.
For original article, please click here
–
Keep Smiling 
June 8, 2007 at 6:08 pm
First, thanks for the fuction “Export DataSet to Excel in C#”, but i’m getting error in “WorkbookEngine”, and i tried to find the namespace, but i dont get it.
I’m using visual studio 2005, C# Asp.net.
I would appreciate if you give a sln asap.
Once again thanks.
November 9, 2009 at 1:51 pm
C# Exporting a (full) Dataset to Excel (with worksheets and all)
Hi All.
I just thought I’d past this code that is collected and modified.
It’s from a whole lot of ppl that I can’t really remember who actually made it, but it work (give credit to the dude(s) out there whose name(s) I can’t remember. hehe)
(this code is just to help you get started, feel free to change and improve it as you see fit.) ^_^
Step 1: Create new Class and copy code below.
using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.IO;
using Microsoft.Office.Interop.Excel;
using System.Data;
namespace VGS
{
class ClassExcel
{
private String m_sFileName;
Application excelApp = null;
Workbook workbook = null;
Sheets sheets = null;
Worksheet newSheet = null;
public ClassExcel(String p_sFileName)
{
m_sFileName = p_sFileName;
}
public void ChangeFileName(String p_sFileName)
{
m_sFileName = p_sFileName;
}
public void SaveDatasetToExcel(DataSet p_dts)
{
try
{
if (File.Exists(m_sFileName))
{
File.Delete(m_sFileName);
}
if (File.Exists(m_sFileName) == false)
{
MakeTemp(m_sFileName);
}
if (File.Exists(m_sFileName))
{
DataSet dts = new DataSet();
dts = p_dts;
excelApp = new Application();
workbook = excelApp.Workbooks.Open(m_sFileName, 0, false, 5, “”, “”,
false, XlPlatform.xlWindows, “”,
true, false, 0, true, false, false);
sheets = workbook.Sheets;
foreach (Worksheet sheet in sheets)
{
sheet.Select(Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
}
for (int iTable = 0; iTable 0)
{
newSheet = (Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
newSheet.Name = dts.Tables[iTable].TableName;
for (int cc = 0; cc < dts.Tables[iTable].Columns.Count; cc++)
{
newSheet.Cells[1, cc + 1] = "'" + dts.Tables[iTable].Columns[cc].ColumnName;
}
for (int cc = 0; cc < dts.Tables[iTable].Columns.Count; cc++)
{
for (int rc = 0; rc < dts.Tables[iTable].Rows.Count; rc++)
{
if (dts.Tables[iTable].Rows[rc][cc] == null)
newSheet.Cells[rc, cc] = "";
if (dts.Tables[iTable].Columns[cc].DataType == System.Type.GetType("System.String"))
{
newSheet.Cells[rc + 2, cc + 1] = "'" + dts.Tables[iTable].Rows[rc][cc];
}
else
{
newSheet.Cells[rc + 2, cc + 1] = dts.Tables[iTable].Rows[rc][cc];
}
}
}
}
}
workbook.Save();
workbook.Close(null, null, null);
excelApp.Quit();
}
}
finally
{
newSheet = null;
sheets = null;
workbook = null;
excelApp = null;
GC.Collect();
}
}
private void MakeTemp(string p_sTempFileName)
{
Application exc = null;
try
{
exc = new Application();
Workbooks workbooks = exc.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
_Worksheet worksheet = (_Worksheet)workbook.Worksheets[1];
Range range = worksheet.get_Range("A1", System.Reflection.Missing.Value);
Object[] data = new Object[] { "" };
range.GetType().InvokeMember("Value", System.Reflection.BindingFlags.SetProperty, null, range, data);
workbook.Close(true, p_sTempFileName, null);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.ReadLine();
}
finally
{
if (exc != null)
exc.Quit();
}
}
}
}
ps. remember to add the reference:
"using Microsoft.Office.Interop.Excel;"
to your project;
Step 2: Done. lol
cheers.
June 8, 2007 at 6:22 pm
Dear Kabilan,
Here it is, step by step
1. Create the following class:
public class WorkbookEngine
{
public static void CreateWorkbook(DataSet ds, String path)
{
XmlDataDocument xmlDataDoc = new XmlDataDocument(ds);
XslTransform xt = new XslTransform();
StreamReader reader =new StreamReader(typeof (WorkbookEngine).Assembly.GetManifestResourceStream(typeof (WorkbookEngine), “Excel.xsl”));
XmlTextReader xRdr = new XmlTextReader(reader);
xt.Load(xRdr, null, null);
StringWriter sw = new StringWriter();
xt.Transform(xmlDataDoc, null, sw, null);
StreamWriter myWriter = new StreamWriter (path + “\\Report.xls”);
myWriter.Write (sw.ToString());
myWriter.Close ();
}
}
2. In your main program/class use the function as
WorkbookEngine.CreateWorkbook(ds, path);
‘ds’ will be your dataset to export and path to where file should be generated.
I hope it will help you.
Thanks.
–
Keep Smiling
June 11, 2007 at 2:52 pm
Dear Muhammad,
This time am getting a different error, and i couldn’t able to locate it…
the error is
“System.ArgumentNullException: Value cannot be null.\r\nParameter name: stream\r\n at System.IO.StreamReader..ctor(Stream stream, Encoding encoding, Boolean detectEncodingFromByteOrderMarks, Int32 bufferSize)\r\n at System.IO.StreamReader..ctor(Stream stream)\r\n at ExcelUtil.WorkbookEngine.CreateWorkbook(DataSet ds, String path) in C:\\ChangeMan\\RAI.FTC\\RAI.FTC.WebUI\\FTC Report\\Excel.cs:line 18″
and it occurs in line – “StreamReader reader =new StreamReader(typeof (WorkbookEngine).Assembly.GetManifestResourceStream(typeof (WorkbookEngine), “Excel.xsl”));”.
Pls. help me….
thanks.
June 11, 2007 at 2:54 pm
Dear Kabilan,
Tell me exactly which version of C# and VS you are using?
–
Keep Smiling
June 11, 2007 at 3:20 pm
Dear Muhammad,
C# 2.0
VS 2005
thanks
June 29, 2007 at 8:04 am
Dear Kabilan,
That line uses reflection to find the xsl file in the solution. This means that you have to mark the Excel.xsl-file as ‘Embedded Content’; if you right-click it in the solution and click properties you can pick the ‘Build Action’.
You dont have anything to do with a namespace; the typeof(WorkbookEngine)-function points to the type of your own class; there is no more to find…
To check if the reflection works: break your code somewhere and paste the “typeof (WorkbookEngine).Assembly.GetManifestResourceStream(typeof (WorkbookEngine), “Excel.xsl”)” into your Watches. You should get something different than null. If you do get null there is a problem with the string in there.
Good luck,
Joep
November 7, 2007 at 2:39 pm
Kabilan, have you ever find solution for the problem you had. I am having the same problem trying to run this code.
Thank you,
-Max
December 18, 2007 at 5:01 am
why the watchs the result is null
February 29, 2008 at 11:25 am
Anyone got this problem solved that Kaliban has? I get the same error using VS 2005 Express C#.
Please help
March 7, 2008 at 6:56 am
you guys has to add the excel to your local project. i am getting some other error at the line “xt.Load(xRdr, null, null);”. Please let me know some body who resolved this error. Error messages Invalid charector at row 1 and position 1
April 24, 2008 at 8:22 pm
Hi
I have the same problem in this line
StreamReader reader = new StreamReader(typeof(WorkbookEngine).Assembly.GetManifestResourceStream(typeof(WorkbookEngine), “Excel.xsl”));
…and im sure that i have added the file “Excel.xsl” to the project can anyone tell me please why this error continue?
May 13, 2008 at 5:50 am
you need to include below file to your project and set the property build action to embed.
// Excel.xsl
May 19, 2008 at 9:31 am
i am getting some other error at the line “xt.Load(xRdr, null, null);”. Please let me know some body who resolved this error. Error messages Invalid charector at row 1 and position 1
May 28, 2008 at 3:04 pm
StreamReader reader = new StreamReader(typeof(WorkbookEngine).Assembly.GetManifestResourceStream(typeof(WorkbookEngine), “Excel.xsl”));
I have some problem in this line like i am getting exception error…could anyone help me in this regard
May 30, 2008 at 12:44 pm
Hi,
There are better ways to export DataSet to Excel. Take a look how GemBox.Spreadsheet (.NET component for importing/exporting data to Excel) handles exporting DataSet to Excel:
http://www.gemboxsoftware.com/LDataSet.htm
Mario
GemBox Software
–
GemBox.Spreadsheet for .NET – Easily read and write Excel (XLS, XLSX or CSV)
or export to HTML files from your .NET apps. See http://www.gemboxsoftware.com/GBSpreadsheet.htm
–
July 15, 2008 at 8:04 am
Does someone resolved this problem?
If do can you please advice.
Thanks, shlomi.
August 12, 2008 at 9:53 pm
I have an error which is :
Error 3 ‘Service.WorkbookEngine.exportToExcel(System.Data.DataSet, string)’: not all code paths return a value
I don’t understand why. Did someone have already this error ?
Thanks
September 26, 2008 at 5:58 am
your code for single sheet was fantastic and was a great learning experience for me……………
I need a code to transfer dataset with multiple table onto a single excel workbook with multiple sheet. each sheet holding one table…………
the code should be in c#………….so that it should be a easy to learn for me as i am a new programmer…………
September 30, 2008 at 11:02 am
I can understand, what is WorkbookEngine,,
I got error on that stage
pls help me
September 30, 2008 at 11:06 am
Sorry I cant…
October 4, 2008 at 4:29 am
Hey.. WorkbookEngine is the class name I guess….
Just no need to put that..
It’s enough..
StreamReader reader = new StreamReader(“Excel.xsl”);
November 4, 2008 at 8:00 pm
I used the method. But My dataset have a few null columns and the excel sheet is getting next columns when there is a null column. Any suggestions?
Thanks
Neelakshi
November 7, 2008 at 9:59 am
i m getting this kind of problem:-
“Value cannot be null. Parameter name: stream”
My ASP.NET version is 1.1.
I get this error after completing this step:-
///////////////////////////////////////////////////////////////////
1. Create the following class:
public class WorkbookEngine
{
public static void CreateWorkbook(DataSet ds, String path)
{
XmlDataDocument xmlDataDoc = new XmlDataDocument(ds);
XslTransform xt = new XslTransform();
StreamReader reader =new StreamReader(typeof (WorkbookEngine).Assembly.GetManifestResourceStream(typeof (WorkbookEngine), “Excel.xsl”));
XmlTextReader xRdr = new XmlTextReader(reader);
xt.Load(xRdr, null, null);
StringWriter sw = new StringWriter();
xt.Transform(xmlDataDoc, null, sw, null);
StreamWriter myWriter = new StreamWriter (path + “\\Report.xls”);
myWriter.Write (sw.ToString());
myWriter.Close ();
}
}
2. In your main program/class use the function as
WorkbookEngine.CreateWorkbook(ds, path);
///////////////////////////////////////////////////////////////////
November 28, 2008 at 6:22 am
Hi Rashid, thanks for the code.. it works well
Just one problem for me. One of the ColumnName contains space character (eg “Column 1″). If it is transformed into Excel, the ColumnName will change to “Column__x0020_1″.
I’m trying to modify the Excel.xsl, but the problem still exist. If you know how to solve this, I will appreciate it..
thanks a lot!
March 5, 2009 at 10:26 am
arghhh i have a problem with the excel report.
i can’t get the simple way to make a report !!!
thx bro , ur sample make me more easier to understand about excel output
http://www.suryodesign.wordpress.com
March 6, 2009 at 4:38 pm
This sample does not WORK. no use
March 28, 2009 at 10:44 am
Can anyone please tell me from where I can find excel.xsl?
March 28, 2009 at 10:56 am
Well to solve this problem just add a xsl file Excel.xsl in this case to your project and change the propert bulid action to embed, The program will run in this case,,,But I am facing another problem after that, which is : The file created is not opening properly, it says, unable to read file..
Do anyone know the solution.
March 31, 2009 at 10:51 pm
Hi,
thanks for your example. This is working great.
Regards,
Alexander
April 28, 2009 at 8:45 am
What about if I want to export it to Excel 2007 xlsx document.
Because i have about 100,000 rows and excel 2003 supports only 64000 rows per sheet.
May 12, 2009 at 7:37 am
well, I’ve almost the same thing to ask what Udayantha has asked in the post above mine with a slight modification that I’m using VS.NET2008.
As I’ve tried the above code, first of all, in VS.NET2008 it says:
‘System.Xml.Xsl.XslTransform’ is obsolete: ‘This class has been deprecated. Please use System.Xml.Xsl.XslCompiledTransform instead.
and when I use System.Xml.Xsl.XslCompiledTransform as it says, then I cannot find the function xt.Transform(xmlDataDoc, null, sw, null) with the same type of arguments (xt is XslCompiledTransform xt = new XslCompiledTransform();)
2nd, I cannot add Excel.xsl in VS.NET2008 rather it only gives option to add *.xslt file
so when I still forcefully run the code with the deprecated one i.e. System.Xml.Xsl.XslTransform & Excel.xslt, then
it creates a file on hard disk with the name MyExcelFile.xls but that file doesn’t get opened in MS Excel. I tried to open it in Notepad++, there it shows all of the exported DataSet in clean xml format
can anyone help me here?
thanks
June 9, 2009 at 10:39 am
Ok, I tried to implement in a application I’m making VS.NET 2008 but it didn’t worked.
Got some of the above errors and a excel-file of 9 bytes.
BUT… with some luck and few wild guesses, I got it to work:
CHEERS!!!!!
public static void CreateWorkbook(DataSet ds, String path)
{
XmlDataDocument xmlDataDoc = new XmlDataDocument(ds);
XslCompiledTransform xt = new XslCompiledTransform();
StreamReader reader =new StreamReader(typeof (WorkbookEngine).Assembly.GetManifestResourceStream(typeof (WorkbookEngine), “Excel.xsl”));
XmlTextReader xRdr = new XmlTextReader(reader);
xt.Load(xRdr, null, null);
StringWriter sw = new StringWriter();
xt.Transform(xmlDataDoc, XmlWriter.Create(sw));
StreamWriter myWriter = new StreamWriter (path + “\\Report.xls”);
myWriter.Write (sw.ToString());
myWriter.Close ();
}
Take care!
Tachyon
May 25, 2009 at 12:28 pm
dfdfdfdfdfdfdffgf
May 29, 2009 at 11:37 am
If you still need a solution to your problems….try this link:
http://aspalliance.com/471_Convert_XML_To_an_Excel_Spreadsheet_Using_XSL.5
June 18, 2009 at 4:25 pm
Hi! I want to export my cutomized data report data to excel format. I am not using any data set / data grid/ data reader. I am working on NHibernate with C#.
here my records will be stored in one prpety bag and report header will be stored in another propety bag . please help me to complete this
June 22, 2009 at 1:10 pm
Hi ,
I m using above code but I m getting following error.
Error 3 The type or namespace name ‘WorkbookEngine’ could not be found (are you missing a using directive or an assembly reference?)
Can u help me.
Regards,
Pradeep
August 26, 2009 at 3:10 pm
it will be better if you include the namespaces, references list what should add to this.
September 14, 2009 at 3:57 pm
If you’re having trouoble with the Excel.xsl file, go to the original article here:
http://www.eggheadcafe.com/articles/20050404.asp
(as per the link in the page above) and download teh source – it contains the Excel.xsl file.
Hint – it isn’t blank
September 30, 2009 at 6:16 am
hello, your code works fine and it’s easy but he have tow BIG problem to use the data in excel :
1) ALL the data are in STRING, how to keep the type of the data ?
2) How to prevent the null values ?
Thanks