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