这两天客户有个需求让在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 #endregion110 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 }
会让你的心态更平和更坦然，