Export data from a dataset to Excel

Question

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

Answer 1

Check DataSetToExcel

C # (WinForms-App) exports DataSet to Excel

In the first link, change the code as follows

Remove all source code 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 2

Using ExcelLibrary , this is one liner ...

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

See also Create Excel File (.XLS and .XLSX) with C #

Answer 3

This C # Excel library can also be used to export a dataset. More information on 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 4

Here's another C # library that allows you to export data from a DataSet to an Excel.xlsx file using OpenXML libraries.

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

All source code is free, as well as a demo application, and you can use it in your ASP.Net, WPF, and WinForms applications.

Once you have added the class to your application, it just makes one function call to export your data to an Excel file.

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

It is not much easier.

Good luck

Answer 5

Hi i found the perfect solution here

Just replace "missing.value" with System.Type.Missing in your code. Also remove

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

oXL.Quit (); from code. Otherwise, your advantage will be closed automatically as soon as it opens.

Answer 6

This is not the best solution, but here is what I did, it opens a new excel document and copies what is in the data set, all you have to do is sort the columns and save it.

Btw is posting his 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 7

This is not the answer to this question. Just for those who quickly want to convert their XML file to an excel file and come here to look for a solution.

condition: you must use a visual studio.

Actions:

• create a console application.

• Add the code below to your main method.

  string filePath = @ "PATH TO XML FILE"; DataSet ds = new DataSet (); ds.ReadXml (Filepath);

• Put a breakpoint on this line

  ds.ReadXml (Filepath);

• Run the application in debug mode

• Once the breakpoint is deleted, continue to the next line. Now the data will be populated in the DataSet.

• Open the DataSet in the Dataizer Visualizer as shown below.

• Done! copy the data to an excel file.