epplus 一般使用教程_epplus教程

1、打开文件

FileInfo fileInfo = new FileInfo(strFilePath); 
using (ExcelPackage package = new ExcelPackage(fileInfo))
 {
    ExcelWorksheet Mapping = package.Workbook.Worksheets.Add(SheetName);
    ExcelWorksheet Mst = package.Workbook.Worksheets.Add("Mst");
    package.Save(); //Save the workbook.
 }

2、赋值

 int skip = 0;
//填充Mst
for (int i = 1; i < ds.Tables.Count; i++)
{
    Column = IndexToColumn(2 * i - 1);
    //填充列，连同数据源表头一起填充
    Mst.Cells[Column + "1"].LoadFromDataTable(ds.Tables[i], true);
    var ListValidation = Mapping.DataValidations.AddListValidation(Mapping.Cells[Startx + Endx + 1, i + skip, Startx + Endx + effectRow, i + skip].Address);//设置下拉框显示的数据区域
    ListValidation.Formula.ExcelFormula = "=Mst!$" + Column + "$2:$" + Column + "$" + (ds.Tables[i].Rows.Count + 1).ToString();//数据区域的名称
    if (i == 4)
    {
        skip = 1;
        Column = IndexToColumn(i);
        string strColumn = Column + (Startx + Endx + 1).ToString();
        Mapping.Cells[Startx + Endx + 1, i + skip, Startx + Endx + effectRow, i + skip].Formula = "=IFERROR(RIGHT(" + strColumn + ",LEN(" + strColumn + ") - FIND(\"@\"," + strColumn + ")), \"\")";
       
    }
    ListValidation.ShowErrorMessage = true;
    ListValidation.Error = Language.GetChiEng("Please choose options from the drop down only.", "请选择存在于下拉框的值");
    ListValidation.ErrorTitle = Language.GetChiEng("Entry was invalid.", "输入的数据无效");
  
}
var intValidation = Mapping.DataValidations.AddIntegerValidation(Mapping.Cells[Startx + Endx + 1, Endy + PostionCount + Other.Length, Startx + Endx + effectRow, Endy + PostionCount + Other.Length].Address);
intValidation.Operator = ExcelDataValidationOperator.between;
intValidation.Formula.Value = DateTime.Now.Year - 50;//有效值，取当前年的前后50年
intValidation.Formula2.Value = DateTime.Now.Year + 50;
intValidation.ShowErrorMessage = true;
intValidation.Error = Language.GetChiEng("Please input year.", "请输入年份");
intValidation.ErrorTitle = Language.GetChiEng("Entry was invalid.", "输入的数据无效");

3、样式

//冻结表头
Mapping.View.FreezePanes(4, 1);
 
//合并行列
Mapping.Cells[1, 1, 3, 5].Merge = true;
Mapping.Cells[1, 6, 3, 11].Merge = true;
 
 Mapping.Cells[1, 1].Value = " Sample Type Mapping and Time Per Style";
 Mapping.Cells[1, 6].Value = "Position Time Per Style(H)";
 
Mapping.Cells[1, 1, 30, 11].Style.Font.Bold = true;//字体为粗体
//设置背景颜色
Mapping.Cells[1, 1, 30, 11].Style.Fill.PatternType = ExcelFillStyle.Solid;
Mapping.Cells[1, 1, 30, 11].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(221, 235, 247));
 
//负数则红色字体显示
ExcelAddress excelAddress = new ExcelAddress(1, 1, 30, 11);
var cond = Mapping.ConditionalFormatting.AddLessThan(excelAddress);
cond.Style.Font.Color.Color = Color.Red;
cond.Formula = "0";
 
//设置边框及颜色
using (ExcelRange r = Mapping.Cells[1, 1, 30, 11])
{
    r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
    r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
    r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
    r.Style.Border.Right.Style = ExcelBorderStyle.Thin;
 
    r.Style.Border.Top.Color.SetColor(Color.Black);
    r.Style.Border.Bottom.Color.SetColor(Color.Black);
    r.Style.Border.Left.Color.SetColor(Color.Black);
    r.Style.Border.Right.Color.SetColor(Color.Black);
}
 
Mapping.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
Mapping.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
Mapping.Cells.AutoFitColumns();//宽度自适应
Mapping.Cells.Style.WrapText = true;//自动换行