使用Epplus生成Excel 图表

原文转自：https://www.cnblogs.com/Cjb8973/archive/2017/11/23/7885914.html
//首页执行
DataTable dt = DBhelper.gettable();
List rlist=new List();
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
{

///
/// 输出Excel文件
///
///
///
///
public static void ExportClient(string FileName, DataTable table, List 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.YAxis.MinorTickMark = eAxisTickMark.None;
chart.XAxis.MinorTickMark = eAxisTickMark.None;//修改刻度线

chart.YAxis.MinorTickMark = eAxisTickMark.None;
chart.XAxis.MinorTickMark = eAxisTickMark.None;//修改刻度线

        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.YAxis.MinorTickMark = eAxisTickMark.None;
chart.XAxis.MinorTickMark = eAxisTickMark.None;//修改刻度线

        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.YAxis.MinorTickMark = eAxisTickMark.None;
chart.XAxis.MinorTickMark = eAxisTickMark.None;//修改刻度线

        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
{
/ 数据开始单元格
//public string XStartCell { get; set; }
// /// 数据结束单元格
//public string XEndCell { get; set; }
// /// Y开始单元格
//public string YStartCell { get; set; }
// /// Y结束单元格
//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; }

}