public static DataTable GetDataTableByExcel(string saveExcelFilePath, out string errorMsg) { errorMsg = string.Empty; if (string.IsNullOrEmpty(saveExcelFilePath)) { errorMsg = "未找到导入的Excel文件,请重新选择要导入Excel文件"; } string excelID = "Import_Excel"; ImportExcelHelper improtExcelHelper = new ImportExcelHelper(excelID, saveExcelFilePath); List <string> msgList = new List <string>(); improtExcelHelper.Validate(20, out msgList); if (msgList != null && msgList.Any()) { //验证报错 errorMsg = string.Join("<br />", msgList); } DataTable dataTable = improtExcelHelper.GetDataTable(); return(dataTable); }
/// <summary> /// 批量导入 /// </summary> /// <param name="_mySqlConnection"></param> /// <param name="dt"></param> /// <returns></returns> public int BulkLoad(DataTable table) { ImportExcelHelper h = new ImportExcelHelper(); h.ToCsv(table); var columns = table.Columns.Cast <DataColumn>().Select(colum => colum.ColumnName).ToList(); MySqlBulkLoader bulk = new MySqlBulkLoader(con) { FieldTerminator = ",", FieldQuotationCharacter = '"', EscapeCharacter = '"', LineTerminator = "\r\n", FileName = @"../uploads/" + table.TableName + ".csv", NumberOfLinesToSkip = 0, TableName = table.TableName, }; bulk.Columns.AddRange(columns); int ret = bulk.Load(); using (ShareFolderHelper helper = new ShareFolderHelper("test", "yfzx.2019", FilePath.CSVPATH + table.TableName + ".csv")) { //删除临时的csv文件 File.Delete(FilePath.CSVPATH + table.TableName + ".csv"); } return(ret); }
public async Task <IActionResult> Import(IFormFile files) { //获取文件流 var stream = files.OpenReadStream(); //读取datatable DataTable data = OfficeHelper.OfficeHelper <GraduationStatistics> .ReadStreamToDataTable(stream, _excelMap); //把Excel转换为model IList <GraduationStatistics> datalist = ImportExcelHelper <GraduationStatistics> .ImportExcel(data); List <int> successList = new List <int>(); List <int> errorList = new List <int>(); //循环保存到数据库 foreach (GraduationStatistics d in datalist) { var id = await graduationStatisticsServices.Add(d); if (id > 0) { successList.Add(id); } else { errorList.Add(id); } } return(Content("")); }
public async Task <object> StudentInfoImporter_Test(Genders genders) { string path = @"C:\Users\heyangming\Desktop\test.xlsx"; var import = await ImportExcelHelper.ImportExcelFile(path); var student = await ImportExcelHelper.FillModelOne <ImportStudentDto>(import); await db.ImportStudentDtos.AddRangeAsync(student); if (await db.SaveChangesAsync() > 0) { return(BadRequest("提交失败")); } return(null); }
/// <summary> /// 导出 /// </summary> /// <returns></returns> public JsonResult Export() { // 导出 var now = DateTime.Now; var time = now.ToString("yyyyMMddHHmmss"); var name = @"/Upload/"; string namePath = AppDomain.CurrentDomain.BaseDirectory + name; //获取上传路径的物理地址 if (!Directory.Exists(namePath)) //判断文件夹是否存在 { Directory.CreateDirectory(namePath); //不存在则创建文件夹 } name += "测试导出Excel" + time + ".xlsx"; #region 生成数据 List <DataListViewModel> list = new List <DataListViewModel>(); list.Add(new DataListViewModel() { ID = 1, ImgPath = "http://pic41.nipic.com/20140508/18609517_112216473140_2.jpg" }); list.Add(new DataListViewModel() { ID = 2, ImgPath = "http://pic31.nipic.com/20130801/11604791_100539834000_2.jpg" }); #endregion ImportExcelHelper import = new ImportExcelHelper(list, AppDomain.CurrentDomain.BaseDirectory + name); var jsonResult = new { path = name, }; return(Json(jsonResult)); }
public JsonResult CheckImport(int idNhomDanhMuc, HttpPostedFileBase fileImport) { JsonResultImportBO <DM_DANHMUC_DATA> result = new JsonResultImportBO <DM_DANHMUC_DATA>(true); DM_NHOMDANHMUCBusiness = Get <DM_NHOMDANHMUCBusiness>(); DM_DANHMUC_DATABusiness = Get <DM_DANHMUC_DATABusiness>(); //Kiểm tra file có tồn tại k? if (fileImport == null) { result.Status = false; result.Message = "Không có file đọc dữ liệu"; return(Json(result)); } //Lưu file upload để đọc var saveFileResult = UploadProvider.SaveFile(fileImport, null, ".xls,.xlsx", null, "TempImportFile", UPLOADFOLDER); if (!saveFileResult.status) { result.Status = false; result.Message = saveFileResult.message; return(Json(result)); } else { var importHelper = new ImportExcelHelper <DM_DANHMUC_DATA>(); importHelper.PathTemplate = saveFileResult.fullPath; importHelper.ConfigColumn = new List <ConfigModule>(); importHelper.ConfigColumn.Add(new ConfigModule() { columnName = "TEXT", require = true, TypeValue = typeof(string).FullName, }); importHelper.ConfigColumn.Add(new ConfigModule() { columnName = "DATA", require = true, TypeValue = typeof(int).FullName, }); //importHelper.ConfigColumn.Add(new ConfigModule() //{ // columnName = "DEPTID", // require = true, // TypeValue = typeof(int).FullName, //}); importHelper.ConfigColumn.Add(new ConfigModule() { columnName = "CODE", require = true, TypeValue = typeof(string).FullName, }); //importHelper.ConfigColumn.Add(new ConfigModule() //{ // columnName = "GHICHU", // require = true, // TypeValue = typeof(string).FullName, //}); //var configTang = new ConfigModule() //{ // columnName = "GHICHU", // require = false, // TypeValue = typeof(string).FullName, //}; var rsl = importHelper.Import(); if (rsl.Status) { result.Status = true; result.Message = rsl.Message; //foreach (var item in rsl.ListTrue.ToList()) //{ // var checkresult = DM_DANHMUC_DATABusiness.ExistValue(idNhomDanhMuc, item.DATA.GetValueOrDefault()); // if (checkresult.Status) // { // var listStrErr = new List<string>(); // listStrErr.Add("0"); // listStrErr.Add(item.TEXT); // listStrErr.Add(item.DATA.ToString()); // listStrErr.Add(item.DEPTID.ToString()); // listStrErr.Add(item.CODE.ToString()); // listStrErr.Add(item.GHICHU.ToString()); // listStrErr.Add("+ " + checkresult.Message); // rsl.lstFalse.Add(listStrErr); // rsl.ListTrue.Remove(item); // } //} result.ListData = rsl.ListTrue; result.ListFalse = rsl.lstFalse; } else { result.Status = false; result.Message = rsl.Message; } } return(Json(result)); }
public async Task <ApiResult> Import(IFormFile file) { ApiResult ret = new ApiResult(); ret.code = Code.ImportError; try { string fileName = Path.GetFileNameWithoutExtension(file.FileName); ImportExcelConfig oneConfig = await _importExcelConfigRepo.GetByFileName(fileName); ImportExcelClass oneClass = await _importExcelConfigRepo.GetClassByID(oneConfig.ClassID); ImportExcelHelper importExcelHelper = new ImportExcelHelper(oneClass.FullName, oneClass.AssemblyName); string[] config = oneConfig.Config.Split(','); string[] required = oneConfig.Required.Split(','); string errMsg = ""; ImportExcelLog log = new ImportExcelLog(); DataTable dt = importExcelHelper.GetData(file, config, required, userID, ref log, ref errMsg); if (errMsg != "") { ret.msg = errMsg; return(ret); } DateTime now = DateTime.Now; int i = 0; foreach (DataRow row in dt.Rows) { i++; foreach (DataColumn col in dt.Columns) { if (row["online_date"].ToString() == "") { errMsg += "第" + i + "行上线时间必填"; } else if (row["medium_repair"].ToString() == "") { errMsg += "第" + i + "行中修频率必填"; } else if (row["large_repair"].ToString() == "") { errMsg += "第" + i + "行大修频率必填"; } if (errMsg != "") { ret.msg = errMsg; return(ret); } else { DateTime onLine = Convert.ToDateTime(row["online_date"]); int mediumRepair = Convert.ToInt32(row["medium_repair"]); int largeRepair = Convert.ToInt32(row["large_repair"]); row["next_medium_repair_date"] = onLine.AddDays(mediumRepair); row["next_large_repair_date"] = onLine.AddDays(largeRepair); } //没有导入的非字符串字段需要默认值 if (row[col].ToString() == "") { string typeName = col.DataType.Name; switch (typeName) { case "Int32": case "Double": row[col] = 0; break; case "DateTime": row[col] = new DateTime(1970, 1, 1); break; } } } //系统赋值 row["created_by"] = userID; row["updated_by"] = userID; row["created_time"] = now; row["updated_time"] = now; row["is_del"] = 0; } log.CreatedBy = userID; log.CreatedTime = now; dt.TableName = "equipment_copy"; using (TransactionScope scope = new TransactionScope()) { ret.data = _importExcelConfigRepo.BulkLoad(dt); await _importExcelConfigRepo.SaveLog(log); scope.Complete(); } ret.code = Code.Success; return(ret); } catch (Exception ex) { ret.code = Code.Failure; ret.msg = ex.Message; return(ret); } }
static void Main(string[] args) { // Initializer initializer = new Initializer(); //initializer.Seed(); AutoMapperConfig.Config(); string filePath = System.AppDomain.CurrentDomain.BaseDirectory + "Excle\\新建工作表.xlsx"; List <PointTxnDetailImport> excelList = ImportExcelHelper.GetListFromExcel <PointTxnDetailImport>(filePath); PointTxnDetailService pointTxnDetailService = new PointTxnDetailService(); var list1 = Mapper.Map <List <PointTxnDetailImport>, List <PointTxnDetail> >(excelList); list1.ForEach(p => { p.CreateUser = "******"; p.CredateTime = DateTime.Now; }); pointTxnDetailService.Add(list1); UserService userService = new UserService(); SysFunctionService sysFunctionService = new SysFunctionService(); RoleService roleService = new RoleService(); UserRightsService userRightsService = new UserRightsService(); SysButtonService sysButtonService = new SysButtonService(); SysMenuService sysMenuService = new SysMenuService(); UserRoleService userRoleService = new UserRoleService(); SystemService systemService = new SystemService(); StoreReportService storeReportService = new StoreReportService(); storeReportService.test(); var user = userService.Find(1); List <UserRole> list = userRoleService.GetUserRoles(); foreach (var item in list) { Console.WriteLine("角色名称:" + item.Role.RoleName); } try { string jsonString = list.ToJsonString(); Console.WriteLine("Json数据:" + jsonString); var userRoles = userRoleService.GetUserRole(); string jsonString2 = userRoles.ToJsonString(); Console.WriteLine("Json2数据:" + jsonString2); } catch (Exception ex) { string errorMsg = ex.Message; } List <UserRights> userRights = userRightsService.GetUserRights(user.Id); //<<<<<<< .mine // //List<SysMenu> SysMenuList = new List<SysMenu>(); // //try // //{ // // SysMenuList = userRights.Where(p => p.SysFunction.SysButtonId == 3).Select(p => p.SysFunction.SysMenu).ToList(); // //} // //catch (Exception ex) // //{ // // string msg = ex.Message; // //} // //List<SysButton> SysButtonList = userRights.Select(p => p.SysFunction.SysButton).ToList(); // //foreach (var item in SysMenuList) // //{ // // Console.WriteLine("菜单名称:" + item.MenuName); // //} // //foreach (var item in SysButtonList) // //{ // // Console.WriteLine("按钮名称:" + item.ButtonName); // //} //||||||| .r59 // List<SysMenu> SysMenuList = new List<SysMenu>(); // try // { // SysMenuList = userRights.Where(p => p.SysFunction.SysButtonId == 3).Select(p => p.SysFunction.SysMenu).ToList(); // } // catch (Exception ex) // { // string msg = ex.Message; // } // List<SysButton> SysButtonList = userRights.Select(p => p.SysFunction.SysButton).ToList(); // foreach (var item in SysMenuList) // { // Console.WriteLine("菜单名称:" + item.MenuName); // } // foreach (var item in SysButtonList) // { // Console.WriteLine("按钮名称:" + item.ButtonName); // } //======= // List<SysMenu> SysMenuList = new List<SysMenu>(); // //try // //{ // // SysMenuList = userRights.Where(p => p.SysFunction.SysButtonId == 3).Select(p => p.SysFunction.SysMenu).ToList(); // //} // //catch (Exception ex) // //{ // // string msg = ex.Message; // //} // //List<SysButton> SysButtonList = userRights.Select(p => p.SysFunction.SysButton).ToList(); // //foreach (var item in SysMenuList) // //{ // // Console.WriteLine("菜单名称:" + item.MenuName); // //} // //foreach (var item in SysButtonList) // //{ // // Console.WriteLine("按钮名称:" + item.ButtonName); // //} //>>>>>>> .r71 Console.WriteLine("按钮名称:"); Retry.Execute(Test, new TimeSpan(2000)); }
/// <summary> /// 导入数据 /// </summary> /// <param name="fileStream"></param> public void ImportPosition(FileStream fileStream) { Validate(); var excelHelper = new ImportExcelHelper(fileStream); List<string> codes = excelHelper.GetAllSHFECode(); List<SHFECodeClass> shfeCodeClasses = AnalysisSHFECodes(codes); //交易日期 DateTime dateTime = excelHelper.GetSHFEPositionDateTime(); string capitalAccount = excelHelper.GetSHFEPositionCapitalAccount(); //资金明细数据 SHFECapitalDetail capitalDetail = excelHelper.GetSHFECapitalDetail(); capitalDetail.CapitalAccount = capitalAccount; capitalDetail.TradeDate = dateTime; if (_selectedBrokerId != null) capitalDetail.AgentId = _selectedBrokerId.Value; if (_selectedInnerCustomer != null) capitalDetail.InternalBPId = _selectedInnerCustomer.Value; //成交汇总数据 List<SHFEPosition> shfePositionList = excelHelper.GetSHFEPosition(shfeCodeClasses); //持仓汇总数据 List<SHFEHoldingPosition> shfeHoldingPositions = excelHelper.GetSHFEHoldingPosition(shfeCodeClasses); List<SHFEFundFlow> shfeFundFlows = excelHelper.GetSHFEFundFlow(); using ( var shfePositionService = SvcClientManager.GetSvcClient<SHFEPositionServiceClient>(SvcType.SHFEPositionSvc)) { shfePositionService.ImportSHFEPosition(capitalDetail, shfePositionList, shfeHoldingPositions,shfeFundFlows,CurrentUser.Id); } }