//导出重名
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);
}
}
