C#将Sql数据保存到Excel文件中的方法

关于“C#将Sql数据保存到Excel文件中的方法”，我可以提供以下攻略：

1.使用Microsoft.Office.Interop.Excel库保存

步骤1：安装Microsoft.Office.Interop.Excel库

在Visual Studio中安装Microsoft.Office.Interop.Excel库。

步骤2：引用命名空间

在文件头部添加using Microsoft.Office.Interop.Excel;命名空间。

步骤3：创建Excel处理对象

创建Excel处理对象.

Excel.Application application = new Excel.Application();
Excel.Workbook workbook = application.Workbooks.Add();
Excel.Worksheet worksheet = workbook.Sheets.Add();

步骤4：写入数据

查询Sql Server数据并获取DataTable变量后，将数据写入Excel文件中。

worksheet.Cells[1, 1].Value = "这里是表格的标题";
for (int i = 0; i < dt.Rows.Count; i++)
{
var row = dt.Rows[i];
for (int j = 0; j < dt.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = row[j].ToString();
}
}

步骤5：保存Excel文件

保存Excel文件.

workbook.SaveAs(@"C:\example.xlsx");

示例1：使用Microsoft.Office.Interop.Excel库

using Microsoft.Office.Interop.Excel;
using System.Data;
using System.Data.SqlClient;

class Program
{
    static void Main(string[] args)
    {
        Application application = new Application();
        Workbook workbook = application.Workbooks.Add();
        Worksheet worksheet = workbook.Worksheets.Add();
        SqlConnection conn = new SqlConnection(@"Server=. ;Database=Test;Trusted_Connection=Yes;");

        SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Student", conn);
        DataTable dt = new DataTable();
        da.Fill(dt);

        worksheet.Cells[1, 1].Value = "Name";
        worksheet.Cells[1, 2].Value = "Age";
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            var row = dt.Rows[i];
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                worksheet.Cells[i + 2, j + 1] = row[j].ToString();
            }
        }

        workbook.SaveAs(@"C:\example.xlsx");
        application.Quit();
    }
}

2.使用OpenXML保存

步骤1：安装DocumentFormat.OpenXml库

在Visual Studio中安装DocumentFormat.OpenXml库。

步骤2：创建Excel文件

创建Excel文件.

SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(@"C:\example.xlsx", SpreadsheetDocumentType.Workbook);
WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart();
worksheetPart.Worksheet = new Worksheet(new SheetData());

步骤3：写入数据

查询Sql Server数据并获取DataTable变量后，将数据写入Excel文件中。

SheetData sheetData = worksheetPart.Worksheet.GetFirstChild();
Row headerRow = new Row();

headerRow.Append(
new Cell()
{
CellValue = new CellValue("Name"),
DataType = CellValues.String
},
new Cell()
{
CellValue = new CellValue("Age"),
DataType = CellValues.String
});

sheetData.AppendChild(headerRow);

foreach (DataRow row in dt.Rows)
{
Row newRow = new Row();
newRow.Append(
new Cell()
{
CellValue = new CellValue(row["Name"].ToString()),
DataType = CellValues.String
},
new Cell()
{
CellValue = new CellValue(row["Age"].ToString()),
DataType = CellValues.String
});

sheetData.AppendChild(newRow);

}

步骤4：保存Excel文件

保存Excel文件。

workbookPart.Workbook.Save();
spreadsheetDocument.Close();

示例2：使用OpenXML库保存

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;

class Program
{
    static void Main(string[] args)
    {
        SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(@"C:\example.xlsx", SpreadsheetDocumentType.Workbook);
        WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
        workbookPart.Workbook = new Workbook();
        WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
        worksheetPart.Worksheet = new Worksheet(new SheetData());

        SqlConnection conn = new SqlConnection(@"Server=. ;Database=Test;Trusted_Connection=Yes;");

        SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Student", conn);
        DataTable dt = new DataTable();
        da.Fill(dt);

        SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
        Row headerRow = new Row();

        headerRow.Append(
            new Cell()
            {
                CellValue = new CellValue("Name"),
                DataType = CellValues.String
            },
            new Cell()
            {
                CellValue = new CellValue("Age"),
                DataType = CellValues.String
            });

        sheetData.AppendChild(headerRow);

        foreach (DataRow row in dt.Rows)
        {
            Row newRow = new Row();
            newRow.Append(
                new Cell()
                {
                    CellValue = new CellValue(row["Name"].ToString()),
                    DataType = CellValues.String
                },
                new Cell()
                {
                    CellValue = new CellValue(row["Age"].ToString()),
                    DataType = CellValues.String
                });

            sheetData.AppendChild(newRow);
        }

        workbookPart.Workbook.Save();
        spreadsheetDocument.Close();
    }
}

以上就是“C#将Sql数据保存到Excel文件中的方法”的攻略，如果还有疑问，请随时询问。