C# EPPlus读写Excel

记录一下自己学习使用EPPlus读写Excel的知识点。

EPPlus开源库，可以直接在vs上的NuGet包里直接安装。

1.写入Excel文件

using OfficeOpenXml;
.
.
.
 ExcelPackage.LicenseContext = LicenseContext.Commercial;//EPPlus需要添加许可
 using (var package=new ExcelPackage())
 {
  //创建Worksheets
  ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Inventory");
  //添加表格头
  worksheet.Cells[1, 1].Value = "ID";
  // worksheet.Cells[1,1].Value = "ID";
  worksheet.Cells[1, 2].Value = "Product";
  worksheet.Cells[1, 3].Value = "Quantity";
  worksheet.Cells[1, 4].Value = "Price";
  worksheet.Cells[1, 5].Value = "Value";

  //添加元素
  worksheet.Cells["A2"].Value = 12001;
  worksheet.Cells["B2"].Value = "Nails";
  worksheet.Cells["C2"].Value = 37;
  worksheet.Cells["D2"].Value = 3.99;

  worksheet.Cells["A3"].Value = 12002;
  worksheet.Cells["B3"].Value = "Hammer";
  worksheet.Cells["C3"].Value = 5;
  worksheet.Cells["D3"].Value = 12.10;

  worksheet.Cells["A4"].Value = 12003;
  worksheet.Cells["B4"].Value = "Saw";
  worksheet.Cells["C4"].Value = 12;
  worksheet.Cells["D4"].Value = 15.37;

  //添加计算格式
  worksheet.Cells["E2:E4"].Formula = "C2*D2";


  //表格格式
  //从(1,1)到(1,5)范围内表格格式就行调整
  using(var range=worksheet.Cells[1,1,1,5])
  {
    range.Style.Font.Bold = true; //字体加粗
    range.Style.Fill.PatternType = ExcelFillStyle.Solid;//单元格填充
    range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue); //背景颜色
    range.Style.Font.Color.SetColor(Color.White);//字体颜色
  }

   worksheet.Cells["A5:E5"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
   worksheet.Cells["A5:E5"].Style.Font.Bold = true;

   worksheet.Cells[5, 3, 5, 5].Formula = String.Format("SUBTOTAL(9,{0})", new ExcelAddress(2, 3, 4, 3).Address);//累加
   worksheet.Cells["C2:C5"].Style.Numberformat.Format = "#,##0"; //数字格式
   worksheet.Cells["D2:E5"].Style.Numberformat.Format = "#,##0.00";

   worksheet.Cells["A1:E4"].AutoFilter = true;
   worksheet.Cells["A2:A4"].Style.Numberformat.Format = "@";

   // 设置标题文本
   worksheet.HeaderFooter.OddHeader.CenteredText = "&24&U&\"Arial,Regular Bold\" Inventory";
   // 将页码添加到页脚加上总页数
   worksheet.HeaderFooter.OddFooter.RightAlignedText =
   string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
   // 将图纸名称添加到页脚
   worksheet.HeaderFooter.OddHeader.CenteredText = "&24&U&\"Arial,Regular Bold\" Inventory";
   // 将页码添加到页脚加上总页数
   worksheet.HeaderFooter.OddFooter.RightAlignedText =
   string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
   // 将图纸名称添加到页脚
   worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;
   // 将文件路径添加到页脚
   worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;

   //更改图纸视图以在页面布局模式下显示
   worksheet.View.PageLayoutView = true;


  //添加保存路径             
  FileInfo fileInfo = new FileInfo(@"F:\系统默认\桌面\LearningEPPlus\sample.xlsx");
  //创建并保存Excel
  package.SaveAs(fileInfo);

实际效果如下 

2.读取Excel文件中某一sheet内的数据

 string excelFilePath = ofd.FileName;
 string sheetName = "sheetName";//读取sheetName的sheet
 textBoxFilePath.Text = ofd.FileName;
 FileInfo fileInfo = new FileInfo(excelFilePath);
 using (ExcelPackage package = new ExcelPackage(fileInfo))
 {
     // 获取指定名称的工作表
     ExcelWorksheet worksheet = package.Workbook.Worksheets[sheetName];
      
     if (worksheet != null)
     {
         // 获取表格数据的起始行和列
         int startRow = worksheet.Dimension.Start.Row;
         int startCol = worksheet.Dimension.Start.Column;

         // 获取表格数据的结束行和列
         int endRow = worksheet.Dimension.End.Row;
         int endCol = worksheet.Dimension.End.Column;
         float[,] MapData = new float[endRow , endCol ];
         // 从 Excel 表格读取数据并存放在对象中
         for (int row = startRow + 1; row <= endRow; row++) // 第一行通常是标题，所以从第二行开始读取数据
         {
            
             for (int col = startCol+1; col <= endCol; col++)
             {
                 MapData[row - 2, col-2] = Convert.ToSingle(worksheet.Cells[row, col].Value);
             }
         
         }
     }
     else
     {
         Console.WriteLine($"Sheet '{sheetName}' not found in the Excel file.");
     }
    
  
 }