ExcelPackage 使用說明

 1.使用方法

using OfficeOpenXml   //引用

  private void CreateExcel(List<WO0500130Q_tamt> data, string page_name)  
        {
            using (var t_Excel = new ExcelPackage())
            {
                var t_DataSheet = ErpManagement.Excel.AddSheet(t_Excel, data, page_name); //工作表，資料集，工作表名稱
                var t_FileName = ErpManagement.ReportLocation + page_name + "_" + ErpManagement.Common.GetDatabaseTime().ToString("yyyyMMddHHmmss") + ".xlsx";   //excel名稱
              t_Excel.SaveAs(t_FileName);
                ErpManagement.Excel.OpenFile(t_FileName);
            }
        }

2.excel操作技巧

t_Sheet1.Cells[row, col].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;     //左右居中
t_Sheet1.Cells[row,  col].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;         //上下居中
t_Sheet1.Cells[row,  col, row+1,  col+1].Style.Numberformat.Format = "@";                                 //設定儲存格格式
t_Sheet1.Cells[row,  col, row+1,  col+1].Style.Border.BorderAround(ExcelBorderStyle.Thin); 
t_Sheet1.Cells[row,  col, row+1,  col+1].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;              //設定邊框
t_Sheet1.Cells[row, col].Formula = "=SUM(B1:B2)";// 加匯
t_Sheet1.Cells[row,  col, row+1,  col+1].Merge = true;                                                         //合併儲存格
t_Sheet1.Cells["A1:A2"].Merge = true;                                                         //合併儲存格

 

t_Sheet1.Cells[row,  col].Style.Font.Color.SetColor(Color.Red);           //紅色
t_Sheet0.Cells[row,  col, row+1,  col+1].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightYellow   ) ;  背景顏色

注：設定背景顏色，要加下面這段，不然提示（Can't set color when patterntype is not set.）

  t_Sheet1.Cells[row, 1, row, 6].Style.Fill.PatternType = ExcelFillStyle.Solid;
 

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