C# ExcelPackage 使用

1.使用方法

public IActionResult Excel()
       {
           string sWebRootFolder = _hostingEnvironment.WebRootPath;
           string sFileName = "测试导出excel.xlsx";

           FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));

           file.Delete();

           using (ExcelPackage package = new ExcelPackage(file))

           {
               // 添加worksheet

               ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("sheet1");

               //添加头
               //worksheet.Cells[1, 1].Value = "ID";
               //worksheet.Cells[1, 2].Value = "Name";
               //worksheet.Cells[1, 3].Value = "Url";

               worksheet.Cells["A1"].Value = "编号";
               worksheet.Cells["B1"].Value = "姓名";
               worksheet.Cells["C1"].Value = "Url";
               worksheet.Cells["D1"].Value = "时间";

               //添加值
               worksheet.Cells["A2"].Value = 1000;
               worksheet.Cells["B2"].Value = "For丨丶";
               worksheet.Cells["C2"].Value = "网页链接";
               worksheet.Cells["D2"].Value = DateTime.Now.ToString();

               worksheet.Cells["A3"].Value = 1001;
               worksheet.Cells["B3"].Value = "For丨丶Tomorrow";
               worksheet.Cells["C3"].Value = "网页链接";
               worksheet.Cells["C3"].Style.Font.Bold = true;

               package.Save();
           }
           return File(sFileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", sFileName);
       }

2.设置样式

worksheet.Cells["A1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;//左右居中
worksheet.Cells["A1"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;//上下居中
worksheet.Cells["A1:H10"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;//A1到H10区域所有单元格左右居中
worksheet.Cells["A1"].Value="四川\r\n成都";  //换行   配合下面的style才生效
worksheet.Cells["A1"].Style.WrapText = true; //单元格自动换行
worksheet.Cells["A1:H10"].Style.WrapText = true;   //A1到H10区域所有单元格自动换行
worksheet.Cells["A1:H10"].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //A1到H10区域的外边框样式
worksheet.Cells["A1:AA1"].Style.Font.Bold = true; //字体加粗
worksheet.Cells[row,  col].Style.Font.Color.SetColor(Color.Red);           //紅色
worksheet.Cells["A1:H10"].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;//单元格边框
worksheet.Cells["A1:H10"].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
worksheet.Cells["A1:H10"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
worksheet.Cells["A1:H10"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;

worksheet.Cells[row,  col, row+1,  col+1].Style.Numberformat.Format = "@";                                 //设定格式
worksheet.Cells[row, col].Formula = "=SUM(B1:B2)";// 求和
worksheet.Cells[row,  col, row+1,  col+1].Merge = true;   //合并单元格
worksheet.Cells["A1:A2"].Merge = true;    //合并单元格


worksheet.Cells[row,  col, row+1,  col+1].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightYellow   ) ;  //背景颜色
//注：設定背景顏色，要加下面這段，不然提示（Can't set color when patterntype is not set.）
worksheet.Cells[row, 1, row, 6].Style.Fill.PatternType = ExcelFillStyle.Solid;


t_Sheet0.Row (1).Height = 54;    //行高
t_Sheet0.Column(1).Width = 13.5;//列寬