/*
********************************************************************************
*
* Project A report project
* Module Name Excel Report
* Author Zhou, Jin-Qiao (周金桥)
* Creation Date [11/03/2013]
* Description Generate Excel Report with Chat demo by Epplus
*
* Copyright 2013 zhoufoxcn.
*
********************************************************************************
*/
using
System;
using
System.Data;
using
System.Drawing;
using
System.IO;
using
OfficeOpenXml;
using
OfficeOpenXml.Drawing.Chart;
using
OfficeOpenXml.Style;
namespace
ExcelReportApplication
{
/// <summary>
/// 使用EPPlus生成带图表(Chart)的Excel文件的例子,注意在运行的机器上无需安装Office,因为EPPlus是使用基于OpenXML技术生成的Excel文件。
/// 任何网站和个人均可在不对本代码做任何修改的情况下转载本文及本文中示例的代码用于非商业用途,任何除去版权的行为均为侵权。
/// 作者:周公(周金桥)
/// 创建日期:2013-11-03
/// 博客地址:http://blog.csdn.net/zhoufoxcn http://zhoufoxcn.blog.51cto.com
/// 新浪微博地址:http://weibo.com/zhoufoxcn
public
class
ExcelExportPage
{
private
static
readonly
string
[] MonthNames =
new
string
[] {
"一月"
,
"二月"
,
"三月"
,
"四月"
,
"五月"
,
"六月"
,
"七月"
,
"八月"
,
"九月"
,
"十月"
,
"十一月"
,
"十二月"
};
//private static readonly string[] CommpanyNames = new string[] { "Microsoft", "IBM", "Oracle", "Amazon", "Google", "Facebook", "Twitter", "Paypal", "Yahoo", "HP" };
private
static
readonly
string
[] CommpanyNames =
new
string
[] {
"Microsoft"
,
"IBM"
,
"Oracle"
,
"Google"
,
"Yahoo"
,
"HP"
};
static
void
Main(
string
[] args)
{
ExcelExportPage.GenerateExcelReport();
}
/// <summary>
/// 周公(周金桥)说明:这个方法就是主要演示如何生成带图表(Chart)的Excel文件的例子
/// </summary>
public
static
void
GenerateExcelReport()
{
string
fileName =
"ExcelReport-"
+DateTime.Now.ToString(
"yyyy_MM_dd_HHmmss"
) +
".xlsx"
;
string
reportTitle =
"2013年度五大公司实际情况与原计划的百分比"
;
FileInfo file =
new
FileInfo(
"C:\\"
+fileName);
using
(ExcelPackage package =
new
ExcelPackage(file))
{
ExcelWorksheet worksheet =
null
;
ExcelChartSerie chartSerie =
null
;
ExcelLineChart chart =
null
;
#region research
worksheet = package.Workbook.Worksheets.Add(
"Data"
);
DataTable dataPercent = GetDataPercent();
//chart = Worksheet.Drawings.AddChart("ColumnStackedChart", eChartType.Line) as ExcelLineChart;
chart = worksheet.Drawings.AddChart(
"ColumnStackedChart"
, eChartType.LineMarkers)
as
ExcelLineChart;
//设置图表样式
chart.Legend.Position = eLegendPosition.Right;
chart.Legend.Add();
chart.Title.Text = reportTitle;
//设置图表的名称
//chart.SetPosition(200, 50);//设置图表位置
chart.SetSize(800, 400);
//设置图表大小
chart.ShowHiddenData =
true
;
//chart.YAxis.MinorUnit = 1;
chart.XAxis.MinorUnit = 1;
//设置X轴的最小刻度
//chart.DataLabel.ShowCategory = true;
chart.DataLabel.ShowPercent =
true
;
//显示百分比
//设置月份
for
(
int
col = 1; col <= dataPercent.Columns.Count; col++)
{
worksheet.Cells[1, col].Value = dataPercent.Columns[col - 1].ColumnName;
}
//设置数据
for
(
int
row = 1; row <= dataPercent.Rows.Count; row++)
{
for
(
int
col = 1; col <= dataPercent.Columns.Count; col++)
{
string
strValue = dataPercent.Rows[row - 1][col - 1].ToString();
if
(col == 1)
{
worksheet.Cells[row + 1, col].Value = strValue;
}
else
{
double
realValue =
double
.Parse(strValue);
worksheet.Cells[row + 1, col].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row + 1, col].Style.Numberformat.Format =
"#0\\.00%"
;
//设置数据的格式为百分比
worksheet.Cells[row + 1, col].Value = realValue;
if
(realValue< 0.90d)
//如果小于90%则该单元格底色显示为红色
{
worksheet.Cells[row + 1, col].Style.Fill.BackgroundColor.SetColor(Color.Red);
}
else
if
(realValue>= 0.90d && realValue <= 0.95d)
//如果在90%与95%之间则该单元格底色显示为黄色
{
worksheet.Cells[row + 1, col].Style.Fill.BackgroundColor.SetColor(Color.Yellow);
}
else
{
worksheet.Cells[row + 1, col].Style.Fill.BackgroundColor.SetColor(Color.Green);
//如果大于95%则该单元格底色显示为绿色
}
}
}
//chartSerie = chart.Series.Add(worksheet.Cells["A2:M2"], worksheet.Cells["B1:M1"]);
//chartSerie.HeaderAddress = worksheet.Cells["A2"];
//chart.Series.Add()方法所需参数为:chart.Series.Add(X轴数据区,Y轴数据区)
chartSerie = chart.Series.Add(worksheet.Cells[row + 1, 2, row + 1, 2 + dataPercent.Columns.Count - 2], worksheet.Cells[
"B1:M1"
]);
chartSerie.HeaderAddress = worksheet.Cells[row + 1, 1];
//设置每条线的名称
}
//因为假定每家公司至少完成了80%以上,所以这里设置Y轴的最小刻度为80%,这样使图表上的折线更清晰
chart.YAxis.MinValue = 0.8d;
//chart.SetPosition(200, 50);//可以通过制定左上角坐标来设置图表位置
//通过指定图表左上角所在的行和列及对应偏移来指定图表位置
//这里CommpanyNames.Length + 1及3分别表示行和列
chart.SetPosition(CommpanyNames.Length + 1, 10, 3, 20);
#endregion research
package.Save();
//保存文件
}
}
/// <summary>
/// 生成数据,由于这一步不是主要逻辑,所以采用随机生成数据的方式,实际中可根据需要从数据库或其它数据源中读取需要的数据
/// </summary>
/// <returns></returns>
private
static
DataTable GetDataPercent()
{
DataTable data =
new
DataTable();
DataRow row =
null
;
Random random=
new
Random();
data.Columns.Add(
new
DataColumn(
"公司名"
,
typeof
(
string
)));
foreach
(
string
monthName
in
MonthNames){
data.Columns.Add(
new
DataColumn(monthName,
typeof
(
double
)));
}
//每个公司每月的百分比表示完成的业绩与计划的百分比
for
(
int
i = 0; i < CommpanyNames.Length; i++)
{
row = data.NewRow();
row[0] = CommpanyNames[i];
for
(
int
j = 1; j <= MonthNames.Length; j++)
{
//这里采用了随机生成数据,但假定每家公司至少完成了计划的85%以上
row[j] = 0.85d + random.Next(0, 15) / 100d;
}
data.Rows.Add(row);
}
return
data;
}
}
}