将DateSet数据源生成Excel表格并生成统计图表
本文章以DataSet为数据源，根据该数据源将该数据源中的数据生成Excel文件，在Excel文件中不仅仅生成表格，而且每个数据表格都针对这一个统计绘图Chart
（1） 生成数据统计图表的类型：饼状图（PieChart）、线状图（LineChart）和柱状图（CulumnChart）
1 /// <summary>
2
3 ///标识绘制图表类型的枚举类
4
5 /// </summary>
6
7 public enum ChartType : int
8
9 {
10
11 /// <summary>
12
13 /// 无图表类型
14
15 /// </summary>
16
17 NoneChartType = 0,
18
19
20
21 /// <summary>
22
23 /// “饼状图表”类型
24
25 /// </summary>
26
27 PieChartType = 1,
28
29
30
31 /// <summary>
32
33 /// “柱状图表”类型
34
35 /// </summary>
36
37 ColumnChartType = 2,
38
39
40
41 /// <summary>
42
43 /// “线状图表”类型
44
45 /// </summary>
46
47 LineChartType = 3
48
49 }
（2）解析DataSet数据源中的数据，将其生成Excel文件并生成图表
/// <summary>
///标识绘制图表类型的枚举类
/// </summary>
public enum ChartType : int
{
/// <summary>
/// 无图表类型
/// </summary>
NoneChartType = 0,
/// <summary>
/// “饼状图表”类型
/// </summary>
PieChartType = 1,
/// <summary>
/// “柱状图表”类型
/// </summary>
ColumnChartType = 2,
/// <summary>
/// “线状图表”类型
/// </summary>
LineChartType = 3
}
/// <summary>
/// 导出GridView控件中的数据，并在Excel文件中生成图表
/// </summary>
public class ExportExcelAction
{
/// <summary>
/// 私有变量，待绘制图表对象的集合
/// </summary>
private List<DataChartObject> _DataChartObjectList;
/// <summary>
/// 私有变量，输出控制
/// </summary>
private System.Web.HttpResponse _Response;
/// <summary>
/// 私有变量，用于存储生成的临时文件的完整路径
/// </summary>
private string FileName;
/// <summary>
/// 私有变量，用于存储生成的Excel文件的文件信息
/// </summary>
private FileInfo _FileInfo;
/// <summary>
/// 私有变量，系统服务工具类对象
/// </summary>
private HttpServerUtility _HttpServerUtility;
/// <summary>
///私有变量，记录当前向Excel文件写数据写到了第几数据行 ,默认：第一行
/// </summary>
private int _CurrentRowIndex = 1;
/// <summary>
/// 私有变量，用于表述生成图表的左边起始位置（单元格编号）
/// </summary>
private int _StartColumnIndex = 2;
/// <summary>
/// 私有变量，记录绘图区域跨行的行数
/// </summary>
private int _ChartAreaRowSpan = 20;
/// <summary>
/// 私有变量，记录电子表格与图表下端的距离行数
/// </summary>
private int _TableAreaRowSpan = 3;
/// <summary>
/// 私有变量，设置绘图区域的宽度
/// </summary>
private double _ChartAreaWidth = 450;
/// <summary>
/// 私有变量，获取或设置绘图区域的高度
/// </summary>
private object _ChartAreaHeigth
{
get
{
this._Range = this._Worksheet.get_Range(this._Worksheet.Cells[this._CurrentRowIndex, 1],this._Worksheet.Cells[this._CurrentRowIndex + this._ChartAreaRowSpan - 1, 1]);
this._Range.RowHeight = this._FooterRowHeigth;
return this._Range.Height;
}
}
/// <summary>
/// 私有变量，记录生成Excel文件的单元格的宽度
/// </summary>
private double _ColumnsWidth;
/// <summary>
/// 公有变量，记录生成Excel文件的单元格宽度
/// </summary>
public double ColumnsWidth
{
get
{
return this._ColumnsWidth;
}
set
{
this._ColumnsWidth = value;
}
}
/// <summary>
/// 私有变量，记录生成Excel表格时，标题栏目的行高
/// </summary>
private double _HeaderRowHeigth;
/// <summary>
/// 公有变量，记录生成Excel表格时，标题栏目的行高
/// </summary>
public double HeaderRowHeigth
{
get
{
return this._HeaderRowHeigth;
}
set
{
this._HeaderRowHeigth = value;
}
}
/// <summary>
/// 私有变量，记录生成Excel表格时，内容栏目的行高
/// </summary>
private double _ContentRowHeigth;
/// <summary>
/// 公有变量，记录生成Excel表格时，内容栏目的行高
/// </summary>
public double ContentRowHeigth
{
get
{
return this._ContentRowHeigth;
}
set
{
this._ContentRowHeigth = value;
}
}
/// <summary>
/// 私有变量，设置普通单元格的行高
/// </summary>
private double _FooterRowHeigth;
/// <summary>
/// 公有变量，设置普通单元格的行高
/// </summary>
public double FooterRowHeigth
{
get
{
return this._FooterRowHeigth;
}
set
{
this._FooterRowHeigth = value;
}
}
/// <summary>
/// 私有变量，记录生成Excel表格时，表格标题栏目的文字的大小
/// </summary>
private int _HeaderTitleFontSize;
/// <summary>
/// 公有变量，记录生成Excel表格时，表格标题栏目的文字的大小
/// </summary>
public int HeaderTitleFontSize
{
get
{
return this._HeaderTitleFontSize;
}
set
{
this._HeaderTitleFontSize = value;
}
}
/// <summary>
/// 私有变量，记录生成Excel表格时，表格内容的文字的大小
/// </summary>
private int _ContentTextFontSize;
/// <summary>
/// 公有变量，记录生成Excel表格时，表格内容的文字的大小
/// </summary>
public int ContentTextFontSize
{
get
{
return this._ContentTextFontSize;
}
set
{
this._ContentTextFontSize = value;
}
}
/// <summary>
/// 私有变量，用以记录该绘图区域距左边距的距离，数据类型为Object类型
/// </summary>
private object _ToLeftValue
{
get
{
return this._Worksheet.get_Range(this._Worksheet.Cells[1, 1],this._Worksheet.Cells[1, this._StartColumnIndex - 1]).Width;
}
}
/// <summary>
/// 私有变量，用以巨鹿该绘图区域距顶边距的距离，数据类型为Object类型
/// </summary>
private object _ToTopValue
{
get
{
return this._Worksheet.get_Range(this._Worksheet.Cells[1, 1],this._Worksheet.Cells[this._CurrentRowIndex - 1, 1]).Height;
}
}
/// <summary>
/// 私有变量，生成Excel文件的应用服务对象
/// </summary>
private Application _Application;
/// <summary>
/// 私有变量，生成Excel文件时，Excel文件的工作簿
/// </summary>
private Workbook _Workbook;
/// <summary>
/// 私有变量，生成Excel文件时，Excel文件的工作簿中的时间表
/// </summary>
private Worksheet _Worksheet;
/// <summary>
/// 私有变量，Excel文件的单元格序列
/// </summary>
private Range _Range;
/// <summary>
/// 私有变量，用于标识绘制图表的区域
/// </summary>
private Shape _Shape;
/// <summary>
/// 私有变量，用于标识绘制的图表对象
/// </summary>
private Chart _Chart;
/// <summary>
/// 私有变量，用于表述图表单元序列
/// </summary>
private Series _Series;
/// <summary>
/// 私有变量，用于表述图表的坐标系轴
/// </summary>
private Axis _Axis;
/// <summary>
/// 私有变量，用于表述图表单元点
/// </summary>
private Point _p;
/// <summary>
/// 构造函数，构造参数为待绘制图表对象的集合
/// </summary>
/// <param name="myDataChartObjectList">构造参数：待绘制图表对象的集合</param>
public ExportExcelAction(List<DataChartObject> myDataChartObjectList,System.Web.HttpResponse myResponse,System.Web.HttpServerUtility myHttpServerUtility)
{
this._DataChartObjectList = myDataChartObjectList;
this._Response = myResponse;
this._HttpServerUtility = myHttpServerUtility;
this.FileName = this._HttpServerUtility.MapPath("/")+DateTime.Now.ToEnCodeLongString() + ".xls";
}
/// <summary>
/// 绘制图表并将其导出到本地文件
/// </summary>
public void ExportAction()
{
//[新建Excel文件生成程序]
this._Application = new Application();
//[向实例对象中插入一个工作簿]
this._Application.Workbooks.Add(true);
//[从Excel文件中提取一个工作簿]
this._Workbook = this._Application.Workbooks[1];
//[从Excel文件中提取活动的工作表]
this._Worksheet = this._Workbook.ActiveSheet as Worksheet;
//[设置生成Excel文件后不进行预览操作]
this._Application.Visible = false;
//[开始向Excel文件中写入数据]
this._DataChartObjectList.ForEach(e =>
{
//[获取数据源]
DataSet ds = e.DataSetName;
//[添加电子表格的标题]
this._Range = this._Worksheet.get_Range(this._Worksheet.Cells[this._CurrentRowIndex, this._StartColumnIndex],this._Worksheet.Cells[this._CurrentRowIndex, 5]);
this._Range.ColumnWidth = this.ColumnsWidth;
this._Range.RowHeight = this._HeaderRowHeigth;
this._Range.Merge(null);
this._Worksheet.Cells[this._CurrentRowIndex, 2] = e.TitleName;
//[设置单元中的文字字体为“华文仿宋”]
this._Range.Font.Name = "华文仿宋";
//[设置单元格中的文字大小为12磅]
this._Range.Font.Size = this._HeaderTitleFontSize;
//[设置单元格中的文字为加粗状态]
this._Range.Font.Bold = true;
this._CurrentRowIndex++;
//[创建“电子表格”的“标题”]
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
this._Worksheet.Cells[this._CurrentRowIndex, i + this._StartColumnIndex]= ds.Tables[0].Columns[i].ColumnName;
}
//[设置选中单元格序列的样式：标题单元格序列]
this._Range = this._Worksheet.get_Range(this._Worksheet.Cells[this._CurrentRowIndex, this._StartColumnIndex],this._Worksheet.Cells[this._CurrentRowIndex, ds.Tables[0].Columns.Count +this._StartColumnIndex - 1]);
//[选中全部的单元格序列]
this._Range.Select();
//[设置文字在单元格中水平居中]
this._Range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
//[设置文字在单元格中垂直居中]
this._Range.HorizontalAlignment = XlVAlign.xlVAlignCenter;
//[设置单元格的宽度：为20毫米]
this._Range.ColumnWidth = 20;
//[设置单元格的行高：]
this._Range.RowHeight = this._HeaderRowHeigth;
//[设置单元中的文字字体为“华文仿宋”]
this._Range.Font.Name = "华文仿宋";
//[设置单元格中的文字大小为12磅]
this._Range.Font.Size = this._HeaderTitleFontSize;
//[设置单元格中的文字为加粗状态]
this._Range.Font.Bold = true;
//[设置单元格的背景颜色]
this._Range.Borders.Value = 1;
//[设置该单元格内部的颜色]
this._Range.Interior.Color =System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(58, 196, 201));
//[数据行递增一行]
this._CurrentRowIndex++;
//[向“电子表格”中写入数据]
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
this._Worksheet.Cells[this._CurrentRowIndex, j + this._StartColumnIndex]= ds.Tables[0].Rows[i][j].ToString();
}
//[设置选中单元格序列的样式：内容单元格序列]
this._Range = this._Worksheet.get_Range(this._Worksheet.Cells[this._CurrentRowIndex, this._StartColumnIndex],this._Worksheet.Cells[this._CurrentRowIndex, ds.Tables[0].Columns.Count +this._StartColumnIndex - 1]);
//[选中全部的单元格序列]
this._Range.Select();
//[设置单元格的宽度]
this._Range.ColumnWidth = this._ColumnsWidth;
//[设置单元格的行高]
this._Range.RowHeight = this._ContentRowHeigth;
//[设置单元格内文字的字体]
this._Range.Font.Name = "新宋体";
//[设置单元格内文字的大小]
this._Range.Font.Size = this._ContentTextFontSize;
//[设置单元格内文字的水平方位]
this._Range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
//[设置单元格内文字的垂直方位]
this._Range.HorizontalAlignment = XlVAlign.xlVAlignCenter;
//[设置单元格的边框]
this._Range.Borders.Value = 1;
this._CurrentRowIndex++;
}
//[设置图表与表格之间的间隙]
this._CurrentRowIndex++;
this._Range = this._Worksheet.get_Range(this._Worksheet.Cells[this._CurrentRowIndex - 1, this._StartColumnIndex],this._Worksheet.Cells[this._CurrentRowIndex - 1,e.DataSetName.Tables[0].Columns.Count+ this._StartColumnIndex - 1]);
this._Range.RowHeight = this._FooterRowHeigth;
//[绘制“电子表格”对应的“图表”]
switch (e.ChartTypeName)
{
case ChartType.NoneChartType:
{
break;
}
case ChartType.PieChartType:
{
this.DrawPieChartAction(e);
break;
}
case ChartType.ColumnChartType:
{
this.DrawColumnChartAction(e);
break;
}
case ChartType.LineChartType:
{
this.DrawLineChartAction(e);
break;
}
default:
{
break;
}
}
//[设置该“电子表格”或者“图表”与下一个“电子表格”或者“图表”之间的空隙]
this._CurrentRowIndex += this._TableAreaRowSpan;
this._Range = this._Worksheet.get_Range(this._Worksheet.Cells[this._CurrentRowIndex - this._TableAreaRowSpan - 1, 1],this._Worksheet.Cells[this._CurrentRowIndex, 1]);
this._Range.RowHeight = this._FooterRowHeigth;
});
//[将生成的Excel文件保存到指定的目录中]
this._Workbook.SaveCopyAs(this.FileName);
//[将文件导入到文件流中]
this._FileInfo = File.Exists(this.FileName) ? new FileInfo(this.FileName) : null;
//[关闭工作簿]
this._Workbook.Close(false, null, null);
//[关闭Excel生成应用程序]
this._Application.Quit();
//[清理Com端口]
System.Runtime.InteropServices.Marshal.ReleaseComObject(this._Axis);
//[清理Com端口]
System.Runtime.InteropServices.Marshal.ReleaseComObject(this._p);
//[清理Com端口]
System.Runtime.InteropServices.Marshal.ReleaseComObject(this._Series);
//[清理Com端口]
System.Runtime.InteropServices.Marshal.ReleaseComObject(this._Range);
//[清理Com端口]
System.Runtime.InteropServices.Marshal.ReleaseComObject(this._Chart);
//[清理Com端口]
System.Runtime.InteropServices.Marshal.ReleaseComObject(this._Shape);
//[清理Com端口]
System.Runtime.InteropServices.Marshal.ReleaseComObject(this._Worksheet);
//[清理Com端口]
System.Runtime.InteropServices.Marshal.ReleaseComObject(this._Workbook);
//[清理Com端口]
System.Runtime.InteropServices.Marshal.ReleaseComObject(this._Application);
//[将文件从“服务端”导出到“客户端”]
this._Response.Clear();
//[设置写入流的字符编码方式为GB2312]
this._Response.Charset = "GB2312";
//[设置写入流的文字编码格式：UTF8]
this._Response.ContentEncoding = System.Text.Encoding.UTF8;
// 添加头信息，为"文件下载/另存为"对话框指定默认文件名
this._Response.AddHeader("Content-Disposition","attachment;filename=" + this._HttpServerUtility.UrlEncode(this._FileInfo.Name));
// 添加头信息，指定文件大小，让浏览器能够显示下载进度
this._Response.AddHeader("Content-Length", this._FileInfo.Length.ToString());
// 指定返回的是一个不能被客户端读取的流，必须被下载
this._Response.ContentType = "application/ms-excel";
// 把文件流发送到客户端
this._Response.WriteFile(this._FileInfo.FullName);
// 停止页面的执行
this._Response.End();
//[关闭文件流]
File.Delete(this.FileName);
}
/// <summary>
/// 根据“数据源”绘制“饼状图”
/// </summary>
/// <param name="ds">数据源</param>
private void DrawPieChartAction(DataChartObject myDataChartObject)
{
//[获取绘制饼状图的数据源]
DataSet ds = myDataChartObject.DataSetName;
//[获取数据源所持有数据行的行数]
int RowsCount = ds.Tables[0].Rows.Count;
//[获取数据源所持有数据列的列数]
int ColumnsCount = ds.Tables[0].Columns.Count;
//[获取绘图区域]
this._Shape = this._Worksheet.Shapes.AddChart(XlChartType.xl3DPie,this._ToLeftValue,this._ToTopValue,this._ChartAreaWidth,this._ChartAreaHeigth);
//[获取绘图对象]
this._Chart = this._Shape.Chart;
//[获取绘制图表的数据来源]
this._Range = this._Worksheet.get_Range(this._Worksheet.Cells[this._CurrentRowIndex - RowsCount - 1,this._StartColumnIndex + myDataChartObject.StartIndex],this._Worksheet.Cells[this._CurrentRowIndex - RowsCount - 1,myDataChartObject.StopIndex + this._StartColumnIndex]);
this._Chart.SetSourceData(this._Range, XlRowCol.xlColumns);
this._Chart.SetSourceData(this._Range, XlRowCol.xlRows);
//[更改图例文字]
this._Series = this._Chart.SeriesCollection(1) as Series;
this._Series.Name = myDataChartObject.TitleName;
//[获取图例上的文字数组]
Array myArray = this._Series.XValues as Array;
//[遍历数据源，对“图例文字”和“数据模块”进行装饰]
for (int i = myDataChartObject.StartIndex; i <= myDataChartObject.StopIndex; i++)
{
//[设置饼图每个数据块]
if (Int32.Parse(ds.Tables[0].Rows[0][i].ToString()) != 0)
{
this._p = this._Series.Points(i - myDataChartObject.StartIndex + 1) as Point;
this._p.HasDataLabel = true;
this._p.DataLabel.Text =
ds.Tables[0].Columns[i].ColumnName + "：" + ds.Tables[0].Rows[0][i].ToString();
}
//[设置每个“图例标签”]
myArray.SetValue(ds.Tables[0].Columns[i].ColumnName,i - myDataChartObject.StartIndex + 1);
}
//[将图例返回Chart对象序列]
this._Series.XValues = myArray;
//[记录当前的索引行数递增20行]
this._CurrentRowIndex += this._ChartAreaRowSpan;
//[设置生成的Excel图表的水平旋转角度，零度]
this._Chart.Rotation = 40;
//[设置生成的柱状图禁止选择]
this._Chart.ProtectSelection = false;
//[设置生成的柱状图禁止修改数据点]
this._Chart.ProtectGoalSeek = false;
//[防止修改序列公式]
this._Chart.ProtectData = false;
//[防止修改格式设置]
this._Chart.ProtectFormatting = false;
}
/// <summary>
/// 根据“数据源”绘制“柱状图”
/// </summary>
/// <param name="ds">数据源</param>
private void DrawColumnChartAction(DataChartObject myDataChartObject)
{
//[获取绘制柱状图的“数据源”]
DataSet ds = myDataChartObject.DataSetName;
//[记录该“数据源”含有数据的“行数”]
int RowsCount = ds.Tables[0].Rows.Count;
//[记录该“数据源”含有数据的“列数”]
int ColumnsCount = ds.Tables[0].Columns.Count;
//[获取绘制图表的“画板区域”]
this._Shape = this._Worksheet.Shapes.AddChart(XlChartType.xl3DColumn,this._ToLeftValue, _ToTopValue,this._ChartAreaWidth,this._ChartAreaHeigth);
//[获取绘制图表的“绘画控件”]
this._Chart = this._Shape.Chart;
//[获取绘制图表的数据来源]
this._Range = this._Worksheet.get_Range(this._Worksheet.Cells[this._CurrentRowIndex - RowsCount - 1,this._StartColumnIndex + myDataChartObject.StartIndex],this._Worksheet.Cells[this._CurrentRowIndex - RowsCount - 1,this._StartColumnIndex + myDataChartObject.StopIndex]);
this._Chart.SetSourceData(this._Range, XlRowCol.xlRows);
this._Chart.SetSourceData(this._Range, XlRowCol.xlColumns);
//[设置生成的Excel图表的X轴旋转角度，270]
this._Chart.Rotation = 270;
//[设置生成的Excel图表的Y轴旋转角度，10]
this._Chart.Elevation = 10;
//[设置生成的Excel图表的透视角度]
this._Chart.Perspective = 30;
//[设置生成的柱状图禁止选择]
this._Chart.ProtectSelection = false;
//[设置生成的柱状图禁止修改数据点]
this._Chart.ProtectGoalSeek = false;
//[防止修改序列公式]
this._Chart.ProtectData = false;
//[防止修改格式设置]
this._Chart.ProtectFormatting = false;
//[记录当前的索引行数递增20行]
this._CurrentRowIndex += this._ChartAreaRowSpan;
for (int i = myDataChartObject.StartIndex; i <= myDataChartObject.StopIndex; i++)
{
//[设置每个标签]
this._Series =this._Chart.SeriesCollection(i - myDataChartObject.StartIndex + 1) as Series;
this._Series.Name = ds.Tables[0].Columns[i].ColumnName;
this._Series.MarkerStyle = XlMarkerStyle.xlMarkerStylePicture;
//[设置每个柱状图]
this._p = this._Series.Points(1) as Point;
this._p.HasDataLabel = true;
this._p.DataLabel.Text = ds.Tables[0].Rows[0][i].ToString();
}
//[设置Y值轴]
this._Axis = this._Chart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary) as Axis;
this._Axis.HasTitle = true;
this._Axis.AxisTitle.Text = myDataChartObject.YTitleName;
this._Axis.HasDisplayUnitLabel = true;
//[设置X类别轴]
this._Axis = this._Chart.Axes(XlAxisType.xlSeriesAxis, XlAxisGroup.xlPrimary) as Axis;
this._Axis.HasTitle = true;
this._Axis.AxisTitle.Text = myDataChartObject.XTitleName;
}
/// <summary>
/// 根据“数据源”绘制“线状图”
/// </summary>
/// <param name="ds">数据源</param>
private void DrawLineChartAction(DataChartObject myDataChartObject)
{
}
}
