Export DataSet to Excel in C#

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 🙂

Advertisements

103 Responses to “Export DataSet to Excel in C#”

  1. Kabilan Jayaraman Says:

    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.

    • Neji Says:

      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.

  2. Muhammad Rashid Idris Says:

    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 🙂

    • venkata sagar voonna Says:

      While trying to use program, i am not able to make use of “xmlDataDocumnet”. I am not able to instantiate it. I am importing all the 4 below:
      using System.Data;
      using System.IO;
      using System.Xml;
      using System.Xml.Xsl;

      Am i Missing any import? Please let me know the .net Framework that supports it?

      Regards,
      Sagar

    • john Says:

      i use this code it did work..

  3. Kabilan Jayaraman Says:

    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.

  4. Muhammad Rashid Idris Says:

    Dear Kabilan,

    Tell me exactly which version of C# and VS you are using?


    Keep Smiling 🙂

  5. Kabilan Jayaraman Says:

    Dear Muhammad,

    C# 2.0
    VS 2005

    thanks

  6. Joep Says:

    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

    • Kailas Says:

      But what is exact use of “Excel.xsl” what it contains ???

      Coult you please help me. I have added blank Excel.xls file ?

  7. max Says:

    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

  8. tammy Says:

    why the watchs the result is null

  9. Deon Says:

    Anyone got this problem solved that Kaliban has? I get the same error using VS 2005 Express C#.
    Please help

  10. gsa nkarar rao Says:

    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

  11. Roberto Says:

    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?

  12. feng Says:

    you need to include below file to your project and set the property build action to embed.
    // Excel.xsl

  13. Wahid Says:

    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

  14. aditya Says:

    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

  15. Mario Says:

    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

  16. shlomi Says:

    Does someone resolved this problem?
    If do can you please advice.

    Thanks, shlomi.

  17. Claire Says:

    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

  18. hemant Says:

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

  19. Dinoop Says:

    I can understand, what is WorkbookEngine,,
    I got error on that stage
    pls help me

  20. Dinoop Says:

    Sorry I cant…

  21. Jegan Says:

    Hey.. WorkbookEngine is the class name I guess….

    Just no need to put that..
    It’s enough..
    StreamReader reader = new StreamReader(“Excel.xsl”);

  22. Neelakshi Joshi Says:

    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

  23. Jaishree Singh Says:

    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);
    ///////////////////////////////////////////////////////////////////

  24. Indra Says:

    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!

  25. suryodesign Says:

    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

  26. ScrewDriver Says:

    This sample does not WORK. no use

  27. Soumiek Ranjan Says:

    Can anyone please tell me from where I can find excel.xsl?

  28. Soumiek Ranjan Chakravarty Says:

    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.

  29. Alexander Says:

    Hi,

    thanks for your example. This is working great.

    Regards,

    Alexander

  30. Udayantha Says:

    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.

  31. umartarar Says:

    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

    • Tachyon Says:

      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

  32. vbvbvb Says:

    dfdfdfdfdfdfdffgf

  33. Ebbs Says:

    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

  34. raju Says:

    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

  35. Pradeep Says:

    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

  36. Naren Says:

    it will be better if you include the namespaces, references list what should add to this.

  37. Jeff Says:

    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

  38. Laurent Says:

    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

  39. murali Says:

    public class WorkbookEngine
    {
    //public WorkbookEngine()
    //{
    // //
    // // TODO: Add constructor logic here
    // //
    //}

    public static void CreateWorkbook(DataSet ds, String path1)
    {
    XmlDataDocument xmlDataDoc = new XmlDataDocument(ds);
    XslCompiledTransform xt = new XslCompiledTransform();
    // XslTransform xt = new XslTransform();

    StreamReader reader =new StreamReader(typeof (WorkbookEngine).Assembly.GetManifestResourceStream(typeof (WorkbookEngine), “Excel.xsl”));
    // StreamReader reader = new StreamReader(“Excel.xsl”);

    XmlTextReader xRdr = new XmlTextReader(reader);
    xt.Load(xRdr, null, null);
    StringWriter sw = new StringWriter();
    xt.Transform(xmlDataDoc, XmlWriter.Create(sw));
    //xt.Transform(xmlDataDoc, null, sw, null);
    StreamWriter myWriter = new StreamWriter (path1 + “\\Report.xls”);
    myWriter.Write (sw.ToString());
    myWriter.Close ();
    }

    }

    ————
    IN THIS LINE
    StreamReader reader = new StreamReader(typeof(WorkbookEngine).Assembly.GetManifestResourceStream(typeof(WorkbookEngine), “Excel.xsl”));

    AM GETTING THIS ERROR

    Value cannot be null.
    Parameter name: stream

    HOW TO SOLVE TIZ

    THANKS IN ADVANCE

    • Ben Says:

      To everyone having a problem with this line:

      StreamReader reader = new StreamReader(typeof(WorkbookEngine).Assembly.GetManifestResourceStream(typeof(WorkbookEngine), “Excel.xsl”));

      1. Go to the properties of the Excel.xsl file
      2. Change the “Build Action” property to “Embedded Resource”

      If that doesn’t work, use the following line of code in addition to the property changes

      StreamReader reader = new StreamReader(Assembly.GetExecutingAssembly().GetManifestResourceStream(“[YourNameSpace].Resources.Excel.xsl”));

      • gggamma Says:

        the ‘GetExecutingAssembly’ workaround solved the problem for me – thanks! Another issue I’m having is that the generated .xls spreadsheets don’t seem to work and play well with Excel 2007…get a warning msg stating “The file you are tyring to open, ‘[filename].xls’, is in a defferent format than specified by the file extension. Verify that the file is not corrupt and is from a trusted source before opening the file…” I’m assuming something needs to be tweaked with the xsl transformation file, Excel.xsl. Any ideas?

      • Chirag Says:

        Hey I m Using Visual Studio 2008 and there is nothing like .xsl file in it so how can i add it to my project..??

  40. Mattia Says:

    Hi,
    i’m using your code and i can succesful generate the excel file, but when I open it, i find a list of xml fields and not the dataset trasport into rows and columns correctly!

    Can you help me? where I have to modify the code? Do I need to modify the excel.xsl file? I think that excel doesn’t “understand” the content of xsl file..

    Thanks
    Mattia

  41. Ivan Says:

    Well, for all You who have a problem with Excel.xsl, the problem is in xSl, it shoud be Excel.xLs….

  42. Rajib Says:

    I spent about 2 hours trying to figure this out. Finally got it working. So thought I would try to help those out with the same problem. If you are using VS 2008, you might be having trouble.

    First, the article doesn’t tell you how to add Excel.xsl to your project. Also, Excel.xsl cannot be an empty file. Best thing to do is to go to the original article, download the code files, copy Excel.xsl from there and paste it in your project folder. Then, add this file to your project by using Add Existing Item (right click on solution). Also, change build to embedded resource as others suggested. This should take care of your new stream blah blah error.

    If you are getting an error on line, xt.Load(xRdr, null, null); then you are probably not calling your static method from teh right place. Below is my code.

    //This fills up my dataset
    public void GetData()
    {
    path = “C:\\Documents and Settings\\……”;
    con = new SqlConnection();
    ds1 = new DataSet();
    con.ConnectionString = “[your connection string here]”;
    string sqlString = “[your sql string here]”;
    da = new SqlDataAdapter(sqlString, con);
    da.Fill(ds1, “Customers”);
    }

    public static void CreateWorkbook(DataSet ds1, String path)
    {

    XmlDataDocument xmlDataDoc = new XmlDataDocument(ds1);
    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 ();
    }

    //Finally first call the GetData method and then the CreateWorkbook method

    public Form1()
    {
    InitializeComponent();
    GetData();
    WorkBookEngine.CreateWorkbook(ds1, path);
    Application.Exit();
    }

  43. Ashok Says:

    I am getting a warning message from excel
    ‘The file you are trying to open ‘Default.xls’, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?’

    How can i avoid this message?

  44. Punit Rana Says:

    I am also having the same problem pls reply if anyone finds its solution getting an error message
    “The file you are trying to open ‘Default.xls’, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?”
    while opening dataset into excel

    • Vineetha Says:

      Hi,

      I was able to create the excel file. However when I try to open it I get the error message “The file you are tyring to open, ‘[filename].xls’, is in a defferent format than specified by the file extension. Verify that the file is not corrupt and is from a trusted source before opening the file…” .DId you find any solution for this?

  45. Muthu Says:

    Hi,
    In Excel 2003 it is atleast opening with the above error message. But if i save as Excel 2007 then it is not even opeing. Any wat to save as Excel 2007 using this XSL since my record count may exceed 65,000 mark.

  46. Muthu Says:

    Hi,
    In Excel 2003 it is atleast opening with the above error message. But if i save as Excel 2007 then it is not even opeing. Any wat to save as Excel 2007 using this XSL since my record count may exceed 65,000 mark.

  47. muhammed Says:

    how to create a Excel.xsl file in visual studio

  48. Lizy Says:

    Quick & simple way:

    DataSet ds = GetData();// ds here has 3 columns
    String[] a=new String[ds.Tables[0].Rows.Count];
    foreach (DataRow dr in ds.Tables[0].Rows)
    {
    a[i] = dr.ItemArray.GetValue(0).ToString();
    a[i] += “\t”+dr.ItemArray.GetValue(1).ToString();
    a[i] += “\t” + dr.ItemArray.GetValue(2).ToString();
    i++;
    }
    File.WriteAllLines(FileName.xls, a);

    Note:
    Filename should have the extension xsl to save as an excel file

  49. Faz Says:

    Hi All

    I had a problem in exporting to excel 2003,its getting opened in excel 2007,but not getting open in excel 2003.can anyone please help.
    am using VS 2008
    asp.net version 3.5

    Thank You

  50. Necklace Holder Says:

    i use both open office and microsoft office and i would say that microsoft office is more responsive and user friendly ,~:

  51. Danny Says:

    Hi all,
    I tried the code which is mentioned above. I found some incomplete information that I feel I cam complete.
    StreamReader reader =new StreamReader(typeof (WorkbookEngine).Assembly.GetManifestResourceStream(typeof (WorkbookEngine), “Excel.xsl”)); This piece of code doesnot show how to create Excel.xsl.

    Steps to create Excel.xsl
    1. Right click on the project
    2. Add new item
    3. Select XSLTFile1.xslt
    4. Rename the file Excel.xsl
    5. Now right click on Excel.xsl file and select properties.
    6. There in Build Action-> select embed resource

    Now the query is I did it and still I am getting ArgumentNullException. Can anybody help me out Why is it so?
    Do we have to add anything in Excel.xsl file?

  52. gio Says:

    Thanks for the article and thanks to all the clarification. I still have the problem opening the file though. Excel says it’s not properly formatted or the extension is incorrect. I use both xls and xlsx. Is there anyone here who successfully opened a file?

  53. ch@n*83 Says:

    PLz replace below line
    StreamReader reader =new StreamReader(typeof (WorkbookEngine).Assembly.GetManifestResourceStream(typeof (WorkbookEngine), “Excel.xsl”));

    With
    StreamReader reader =new StreamReader( “Excel.xsl”));

    it works
    BEST OF LUCK lol

  54. lokesh Says:

    Below example gets data from Oracle server into dataset and export to excel from dataset

    add refrence from com tab
    1. microsoft excel library 12.0

    Name Spaces to be used

    using System.Data.OleDb;
    using Microsoft.Office.Interop.Excel;
    using Microsoft.Office.Core;

    private void button4_Click(object sender, EventArgs e)
    {
    OleDbConnection bosscon = new OleDbConnection(“Provider=MSDAORA.1;User ID=123;Data Source=it;Password=123;Load Balancing=True;”);
    OleDbCommand bosscmd = new OleDbCommand();
    string path=”c:\\”;
    st= “select * from table”;

    bosscon.Open();
    if (bosscon.State == ConnectionState.Open)
    {
    MessageBox.Show(“connection opened”);

    try
    {

    OleDbDataAdapter oAdapter = new OleDbDataAdapter(st, bosscon);

    DataSet ds = new DataSet();

    oAdapter.Fill(ds);

    CreateWorkbook(ds,path);

    }
    catch (Exception ex)
    {

    MessageBox.Show(ex.Message);
    }
    }
    else
    {
    MessageBox.Show(“Connectioned failed”);

    }
    }

    public static void CreateWorkbook(DataSet ds, String path)
    {

    int rowindex=0;
    int columnindex=0;

    Microsoft.Office.Interop.Excel.Application wapp;
    Microsoft.Office.Interop.Excel.Worksheet wsheet;
    Microsoft.Office.Interop.Excel.Workbook wbook;
    wapp = new Microsoft.Office.Interop.Excel.Application();
    wapp.Visible = false;
    wbook = wapp.Workbooks.Add(true);
    wsheet = (Worksheet)wbook.ActiveSheet;
    try
    {

    for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
    {
    wsheet.Cells[1, i + 1] = ds.Tables[0].Columns[i].ColumnName;

    }

    foreach (DataRow row in ds.Tables[0].Rows)
    {
    rowindex++;
    columnindex = 0;
    foreach (DataColumn col in ds.Tables[0].Columns)
    {
    columnindex++;
    wsheet.Cells[rowindex + 1, columnindex] = row[col.ColumnName];
    }
    }

    wapp.Visible = true;
    }
    catch (Exception ex1)
    {
    MessageBox.Show(ex1.Message);
    }
    wapp.UserControl = true;
    }

  55. Zeeshan Umar Says:

    Nice article, thanks for sharing. Also have a look at this article, it generates excel 2007 based reports:-

    Export to Excel

  56. Manigandan Says:

    Thanks to Ebbs. It works very wells. XSL file’s content should be,

  57. Manigandan Says:
  58. Manigandan Says:

    Sorry guys, When I paste the xsl content , it just disappears.
    Another easy way is to use an open source.
    1.Download the dll from http://code.google.com/p/excellibrary/downloads/list
    2.Add it your project
    3.call the function like, ExcelLibrary.DataSetHelper.CreateWorkbook(fileName, dataset);

  59. duks Says:

    Thanks to lokesh

  60. David Says:

    Where the hell is “Excel.xsl” file???

  61. fernando yevenes Says:

    http://msdn.microsoft.com/es-es/library/system.io.textreader(v=VS.80).aspx

    al parecer el problema es el framework 2 y 3.5

    ya que este es para framework 4
    TextReader (Clase)

  62. Akshay Says:

    For all those who are hitting their heads on the desk looking for Excel.xsl file, goto Notepad—>File–>save as —> Excel.xsl (Include this file in your solution)
    Thanks.
    Regards,
    Akshay

  63. Lana Says:

    public static void CreateWorkbook(DataSet ds, String filepathname)
    {
    XmlDataDocument xmlDataDoc = new XmlDataDocument(ds);
    //System.Xml.Xsl.XslCompiledTransform xt = new XslCompiledTransform();
    XslTransform xt = new XslTransform();
    StreamReader reader =new StreamReader( “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(filepathname);
    myWriter.Write(sw.ToString());
    myWriter.Close();
    }
    ——————–
    the above code works with the “copy to build folder” property attached to Excel.xsl file.

  64. Vijaya Says:

    Please note the file name is wrongly mentioned in the sample everywhere it is “Excel.xsl” instead of “Excel.xls”
    Correct that and use the code 🙂
    StreamReader reader =new StreamReader( “Excel.xsl”);

  65. cccam Says:

    You’ve made some good points there. I looked on the internet for more information about the issue and found most individuals will go along with your views on this website.

  66. Workers Injury Attorney Los Angeles Says:

    At this time it looks like WordPress is the top blogging platform available right now.
    (from what I’ve read) Is that what you’re using on your
    blog?

  67. Sushil Dharmar Says:

    Nice one lokesh.
    very useful thanks for sharing…!!!!

  68. Accelerate Eben Pagan Says:

    After I initially commented I seem to have clicked on the -Notify me when new comments are added- checkbox and from now on every time a comment
    is added I receive four emails with the exact same comment.
    Is there an easy method you can remove me from that service?
    Appreciate it!

  69. celine box bag colours Says:

    Dead composed content, appreciate it for information. “Necessity is the mother of taking chances.” by Mark Twain.

  70. Manikanta Says:

    Hi, Thanks for the article.

    I have a sample templete wher the datatable/dataset to be copied into an excel.

    While copyin the data to excel it is overwriting Heading or other data in template.

    So please help me in copying the data to excel by skipping some rows in the top of excel(Used as Headings for columns) and also help me in avoiding of copying the header of data set(Which consits of cloumn name).

    Thanks in advance,

  71. Shona Says:

    Seems like u really fully understand quite a bit with regards to this subject and this shows by means of this
    specific blog, named “Export DataSet to Excel in C# | Rashid’s”.

    Many thanks ,Julienne

  72. gta Says:

    Hey! I could have sworn I’ve been to this blog before
    but after checking through some of the post I realized it’s
    new to me. Anyhow, I’m definitely happy I found it and I’ll be book-marking and checking back frequently!

  73. Edward Saverchenko Says:

    Thank you for sharing your great website.|

  74. Shane Holmstead Says:

    I do trust all the ideas you’ve introduced for your post. They are very convincing and will certainly work. Still, the posts are very quick for beginners. Could you please extend them a little from subsequent time? Thanks for the post.

  75. outdoor recreation valuation tool Says:

    Fingers down, Apple’s app shop wins by a mile. It is really a substantial determination of all kinds of apps vs a pretty unsatisfied choice of a handful for Zune. Microsoft is made up of applications, primarily within just the realm of game titles, nevertheless I am not certainly I’d require towards bet upon the foreseeable future if this function is substantial in the direction of your self. The iPod is a substantially superior alternative in just that case.

  76. Courtney Duriga Says:

    I adore this website – its so usefull and helpfull.|

  77. investor adviser Says:

    I bet you make babies smile. Ever been to Nevada? You have made my day! Really Cool! I am bored to death at work so I decided to browse your post on my iphone during lunch break.

  78. Jesse Grillo Says:

    I really like your writing style. I am hoping you write once more very soon! My roomates trying to discover more on this field. Do you have any video of that?

  79. entrepreneur adviser Says:

    I really like your writing style and how well you express your thoughts. Somehow you make time stop and fly at the same time.

  80. new business speaker Says:

    My bestie enjoys your blogs. This is so helpful! After looking over a number of the blog posts on your blog, I seriously like your way of blogging. I simply must tell you that your articles are totally awesome.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: