1 public static bool OutputToExcel(System.Data.DataTable Table, string ExcelFilePath)
  2     {
  3         if (File.Exists(ExcelFilePath))
  4         {
  5             File.Delete(ExcelFilePath);
  6         }
  7 
  8         if ((Table.TableName.Trim().Length == 0) || (Table.TableName.ToLower() == "table"))
  9         {
 10             Table.TableName = "TTable";
 11         }
 12 
 13         //数据表的列数
 14         int ColCount = Table.Columns.Count;
 15         //用于记数，实例化参数时的序号
 16         int i = 0;
 17         //创建参数
 18         OleDbParameter[] para = new OleDbParameter[ColCount];
 19         //创建表结构的SQL语句
 20         string TableStructStr = @"Create Table " + Table.TableName + "(";
 21 
 22         //连接字符串
 23         string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;";
 24         OleDbConnection objConn = new OleDbConnection(connString);
 25         //创建表结构
 26         OleDbCommand objCmd = new OleDbCommand();
 27 
 28         //数据类型集合
 29         ArrayList DataTypeList = new ArrayList();
 30         DataTypeList.Add("System.Decimal");
 31         DataTypeList.Add("System.Double");
 32         DataTypeList.Add("System.Int16");
 33         DataTypeList.Add("System.Int32");
 34         DataTypeList.Add("System.Int64");
 35         DataTypeList.Add("System.Single");
 36 
 37         //遍历数据表的所有列，用于创建表结构
 38         foreach (DataColumn col in Table.Columns)
 39         {
 40             //如果列属于数字列，则设置该列的数据类型为double
 41             if (DataTypeList.IndexOf(col.DataType.ToString()) >= 0)
 42             {
 43                 para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.VarChar);
 44                 objCmd.Parameters.Add(para[i]);
 45                 //如果是最后一列
 46                 if (i + 1 == ColCount)
 47                 {
 48                     TableStructStr += col.ColumnName + " VarChar)";
 49 
 50                 }
 51                 else
 52                 {
 53                     TableStructStr += col.ColumnName + " VarChar,";
 54                 }
 55             }
 56             else
 57             {
 58                 para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.VarChar);
 59                 objCmd.Parameters.Add(para[i]);
 60                 //如果是最后一列
 61                 if (i + 1 == ColCount)
 62                 {
 63                     TableStructStr += col.ColumnName + " varchar)";
 64                 }
 65                 else
 66                 {
 67                     TableStructStr += col.ColumnName + " varchar,";
 68                 }
 69             }
 70             i++;
 71         }
 72 
 73         //创建Excel文件及文件结构
 74         try
 75         {
 76             objCmd.Connection = objConn;
 77             objCmd.CommandText = TableStructStr;
 78 
 79             if (objConn.State == ConnectionState.Closed)
 80             {
 81                 objConn.Open();
 82             }
 83             objCmd.ExecuteNonQuery();
 84         }
 85         catch (Exception exp)
 86         {
 87             throw exp;
 88         }
 89 
 90         //插入记录的SQL语句
 91         string InsertSql_1 = "Insert into " + Table.TableName + " (";
 92         string InsertSql_2 = " Values (";
 93         string InsertSql = "";
 94         //遍历所有列，用于插入记录，在此创建插入记录的SQL语句
 95         for (int colID = 0; colID < ColCount; colID++)
 96         {
 97             if (colID + 1 == ColCount)  //最后一列
 98             {
 99                 InsertSql_1 += Table.Columns[colID].ColumnName + ")";
100                 InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ")";
101             }
102             else
103             {
104                 InsertSql_1 += Table.Columns[colID].ColumnName + ",";
105                 InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ",";
106             }
107         }
108 
109         InsertSql = InsertSql_1 + InsertSql_2;
110 
111         //遍历数据表的所有数据行
112         for (int rowID = 0; rowID < Table.Rows.Count; rowID++)
113         {
114             for (int colID = 0; colID < ColCount; colID++)
115             {
116                 if (para[colID].DbType == DbType.Double && Table.Rows[rowID][colID].ToString().Trim() == "")
117                 {
118                     para[colID].Value = 0;
119                 }
120                 else
121                 {
122                     para[colID].Value = Table.Rows[rowID][colID].ToString().Trim();
123                 }
124             }
125 
126             try
127             {
128                 objCmd.CommandText = InsertSql;
129                 objCmd.ExecuteNonQuery();
130             }
131             catch (Exception exp)
132             {
133                 string str = exp.Message;
134             }
135         }
136 
137         try
138         {
139             if (objConn.State == ConnectionState.Open)
140             {
141                 objConn.Close();
142             }
143         }
144         catch (Exception exp)
145         {
146             throw exp;
147         }
148         return true;
149     }

 1 private void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName)
 2     {
 3         if (tmpDataTable == null)
 4         {
 5             return;
 6         }
 7         int rowNum = tmpDataTable.Rows.Count;
 8         int columnNum = tmpDataTable.Columns.Count;
 9         int rowIndex = 1;
10         int columnIndex = 0;
11 
12         Excel.Application xlApp = new Excel.ApplicationClass();
13 
14         xlApp.DefaultFilePath = "";
15         xlApp.DisplayAlerts = true;
16         xlApp.SheetsInNewWorkbook = 1;
17 
18         Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
19 
20         //将DataTable的列名导入Excel表第一行
21         foreach (DataColumn dc in tmpDataTable.Columns)
22         {
23             columnIndex++;
24             xlApp.Cells[rowIndex, columnIndex] = dc.ColumnName;
25         }
26 
27         //将DataTable中的数据导入Excel中
28         for (int i = 0; i < rowNum; i++)
29         {
30             rowIndex++;
31             columnIndex = 0;
32             for (int j = 0; j < columnNum; j++)
33             {
34                 columnIndex++;
35                 xlApp.Cells[rowIndex, columnIndex] = "'"+tmpDataTable.Rows[i][j].ToString();
36             }
37         }
38         xlBook.SaveCopyAs(strFileName);
39     }