/// <summary> /// 将DataTalbe导出到Excel中 /// </summary> /// <param name="dt"></param> /// <param name="ProjectName">生成的Excel的Sheet的名字</param> /// <param name="filePath">保存的路径</param> public static void Export(System.Data.DataTable dt, string filePath) { if (dt == null ) { throw new Exception( "数据表中无数据" ); } int eRowIndex = 1; int eColIndex = 1; int cols = dt.Columns.Count; int rows = dt.Rows.Count; Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add( true ); try { //列名的处理 for ( int i = 0; i < cols; i++) { xlApp.Cells[eRowIndex, eColIndex] = dt.Columns[i].ColumnName; eColIndex++; } //列名加粗显示 xlApp.get_Range(xlApp.Cells[eRowIndex, 1], xlApp.Cells[eRowIndex, cols]).Font.Bold = true ; xlApp.get_Range(xlApp.Cells[eRowIndex, 1], xlApp.Cells[rows + 1, cols]).Font.Name = "Arial" ; xlApp.get_Range(xlApp.Cells[eRowIndex, 1], xlApp.Cells[rows + 1, cols]).Font.Size = "10" ; eRowIndex++; for ( int i = 0; i < rows; i++) { eColIndex = 1; for ( int j = 0; j < cols; j++) { xlApp.Cells[eRowIndex, eColIndex] = dt.Rows[i][j].ToString(); eColIndex++; } eRowIndex++; } //控制单元格中的内容。 xlApp.Cells.EntireColumn.AutoFit(); xlApp.DisplayAlerts = false ; xlBook.SaveCopyAs(filePath); xlApp.Workbooks.Close(); } catch { throw ; } finally { xlApp.Quit(); //杀掉Excel进程。 GC.Collect(); } } |
注意：using Microsoft.Office.Interop.Excel; VS2010 下 添加引用 COM选项中找Microsoft Office 12.0 Object Library Microsoft Excel 14.0 Object Library
引用目录下出现Microsoft.Office.Core Microsoft.Office.Interop.Excel
如果代码中出现 Excel.ApplicationClass()无法互嵌套操作类型 请改用适用的接口
把引用的Microsoft.Office.Interop.Excel 右击属性 嵌入互操作类型改为false即可