如何将一些数据写入Excel文件(.xlsx)？

Question

如何将一些数据写入Excel文件(.xlsx)？

70

这是我想做的事情：

创建一个名为c://test/files/work1_4.13.14.xlsx的Excel文件（.xlsx），命名格式为“名称+值（日期）”。

例如：work1_4.13.14.xlsx
将文件的标题设置为：

[Name] [Age] [City]
我有三个 List<string> 对象，包含姓名、年龄和城市信息，需要将它们填写到Excel表格中。

以下是我希望数据呈现的格式：

Name   Age  City
Ben    20   xyz
Jack   25   xyz
Mike   45   zyx

我该如何以这种格式将数据发送到Excel表格中？

- Vladimir Potapov

1

在Gridview中显示相同的数据，然后使用Gridview中的“导出到Excel”功能进行导出。 - Hardik Vinzava

12

接受的回答已经过时，我认为现在不适合开发人员使用 - 如果您计划使用office.interop.excel，则必须在部署的PC上安装Office，这是一个不必要的依赖项，而且如果您忘记释放资源，就会出现内存泄漏。还有其他库可用，您不需要安装Office，而且使用起来更加简单：NPOI、ClosedXml和EPPlus是一些流行的替代品。我会让读者根据他们的特定需求决定哪个是最好的，但我会毫不犹豫地推荐那三个中最差的，而不是office.interop dll。 - BenKoshy

7个回答

37

您可以使用ClosedXML来实现。将您的表格存储在DataTable中，然后使用这个简单的片段就可以将表格导出到Excel：

XLWorkbook workbook = new XLWorkbook();
DataTable table = GetYourTable();
workbook.Worksheets.Add(table );

您可以阅读ClosedXML的文档以了解更多信息。希望这有所帮助！

- jomsk1e

2

适用于新数据，但这里的缺点是ClosedXML缺乏智能来更新或替换现有工作表中的数据。常见的用例是从模板开始并将数据应用于它; 如果模板具有任何有意义的内容（如图表），ClosedXML可能会破坏模板。 - jws

15

使用 Microsoft.Jet.OLEDB.4.0 和 OleDb，可以在不打开 Excel 文件的情况下进行写入操作。使用 OleDb 时，它的行为类似于使用 SQL 写入表格。

以下是我用来创建和写入新 Excel 文件的代码。无需额外引用。

var connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SomePath\ExcelWorkBook.xls;Extended Properties=Excel 8.0";
using (var excelConnection = new OleDbConnection(connectionString))
{
    // The excel file does not need to exist, opening the connection will create the
    // excel file for you
    if (excelConnection.State != ConnectionState.Open) { excelConnection.Open(); }

    // data is an object so it works with DBNull.Value
    object propertyOneValue = "cool!";
    object propertyTwoValue = "testing";

    var sqlText = "CREATE TABLE YourTableNameHere ([PropertyOne] VARCHAR(100), [PropertyTwo] INT)";

    // Executing this command will create the worksheet inside of the workbook
    // the table name will be the new worksheet name
    using (var command = new OleDbCommand(sqlText, excelConnection)) { command.ExecuteNonQuery(); }

    // Add (insert) data to the worksheet
    var commandText = $"Insert Into YourTableNameHere ([PropertyOne], [PropertyTwo]) Values (@PropertyOne, @PropertyTwo)";

    using (var command = new OleDbCommand(commandText, excelConnection))
    {
        // We need to allow for nulls just like we would with
        // sql, if your data is null a DBNull.Value should be used
        // instead of null 
        command.Parameters.AddWithValue("@PropertyOne", propertyOneValue ?? DBNull.Value);
        command.Parameters.AddWithValue("@PropertyTwo", propertyTwoValue ?? DBNull.Value);

        command.ExecuteNonQuery();
    }
}

- Agrejus

1

问题在于使用 ODEDB 时会遇到 255 字符限制的问题。 - Jim Buckley Barret

14

希望这正是我们正在寻找的。

private void button2_Click(object sender, RoutedEventArgs e)
{
    UpdateExcel("Sheet3", 4, 7, "Namachi@gmail");
}

private void UpdateExcel(string sheetName, int row, int col, string data)
{
    Microsoft.Office.Interop.Excel.Application oXL = null;
    Microsoft.Office.Interop.Excel._Workbook oWB = null;
    Microsoft.Office.Interop.Excel._Worksheet oSheet = null;

    try
    {
        oXL = new Microsoft.Office.Interop.Excel.Application();
        oWB = oXL.Workbooks.Open("d:\\MyExcel.xlsx");
        oSheet = String.IsNullOrEmpty(sheetName) ? (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet : (Microsoft.Office.Interop.Excel._Worksheet)oWB.Worksheets[sheetName];

        oSheet.Cells[row, col] = data;

        oWB.Save();

        MessageBox.Show("Done!");
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
    finally
    {
        if (oWB != null)
        oWB.Close();
    }
}

- Namachi R

4

最近，我尝试了 npoi ，它非常简单易用。

按照要求，让我们编写代码将数据输出到 work1_4.13.14.xlsx 文件中，如下所示：

Name   Age  City
Ben    20   xyz
Jack   25   xyz
Mike   45   zyx

这里是代码

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;

namespace ExcelWriter
{
    class Program
    {
        static void Main(string[] args)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet1 = workbook.CreateSheet("Sheet1");

            IRow row1 = sheet1.CreateRow(0);
            row1.CreateCell(0).SetCellValue("Name");
            row1.CreateCell(1).SetCellValue("Age");
            row1.CreateCell(2).SetCellValue("City");

            IRow row2 = sheet1.CreateRow(1);
            row2.CreateCell(0).SetCellValue("Ben");
            row2.CreateCell(1).SetCellValue("20");
            row2.CreateCell(2).SetCellValue("xyz");

            IRow row3 = sheet1.CreateRow(2);
            row3.CreateCell(0).SetCellValue("Jack");
            row3.CreateCell(1).SetCellValue("25");
            row3.CreateCell(2).SetCellValue("xyz");

            IRow row4 = sheet1.CreateRow(3);
            row4.CreateCell(0).SetCellValue("Mike");
            row4.CreateCell(1).SetCellValue("45");
            row4.CreateCell(2).SetCellValue("zyx");

            FileStream sw = File.Create("work1_4.13.14.xlsx");
            workbook.Write(sw);
            sw.Close();
        }
    }
}

我没有使用循环，只是为了让初学者更容易理解。

.CreateRow(int index) 在指定的索引处创建新行。
row.CreateCell(int index) 在行中指定的索引处创建新单元格。
cell.SetCellValue(string value) 在行索引处设置值。

进一步了解：

Nuget：https://www.nuget.org/packages/NPOI
代码：https://github.com/nissl-lab/npoi
示例：https://github.com/nissl-lab/npoi-examples

- Gray Programmerz

0

使用 .Net 7 和 NuGet Package EPPlus，我们可以使用以下代码创建 OP 请求的 Excel 文件。

创建控制台应用程序并在 Program.cs 文件中编写以下内容：

using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;

ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

List<string>[] users =
{
    new()
    {
        "Ben",
        "20",
        "xyz"
    },
    new()
    {
        "Jack",
        "25",
        "xyz"
    },
    new()
    {
        "Mike",
        "45",
        "zyx"
    }
};

//change to your desired directory
string dir = @"C:\Users\user\Desktop\";
string name = "work";
string extension = ".xlsx";
string fileName = $"{name}_{DateTime.Today:d.M.yy}{extension}";
string path = Path.Combine(dir, fileName);

//Create a new file (if none exists at this path)
FileInfo excel = new(path);

//Using this next statement makes sure you end the process when its not needed
using ExcelPackage package = new ExcelPackage(excel);

//Create a new worksheet
ExcelWorksheet worksheetWork = package.Workbook.Worksheets.Add("Work");

//Create the headers by cell name
worksheetWork.Cells["A1"].Value = "Name";
worksheetWork.Cells["B1"].Value = "Age";
worksheetWork.Cells["C1"].Value = "City";

for (int row = 0; row < users.Length; row++)
{
    for (int column = 0; column < 3; column++)
    {
        //Add the required values to cells by row and column
        //rows and columns start from 1, in this example we need to add 1 to both,
        //and an extra 1 to row to account for the header.
        if (column is 1) //Age column
        {
             worksheetWork.Cells[row + 2, column + 1].Value = int.Parse(users[row][column]);
        }
        else
        {
             worksheetWork.Cells[row + 2, column + 1].Value = users[row][column];
        }
    }
}

package.Save();

一旦程序运行，文件将会被创建，如屏幕截图所示：

- Barreto

-7

只需按照以下步骤进行：

//启动 Excel 并获取 Application 对象。

oXL = new Microsoft.Office.Interop.Excel.Application();

oXL.Visible = false;

- naioleaga

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- user3458958 · Accepted Answer

试试这段代码

Microsoft.Office.Interop.Excel.Application oXL;
Microsoft.Office.Interop.Excel._Workbook oWB;
Microsoft.Office.Interop.Excel._Worksheet oSheet;
Microsoft.Office.Interop.Excel.Range oRng;
object misvalue = System.Reflection.Missing.Value;
try
{
    //Start Excel and get Application object.
    oXL = new Microsoft.Office.Interop.Excel.Application();
    oXL.Visible = true;

    //Get a new workbook.
    oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
    oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

    //Add table headers going cell by cell.
    oSheet.Cells[1, 1] = "First Name";
    oSheet.Cells[1, 2] = "Last Name";
    oSheet.Cells[1, 3] = "Full Name";
    oSheet.Cells[1, 4] = "Salary";

    //Format A1:D1 as bold, vertical alignment = center.
    oSheet.get_Range("A1", "D1").Font.Bold = true;
    oSheet.get_Range("A1", "D1").VerticalAlignment =
        Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

    // Create an array to multiple values at once.
    string[,] saNames = new string[5, 2];

    saNames[0, 0] = "John";
    saNames[0, 1] = "Smith";
    saNames[1, 0] = "Tom";

    saNames[4, 1] = "Johnson";

    //Fill A2:B6 with an array of values (First and Last Names).
    oSheet.get_Range("A2", "B6").Value2 = saNames;

    //Fill C2:C6 with a relative formula (=A2 & " " & B2).
    oRng = oSheet.get_Range("C2", "C6");
    oRng.Formula = "=A2 & \" \" & B2";

    //Fill D2:D6 with a formula(=RAND()*100000) and apply format.
    oRng = oSheet.get_Range("D2", "D6");
    oRng.Formula = "=RAND()*100000";
    oRng.NumberFormat = "$0.00";

    //AutoFit columns A:D.
    oRng = oSheet.get_Range("A1", "D1");
    oRng.EntireColumn.AutoFit();

    oXL.Visible = false;
    oXL.UserControl = false;
    oWB.SaveAs("c:\\test\\test505.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
        false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

    oWB.Close();
    oXL.Quit();

    //...