c# 从sql 数据库生成 excel (转)

首先要在工程中添加com引用，选择Microsoft Excel 11.0 Object Liberty。

源代码如下（本例是在下生成公司的考勤表测试代码）：
代码
  1 using System; 
  2  using System.Collections.Generic; 
  3 using System.ComponentModel; 
  4 using System.Data; 
  5 using System.Drawing; 
  6 using System.Text; 
  7 using System.Windows.Forms; 
  8 using System.Data.SqlClient; 
  9 using Microsoft.Office.Interop.Excel; 
 10 using System.IO; 
 11 //using Microsoft.Office.Interop.Excel; 
 12 
 13 namespace AttToExcel 
 14 { 
 15 public partial class Form1 : Form 
 16 { 
 17 public Form1() 
 18 { 
 19 InitializeComponent(); 
 20 } 
 21 
 22 private void button1_Click(object sender, EventArgs e) 
 23 { 
 24 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); 
 25 int rowIndex = 1; 
 26 int colIndex = 0; 
 27 excel.Application.Workbooks.Add(true); 
 28 System.Data.DataTable table = GetData(); 
 29 // dealTable(table); 
 30 
 31 //将所得到的表的列名,赋值给单元格 
 32 foreach (DataColumn col in table.Columns) 
 33 { 
 34 colIndex++; 
 35 excel.Cells[1, colIndex] = col.ColumnName; 
 36 } 
 37 
 38 
 39 //同样方法处理数据 
 40 foreach (DataRow row in table.Rows) 
 41 { 
 42 rowIndex++; 
 43 colIndex = 0; 
 44 foreach (DataColumn col in table.Columns) 
 45 { 
 46 colIndex++; 
 47 excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString(); 
 48 } 
 49 } 
 50 
 51 Worksheet ws = (Worksheet)excel.ActiveSheet; 
 52 AutoFitColumn(ws, 5); 
 53 //true则直接用excel打开得到的数据 
 54 excel.Visible = true ; 
 55 
 56 } 
 57 //进行加班处理 
 58 private void dealTable(System.Data.DataTable table) 
 59 { 
 60 
 61 } 
 62 //让excel列宽自适应宽度 
 63 public static void AutoFitColumn(Worksheet ws, int col) 
 64 { 
 65 ((Range)ws.Cells[1, col]).EntireColumn.AutoFit(); 
 66 } 
 67 //获取数据库打卡记录数据 
 68 private System.Data.DataTable GetData() 
 69 { 
 70 SqlConnection conn= new SqlConnection(@"Server=127.0.0.1;Initial Catalog=attendance;Uid=sa;Pwd=;"); 
 71 String sqlText = sql;
 73 
 89 SqlDataAdapter adapter = new SqlDataAdapter(sqlText, conn); 
 90 
 91 DataSet ds= new DataSet(); 
 92 try 
 93 { 
 94 adapter.Fill(ds,"Customer"); 
 95 } 
 96 catch(Exception ex) 
 97 { 
 98 MessageBox.Show(ex.ToString()); 
 99 } 
100 return ds.Tables[0]; 
101 }//end GetData() 
102 
103 }//end public partial class 
104 }//end namespace