将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) { } }