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

复制代码