Export data from dataset to excel

Question

I am trying to export data from dataset to excel and save it directly to a given path without giving me the option to open,save or cancel.

Answer 1

This C# Excel library can also be used to export the dataset. More details about how to export can be found here.

ExcelDocument xls = new ExcelDocument();
xls.easy_WriteXLSFile_FromDataSet("ExcelFile.xls", dataset, 
                 new ExcelAutoFormat(Styles.AUTOFORMAT_EASYXLS1), "Sheet Name");

Answer 2

Hi i found a perfect solution Here

Just replace 'missing.value' with System.Type.Missing in the code. Also remove

oWB.Close(System.Type.Missing, System.Type.Missing, System.Type.Missing); and

oXL.Quit(); from the code. Otherwise your excel will get closed automatically as soon as it open.

Answer 3

Using ExcelLibrary this is a one liner ...

DataSet myDataSet;
... populate data set ...
ExcelLibrary.DataSetHelper.CreateWorkbook("MyExcelFile.xls", myDataSet);

See also Create Excel (.XLS and .XLSX) file from C#

Answer 4

It's not the greatest solution but here is what I did, it opens a new excel document then copies what is in the dataset, all you need to do is sort out the columns and save it.

Btw totes my first post to answer a question, hope it helps

        private void cmdExport_Click(object sender, EventArgs e)
        {
            System.Diagnostics.Process.Start("excel.exe");
            try
            {
                copyAlltoClipboard();
                Microsoft.Office.Interop.Excel.Application xlexcel;
                Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
                Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
                object misValue = System.Reflection.Missing.Value;
                xlexcel = new Excel.Application();
                xlexcel.Visible = true;
                xlWorkBook = xlexcel.Workbooks.Add(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
                CR.Select();
                xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error :" + ex.Message);
            }
        }
        private void copyAlltoClipboard()
        {
            dataGridViewItems.SelectAll();
            DataObject dataObj = dataGridViewItems.GetClipboardContent();
            if (dataObj != null)
                Clipboard.SetDataObject(dataObj);
        }

Answer 5

Check this DataSetToExcel

and c# (WinForms-App) export DataSet to Excel

In the first link change the code as follows:

Remove the all code that initially starts and try the following

using (StringWriter sw = new StringWriter("Your Path to save"))
{
  using (HtmlTextWriter htw = new HtmlTextWriter(sw))
  {
    // instantiate a datagrid
    DataGrid dg = new DataGrid();
    dg.DataSource = ds.Tables[0];
    dg.DataBind();
    dg.RenderControl(htw);
  }
}

Answer 6

Here's another C# library, which lets you export from a DataSet to an Excel 2007 .xlsx file, using the OpenXML libraries.

http://www.mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm

All of the source code is provided, free of charge, along with a demo application, and you can use this in your ASP.Net, WPF and WinForms applications.

Once you've added the class to your application, it just takes one function call to export your data into an Excel file.

CreateExcelFile.CreateExcelDocument(myDataSet, "C:\\Sample.xlsx");

It doesn't get much easier than that.

Good luck !