使用Epplus生成Excel 图表

来源：互联网发布：javascript 列表点击编辑：程序博客网时间：2024/06/06 09:50

1. 前言

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

//首页执行   DataTable dt = DBhelper.gettable();            List<RangeData> rlist=new List<RangeData>();            RangeData rd = new RangeData();            rd.Charttype = "ColumnStacked";            rd.Yaxis = "E6,G6,O6";            rd.UserName = "测试名称";            rd.Region = "测试地址";            rd.ChartName = "测试名称";            rd.ChartKeyword = "F36";            rd.Appointedtime = DateTime.Now.ToString();            rlist.Add(rd);            RangeData rd1 = new RangeData();            rd1.Charttype = "ColumnClustered";            rd1.Yaxis = "E6,G6,O6";            rd1.UserName = "测试名称1";            rd1.Region = "测试地址1";            rd1.ChartName = "测试名称1";            rd1.ChartKeyword = "F36";            rd1.Appointedtime = DateTime.Now.ToString();            rlist.Add(rd1);            RangeData rd2 = new RangeData();            rd2.Charttype = "Pie";            rd2.Yaxis = "E6,G6,O6";            rd2.UserName = "测试名称2";            rd2.Region = "测试地址2";            rd2.ChartName = "测试名称2";            rd2.ChartKeyword = "F36";            rd2.Appointedtime = DateTime.Now.ToString();            rlist.Add(rd2);            RangeData rd3 = new RangeData();            rd3.Charttype = "singleColumnClustered";            rd3.Yaxis = "E6,G6,O6";            rd3.UserName = "测试名称23";            rd3.Region = "测试地址23";            rd3.ChartName = "测试名称3";            rd3.ChartKeyword = "F36";            rd3.Appointedtime = DateTime.Now.ToString();            rlist.Add(rd3);            ExcelInfo.ExportClient("", dt, rlist);

public class ExcelInfo    {      　　　　  /// <summary>        /// 输出Excel文件        /// </summary>        /// <param name="FileName"></param>        /// <param name="table"></param>        /// <param name="?"></param>        public static void ExportClient(string FileName, DataTable table, List<RangeData> Rlist)        {            //reportTitle = "Microsoft,IBM,Oracle,Google.Yahoo";            FileName = FileName+DateTime.Now.ToString("yyyy_MM_dd_HHmmss") + ".xlsx";           // string TJcell = "D6,F6,H6,AJ6";            if (Directory.Exists("UpFiles"))            {            }            else            {                DirectoryInfo directoryInfo = new DirectoryInfo("UpFiles");                directoryInfo.Create();            }            FileInfo file = new FileInfo(@"D:\文档资料\自学项目\EpplusExcelChartWeb\EpplusExcelChartWeb\upfiles\" + FileName);            createExcel(file, table, Rlist);                   }        /// <summary>        /// 创建Excel Sheet        /// </summary>        /// <param name="file"></param>        public static void createExcel(FileInfo file, DataTable table, List<RangeData> Rlist)        {            ExcelPackage package = new ExcelPackage(new FileStream(@"D:\文档资料\自学项目\EpplusExcelChartWeb\EpplusExcelChartWeb\test1.xlsx", FileMode.Open));            ExcelWorksheet sheet = null;            sheet = package.Workbook.Worksheets[1];                        #region 设置Excel数据            SheetData(table, sheet);            #endregion            //设置图形            if (Rlist.Count > 0)            {                ExcelWorksheet sheet1 = null;                sheet1 = package.Workbook.Worksheets.Add("Data");                                for (int i = 0; i < Rlist.Count; i++)                {                    SheetData(table, sheet1);                    sheet.Cells["C3"].Value = Rlist[i].UserName;                    sheet.Cells["C4"].Value = Rlist[i].Region;                    sheet.Cells["O4"].Value = Rlist[i].Appointedtime;                    if (Rlist[i].Charttype == "ColumnStacked")                    {                        //堆积柱形图                        ColumnStacked(table, sheet, Rlist[i].Yaxis, Rlist[i].ChartName,Rlist[i].ChartKeyword);                    }                    else if (Rlist[i].Charttype == "ColumnClustered")                    {                        //簇状柱形图                        ColumnClustered(table, sheet, Rlist[i].Yaxis, Rlist[i].ChartName, Rlist[i].ChartKeyword);                    }                    else if (Rlist[i].Charttype == "Pie")                    {                        //饼图                        Pie(table, sheet, Rlist[i].Yaxis, Rlist[i].ChartName, Rlist[i].ChartKeyword);                    }                    else if (Rlist[i].Charttype == "singleColumnClustered")                    {                        singleColumnClustered(table, sheet, Rlist[i].Yaxis, Rlist[i].ChartName, Rlist[i].ChartKeyword);                    }                }            }            else            {                 System.Web.HttpContext.Current.Response.Write("<script language=javascript>alert('没有数据')</script>");            }            package.SaveAs(file);//保存文件         }        //工作簿数据        private static void SheetData(DataTable table, ExcelWorksheet sheet)        {            //if (cols[0] != "")            //{            //    //设置列标题            //    for (int col = 1; col <= cols.Length; col++)            //    {            //        sheet.Cells[1, col].Value = cols[col - 1];            //    }            //}            //else            //{            //设置列标题            //for (int col = 1; col <= table.Columns.Count; col++)            //{            //    sheet.Cells[1, col].Value = table.Columns[col - 1].ColumnName;            //}            //}            //设置数据            for (int row = 0; row < table.Rows.Count; row++)            {                for (int col = 0; col < table.Columns.Count; col++)                {                    string range = sheet.MergedCells[row + 7, col + 2];                    string strvalue = table.Rows[row][col].ToString();                    // sheet.Cells[row + 1, col].Style.Numberformat.Format = "#0\\.00%";//设置数据的格式为百分比                       if (table.Rows[row]["F36"].ToString() != "")                    {                        sheet.Cells[row + 7, col + 2].Style.Fill.PatternType = ExcelFillStyle.Solid;                        sheet.Cells[row + 7, col + 2].Style.Fill.BackgroundColor.SetColor(Color.Gray);                        sheet.Cells[row + 7, col + 2].Value = strvalue;                    }                    sheet.Cells[row + 7, col + 2].Value = strvalue;                }            }        }        /// <summary>        /// 堆积柱形图        /// </summary>        private static void ColumnStacked(DataTable table,ExcelWorksheet sheet, string TJCell,string Chartname,string  ChartKeyword)        {            ////图表系列            ExcelChartSerie chartSerie = null;            //图表            ExcelChart chart = null;            chart = sheet.Drawings.AddChart(Chartname, eChartType.ColumnStacked);            chart.Legend.Position = eLegendPosition.Right;            chart.Legend.Add();            chart.SetSize(500, 400);//设置图表大小              chart.ShowHiddenData = true;            #region 规定单元格生成图表            string[] TJcellarray = TJCell.Split(',');            string XAxis = string.Empty;            string YAxis = string.Empty;            for (int row = 1; row <= table.Rows.Count; row++)            {                if (table.Rows[row - 1][ChartKeyword].ToString() != "")                {                    for (int j = 0; j < TJcellarray.Length; j++)                    {                        XAxis += "Data!" + GetEXcelstr(TJcellarray[j]) + (row + 6) + ",";                        YAxis += "test!" + TJcellarray[j] + ",";                    }                    int Xlength = XAxis.Length;                    int Ylength = YAxis.Length;                    XAxis = XAxis.Substring(0, Xlength - 1);                    YAxis = YAxis.Substring(0, Ylength - 1);                    chartSerie = chart.Series.Add(XAxis, YAxis);                    chartSerie.HeaderAddress = sheet.Cells[row + 6, 2];//设置每条线的名称                     XAxis = "";                    YAxis = "";                }            }            #endregion            //   chartSerie = ChartData(table, cell, sheet, chartSerie, chart, TJcell,"F36");            chart.SetPosition(table.Rows.Count + 7, 10, 1, 20);//设置图表位置          }        /// <summary>        /// 多条簇状柱形图        /// </summary>        private static void ColumnClustered(DataTable table, ExcelWorksheet sheet, string TJCell, string Chartname, string ChartKeyword)        {            ////图表系列            ExcelChartSerie chartSerie = null;            //图表            ExcelChart chart = null;            chart = sheet.Drawings.AddChart(Chartname, eChartType.ColumnClustered);            chart.Legend.Position = eLegendPosition.Right;            chart.Legend.Add();            chart.SetSize(500, 400);//设置图表大小              chart.ShowHiddenData = true;            #region 规定单元格生成图表            string[] TJcellarray = TJCell.Split(',');            string XAxis = string.Empty;            string YAxis = string.Empty;            for (int row = 1; row <= table.Rows.Count; row++)            {                if (table.Rows[row - 1][ChartKeyword].ToString() != "")                {                    for (int j = 0; j < TJcellarray.Length; j++)                    {                        XAxis += "Data!" + GetEXcelstr(TJcellarray[j]) + (row + 6) + ",";                        YAxis += "test!" + TJcellarray[j] + ",";                    }                    int Xlength = XAxis.Length;                    int Ylength = YAxis.Length;                    XAxis = XAxis.Substring(0, Xlength - 1);                    YAxis = YAxis.Substring(0, Ylength - 1);                    chartSerie = chart.Series.Add(XAxis, YAxis);                    chartSerie.HeaderAddress = sheet.Cells[row + 6, 2];//设置每条线的名称                     XAxis = "";                    YAxis = "";                }            }            #endregion            //   chartSerie = ChartData(table, cell, sheet, chartSerie, chart, TJcell,"F36");            chart.SetPosition(table.Rows.Count + 7, 10, 10, 20);//设置图表位置          }        /// <summary>        ///饼图        /// </summary>        private static void Pie(DataTable table, ExcelWorksheet sheet, string TJCell, string Chartname, string ChartKeyword)        {            ////图表系列            ExcelChartSerie chartSerie = null;            //图表            ExcelChart chart = null;            chart = sheet.Drawings.AddChart(Chartname, eChartType.Pie);            chart.Legend.Position = eLegendPosition.Right;            chart.Legend.Add();            chart.SetSize(500, 400);//设置图表大小              chart.ShowHiddenData = true;            #region 规定单元格生成图表            string[] TJcellarray = TJCell.Split(',');            string XAxis = string.Empty;            string YAxis = string.Empty;            for (int row = 1; row <= table.Rows.Count; row++)            {                if (table.Rows[row - 1][ChartKeyword].ToString() != "")                {                    for (int j = 0; j < TJcellarray.Length; j++)                    {                        XAxis += "Data!" + GetEXcelstr(TJcellarray[j]) + (row + 6) + ",";                        YAxis += "test!" + TJcellarray[j] + ",";                    }                    int Xlength = XAxis.Length;                    int Ylength = YAxis.Length;                    XAxis = XAxis.Substring(0, Xlength - 1);                    YAxis = YAxis.Substring(0, Ylength - 1);                    chartSerie = chart.Series.Add(XAxis, YAxis);                    chartSerie.HeaderAddress = sheet.Cells[row + 6, 2];//设置每条线的名称                     XAxis = "";                    YAxis = "";                }            }            #endregion            //   chartSerie = ChartData(table, cell, sheet, chartSerie, chart, TJcell,"F36");            chart.SetPosition(table.Rows.Count + 7+25, 10, 1, 20);//设置图表位置          }        /// <summary>        /// 单条簇状柱形图        /// </summary>        private static void singleColumnClustered(DataTable table, ExcelWorksheet sheet, string TJCell, string Chartname, string ChartKeyword)        {            ////图表系列            ExcelChartSerie chartSerie = null;            //图表            ExcelChart chart = null;            chart = sheet.Drawings.AddChart(Chartname, eChartType.ColumnClustered);            chart.Legend.Position = eLegendPosition.Right;            chart.Legend.Add();            chart.SetSize(500, 400);//设置图表大小              chart.ShowHiddenData = true;            #region 规定单元格生成图表            string[] TJcellarray = TJCell.Split(',');            string XAxis = string.Empty;            string YAxis = string.Empty;            for (int row = 1; row <= table.Rows.Count; row++)            {                if (table.Rows[row - 1][ChartKeyword].ToString() != "")                {                    for (int j = 0; j < TJcellarray.Length; j++)                    {                        XAxis += "Data!" + GetEXcelstr(TJcellarray[j]) + (row + 6) + ",";                        YAxis += "test!" + TJcellarray[j] + ",";                    }                    int Xlength = XAxis.Length;                    int Ylength = YAxis.Length;                    XAxis = XAxis.Substring(0, Xlength - 1);                    YAxis = YAxis.Substring(0, Ylength - 1);                    chartSerie = chart.Series.Add(XAxis, YAxis);                    chartSerie.HeaderAddress = sheet.Cells[row + 6, 2];//设置每条线的名称                     XAxis = "";                    YAxis = "";                }            }            #endregion            //   chartSerie = ChartData(table, cell, sheet, chartSerie, chart, TJcell,"F36");            chart.SetPosition(table.Rows.Count + 7 + 25, 10, 10, 20);//设置图表位置          }        /// <summary>        /// 提取字符串        /// </summary>        /// <param name="p_str"></param>        /// <returns></returns>        public static string GetEXcelstr(string p_str)        {            string strReturn = string.Empty;            if (p_str == null || p_str.Trim() == "")            {                strReturn = "";            }            foreach (char chrTemp in p_str)            {                if (!Char.IsNumber(chrTemp))                {                    strReturn += chrTemp;                }            }            return strReturn;        }}

//RangeData类 public class RangeData      {        /////  <param name="XStartCell">数据开始单元格</param>        //public  string XStartCell { get; set; }        //  ///   <param name="XEndCell">数据结束单元格</param>        //public  string XEndCell { get; set; }        //  ///    <param name="EduName">Y开始单元格</param>        //public  string YStartCell { get; set; }        //  ///     <param name="EduName">Y结束单元格</param>        //public  string YEndCell { get; set; }        //图表类型 1.ColumnStacked 堆积柱形图 2.ColumnClustered 多条簇状柱形图 3.Pie饼图4.singleColumnClustered 单条簇状图        public string Charttype { get; set; }        //指定Y轴分类标签        public string Yaxis { get; set; }        //用户名        public string UserName { get; set; }        //所在区域        public string Region { get; set; }        //指定时间        public string Appointedtime { get; set; }        //图表名称        public string ChartName { get; set; }        //制图关键字        public string ChartKeyword { get; set; }    }

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

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

阅读全文

0 0