//导出重名 private void Btn_SameName_Click(object sender, EventArgs e) { string path = txt_filepath.Text.Trim(); string exportpath = txt_exportpath.Text.Trim(); TableHelper tableHelper = new TableHelper(); DataTable dt = NpoiHelper.ExcelToDataTable("mysheet", true, filepath); DataTable table = dt.Clone(); List <string> record = new List <string>(); for (int i = 0; i < dt.Rows.Count; i++) { Regex reg = new Regex(@"\s/g"); string name = reg.Replace(dt.Rows[i]["name"].ToString(), ""); dt.Rows[i]["name"] = name; if (record.Contains(name)) { continue; } record.Add(name); DataRow[] rows = dt.Select("name = '" + name + "'"); if (rows.Length > 1) { foreach (DataRow item in rows) { //解决该行已经属于另一个表 table.Rows.Add(item.ItemArray); } } } NpoiHelper.DataTableToExcel(table, "mysheet", true, exportpath); MessageBox.Show("导出成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); }
//打开文件 private void OpenFile_Click(object sender, EventArgs e) { //定义一个文件打开控件 OpenFileDialog ofd = new OpenFileDialog(); //设置打开对话框的初始目录,默认目录为exe运行文件所在的路径 ofd.InitialDirectory = Application.StartupPath; //设置打开对话框的标题 ofd.Title = "请选择要打开的文件"; //设置打开对话框可以多选 ofd.Multiselect = true; //设置对话框打开的文件类型 ofd.Filter = "Excel-2003|*.xls|Excel-2007|*.xlsx|所有文件|*.*"; //设置文件对话框当前选定的筛选器的索引 ofd.FilterIndex = 2; //设置对话框是否记忆之前打开的目录 ofd.RestoreDirectory = true; if (ofd.ShowDialog() == DialogResult.OK) { //获取用户选择的文件完整路径 filepath = ofd.FileName; //获取对话框中所选文件的文件名和扩展名,文件名不包括路径 string fileName = ofd.SafeFileName; lab_filename.Text = fileName; dt = null; dt = NpoiHelper.ExcelToDataTable("mysheet", true, filepath); } }
private void GreatExcel() { string exportpath = txt_endpath.Text.Trim(); if (filepath == "") { MessageBox.Show("请先选择文件", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } //判断是否存在目录 TableHelper tableHelper = new TableHelper(); DataTable dt = NpoiHelper.ExcelToDataTable("mysheet", true, filepath); DataTable table = dt.Clone(); ValidateHelper validateHelper = new ValidateHelper(); for (int i = 0; i < dt.Rows.Count; i++) { string idcard = dt.Rows[i]["idcard"].ToString(); string userid = dt.Rows[i]["userid"].ToString(); string username = dt.Rows[i]["username"].ToString(); string areaname = dt.Rows[i]["areaname"].ToString(); bool istrue = validateHelper.IsIdCard(idcard); if (!istrue) { DataRow row = table.NewRow(); row["userid"] = userid; row["username"] = username; row["idcard"] = idcard; row["areaname"] = areaname; table.Rows.Add(row); } } NpoiHelper.DataTableToExcel(table, "mysheet", true, exportpath); MessageBox.Show("验证完成", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); }
/// <summary> /// DataTable to list /// </summary> public void ToListTest() { var filePath = "..\\..\\TestUseFile\\TestExport.xlsx"; var excelHelperTwo = new NpoiHelper(filePath); string message; var tableTwo = excelHelperTwo.ExcelToDataTable(null, true, out message); List <PersonGattScheduleInfoDto> tableToList = DataTypeConvertHelper.ToList <PersonGattScheduleInfoDto>(tableTwo); tableTwo.Rows.Count.IsEqualTo(tableToList.Count); }
private void tsmiImportGuest_Click(object sender, EventArgs e) { var ofd = new OpenFileDialog { Filter = "(Excel文件)|*.xls;*.xlsx;", Multiselect = false }; if (ofd.ShowDialog() == DialogResult.OK) { NpoiHelper npoi = new NpoiHelper(ofd.FileName); var dt = npoi.ExcelToDataTable("", true, out List <PicturesInfo> pictures); var jsonValue = JsonConvert.SerializeObject(dt); JsonSerializerSettings jsetting = new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore }; var objects = JsonConvert.DeserializeObject <List <EnrollmentModel> >(jsonValue, jsetting); if (objects == null) { return; } var persons = objects.Where(x => x.Name != null && x.Name != "").ToList(); for (int i = 0; i < persons.Count; i++) { persons[i].相片 = pictures[i].PictureData; var person = persons[i]; var img = new Bitmap(person.FaceImage); var imageFileName = Path.Combine($"GuestImages\\{person.ID}.jpg"); if (File.Exists(imageFileName)) { File.Delete(imageFileName); } img.Save(imageFileName); img.Dispose(); var info = new GuestInfo() { Id = person.ID, Name = person.Name, Gender = person.Gender, GuestType = person.GuestType, Entourage = person.Entourage, Labels = person.Labels, ImagePath = imageFileName, TableNo = person.TableNo, CreateTime = DateTime.Now, }; GuestMgr.SaveOrUpdateFace(info); } RefreshGuests(); MessageBox.Show("导入完成"); } }
public void ExcelToDataTableTest() { var filePath = "..\\..\\TestUseFile\\ExcelToDataTable.xls"; var excelHelper = new NpoiHelper(filePath); string message; var table = excelHelper.ExcelToDataTable(null, true, out message); table.Rows.Count.IsEqualTo(166); filePath = "..\\..\\TestUseFile\\TestExport.xlsx"; var excelHelperTwo = new NpoiHelper(filePath); var tableTwo = excelHelperTwo.ExcelToDataTable(null, true, out message); tableTwo.Rows.Count.IsEqualTo(162); }
/// <summary> /// list to datatable slowly /// </summary> public void ToDataTableSlowlyTest() { var filePath = "..\\..\\TestUseFile\\TestExportLessData.xlsx"; var excelHelperTwo = new NpoiHelper(filePath); string message; var tableTwo = excelHelperTwo.ExcelToDataTable("sheet1", true, out message); List <PersonGattScheduleInfoDto> personGantScheduleInfoDtosList = DataTypeConvertHelper.ToList <PersonGattScheduleInfoDto>(tableTwo); var storgeTable = DataTypeConvertHelper.ToDataTable(personGantScheduleInfoDtosList); tableTwo.Rows.Count.IsEqualTo(storgeTable.Rows.Count); var i = 5; var j = 4; tableTwo.Rows[i][j].IsEqualTo(storgeTable.Rows[i][j]); }
private void GreatExcel() { string path = txt_filepath.Text.Trim(); string exportpath = txt_exportpath.Text.Trim(); string exportphoto = txt_exportphoto.Text.Trim(); if (path == "") { MessageBox.Show("文件路径不能为空", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } //判断是否存在目录 if (!Directory.Exists(path)) { MessageBox.Show("文件路径不存在", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } if (filepath == "") { MessageBox.Show("请先选择文件", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } //判断是否存在目录 TableHelper tableHelper = new TableHelper(); DataTable dt = NpoiHelper.ExcelToDataTable("mysheet", true, filepath); //dt = tableHelper.ReplaceData(dt, "sfzz"); //dt = tableHelper.ReplaceData(dt, "sfxr"); FileHelper fileHelper = new FileHelper(); List <DirectoryInfo> folders = fileHelper.GetAllFoldersInPath(path); List <FileInfo> files = fileHelper.GetAllFilesInPaths(folders); for (int i = 0; i < dt.Rows.Count; i++) { string name = dt.Rows[i]["name"].ToString(); string newname = dt.Rows[i]["newname"].ToString(); if (name.Contains(" ")) { name = name.Replace(" ", ""); dt.Rows[i]["name"] = name; } string photo = fileHelper.IsExistFile(name, files, "default.jpg", newname, exportphoto); dt.Rows[i]["photo"] = photo; } NpoiHelper.DataTableToExcel(dt, "mysheet", true, exportpath); MessageBox.Show("导出成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); }
/// <summary> /// Special Schedule Import /// </summary> /// <param name="strPath"></param> /// <param name="adjustmentType"></param> private void AdjustmentTableImport(string strPath, string adjustmentType) { try { NpoiHelper npoiHelper = new NpoiHelper(strPath, false); DataTable dt = npoiHelper.ExcelToDataTable(0); Dictionary <string, string> dicDealerCode = new Dictionary <string, string>(); if (dt != null && dt.Rows.Count > 0) { int index = 2; foreach (DataRow row in dt.Rows) { string printDate = row["Exclude STK of below TN print date"].ToString(); string tnNo = row["Exclude below TN number"].ToString(); string tnProperty = row["TN Property"].ToString(); string serial = row["Serial Number"].ToString(); if (!printDate.IsNullString() || !tnNo.IsNullString() || !tnProperty.IsNullString()) { CBasAdjustmentTable item = new CBasAdjustmentTable(); Dashboard.Authentication.Authentication.UpdateEntity <CBasAdjustmentTable>(item); item.BasAdjustmentTableId = Guid.NewGuid().ToString(); item.AdjustmentType = adjustmentType; item.UserCode = Authentication.Authentication.CurrentUser.SysUserId; if (!printDate.IsNullString()) { DataValidator <DateTime>(row, "Exclude STK of below TN print date", index); item.PrintDate = Convert.ToDateTime(printDate); } item.TnNo = tnNo; item.TnProperty = tnProperty; item.Serial = serial;//排序字段 DataContext.CBasAdjustmentTable.Add(item); } index++; } } } catch (Exception ex) { throw ex; } }
/// <summary> /// Normal Schedule V Trans Mode Import /// </summary> /// <param name="basNormalScheduleId"></param> /// <param name="strPath"></param> private void NormalScheduleVTmImport(string basNormalScheduleId, string strPath) { try { NpoiHelper npoiHelper = new NpoiHelper(strPath, false); DataTable dt = npoiHelper.ExcelToDataTable(0); //替换为从表里获取 var pdcs = DataContext.CBasPdcSequence.Select(p => p.Pdc); //string[] pdcs = new string[] { "BJ", "SH", "GZ", "YZ", "CD" }; string[] transModes = new string[] { "FTL_Line", "FTL_DTD", "LTL", "Air", "Courier" }; Dictionary <string, string> dicDestination = new Dictionary <string, string>(); if (dt != null && dt.Rows.Count > 0) { int index = 2; foreach (DataRow row in dt.Rows) { NormalScheduleVTmImportCheckData(row, index); string destination = row["Destination"].ToString(); if (dicDestination.ContainsKey(destination)) { throw new Exception(string.Format(@"Destination ""{0}"" <=Duplicate>", destination)); } dicDestination.Add(destination, destination); CBasNormalScheduleVTm item = new CBasNormalScheduleVTm(); Dashboard.Authentication.Authentication.UpdateEntity <CBasNormalScheduleVTm>(item); item.BasNormalScheduleId = basNormalScheduleId; item.BasNormalScheduleVTmId = Guid.NewGuid().ToString(); item.Destination = row["Destination"].ToString(); item.Province = row["Province"].ToString(); if (!DataIsNullValidator(row, "1st_V_Cutoff_Day") && !DataIsNullValidator(row, "1st_V_Cutoff_Time") && !DataIsNullValidator(row, "1st_V_Pickup_Day") && !DataIsNullValidator(row, "1st_V_Pickup_Time") && !DataIsNullValidator(row, "1st_V_Trans_Mode") && !DataIsNullValidator(row, "1st_V_Leadtime") && !DataIsNullValidator(row, "1st_V_Leadtime_AM_PM") && !DataIsNullValidator(row, "1st_V_Leadtime_Start") && !DataIsNullValidator(row, "1st_V_Leadtime_End")) { DataValidator <DateTime>(row, "1st_V_Cutoff_Time", index); DataValidator <DateTime>(row, "1st_V_Pickup_Time", index); DataValidator <int>(row, "1st_V_Leadtime", index); DataValidator <DateTime>(row, "1st_V_Leadtime_Start", index); DataValidator <DateTime>(row, "1st_V_Leadtime_End", index); item.FirstVCutoffDay = row["1st_V_Cutoff_Day"].ToString(); item.FirstVCutoffTime = Convert.ToDateTime(row["1st_V_Cutoff_Time"].ToString()); item.FirstVPickupDay = row["1st_V_Pickup_Day"].ToString(); item.FirstVPickupTime = Convert.ToDateTime(row["1st_V_Pickup_Time"].ToString()); item.FirstVTransMode = row["1st_V_Trans_Mode"].ToString(); item.FirstVLeadtime = Convert.ToInt32(row["1st_V_Leadtime"].ToString()); item.FirstVLeadtimeAmPm = row["1st_V_Leadtime_AM_PM"].ToString(); item.FirstVLeadtimeStart = Convert.ToDateTime(row["1st_V_Leadtime_Start"].ToString()); item.FirstVLeadtimeEnd = Convert.ToDateTime(row["1st_V_Leadtime_End"].ToString()); } item.SecondVCutoffDay = row["2nd_V_Cutoff_Day"].ToString(); item.SecondVCutoffTime = Convert.ToDateTime(row["2nd_V_Cutoff_Time"].ToString()); item.SecondVPickupDay = row["2nd_V_Pickup_Day"].ToString(); item.SecondVPickupTime = Convert.ToDateTime(row["2nd_V_Pickup_Time"].ToString()); DataContext.CBasNormalScheduleVTm.Add(item); foreach (var strPdc in pdcs) { foreach (var strTransMode in transModes) { if (!dt.Columns.Contains(strPdc + "_2nd_V_" + strTransMode + "_Leadtime")) { continue; } if (row[strPdc + "_2nd_V_" + strTransMode + "_Leadtime"] != null && !row[strPdc + "_2nd_V_" + strTransMode + "_Leadtime"].ToString().IsNullString()) { DataValidator <int>(row, strPdc + "_2nd_V_" + strTransMode + "_Leadtime", index); DataValidator <DateTime>(row, strPdc + "_2nd_V_" + strTransMode + "_Leadtime_Start", index); DataValidator <DateTime>(row, strPdc + "_2nd_V_" + strTransMode + "_Leadtime_End", index); CBasNormalScheduleVTmDet itemDet = new CBasNormalScheduleVTmDet(); Dashboard.Authentication.Authentication.UpdateEntity <CBasNormalScheduleVTmDet>(itemDet); itemDet.BasNormalScheduleId = basNormalScheduleId; itemDet.BasNormalScheduleVTmId = item.BasNormalScheduleVTmId; itemDet.BasNormalScheduleVTmDetId = Guid.NewGuid().ToString(); itemDet.Pdc = strPdc; itemDet.TransMode = strTransMode; itemDet.SecondVLeadtime = Convert.ToInt32(row[strPdc + "_2nd_V_" + strTransMode + "_Leadtime"].ToString()); itemDet.SecondVLeadtimeAmPm = row[strPdc + "_2nd_V_" + strTransMode + "_Leadtime_AM_PM"].ToString(); itemDet.SecondVLeadtimeStart = Convert.ToDateTime(row[strPdc + "_2nd_V_" + strTransMode + "_Leadtime_Start"].ToString()); itemDet.SecondVLeadtimeEnd = Convert.ToDateTime(row[strPdc + "_2nd_V_" + strTransMode + "_Leadtime_End"].ToString()); DataContext.CBasNormalScheduleVTmDet.Add(itemDet); } } } index++; } } } catch (Exception ex) { throw ex; } }
/// <summary> /// Normal Schedule V Cargo Type Import /// </summary> /// <param name="basNormalScheduleId"></param> /// <param name="strPath"></param> private void NormalScheduleVCtImport(string basNormalScheduleId, string strPath) { try { NpoiHelper npoiHelper = new NpoiHelper(strPath, false); DataTable dt = npoiHelper.ExcelToDataTable(0); //替换为从表里获取 var warehouse = DataContext.CBasPdcSequence.Select(p => p.Pdc); //string[] warehouse = new string[] { "BJ", "SH", "GZ", "YZ", "CD" }; Dictionary <string, string> dicDealerCode = new Dictionary <string, string>(); if (dt != null && dt.Rows.Count > 0) { int index = 2; foreach (DataRow row in dt.Rows) { //验证指定字段不能为空 NormalScheduleVCtImportCheckData(row, index); string dealerCode = row["Dealer_Code"].ToString(); if (dicDealerCode.ContainsKey(dealerCode)) { throw new Exception(string.Format(@"Dealer Code ""{0}"" <=Duplicate>", dealerCode)); } dicDealerCode.Add(dealerCode, dealerCode); CBasNormalScheduleVCt item = new CBasNormalScheduleVCt(); Dashboard.Authentication.Authentication.UpdateEntity <CBasNormalScheduleVCt>(item); item.BasNormalScheduleId = basNormalScheduleId; item.BasNormalScheduleVCtId = Guid.NewGuid().ToString(); item.DealerCode = dealerCode; item.FacingPdc = row["Facing_PDC"].ToString(); item.DealerType = row["Dealer_Type"].ToString(); item.DealerName = row["Dealer_Name"].ToString(); item.Destination = row["Destination"].ToString(); item.Province = row["Province"].ToString(); if (!DataIsNullValidator(row, "1st_V_Cutoff_Day") && !DataIsNullValidator(row, "1st_V_Cutoff_Time") && !DataIsNullValidator(row, "1st_V_Pickup_Day") && !DataIsNullValidator(row, "1st_V_Pickup_Time") && !DataIsNullValidator(row, "1st_V_Trans_Mode") && !DataIsNullValidator(row, "1st_V_Leadtime") && !DataIsNullValidator(row, "1st_V_Leadtime_AM_PM") && !DataIsNullValidator(row, "1st_V_Leadtime_Start") && !DataIsNullValidator(row, "1st_V_Leadtime_End")) { DataValidator <DateTime>(row, "1st_V_Cutoff_Time", index); DataValidator <DateTime>(row, "1st_V_Pickup_Time", index); DataValidator <int>(row, "1st_V_Leadtime", index); DataValidator <DateTime>(row, "1st_V_Leadtime_Start", index); DataValidator <DateTime>(row, "1st_V_Leadtime_End", index); item.FirstVCutoffDay = row["1st_V_Cutoff_Day"].ToString(); item.FirstVCutoffTime = Convert.ToDateTime(row["1st_V_Cutoff_Time"].ToString()); item.FirstVPickupDay = row["1st_V_Pickup_Day"].ToString(); item.FirstVPickupTime = Convert.ToDateTime(row["1st_V_Pickup_Time"].ToString()); item.FirstVTransMode = row["1st_V_Trans_Mode"].ToString(); item.FirstVLeadtime = Convert.ToInt32(row["1st_V_Leadtime"].ToString()); item.FirstVLeadtimeAmPm = row["1st_V_Leadtime_AM_PM"].ToString(); item.FirstVLeadtimeStart = Convert.ToDateTime(row["1st_V_Leadtime_Start"].ToString()); item.FirstVLeadtimeEnd = Convert.ToDateTime(row["1st_V_Leadtime_End"].ToString()); } item.SecondVCutoffDay = row["2nd_V_Cutoff_Day"].ToString(); item.SecondVCutoffTime = Convert.ToDateTime(row["2nd_V_Cutoff_Time"].ToString()); item.SecondVPickupDay = row["2nd_V_Pickup_Day"].ToString(); item.SecondVPickupTime = Convert.ToDateTime(row["2nd_V_Pickup_Time"].ToString()); DataContext.CBasNormalScheduleVCt.Add(item); foreach (var strPdc in warehouse) { if (!dt.Columns.Contains(strPdc + "_2nd_V_Normal_Trans_Mode")) { continue; } DataValidator <int>(row, strPdc + "_2nd_V_Normal_Leadtime", index); DataValidator <DateTime>(row, strPdc + "_2nd_V_Normal_Leadtime_Start", index); DataValidator <DateTime>(row, strPdc + "_2nd_V_Normal_Leadtime_End", index); DataValidator <int>(row, strPdc + "_2nd_V_DG_Bulky_Leadtime", index); DataValidator <DateTime>(row, strPdc + "_2nd_V_DG_Bulky_Leadtime_Start", index); DataValidator <DateTime>(row, strPdc + "_2nd_V_DG_Bulky_Leadtime_End", index); CBasNormalScheduleVCtDet itemNormal = new CBasNormalScheduleVCtDet(); Dashboard.Authentication.Authentication.UpdateEntity <CBasNormalScheduleVCtDet>(itemNormal); itemNormal.BasNormalScheduleId = basNormalScheduleId; itemNormal.BasNormalScheduleVCtId = item.BasNormalScheduleVCtId; itemNormal.BasNormalScheduleVCtDetId = Guid.NewGuid().ToString(); itemNormal.Pdc = strPdc; itemNormal.CargoType = "Normal"; itemNormal.SecondVTransMode = row[strPdc + "_2nd_V_Normal_Trans_Mode"].ToString(); itemNormal.SecondVLeadtime = Convert.ToInt32(row[strPdc + "_2nd_V_Normal_Leadtime"].ToString()); itemNormal.SecondVLeadtimeAmPm = row[strPdc + "_2nd_V_Normal_Leadtime_AM_PM"].ToString(); itemNormal.SecondVLeadtimeStart = Convert.ToDateTime(row[strPdc + "_2nd_V_Normal_Leadtime_Start"].ToString()); itemNormal.SecondVLeadtimeEnd = Convert.ToDateTime(row[strPdc + "_2nd_V_Normal_Leadtime_End"].ToString()); DataContext.CBasNormalScheduleVCtDet.Add(itemNormal); CBasNormalScheduleVCtDet itemDgBulky = new CBasNormalScheduleVCtDet(); Dashboard.Authentication.Authentication.UpdateEntity <CBasNormalScheduleVCtDet>(itemDgBulky); itemDgBulky.BasNormalScheduleId = basNormalScheduleId; itemDgBulky.BasNormalScheduleVCtId = item.BasNormalScheduleVCtId; itemDgBulky.BasNormalScheduleVCtDetId = Guid.NewGuid().ToString(); itemDgBulky.Pdc = strPdc; itemDgBulky.CargoType = "DG/Bulky"; itemDgBulky.SecondVTransMode = row[strPdc + "_2nd_V_DG_Bulky_Trans_Mode"].ToString(); itemDgBulky.SecondVLeadtime = Convert.ToInt32(row[strPdc + "_2nd_V_DG_Bulky_Leadtime"].ToString()); itemDgBulky.SecondVLeadtimeAmPm = row[strPdc + "_2nd_V_DG_Bulky_Leadtime_AM_PM"].ToString(); itemDgBulky.SecondVLeadtimeStart = Convert.ToDateTime(row[strPdc + "_2nd_V_DG_Bulky_Leadtime_Start"].ToString()); itemDgBulky.SecondVLeadtimeEnd = Convert.ToDateTime(row[strPdc + "_2nd_V_DG_Bulky_Leadtime_End"].ToString()); DataContext.CBasNormalScheduleVCtDet.Add(itemDgBulky); } index++; } } } catch (Exception ex) { throw ex; } }
/// <summary> /// Normal Schedule S Import /// </summary> /// <param name="basNormalScheduleId"></param> /// <param name="strPath"></param> private void NormalScheduleSImport(string basNormalScheduleId, string strPath) { try { NpoiHelper npoiHelper = new NpoiHelper(strPath, false); DataTable dt = npoiHelper.ExcelToDataTable(0); Dictionary <string, string> dicDealerCode = new Dictionary <string, string>(); Dictionary <string, string> dicDealerCodePickupDay = new Dictionary <string, string>(); if (dt != null && dt.Rows.Count > 0) { int index = 2; foreach (DataRow row in dt.Rows) { NormalScheduleSImportCheckData(row, index); string dealerCode = row["Dealer_Code"].ToString(); string definition = row["Definition"].ToString(); if (dicDealerCode.ContainsKey(dealerCode + "_" + definition)) { throw new Exception(string.Format(@"Dealer Code ""{0}"" Definition ""{1}"" <=Duplicate>", dealerCode, definition)); } dicDealerCode.Add(dealerCode + "_" + definition, dealerCode + "_" + definition); string pickupDay = row["Pickup_Day"].ToString(); if (dicDealerCodePickupDay.ContainsKey(dealerCode + "_" + pickupDay)) { throw new Exception(string.Format(@"Dealer Code ""{0}"",Pickup Day ""{1}"" <=Duplicate>", dealerCode, pickupDay)); } dicDealerCodePickupDay.Add(dealerCode + "_" + pickupDay, dealerCode + "_" + pickupDay); CBasNormalScheduleS item = new CBasNormalScheduleS(); Dashboard.Authentication.Authentication.UpdateEntity <CBasNormalScheduleS>(item); item.BasNormalScheduleId = basNormalScheduleId; item.BasNormalScheduleSId = Guid.NewGuid().ToString(); item.DealerCode = row["Dealer_Code"].ToString(); item.FacingPdc = row["Facing_PDC"].ToString(); item.Definition = row["Definition"].ToString(); item.DealerType = row["Dealer_Type"].ToString(); item.ShortDealerName = row["Short_Dealer_Name"].ToString(); item.Destination = row["Destination"].ToString(); item.Province = row["Province"].ToString(); item.CutoffDay = Convert.ToInt32(row["Cutoff_Day"].ToString()); item.CutoffTime = Convert.ToDateTime(row["Cutoff_Time"].ToString()); item.PickupDay = Convert.ToInt32(row["Pickup_Day"].ToString()); item.PickupTime = Convert.ToDateTime(row["Pickup_Time"].ToString()); item.TmsRouteName = row["TMS_Route_Name"].ToString(); item.StopOrder = Convert.ToInt32(row["Stop_Order"].ToString()); item.TransMode = row["Trans_Mode"].ToString(); item.Overweek = Convert.ToInt32(row["Overweek"].ToString()); item.ArrivalDay = Convert.ToInt32(row["Arrival_Day"].ToString()); item.Eta = Convert.ToDateTime(row["ETA"].ToString()); item.ArrivalTime = row["Arrival_Time"].ToString(); item.ArrivalTimeStart = Convert.ToDateTime(row["Arrival_Time_Start"].ToString()); item.ArrivalTimeEnd = Convert.ToDateTime(row["Arrival_Time_End"].ToString()); item.Forwarder = row["Forwarder"].ToString(); DataContext.CBasNormalScheduleS.Add(item); index++; } } } catch (Exception ex) { throw ex; } }
/// <summary> /// Special Schedule Import /// </summary> /// <param name="strPath"></param> private void SpecialScheduleImport(string BasSpecialScheduleIndexId, string strPath) { try { //清除 var basSpecialScheduleIndex = DataContext.CBasSpecialScheduleIndex.FirstOrDefault(p => p.BasSpecialScheduleIndexId == BasSpecialScheduleIndexId); //保存上传的文件路径 basSpecialScheduleIndex.SpecilScheduleFile = strPath; DataContext.Entry(basSpecialScheduleIndex).State = System.Data.EntityState.Modified; var args = new DbParameter[] { new SqlParameter("@id", BasSpecialScheduleIndexId) }; DataContext.Database.ExecuteSqlCommand("DELETE BAS_SPECIAL_DAY WHERE BAS_SPECIAL_SCHEDULE_INDEX_ID=@id", args); var argst = new DbParameter[] { new SqlParameter("@id", BasSpecialScheduleIndexId) }; DataContext.Database.ExecuteSqlCommand("DELETE BAS_SPECIAL_SCHEDULE WHERE BAS_SPECIAL_SCHEDULE_INDEX_ID= @id", argst); //自动抓取的特殊日列表 List <DateTime> ArrBasSpecialDay = new List <DateTime>(); NpoiHelper npoiHelper = new NpoiHelper(strPath, false); DataTable dt = npoiHelper.ExcelToDataTable(0); Dictionary <string, string> dicDealerCode = new Dictionary <string, string>(); if (dt != null && dt.Rows.Count > 0) { int index = 2; foreach (DataRow row in dt.Rows) { DataValidator(row, "Dealer_Code", index); string dealerCode = row["Dealer_Code"].ToString(); string pickupTime = ""; #region 1st Stk if (row["1st_S_Pickup_Date"] != null && !row["1st_S_Pickup_Date"].ToString().IsNullString()) { DataValidator <DateTime>(row, "1st_S_Cutoff_Day", index); DataValidator <DateTime>(row, "1st_S_Cutoff_Time", index); DataValidator <DateTime>(row, "1st_S_Pickup_Date", index); DataValidator <DateTime>(row, "1st_S_Pickup_Time", index); DataValidator <DateTime>(row, "1st_S_Arrival_Date", index); DataValidator(row, "1st_S_Arrival_Time", index); DataValidator <DateTime>(row, "1st_S_Arrival_Time_Start", index); DataValidator <DateTime>(row, "1st_S_Arrival_Time_End", index); DateTime pickupDay = Convert.ToDateTime(row["1st_S_Pickup_Date"].ToString()); pickupTime = pickupDay.ToString("yyyy-MM-dd"); if (dicDealerCode.ContainsKey(dealerCode + "_" + pickupTime)) { throw new Exception(string.Format(@"Dealer Code ""{0}"" 1st_S_Pickup_Date ""{1}"", <=Duplicate>", dealerCode, pickupDay.ToString("yyyy-MM-dd"))); } dicDealerCode.Add(dealerCode + "_" + pickupTime, dealerCode + "_" + pickupTime); var oldFirstSpecialSchedule = DataContext.CBasSpecialSchedule.Where(p => p.DealerCode == dealerCode && p.PickupDay == pickupDay).FirstOrDefault(); if (oldFirstSpecialSchedule == null) { oldFirstSpecialSchedule = new CBasSpecialSchedule(); Dashboard.Authentication.Authentication.UpdateEntity <CBasSpecialSchedule>(oldFirstSpecialSchedule); oldFirstSpecialSchedule.BasSpecialScheduleId = Guid.NewGuid().ToString(); DataContext.CBasSpecialSchedule.Add(oldFirstSpecialSchedule); } else { Dashboard.Authentication.Authentication.UpdateEntity <CBasSpecialSchedule>(oldFirstSpecialSchedule); DataContext.CBasSpecialSchedule.Attach(oldFirstSpecialSchedule); DataContext.Entry(oldFirstSpecialSchedule).State = EntityState.Modified; } //标记特殊时效的归属关系 oldFirstSpecialSchedule.BasSpecialScheduleIndexId = BasSpecialScheduleIndexId; oldFirstSpecialSchedule.DealerCode = row["Dealer_Code"].ToString(); oldFirstSpecialSchedule.Definition = "1st Stk"; oldFirstSpecialSchedule.FacingPdc = row["Facing_PDC"].ToString(); oldFirstSpecialSchedule.DealerType = row["Dealer_Type"].ToString(); oldFirstSpecialSchedule.DealerName = row["Dealer_Name"].ToString(); oldFirstSpecialSchedule.Destination = row["Destination"].ToString(); oldFirstSpecialSchedule.Province = row["Province"].ToString(); oldFirstSpecialSchedule.CutoffDay = Convert.ToDateTime(row["1st_S_Cutoff_Day"].ToString()); oldFirstSpecialSchedule.CutoffTime = Convert.ToDateTime(row["1st_S_Cutoff_Time"].ToString()); oldFirstSpecialSchedule.PickupDay = Convert.ToDateTime(row["1st_S_Pickup_Date"].ToString()); oldFirstSpecialSchedule.PickupTime = Convert.ToDateTime(row["1st_S_Pickup_Time"].ToString()); oldFirstSpecialSchedule.TransMode = row["1st_S_Trans_Mode"].ToString(); oldFirstSpecialSchedule.ArrivalDay = Convert.ToDateTime(row["1st_S_Arrival_Date"].ToString()); oldFirstSpecialSchedule.ArrivalTime = row["1st_S_Arrival_Time"].ToString(); oldFirstSpecialSchedule.ArrivalTimeStart = Convert.ToDateTime(row["1st_S_Arrival_Time_Start"].ToString()); oldFirstSpecialSchedule.ArrivalTimeEnd = Convert.ToDateTime(row["1st_S_Arrival_Time_End"].ToString()); if (oldFirstSpecialSchedule.PickupDay < basSpecialScheduleIndex.EffectiveDate || oldFirstSpecialSchedule.PickupDay > basSpecialScheduleIndex.ExpirationDate) { throw new Exception(string.Format(@"Dealer Code ""{0}"", ""{1}"" <=PickupDayError>, <=Duplicate>", dealerCode, pickupDay.ToString("yyyy-MM-dd"))); } #region 自动抓取特殊日 ArrBasSpecialDay.Add(oldFirstSpecialSchedule.PickupDay); #endregion } #endregion #region 2nd Stk if (row["2nd_S_Pickup_Date"] != null && !row["2nd_S_Pickup_Date"].ToString().IsNullString()) { DataValidator <DateTime>(row, "2nd_S_Cutoff_Day", index); DataValidator <DateTime>(row, "2nd_S_Cutoff_Time", index); DataValidator <DateTime>(row, "2nd_S_Pickup_Date", index); DataValidator <DateTime>(row, "2nd_S_Pickup_Time", index); DataValidator <DateTime>(row, "2nd_S_Arrival_Date", index); DataValidator(row, "2nd_S_Arrival_Time", index); DataValidator <DateTime>(row, "2nd_S_Arrival_Time_Start", index); DataValidator <DateTime>(row, "2nd_S_Arrival_Time_End", index); DateTime pickupDay = Convert.ToDateTime(row["2nd_S_Pickup_Date"].ToString()); pickupTime = pickupDay.ToString("yyyy-MM-dd"); if (dicDealerCode.ContainsKey(dealerCode + "_" + pickupTime)) { throw new Exception(string.Format(@"Dealer Code ""{0}"" 2nd_S_Pickup_Date ""{1}"", <=Duplicate>", dealerCode, pickupDay.ToString("yyyy-MM-dd"))); } dicDealerCode.Add(dealerCode + "_" + pickupTime, dealerCode + "_" + pickupTime); var oldFirstSpecialSchedule = DataContext.CBasSpecialSchedule.Where(p => p.DealerCode == dealerCode && p.PickupDay == pickupDay).FirstOrDefault(); if (oldFirstSpecialSchedule == null) { oldFirstSpecialSchedule = new CBasSpecialSchedule(); Dashboard.Authentication.Authentication.UpdateEntity <CBasSpecialSchedule>(oldFirstSpecialSchedule); oldFirstSpecialSchedule.BasSpecialScheduleId = Guid.NewGuid().ToString(); DataContext.CBasSpecialSchedule.Add(oldFirstSpecialSchedule); } else { Dashboard.Authentication.Authentication.UpdateEntity <CBasSpecialSchedule>(oldFirstSpecialSchedule); DataContext.CBasSpecialSchedule.Attach(oldFirstSpecialSchedule); DataContext.Entry(oldFirstSpecialSchedule).State = EntityState.Modified; } oldFirstSpecialSchedule.BasSpecialScheduleIndexId = BasSpecialScheduleIndexId; oldFirstSpecialSchedule.DealerCode = row["Dealer_Code"].ToString(); oldFirstSpecialSchedule.Definition = "2nd Stk"; oldFirstSpecialSchedule.FacingPdc = row["Facing_PDC"].ToString(); oldFirstSpecialSchedule.DealerType = row["Dealer_Type"].ToString(); oldFirstSpecialSchedule.DealerName = row["Dealer_Name"].ToString(); oldFirstSpecialSchedule.Destination = row["Destination"].ToString(); oldFirstSpecialSchedule.Province = row["Province"].ToString(); oldFirstSpecialSchedule.CutoffDay = Convert.ToDateTime(row["2nd_S_Cutoff_Day"].ToString()); oldFirstSpecialSchedule.CutoffTime = Convert.ToDateTime(row["2nd_S_Cutoff_Time"].ToString()); oldFirstSpecialSchedule.PickupDay = Convert.ToDateTime(row["2nd_S_Pickup_Date"].ToString()); oldFirstSpecialSchedule.PickupTime = Convert.ToDateTime(row["2nd_S_Pickup_Time"].ToString()); oldFirstSpecialSchedule.TransMode = row["2nd_S_Trans_Mode"].ToString(); oldFirstSpecialSchedule.ArrivalDay = Convert.ToDateTime(row["2nd_S_Arrival_Date"].ToString()); oldFirstSpecialSchedule.ArrivalTime = row["2nd_S_Arrival_Time"].ToString(); oldFirstSpecialSchedule.ArrivalTimeStart = Convert.ToDateTime(row["2nd_S_Arrival_Time_Start"].ToString()); oldFirstSpecialSchedule.ArrivalTimeEnd = Convert.ToDateTime(row["2nd_S_Arrival_Time_End"].ToString()); if (oldFirstSpecialSchedule.PickupDay < basSpecialScheduleIndex.EffectiveDate || oldFirstSpecialSchedule.PickupDay > basSpecialScheduleIndex.ExpirationDate) { throw new Exception(string.Format(@"Dealer Code ""{0}"", ""{1}"" <=PickupDayError>, <=Duplicate>", dealerCode, pickupDay.ToString("yyyy-MM-dd"))); } #region 自动抓取特殊日 ArrBasSpecialDay.Add(oldFirstSpecialSchedule.PickupDay); #endregion } #endregion index++; } //添加自动抓取的特殊日到数据库 var ReArrBasSpecialDay = ArrBasSpecialDay.Distinct(); foreach (var item in ReArrBasSpecialDay) { CBasSpecialDay BasSpecialDay = new CBasSpecialDay(); Dashboard.Authentication.Authentication.UpdateEntity <CBasSpecialDay>(BasSpecialDay); BasSpecialDay.SpecialDay = item; BasSpecialDay.BasSpecialScheduleIndexId = BasSpecialScheduleIndexId; BasSpecialDay.BasSpecialDayId = Guid.NewGuid().ToString(); DataContext.CBasSpecialDay.Add(BasSpecialDay); } } } catch (Exception ex) { throw ex; } }
private void TryBuild_Click(object sender, EventArgs e) { string table = txt_table.Text.Trim(); if (table == "") { MessageBox.Show("表名称不能为空", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } if (filepath == "") { MessageBox.Show("请先选择文件", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } List <string> sqllist = new List <string>(); //获取excel成datatable DataTable dt = NpoiHelper.ExcelToDataTable("mysheet", true, filepath); int dt_column_count = dt.Columns.Count; int dt_row_count = dt.Rows.Count; List <string> columns = new List <string>();//excel中字段列 for (int i = 0; i < dt_column_count; i++) { columns.Add(dt.Columns[i].ColumnName); } object[] auto = GetGridviewValue(); List <string> auto_columns = (List <string>)auto[0]; //自动字段 List <string> auto_values = (List <string>)auto[1]; //自动字段类型 List <string> all_columns = new List <string>(); //插入的所有字段 all_columns.AddRange(columns); all_columns.AddRange(auto_columns); for (int i = 0; i < dt_row_count; i++) { List <string> values = new List <string>(); for (int j = 0; j < dt_column_count; j++) { values.Add(dt.Rows[i][j].ToString()); } //自动字段赋值 for (int n = 0; n < auto_values.Count; n++) { string autotype = auto_values[n]; if (autotype == "当前时间") { values.Add(DateTime.Now.ToString()); } else if (autotype == "时间字符") { values.Add(DateTime.Now.ToString("yyyyMMddHHmmssfff")); } else if (autotype == "guid") { } } //拼写sql string sql_one = string.Format("insert into {0}({1}) values('{2}')", table, string.Join(",", all_columns) , string.Join("','", values)); sqllist.Add(sql_one); } //写入文本及导出 string lujing = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "插入语句.txt"); File.WriteAllLines(lujing, sqllist); if (MessageBox.Show("已生成,需要打开吗?", "打开文件", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes) { Process.Start(lujing); } }
private void Btn_UpdateSql_Click(object sender, EventArgs e) { string table = txt_table.Text.Trim(); string where_zd = txt_where.Text.Trim(); if (table == "") { MessageBox.Show("表名称不能为空", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } if (filepath == "") { MessageBox.Show("请先选择文件", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } List <string> sqllist = new List <string>(); //获取excel成datatable DataTable dt = NpoiHelper.ExcelToDataTable("mysheet", true, filepath); int dt_column_count = dt.Columns.Count; int dt_row_count = dt.Rows.Count; List <string> columns = new List <string>();//excel中字段列 for (int i = 0; i < dt_column_count; i++) { columns.Add(dt.Columns[i].ColumnName); } object[] auto = GetGridviewValue(); List <string> auto_columns = (List <string>)auto[0]; //自动字段 List <string> auto_values = (List <string>)auto[1]; //自动字段类型 List <string> all_columns = new List <string>(); //插入的所有字段 all_columns.AddRange(columns); all_columns.AddRange(auto_columns); string sql = ""; for (int i = 0; i < dt_row_count; i++) { sql += dt.Rows[i]["userid"] + ","; } sqllist.Add(sql); #region MyRegion //for (int i = 0; i < dt_row_count; i++) //{ // List<string> values = new List<string>(); // for (int j = 0; j < dt_column_count; j++) // { // values.Add(dt.Rows[i][j].ToString()); // } // //自动字段赋值 // for (int n = 0; n < auto_values.Count; n++) // { // string autotype = auto_values[n]; // if (autotype == "当前时间") // { // values.Add(DateTime.Now.ToString()); // } // else if (autotype == "时间字符") // { // values.Add(DateTime.Now.ToString("yyyyMMddHHmmssfff")); // } // } // string updatesql = ""; // for (int j = 0; j < all_columns.Count; j++) // { // if (j == 0) // { // updatesql += string.Format(" set {0} = {1},", all_columns[j], values[j]); // } // else if (j == all_columns.Count - 1) // { // updatesql += string.Format(" {0} = {1}", all_columns[j], values[j]); // } // else // { // updatesql += string.Format(" {0} = {1},", all_columns[j], values[j]); // } // } // string where = string.Format(" where {0} = '{1}'", dt[]); // //拼写sql // string sql_one = string.Format("update {0} {1} {2}", table, updatesql // , string.Join("','", values)); // sqllist.Add(sql_one); //} #endregion //写入文本及导出 string lujing = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "更新语句.txt"); File.WriteAllLines(lujing, sqllist); if (MessageBox.Show("已生成,需要打开吗?", "打开文件", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes) { Process.Start(lujing); } }