这两天客户有个需求让在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 }