Export DataSet to Excel in C#

May 14, 2007 — Muhammad Rashid Idris

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 🙂

Posted in ASP.NET, C#.NET & VB.NET. 113 Comments »

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

  1. Kabilan Jayaraman Says:
    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.

    Reply
    • Neji Says:
      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.

      Reply
  2. Muhammad Rashid Idris Says:
    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 🙂

    Reply
    • venkata sagar voonna Says:
      June 19, 2013 at 9:11 pm

      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

      Reply
    • john Says:
      October 6, 2013 at 1:52 pm

      i use this code it did work..

      Reply
  3. Kabilan Jayaraman Says:
    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.

    Reply
  4. Muhammad Rashid Idris Says:
    June 11, 2007 at 2:54 pm

    Dear Kabilan,

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

    —
    Keep Smiling 🙂

    Reply
  5. Kabilan Jayaraman Says:
    June 11, 2007 at 3:20 pm

    Dear Muhammad,

    C# 2.0
    VS 2005

    thanks

    Reply
  6. Joep Says:
    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

    Reply
    • Kailas Says:
      April 1, 2011 at 6:55 am

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

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

      Reply
  7. max Says:
    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

    Reply
  8. tammy Says:
    December 18, 2007 at 5:01 am

    why the watchs the result is null

    Reply
  9. Deon Says:
    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

    Reply
  10. gsa nkarar rao Says:
    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

    Reply
  11. Roberto Says:
    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?

    Reply
  12. feng Says:
    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

    Reply
  13. Wahid Says:
    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

    Reply
  14. aditya Says:
    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

    Reply
  15. Mario Says:
    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
    —

    Reply
  16. shlomi Says:
    July 15, 2008 at 8:04 am

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

    Thanks, shlomi.

    Reply
  17. Claire Says:
    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

    Reply
  18. hemant Says:
    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…………

    Reply
  19. Dinoop Says:
    September 30, 2008 at 11:02 am

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

    Reply
  20. Dinoop Says:
    September 30, 2008 at 11:06 am

    Sorry I cant…

    Reply
  21. Jegan Says:
    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”);

    Reply
  22. Neelakshi Joshi Says:
    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

    Reply
  23. Jaishree Singh Says:
    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);
    ///////////////////////////////////////////////////////////////////

    Reply
  24. Indra Says:
    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!

    Reply
  25. suryodesign Says:
    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

    Reply
  26. ScrewDriver Says:
    March 6, 2009 at 4:38 pm

    This sample does not WORK. no use

    Reply
  27. Soumiek Ranjan Says:
    March 28, 2009 at 10:44 am

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

    Reply
  28. Soumiek Ranjan Chakravarty Says:
    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.

    Reply
  29. Alexander Says:
    March 31, 2009 at 10:51 pm

    Hi,

    thanks for your example. This is working great.

    Regards,

    Alexander

    Reply
  30. Udayantha Says:
    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.

    Reply
  31. umartarar Says:
    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

    Reply
    • Tachyon Says:
      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

      Reply
  32. vbvbvb Says:
    May 25, 2009 at 12:28 pm

    dfdfdfdfdfdfdffgf

    Reply
  33. Ebbs Says:
    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

    Reply
  34. raju Says:
    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

    Reply
  35. Pradeep Says:
    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

    Reply
  36. Naren Says:
    August 26, 2009 at 3:10 pm

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

    Reply
  37. Jeff Says:
    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

    Reply
  38. Laurent Says:
    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

    Reply
  39. murali Says:
    January 16, 2010 at 4:44 am

    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

    Reply
    • Ben Says:
      January 28, 2010 at 5:26 pm

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

      Reply
      • gggamma Says:
        January 29, 2010 at 7:39 pm

        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:
        June 1, 2011 at 12:45 pm

        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:
    February 2, 2010 at 10:41 am

    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

    Reply
  41. Ivan Says:
    April 21, 2010 at 5:42 pm

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

    Reply
  42. Rajib Says:
    June 11, 2010 at 11:33 pm

    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();
    }

    Reply
  43. Ashok Says:
    August 4, 2010 at 8:45 am

    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?

    Reply
  44. Punit Rana Says:
    August 14, 2010 at 6:43 am

    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

    Reply
    • Vineetha Says:
      October 16, 2012 at 4:55 pm

      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?

      Reply
  45. Muthu Says:
    August 17, 2010 at 6:20 am

    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.

    Reply
  46. Muthu Says:
    August 17, 2010 at 6:21 am

    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.

    Reply
  47. muhammed Says:
    September 13, 2010 at 11:23 am

    how to create a Excel.xsl file in visual studio

    Reply
  48. Lizy Says:
    September 24, 2010 at 1:01 pm

    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

    Reply
    • kk Says:
      April 20, 2011 at 5:44 am

      Hi Lizy,
      how do we mention the sheet name when we are writing to .xls file

      Reply
    • Jesse Grillo Says:
      December 30, 2018 at 2:36 am

      There simply has to be a better solution then the ones you’ve laid out here. Up until right this very moment I have had a completely incorrect way of thinking about this sort of thing. Full disclosure, I should say that I used to work in this field. Got sucked into your write up forabout the last hour. A few of my classmates have been telling me about this site to me for some time now and I finally decided to give it a look.

      Reply
  49. Faz Says:
    November 12, 2010 at 12:14 pm

    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

    Reply
  50. Necklace Holder Says:
    November 17, 2010 at 8:31 pm

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

    Reply
  51. Danny Says:
    January 19, 2011 at 1:28 pm

    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?

    Reply
  52. gio Says:
    January 21, 2011 at 12:37 am

    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?

    Reply
  53. ch@n*83 Says:
    April 7, 2011 at 5:11 am

    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

    Reply
  54. lokesh Says:
    May 25, 2011 at 11:53 am

    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;
    }

    Reply
  55. Zeeshan Umar Says:
    July 8, 2011 at 12:09 am

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

    Export to Excel

    Reply
  56. Manigandan Says:
    July 18, 2011 at 5:57 pm

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

    Reply
  57. Manigandan Says:
    July 18, 2011 at 5:59 pm
    Reply
  58. Manigandan Says:
    July 18, 2011 at 6:05 pm

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

    Reply
  59. duks Says:
    September 7, 2011 at 8:15 am

    Thanks to lokesh

    Reply
  60. David Says:
    September 23, 2011 at 9:10 pm

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

    Reply
  61. fernando yevenes Says:
    November 2, 2011 at 3:18 pm

    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)

    Reply
  62. Akshay Says:
    January 13, 2012 at 1:24 pm

    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

    Reply
  63. Lana Says:
    February 23, 2012 at 6:08 pm

    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.

    Reply
  64. Vijaya Says:
    May 11, 2012 at 11:55 am

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

    Reply
  65. cccam Says:
    March 17, 2013 at 12:30 pm

    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.

    Reply
  66. Workers Injury Attorney Los Angeles Says:
    March 27, 2013 at 9:14 am

    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?

    Reply
  67. Sushil Dharmar Says:
    May 6, 2013 at 3:00 pm

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

    Reply
  68. Accelerate Eben Pagan Says:
    May 19, 2013 at 7:55 pm

    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!

    Reply
  69. celine box bag colours Says:
    June 29, 2013 at 5:56 pm

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

    Reply
  70. Manikanta Says:
    August 16, 2013 at 3:29 pm

    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,

    Reply
  71. Shona Says:
    January 25, 2014 at 5:39 am

    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

    Reply
  72. gta Says:
    July 15, 2014 at 1:23 pm

    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!

    Reply
  73. Edward Saverchenko Says:
    December 28, 2016 at 4:30 pm

    Thank you for sharing your great website.|

    Reply
  74. Shane Holmstead Says:
    January 8, 2017 at 11:00 pm

    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.

    Reply
  75. outdoor recreation valuation tool Says:
    February 4, 2017 at 9:56 am

    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.

    Reply
  76. Courtney Duriga Says:
    February 22, 2017 at 12:57 pm

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

    Reply
  77. investor adviser Says:
    August 30, 2017 at 9:45 pm

    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.

    Reply
  78. Jesse Grillo Says:
    September 2, 2017 at 12:01 am

    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?

    Reply
  79. entrepreneur adviser Says:
    September 2, 2017 at 11:30 pm

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

    Reply
  80. new business speaker Says:
    September 4, 2017 at 5:55 am

    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.

    Reply
  81. Luther Sedor Says:
    January 7, 2018 at 6:07 pm

    I was recommended this blog by way of my cousin. I am no longer certain whether or not this post is written by him as nobody else understand such unique approximately my difficulty. You are incredible! Thanks!

    Reply
  82. Generate Leads Says:
    February 21, 2018 at 11:11 pm

    Admiring the hard work you put into your site and in depth information you provide. It’s great to come across a blog every once in a while that isn’t the same outdated rehashed information. Wonderful read! I’ve bookmarked your site and I’m adding your RSS feeds to my Google account.

    Reply
  83. Sushil Agarwal Says:
    July 14, 2018 at 5:43 am

    // create the DataGrid and perform the databinding
    System.Web.UI.WebControls.DataGrid grid =
    new System.Web.UI.WebControls.DataGrid();
    grid.HeaderStyle.Font.Bold = true;
    grid.DataSource = ds;
    grid.DataMember = ds.Tables[“item”].ToString();

    grid.DataBind();
    // render the DataGrid control to a file
    Cursor.Current = Cursors.WaitCursor;
    using (StreamWriter sw = new StreamWriter(bindNavigator1.save2xlfile)) //saveFileDialog1.FileName
    {
    using (HtmlTextWriter hw = new HtmlTextWriter(sw))
    {
    grid.RenderControl(hw);
    }
    }
    Cursor.Current = Cursors.Default;

    Reply
  84. Sushil Agarwal Says:
    July 14, 2018 at 12:14 pm

    try
    {
    //https://stackoverflow.com/questions/12201627/whats-the-non-deprecated-alternative-to-xmldatadocument-and-xsltransform
    XDocument doc = new XDocument();
    using (XmlWriter xw = doc.CreateWriter())
    {
    ds.WriteXml(xw);
    xw.Close();
    }

    XslCompiledTransform proc = new XslCompiledTransform();

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

    using (XmlReader xr = XmlReader.Create(reader)) //myMemoryStream
    {
    proc.Load(xr);
    }

    string result;

    using (StringWriter sw = new StringWriter())
    {
    proc.Transform(doc.CreateNavigator(), null, sw); // needs using System.Xml.XPath;
    result = sw.ToString();
    }

    //option 2
    // *http://mytipsfordotnet.blogspot.com/2010/10/format-excel-file-using-xslt.html
    //XmlDataDocument xmlDoc = new XmlDataDocument();
    //xmlDoc.LoadXml(ds.GetXml());
    //XslCompiledTransform xsl = new XslCompiledTransform();
    //xsl.Load(“Excel.xsl”);
    //StreamWriter strWriter = new StreamWriter(pathToSave);
    //xsl.Transform(xmlDoc, null, strWriter);
    //strWriter.Flush();
    }
    catch (Exception ex)
    {
    throw ex;
    }

    Reply
  85. qsr franchise opportunities Says:
    July 20, 2018 at 10:04 pm

    I am really enjoying the theme/design of your web site. Do you ever run into any internet browser compatibility issues? A number of my blog audience have complained about my site not working correctly in Explorer but looks great in Chrome. Do you have any recommendations to help fix this problem?

    Reply
  86. iptv list Says:
    August 29, 2018 at 6:52 am

    bonjour
    Nice share.
    I consider the great cccam servers are those from Fishbone cccam
    I would like to see more posts like this
    Thanks

    Reply
  87. food franchise opportunities Says:
    March 3, 2019 at 8:48 pm

    I’m really enjoying the theme/design of your web site. Do you ever run into any browser compatibility issues? A small number of my blog visitors have complained about my blog not operating correctly in Explorer but looks great in Firefox. Do you have any recommendations to help fix this problem?

    Reply
  88. preschool franchise opportunities Says:
    March 11, 2019 at 3:54 pm

    We absolutely love your blog and find almost all of your post’s to be what precisely I’m looking for. Do you offer guest writers to write content to suit your needs? I wouldn’t mind creating a post or elaborating on a number of the subjects you write in relation to here. Again, awesome website!

    Reply
  89. 4fmarysteenburgennudezn.tumblr.com Says:
    October 17, 2020 at 11:18 pm

    I enjoyed going to your webiste. I rarely leave comments, but
    you definately up deserve a thumbs!

    Reply

Leave a comment

«

»