EPPlus生成Excel表格(只支持2007及以上)
主要来源:
博客: 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[1, 1].Value = "Product"; worksheet.Cells[2, 1].Value = "Broad Beans"; worksheet.Cells[3, 1].Value = "String Beans"; worksheet.Cells[4, 1].Value = "Peas"; worksheet.Cells[5, 1].Value = "Total"; worksheet.Cells[1, 2].Value = "Tins Sold";//给单元格赋值 ExcelRange cell = worksheet.Cells[2, 2]; cell.Value = 15;//另一种方式给单元格赋值 string calcStartAddress = cell.Address; worksheet.Cells[3, 2].Value = 32; worksheet.Cells[4, 2].Value = 65; string calcEndAddress = worksheet.Cells[4, 2].Address; worksheet.Cells[5, 2].Formula = string.Format("SUM({0}:{1})", calcStartAddress, calcEndAddress);//使用公式计算值,并赋值给单元格 worksheet.Column(1).Width = 15;//设置列宽 xlPackage.Workbook.Properties.Title = "Sample 1";//设置excel的一些属性 xlPackage.Workbook.Properties.Author = "John Tunnicliffe"; xlPackage.Workbook.Properties.SetCustomPropertyValue("EmployeeID", "1147"); 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(79, 129, 189)); //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[2, 1, 2 + tbl.Rows.Count, 1]) { 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()); }
DialogResult dr = openFileDialog1.ShowDialog(); if (dr == System.Windows.Forms.DialogResult.OK) { string path = openFileDialog1.FileName.ToString(); FileInfo fileInfo = new FileInfo(path); using (ExcelPackage xlPackage = new ExcelPackage(fileInfo)) { ExcelWorksheet worksheet1 = xlPackage.Workbook.Worksheets[2]; int rows2 = worksheet1.Dimension.Rows; int cols2 = worksheet1.Dimension.Columns; for (int i = 0; i < rows2; i++) { string val = worksheet1.Cells[i + 1, 1].Value.ToString();//从 1,1开始 } } }
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(4).OutlineLevel = 1;//0表示没有线 ws.Column(4).Collapsed = true;//合并 ws.Column(5).OutlineLevel = 1; ws.Column(5).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 = 5; ws.Row(row).Height = 125;//设置整个第五行的高度 //Add the icon as a picture if (icon != null) { ExcelPicture pic = ws.Drawings.AddPicture("pic" + (row).ToString(), icon); pic.SetPosition((int)20 * (row - 1) + 2, 0);//margin-left:0px; margin-top:(int)20 * (row - 1) [20:默认的单元格高度] } ws.Cells[3, 3].Formula = string.Format("SUBTOTAL(9, {0})", ExcelCellBase.GetAddress(3 + 1, 3, row - 1, 3)); #endregion #region 定义一块矩形,自由填写文字 var shape = ws.Drawings.AddShape("txtDesc", eShapeStyle.Rect); shape.SetPosition(7, 10, 7, 10);//(第7行,向下偏移10px,第7列,向右偏移10px) shape.SetSize(400, 200); shape.Text = "这是一块自定义的区域, Shapes and charts.\n\r\n\r这是换行之后的内容..."; shape.Fill.Style = eFillStyle.SolidFill; shape.Fill.Color = Color.DarkSlateGray; shape.Fill.Transparancy = 20;//透明度 shape.Border.Fill.Style = eFillStyle.SolidFill; shape.Border.LineStyle = eLineStyle.LongDash; shape.Border.Width = 1; 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表格
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[1, 1].Value = "名称"; worksheet.Cells[1, 2].Value = "价格"; worksheet.Cells[1, 3].Value = "销量"; worksheet.Cells[2, 1].Value = "大米"; worksheet.Cells[2, 2].Value = 56; worksheet.Cells[2, 3].Value = 100; worksheet.Cells[3, 1].Value = "玉米"; worksheet.Cells[3, 2].Value = 45; worksheet.Cells[3, 3].Value = 150; worksheet.Cells[4, 1].Value = "小米"; worksheet.Cells[4, 2].Value = 38; worksheet.Cells[4, 3].Value = 130; worksheet.Cells[5, 1].Value = "糯米"; worksheet.Cells[5, 2].Value = 22; worksheet.Cells[5, 3].Value = 200; using (ExcelRange range = worksheet.Cells[1, 1, 5, 3])//取一块区域 从1行1列那个单元格开始,向下取5行,向右取3列 { range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; range.Style.VerticalAlignment = ExcelVerticalAlignment.Center; } using (ExcelRange range = worksheet.Cells[1, 1, 1, 3]) { range.Style.Font.Bold = true; range.Style.Font.Color.SetColor(Color.White); range.Style.Font.Name = "微软雅黑"; range.Style.Font.Size = 12; range.Style.Fill.PatternType = ExcelFillStyle.Solid; range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128, 128, 128)); } worksheet.Cells[1, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); worksheet.Cells[1, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); worksheet.Cells[1, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); worksheet.Cells[2, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); worksheet.Cells[2, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); worksheet.Cells[2, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); worksheet.Cells[3, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); worksheet.Cells[3, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); worksheet.Cells[3, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); worksheet.Cells[4, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); worksheet.Cells[4, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); worksheet.Cells[4, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); worksheet.Cells[5, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); worksheet.Cells[5, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); worksheet.Cells[5, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); //创建一个图表 ExcelChart chart = worksheet.Drawings.AddChart("chart", eChartType.ColumnClustered); //chart.Series.Add()方法所需参数为:chart.Series.Add(Y轴数据区,X轴数据区) ExcelChartSerie serie = chart.Series.Add(worksheet.Cells[2, 3, 5, 3], worksheet.Cells[2, 1, 5, 1]); serie.HeaderAddress = worksheet.Cells[1, 3];//设置图表的图例 chart.SetPosition(150, 10); chart.SetSize(500, 300); chart.Title.Text = "销量走势"; chart.Title.Font.Color = Color.FromArgb(89, 89, 89); chart.Title.Font.Size = 15; chart.Title.Font.Bold = true; chart.Style = eChartStyle.Style15; chart.Legend.Border.LineStyle = eLineStyle.Solid; chart.Legend.Border.Fill.Color = Color.FromArgb(217, 217, 217); 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[1, 1].Value = "名称"; ws.Cells[1, 2].Value = "价格"; ws.Cells[1, 3].Value = "销量"; ws.Cells[2, 1].Value = "大米"; ws.Cells[2, 2].Value = 56; ws.Cells[2, 3].Value = 100; ws.Cells[3, 1].Value = "玉米"; ws.Cells[3, 2].Value = 45; ws.Cells[3, 3].Value = 150; ws.Cells[4, 1].Value = "小米"; ws.Cells[4, 2].Value = 38; ws.Cells[4, 3].Value = 130; ws.Cells[5, 1].Value = "糯米"; ws.Cells[5, 2].Value = 22; ws.Cells[5, 3].Value = 200; using (ExcelRange r = ws.Cells["A1:C1"]) { r.Merge = true; r.Style.Font.SetFromFont(new Font("Arial", 22, 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(23, 55, 93)); //Add the piechart var pieChart = ws.Drawings.AddChart("crtExtensionsSize", eChartType.PieExploded3D) as ExcelPieChart; //Set top left corner to row 1 column 2 pieChart.SetPosition(10, 0, 2, 0); pieChart.SetSize(400, 400); pieChart.Series.Add(ws.Cells[2, 3, 5, 3], ws.Cells[2, 1, 5, 1]);//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(800, 400);//设置图表大小 chart.ShowHiddenData = true; //chart.YAxis.MinorUnit = 1; chart.XAxis.MinorUnit = 1;//设置X轴的最小刻度 //chart.DataLabel.ShowCategory = true; chart.DataLabel.ShowPercent = true;//显示百分比 //设置月份 for (int col = 1; col <= dataPercent.Columns.Count; col++) { worksheet.Cells[1, col].Value = dataPercent.Columns[col - 1].ColumnName; } //设置数据 for (int row = 1; row <= dataPercent.Rows.Count; row++) { for (int col = 1; col <= dataPercent.Columns.Count; col++) { string strValue = dataPercent.Rows[row - 1][col - 1].ToString(); if (col == 1) { worksheet.Cells[row + 1, col].Value = strValue; } else { double realValue = double.Parse(strValue); worksheet.Cells[row + 1, col].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row + 1, col].Style.Numberformat.Format = "#0\\.00%";//设置数据的格式为百分比 worksheet.Cells[row + 1, col].Value = realValue; if (realValue < 0.90d)//如果小于90%则该单元格底色显示为红色 { worksheet.Cells[row + 1, col].Style.Fill.BackgroundColor.SetColor(Color.Red); } else if (realValue >= 0.90d && realValue <= 0.95d)//如果在90%与95%之间则该单元格底色显示为黄色 { worksheet.Cells[row + 1, col].Style.Fill.BackgroundColor.SetColor(Color.Yellow); } else { worksheet.Cells[row + 1, 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 + 1, 2, row + 1, 2 + dataPercent.Columns.Count - 2], worksheet.Cells["B1:M1"]); chartSerie.HeaderAddress = worksheet.Cells[row + 1, 1];//设置每条线的名称 } //因为假定每家公司至少完成了80%以上,所以这里设置Y轴的最小刻度为80%,这样使图表上的折线更清晰 chart.YAxis.MinValue = 0.8d; //chart.SetPosition(200, 50);//可以通过制定左上角坐标来设置图表位置 //通过指定图表左上角所在的行和列及对应偏移来指定图表位置 //这里CommpanyNames.Length + 1及3分别表示行和列 chart.SetPosition(CommpanyNames.Length + 1, 10, 3, 20); #endregion research package.Save();//保存文件 } }
下面的是周金桥的demo
static string[] CommpanyNames = new string[] { "Microsoft", "IBM", "Oracle", "Google", "Yahoo", "HP" };
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 = 0; i < CommpanyNames.Length; i++) { row = data.NewRow(); row[0] = CommpanyNames[i]; for (int j = 1; j <= MonthNames.Length; j++) { //这里采用了随机生成数据,但假定每家公司至少完成了计划的85%以上 row[j] = 0.85d + random.Next(0, 15) / 100d; } data.Rows.Add(row); } return data; }
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(800, 400);//设置图表大小 chart.ShowHiddenData = true; //chart.YAxis.MinorUnit = 1; chart.XAxis.MinorUnit = 1;//设置X轴的最小刻度 //chart.DataLabel.ShowCategory = true; chart.DataLabel.ShowPercent = true;//显示百分比 //设置月份 for (int col = 1; col <= dataPercent.Columns.Count; col++) { worksheet.Cells[1, col].Value = dataPercent.Columns[col - 1].ColumnName; } //设置数据 for (int row = 1; row <= dataPercent.Rows.Count; row++) { for (int col = 1; col <= dataPercent.Columns.Count; col++) { string strValue = dataPercent.Rows[row - 1][col - 1].ToString(); if (col == 1) { worksheet.Cells[row + 1, col].Value = strValue; } else { double realValue = double.Parse(strValue); worksheet.Cells[row + 1, col].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row + 1, col].Style.Numberformat.Format = "#0\\.00%";//设置数据的格式为百分比 worksheet.Cells[row + 1, col].Value = realValue; if (realValue < 0.90d)//如果小于90%则该单元格底色显示为红色 { worksheet.Cells[row + 1, col].Style.Fill.BackgroundColor.SetColor(Color.Red); } else if (realValue >= 0.90d && realValue <= 0.95d)//如果在90%与95%之间则该单元格底色显示为黄色 { worksheet.Cells[row + 1, col].Style.Fill.BackgroundColor.SetColor(Color.Yellow); } else { worksheet.Cells[row + 1, 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 + 1, 2, row + 1, 2 + dataPercent.Columns.Count - 2], worksheet.Cells["B1:M1"]); chartSerie.HeaderAddress = worksheet.Cells[row + 1, 1];//设置每条线的名称 } //因为假定每家公司至少完成了80%以上,所以这里设置Y轴的最小刻度为80%,这样使图表上的折线更清晰 chart.YAxis.MinValue = 0.8d; //chart.SetPosition(200, 50);//可以通过制定左上角坐标来设置图表位置 //通过指定图表左上角所在的行和列及对应偏移来指定图表位置 //这里CommpanyNames.Length + 1及3分别表示行和列 chart.SetPosition(CommpanyNames.Length + 1, 10, 3, 20); #endregion research package.Save();//保存文件 } }