2017-11-23 17:24 by 曹金宝, … 阅读, … 评论, 收藏, 编辑

1.  前言

　　这是我最近项目刚要的需求，然后在网上找了半天的教材  但是很不幸，有关于Epplus的介绍真的太少了，然后经过了我的不断研究然后不断的采坑，知道现在看到Excel都想吐的时候，终于成功的完成了公司的要求，最后我 稍微的优化了一下代码（毕竟是个刚工作一年多的新人），现在就给大家看一看不足之处，希望给为大佬给以指点，稍后在后面我会给出项目下载。

  1. //首页执行   
  2. DataTable dt = DBhelper.gettable();
  3.             List<RangeData> rlist=new List<RangeData>();
  4.             RangeData rd = new RangeData();
  5.             rd.Charttype = "ColumnStacked";
  6.             rd.Yaxis = "E6,G6,O6";
  7.             rd.UserName = "测试名称";
  8.             rd.Region = "测试地址";
  9.             rd.ChartName = "测试名称";
  10.             rd.ChartKeyword = "F36";
  11.             rd.Appointedtime = DateTime.Now.ToString();
  12.             rlist.Add(rd);
  13.             RangeData rd1 = new RangeData();
  14.             rd1.Charttype = "ColumnClustered";
  15.             rd1.Yaxis = "E6,G6,O6";
  16.             rd1.UserName = "测试名称1";
  17.             rd1.Region = "测试地址1";
  18.             rd1.ChartName = "测试名称1";
  19.             rd1.ChartKeyword = "F36";
  20.             rd1.Appointedtime = DateTime.Now.ToString();
  21.             rlist.Add(rd1);
  22.             RangeData rd2 = new RangeData();
  23.             rd2.Charttype = "Pie";
  24.             rd2.Yaxis = "E6,G6,O6";
  25.             rd2.UserName = "测试名称2";
  26.             rd2.Region = "测试地址2";
  27.             rd2.ChartName = "测试名称2";
  28.             rd2.ChartKeyword = "F36";
  29.             rd2.Appointedtime = DateTime.Now.ToString();
  30.             rlist.Add(rd2);
  31.             RangeData rd3 = new RangeData();
  32.             rd3.Charttype = "singleColumnClustered";
  33.             rd3.Yaxis = "E6,G6,O6";
  34.             rd3.UserName = "测试名称23";
  35.             rd3.Region = "测试地址23";
  36.             rd3.ChartName = "测试名称3";
  37.             rd3.ChartKeyword = "F36";
  38.             rd3.Appointedtime = DateTime.Now.ToString();
  39.             rlist.Add(rd3);
  40.             ExcelInfo.ExportClient("", dt, rlist);

public class ExcelInfo
{


  1. 　　　　  /// <summary>
  2.         /// 输出Excel文件
  3.         /// </summary>
  4.         /// <param name="FileName"></param>
  5.         /// <param name="table"></param>
  6.         /// <param name="?"></param>
  7.         public static void ExportClient(string FileName, DataTable table, List<RangeData> Rlist)
  8.         {
  9.             //reportTitle = "Microsoft,IBM,Oracle,Google.Yahoo";
  10.             FileName = FileName+DateTime.Now.ToString("yyyy_MM_dd_HHmmss") + ".xlsx";
  11.            // string TJcell = "D6,F6,H6,AJ6";
  12.             if (Directory.Exists("UpFiles"))
  13.             {
  14.             }
  15.             else
  16.             {
  17.                 DirectoryInfo directoryInfo = new DirectoryInfo("UpFiles");
  18.                 directoryInfo.Create();
  19.             }
  20.             FileInfo file = new FileInfo(@"D:\文档资料\自学项目\EpplusExcelChartWeb\EpplusExcelChartWeb\upfiles\" + FileName);
  21.             createExcel(file, table, Rlist);
  22.            
  23.         }
  24.         /// <summary>
  25.         /// 创建Excel Sheet
  26.         /// </summary>
  27.         /// <param name="file"></param>
  28.         public static void createExcel(FileInfo file, DataTable table, List<RangeData> Rlist)
  29.         {
  30.             ExcelPackage package = new ExcelPackage(new FileStream(@"D:\文档资料\自学项目\EpplusExcelChartWeb\EpplusExcelChartWeb\test1.xlsx", FileMode.Open));
  31.             ExcelWorksheet sheet = null;
  32.             sheet = package.Workbook.Worksheets[1];
  33.             
  34.             #region 设置Excel数据
  35.             SheetData(table, sheet);
  36.             #endregion
  37.             //设置图形
  38.             if (Rlist.Count > 0)
  39.             {
  40.                 ExcelWorksheet sheet1 = null;
  41.                 sheet1 = package.Workbook.Worksheets.Add("Data");
  42.                 
  43.                 for (int i = 0; i < Rlist.Count; i++)
  44.                 {
  45.                     SheetData(table, sheet1);
  46.                     sheet.Cells["C3"].Value = Rlist[i].UserName;
  47.                     sheet.Cells["C4"].Value = Rlist[i].Region;
  48.                     sheet.Cells["O4"].Value = Rlist[i].Appointedtime;
  49.                     if (Rlist[i].Charttype == "ColumnStacked")
  50.                     {
  51.                         //堆积柱形图
  52.                         ColumnStacked(table, sheet, Rlist[i].Yaxis, Rlist[i].ChartName,Rlist[i].ChartKeyword);
  53.                     }
  54.                     else if (Rlist[i].Charttype == "ColumnClustered")
  55.                     {
  56.                         //簇状柱形图
  57.                         ColumnClustered(table, sheet, Rlist[i].Yaxis, Rlist[i].ChartName, Rlist[i].ChartKeyword);
  58.                     }
  59.                     else if (Rlist[i].Charttype == "Pie")
  60.                     {
  61.                         //饼图
  62.                         Pie(table, sheet, Rlist[i].Yaxis, Rlist[i].ChartName, Rlist[i].ChartKeyword);
  63.                     }
  64.                     else if (Rlist[i].Charttype == "singleColumnClustered")
  65.                     {
  66.                         singleColumnClustered(table, sheet, Rlist[i].Yaxis, Rlist[i].ChartName, Rlist[i].ChartKeyword);
  67.                     }
  68.                 }
  69.             }
  70.             else
  71.             {
  72.                  System.Web.HttpContext.Current.Response.Write("<script language=javascript>alert('没有数据')</script>");
  73.             }
  74.             package.SaveAs(file);//保存文件 
  75.         }
  76.         //工作簿数据
  77.         private static void SheetData(DataTable table, ExcelWorksheet sheet)
  78.         {
  79.             //if (cols[0] != "")
  80.             //{
  81.             //    //设置列标题
  82.             //    for (int col = 1; col <= cols.Length; col++)
  83.             //    {
  84.             //        sheet.Cells[1, col].Value = cols[col - 1];
  85.             //    }
  86.             //}
  87.             //else
  88.             //{
  89.             //设置列标题
  90.             //for (int col = 1; col <= table.Columns.Count; col++)
  91.             //{
  92.             //    sheet.Cells[1, col].Value = table.Columns[col - 1].ColumnName;
  93.             //}
  94.             //}
  95.             //设置数据
  96.             for (int row = 0; row < table.Rows.Count; row++)
  97.             {
  98.                 for (int col = 0; col < table.Columns.Count; col++)
  99.                 {
  100.                     string range = sheet.MergedCells[row + 7, col + 2];
  101.                     string strvalue = table.Rows[row][col].ToString();
  102.                     // sheet.Cells[row + 1, col].Style.Numberformat.Format = "#0\\.00%";//设置数据的格式为百分比   
  103.                     if (table.Rows[row]["F36"].ToString() != "")
  104.                     {
  105.                         sheet.Cells[row + 7, col + 2].Style.Fill.PatternType = ExcelFillStyle.Solid;
  106.                         sheet.Cells[row + 7, col + 2].Style.Fill.BackgroundColor.SetColor(Color.Gray);
  107.                         sheet.Cells[row + 7, col + 2].Value = strvalue;
  108.                     }
  109.                     sheet.Cells[row + 7, col + 2].Value = strvalue;
  111.                 }
  112.             }
  113.         }
  114.         /// <summary>
  115.         /// 堆积柱形图
  116.         /// </summary>
  117.         private static void ColumnStacked(DataTable table,ExcelWorksheet sheet, string TJCell,string Chartname,string  ChartKeyword)
  118.         {
  119.             ////图表系列
  120.             ExcelChartSerie chartSerie = null;
  121.             //图表
  122.             ExcelChart chart = null;
  123.             chart = sheet.Drawings.AddChart(Chartname, eChartType.ColumnStacked);
  124.             chart.Legend.Position = eLegendPosition.Right;
  125.             chart.Legend.Add();
  126.             chart.SetSize(500, 400);//设置图表大小  
  127.             chart.ShowHiddenData = true;
  128.             #region 规定单元格生成图表
  129.             string[] TJcellarray = TJCell.Split(',');
  130.             string XAxis = string.Empty;
  131.             string YAxis = string.Empty;
  132.             for (int row = 1; row <= table.Rows.Count; row++)
  133.             {
  134.                 if (table.Rows[row - 1][ChartKeyword].ToString() != "")
  135.                 {
  136.                     for (int j = 0; j < TJcellarray.Length; j++)
  137.                     {
  138.                         XAxis += "Data!" + GetEXcelstr(TJcellarray[j]) + (row + 6) + ",";
  139.                         YAxis += "test!" + TJcellarray[j] + ",";
  140.                     }
  141.                     int Xlength = XAxis.Length;
  142.                     int Ylength = YAxis.Length;
  143.                     XAxis = XAxis.Substring(0, Xlength - 1);
  144.                     YAxis = YAxis.Substring(0, Ylength - 1);
  145.                     chartSerie = chart.Series.Add(XAxis, YAxis);
  146.                     chartSerie.HeaderAddress = sheet.Cells[row + 6, 2];//设置每条线的名称 
  147.                     XAxis = "";
  148.                     YAxis = "";
  149.                 }
  150.             }
  151.             #endregion
  152.             //   chartSerie = ChartData(table, cell, sheet, chartSerie, chart, TJcell,"F36");
  153.             chart.SetPosition(table.Rows.Count + 7, 10, 1, 20);//设置图表位置  
  154.         }
  155.         /// <summary>
  156.         /// 多条簇状柱形图
  157.         /// </summary>
  158.         private static void ColumnClustered(DataTable table, ExcelWorksheet sheet, string TJCell, string Chartname, string ChartKeyword)
  159.         {
  160.             ////图表系列
  161.             ExcelChartSerie chartSerie = null;
  162.             //图表
  163.             ExcelChart chart = null;
  164.             chart = sheet.Drawings.AddChart(Chartname, eChartType.ColumnClustered);
  165.             chart.Legend.Position = eLegendPosition.Right;
  166.             chart.Legend.Add();
  167.             chart.SetSize(500, 400);//设置图表大小  
  168.             chart.ShowHiddenData = true;
  169.             #region 规定单元格生成图表
  170.             string[] TJcellarray = TJCell.Split(',');
  171.             string XAxis = string.Empty;
  172.             string YAxis = string.Empty;
  173.             for (int row = 1; row <= table.Rows.Count; row++)
  174.             {
  175.                 if (table.Rows[row - 1][ChartKeyword].ToString() != "")
  176.                 {
  177.                     for (int j = 0; j < TJcellarray.Length; j++)
  178.                     {
  179.                         XAxis += "Data!" + GetEXcelstr(TJcellarray[j]) + (row + 6) + ",";
  180.                         YAxis += "test!" + TJcellarray[j] + ",";
  181.                     }
  182.                     int Xlength = XAxis.Length;
  183.                     int Ylength = YAxis.Length;
  184.                     XAxis = XAxis.Substring(0, Xlength - 1);
  185.                     YAxis = YAxis.Substring(0, Ylength - 1);
  186.                     chartSerie = chart.Series.Add(XAxis, YAxis);
  187.                     chartSerie.HeaderAddress = sheet.Cells[row + 6, 2];//设置每条线的名称 
  188.                     XAxis = "";
  189.                     YAxis = "";
  190.                 }
  191.             }
  192.             #endregion
  193.             //   chartSerie = ChartData(table, cell, sheet, chartSerie, chart, TJcell,"F36");
  194.             chart.SetPosition(table.Rows.Count + 7, 10, 10, 20);//设置图表位置  
  195.         }
  197.         /// <summary>
  198.         ///饼图
  199.         /// </summary>
  200.         private static void Pie(DataTable table, ExcelWorksheet sheet, string TJCell, string Chartname, string ChartKeyword)
  201.         {
  202.             ////图表系列
  203.             ExcelChartSerie chartSerie = null;
  204.             //图表
  205.             ExcelChart chart = null;
  206.             chart = sheet.Drawings.AddChart(Chartname, eChartType.Pie);
  207.             chart.Legend.Position = eLegendPosition.Right;
  208.             chart.Legend.Add();
  209.             chart.SetSize(500, 400);//设置图表大小  
  210.             chart.ShowHiddenData = true;
  211.             #region 规定单元格生成图表
  212.             string[] TJcellarray = TJCell.Split(',');
  213.             string XAxis = string.Empty;
  214.             string YAxis = string.Empty;
  215.             for (int row = 1; row <= table.Rows.Count; row++)
  216.             {
  217.                 if (table.Rows[row - 1][ChartKeyword].ToString() != "")
  218.                 {
  219.                     for (int j = 0; j < TJcellarray.Length; j++)
  220.                     {
  221.                         XAxis += "Data!" + GetEXcelstr(TJcellarray[j]) + (row + 6) + ",";
  222.                         YAxis += "test!" + TJcellarray[j] + ",";
  223.                     }
  224.                     int Xlength = XAxis.Length;
  225.                     int Ylength = YAxis.Length;
  226.                     XAxis = XAxis.Substring(0, Xlength - 1);
  227.                     YAxis = YAxis.Substring(0, Ylength - 1);
  228.                     chartSerie = chart.Series.Add(XAxis, YAxis);
  229.                     chartSerie.HeaderAddress = sheet.Cells[row + 6, 2];//设置每条线的名称 
  230.                     XAxis = "";
  231.                     YAxis = "";
  232.                 }
  233.             }
  234.             #endregion
  235.             //   chartSerie = ChartData(table, cell, sheet, chartSerie, chart, TJcell,"F36");
  236.             chart.SetPosition(table.Rows.Count + 7+25, 10, 1, 20);//设置图表位置  
  237.         }
  238.         /// <summary>
  239.         /// 单条簇状柱形图
  240.         /// </summary>
  241.         private static void singleColumnClustered(DataTable table, ExcelWorksheet sheet, string TJCell, string Chartname, string ChartKeyword)
  242.         {
  243.             ////图表系列
  244.             ExcelChartSerie chartSerie = null;
  245.             //图表
  246.             ExcelChart chart = null;
  247.             chart = sheet.Drawings.AddChart(Chartname, eChartType.ColumnClustered);
  248.             chart.Legend.Position = eLegendPosition.Right;
  249.             chart.Legend.Add();
  250.             chart.SetSize(500, 400);//设置图表大小  
  251.             chart.ShowHiddenData = true;
  252.             #region 规定单元格生成图表
  253.             string[] TJcellarray = TJCell.Split(',');
  254.             string XAxis = string.Empty;
  255.             string YAxis = string.Empty;
  256.             for (int row = 1; row <= table.Rows.Count; row++)
  257.             {
  258.                 if (table.Rows[row - 1][ChartKeyword].ToString() != "")
  259.                 {
  260.                     for (int j = 0; j < TJcellarray.Length; j++)
  261.                     {
  262.                         XAxis += "Data!" + GetEXcelstr(TJcellarray[j]) + (row + 6) + ",";
  263.                         YAxis += "test!" + TJcellarray[j] + ",";
  264.                     }
  265.                     int Xlength = XAxis.Length;
  266.                     int Ylength = YAxis.Length;
  267.                     XAxis = XAxis.Substring(0, Xlength - 1);
  268.                     YAxis = YAxis.Substring(0, Ylength - 1);
  269.                     chartSerie = chart.Series.Add(XAxis, YAxis);
  270.                     chartSerie.HeaderAddress = sheet.Cells[row + 6, 2];//设置每条线的名称 
  271.                     XAxis = "";
  272.                     YAxis = "";
  273.                 }
  274.             }
  275.             #endregion
  276.             //   chartSerie = ChartData(table, cell, sheet, chartSerie, chart, TJcell,"F36");
  277.             chart.SetPosition(table.Rows.Count + 7 + 25, 10, 10, 20);//设置图表位置  
  278.         }
  279.         /// <summary>
  280.         /// 提取字符串
  281.         /// </summary>
  282.         /// <param name="p_str"></param>
  283.         /// <returns></returns>
  284.         public static string GetEXcelstr(string p_str)
  285.         {
  286.             string strReturn = string.Empty;
  288.             if (p_str == null || p_str.Trim() == "")
  289.             {
  290.                 strReturn = "";
  291.             }
  293.             foreach (char chrTemp in p_str)
  294.             {
  295.                 if (!Char.IsNumber(chrTemp))
  296.                 {
  298.                     strReturn += chrTemp;
  300.                 }
  301.             }
  303.             return strReturn;
  306.         }
    }
  1. //RangeData类
  2.  
  3.  public class RangeData  
  4.     {
  5.         /////  <param name="XStartCell">数据开始单元格</param>
  6.         //public  string XStartCell { get; set; }
  7.         //  ///   <param name="XEndCell">数据结束单元格</param>
  8.         //public  string XEndCell { get; set; }
  9.         //  ///    <param name="EduName">Y开始单元格</param>
  10.         //public  string YStartCell { get; set; }
  11.         //  ///     <param name="EduName">Y结束单元格</param>
  12.         //public  string YEndCell { get; set; }
  13.         //图表类型 1.ColumnStacked 堆积柱形图 2.ColumnClustered 多条簇状柱形图 3.Pie饼图4.singleColumnClustered 单条簇状图
  14.         public string Charttype { get; set; }
  15.         //指定Y轴分类标签
  16.         public string Yaxis { get; set; }
  17.         //用户名
  18.         public string UserName { get; set; }
  19.         //所在区域
  20.         public string Region { get; set; }
  21.         //指定时间
  22.         public string Appointedtime { get; set; }
  23.         //图表名称
  24.         public string ChartName { get; set; }
  25.         //制图关键字
  26.         public string ChartKeyword { get; set; }
  28.     }

 如果执行报错 可能是获取Excel文件地址的问题。

 

下载地址https://files.cnblogs.com/files/Cjb8973/EpplusExcelChartWeb.rar

版权声明：本文为Cjb8973原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：http://www.cnblogs.com/Cjb8973/p/7885914.html