csharp如何读写excel文件

C#可以使用Microsoft Office Interop库或者第三方库来读写Excel文件。

1. 使用Microsoft Office Interop库读写Excel文件

使用Microsoft Office Interop库需要先安装Microsoft Office软件，然后在C#项目中引用Microsoft.Office.Interop.Excel.dll文件。

读取Excel文件：

using Microsoft.Office.Interop.Excel;

//打开Excel文件
Application excel = new Application();
Workbook workbook = excel.Workbooks.Open(filePath);

//读取数据
Worksheet worksheet = workbook.Worksheets[1];
Range range = worksheet.UsedRange;
int rowCount = range.Rows.Count;
int colCount = range.Columns.Count;
for (int i = 1; i <= rowCount; i++)
{
    for (int j = 1; j <= colCount; j++)
    {
        string cellValue = range.Cells[i, j].Value2.ToString();
        Console.Write(cellValue + "\t");
    }
    Console.WriteLine();
}

//关闭Excel文件
workbook.Close(false, Type.Missing, Type.Missing);
excel.Quit();

写入Excel文件：

using Microsoft.Office.Interop.Excel;

//打开Excel文件
Application excel = new Application();
Workbook workbook = excel.Workbooks.Open(filePath);

//写入数据
Worksheet worksheet = workbook.Worksheets[1];
Range range = worksheet.Range["A1:C3"];
range.Value2 = new object[,] { { "A1", "B1", "C1" }, { "A2", "B2", "C2" }, { "A3", "B3", "C3" } };

//保存Excel文件
workbook.Save();
workbook.Close();
excel.Quit();

2. 使用第三方库读写Excel文件

常用的第三方库有NPOI和EPPlus，它们都支持读写Excel文件，而且不需要安装Microsoft Office软件。

使用NPOI读写Excel文件：

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

//读取Excel文件
using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
    IWorkbook workbook;
    if (filePath.EndsWith(".xls"))
    {
        workbook = new HSSFWorkbook(stream);
    }
    else if (filePath.EndsWith(".xlsx"))
    {
        workbook = new XSSFWorkbook(stream);
    }
    else
    {
        throw new Exception("Invalid Excel file format.");
    }

    ISheet sheet = workbook.GetSheetAt(0);
    int rowCount = sheet.LastRowNum + 1;
    for (int i = 0; i < rowCount; i++)
    {
        IRow row = sheet.GetRow(i);
        int colCount = row.LastCellNum;
        for (int j = 0; j < colCount; j++)
        {
            ICell cell = row.GetCell(j);
            string cellValue = cell.ToString();
            Console.Write(cellValue + "\t");
        }
        Console.WriteLine();
    }
}

//写入Excel文件
using (var stream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
    IWorkbook workbook = new XSSFWorkbook();
    ISheet sheet = workbook.CreateSheet("Sheet1");
    sheet.CreateRow(0).CreateCell(0).SetCellValue("A1");
    sheet.CreateRow(1).CreateCell(0).SetCellValue("A2");
    sheet.CreateRow(2).CreateCell(0).SetCellValue("A3");
    sheet.CreateRow(3).CreateCell(0).SetCellValue("A4");
    workbook.Write(stream);
}

使用EPPlus读写Excel文件：

using OfficeOpenXml;

//读取Excel文件
using (var package = new ExcelPackage(new FileInfo(filePath)))
{
    ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
    int rowCount = worksheet.Dimension.Rows;
    int colCount = worksheet.Dimension.Columns;
    for (int i = 1; i <= rowCount; i++)
    {
        for (int j = 1; j <= colCount; j++)
        {
            string cellValue = worksheet.Cells[i, j].Value.ToString();
            Console.Write(cellValue + "\t");
        }
        Console.WriteLine();
    }
}

//写入Excel文件
using (var package = new ExcelPackage(new FileInfo(filePath)))
{
    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");
    worksheet.Cells["A1"].Value = "A1";
    worksheet.Cells["A2"].Value = "A2";
    worksheet.Cells["A3"].Value = "A3";
    worksheet.Cells["A4"].Value = "A4";
    package.Save();
}