原文转自: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; }
}