CODEWITHSUNDEEP
c#excelexport-to-excel
creator
creator

Reputation: 69

Export data from dataset to excel

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.

Upvotes: 6

Views: 82542

Answers (6)

Developer
Developer

Reputation: 8634

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

Upvotes: 1

Cytriax
Cytriax

Reputation: 11

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

Upvotes: 1

alex.pulver
alex.pulver

Reputation: 2125

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

Upvotes: 1

hitesh.gawhade
hitesh.gawhade

Reputation: 347

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.

Upvotes: 0

Mike Gledhill
Mike Gledhill

Reputation: 29161

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 !

Upvotes: 0

Tony O'Hagan
Tony O'Hagan

Reputation: 22692

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#

Upvotes: 1

Related Questions