from:http://limmo.blog.sohu.com/45206198.html
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using System.Windows.Forms;
- using System.Data.OleDb;
- using System.Data;
- namespace jiayuan.Util
- {
- public static class ListViewToExcel
- {
- /// <summary>
- /// 将ListView中的数据导出到指定的Excel文件中
- /// </summary>
- /// <param name="listView">System.Windows.Forms.ListView,指定要导出的数据源</param>
- /// <param name="destFileName">指定目标文件路径</param>
- /// <param name="tableName">要导出到的表名称</param>
- /// <param name="overWrite">指定是否覆盖已存在的表</param>
- /// <returns>导出的记录的行数</returns>
- public static int ExportToExcel(ListView listView, string destFileName, string tableName)
- {
- //得到字段名
- string szFields = "";
- string szValues = "";
- for (int i = 0; i < listView.Columns.Count; i++)
- {
- szFields += "[" + listView.Columns[i].Text + "],";
- }
- szFields = szFields.TrimEnd(',');
- //定义数据连接
- OleDbConnection connection = new OleDbConnection();
- connection.ConnectionString = ListViewToExcel.GetConnectionString(destFileName);
- OleDbCommand command = new OleDbCommand();
- command.Connection = connection;
- command.CommandType = CommandType.Text;
- //打开数据库连接
- try
- {
- connection.Open();
- }
- catch
- {
- throw new Exception("目标文件路径错误。");
- }
- //创建数据库表
- try
- {
- command.CommandText = ListViewToExcel.GetCreateTableSql("[" + tableName + "]", szFields.Split(','));
- command.ExecuteNonQuery();
- }
- catch (Exception ex)
- {
- //如果允许覆盖则删除已有数据
- throw ex;
- }
- try
- {
- //循环处理数据------------------------------------------
- int recordCount = 0;
- for (int i = 0; i < listView.Items.Count; i++)
- {
- szValues = "";
- for (int j = 0; j < listView.Columns.Count; j++)
- {
- if (j >= listView.Items[i].SubItems.Count)
- {
- szValues += "'',";
- }
- else
- {
- szValues += "'" + listView.Items[i].SubItems[j].Text + "',";
- }
- }
- szValues = szValues.TrimEnd(',');
- //组合成SQL语句并执行
- string szSql = "INSERT INTO [" + tableName + "](" + szFields + ") VALUES(" + szValues + ")";
- command.CommandText = szSql;
- recordCount += command.ExecuteNonQuery();
- }
- connection.Close();
- return recordCount;
- }
- catch (Exception ex)
- {
- throw ex;
- }
- }
- //得到连接字符串
- private static String GetConnectionString(string fullPath)
- {
- string szConnection;
- szConnection = "Provider=Microsoft.JET.OLEDB.4.0;Extended Properties=Excel 8.0;data source=" + fullPath;
- return szConnection;
- }
- //得到创建表的SQL语句
- private static string GetCreateTableSql(string tableName, string[] fields)
- {
- string szSql = "CREATE TABLE " + tableName + "(";
- for (int i = 0; i < fields.Length; i++)
- {
- szSql += fields[i] + " VARCHAR(200),";
- }
- szSql = szSql.TrimEnd(',') + ")";
- return szSql;
- }
- }
- }