Excel操作之Epplus
下面是Excel中写入一个折线图;两条折线,其中第二条折线的Y轴为次坐标轴(右边轴);
Excel单元格格式设置 :
worksheet.Cells[row,colum].Style.Numberformat.Format = "@";单元格类型设置为文本类型
worksheet.Column(colum).Style.Numberformat.Format="@"设置列类型为文本类型
worksheet.Row(row).Style.Numberformat.Format="@"设置行类型为文本类型
worksheet.Cells[""].Style.Font.Color.Indexed=5 设置单元格中字体的颜色 或者 worksheet.Cells[1, 1].Style.Font.Color.SetColor(Color.White);
下图为颜色对应的数字
![](https://img2018.cnblogs.com/blog/1550577/201902/1550577-20190221224253524-715800245.jpg)
一组单元格 = worksheet.Cells[起始行, 起始列, 终止行, 终止列]
package.Save();保存Excel;package.SaveAs(Path);将Excel保存到Path;其中Path带文件名
worksheet.Cells[row,colum].Style.Numberformat.Format = "@";单元格类型设置为文本类型
public void GetToChart() { FileInfo newFile = new FileInfo(@"D:\C#程序\代码实验室\Excel\x.xlsx"); using (ExcelPackage package = new ExcelPackage(newFile)) { ExcelWorksheet worksheet = package.Workbook.Worksheets[1]; #region 制作表格 worksheet.Cells.Style.WrapText = true; worksheet.Cells[1, 1].Value = "2018/02/01"; worksheet.Cells[1, 2].Value = "价格"; worksheet.Cells[1, 3].Value = "销量"; worksheet.Cells[2, 1].Value = "2018/02/02"; worksheet.Cells[2, 2].Value = 56; worksheet.Cells[2, 3].Value = 100; worksheet.Cells[3, 1].Value = "2018/02/03"; worksheet.Cells[3, 2].Value = 45; worksheet.Cells[3, 3].Value = 150; worksheet.Cells[4, 1].Value = "2018/02/04"; worksheet.Cells[4, 2].Value = 38; worksheet.Cells[4, 3].Value = 130; worksheet.Cells[5, 1].Value = "2018/02/05"; worksheet.Cells[5, 2].Value = 22; worksheet.Cells[5, 3].Value = 200; #endregion #region 表格样式 using (ExcelRange range = worksheet.Cells[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)); } #endregion ExcelChart chart = worksheet.Drawings.AddChart("chart", eChartType.Line);//设置图标类型 #region 换坐标值 ExcelRange er = worksheet.Cells[2, 2, 5, 2]; #endregion var chartType1 = chart.PlotArea.ChartTypes.Add(eChartType.Line);// 定义一个折线类型chart chartType1.UseSecondaryAxis = false;//第二坐标 var serie1 = (chartType1.Series.Add(worksheet.Cells[2, 3, 5, 3], worksheet.Cells[2, 1, 5, 1]) as ExcelChartSerie);//定义序列 var chartType2 = chart.PlotArea.ChartTypes.Add(eChartType.Line);// 定义一个折线类型chart chartType2.UseSecondaryAxis = true;//第二坐标 var serie2 = (chartType2.Series.Add(worksheet.Cells[2, 2, 5, 2], worksheet.Cells[2, 1, 5, 1]) as ExcelChartSerie);//定义序列 serie1.HeaderAddress = worksheet.Cells[1, 3]; serie2.HeaderAddress = worksheet.Cells[1, 2]; 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(); } }