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", "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.LineMarkers) as ExcelLineChart;
chart.Legend.Position = eLegendPosition.Right;
chart.Legend.Add();
chart.Title.Text = reportTitle;
chart.SetSize(800, 400);
chart.ShowHiddenData = true;
chart.XAxis.MinorUnit = 1;
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)
{
worksheet.Cells[row + 1, col].Style.Fill.BackgroundColor.SetColor(Color.Red);
}
else if (realValue>= 0.90d && realValue <= 0.95d)
{
worksheet.Cells[row + 1, col].Style.Fill.BackgroundColor.SetColor(Color.Yellow);
}
else
{
worksheet.Cells[row + 1, col].Style.Fill.BackgroundColor.SetColor(Color.Green);
}
}
}
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];
}
chart.YAxis.MinValue = 0.8d;
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++)
{
row[j] = 0.85d + random.Next(0, 15) / 100d;
}
data.Rows.Add(row);
}
return data;
}
}
}
