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.

guys i should not get the option to save the excel but it should get saved automatically at a location specified by me. — creator, Apr 27 '11 at 04:26
I've merged your unregistered accounts. You can now leave comments under answers, edit your question and accept an answer when the time comes to do that. — Tim Post, Apr 27 '11 at 07:33
See http://www.codeproject.com/KB/aspnet/coolcode2_aspx.aspx — Pasi, Apr 26 '11 at 12:43
I think it is not good to save the file in the path u specified what if the user does not have certain drive that you are saving in — Developer, Apr 27 '11 at 12:21

score 1 · Answer 1 · answered Jun 25 '14 at 11:23

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

score 1 · Answer 2 · answered May 28 '15 at 14:05

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

Where is the path that the OP wishes to save to ? Perhaps ask him for clarification on how he plans to specify this path and then expand on that. — OYRM, May 28 '15 at 14:13

score 1 · Answer 3 · edited Oct 07 '18 at 23:09

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

score 1 · Answer 4 · edited May 23 '17 at 11:46

1

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#

edited May 23 '17 at 11:46

Community

1
1

answered Oct 01 '11 at 02:35

Tony O'Hagan

21,638
3
67
78

1

I tried this.it was giving 'invalid cell value' exception.I do not see any active development on this – shreesha Mar 10 '17 at 11:46
@shreesha I wonder if you have an error in one or more of your cell formulas? – Tony O'Hagan Mar 16 '17 at 05:30
yes @tony probably.Otherwise your solution is perfect. – shreesha Mar 17 '17 at 08:29

score 0 · Answer 5 · answered Dec 06 '12 at 12:23

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.

score 0 · Answer 6 · answered Dec 05 '11 at 12:02

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 !

Export data from dataset to excel

6 Answers6

Linked