ListView导出到Excel

from:http://limmo.blog.sohu.com/45206198.html

[csharp] view plaincopyprint?

1. using System;  
2. using System.Collections.Generic;  
3. using System.Linq;  
4. using System.Text;  
5. using System.Windows.Forms;  
6. using System.Data.OleDb;  
7. using System.Data;  
8.   
9.   
10. namespace jiayuan.Util  
11. {  
12.     public static class ListViewToExcel  
13.     {  
14.           
15.         /// <summary>  
16.         /// 将ListView中的数据导出到指定的Excel文件中  
17.         /// </summary>  
18.         /// <param name="listView">System.Windows.Forms.ListView,指定要导出的数据源</param>  
19.         /// <param name="destFileName">指定目标文件路径</param>  
20.         /// <param name="tableName">要导出到的表名称</param>  
21.         /// <param name="overWrite">指定是否覆盖已存在的表</param>  
22.         /// <returns>导出的记录的行数</returns>  
23.         public static int ExportToExcel(ListView listView, string destFileName, string tableName)  
24.         {  
25.             //得到字段名  
26.             string szFields = "";  
27.             string szValues = "";  
28.             for (int i = 0; i < listView.Columns.Count; i++)  
29.             {  
30.                 szFields += "[" + listView.Columns[i].Text + "],";  
31.             }  
32.             szFields = szFields.TrimEnd(',');  
33.             //定义数据连接  
34.             OleDbConnection connection = new OleDbConnection();  
35.             connection.ConnectionString = ListViewToExcel.GetConnectionString(destFileName);  
36.             OleDbCommand command = new OleDbCommand();  
37.             command.Connection = connection;  
38.             command.CommandType = CommandType.Text;  
39.             //打开数据库连接  
40.             try  
41.             {  
42.                 connection.Open();  
43.             }  
44.             catch  
45.             {  
46.                 throw new Exception("目标文件路径错误。");  
47.             }  
48.             //创建数据库表  
49.             try  
50.             {  
51.                 command.CommandText = ListViewToExcel.GetCreateTableSql("[" + tableName + "]", szFields.Split(','));  
52.                 command.ExecuteNonQuery();  
53.             }  
54.             catch (Exception ex)  
55.             {  
56.                 //如果允许覆盖则删除已有数据  
57.                 throw ex;  
58.             }  
59.             try  
60.             {  
61.                 //循环处理数据------------------------------------------  
62.                 int recordCount = 0;  
63.                 for (int i = 0; i < listView.Items.Count; i++)  
64.                 {  
65.                     szValues = "";  
66.                     for (int j = 0; j < listView.Columns.Count; j++)  
67.                     {  
68.                         if (j >= listView.Items[i].SubItems.Count)  
69.                         {  
70.                             szValues += "'',";  
71.                         }  
72.                         else  
73.                         {  
74.                             szValues += "'" + listView.Items[i].SubItems[j].Text + "',";  
75.                         }  
76.                     }  
77.                     szValues = szValues.TrimEnd(',');  
78.                     //组合成SQL语句并执行  
79.                     string szSql = "INSERT INTO [" + tableName + "](" + szFields + ") VALUES(" + szValues + ")";  
80.                     command.CommandText = szSql;  
81.                     recordCount += command.ExecuteNonQuery();  
82.                 }  
83.                 connection.Close();  
84.                 return recordCount;  
85.             }  
86.             catch (Exception ex)  
87.             {  
88.                 throw ex;  
89.             }  
90.         }  
91.         //得到连接字符串  
92.         private static String GetConnectionString(string fullPath)  
93.         {  
94.             string szConnection;  
95.             szConnection = "Provider=Microsoft.JET.OLEDB.4.0;Extended Properties=Excel 8.0;data source=" + fullPath;  
96.             return szConnection;  
97.         }  
98.         //得到创建表的SQL语句  
99.         private static string GetCreateTableSql(string tableName, string[] fields)  
100.         {  
101.             string szSql = "CREATE TABLE " + tableName + "(";  
102.             for (int i = 0; i < fields.Length; i++)  
103.             {  
104.                 szSql += fields[i] + " VARCHAR(200),";  
105.             }  
106.             szSql = szSql.TrimEnd(',') + ")";  
107.             return szSql;  
108.         }  
109.   
110.   
111.   
112.   
113.     }  
114. }