将数据从数据集导出到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.

我正在尝试将数据从数据集导出到Excel并将其直接保存到给定路径，而不提供打开，保存或取消选项。

7 个解决方案

#1

Check this DataSetToExcel

检查此DataSetToExcel

c# (WinForms-App) export DataSet to Excel

c＃（WinForms-App）将DataSet导出到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);
   }
 }

#2

Using ExcelLibrary this is a one liner ...

使用ExcelLibrary这是一个单行...

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

另请参阅从C＃创建Excel（.XLS和.XLSX）文件

#3

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

此C＃Excel库也可用于导出数据集。有关如何导出的更多详细信息，请访问此处。

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

#4

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

这是另一个C＃库，它允许您使用OpenXML库从DataSet导出到Excel 2007 .xlsx文件。

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.

所有源代码都是免费提供的，还有一个演示应用程序，您可以在ASP.Net，WPF和WinForms应用程序中使用它。

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

将类添加到应用程序后，只需一次函数调用即可将数据导出到Excel文件中。

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

It doesn't get much easier than that.

它并没有那么容易。

Good luck !

祝你好运！

#5

Hi i found a perfect solution Here

嗨，我在这里找到了一个完美的解

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

只需用代码中的System.Type.Missing替换'missing.value'即可。也删除

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

oWB.Close（System.Type.Missing，System.Type.Missing，System.Type.Missing）;和

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

oXL.Quit（）;从代码。否则，您的excel会在打开后自动关闭。

#6

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.

这不是最好的解决方案，但这是我所做的，它打开一个新的Excel文档，然后复制数据集中的内容，您需要做的就是整理列并保存它。

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

#7

-1

This is not an answer to this question.just for those who quickly want to convert their xml file to excel file and come here looking for a solution.

这不是这个问题的答案。只是那些想快速将他们的xml文件转换为excel文件并来到这里寻找解决方案的人。

condition:you must be using visual studio.

条件：你必须使用visual studio。

Steps to follow:

要遵循的步骤：

create a console application.
创建控制台应用程序。
Add below code to your Main method.

将以下代码添加到Main方法中。

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

string filePath = @“PATH TO XML FILE”; DataSet ds = new DataSet（）; ds.ReadXml（文件路径）;
Put a breakpoint on this line

在这一行上设一个断点

ds.ReadXml(filePath);

ds.ReadXml（文件路径）;
Run the application in debug mode

在调试模式下运行应用程序
Once the breakpoint is hit, go to next line.now the data will be populated in DataSet.

一旦断点被击中，转到下一行。现在数据将填充在DataSet中。
Open DataSet in DataSet Visualizer as shown below.

在DataSet Visualizer中打开DataSet，如下所示。
Done! copy the data to excel file.

完成！将数据复制到excel文件。

#1