ExcelPackage 使用說明

1.使用方法

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

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.设置样式

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

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;//列寬