public static string getXLSCreteScript(string[] ExcludedFieldnames = null) { string result = ""; foreach (var item in ReflectionTool <T> .getPropertyNames(ExcludedFieldnames)) { result += (result.Length == 0 ? "" : ", \r\n") + "[" + item + "] " + GetOleDbTypeString(ReflectionTool <T> .GetPropertyType(item)).ToString().ToLower(); } result = "CREATE TABLE " + typeof(T).Name + "s (" + result + ")"; return(result); }
public static bool ExportExcel(List <T> dataList, string FileName, string FilePath, string[] ExcludedFieldnames = null) { StringBuilder sb = new StringBuilder(); if (dataList.Count > 0) { string conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties='Excel 12.0 Xml;HDR=Yes'"; if (System.IO.File.Exists(FilePath)) { System.IO.File.Delete(FilePath); } using (OleDbConnection con = new OleDbConnection(conString)) { if (con.State == ConnectionState.Closed) { con.Open(); } OleDbCommand cmd = new OleDbCommand(getXLSCreteScript(ExcludedFieldnames), con); cmd.ExecuteNonQuery(); OleDbCommand cmdIns = new OleDbCommand(getXLSInsertScript(), con); List <string> FieldList = ReflectionTool <T> .getPropertyNames(ExcludedFieldnames); foreach (string fieldName in FieldList) { OleDbType paramType = GetOleDbType(ReflectionTool <T> .GetPropertyType(fieldName)); if (paramType == OleDbType.VarChar) { cmdIns.Parameters.Add("@" + fieldName, paramType, 4000); } else { cmdIns.Parameters.Add("@" + fieldName, paramType); } } foreach (object field in dataList) { try { foreach (string fieldName in FieldList) { var type = ReflectionTool <T> .GetPropertyType(fieldName); object defaultValue = ""; if (type.Equals(typeof(String))) { defaultValue = (object)""; } else if (type.Equals(typeof(DateTime))) { defaultValue = DBNull.Value; //new DateTime(); //(DateTime?)null; } else { defaultValue = (object)0; } cmdIns.Parameters["@" + fieldName].Value = ReflectionTool <T> .GetPropertyValue(field, fieldName, defaultValue); } cmdIns.ExecuteNonQuery(); } catch (Exception) { if (!System.Diagnostics.Debugger.IsAttached) { throw; } } } con.Close(); } } return(true); }