/// <summary> 导入excel数据到数据库中
/// </summary>
/// <returns></returns>
private void ExcelToDatabase <T>(string originFileName, string tempFileName)
{
//if (Logger.IsDebugEnabled)
// Logger.Debug(string.Format("import\tS\t{0}", originFileName));
string fileType = System.IO.Path.GetExtension(tempFileName);
string connStr = null;
if (fileType == ".xls")
{
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + tempFileName + ";" + "Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
}
else //临时文件都是 xls格式
{
connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + tempFileName + ";" + "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
}
//链接文件
using (var cnnxls = new OleDbConnection(connStr))
{
try
{
cnnxls.Open();
var cmd = new OleDbCommand();
cmd.Connection = cnnxls;
// Get all Sheets in Excel File
var dtSheet = cnnxls.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
foreach (DataRow dr in dtSheet.Rows)
{
string sheetName = dr["TABLE_NAME"].ToString();
if (!sheetName.EndsWith("$"))
{
continue;
}
// Get all rows from the Sheet
cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
var list = cmd.ConvertToList <T>();
IDictionary <string, IList <string> > dic = new Dictionary <string, IList <string> >();
var fileName = System.IO.Path.GetFileName(originFileName);
try
{
//TODO 检查复合模型
foreach (var item in list)
{
IList <string> names = new List <string>();
foreach (var propertyInfo in item.GetType().GetProperties())
{
if (!(propertyInfo.PropertyType.IsPrimitive || typeof(string) == propertyInfo.PropertyType))
{//字段是复合类型
var val = propertyInfo.GetValue(item, null);
if (val == null && _ignore[fileName] != null)
{//填写不对了
//if (Logger.IsWarnEnabled)
// Logger.Warn(typeof(T).Name + ":" + propertyInfo.Name);
var ss = _ignore[fileName].Split(',');
if (!ss.Contains(propertyInfo.Name))
{
names.Add(propertyInfo.Name);
}
}
}
}
if (names.Count > 0 && !dic.ContainsKey(fileName))
{
dic.Add(fileName, names);
}
}
foreach (var item in dic)
{
if (Logger.IsWarnEnabled)
{
Logger.Warn("Field Null ,Check! -> " + item.Key + ":" + string.Join(",", item.Value));
}
}
}
catch (Exception exception)
{
if (Logger.IsErrorEnabled)
{
Logger.Error(exception);
}
}
IDbConnectionFactory connFactory = new OrmLiteConnectionFactory(_connectionString, MySqlDialect.Provider);
//SqlServerOrmLiteDialectProvider.Instance.UseUnicode = true;
using (var db = connFactory.OpenDbConnection())
{
//using (var trans = db.OpenTransaction())
{
try
{
db.DropAndCreateTable <T>();
var len = list.Count;
var count = 500;
var page = len % count == 0 ? len / count : (len / count) + 1;
for (int i = 0; i < page; i++)
{
db.InsertAll <T>(list.Skip(i * count).Take(count));
}
var hash = GetMD5HashFromFile(string.Format("{0}{1}{2}", _inputDir, Path.DirectorySeparatorChar, originFileName));
if (db.Exists <FileHash>("Id=@Id", new { Id = Path.GetFileName(originFileName) }))
{
db.Update <FileHash>(new { Hash = hash, UpdateAtUtc = DateTime.UtcNow },
item => item.Id == Path.GetFileName(originFileName));
}
else
{
db.Insert <FileHash>(new FileHash()
{
FileName = Path.GetFileName(originFileName),
Hash = hash,
UpdateAtUtc = DateTime.UtcNow
});
}
}
catch (Exception exception)
{
if (Logger.IsErrorEnabled)
{
Logger.Error(string.Format("{0} in {1}", sheetName, originFileName));
Logger.Error(exception.Message);
}
_hasErrors = true;
}
finally
{
progressBar1.Value += 1;
}
}
}
}
cnnxls.Close();
}
catch (Exception exception)
{
if (Logger.IsErrorEnabled)
{
Logger.Error(exception.Message);
}
}
//if (Logger.IsDebugEnabled)
// Logger.Debug(string.Format("import\tE\t{0}", originFileName));
}
}
