c# (WinForms-App) 将数据集导出到 Excel

c# (WinForms-App) export DataSet to Excel

提问by maveonair

I need a solution to export a dataset to an excel file without any asp code (HttpResonpsne...) but i did not find a good example to do this...

我需要一个解决方案来将数据集导出到一个没有任何 asp 代码的 excel 文件（HttpResonpsne ...），但我没有找到一个很好的例子来做到这一点......

Best thanks in advance

提前致谢

采纳答案by lc.

I've created a class that exports a DataGridViewor DataTableto an Excel file. You can probably change it a bit to make it use your DataSetinstead (iterating through the DataTablesin it). It also does some basic formatting which you could also extend.

我创建了一个将DataGridView或导出DataTable到 Excel 文件的类。您可能可以稍微更改它以使其DataSet改为使用您的（DataTables在其中迭代）。它还执行一些您也可以扩展的基本格式。

To use it, simply call ExcelExport, and specify a filename and whether to open the file automatically or not after exporting. I also could have made them extension methods, but I didn't. Feel free to.

要使用它，只需调用 ExcelExport，并指定文件名以及导出后是否自动打开文件。我也可以让它们扩展方法，但我没有。随意地。

Note that Excel files can be saved as a glorified XML document and this makes use of that.

请注意，Excel 文件可以保存为美化的 XML 文档，这将利用它。

EDIT: This used to use a vanilla StreamWriter, but as pointed out, things would not be escaped correctly in many cases. Now it uses a XmlWriter, which will do the escaping for you.

编辑：这曾经使用 vanilla StreamWriter，但正如所指出的那样，在许多情况下，事情不会被正确转义。现在它使用 a XmlWriter，它将为您进行转义。

The ExcelWriterclass wraps an XmlWriter. I haven't bothered, but you might want to do a bit more error checking to make sure you can't write cell data before starting a row, and such. The code is below.

本ExcelWriter类包装的XmlWriter。我没有打扰，但您可能想要做更多的错误检查，以确保您不能在开始一行之前写入单元格数据，等等。代码如下。

public class ExcelWriter : IDisposable
{
    private XmlWriter _writer;

    public enum CellStyle { General, Number, Currency, DateTime, ShortDate };

    public void WriteStartDocument()
    {
        if (_writer == null) throw new InvalidOperationException("Cannot write after closing.");

        _writer.WriteProcessingInstruction("mso-application", "progid=\"Excel.Sheet\"");
        _writer.WriteStartElement("ss", "Workbook", "urn:schemas-microsoft-com:office:spreadsheet");
         WriteExcelStyles();
   }

    public void WriteEndDocument()
    {
        if (_writer == null) throw new InvalidOperationException("Cannot write after closing.");

        _writer.WriteEndElement();
    }

    private void WriteExcelStyleElement(CellStyle style)
    {
        _writer.WriteStartElement("Style", "urn:schemas-microsoft-com:office:spreadsheet");
        _writer.WriteAttributeString("ID", "urn:schemas-microsoft-com:office:spreadsheet", style.ToString());
        _writer.WriteEndElement();
    }

    private void WriteExcelStyleElement(CellStyle style, string NumberFormat)
    {
        _writer.WriteStartElement("Style", "urn:schemas-microsoft-com:office:spreadsheet");

        _writer.WriteAttributeString("ID", "urn:schemas-microsoft-com:office:spreadsheet", style.ToString());
        _writer.WriteStartElement("NumberFormat", "urn:schemas-microsoft-com:office:spreadsheet");
        _writer.WriteAttributeString("Format", "urn:schemas-microsoft-com:office:spreadsheet", NumberFormat);
        _writer.WriteEndElement();

        _writer.WriteEndElement();

    }

    private void WriteExcelStyles()
    {
        _writer.WriteStartElement("Styles", "urn:schemas-microsoft-com:office:spreadsheet");

        WriteExcelStyleElement(CellStyle.General);
        WriteExcelStyleElement(CellStyle.Number, "General Number");
        WriteExcelStyleElement(CellStyle.DateTime, "General Date");
        WriteExcelStyleElement(CellStyle.Currency, "Currency");
        WriteExcelStyleElement(CellStyle.ShortDate, "Short Date");

        _writer.WriteEndElement();
    }

    public void WriteStartWorksheet(string name)
    {
        if (_writer == null) throw new InvalidOperationException("Cannot write after closing.");

        _writer.WriteStartElement("Worksheet", "urn:schemas-microsoft-com:office:spreadsheet");
        _writer.WriteAttributeString("Name", "urn:schemas-microsoft-com:office:spreadsheet", name);
        _writer.WriteStartElement("Table", "urn:schemas-microsoft-com:office:spreadsheet");
    }

    public void WriteEndWorksheet()
    {
        if (_writer == null) throw new InvalidOperationException("Cannot write after closing.");

        _writer.WriteEndElement();
        _writer.WriteEndElement();
    }

    public ExcelWriter(string outputFileName)
    {
        XmlWriterSettings settings = new XmlWriterSettings();
        settings.Indent = true;
        _writer = XmlWriter.Create(outputFileName, settings);
    }

    public void Close()
    {
        if (_writer == null) throw new InvalidOperationException("Already closed.");

        _writer.Close();
        _writer = null;
    }

    public void WriteExcelColumnDefinition(int columnWidth)
    {
        if (_writer == null) throw new InvalidOperationException("Cannot write after closing.");

        _writer.WriteStartElement("Column", "urn:schemas-microsoft-com:office:spreadsheet");
        _writer.WriteStartAttribute("Width", "urn:schemas-microsoft-com:office:spreadsheet");
        _writer.WriteValue(columnWidth);
        _writer.WriteEndAttribute();
        _writer.WriteEndElement();
    }

    public void WriteExcelUnstyledCell(string value)
    {
        if (_writer == null) throw new InvalidOperationException("Cannot write after closing.");

        _writer.WriteStartElement("Cell", "urn:schemas-microsoft-com:office:spreadsheet");
        _writer.WriteStartElement("Data", "urn:schemas-microsoft-com:office:spreadsheet");
        _writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "String");
        _writer.WriteValue(value);
        _writer.WriteEndElement();
        _writer.WriteEndElement();
    }

    public void WriteStartRow()
    {
        if (_writer == null) throw new InvalidOperationException("Cannot write after closing.");

        _writer.WriteStartElement("Row", "urn:schemas-microsoft-com:office:spreadsheet");
    }

    public void WriteEndRow()
    {
        if (_writer == null) throw new InvalidOperationException("Cannot write after closing.");

        _writer.WriteEndElement();
    }

    public void WriteExcelStyledCell(object value, CellStyle style)
    {
        if (_writer == null) throw new InvalidOperationException("Cannot write after closing.");

        _writer.WriteStartElement("Cell", "urn:schemas-microsoft-com:office:spreadsheet");
        _writer.WriteAttributeString("StyleID", "urn:schemas-microsoft-com:office:spreadsheet", style.ToString());
        _writer.WriteStartElement("Data", "urn:schemas-microsoft-com:office:spreadsheet");
        switch (style)
        {
            case CellStyle.General:
                _writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "String");
                break;
            case CellStyle.Number:
            case CellStyle.Currency:
                _writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "Number");
                break;
            case CellStyle.ShortDate:
            case CellStyle.DateTime:
                _writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "DateTime");
                break;
        }
        _writer.WriteValue(value);
        //  tag += String.Format("{1}\"><ss:Data ss:Type=\"DateTime\">{0:yyyy\-MM\-dd\THH\:mm\:ss\.fff}</ss:Data>", value,

        _writer.WriteEndElement();
        _writer.WriteEndElement();
    }

    public void WriteExcelAutoStyledCell(object value)
    {
        if (_writer == null) throw new InvalidOperationException("Cannot write after closing.");

        //write the <ss:Cell> and <ss:Data> tags for something
        if (value is Int16 || value is Int32 || value is Int64 || value is SByte ||
            value is UInt16 || value is UInt32 || value is UInt64 || value is Byte)
        {
            WriteExcelStyledCell(value, CellStyle.Number);
        }
        else if (value is Single || value is Double || value is Decimal) //we'll assume it's a currency
        {
            WriteExcelStyledCell(value, CellStyle.Currency);
        }
        else if (value is DateTime)
        {
            //check if there's no time information and use the appropriate style
            WriteExcelStyledCell(value, ((DateTime)value).TimeOfDay.CompareTo(new TimeSpan(0, 0, 0, 0, 0)) == 0 ? CellStyle.ShortDate : CellStyle.DateTime);
        }
        else
        {
            WriteExcelStyledCell(value, CellStyle.General);
        }
    }

    #region IDisposable Members

    public void Dispose()
    {
        if (_writer == null)
            return;

        _writer.Close();
        _writer = null;
    }

    #endregion
}

Then you can export your DataTableusing the following:

然后您可以DataTable使用以下方法导出您的：

public static void ExcelExport(DataTable data, String fileName, bool openAfter)
{
    //export a DataTable to Excel
    DialogResult retry = DialogResult.Retry;

    while (retry == DialogResult.Retry)
    {
        try
        {
            using (ExcelWriter writer = new ExcelWriter(fileName))
            {
                writer.WriteStartDocument();

                // Write the worksheet contents
                writer.WriteStartWorksheet("Sheet1");

                //Write header row
                writer.WriteStartRow();
                foreach (DataColumn col in data.Columns)
                    writer.WriteExcelUnstyledCell(col.Caption);
                writer.WriteEndRow();

                //write data
                foreach (DataRow row in data.Rows)
                {
                    writer.WriteStartRow();
                    foreach (object o in row.ItemArray)
                    {
                        writer.WriteExcelAutoStyledCell(o);
                    }
                    writer.WriteEndRow();
                }

                // Close up the document
                writer.WriteEndWorksheet();
                writer.WriteEndDocument();
                writer.Close();
                if (openAfter)
                    OpenFile(fileName);
                retry = DialogResult.Cancel;
            }
        }
        catch (Exception myException)
        {
            retry = MessageBox.Show(myException.Message, "Excel Export", MessageBoxButtons.RetryCancel, MessageBoxIcon.Asterisk);
        }
    }
}

回答by Rune Grimstad

Creating excel files in .NET applications is quite common and similar questions have been asked several times before. For instance hereand here. The last question asks about reading excel files, but most suggested solutions should work both ways.

在 .NET 应用程序中创建 excel 文件是很常见的，类似的问题之前已经被问过多次。例如这里和这里。最后一个问题是关于阅读 excel 文件，但大多数建议的解决方案应该是双向的。

回答by Mike Gledhill

The following site demonstrates how to export a DataSet (or DataTable or List<>) into a "genuine" Excel 2007 .xlsx file.

以下站点演示了如何将 DataSet（或 DataTable 或 List<>）导出到“真正的”Excel 2007 .xlsx 文件中。

It uses the OpenXMLlibraries, so you don'tneed to have Excel installed on your server.

它使用的OpenXML库，所以你不做需要在你的服务器上安装Excel。

C# ExportToExcel library

C#ExportToExcel 库

All of the source code is provided, free of charge, along with instructions using it with ASP.NET, ASP.NET Core 2+ or regular C#.

免费提供所有源代码，以及在 ASP.NET、ASP.NET Core 2+ 或常规 C# 中使用它的说明。

It's veryeasy to add to your own applications, you just need to call one function, passing in an Excel filename, and your data source:

这是很容易添加到自己的应用程序，你只需要调用一个函数，传入一个Excel文件名，并且您的数据源：

DataSet ds = CreateSampleData();
string excelFilename = "C:\Sample.xlsx";
CreateExcelFile.CreateExcelDocument(ds, excelFilename);

Hope this helps.

希望这可以帮助。

回答by user1314323

using XL = Microsoft.Office.Interop.Excel;
using System.Reflection;

public static void Datasource(DataTable dt)
{
    XL.Application oXL;
    XL._Workbook oWB;
    XL._Worksheet oSheet;
    XL.Range oRng;

    try
    {
        oXL = new XL.Application();
        Application.DoEvents();
        oXL.Visible = false;

        //Get a new workbook.
        oWB = (XL._Workbook)(oXL.Workbooks.Add(Missing.Value));
        oSheet = (XL._Worksheet)oWB.ActiveSheet;

        //System.Data.DataTable dtGridData=ds.Tables[0];

        int iRow = 2;
        if (dt.Rows.Count > 0)
        {
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                oSheet.Cells[1, j + 1] = dt.Columns[j].ColumnName;
            }

            // For each row, print the values of each column.
            for (int rowNo = 0; rowNo < dt.Rows.Count; rowNo++)
            {
                for (int colNo = 0; colNo < dt.Columns.Count; colNo++)
                {
                    oSheet.Cells[iRow, colNo + 1] = dt.Rows[rowNo][colNo].ToString();
                }
                iRow++;
            }
            iRow++;
        }
        oRng = oSheet.get_Range("A1", "IV1");
        oRng.EntireColumn.AutoFit();
        oXL.Visible = true;
    }
    catch (Exception theException)
    {
        throw theException;
    }
    finally
    {
        oXL = null;
        oWB = null;
        oSheet = null;
        oRng = null;
    }
}

Import from Excel to datatable:

从 Excel 导入数据表：

 DataTable dtTable = new DataTable();
 DataColumn col = new DataColumn("Rfid");
 dtTable.Columns.Add(col);
 DataRow drRow;

 Microsoft.Office.Interop.Excel.Application ExcelObj =
        new Microsoft.Office.Interop.Excel.Application();
 Microsoft.Office.Interop.Excel.Workbook theWorkbook =
            ExcelObj.Workbooks.Open(txt_FilePath.Text, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
 Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;
 try
 {
     for (int sht = 1; sht <= sheets.Count; sht++)
     {
          Microsoft.Office.Interop.Excel.Worksheet worksheet =
                    (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(sht);

          for (int i = 2; i <= worksheet.UsedRange.Rows.Count; i++)
          {
              Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A" + i.ToString(), "B" + i.ToString());
              System.Array myvalues = (System.Array)range.Cells.Value2;
              String name = Convert.ToString(myvalues.GetValue(1, 1));
              if (string.IsNullOrEmpty(name) == false)
              {
                  drRow = dtTable.NewRow();
                  drRow["Rfid"] = name;
                  dtTable.Rows.Add(drRow);
              }
          }
              Marshal.ReleaseComObject(worksheet);
              worksheet = null;
        }
    return dtTable;
}
catch
{
    throw;
}
finally
{
   // Marshal.ReleaseComObject(worksheet);
    Marshal.ReleaseComObject(sheets);
    Marshal.ReleaseComObject(theWorkbook);
    Marshal.ReleaseComObject(ExcelObj);
    //worksheet = null;
    sheets = null;
    theWorkbook = null;
    ExcelObj = null;
}

回答by Adamantine

This was a post with a really helpful top answer, but I found it was lacking because there was no simple way to import the XML file back into a datatable. I ended up having to write my own, and thought I would share it here in case anyone else was in the same boat (google was exceptionally unhelpful in that regard):

这是一篇非常有帮助的最佳答案的帖子，但我发现它缺乏，因为没有简单的方法将 XML 文件导入回数据表。我最终不得不自己写，并认为我会在这里分享，以防其他人在同一条船上（谷歌在这方面非常无助）：

public static DataTable ImportExcelXML(string Filename)
    {
        //create a new dataset to load in the XML file
        DataSet DS = new DataSet();
        //Read the XML file into the dataset
        DS.ReadXml(Filename);
        //Create a new datatable to store the raw Data
        DataTable Raw = new DataTable();
        //assign the raw data from the file to the datatable
        Raw = DS.Tables["Data"];
        //count the number of columns in the XML file
        int ColumnNumber = Raw.Columns.Count;
        //create a datatable to store formatted Import Data
        DataTable ImportData = new DataTable();
        //create a string list to store the cell data of each row
        List<string> RowData = new List<string>();
        //loop through each row in the raw data table
        for (int Counter = 0; Counter < Raw.Rows.Count; Counter++)
        {
            //if the data in the row is a colum header
            if (Counter < ColumnNumber)
            {
                //add the column name to our formatted datatable
                ImportData.Columns.Add(Raw.Rows[Counter].ItemArray.GetValue(1).ToString());
            }
            else
            {
                //if the row # (1 row = 1 cell from the excel file) from the raw datatable is divisable evenly by the number of columns in the formated import datatable AND this is not the 1st row of the raw table data after the headers
                if ((Counter % ColumnNumber == 0) && (Counter != ColumnNumber))
                {
                    //add the row we just built to the formatted import datatable
                    ImportData.Rows.Add(GenerateRow(ImportData, RowData));
                    //clear rowdata list in preperation for the next row
                    RowData.Clear();
                }
                //add the current cell data value from the raw datatable to the string list of cell values for the next row to be added to the formatted input datatable
                RowData.Add(Raw.Rows[Counter].ItemArray.GetValue(1).ToString());
            }
        }
        //add the final row
        ImportData.Rows.Add(GenerateRow(ImportData, RowData));

        return ImportData;
    }

    public static DataRow GenerateRow(DataTable ImportData, List<string> RowData)
    {
        //create a counter to keep track of the column position during row composition
        int ColumnPosition = 0;
        //make a new datarow based on the schema of the formated import datatable
        DataRow NewRow = ImportData.NewRow();
        //for each string cell value collected for the RowData list for this row
        foreach (string CellData in RowData)
        {
            //add the cell value to the new datarow
            NewRow[ImportData.Columns[ColumnPosition].ColumnName] = CellData;
            //incriment column position in the new row
            ColumnPosition++;
        }
        //return the generated row
        return NewRow;
    }

回答by user2882267

the code have problem with null values.

代码有空值问题。

 public void WriteExcelAutoStyledCell(object value)
    {
        //solve null values
        if (value is DBNull) return;

回答by lwsolutions

I would have added this to the comments but I am new to stack so I am unable to comment. Using lc.'s solution I added another function that tests the strings characters for invalid XML characters. When I was exporting to excel on occasion there were characters that were found that were causing the export to fail.

我会将此添加到评论中，但我是堆栈的新手，所以我无法发表评论。使用 lc. 的解决方案，我添加了另一个函数来测试无效的 XML 字符的字符串字符。当我导出到 excel 时，有时会发现导致导出失败的字符。

You will need to modify one of the functions that is in lc's code.

您将需要修改 lc 代码中的函数之一。

public void WriteExcelAutoStyledCell(object value)
    {
        if (_writer == null) throw new InvalidOperationException("Cannot write after closing.");
        string newValue = string.Empty;

        try
        {
            //write the <ss:Cell> and <ss:Data> tags for something
            if (value is Int16 || value is Int32 || value is Int64 || value is SByte ||
                value is UInt16 || value is UInt32 || value is UInt64 || value is Byte)
            {
                WriteExcelStyledCell(value, CellStyle.Number);
            }
            else if (value is Single || value is Double || value is Decimal) //we'll assume it's a currency
            {
                WriteExcelStyledCell(value, CellStyle.Currency);
            }
            else if (value is DateTime)
            {
                //check if there's no time information and use the appropriate style
                WriteExcelStyledCell(value, ((DateTime)value).TimeOfDay.CompareTo(new TimeSpan(0, 0, 0, 0, 0)) == 0 ? CellStyle.ShortDate : CellStyle.DateTime);
            }
            else
            {
                newValue = CheckXmlCompatibleValues(value.ToString()).ToString();
                WriteExcelStyledCell(newValue, CellStyle.General);
            }
        }
        catch (Exception thisException)
        {
            throw new InvalidOperationException(thisException.Message.ToString());
        }
    }

And add this function to 'ExcelWriter' class

并将此函数添加到“ExcelWriter”类

public string CheckXmlCompatibleValues(string value)
    {
        string newValue = string.Empty;
        bool found = false;

        foreach (char c in value)
        {
            if (XmlConvert.IsXmlChar(c))
                newValue += c.ToString();
            else
                found = true;
        }

        return newValue.ToString();
    }

lc. Thanks for the code!

液晶。感谢您的代码！

回答by DamienMann

Microsoft has a built in solution for Importing/Exporting Excel files. It's not the most straightforward library but it generally works better than the others listed above.

Microsoft 有一个用于导入/导出 Excel 文件的内置解决方案。它不是最简单的库，但它通常比上面列出的其他库更有效。

The library required to do this is included in Office and can be found under the list of Framework assemblies at Microsoft.Office.Interop.Excel.

执行此操作所需的库包含在 Office 中，可以在 Microsoft.Office.Interop.Excel 的框架程序集列表下找到。

Here is some example code:

下面是一些示例代码：

using Excel = Microsoft.Office.Interop.Excel;

Excel.Application app = new Excel.Application();

//Open existing workbook
//Excel.Workbook workbook = xlApp.Workbooks.Open(fileName);

//Create new workbook
Excel.Workbook workbook = app.Workbooks.Add();

Excel.Worksheet worksheet = workbook.ActiveSheet;

worksheet.Cells[1,1] = "Hello world!"; // Indexes start at 1, because Excel
workbook.SaveAs("C:\MyWorkbook.xlsx");
workbook.Close();
app.Quit();