IT知识库 购物 网址 游戏 小说 歌词 快照 开发 股票 美女 新闻 笑话 | 汉字 软件 日历 阅读 下载 图书馆 编程 China
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
图片批量下载器
↓批量下载图片,美女图库↓
图片自动播放器
↓图片自动播放器↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
vbs/VBScript DOS/BAT hta htc python perl 游戏相关 VBA 远程脚本 ColdFusion ruby专题 autoit seraphzone PowerShell linux shell Lua Golang Erlang 其它教程 CSS/HTML/Xhtml html5 CSS XML/XSLT Dreamweaver教程 经验交流 开发者乐园 Android开发资料
站长资讯 .NET新手 ASP.NET C# WinForm Silverlight WCF CLR WPF XNA VisualStudio ASP.NET-MVC .NET控件开发 EntityFramework WinRT-Metro Java C++ PHP Delphi Python Ruby C语言 Erlang Go Swift Scala R语言 Verilog 其它语言 架构设计 面向对象 设计模式 领域驱动 Html-Css JavaScript jQuery HTML5 SharePoint GIS技术 SAP OracleERP DynamicsCRM K2 BPM 信息安全 企业信息 Android开发 iOS开发 WindowsPhone WindowsMobile 其他手机 敏捷开发 项目管理 软件工程 SQLServer Oracle MySQL NoSQL 其它数据库 Windows7 WindowsServer Linux
  IT知识库 -> .NET新手区 -> 使用Epplus生成Excel 图表 -> 正文阅读

[.NET新手区]使用Epplus生成Excel 图表

使用Epplus生成Excel 图表 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
上一篇文章      下一篇文章      查看所有文章
加:2017-11-23 23:21:42  更:2017-11-23 23:21:49 
 
  .NET新手区 最新文章
将ZIP文件添加到程序集资源文件然后在运行时
Web服务的调用
.NET创建WebService服务简单的例子
多线程编程学习笔记——任务并行库(三)
序列化和反序列化
Spring学习之路
cs代码实现控件移动TranslateTransform
Asp.net基础知识
ACdream原创群赛(11)の风神日华神专场C.神奇
SQL 存储和触发器
技术频道: 站长资讯 .NET新手区 ASP.NET C# WinForm Silverlight WCF CLR WPF XNA Visual Studio ASP.NET MVC .NET控件开发 Entity Framework WinRT/Metro Java C++ PHP Delphi Python Ruby C语言 Erlang Go Swift Scala R语言 Verilog 其它语言 架构设计 面向对象 设计模式 领域驱动设计 Html/Css JavaScript jQuery HTML5 SharePoint GIS技术 SAP Oracle ERP Dynamics CRM K2 BPM 信息安全 企业信息化其他 Android开发 iOS开发 Windows Phone Windows Mobile 其他手机开发 敏捷开发 项目与团队管理 软件工程其他 SQL Server Oracle MySQL NoSQL 其它数据库 Windows 7 Windows Server Linux
脚本语言: vbs/VBScript DOS/BAT hta htc python perl 游戏相关 VBA 远程脚本 ColdFusion ruby专题 autoit seraphzone PowerShell linux shell Lua Golang Erlang 其它教程
网站开发: CSS/HTML/Xhtml html5 CSS XML/XSLT Dreamweaver教程 经验交流 开发者乐园 Android开发资料
360图书馆 软件开发资料 文字转语音 购物精选 软件下载 新闻资讯 小游戏 Chinese Culture 股票 三丰软件 开发 中国文化 网文精选 阅读网 看图 日历 万年历 2018年10日历
2018-10-24 3:20:42
多播视频美女直播
↓电视,电影,美女直播,迅雷资源↓
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
图片批量下载器
↓批量下载图片,美女图库↓
  网站联系: qq:121756557 email:121756557@qq.com  IT知识库