EPPlus:如何为列和行设置不同的背景色，扩展宽度并将单个单元格设置为电子表格的名称？
EPPlus:如何为列和行设置不同的背景色，扩展宽度并将单个单元格设置为电子表格的名称？
提问于 2016-03-11 00:07:33
我正在使用EPPlus库将DataTable映射到excel。这是我的代码：
using (pck = new ExcelPackage())
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Demo");
ws.Cells["A1:I1"].LoadFromDataTable(dt, true);
using (ExcelRange rng = ws.Cells["A1:I1"])
{
rng.Style.Border.Top.Style = ExcelBorderStyle.Thick;
rng.Style.Border.Bottom.Style = ExcelBorderStyle.Thick;
rng.Style.Border.Left.Style = ExcelBorderStyle.Thick;
rng.Style.Border.Right.Style = ExcelBorderStyle.Thick;
rng.Style.Border.Top.Color.SetColor(System.Drawing.Color.White);
rng.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.White);
rng.Style.Border.Left.Color.SetColor(System.Drawing.Color.White);
rng.Style.Border.Right.Color.SetColor(System.Drawing.Color.White);
rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.BurlyWood);
ws.Cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Bisque);
ws.Cells.Style.Border.Top.Style = ExcelBorderStyle.Thick;
ws.Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thick;
ws.Cells.Style.Border.Left.Style = ExcelBorderStyle.Thick;
ws.Cells.Style.Border.Right.Style = ExcelBorderStyle.Thick;
ws.Cells.Style.Border.Top.Color.SetColor(System.Drawing.Color.White);
ws.Cells.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.White);
ws.Cells.Style.Border.Left.Color.SetColor(System.Drawing.Color.White);
ws.Cells.Style.Border.Right.Color.SetColor(System.Drawing.Color.White);
}
Response.AddHeader("content-disposition", "inline;filename=" + ReportName + ".xls");
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.BinaryWrite(pck.GetAsByteArray());
}当运行上面的代码时，我得到了以下结果：
我需要这样的东西：
如您所见，列和行的背景颜色不同，列的宽度更大，并且报表有名称。
我怎样才能达到这个效果呢？
回答 2
Stack Overflow用户
回答已采纳
发布于 2016-03-11 04:46:06
交换订单。首先为整个WorkSheet设置，然后为范围设置。此时，您可以通过设置WorkSheet颜色来覆盖范围颜色。
Stack Overflow用户
发布于 2016-03-11 04:51:29
我想出了解决方案：
using (pck = new ExcelPackage())
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Demo");
ws.Cells["A5:I5"].LoadFromDataTable(dt, true);
ws.DefaultColWidth = 25;
ws.Cells.Style.Border.Top.Style = ExcelBorderStyle.Thick;
ws.Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thick;
ws.Cells.Style.Border.Left.Style = ExcelBorderStyle.Thick;
ws.Cells.Style.Border.Right.Style = ExcelBorderStyle.Thick;
ws.Cells.Style.Border.Top.Color.SetColor(System.Drawing.Color.White);
ws.Cells.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.White);
ws.Cells.Style.Border.Left.Color.SetColor(System.Drawing.Color.White);
ws.Cells.Style.Border.Right.Color.SetColor(System.Drawing.Color.White);
var headerCell = ws.Cells["A5:I5"];
headerCell.Style.Fill.PatternType = ExcelFillStyle.Solid;
headerCell.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.BurlyWood);
var headerFont = headerCell.Style.Font;
headerFont.Bold = true;
int totalRow = ws.Dimension.End.Row;
int totalCol = ws.Dimension.End.Column;
using (ExcelRange rng = ws.Cells[6,1,totalRow,totalCol])
{
rng.Style.Border.Top.Style = ExcelBorderStyle.Thick;
rng.Style.Border.Bottom.Style = ExcelBorderStyle.Thick;
rng.Style.Border.Left.Style = ExcelBorderStyle.Thick;
rng.Style.Border.Right.Style = ExcelBorderStyle.Thick;
rng.Style.Border.Top.Color.SetColor(System.Drawing.Color.White);
rng.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.White);
rng.Style.Border.Left.Color.SetColor(System.Drawing.Color.White);
rng.Style.Border.Right.Color.SetColor(System.Drawing.Color.White);
rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Bisque);
}
watch.Stop();
var elapsedMs = watch.ElapsedMilliseconds;
ws.Cells["A4"].LoadFromText(name + " Generation Time: " + elapsedMs.ToString());
Response.AddHeader("content-disposition", "inline;filename=" + name + ".xls");
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.BinaryWrite(pck.GetAsByteArray());
}我只需将Worksheet的设置与范围互换。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/35921577
复制相关文章
相似问题
腾讯云开发者
