使用OleDB保存数据到Excel

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Collections.Specialized;
using System.IO;
using System.Reflection;

类内容：

 

namespace Jd.RestoreNotify.Data
{
    public static class ExcelHelper
    {
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T">Model类型</typeparam>
        /// <param name="list">列表</param>
        /// <param name="tmppath">模板文件（空的excel文件）地址</param>
        /// <param name="outputpath">要输出的地址</param>
        /// <param name="sheetname">excel的sheet名称</param>
        /// <param name="propdict">要导出的列名列表</param>
        /// <returns></returns>
        public static bool ExportToExcel<T>(IList<T> list, string tmppath, string outputpath, string sheetname, NameValueCollection propdict)
            where T : class
        {
            if (list == null || list.Count == 0)
            {
                throw new ArgumentNullException("该列表没有实际的值", "list");
            }

            #region 判断模板文件、保存路径
            if (!File.Exists(tmppath))
            {
                throw new FileNotFoundException("模板文件路径不正确，找不到模板文件");
            }
            if (File.Exists(outputpath))
            {
                File.Delete(outputpath);
            }
            File.Copy(tmppath, outputpath);
            #endregion
            sheetname = string.IsNullOrEmpty(sheetname) ? "Sheet1" : sheetname;
            string excelconntext = "Provider=Microsoft.Jet.OleDB.4.0;Data Source=" + outputpath + ";Extended Properties = \"Excel 8.0;HDR=Yes;\"";

            using (OleDbConnection cn = new OleDbConnection(excelconntext))
            {
                cn.Open();
                OleDbCommand com = cn.CreateCommand();
                PropertyInfo[] ps = typeof(T).GetProperties();
                List<PropertyInfo> proplist = new List<PropertyInfo>();
                StringBuilder sb = new StringBuilder();
                sb.Append("Create Table [" + sheetname + "$](");

                StringBuilder sb2 = new StringBuilder();
                sb2.Append("Insert into [" + sheetname + "$] values(");
                foreach (var item in ps)
                {
                    string head = propdict[item.Name];
                    if (string.IsNullOrEmpty(head)) continue;
                    else proplist.Add(item);
                    sb.Append(head + " ");
                    switch (item.PropertyType.Name)
                    {
                        case "String": sb.Append("Char,"); break;
                        case "Decimal": sb.Append("Decimal,"); break;
                        case "DateTime": sb.Append("Char,"); break;
                        case "Int32": sb.Append("Integer,"); break;
                        default: sb.Append("Char,");
                            break;
                    }
                    sb2.Append("@" + item.Name + " ,");
                }
                com.CommandText = sb.ToString().Trim(',') + ")";
                com.ExecuteNonQuery();//创建表头

                com.CommandText = sb2.ToString().Trim(',') + ")";

                foreach (var item in list)
                {
                    OleDbParameter[] pars = new OleDbParameter[proplist.Count];
                    for (int i = 0; i < proplist.Count; i++)
                    {
                        pars[i] = new OleDbParameter(proplist[i].Name, proplist[i].GetValue(item, null) ?? "");
                    }
                    com.Parameters.Clear();
                    com.Parameters.AddRange(pars);
                    com.ExecuteNonQuery();//写入每行的数据
                }
            }

            return true;
        }
    }
}