[转] C#操作EXCEL,生成图表的全面应用

gailzhao 原文 关于C#操作EXCEL,生成图表的全面应用

 

　　近来我在开发一个运用C#生成EXCEL文档的程序，其中要根据数据生成相应的图表，该图表对颜色和格式都有严格的要求，在百度和谷歌中搜索了所有的相关信息，只有部分介绍，具体格式的介绍没有，经过我不断的实践和探索，终于完成了这项艰巨的任务。

　　有两种实现方式，一种是利用OWC11组件完成，一种运用Excel完成！

　　运用OWC11的完成，适合生成一个图形文件，之后不能不在文件中编辑；运用Excel则更适合利用EXCEL文件中的数据直 接在文件中加入图表，方便以后编辑！两种我都尝试了，由于我更适合于使用第二种，所以我开发的较完善。在这里公布源码供大家参考使用！

　　实例问题： 在EXCEL文档中生成如下数据（部分，数据中是日期对应的两种数据），在数据右侧根据数据生成图表。基金净值指数图表如下。

开放式基金	2008-1-2	5158.0456
4246.8861	2008-1-4	5214.2867
4325.5252	2008-1-8	5252.3962

 

 一、利用Microsoft.Office.Interop.Excel组件

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190

using  System.IO; using  System.Runtime.InteropServices; using  Excel = Microsoft.Office.Interop.Excel;     private  void  Btn_Click( object  sender, EventArgs e) {            //创建一个EXCEL文档          CreateExcel( "标题" , "文档.XLS" ,  "工作表名" ); }   private  void  CreateExcel( string  title,   string  fileName,  string  sheetNames) {              //待生成的文件名称              string  FileName = fileName;              string  FilePath = strCurrentPath + FileName;                FileInfo fi =  new  FileInfo(FilePath);              if  (fi.Exists)      //判断文件是否已经存在,如果存在就删除!              {                  fi.Delete();              }              if  (sheetNames !=  null  && sheetNames !=  "" )              {                  Excel.Application m_Excel =  new  Excel.Application(); //创建一个Excel对象(同时启动EXCEL.EXE进程)                  m_Excel.SheetsInNewWorkbook = 1; //工作表的个数                  Excel._Workbook m_Book = (Excel._Workbook)(m_Excel.Workbooks.Add(Missing.Value)); //添加新工作簿                  Excel._Worksheet m_Sheet;                      #region 处理                    DataSet ds= ScData.ListData( "exec Vote_2008.dbo.P_VoteResult_Update "  +  int .Parse(fdate));                  if  (ds.Tables.Count<= 0)                  {                          MessageBox.Show( "没有最新数据!" );                          return ;                   }                   DataTableToSheet(title,  ds.Tables[0], m_Sheet, m_Book, 0);                  #endregion                         #region 保存Excel,清除进程                  m_Book.SaveAs(FilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,   Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);                  //m_Excel.ActiveWorkbook._SaveAs(FilePath, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);                  m_Book.Close( false , Missing.Value, Missing.Value);                  m_Excel.Quit();                  System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Book);                  System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Excel);                    m_Book =  null ;                  m_Sheet =  null ;                  m_Excel =  null ;                  GC.Collect();                  //this.Close();//关闭窗体                    #endregion              }     }            #region 将DataTable中的数据写到Excel的指定Sheet中          /// <summary>          /// 将DataTable中的数据写到Excel的指定Sheet中          /// </summary>          /// <param name="dt"></param>          /// <param name="m_Sheet"></param>          public  void  DataTableToSheet( string  title, DataTable dt, Excel._Worksheet m_Sheet,                                      Excel._Workbook m_Book,    int  startrow)          {                //以下是填写EXCEL中数据              Excel.Range range = m_Sheet.get_Range(m_Sheet.Cells[1, 1], m_Sheet.Cells[1, 2]);              range.MergeCells =  true ;   //合并单元格              range.Font.Bold =  true ;    //加粗单元格内字符              //写入题目              m_Sheet.Cells[startrow, startrow] = title;              int  rownum = dt.Rows.Count; //行数              int  columnnum = dt.Columns.Count; //列数              int  num = rownum + 2;    //得到数据中的最大行数                  //写入列标题              for  ( int  j = 0; j < columnnum; j++)              {                      int  bt_startrow = startrow + 1;                        //将字段名写入文档                      m_Sheet.Cells[bt_startrow, 1 + j] = dt.Columns[j].ColumnName;                         //单元格内背景色                      m_Sheet.get_Range(m_Sheet.Cells[bt_startrow, 1 + j], m_Sheet.Cells[bt_startrow, 1 + j]).Interior.ColorIndex = 15;             }                             //逐行写入数据              for  ( int  i = 0; i < rownum; i++)              {                  for  ( int  j = 0; j < columnnum; j++)                  {                          m_Sheet.Cells[startrow + 2 + i, 1 + j] = dt.Rows[i][j].ToString();                  }              }              m_Sheet.Columns.AutoFit();                              //在当前工作表中根据数据生成图表               CreateChart(m_Book, m_Sheet, num);           }           private  void  CreateChart(Excel._Workbook m_Book,Excel._Worksheet m_Sheet, int  num)          {              Excel.Range oResizeRange;              Excel.Series oSeries;                m_Book.Charts.Add(Missing.Value, Missing.Value, 1, Missing.Value);              m_Book.ActiveChart.ChartType = Excel.XlChartType.xlLine; //设置图形                //设置数据取值范围              m_Book.ActiveChart.SetSourceData(m_Sheet.get_Range( "A2" ,  "C"  + num.ToString()), Excel.XlRowCol.xlColumns);              //m_Book.ActiveChart.Location(Excel.XlChartLocation.xlLocationAutomatic, title);              //以下是给图表放在指定位置              m_Book.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, m_Sheet.Name);              oResizeRange = (Excel.Range)m_Sheet.Rows.get_Item(10, Missing.Value);              m_Sheet.Shapes.Item( "Chart 1" ).Top = ( float )( double )oResizeRange.Top;   //调图表的位置上边距              oResizeRange = (Excel.Range)m_Sheet.Columns.get_Item(6, Missing.Value);   //调图表的位置左边距             // m_Sheet.Shapes.Item("Chart 1").Left = (float)(double)oResizeRange.Left;              m_Sheet.Shapes.Item( "Chart 1" ).Width = 400;    //调图表的宽度              m_Sheet.Shapes.Item( "Chart 1" ).Height = 250;   //调图表的高度                m_Book.ActiveChart.PlotArea.Interior.ColorIndex = 19;   //设置绘图区的背景色              m_Book.ActiveChart.PlotArea.Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone; //设置绘图区边框线条              m_Book.ActiveChart.PlotArea.Width = 400;    //设置绘图区宽度              //m_Book.ActiveChart.ChartArea.Interior.ColorIndex = 10; //设置整个图表的背影颜色              //m_Book.ActiveChart.ChartArea.Border.ColorIndex = 8;// 设置整个图表的边框颜色              m_Book.ActiveChart.ChartArea.Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone; //设置边框线条              m_Book.ActiveChart.HasDataTable =  false ;                  //设置Legend图例的位置和格式              m_Book.ActiveChart.Legend.Top = 20.00;  //具体设置图例的上边距              m_Book.ActiveChart.Legend.Left = 60.00; //具体设置图例的左边距              m_Book.ActiveChart.Legend.Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone;              m_Book.ActiveChart.Legend.Width = 150;              m_Book.ActiveChart.Legend.Font.Size = 9.5;              //m_Book.ActiveChart.Legend.Font.Bold = true;              m_Book.ActiveChart.Legend.Font.Name =  "宋体" ;              //m_Book.ActiveChart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionTop;//设置图例的位置              m_Book.ActiveChart.Legend.Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone; //设置图例边框线条                     //设置X轴的显示              Excel.Axis xAxis = (Excel.Axis)m_Book.ActiveChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);              xAxis.MajorGridlines.Border.LineStyle = Excel.XlLineStyle.xlDot;              xAxis.MajorGridlines.Border.ColorIndex = 1; //gridLine横向线条的颜色              xAxis.HasTitle =  false ;              xAxis.MinimumScale = 1500;              xAxis.MaximumScale = 6000;              xAxis.TickLabels.Font.Name =  "宋体" ;              xAxis.TickLabels.Font.Size = 9;                     //设置Y轴的显示              Excel.Axis yAxis = (Excel.Axis)m_Book.ActiveChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);              yAxis.TickLabelSpacing = 30;              yAxis.TickLabels.NumberFormat =  "M月D日" ;              yAxis.TickLabels.Orientation = Excel.XlTickLabelOrientation.xlTickLabelOrientationHorizontal; //Y轴显示的方向,是水平还是垂直等              yAxis.TickLabels.Font.Size = 8;              yAxis.TickLabels.Font.Name =  "宋体" ;                //m_Book.ActiveChart.Floor.Interior.ColorIndex = 8;              /***以下是设置标题*****              m_Book.ActiveChart.HasTitle=true;              m_Book.ActiveChart.ChartTitle.Text = "净值指数";              m_Book.ActiveChart.ChartTitle.Shadow = true;              m_Book.ActiveChart.ChartTitle.Border.LineStyle = Excel.XlLineStyle.xlContinuous;              */                oSeries = (Excel.Series)m_Book.ActiveChart.SeriesCollection(1);              oSeries.Border.ColorIndex = 45;              oSeries.Border.Weight = Excel.XlBorderWeight.xlThick;              oSeries = (Excel.Series)m_Book.ActiveChart.SeriesCollection(2);              oSeries.Border.ColorIndex = 9;              oSeries.Border.Weight = Excel.XlBorderWeight.xlThick;            }

 

 

二、利用Microsoft.Office.Interop.OWC11

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113

using  OWC = Microsoft.Office.Interop.Owc11;            public  void  DataTableToSheet( string  title, DataTable dt, Excel._Worksheet m_Sheet, Excel._Workbook m_Book,    int  startrow)      {            。。。。。。（同上篇）。。。。。。                //使用OWC11组件画图              showChart(OWC.ChartChartTypeEnum.chChartTypeLine);   //生成图表文件                //将图表文件插入到EXCEL文档中              m_Sheet.Shapes.AddPicture( "E://chart.gif" , MsoTriState.msoFalse, MsoTriState.msoTrue, 250, 100, 350, 250 );        }   private  void  showChart(OWC.ChartChartTypeEnum Type)      {          OWC.ChartChartTypeEnum _Type;            OWC.ChartSpace axChartSpace1 =  new  OWC.ChartSpaceClass();          try          {                axChartSpace1.Clear();              OWC.ChChart objChart = axChartSpace1.Charts.Add(0);              OWC.ChAxis axis = objChart.Axes[0];    //X轴              OWC.ChAxis axis1 = objChart.Axes[1];   //Y轴                objChart.Type = Type;              objChart.HasLegend =  true ;              objChart.Legend.Position = OWC.ChartLegendPositionEnum.chLegendPositionTop;              //objChart.HasTitle = true;              //objChart.Title.Caption = "净值指数图";              //objChart.Axes[0].HasTitle = true ;              //objChart.Axes[0].Title.Caption = "日期";              //objChart.Axes[1].HasTitle = true;              //objChart.Axes[1].Title.Caption = "数值";                objChart.SeriesCollection.Add(0);              objChart.SeriesCollection[0].SetData(OWC.ChartDimensionsEnum.chDimSeriesNames,               +( int )OWC.ChartSpecialDataSourcesEnum.chDataLiteral,  "开放式基金" );                DataSet ds = ScData.ListData( "exec zb_LiCaiZhouBao "  +  int .Parse(txtStartDate.Text.Replace( "-" , "" ) ) +  ",1" );              string  X_Value1 =  "" ;              string  X_Value2 =  "" ;              string  Y_Value =  "" ;              if  (ds.Tables.Count > 0)              {                  foreach  (DataRow dr  in  ds.Tables[0].Rows)                  {                     // Console.WriteLine(dr["日期"].ToString());                      Y_Value = Y_Value + dr[ "日期" ].ToString() +  '/t' ;                      X_Value1 = X_Value1 + dr[ "开放式基金" ].ToString() +  '/t' ;                      X_Value2 = X_Value2 + dr[ "偏股型基金" ].ToString() +  '/t' ;                  }                }                objChart.SeriesCollection[0].SetData(OWC.ChartDimensionsEnum.chDimCategories,               +( int )OWC.ChartSpecialDataSourcesEnum.chDataLiteral, Y_Value);                            objChart.SeriesCollection[0].SetData(OWC.ChartDimensionsEnum.chDimValues,               ( int )OWC.ChartSpecialDataSourcesEnum.chDataLiteral, X_Value1);                objChart.SeriesCollection.Add(1);              objChart.SeriesCollection[1].SetData(OWC.ChartDimensionsEnum.chDimSeriesNames,               +( int )OWC.ChartSpecialDataSourcesEnum.chDataLiteral,  "偏股型基金" );                objChart.SeriesCollection[1].SetData(OWC.ChartDimensionsEnum.chDimCategories,               +( int )OWC.ChartSpecialDataSourcesEnum.chDataLiteral, Y_Value);              objChart.SeriesCollection[1].SetData(OWC.ChartDimensionsEnum.chDimValues,               ( int )OWC.ChartSpecialDataSourcesEnum.chDataLiteral, X_Value2);                    objChart.SeriesCollection[0].Line.Color =  "orange" ;              //objChart.SeriesCollection[0].Line.Weight = OWC.ChLine.LineWeightEnum.owcLineWeightThick;   //线条加粗              objChart.SeriesCollection[1].Line.Color =  "maroon" ;              //objChart.SeriesCollection[1].Line.Weight = OWC.LineWeightEnum.owcLineWeightThick;                axis.TickMarkSpacing = 30;              axis.HasTickLabels =  true ;              axis.TickLabelSpacing =30;                axis1.HasMajorGridlines =  true ;              axis1.MajorGridlines.Line.DashStyle = OWC.ChartLineDashStyleEnum.chLineDashDotDot; //.chLineDashDot;              //axis1.MajorGridlines.Line.Color = "orange";                objChart.PlotArea.Interior.Color =  "LightYellow" ;     //图表区的背景色                             // objChart.Interior.Color = "green";                         //objChart.Interior.BackColor = "yellow";              //axis.TickMarkSpacing = 50;          }          catch  (Exception ex)          {              //timer1.Enabled = false;              MessageBox.Show(ex.Message);          }            finally          {              _Type = Type;              //输出成GIF文件.               string  strAbsolutePath =  "E://chart.gif" ; //生成图形文件                axChartSpace1.ExportPicture(strAbsolutePath,  "GIF" , 600, 350);            }      }

 

没有整理与归纳的知识，一文不值！高度概括与梳理的知识，才是自己真正的知识与技能。 永远不要让自己的自由、好奇、充满创造力的想法被现实的框架所束缚，让创造力自由成长吧！ 多花时间，关心他（她）人，正如别人所关心你的。理想的腾飞与实现，没有别人的支持与帮助，是万万不能的。

    本文转自wenglabs博客园博客，原文链接：http://www.cnblogs.com/arxive/p/5848867.html，如需转载请自行联系原作者