1、上传Excel文件服务端
/// <summary> /// 任务详情导入模板数据 /// </summary> /// <param name="advertId">广告公司id</param> /// <param name="tempId">excel模板id</param> /// <returns></returns> public ActionResult Task_TempData_Details_Import(int tempId, Guid advertId) { var data = OperateContext.Current.Execute<List<Task_TempData_Details>>(rm => { var acc = UserAccount.Instance().GetUserInfo(); var companyId = acc.CompanyId; HttpFileCollectionBase files = HttpContext.Request.Files; if (files != null && files.Count == 1) { HttpPostedFileBase file = files[0]; if (file != null && !String.IsNullOrWhiteSpace(file.FileName) && (file.FileName.EndsWith(".xlsx") || file.FileName.EndsWith(".xls"))) { rm.RetData = OperateContext.Current.BLLSession.ITask_StagePlanInfoBLL.Task_TempData_Details_Import(file.InputStream, file.FileName, companyId, advertId, tempId); OperateContext.Current.BLLSession.ISys_AccountLogBLL.AddLog(acc.AccountId, acc.Name, LogTypeEnum.重要, "任务详情导入基础数据"); } else { rm.RetCode = 1; rm.RetMsg = "上传的文件格式错误"; } } else { rm.RetCode = 1; rm.RetMsg = "未选择文件"; } }); return Json(data, JsonRequestBehavior.AllowGet); }
2、上传Excel文件js
scope.uploadFile = function () { if (!scope.ModelInfo.advertId) { layer.msg("请选择广告公司"); return; } if (!scope.ModelInfo.tempId) { layer.msg("请选择模板"); return; } //file类型的input var file = document.getElementById("fileUpload").files[0]; if (!file) { layer.msg("请选择文件"); return; } var form = new FormData(); form.append('file', file); //添加其他参数 form.append('advertId', scope.ModelInfo.advertId); form.append('tempId', scope.ModelInfo.tempId); layer.msg("正在导入数据，请勿进行任何操作，完成后会自动显示结果"); layer.load(); //请求 http({ method: 'POST', url: 'Task_TempData_Details_Import', data: form, headers: { 'Content-Type': undefined }, transformRequest: angular.identity }).success(function (res) { layer.closeAll(); if (res.RetCode == 0) { var data = res.RetData; scope.TotalCount = data.TotalCount; scope.SuccessCount = data.SuccessCount; scope.FailCount = data.FailCount; scope.Result = data.Result; scope.isResult = true; } else { layer.msg(res.RetMsg); } }).error(function (data) { layer.msg("上传文件失败"); }) } //手动执行一次模板基础数据生成任务 scope.TaskMatching = function () { Ajax.post("TaskDetailsMatching", {}, function (result) { }); }
3、解析数据类XSSFWorkbookImportHelper
using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; namespace ElevatorAdvertising.Common { /// <summary> /// excel文件解析 /// </summary> public class XSSFWorkbookImportHelper { /// <summary> /// 解析excel /// </summary> /// <param name="stream"></param> /// <param name="fileName"></param> /// <param name="headerNumber"></param> /// <param name="isNoCloseStream"></param> public static DataTable GetDataTable(System.IO.Stream stream, string fileName, int headerNumber = 0, bool isNoCloseStream = true ,Func<DataTable, DataTable> addDefaultCol=null, Func<DataRow, DataRow> addDefaultRowData = null ) { try { DataTable table = new DataTable(); IWorkbook workbook; if (fileName.EndsWith(".xlsx")) { workbook = new XSSFWorkbook(stream); } else { workbook = new HSSFWorkbook(stream); } ISheet sheet = workbook.GetSheetAt(0); if (sheet == null) throw new GeneralException(11, "Excel无工作表"); // 表头即下标第temp.HeaderNumber行(0开始)数据，每列的列名 IRow headerRow = sheet.GetRow(headerNumber); int cellCount = headerRow.LastCellNum; int rowCount = sheet.LastRowNum; //循环添加标题列 for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } //添加默认列 if (addDefaultCol != null) { table = addDefaultCol(table); } //数据 for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = table.NewRow(); if (row != null) { for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { dataRow[j] = GetCellValue(row.GetCell(j)); } } //添加默认数据 if (addDefaultRowData != null) { dataRow = addDefaultRowData(dataRow); } } table.Rows.Add(dataRow); } return table; } catch (GeneralException ge) { throw ge; } finally { if (!isNoCloseStream && stream != null) stream.Close(); } } private static string GetCellValue(ICell cell) { if (cell == null) { return string.Empty; } switch (cell.CellType) { case CellType.Blank: return string.Empty; case CellType.Boolean: return cell.BooleanCellValue.ToString(); case CellType.Error: return cell.ErrorCellValue.ToString(); case CellType.Numeric: case CellType.Unknown: default: return cell.ToString(); case CellType.String: return cell.StringCellValue; case CellType.Formula: try { HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook); e.EvaluateInCell(cell); return cell.ToString(); } catch { return cell.NumericCellValue.ToString(); } } } public static void RemoveEmpty(DataTable dt) { List<DataRow> removelist = new List<DataRow>(); for (int i = 0; i < dt.Rows.Count; i++) { bool IsNull = true; for (int j = 0; j < dt.Columns.Count; j++) { if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim())) { IsNull = false; } } if (IsNull) { removelist.Add(dt.Rows[i]); } } for (int i = 0; i < removelist.Count; i++) { dt.Rows.Remove(removelist[i]); } } /// <summary> /// 大数据插入 /// </summary> /// <param name="connectionString">目标库连接</param> /// <param name="TableName">目标表</param> /// <param name="dtSelect">来源数据</param> public static void SqlBulkCopyByDatatable(string connectionString, string TableName, DataTable dtSelect) { using (SqlConnection conn = new SqlConnection(connectionString)) { using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction)) { try { sqlbulkcopy.DestinationTableName = TableName; sqlbulkcopy.BatchSize = 20000; sqlbulkcopy.BulkCopyTimeout = 0;//不限时间 for (int i = 0; i < dtSelect.Columns.Count; i++) { sqlbulkcopy.ColumnMappings.Add(dtSelect.Columns[i].ColumnName, dtSelect.Columns[i].ColumnName); } sqlbulkcopy.WriteToServer(dtSelect); } catch (System.Exception ex) { throw ex; } } } } } }
4、BLL调用解析并SqlBulkCopy插入数据库
/// <summary> /// 任务详情导入模板数据 /// </summary> /// <param name="stream"></param> /// <returns></returns> public void Task_TempData_Details_Import(System.IO.Stream stream, TaskDetailsMatchingModel model) { #region 参数验证 if (model.CreateUserId == Guid.Empty) { throw new GeneralException(1, "上传人错误"); } if (model.AdvertId == Guid.Empty) { throw new GeneralException(1, "请选择广告公司"); } if (model.CompanyId == Guid.Empty) { throw new GeneralException(1, "请选择安装公司"); } if (model.Time == default) { throw new GeneralException(1, "请选择安装日期"); } if (model.ImportTime == default) { throw new GeneralException(1, "请设置导入日期"); } #endregion try { //excel解析模板对象 var temp = DBSession.ITask_Temp_DetailsDAL.GetFirstBy(x => x.CompanyId == model.CompanyId && x.TempState == 1 && x.TempId == model.TempId); if (temp == null) { throw new GeneralException(1, "任务详情模板错误，请选择可用模板"); } #region 解析excel,table var groupByValue = model.ImportTime.ToString("yyyy-MM-dd HH:mm:ss"); model.ImportTime = ConvertHelper.ToDateTime(model.ImportTime.ToString("yyyy-MM-dd HH:mm:ss"));//精确到秒 var table = XSSFWorkbookImportHelper.GetDataTable(stream, model.FileName, temp.HeaderNumber, addDefaultCol:(DataTable tb) => { //添加其他列 tb.Columns.Add("DataId", typeof(Guid)); tb.Columns.Add("CompanyId", typeof(Guid)); tb.Columns.Add("AdvertCompanyId", typeof(Guid)); tb.Columns.Add("DataState", typeof(int)); tb.Columns.Add("GroupByValue", typeof(string)); tb.Columns.Add("CreateUserId", typeof(Guid)); tb.Columns.Add("CreateUserName", typeof(string)); tb.Columns.Add("CreateTime", typeof(DateTime)); tb.Columns.Add("IsBottomAdvert", typeof(int)); return tb; }, addDefaultRowData:(DataRow dr) => { //添加其他列的数据 dr["DataId"] = Guid.NewGuid(); dr["CompanyId"] = model.CompanyId; dr["AdvertCompanyId"] = model.AdvertId; dr["DataState"] = 1; dr["GroupByValue"] = groupByValue; dr["CreateUserId"] = model.CreateUserId; dr["CreateUserName"] = model.CreateUserName; dr["CreateTime"] = model.ImportTime; dr["IsBottomAdvert"] = model.IsBottomAdvert; return dr; }); XSSFWorkbookImportHelper.RemoveEmpty(table); //校准模板的列 for (int i = 0; i < table.Columns.Count; i++) { var column = (table.Columns[i].ColumnName + "").Replace(" ", ""); if (column == temp.TaskNumber) { table.Columns[i].ColumnName = "TaskNumber"; } else if (column == temp.TaskName) { table.Columns[i].ColumnName = "TaskName"; } else if (column == temp.InstallState) { table.Columns[i].ColumnName = "InstallState"; } else if (column == temp.Sort) { table.Columns[i].ColumnName = "Sort"; } else if (column == temp.RegionName) { table.Columns[i].ColumnName = "RegionName"; } else if (column == temp.FloorDiscNature) { table.Columns[i].ColumnName = "FloorDiscNature"; } else if (column == temp.FloorDiscName) { table.Columns[i].ColumnName = "FloorDiscName"; } else if (column == temp.AddressDetails) { table.Columns[i].ColumnName = "AddressDetails"; } else if (column == temp.LinkJobNumberName) { table.Columns[i].ColumnName = "LinkJobNumberName"; } else if (column == temp.InstallJobNumber) { table.Columns[i].ColumnName = "InstallJobNumber"; } else if (column == temp.FloorUnit) { table.Columns[i].ColumnName = "FloorUnit"; } else if (column == temp.Elevator) { table.Columns[i].ColumnName = "Elevator"; } else if (column == temp.ResourceName) { table.Columns[i].ColumnName = "ResourceName"; } else if (column == temp.MediaName) { table.Columns[i].ColumnName = "MediaName"; } else if (column == temp.TaskCode) { table.Columns[i].ColumnName = "TaskCode"; } else if (column == temp.ResourceType) { table.Columns[i].ColumnName = "ResourceType"; } else if (column == temp.UnitName) { table.Columns[i].ColumnName = "UnitName "; } else if (column == temp.MediaSpecs) { table.Columns[i].ColumnName = "MediaSpecs"; } else if (column == temp.OldAdvertImage) { table.Columns[i].ColumnName = "OldAdvertImage"; } else if (column == temp.DownDrawPhotoRequire) { table.Columns[i].ColumnName = "DownDrawPhotoRequire"; } else if (column == temp.NewImg) { table.Columns[i].ColumnName = "NewImg"; } else if (column == temp.IsChangeImg) { table.Columns[i].ColumnName = "IsChangeImg"; } else if (column == temp.GetImgNumber) { table.Columns[i].ColumnName = "GetImgNumber"; } else if (column == temp.UpperDrawPhotoRequire) { table.Columns[i].ColumnName = "UpperDrawPhotoRequire"; } else if (column == temp.TakeTime) { table.Columns[i].ColumnName = "TakeTime"; } else if (column == temp.CommitDate) { table.Columns[i].ColumnName = "CommitDate"; } else if (column == temp.FailRemark) { table.Columns[i].ColumnName = "FailRemark"; } else if (column == temp.ConfirmState) { table.Columns[i].ColumnName = "ConfirmState"; } else if (column == temp.ConfirmTime) { table.Columns[i].ColumnName = "ConfirmTime"; } else if (column == temp.AvailablePosition) { table.Columns[i].ColumnName = "AvailablePosition"; } else if (column == temp.PhotoRequire) { table.Columns[i].ColumnName = "PhotoRequire"; } else if (column == temp.PhotoCommit) { table.Columns[i].ColumnName = "PhotoCommit"; } else if (column == temp.PhotoDownload) { table.Columns[i].ColumnName = "PhotoDownload"; } else if (typeof(Task_TempData_Details).GetProperty(column)==null) { table.Columns.Remove(table.Columns[i]); i--; } } #endregion //创建导入记录 Task_ImportState importModel = new Task_ImportState() { CompanyId = model.CompanyId, AdvertCompanyId = model.AdvertId, ImportTime = model.ImportTime, InstallTime = model.Time, State = (int)EnumTask_ImportState.正在导入, CreateUserId = model.CreateUserId, CreateUserName = model.CreateUserName, TaskImportCount = table.Rows.Count, Remark = "开始导入", }; DBSession.ITask_ImportStateDAL.Add(importModel); //批量插入table XSSFWorkbookImportHelper.SqlBulkCopyByDatatable(ConfigurationHelper.AppSetting("sqlConnectionString"), "Task_TempData_Details", table); //导入的模板基础数据生成任务详情，以导入日期区分本次生成的数据。 TaskDetailsMatching(model); } catch (Exception ex) { LogHelper.Instance.Error("导入任务批量加入数据库异常ImportTime:" + model.ImportTime.ToString(), ex); throw; } }
5、DataTable和List转换
using System; using System.Collections.Generic; using System.Data; using System.Reflection; namespace CommonUtil { public static class DataTableHelper { /// <summary> /// list 转table /// </summary> /// <typeparam name="T"></typeparam> /// <param name="list"></param> /// <returns></returns> public static DataTable ListToDataTable<T>(List<T> list) { Type tp = typeof(T); PropertyInfo[] proInfos = tp.GetProperties(); DataTable dt = new DataTable(); foreach (var item in proInfos) { //解决DataSet不支持System.Nullable<>问题 Type colType = item.PropertyType; if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>))) { colType = colType.GetGenericArguments()[0]; } //添加列明及对应类型 { dt.Columns.Add(item.Name, colType); } } foreach (var item in list) { DataRow dr = dt.NewRow(); foreach (var proInfo in proInfos) { object obj = proInfo.GetValue(item); if (obj == null) { continue; } if (proInfo.PropertyType == typeof(DateTime) && Convert.ToDateTime(obj) < Convert.ToDateTime("1753-01-01")) { continue; } dr[proInfo.Name] = obj; } dt.Rows.Add(dr); } return dt; } /// <summary> /// table 转list /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dt"></param> /// <returns></returns> public static List<T> ToDataList<T>(this DataTable dt) { var list = new List<T>(); var plist = new List<PropertyInfo>(typeof(T).GetProperties()); foreach (DataRow item in dt.Rows) { T s = Activator.CreateInstance<T>(); for (int i = 0; i < dt.Columns.Count; i++) { PropertyInfo info = plist.Find(p => p.Name == dt.Columns[i].ColumnName); if (info != null) { try { if (!Convert.IsDBNull(item[i])) { object v = null; if (info.PropertyType.ToString().Contains("System.Nullable")) { v = Convert.ChangeType(item[i], Nullable.GetUnderlyingType(info.PropertyType)); } else { v = Convert.ChangeType(item[i], info.PropertyType); } info.SetValue(s, v, null); } } catch (Exception ex) { throw new Exception("字段[" + info.Name + "]转换出错," + ex.Message); } } } list.Add(s); } return list; } } }