locked
Export Dataset into Excel Sheet in C# RRS feed

  • Question

  • Hi ,

    How to export data set into a new excel sheet in C#?

    Can you please help me?

    Thursday, October 20, 2011 8:20 AM

Answers

  • Hi Dhanalakshmi Periasamy,

     

    Welcome to the MSDN forum.

     

    There are many ways to export Dataset into Excel.

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

    You have to pass it the DataSet to export and path to where file should be generated.

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

    The links below contain more information that may help you:

    http://itsrashid.wordpress.com/2007/05/14/export-dataset-to-excel-in-c/

    http://social.msdn.microsoft.com/Forums/en-US/csharplanguage/thread/b5376135-75e3-4960-990b-ad16013561f1

     

    Have a nice day.


    Bob Shen [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Bob Shen Monday, October 31, 2011 2:01 AM
    Monday, October 24, 2011 9:46 AM
  • Welcome to MSDN Forums.

    Please see if http://www.codeproject.com/KB/office/datasettoexcel.aspx addresses your issue too.

    Sincerely Yours,

    Yasser.

    • Marked as answer by Bob Shen Monday, October 31, 2011 2:01 AM
    Thursday, October 20, 2011 8:43 AM

All replies

  • Welcome to MSDN Forums.

    Please see if http://www.codeproject.com/KB/office/datasettoexcel.aspx addresses your issue too.

    Sincerely Yours,

    Yasser.

    • Marked as answer by Bob Shen Monday, October 31, 2011 2:01 AM
    Thursday, October 20, 2011 8:43 AM
  • Hi Dhanalakshmi Periasamy,

     

    Welcome to the MSDN forum.

     

    There are many ways to export Dataset into Excel.

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

    You have to pass it the DataSet to export and path to where file should be generated.

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

    The links below contain more information that may help you:

    http://itsrashid.wordpress.com/2007/05/14/export-dataset-to-excel-in-c/

    http://social.msdn.microsoft.com/Forums/en-US/csharplanguage/thread/b5376135-75e3-4960-990b-ad16013561f1

     

    Have a nice day.


    Bob Shen [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Bob Shen Monday, October 31, 2011 2:01 AM
    Monday, October 24, 2011 9:46 AM
  • Hi Dhanalakshmi Periasamy,

    Do you have any updates about the issue? Please reply to let us know if this issue has been resolved.


    Bob Shen [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, October 26, 2011 2:43 AM
  • Hello,

    using this Excel C# component you can easily export DataSet to Excel file like this:

    // Create new Excel file.
    var excelFile = new ExcelFile();
    
    // Foreach DataTable, add new worksheet and insert data from DataTable into it.
    foreach (DataTable dataTable in dataSet.Tables)
    	excelFile.Worksheets.Add(dataTable.TableName).InsertDataTable(dataTable, 0, 0, true);
    
    // Save Excel file to XLS format.
    excelFile.SaveXls(dataSet.DataSetName + ".xls");

    Friday, May 11, 2012 10:20 AM
  • hi bob,

    i got an error...

    ArgumentNullException was Unhandled

    Value cannot be null.
    Parameter name: stream

     thanks,

    sivasreenath

    Thursday, January 10, 2013 9:10 AM
  • hi bob,

    i got an error...

    ArgumentNullException was Unhandled

    Value cannot be null.
    Parameter name: stream

     thanks,

    sivasreenath

    Same problem here...the reason is that we don't have Excel.xsl which is null. I found code for that file here:

    http://zanymuse.wordpress.com/2012/05/23/create-an-excel-from-a-dataset/

    It says to create a file called Excel.xsl, paste code from that page into it and save the file in the same directory as your solution.

    Thursday, March 27, 2014 5:37 PM
  • Another option is to use this .NET Excel component with this code:

    // Create an instance of the object that exports Excel files
    ExcelDocument xls = new ExcelDocument();

    // Export dataset to Excel file xls.easy_WriteXLSFile_FromDataSet("DataSet to Excel.xls",

    dataset, new ExcelAutoFormat(Styles.AUTOFORMAT_EASYXLS1), "DataSet");

    You can find the entire sample at this link:

    http://www.easyxls.com/manual/basics/export-dataset-to-excel.html

    Wednesday, June 25, 2014 10:07 AM
  •                 MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\csharp.net-informations.xls';Extended Properties=Excel 8.0;");
                     MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
                     MyCommand.TableMappings.Add("Table", "TestTable");
                     DtSet = new System.Data.DataSet();
                     MyCommand.Fill(DtSet);

    Dell


    • Edited by DellMerca Monday, October 20, 2014 6:43 AM NA
    Monday, October 20, 2014 6:42 AM
  • Hi, This article gives sample of how to export Datatable to Excel through DataGridView, through DataGridView users can preview and modify data information before exporting. Hope it also work in your situation.

    Regards
    Thursday, October 23, 2014 2:59 AM
  • Hi I got an error argument null exception was handled. Value can not be null Parameter name: stream Please help me....
    Sunday, July 12, 2015 4:59 AM