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