EPPus 使用总结

EPPus 使用

1. 安装： nuget 搜索 EPPlus
2. 使用tips
   1. 新建一个文件的：
      var template = $"{CommonConst.TEMPLATE_FILE_PATH}/invoice.xlsx";

var fileName = string.Concat("invoice_", shippingPackageId, ".xlsx");

var filePath = $"{CommonConst.UPLOAD_FILE_PATH}/{fileName}";

 

var templateFile = new FileInfo(template);

var newFile = new FileInfo(filePath);
using (ExcelPackage package = new ExcelPackage(newFile))

{

}

1. 1. 从一个文件copy生成一个新的excel

using (ExcelPackage package = new ExcelPackage(newFile, templateFile))

{

}                                                                                                              

1. 1. package-> workbook -> worksheet

workbook = package.Workbook

worksheets = package.Workbook.Worksheets  ->是一个数组

第一个sheet是worksheets[0]

1. 1. 插入新的row
      从第31行开始插入insertRowCount 这么多行
        worksheet.InsertRow(31, insertRowCount);   
      从第31行开始插入 和 22行一样样式的row

worksheet.InsertRow(31, insertRowCount, 22);    

1. 1. 写入信息
          worksheet.Cells[“A1”].Value = “值”
          worksheet.Cells[“A1”].Formula=string.Format("=SUM(L{0}:L{1})",21,42)

package.Save();

1. 1. 合并单元格
         r = 是指第几行 ， 4是第4列就是D， 下面就是合并第r行的D->I 这几个单元格
         ExcelRange excelRange = worksheet.Cells[r, 4, r, 9];
         excelRange.Merge = true;
   2. 设置背景色
             ExcelRange excelRangeStyle = worksheet.Cells[r, 2, r, 13];
             copyStyleExcelRange = worksheet.Cells[21, 1, 21, 13];
             excelRangeStyle.Style.Font = copyStyleExcelRange.Style.Font;
             excelRangeStyle.Style.Fill.PatternType = copyStyleExcelRange.Style.Fill.PatternType;

excelRangeStyle.Style.Fill.BackgroundColor.SetColor(Color.White);

 

1. 1. 设置border
      ExcelRange excelRangeStyle2 = worksheet.Cells[r, 3, r, 12];

excelRangeStyle2.Style.Border.Top.Style = ExcelBorderStyle.Hair;

excelRangeStyle2.Style.Border.Bottom.Style = ExcelBorderStyle.Hair;

excelRangeStyle2.Style.Border.Left.Style = ExcelBorderStyle.Hair;

excelRangeStyle2.Style.Border.Right.Style = ExcelBorderStyle.Hair;

excelRangeStyle2.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

excelRangeStyle2.Style.VerticalAlignment = ExcelVerticalAlignment.Bottom;

ExcelRange excelRangeStyle3 = worksheet.Cells[r, 4, r, 9];

excelRangeStyle3.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;

}