在NET中不安装Office使用EPPlus生成带图表的Excel报表.doc

在NET中不安装Office使用EPPlus生成带图表的Excel报表正式版

在.NET中不安装Office使用EPPlus生成带图表(Chart)的Excel报表
在开发.NET应用中可能会遇到需要生成带图表(Chart)的Excel报表的需求，特别是在一些ASP.NET网站中，有时候我们并不能保证Web服务器上一定安装了Office组件，所以使用微软的Office来生成Excel并不保证在所有情况下都使用，有时候即使Web服务器上安装了Office也会出现一些运行权限方面的原因到导致调用Excel组件生成Excel失败，所以在这里介绍一种无需安装Office并且无需较高权限就能生成Excel的方法。
EPPlus简介
在介绍EPPlus之前，首先要介绍一下Office Open XML。以下文字来自于维基百科（ ，有删节）：
Office Open XML（缩写：Open XML、OpenXML或OOXML），是由Microsoft开发的一种以XML为基础并以ZIP格式压缩的电子文件，支持Word、Excel、Office Note、PPT等文件格式。OOXML在2006年12月成为了ECMA规范的一部分，编号为ECMA-376；并于2021年4月国际标准化组织(ISO)的表決，在两个月公布为ISO／IEC 29500国际标准。从Microsoft Office 2007开始，Office Open XML文件格式已经成为Microsoft Office默认的文件格式。Microsoft Office 2021支持对ECMA-376标准文档的读操作，ISO/IEC 29500 Transitional的读/写，ISO/IEC 29500 Strict的读取。Microsoft Office 2021同时支持ISO/IEC 29500 Strict的读写操作。
EPPlus就是一个通过Open XML方式来读写Office文件的开源.NET类库，所以使用它生成Office文件完全不需要Microsoft Office（当然如果你需需要查看生成的文件就需要Office了）。它的官方网址是：。如果需要体验本文中提到的效果，需要从这个网址下载最新版本的类库，我现在使用的这个名为EPPlus.dll类库仅658K，非常方便部署。
代码示例
为了演示EPPlus的用法，这里写了一个简单的例子，在这个例子里演示模拟了几大公司实际业绩与计划业绩的百分比，如果这个百分比大于95%则会将所在的单元格显示为绿色，如果小于90%则会显示为红色，否则就显示为黄色，并且还会生成一个图标来直观表示每月实际完成情况与计划的百分比。
完整代码如下：
/*
********************************************************************************
*
* Project A report project
* Module Name Excel Report
* Author Zhou, Jin-Qiao (周金桥)
* Creation Date [11/03/2021]
* Description Generate Excel Report with Chat demo by Epplus
*
* © Copyright 2021 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文件。
/// 任何网站和个人均可在不对本代码做任何修改的情况下转载本文及本文中示例的代码用于非商业用途，任何除去版权的行为均为侵权。
/// 周公(周金桥)
/// 创建日期：2021-11-03
/// 博客地址： :// ://zhoufoxcn.blog.51cto
/// 新浪微博地址： ://weibo /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 = "2021年度五大公司实际情况与原计划的百分比";
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;
}
}
}
最终生成的Excel文件内容如下：

周公（周金桥）
2021年11月4日凌晨于武汉
[任务单5-1]
项目五 报表系统处理学时8
任务5.1
报表系统概述及利用报表模板生成报表
学时
4
一、学习目标
通过学习，掌握报表系统中常用的基本概念及操作流程，理解报表系统的功能，利用报表模板生成报表。
二、学习资源
1.用友U8v.10软件
2.操作视频：启动报表系统、展示报表功能，利用报表模板生成报表的操作视频。
三、学习方法
1、 认真观看视频并记录重点
2、 四人组成一个学习组讨论交流
四、准备工作：
1、准备一个剩余空间不小于2G的U盘
2、修改计算系统时间为2016年1月31日
3、引入“学习任务4-4期末账务处理”账套备份
五、学习任务
（一）“101李伟”启用报表、了解报表功能
（二）“101李伟”调用报表模板生成资产负债表
1、调用行业为“2007新会计制度科目”，选择财务报表为“资产负债表”模板
2、在数据状态下输入关键字：年2021，月1，日31
3、在格式状态下，双击存货的期初余额计算公式栏，并在弹出的定义公式栏中增加QC("1409",全年,,,年,,)；双击存货的期末余额计算公式栏，并在弹出的定义公式栏中增加QM("1409",全年,,,年,,)
4、将格式状态转化为数据状态，进行全表重算，生成资产负债表数据：资产总计期初数据为（5715741.00），期末数据为（6537171.00）；负债及所有者权益期初数据为（5715741.00），期末数据为（6537171.00）
5、保存报表
（1）以文件名为资产负债表，Excle的形式保存到E:/2021账套备份文件中。
（2）以文件名为资产负债表，.REP的形式保存到E:/2021账套备份文件中。
（三）“101李伟”调用报表模板生成利润表
1、调用行业为“2007新会计制度科目”，选择财务报表为“利润表”模板
2、在数据状态下输入关键字：输入关键字：年2021，月1
3、生成利润表数据：净利润数据为（718240.00）
4、保存报表
（1）以文件名为利润表，Excle的形式保存到E:/2021账套备份文件中。
（2）以文件名为利润表，.REP的形式保存到E:/2021账套备份文件中。
（四）利用总账的项目核算生成现金流量表
1、在设置会计科目界面指定现金流量科目：将1001库存现金、10 1中国工商银行、10 2建设银行、1003存放中央银行款项、1011存放同业、1012其他货币资金、1021结算备付金、1031存出保证金等科目从待选科目移至已选科目
2、系统在项目目录里已经建立了“现金流量项目”项目大类
3、执行［现金流量表]→［现金流量凭证查询]命令，进入［现金流量查询及修改]窗口，针对每一张现金流量凭证，单击“修改”按钮补充录入现金流量项目，如下表：
摘要
科目编码
科目名称
项目名称
方向
金额
报销差旅费
1001
库存现金
收的的其他与经营活动的现金
借
240
收到货款
10 1
中国工商银行
销售商品、提供劳务收到的现金
借
30000
支付广告费
10 1
中国工商银行
支付的与其他经营活动有关的现金
贷
20000
采购货物
10 1
中国工商银行
购买商品、接受劳务支付的现金
贷
155610
4、调用行业为“2007新会计制度科目”，选择财务报表为“现金流量表”模板
5、在格式状态下，单击单元格C6，打开fx函数选择用友账务函数对应的“现金流量项目金额”，在“参照”窗口下完善“项目编码”内容，方向为“流入”。同理，完成C8、C10、C13单元格公式。
6、在数据状态下，输入关键字：年2021，月1
7、生成现金流量表数据
（四）账套备份
在E盘根目录下建立一个文件夹，文件夹的名字为“216账套备份”，在该文件中建立一个名为“报表系统概述及利用报表模板生成报表”的子文件夹，将账套备份到该子文件夹中。

[信息页5-1]
项目五 报表系统处理
任务5.1报表系统概述及利用报表模板生成报表
理论目标：
掌握报表系统的主要功能
掌握报表系统的操作流程
掌握报表系统的基本概念
任务目标：
熟练地启用报表系统
熟练地掌握报表模板的使用修改以及报表生成等操作方法
熟练地引入、备份账套

一、报表系统的功能

利用UFO报表系统既能编制对外报表，又可编制各种内部报表。UFO报表系统的主要功能有：提供各行业报表模板，文件管理功能，格式设计功能，公式设计功能，数据处理功能，图表功能，打印功能，二次开发功能。
报表模板、格式管理、数据处理、打印是常用到的最主要功能。可以自行设计报表格式，也可以根据报表系统提供的报表模板自动生成报表格式。还可以将报表结果另存为Excel，使用Excel功能对数据进行加工处理。

（一） 功能概述
1、文件管理功能
UFO提供了各类文件管理功能，除能完成一般的文件管理外，UFO的数据文件还能够转化为不同的文件格式，例如文本文件、MDB文件、XLS文件等。此外，通过UFO提供的“导入”和“导出”功能，可以实现和其他流行财务软件之间的数据交换。
2、格式设计功能
UFO提供的格式设计功能，可以设置报表尺寸、组合单元、画表格线、调整行高列宽、设置字体和颜色、设置显示比例等。同时，UFO还内置了11种套用格式和33个行业的标准财务报表模板，包括最新的现金流量表，方便了用户标准报表的制作。对于用户单位内部常用的管理报表，UFO还提供了自定义模板功能。
3、公式设计功能
UFO提供了绝对单元公式和相对单元公式，可以方便、迅速地定义计算公式、审核公式及舍位平衡公式；UFO还提供了种类丰富的函数，在系统向导的引导下可轻松地从用友账务及其他子系统中提取数据，生成财务报表。
4、数据处理功能
UFO的数据处理功能可以固定的格式管理大量数据不同的表页，并在每张表页之间建立有机的联系。此外，还提供了表页的排序、查询、审核、舍位平衡及汇总功能。
5、图表功能
UFO可以很方便地对数据进行图形组织和分析，制作包括直方图、立体图、圆饼图、折线图等多种分析图表，并能编辑图表的位置、大小、标题、字体、颜色和打印输出。
6、打印功能
UFO提供“所见即所得”和“打印预览”的功能，可以随时观看报表或图形的打印效果。报表打印时，可以打印格式或数据，可以设置表头和表尾，可以在0.3～3倍之间缩放打印，可以横向或纵向打印等。
7、二次开发功能
UFO提供了批命令和自定义菜单，利用该功能可以开发出适合本企业的专用系统。

二、报表系统操作流程
（一）启动报表系统。
（二）创建报表文件。
（三）报表格式定义。包括设置表尺寸、画表格线、设置组合单元、输入表样文字、设置关键字位置、定义单元公式。表尺寸是报表的行数和列数。根据表格式定义是报表操作的关键，报表格式定义的关键是报表取数公式的定义。
（四）报表数据处理。包括打开报表、增加表页、录入关键字值、编制报表、审核报表。
（五）报表输出。
（六）报表分析。

三、报表系统中的基本概念

（一）报表结构
报表按其结构的复杂性，分为简单表和复合表。简单表就是由若干行和列组成的二维表。简单表的格式一般由标题、表头、表体和表尾组成，资产负债表、利润表、现金流量表都是简单表。复合表是由若干张简单表组合而成。
标题即报表的名称，表头包括编制单位、日期、计量单位、报表栏目等，报表栏目是表头中最重要的内容。表体是报表的主体，由行和列组成。表尾，即表体以下的辅助说明部分。
（二）单元、单元属性、单元风格
单元是报表中由行和列确定的方格，是组成报表的最小单位。如C2表是第二行C列对应的单元。
单元属性是指单元类型、数字格式、边框的样式。单元类型有数值型、字符型、表样型。
1、数值单元
用于存放报表的数据，在数据状态下输入。数值单元的内容可以直接输入或由单元中存放的单元公式运算生成。建立一个新表时，所有单元的类型默认为数值型。
2、字符单元
字符单元也是报表的数据，也在数据状态下输入。字符单元的内容可以直接输入，也可由单元公式生成。
3、表样公式
表样单元是报表的格式，是定义一个没有数据的空表所需的所有文字、符号、或数字。一旦单元被定义为表样，那么在其中输入的内容对所有表页都有效。表样单元只能在格式状态下输入和修改。
单元风格是指单元内容的字体、字号、字形、对应方式、颜色图案等。
（三）组合单元
组合单元由相邻的两个或更多的单元组成，这些单元必须是同一种单元类型（表样、数值、字符），UFO在处理报表时将组合单元视为一个单元，组合单元的名称可以用区域的名称或区域中的任何一个单元的名称来表示。
（四）表页
表页是由若干行和列组成的一个二维表。描述某表页某单元格的方法为：列行@页，如第2页中的C2单元的表示方法为C2@2。一个UFO报表最多可容纳99999张表页，一个报表中的所有表页具有相同的格式，但其中的数据不同。
（五）区域
区域是由一张表页上的相邻单元组成，自起点单元至终点单元是一个完整的长方形矩阵。在UFO中，区域是二维的，最大的区域是整个表页，最小的区域是一个单元。例如，A6到C10的长方形区域表示为A6：C10，起点单元与终点单元用“：”连接。
（六）固定区和可变区
固定区是组成一个区域的行数和列数是固定的。可变区是指一个区域的行数或列数是不固定的。可变区的最大值在格式设计中设定。许多情况下，报表内的记录数是不固定的，不能确定表的大小。
含有可变区的报表叫可变表，不含有可变区的表叫固定表，一个报表只能设置一个可变区，行可变，或列可变，可变区在格式状态下只显示一行或一列，在数据状态下，可变区随需要增减。
（七）关键字
关键字是游离于单元之外的特殊数据单元，可用于大量表页中快速选择表页。每个表页中可定义多个关键字，关键字一般包括：单位名称、单位编号、年、季、月、日，也可以自定义关键字，在取数公式中会使用关键字。关键字的显示位置在格式状态下设置，关键字的值则在数据状态下录入，每个报表可以定义多个关键字。
（八）格式状态和数据状态
UFO将报表制作分为两大部分来处理，即报表格式、公式设计工作与报表数据处理工作。这两部分的工作是在不同状态下进行的。
1、 格式状态
在报表格式设计状态下进行有关格式设计的操作，例如，表尺寸、行高列宽、单元属性、、单元风格、组合单元、关键字；定义报表的单元公式（计算公式）、审核公式及设为舍位平衡公式。在格式状态下所看到的是报表的格式，报表的数据全部隐藏；在格式状态下所做的操作对本报表所有的表页都发生作用；在格式状态下不能进行数据的录入、计算等操作。
2、数据状态
在报表的数据状态下管理报表的数据，例如，输入数据、增加或删除表页、审核、舍位平衡、制作图形、汇总、合并报表等。在数据状态下不能修改报表的格式，看到的时报表的全部内容，包括格式和数据。
报表工作区的左下角有一个“格式/状态”按钮，单击这个按钮可以在“格式状态”和“数据状态”之间切换。
（九）报表文件
报表文件是存储数据的基本单位，是以rep为后缀的一个文件，如：zcfzb.rep。表示某文件某表页某单元格的方法为：“路径+文件名”—>列行@页。例如d:\zcfzb.rep文件第2页C2单元格，表示方法为：“d:\zcfzb.rep”—>C2@2。
一个报表文件可以容纳多张报表（表页）。

四、调用报表模板生成资产负债表

（一）调用资产负债表模板（如图5-4-1所示）
【操作步骤】
1、以“李伟”的身份进入企业应用平台，执行[财务会计]︱“UFO报表”命令，启用UFO报表管理系统
2、在格式状态下，执行[格式]→[报表模板]命令，打开“报表模板”对话框，选择您所在的行业为“2007新会计制度科目”，财务报表为“资产负债表”。
3、单击[确认]按钮，系统弹出“模板格式将覆盖本表格式！是否继续？”信息提示对话框。
4、单击“确定”按钮，即可打开“资产负债表”模板。

图5-4-1 调用资产负债表模板

（二）调整报表模板（如图5-4-2所示）
【操作步骤】
1、执行“数据/格式”按钮，将“资产负债表”处于格式状态。
2、根据本单位的实际情况，在格式状态下将存货的期初余额计算公式中增加QC("1409",全年,,,年,,)，期末余额计算公式中增加QM("1409",全年,,,年,,)
3、保存调整后的报表模板。

图5-4-2 调整报表模板

（三）生成资产负债表数据（如图5-4-3所示）
【操作步骤】
1、在数据状态下，执行“数据”︱“关键字”︱“录入”命令，打开“录入关键字”对话框。
2、输入关键字：年2021，月1，日31。
3、单击“确认”按钮，系统弹出“是否重算第1页？”信息提示对话框。
4、单击“是”按钮，系统会自动根据单元公式计算1月份数据；单击“否”按钮，系统不计算1月份数据，以后可利用“表页重算”功能生成1月份数据。
5、单击工具栏上的“保存”按钮，将生成的报表数据保存。

图5-4-3 生成资产负债表数据

五、调用报表模板生成利润表
注意：
◆ 同样的方法，生成2021年1月份利润表。

六、利用总账的项目核算生成现金流量表
系统提供了两种生成现金流量表的方法：一是利用现金流量表模板，二是利用总账的项目管理功能和UFO报表。下面主要介绍第二种方法。
生成现金流量表之前在总账系统中需要做如下工作：
（一）在设置会计科目界面指定现金流量科目，如图5-4-4所示。

图5-4-4 指定现金流量科目
（二）系统在项目目录里已经建立了“现金流量项目”项目大类，如图5-4-5所示。

图5-4-5 现金流量项目大类及项目目录

（三）在填制凭证时如果涉及现金流量科目可以在填制凭证界面单击“流量”按钮，打开“现金流量表”对话框，指定发生的该笔现金流量的所属项目。如果在填制凭证时未指定现金流量项目，也可以执行“现金流量表”︱“现金流量凭证查询”命令，进入“现金流量查询及修改”窗口，针对每一张现金流量凭证，单击“修改”按钮补充录入现金流量项目，如图5-4-6所示。

图5-4-6 现金流量查询及修改

（四）调用现金流量表模板，在UFO报表系统中生成现金流量表。（如图5-4-7所示）
1、在“格式”状态下，执行“格式”︱“报表模板”命令，打开“报表模板”对话框。
2、选择您所在的行业为“2007新会计制度科目”，财务报表为“现金流量表”。
3、单击“确认”按钮，系统弹出“模板格式将覆盖本表格式！是否继续？”信息提示对话框。
4、单击“确定”按钮，即可打开“现金流量表”模板。

图5-4-7 调用现金流量模板
（五）调整报表模板。
1、单击“数据/格式”按钮，将“现金流量表”处于格式状态。
2、单击选择C6单元格，单击fx按钮，打开“定义公式”对话框。单击“函数向导”按钮，打开“函数向导”对话框。
3、在函数分类列表框中选择“用友账务函数”，在右边的函数名列表中选中“现金流量项目金额（XJLL）”，单击“下一步”按钮，打开“用友账务函数”对话框。
4、单击“参照”按钮，打开“账务函数”对话框。
5、单击“项目编码”右边的参照按钮，打开“现金流量项目”对话框。
6、双击选择与C6单元格左边相对应的项目，单击“确定”按钮，返回“用友账务函数”对话框。
7、单击“确定”按钮，返回“定义公式”对话框，单击“确认”按钮。
8、重复3～8步骤，输入C8、C10、C13单元格公式（注意：C6、C8单元格公式的方向为“流入”，C10、C13单元格公式的方向为“流出”）。
9、单击工具栏中的“保存”按钮，保存调整后的报表模板