c#使用VBA方法在Excel中绘制饼图（Pie Chart）

这两天客户有个需求让在EXCEL中生成饼图，上网搜了下，写了个代码。
效果如图：
上代码：
View Code
  1 using System;
  2 
  3 using System.Collections.Generic;
  4 
  5 using System.Text;
  6 
  7 using System.IO;
  8 
  9 using System.Data;
 10 
 11 using System.Reflection;
 12 using Microsoft.Office.Core;
 13 
 14 
 15 namespace ExcelGraph
 16 {
 17 
 18     class Program
 19     {
 20 
 21         private static string strCurrentPath = @"D:\Code\";
 22 
 23         private static string title = "testGraph";
 24 
 25         static void Main(string[] args)
 26         {
 27 
 28             Console.WriteLine("begin");
 29 
 30             Program p = new Program();
 31 
 32             p.CreateExcel("TestGraph", "TestGraph.xlsx", "TestGraph");
 33 
 34             Console.WriteLine("done");
 35 
 36         }
 37 
 38 
 39 
 40 
 41 
 42         private void CreateExcel(string title, string fileName, string sheetNames)
 43         {
 44             //待生成的文件名称
 45             string FileName = fileName;
 46 
 47             string FilePath = strCurrentPath + FileName;
 48 
 49             FileInfo fi = new FileInfo(FilePath);
 50 
 51             if (fi.Exists)     //判断文件是否已经存在,如果存在就删除!
 52             {
 53 
 54                 fi.Delete();
 55 
 56             }
 57 
 58             if (sheetNames != null && sheetNames != "")
 59             {
 60 
 61                 Microsoft.Office.Interop.Excel.Application m_Excel = new Microsoft.Office.Interop.Excel.Application();//创建一个Excel对象(同时启动EXCEL.EXE进程) 
 62 
 63                 m_Excel.SheetsInNewWorkbook = 1;//工作表的个数 
 64 
 65                 Microsoft.Office.Interop.Excel._Workbook m_Book = (Microsoft.Office.Interop.Excel._Workbook)(m_Excel.Workbooks.Add(Missing.Value));//添加新工作簿 
 66 
 67                 Microsoft.Office.Interop.Excel._Worksheet m_Sheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_Excel.Worksheets.Add(Missing.Value));
 68 
 69                 #region 处理
 70 
 71                 DataTable auto = new DataTable();
 72 
 73                 auto.Columns.Add("LaunchName");
 74 
 75                 auto.Columns.Add("Usage");
 76 
 77                 auto.Rows.Add(new Object[] { "win8 apac","100" });
 78                 auto.Rows.Add(new Object[] { "win8 china", "200" });
 79                 auto.Rows.Add(new Object[] { "win8 india", "300" });
 80                 // DataSet ds = ScData.ListData("exec Vote_2008.dbo.P_VoteResult_Update " + int.Parse(fdate));
 81                 DataTableToSheet(title, auto, m_Sheet, m_Book, 1);
 82 
 83                 #endregion
 84 
 85                 #region 保存Excel,清除进程
 86 
 87                 m_Book.SaveAs(FilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
 88 
 89                 //m_Excel.ActiveWorkbook._SaveAs(FilePath, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
 90 
 91                 m_Book.Close(false, Missing.Value, Missing.Value);
 92 
 93                 m_Excel.Quit();
 94 
 95                 System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Book);
 96 
 97                 System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Excel);
 98 
 99                 m_Book = null;
100 
101                 m_Sheet = null;
102 
103                 m_Excel = null;
104 
105                 GC.Collect();
106 
107                 //this.Close();//关闭窗体
108 
109                 #endregion
110 
111             }
112 
113         }
114 
115 
116         /// <summary>
117 
118         /// 将DataTable中的数据写到Excel的指定Sheet中
119 
120         /// </summary>
121 
122         /// <param name="dt"></param>
123 
124         /// <param name="m_Sheet"></param>
125 
126         public void DataTableToSheet(string title, DataTable dt, Microsoft.Office.Interop.Excel._Worksheet m_Sheet,
127 
128                                     Microsoft.Office.Interop.Excel._Workbook m_Book, int startrow)
129         {
130             //以下是填写EXCEL中数据
131 
132             Microsoft.Office.Interop.Excel.Range range = m_Sheet.get_Range(m_Sheet.Cells[1, 1], m_Sheet.Cells[1, 2]);
133             // range.MergeCells = true;  //合并单元格
134 
135             range.Font.Bold = true;   //加粗单元格内字符
136 
137             //写入题目
138 
139             m_Sheet.Cells[startrow, startrow] = title;
140 
141             int rownum = dt.Rows.Count;//行数
142 
143             int columnnum = dt.Columns.Count;//列数
144 
145             int num = rownum + 2;   //得到数据中的最大行数
146 
147             //写入列标题
148 
149             for (int j = 0; j < columnnum; j++)
150             {
151 
152                 int bt_startrow = startrow + 1;
153 
154                 //将字段名写入文档
155 
156                 m_Sheet.Cells[bt_startrow, 1 + j] = dt.Columns[j].ColumnName;
157 
158                 //单元格内背景色
159                 m_Sheet.get_Range(m_Sheet.Cells[bt_startrow, 1 + j], m_Sheet.Cells[bt_startrow, 1 + j]).Interior.ColorIndex = 15;
160 
161             }
162 
163             //逐行写入数据 
164 
165             for (int i = 0; i < rownum; i++)
166             {
167 
168                 for (int j = 0; j < columnnum; j++)
169                 {
170 
171                     m_Sheet.Cells[startrow + 2 + i, 1 + j] = dt.Rows[i][j].ToString();
172 
173                 }
174 
175             }
176 
177             m_Sheet.Columns.AutoFit();
178 
179             //在当前工作表中根据数据生成图表
180 
181             CreateChart(m_Book, m_Sheet, num);
182 
183         }
184 
185 
186 
187         private void CreateChart(Microsoft.Office.Interop.Excel._Workbook m_Book, Microsoft.Office.Interop.Excel._Worksheet m_Sheet, int num)
188         {
189 
190             Microsoft.Office.Interop.Excel.Range oResizeRange;
191 
192             Microsoft.Office.Interop.Excel.Series oSeries;
193 
194             m_Book.Charts.Add(Missing.Value, Missing.Value, 1, Missing.Value);
195             m_Book.ActiveChart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlPie;//设置图形
196 
197             //设置数据取值范围
198 
199             m_Book.ActiveChart.SetSourceData(m_Sheet.get_Range("A2", "B" + num.ToString()), Microsoft.Office.Interop.Excel.XlRowCol.xlColumns);
200 
201             m_Book.ActiveChart.Location(Microsoft.Office.Interop.Excel.XlChartLocation.xlLocationAutomatic, title);
202 
203             //以下是给图表放在指定位置
204 
205             m_Book.ActiveChart.Location(Microsoft.Office.Interop.Excel.XlChartLocation.xlLocationAsObject, m_Sheet.Name);
206 
207             oResizeRange = (Microsoft.Office.Interop.Excel.Range)m_Sheet.Rows.get_Item(10, Missing.Value);
208 
209             m_Sheet.Shapes.Item("Chart 1").Top = 0;  //调图表的位置上边距
210             oResizeRange = (Microsoft.Office.Interop.Excel.Range)m_Sheet.Columns.get_Item(5, Missing.Value);  //调图表的位置左边距
211             m_Sheet.Shapes.Item("Chart 1").Left = (float)(double)oResizeRange.Left;
212             m_Sheet.Shapes.Item("Chart 1").Width = 432;   //调图表的宽度
213             m_Sheet.Shapes.Item("Chart 1").Height = 300;  //调图表的高度
214             //m_Book.ActiveChart.PlotArea.Interior.Color = "blue";  //设置绘图区的背景色 
215             m_Book.ActiveChart.PlotArea.Border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;//设置绘图区边框线条
216             m_Book.ActiveChart.PlotArea.Width = 400;
217             m_Book.ActiveChart.PlotArea.Height = 300;
218             m_Book.ActiveChart.PlotArea.Top = 30;
219             m_Book.ActiveChart.PlotArea.Left = 0;
220             // m_Book.ActiveChart.ChartArea.Interior.ColorIndex = 10; //设置整个图表的背影颜色
221             // m_Book.ActiveChart.ChartArea.Border.ColorIndex = 8;// 设置整个图表的边框颜色
222             m_Book.ActiveChart.ChartArea.Border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;//设置边框线条
223             m_Book.ActiveChart.HasDataTable = false;
224             m_Book.ActiveChart.HasTitle = true;
225             m_Book.ActiveChart.HasLegend = true;
226             m_Book.ActiveChart.Shapes.AddLabel(MsoTextOrientation.msoTextOrientationHorizontal, 0, 0, 50, 50);
227 
228             //设置Legend图例的位置和格式
229             //m_Book.ActiveChart.Legend.Top = 50; //具体设置图例的上边距
230             m_Book.ActiveChart.Legend.Left = 410;//具体设置图例的左边距
231             m_Book.ActiveChart.Legend.Interior.ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexNone;
232             m_Book.ActiveChart.Legend.Width = 100;
233             m_Book.ActiveChart.Legend.Font.Size = 12;
234             m_Book.ActiveChart.Legend.Font.Bold = true;
235             m_Book.ActiveChart.Legend.Position = Microsoft.Office.Interop.Excel.XlLegendPosition.xlLegendPositionCorner;//设置图例的位置
236             m_Book.ActiveChart.Legend.Border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;//设置图例边框线条
237 
238             oSeries = (Microsoft.Office.Interop.Excel.Series)m_Book.ActiveChart.SeriesCollection(1);
239             
240             oSeries.Border.ColorIndex = 45;
241             oSeries.Border.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
242 
243         }
244 
245     }
246 
247 }