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);
}