主要来源：
博客： https://www.cnblogs.com/rumeng/p/3785748.html
官网： http://epplus.codeplex.com/
教程： https://riptutorial.com/zh-CN/epplus/topic/8070/%E5%BC%80%E5%A7%8B%E4%BD%BF%E7%94%A8epplus
FileInfo newFile = new FileInfo(@"F:\mynewfile.xlsx");
using (ExcelPackage xlPackage = new ExcelPackage(newFile))//如果mynewfile.xlsx存在，就打开它，否则就在该位置上创建
{
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Tinned Goods");
worksheet.Cells[, ].Value = "Product";
worksheet.Cells[, ].Value = "Broad Beans";
worksheet.Cells[, ].Value = "String Beans";
worksheet.Cells[, ].Value = "Peas";
worksheet.Cells[, ].Value = "Total"; worksheet.Cells[, ].Value = "Tins Sold";//给单元格赋值 ExcelRange cell = worksheet.Cells[, ];
cell.Value = ;//另一种方式给单元格赋值 string calcStartAddress = cell.Address; worksheet.Cells[, ].Value = ;
worksheet.Cells[, ].Value = ; string calcEndAddress = worksheet.Cells[, ].Address; worksheet.Cells[, ].Formula = string.Format("SUM({0}:{1})", calcStartAddress, calcEndAddress);//使用公式计算值，并赋值给单元格 worksheet.Column().Width = ;//设置列宽 xlPackage.Workbook.Properties.Title = "Sample 1";//设置excel的一些属性
xlPackage.Workbook.Properties.Author = "John Tunnicliffe";
xlPackage.Workbook.Properties.SetCustomPropertyValue("EmployeeID", ""); xlPackage.Save();//保存Excel表格
简单使用（单元格赋值、使用公式等）
using (ExcelPackage pck = new ExcelPackage())
{
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Demo"); //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
ws.Cells["A1"].LoadFromDataTable(tbl, true); //Format the header for column 1-3
using (ExcelRange rng = ws.Cells["A1:C1"])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(, , )); //Set color to dark blue
rng.Style.Font.Color.SetColor(Color.White);
} //Example how to Format Column 1 as numeric
using (ExcelRange col = ws.Cells[, , + tbl.Rows.Count, ])
{
col.Style.Numberformat.Format = "#,##0.00";
col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
} //Write it back to the client
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=ExcelDemo.xlsx");
Response.BinaryWrite(pck.GetAsByteArray());
}
在Web中使用
FileInfo newFile = new FileInfo(@"F:\mynewfile.xlsx");
using (ExcelPackage xlPackage = new ExcelPackage(newFile))
{
// get the first worksheet in the workbook
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[];
int iCol = ;
for (int iRow = ; iRow < ; iRow++)
{
string valueStr = string.Format("Cell({0},{1}).Value={2}", iRow, iCol, worksheet.Cells[iRow, iCol].Value);//循环取出单元格值
string value_Str = string.Format("Cell({0},{1}).Formula={2}", , iCol, worksheet.Cells[, iCol].Formula);//取公式（失败了）
}
}
读取Excel表格中的内容
FileInfo newFile = new FileInfo(@"F:\mynewfile.xlsx");
ExcelPackage pck = new ExcelPackage(newFile);
//Add the Content sheet
var ws = pck.Workbook.Worksheets.Add("Content"); #region 缩略column
ws.View.ShowGridLines = false;
ws.Column().OutlineLevel = ;//0表示没有线
ws.Column().Collapsed = true;//合并
ws.Column().OutlineLevel = ;
ws.Column().Collapsed = true;
ws.OutLineSummaryRight = true;
ws.Cells["B1"].Value = "Name";
ws.Cells["C1"].Value = "Size";
ws.Cells["D1"].Value = "Created";
ws.Cells["E1"].Value = "Last modified";
ws.Cells["B1:E1"].Style.Font.Bold = true;
#endregion #region 添加图片到Excel中
Bitmap icon = new Bitmap(@"F:\3765249-468df6edf927b569.jpg");
int row = ;
ws.Row(row).Height = ;//设置整个第五行的高度
//Add the icon as a picture
if (icon != null)
{
ExcelPicture pic = ws.Drawings.AddPicture("pic" + (row).ToString(), icon);
pic.SetPosition((int) * (row - ) + , );//margin-left:0px; margin-top:(int)20 * (row - 1) [20：默认的单元格高度]
}
ws.Cells[, ].Formula = string.Format("SUBTOTAL(9, {0})", ExcelCellBase.GetAddress( + , , row - , ));
#endregion #region 定义一块矩形，*填写文字
var shape = ws.Drawings.AddShape("txtDesc", eShapeStyle.Rect);
shape.SetPosition(, , , );//(第7行,向下偏移10px,第7列,向右偏移10px)
shape.SetSize(, );
shape.Text = "这是一块自定义的区域, Shapes and charts.\n\r\n\r这是换行之后的内容...";
shape.Fill.Style = eFillStyle.SolidFill;
shape.Fill.Color = Color.DarkSlateGray;
shape.Fill.Transparancy = ;//透明度
shape.Border.Fill.Style = eFillStyle.SolidFill;
shape.Border.LineStyle = eLineStyle.LongDash;
shape.Border.Width = ;
shape.Border.Fill.Color = Color.Black;
shape.Border.LineCap = eLineCap.Round;
shape.TextAnchoring = eTextAnchoringType.Top;
shape.TextVertical = eTextVerticalType.Horizontal;
shape.TextAnchoringControl = false;
#endregion #region 超链接
var namedStyle = pck.Workbook.Styles.CreateNamedStyle("HyperLink"); //This one is language dependent
namedStyle.Style.Font.UnderLine = true;
namedStyle.Style.Font.Color.SetColor(Color.Blue);
ws.Cells["K12"].Hyperlink = new ExcelHyperLink(@"A51", "Statistics");//在K12单元格设置一个超链接，点击该超链接可以快速定位到A51单元格
ws.Cells["K12"].StyleName = "HyperLink";
#endregion pck.Save();//保存Excel表格
缩略column，添加图片到Excel中，定义一块矩形填写内容，超链接
public void TiaoXingTu()
{
FileInfo newFile = new FileInfo(@"F:\test.xlsx");
using (ExcelPackage package = new ExcelPackage(newFile))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("test");//工作簿名称 worksheet.Cells.Style.WrapText = true;//自动换行
worksheet.View.ShowGridLines = false;//去掉sheet的网格线 worksheet.Cells[, ].Value = "名称";
worksheet.Cells[, ].Value = "价格";
worksheet.Cells[, ].Value = "销量"; worksheet.Cells[, ].Value = "大米";
worksheet.Cells[, ].Value = ;
worksheet.Cells[, ].Value = ; worksheet.Cells[, ].Value = "玉米";
worksheet.Cells[, ].Value = ;
worksheet.Cells[, ].Value = ; worksheet.Cells[, ].Value = "小米";
worksheet.Cells[, ].Value = ;
worksheet.Cells[, ].Value = ; worksheet.Cells[, ].Value = "糯米";
worksheet.Cells[, ].Value = ;
worksheet.Cells[, ].Value = ; using (ExcelRange range = worksheet.Cells[, , , ])//取一块区域 从1行1列那个单元格开始，向下取5行，向右取3列
{
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
} using (ExcelRange range = worksheet.Cells[, , , ])
{
range.Style.Font.Bold = true;
range.Style.Font.Color.SetColor(Color.White);
range.Style.Font.Name = "微软雅黑";
range.Style.Font.Size = ;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(, , ));
} worksheet.Cells[, ].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(, , ));
worksheet.Cells[, ].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(, , ));
worksheet.Cells[, ].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(, , )); worksheet.Cells[, ].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(, , ));
worksheet.Cells[, ].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(, , ));
worksheet.Cells[, ].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(, , )); worksheet.Cells[, ].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(, , ));
worksheet.Cells[, ].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(, , ));
worksheet.Cells[, ].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(, , )); worksheet.Cells[, ].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(, , ));
worksheet.Cells[, ].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(, , ));
worksheet.Cells[, ].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(, , )); worksheet.Cells[, ].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(, , ));
worksheet.Cells[, ].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(, , ));
worksheet.Cells[, ].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(, , )); //创建一个图表
ExcelChart chart = worksheet.Drawings.AddChart("chart", eChartType.ColumnClustered); //chart.Series.Add()方法所需参数为：chart.Series.Add(Y轴数据区,X轴数据区)
ExcelChartSerie serie = chart.Series.Add(worksheet.Cells[, , , ], worksheet.Cells[, , , ]);
serie.HeaderAddress = worksheet.Cells[, ];//设置图表的图例 chart.SetPosition(, );
chart.SetSize(, );
chart.Title.Text = "销量走势";
chart.Title.Font.Color = Color.FromArgb(, , );
chart.Title.Font.Size = ;
chart.Title.Font.Bold = true;
chart.Style = eChartStyle.Style15;
chart.Legend.Border.LineStyle = eLineStyle.Solid;
chart.Legend.Border.Fill.Color = Color.FromArgb(, , ); package.Save();
}
}
生成简单条形图
public void bingTu()
{
using (ExcelPackage pck = new ExcelPackage())
{
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Demo");
//ws.Cells["A1"].Value = "饼图示例";
ws.Cells[, ].Value = "名称";
ws.Cells[, ].Value = "价格";
ws.Cells[, ].Value = "销量"; ws.Cells[, ].Value = "大米";
ws.Cells[, ].Value = ;
ws.Cells[, ].Value = ; ws.Cells[, ].Value = "玉米";
ws.Cells[, ].Value = ;
ws.Cells[, ].Value = ; ws.Cells[, ].Value = "小米";
ws.Cells[, ].Value = ;
ws.Cells[, ].Value = ; ws.Cells[, ].Value = "糯米";
ws.Cells[, ].Value = ;
ws.Cells[, ].Value = ;
using (ExcelRange r = ws.Cells["A1:C1"])
{
r.Merge = true;
r.Style.Font.SetFromFont(new Font("Arial", , FontStyle.Italic));
r.Style.Font.Color.SetColor(Color.White);
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(, , )); //Add the piechart
var pieChart = ws.Drawings.AddChart("crtExtensionsSize", eChartType.PieExploded3D) as ExcelPieChart;
//Set top left corner to row 1 column 2
pieChart.SetPosition(, , , );
pieChart.SetSize(, ); pieChart.Series.Add(ws.Cells[, , , ], ws.Cells[, , , ]);//chart.Series.Add(Y轴数据区,X轴数据区)
//pieChart.Series.Add(ws.Cells[2, 3, 15, 13], ws.Cells[2, 3, 15, 13]);
//pieChart.Series.Add(ExcelRange.GetAddress(4, 2, 2, 2), ExcelRange.GetAddress(4, 1, 3, 1)); pieChart.Title.Text = "Extension Size";
//Set datalabels and remove the legend
pieChart.DataLabel.ShowCategory = true;
pieChart.DataLabel.ShowPercent = true;
pieChart.DataLabel.ShowLeaderLines = true;
pieChart.Legend.Remove(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=ExcelDemo.xlsx");
Response.BinaryWrite(pck.GetAsByteArray());
}
}
}
生成简单饼图
public static void GenerateExcelReport()
{
string fileName = "ExcelReport-" + DateTime.Now.ToString("yyyy_MM_dd_HHmmss") + ".xlsx";
string reportTitle = "2013年度五大公司实际情况与原计划的百分比";
FileInfo file = new FileInfo("F:\\" + fileName);
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet worksheet = null;
ExcelChartSerie chartSerie = null;
ExcelLineChart chart = null;
#region research
worksheet = package.Workbook.Worksheets.Add("Data");
DataTable dataPercent = GetDataPercent();
//chart = Worksheet.Drawings.AddChart("ColumnStackedChart", eChartType.Line) as ExcelLineChart;
chart = worksheet.Drawings.AddChart("ColumnStackedChart", eChartType.LineMarkers) as ExcelLineChart;//设置图表样式
chart.Legend.Position = eLegendPosition.Right;
chart.Legend.Add();
chart.Title.Text = reportTitle;//设置图表的名称
//chart.SetPosition(200, 50);//设置图表位置
chart.SetSize(, );//设置图表大小
chart.ShowHiddenData = true;
//chart.YAxis.MinorUnit = 1;
chart.XAxis.MinorUnit = ;//设置X轴的最小刻度
//chart.DataLabel.ShowCategory = true;
chart.DataLabel.ShowPercent = true;//显示百分比 //设置月份
for (int col = ; col <= dataPercent.Columns.Count; col++)
{
worksheet.Cells[, col].Value = dataPercent.Columns[col - ].ColumnName;
}
//设置数据
for (int row = ; row <= dataPercent.Rows.Count; row++)
{
for (int col = ; col <= dataPercent.Columns.Count; col++)
{
string strValue = dataPercent.Rows[row - ][col - ].ToString();
if (col == )
{
worksheet.Cells[row + , col].Value = strValue;
}
else
{
double realValue = double.Parse(strValue);
worksheet.Cells[row + , col].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row + , col].Style.Numberformat.Format = "#0\\.00%";//设置数据的格式为百分比
worksheet.Cells[row + , col].Value = realValue;
if (realValue < 0.90d)//如果小于90%则该单元格底色显示为红色
{ worksheet.Cells[row + , col].Style.Fill.BackgroundColor.SetColor(Color.Red);
}
else if (realValue >= 0.90d && realValue <= 0.95d)//如果在90%与95%之间则该单元格底色显示为黄色
{
worksheet.Cells[row + , col].Style.Fill.BackgroundColor.SetColor(Color.Yellow);
}
else
{
worksheet.Cells[row + , col].Style.Fill.BackgroundColor.SetColor(Color.Green);//如果大于95%则该单元格底色显示为绿色
}
}
}
//chartSerie = chart.Series.Add(worksheet.Cells["A2:M2"], worksheet.Cells["B1:M1"]);
//chartSerie.HeaderAddress = worksheet.Cells["A2"];
//chart.Series.Add()方法所需参数为：chart.Series.Add(X轴数据区,Y轴数据区)
chartSerie = chart.Series.Add(worksheet.Cells[row + , , row + , + dataPercent.Columns.Count - ], worksheet.Cells["B1:M1"]);
chartSerie.HeaderAddress = worksheet.Cells[row + , ];//设置每条线的名称
}
//因为假定每家公司至少完成了80%以上，所以这里设置Y轴的最小刻度为80%，这样使图表上的折线更清晰
chart.YAxis.MinValue = 0.8d;
//chart.SetPosition(200, 50);//可以通过制定左上角坐标来设置图表位置
//通过指定图表左上角所在的行和列及对应偏移来指定图表位置
//这里CommpanyNames.Length + 1及3分别表示行和列
chart.SetPosition(CommpanyNames.Length + , , , );
#endregion research
package.Save();//保存文件
}
}
生成简单折线图
下面的是周金桥的demo
static string[] CommpanyNames = new string[] { "Microsoft", "IBM", "Oracle", "Google", "Yahoo", "HP" };
生成稍微复杂的折线图1
private static DataTable GetDataPercent()
{
string[] MonthNames = new string[] { "一月", "二月", "三月", "四月", "五月", "六月", "七月", "八月", "九月", "十月", "十一月", "十二月" };
//private static readonly string[] CommpanyNames = new string[] { "Microsoft", "IBM", "Oracle", "Amazon", "Google", "Facebook", "Twitter", "Paypal", "Yahoo", "HP" };
DataTable data = new DataTable();
DataRow row = null;
Random random = new Random();
data.Columns.Add(new DataColumn("公司名", typeof(string)));
foreach (string monthName in MonthNames)
{
data.Columns.Add(new DataColumn(monthName, typeof(double)));
}
//每个公司每月的百分比表示完成的业绩与计划的百分比
for (int i = ; i < CommpanyNames.Length; i++)
{
row = data.NewRow();
row[] = CommpanyNames[i];
for (int j = ; j <= MonthNames.Length; j++)
{
//这里采用了随机生成数据，但假定每家公司至少完成了计划的85%以上
row[j] = 0.85d + random.Next(, ) / 100d;
}
data.Rows.Add(row);
} return data;
}
生成稍微复杂的折线图2
public static void GenerateExcelReport()
{
string fileName = "ExcelReport-" + DateTime.Now.ToString("yyyy_MM_dd_HHmmss") + ".xlsx";
string reportTitle = "2013年度五大公司实际情况与原计划的百分比";
FileInfo file = new FileInfo("F:\\" + fileName);
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet worksheet = null;
ExcelChartSerie chartSerie = null;
ExcelLineChart chart = null;
#region research
worksheet = package.Workbook.Worksheets.Add("Data");
DataTable dataPercent = GetDataPercent();
//chart = Worksheet.Drawings.AddChart("ColumnStackedChart", eChartType.Line) as ExcelLineChart;
chart = worksheet.Drawings.AddChart("ColumnStackedChart", eChartType.LineMarkers) as ExcelLineChart;//设置图表样式
chart.Legend.Position = eLegendPosition.Right;
chart.Legend.Add();
chart.Title.Text = reportTitle;//设置图表的名称
//chart.SetPosition(200, 50);//设置图表位置
chart.SetSize(, );//设置图表大小
chart.ShowHiddenData = true;
//chart.YAxis.MinorUnit = 1;
chart.XAxis.MinorUnit = ;//设置X轴的最小刻度
//chart.DataLabel.ShowCategory = true;
chart.DataLabel.ShowPercent = true;//显示百分比 //设置月份
for (int col = ; col <= dataPercent.Columns.Count; col++)
{
worksheet.Cells[, col].Value = dataPercent.Columns[col - ].ColumnName;
}
//设置数据
for (int row = ; row <= dataPercent.Rows.Count; row++)
{
for (int col = ; col <= dataPercent.Columns.Count; col++)
{
string strValue = dataPercent.Rows[row - ][col - ].ToString();
if (col == )
{
worksheet.Cells[row + , col].Value = strValue;
}
else
{
double realValue = double.Parse(strValue);
worksheet.Cells[row + , col].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row + , col].Style.Numberformat.Format = "#0\\.00%";//设置数据的格式为百分比
worksheet.Cells[row + , col].Value = realValue;
if (realValue < 0.90d)//如果小于90%则该单元格底色显示为红色
{ worksheet.Cells[row + , col].Style.Fill.BackgroundColor.SetColor(Color.Red);
}
else if (realValue >= 0.90d && realValue <= 0.95d)//如果在90%与95%之间则该单元格底色显示为黄色
{
worksheet.Cells[row + , col].Style.Fill.BackgroundColor.SetColor(Color.Yellow);
}
else
{
worksheet.Cells[row + , col].Style.Fill.BackgroundColor.SetColor(Color.Green);//如果大于95%则该单元格底色显示为绿色
}
}
}
//chartSerie = chart.Series.Add(worksheet.Cells["A2:M2"], worksheet.Cells["B1:M1"]);
//chartSerie.HeaderAddress = worksheet.Cells["A2"];
//chart.Series.Add()方法所需参数为：chart.Series.Add(X轴数据区,Y轴数据区)
chartSerie = chart.Series.Add(worksheet.Cells[row + , , row + , + dataPercent.Columns.Count - ], worksheet.Cells["B1:M1"]);
chartSerie.HeaderAddress = worksheet.Cells[row + , ];//设置每条线的名称
}
//因为假定每家公司至少完成了80%以上，所以这里设置Y轴的最小刻度为80%，这样使图表上的折线更清晰
chart.YAxis.MinValue = 0.8d;
//chart.SetPosition(200, 50);//可以通过制定左上角坐标来设置图表位置
//通过指定图表左上角所在的行和列及对应偏移来指定图表位置
//这里CommpanyNames.Length + 1及3分别表示行和列
chart.SetPosition(CommpanyNames.Length + , , , );
#endregion research
package.Save();//保存文件
}
}
生成稍微复杂的折线图3