/// <summary>
/// IDataReader to entity list.
/// </summary>
public void ToEntityTest()
{
var filePath = "..\\..\\TestUseFile\\TestExport.xlsx";
var excelHelperTwo = new NpoiHelper(filePath);
string message;
var tableTwo = excelHelperTwo.ExcelToDataTable(null, true, out message);
IDataReader dr = tableTwo.CreateDataReader();
List <PersonGattScheduleInfoDto> tableToList =
DataTypeConvertHelper.ToList <PersonGattScheduleInfoDto>(dr);
tableTwo.Rows.Count.IsEqualTo(tableToList.Count);
}
public JsonResult Upload(string data)
{
var examFile = JsonHelper.Deserialize <ExamFiles>(data);
if (examFile == null)
{
return(Json(ErrorModel.InputError));
}
var result = NpoiHelper.AnalysisExamQuestion(examFile);
return(Json(result));
}
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);
}
public ActionResult RenderToExcel(PubPage fg)
{
string sql = "select Telephone,Pwd from SystemUser";
var dt = _iSysUserServices.ToDataTable(sql, null);
string[] Telephone = { "手机号", "密码" };
for (int i = 0; i < dt.Columns.Count; i++)
{
dt.Columns[i].ColumnName = Telephone[i];
}
var ms = NpoiHelper.RenderToExcel(dt);
var xlsx = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
var xls = "application/vnd.ms-excel";
return(File(ms, xls, "用户信息" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls"));
}
/// <summary>
/// list to datatable fast
/// </summary>
public void ToDataTableTest()
{
var filePath = "..\\..\\TestUseFile\\TestExport.xlsx";
var excelHelperTwo = new NpoiHelper(filePath);
string message;
var tableTwo = excelHelperTwo.ExcelToDataTable(null, 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>
/// 获取NPOI工具类实例
/// </summary>
/// <param name="isCompatible">是否生成兼容模式EXCEL(XLS),默认为兼容模式</param>
/// <returns></returns>
public static NpoiHelper GetInstance(bool isCompatible = true)
{
IsCompatible = isCompatible;
if (_obj == null)
{
_obj = new NpoiHelper();
}
if (IsCompatible)
{
_obj.Extend = @".xls";
}
else
{
_obj.Extend = @".xlsx";
}
return(_obj);
}
private void tsmiExportGuest_Click(object sender, EventArgs e)
{
var sfd = new SaveFileDialog
{
Filter = "(Excel文件)|*.xls;",
FileName = "E&E宾客名单"
};
if (sfd.ShowDialog() == DialogResult.OK)
{
// ID 姓名 相片 性别 宾客类型 标签 随行人员 桌号 礼金
List <string> headers = new List <string>
{
"Id",
"姓名",
"相片",
"性别",
"宾客类型",
"标签",
"随行人员",
"桌号",
"礼金"
};
List <string> arries = new List <string>
{
"Id",
"Name",
"Image",
"Gender",
"GuestTypeStr",
"Labels",
"Entourage",
"TableNo",
"CashGift"
};
var data = GlobalConfigs.Guests;
NpoiHelper.ToExcel(data, sfd.FileName, arries, headers);
MessageBox.Show("导出完成");
}
}
/// <summary>
/// Npoi的测试
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnNpoi_Click(object sender, EventArgs e)
{
try
{
this.richTextBox3.Text += LineBreak + " 开始.....";
var file_name = string.Format(@"{0}\\{1}", CACHE_FILE_PATH, "Demo.xls");
NpoiHelper.CreateExcel(file_name, "Demo");
this.richTextBox3.Text += LineBreak + string.Format("创建 {0} 成功！", file_name);
}
catch (Exception exc)
{
this.richTextBox3.Text += LineBreak + exc.Message;
}
finally
{
this.richTextBox3.Text += LineBreak + " 结束.....";
}
}
public JsonResult Export()
{
try
{
//导出所有数据
var All_ListData = UserEntites.UserInfo.ToList();
//将list 转化为datatable类型
var Dt = DatabaseOpreas._.ListToDataTable(All_ListData);
NpoiHelper.Export(Dt, "用户信息", Server.MapPath("~/Content/Export.xls"));//这里的路径是需要写入你需要保存的文件格式的，不需要创建自动检测创建
return(Json(new { code = 1, msg = "/Content/Export.xls" }));
}
catch (Exception ex)
{
return(Json(new { code = 0, msg = ex.Message }));
}
}
private DataTable ExcelToTable(string filePath)
{
DataTable dt = null;
if (string.IsNullOrEmpty(filePath))
{
return(dt);
}
if (filePath.ToLower().EndsWith("xls"))
{
dt = NpoiHelper.GetInstance().ImportFromExcel(filePath, "0", 0);
}
else if (filePath.ToLower().EndsWith("xlsx"))
{
dt = NpoiHelper.GetInstance(false).ImportFromExcel(filePath, "0", 0);
}
return(dt);
}
/// <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;
}
}
//从excel中导入人员
private void ExcuteCmdInputPersons()
{
string xpath;
List <viewPerson> viewPeople = new List <viewPerson>();
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
if (openFileDialog.ShowDialog() == true)
{
xpath = openFileDialog.FileName;
viewPeople = NpoiHelper.ReadToList <viewPerson>(xpath, 0);
}
if (viewPeople.Count > 0)
{
using (var db = new DataModel())
{
foreach (var item in viewPeople)
{
if (db.Persons.Where(p => p.PersonName == item.Name && p.Department.DepartmentName == item.Department).Count() <= 0)
{
Department department = db.Departments.Where(d => d.DepartmentName == item.Department).First();
if (department != null)
{
Person person = new Person()
{
DepId = department.DepartmentId, PersonName = item.Name
};
db.Persons.Add(person);
}
}
}
db.SaveChanges();
}
int tempid = 0;
if (QueryDepartment != null)
{
tempid = QueryDepartment.DepartmentId;
}
Inital();
QueryDepartment = Departments.Where(d => d.DepartmentId == tempid).FirstOrDefault();
ExcuteCbChangeCommand();
}
}
private void btnLoad_Click(object sender, EventArgs e)
{
string rootpath = Path.Combine(System.Environment.CurrentDirectory, "read");
var files = Directory.GetFiles(rootpath, "*.xls");
float sumOfFloder = 0;//当前文件夹下所有充电
foreach (var item in files)
{
FileInfo fi = new FileInfo(item);
var dt = NpoiHelper.ImportExcelFile(fi.FullName);//通过NPOI读取excel
//dgv.DataSource = dt;//测试时候, 看下内容是否正确
//break;
var sum = GetSum(dt, "充电量");// dt.Compute("sum(充电量)", "");//由于读取时候全部作为字符串处理,所以这个方法不正确的
sumOfFloder += sum;
txtResult.AppendText(fi.Name + "\t" + sum.ToString() + Environment.NewLine);
}
txtResult.AppendText("充电量合计:" + "\t" + sumOfFloder.ToString() + Environment.NewLine);
}
public ActionResult ImportPersonData()
{
string UnitID = RequestHelper.GetStrByName("UnitID"),
RowID = RequestHelper.GetStrByName("RowID");
if (string.IsNullOrEmpty(UnitID) || string.IsNullOrEmpty(RowID))
{
return(operateContext.RedirectAjax(1, "参数异常~", "", ""));
}
var files = Request.Files;
if (files == null || files.Count <= 0)
{
return(null);
}
string file_name = "工资模板" + operateContext.Usr.login_name;
string pathServer = "~/UpFile/WageManager";
string path = Server.MapPath(pathServer);
if (!Directory.Exists(path.ToString()))
{
Directory.CreateDirectory(path.ToString());//文件夹不存在则创建
}
HttpPostedFileBase file = Request.Files["filePerson"];
//1.上传文档
string sreUrl = Server.MapPath(pathServer + "/" + file_name + "." + file.FileName.Split('.')[1]);
file.SaveAs(sreUrl);//上传文件
System.Data.DataTable dt = NpoiHelper.ImportExcelToDataTable(sreUrl);
if (null == dt || dt.Rows.Count <= 0)
{
return(operateContext.RedirectAjax(1, "导入数据为空~", "", ""));
}
bool mark = operateContext.bllSession.WGJG02_Template.ImportPersonData(dt, UnitID, RowID);
if (mark)
{
return(operateContext.RedirectAjax(0, "导入成功~", "", ""));
}
return(operateContext.RedirectAjax(1, "导入失败~", "", ""));
}
public Form1()
{
InitializeComponent();
list.Add(new TestData {
Name = "zhangshan", Age = 40, Birthday = DateTime.Now
});
list.Add(new TestData {
Name = "lisi", Age = 28, Birthday = DateTime.Now
});
list.Add(new TestData {
Name = "wangwu", Age = 36, Birthday = DateTime.Now
});
list1.Add(new ComplexData {
Name = "zhangshan", Age = 40, Birthday = DateTime.Now, Detail = new Detail {
Tel = "02885394654", HomeAddr = new Address {
CommonAddr = "黄河村", PostNo = "610048"
}
}
});
DataTable dt = new DataTable("TestTable");
dt.Columns.Add("Name");
dt.Columns.Add("Age");
dt.Columns.Add("Birthday");
DataRow dr = dt.NewRow();
dr[0] = "zhangshan";
dr[1] = 50;
dr[2] = DateTime.Now;
dt.Rows.Add(dr);
NpoiHelper.GetInstance().ExportToExcel(dt);
//NpoiHelper.GetInstance().ExportToExcel<TestData>(list);
//NpoiHelper.GetInstance().ExportToExcel<ComplexData>(list1);
//string file = AppDomain.CurrentDomain.BaseDirectory + "excel.xls";
//NpoiHelper.GetInstance().ImportFromExcel(file,"0",0);
}
/// <summary>
/// 开始生成
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
if (this.filenameDing == null || this.filenameHe == null)
{
MessageBox.Show("请先选择文件！", "错误警告");
return;
}
DataTable dtDing = NpoiHelper.Import(filenameDing, getFileSuffiex(filenameDing));
DataTable dtHe = NpoiHelper.Import(filenameHe, getFileSuffiex(filenameHe));
for (int i = 3; i < dtDing.Rows.Count; i++)
{
DataRow dr = dtDing.Rows[i];
this.RowHandleDingToHe(dtHe, dr);
for (int j = 0; j < dr.ItemArray.Length; j++)
{
Console.Out.Write(j + "=>" + dr.ItemArray[j].ToString() + ",");
}
Console.Out.WriteLine();
}
//保存到桌面
saveFile(dtHe);
}
/// <summary>
/// 导出遥测跳变历史记录
/// </summary>
/// <param name="httpRequest"></param>
/// <param name="httpResponse"></param>
private void ExpYctbls(HttpRequest httpRequest, HttpResponse httpResponse)
{
try
{
string tmp = httpRequest["dateBegin"] ?? "";
DateTime dateBegin = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd")).AddDays(-1);
DateTime dateEnd = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd")).AddDays(1);
if (!string.IsNullOrEmpty(tmp))
{
dateBegin = DateTime.Parse(tmp);
}
tmp = httpRequest["dateEnd"] ?? "";
if (!string.IsNullOrEmpty(tmp))
{
dateEnd = DateTime.Parse(tmp).AddDays(1);
}
DataTable dt = _wrBll.Query(Constant.YclxYctb, dateBegin, dateEnd);
string[] title = new string[]
{
"充电站", "桩类型", "桩型号", "装编号", "数据项", "阈值最小值", "阈值最大值",
"告警值", "出错原因", "是否处理", "灭警方式", "灭警时间", "灭警人"
};
dt.Columns.Remove("yxstates");
dt.Columns.Remove("yxwarn");
dt.Columns.Remove("eff_min");
dt.Columns.Remove("eff_max");
//dt.Columns.Remove("mvalue");
dt.Columns.Remove("yxeff");
foreach (DataRow dr in dt.Rows)
{
if (dr["processflag"].ToString() == "0")
{
dr["isproc"] = "未处理";
dr["procm"] = "未处理";
}
else if (dr["processflag"].ToString() == "1")
{
dr["isproc"] = "已处理";
dr["procm"] = "自动灭警";
}
else
{
dr["isproc"] = "已处理";
dr["procm"] = "手动灭警";
}
}
dt.Columns.Remove("processflag");
NpoiHelper nh = new NpoiHelper(title, dt);
Stream ms = nh.ToExcel();
if (null == ms)
{
return;
}
byte[] exp = new byte[ms.Length];
ms.Read(exp, 0, (int)ms.Length);
httpResponse.ContentType = "application/x-zip-compressed";
httpResponse.AddHeader("Content-Disposition", "attachment;filename=" +
HttpUtility.UrlEncode("遥测跳变历史.xls", System.Text.Encoding.UTF8));
httpResponse.BinaryWrite(exp);
}
catch (Exception ex)
{
Log.Error(ex);
httpResponse.Write(ErrorJson);
}
}
/// <summary>
/// 导入确认
/// </summary>
/// <param name="matchid"></param>
/// <param name="fid"></param>
/// <returns></returns>
public ActionResult confirm(string matchid, string fid)
{
try
{
ViewBag.error = "0";
ViewBag.matchid = matchid;
ViewBag.fid = fid;
List <tblmatchentity> lstMatchusers = new List <tblmatchentity>();
DataTable data = NpoiHelper.XlSToDataTable(System.IO.Path.Combine(Server.MapPath("~/upload/file"), fid), "TTBM", 0);
if (data == null || data.Rows.Count < 1)
{
return(View(lstMatchusers));
}
GroupBll bll = new GroupBll();
tblmatch match = bll.GetMatchById(matchid);
ViewBag.matchname = HttpUtility.HtmlEncode(match.Match_name);
StringBuilder sbtError = new StringBuilder();
int sn = 0;
string lineid = "";
string teamno = "";
string teamname = "";
string company = "";
int year = 0;
foreach (DataRow row in data.Rows)
{
sbtError.Clear();
year = 0;
if (string.IsNullOrEmpty(row["队员姓名"].ToString().Trim()))
{
continue;
}
//记录序号，以标记团队
if (!string.IsNullOrEmpty(row["序号"].ToString().Trim()))
{
sn = int.Parse(row["序号"].ToString().Trim());
}
if (!string.IsNullOrEmpty(row["路线名称"].ToString().Trim()))
{
lineid = row["路线名称"].ToString().Trim();
var d = bll.GetLineByName(lineid);
if (d == null)
{
sbtError.AppendFormat("[路线名称:{0}]不存在;", lineid);
}
}
if (!string.IsNullOrEmpty(row["队列号"].ToString().Trim()))
{
teamno = row["队列号"].ToString().Trim();
}
if (!string.IsNullOrEmpty(row["队名(6个字符以内)"].ToString().Trim()))
{
teamname = row["队名(6个字符以内)"].ToString().Trim();
}
if (!string.IsNullOrEmpty(row["单位名称"].ToString().Trim()))
{
company = row["单位名称"].ToString().Trim();
}
if (string.IsNullOrEmpty(row["性别"].ToString().Trim()))
{
sbtError.Append("[性别]不能为空;");
}
if (string.IsNullOrEmpty(row["身份证/护照"].ToString().Trim()))
{
sbtError.Append("[身份证/护照]不能为空;");
}
else
{
if (!System.Text.RegularExpressions.Regex.IsMatch(row["身份证/护照"].ToString().Trim(), @"^(^\d{15}$|^\d{18}$|^\d{17}(\d|X|x))$"))
{
sbtError.Append("[身份证/护照]格式错误;");
}
}
if (!System.Text.RegularExpressions.Regex.IsMatch(row["手机号码"].ToString().Trim(), @"^[1]+[0-9]+\d{9}"))
{
sbtError.AppendFormat("[手机号码:{0}]格式错误;", row["手机号码"]);
}
if (!int.TryParse(row["年龄"].ToString().Trim(), out year))
{
sbtError.AppendFormat("[年龄:{0}]是否输入及正确;", row["年龄"]);
}
if (sbtError.Length > 0)
{
ViewBag.error = "-1";
}
tblmatchentity muser = new tblmatchentity();
muser.Pnov = sn.ToString();
muser.Teamname = teamname;
muser.Cardno = row["身份证/护照"].ToString().Trim();
muser.Cardtype = "1";
muser.Createtime = DateTime.Now;//.ToString("yyyy-MM-dd");
//muser.Leader = row["队员编号"].ToString().Trim() == "队长" ? 1 : 0;
muser.Match_Id = "";
muser.Matchuserid = Guid.NewGuid().ToString();
muser.Mobile = row["手机号码"].ToString().Trim();
muser.Nickname = row["队员姓名"].ToString().Trim();
muser.Lineno = lineid;
muser.LeaderM = row["队员编号"].ToString().Trim() == "队长" ? "是" : "";
muser.Sexy = row["性别"].ToString().Trim() == "男" ? 1 : 0;
muser.Age = year;
muser.Mono = row["是否健康"].ToString().Trim();
muser.Content = HttpUtility.HtmlEncode(sbtError.ToString());
lstMatchusers.Add(muser);
}
return(View(lstMatchusers));
}
catch (Exception ex)
{
ILog log = LogManager.GetLogger(this.GetType());
log.Error(ex);
ViewBag.error = "-2";
return(View());
}
}
public ActionResult beginimp(string matchid, string fid)
{
try
{
List <tblmatchentity> lstMatchusers = new List <tblmatchentity>();
DataTable data = NpoiHelper.XlSToDataTable(System.IO.Path.Combine(Server.MapPath("~/upload/file"), fid), "TTBM", 0);
GroupBll bll = new GroupBll();
tblmatch match = bll.GetMatchById(matchid);
int sn = 0;
string lineid = "";
string teamno = "";
string teamname = "";
string company = "";
int year = 0;
int dm = 0;
foreach (DataRow row in data.Rows)
{
year = 0;
if (string.IsNullOrEmpty(row["队员姓名"].ToString().Trim()))
{
continue;
}
//记录序号，以标记团队
if (!string.IsNullOrEmpty(row["序号"].ToString().Trim()))
{
sn = int.Parse(row["序号"].ToString().Trim());
}
if (!string.IsNullOrEmpty(row["路线名称"].ToString().Trim()))
{
var d = bll.GetLineByName(row["路线名称"].ToString().Trim());
lineid = d.Lineid;
}
if (!string.IsNullOrEmpty(row["队列号"].ToString().Trim()))
{
teamno = row["队列号"].ToString().Trim();
int.TryParse(teamno, out dm);
}
if (!string.IsNullOrEmpty(row["队名(6个字符以内)"].ToString().Trim()))
{
teamname = row["队名(6个字符以内)"].ToString().Trim();
}
if (!string.IsNullOrEmpty(row["单位名称"].ToString().Trim()))
{
company = row["单位名称"].ToString().Trim();
}
tblmatchentity muser = new tblmatchentity();
muser.Pnov = sn.ToString();
muser.Teamname = teamname;
muser.Teamno = dm;
muser.Cardno = row["身份证/护照"].ToString().Trim();
muser.Cardtype = "1";
muser.Leader = row["队员编号"].ToString().Trim() == "队长" ? 1 : 0;
muser.Match_Id = matchid;
muser.Mobile = row["手机号码"].ToString().Trim();
muser.Nickname = row["队员姓名"].ToString().Trim();
muser.Lineno = lineid;
muser.Sexy = row["性别"].ToString().Trim() == "男" ? 1 : 0;
muser.Passwd = company;
int.TryParse(row["年龄"].ToString().Trim(), out year);
muser.Age = year;
muser.Mono = row["是否健康"].ToString().Trim();
lstMatchusers.Add(muser);
}
int count = 0;
TeamRegBll tbll = new TeamRegBll();
int res = tbll.ImpTeams(lstMatchusers, ref count);
return(RedirectToAction("importsuccess", new { m = HttpUtility.UrlEncode(match.Match_name), s = HttpUtility.UrlEncode(string.Format("已成功导入{0}个队伍，{1}个队员信息", count, res)) }));
}
catch (Exception ex)
{
ILog log = LogManager.GetLogger(this.GetType());
log.Error(ex);
return(new EmptyResult());
}
}
/// <summary>
/// 批量更新已发货
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public ActionResult BatchDis(string path)
{
path = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + path.Replace("..", "");
var fileArr = path.Split(new[] { "/" }, StringSplitOptions.RemoveEmptyEntries);
var fileName = fileArr[fileArr.Length - 1];
IEnumerable <CsOrderView.CsOrderBatchDis> orders;
try
{
orders = NpoiHelper.ReadExcel(path.Replace("/" + fileName, ""), fileName)
.ToList <CsOrderView.CsOrderBatchDis>()
.Where(x => !x.订单编号.IsNullOrEmpty() && !x.运单号.IsNullOrEmpty());
}
catch (Exception ex)
{
LogHelper.Log(ex.ToJson(), "execl文件读取失败");
return(Json(new ResModel
{
ResStatus = ResStatue.No,
Data = "execl 文件读取失败,请确认文件数据正确, 请检查日志描述"
}));
}
if (!orders.Any())
{
return(Json(new ResModel
{
ResStatus = ResStatue.No,
Data = "当前Excel中没有数据,请确认文件是否包含规定完整的数据"
}));
}
if (orders.Any(x => x.订单编号.IsNullOrEmpty() || x.运单号.IsNullOrEmpty()))
{
return(Json(new ResModel
{
ResStatus = ResStatue.No,
Data = "当前Excel中存在空数据,请确认数据的完整性, 数据一旦上传将无法撤回"
}));
}
var distinct = orders.Distinct(x => x.订单编号);
if (distinct.Count() != orders.Count())
{
return(Json(new
{
code = ResStatue.Warn.GetHashCode(),
data = "存在重复的订单编号,请依据给出的数据检查, 数据一旦上传将无法撤回",
orderIds = orders.Except(distinct).Select(x => x.订单编号)
}));
}
var data = _csOrderBll.GetModelList($" AND OrderNumber IN ('{string.Join("','", orders.Select(x => x.订单编号))}')");
if (data.Count != orders.Count())
{
return(Json(new
{
code = ResStatue.Warn.GetHashCode(),
data = "存在有误的订单编号,请依据给出的数据检查, 数据一旦上传将无法撤回",
orderIds = orders.Select(x => x.订单编号.Trim()).Except(data.Select(x => x.OrderNumber))
}));
}
var count = 0;
foreach (var order in orders)
{
var sh = new SqlHelper <CsOrder>();
sh.AddUpdate("OrderState", OrderState.已发货.GetHashCode());
sh.AddUpdate("OrderDelivery", order.运单号);
sh.AddWhere($" AND OrderNumber = '{order.订单编号}'");
count += sh.Update();
}
return(Json(new ResModel
{
ResStatus = ResStatue.Yes,
Data = $"更新成功, 共计更新{count}条数据."
}));
}
/// <summary>
/// 数据源导出总运单和承运单清单
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void gv_HAWB_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "Eidt")
{
Guid id = (Guid)Session["UserID"];
ModulePrivilege Authority = _sysUserManagementService.GetPrivilegeByUserid(id);
bool aPrivilege = (bool)Authority[Privilege.修改.ToString()];
bool aPrivilege2 = (bool)Authority[Privilege.导出.ToString()];
bool aPrivilege1 = (bool)Authority[Privilege.解锁.ToString()];
Response.Redirect("MawbDetails.aspx?Privilege=" + aPrivilege + "&BarCode=" + e.CommandArgument + "&aPrivilege1=" + aPrivilege1 + "&aPrivilege2=" + aPrivilege2 + "");
}
if (e.CommandName == "Derive")
{
string barcode = e.CommandArgument.ToString();
MAWB mawb = _mawbService.FindMAWBByBarcode(barcode);
IList<HAWB> hawbs = _hawbService.FindHAWBsByMID(mawb.MID.ToString());
if (hawbs.Count != 0)
{
var NpoiHelper = new NpoiHelper(mawb, hawbs, ExportType.运单号);
NpoiHelper.ExportMAWB();
var str = (MemoryStream)NpoiHelper.RenderToExcel();
if (str == null) return;
var data = str.ToArray();
var resp = Page.Response;
resp.Buffer = true;
resp.Clear();
resp.Charset = "utf-8";
resp.ContentEncoding = System.Text.Encoding.UTF8;
resp.ContentType = "application/ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(String.Format("{0}.xls", barcode + "总运单清单"), System.Text.Encoding.UTF8));
HttpContext.Current.Response.BinaryWrite(data);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
else
{
ScriptManager.RegisterStartupScript(UpdatePanel1, UpdatePanel1.GetType(), "", "alert('该总运单下没有运单，不能导出！')", true);
}
}
else if (e.CommandName == "DeriveAccept")
{
string barcode = e.CommandArgument.ToString();
MAWB mawb = _mawbService.FindMAWBByBarcode(barcode);
IList<HAWB> hawbs = _hawbService.FindHAWBsByMID(mawb.MID.ToString());
if (hawbs.Count != 0)
{
var NpoiHelper = new NpoiHelper(mawb, hawbs, ExportType.承运单号);
NpoiHelper.ExportMAWB();
var str = (MemoryStream)NpoiHelper.RenderToExcel();
if (str == null) return;
var data = str.ToArray();
var resp = Page.Response;
resp.Buffer = true;
resp.Clear();
resp.Charset = "utf-8";
resp.ContentEncoding = System.Text.Encoding.UTF8;
resp.ContentType = "application/ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(String.Format("{0}.xls", barcode + "承运清单"), System.Text.Encoding.UTF8));
HttpContext.Current.Response.BinaryWrite(data);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
else
{
ScriptManager.RegisterStartupScript(UpdatePanel1, UpdatePanel1.GetType(), "", "alert('该总运单下没有运单，不能导出！')", true);
}
}
}
/// <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;
}
}
public void TestEntireReport()
{
string Setnb = "";
// Set up the database with test publications (and don't forget to add the
// publication types!)
DB = new Database("Publication Harvester Unit Test");
Harvester harvester = new Harvester(DB);
harvester.CreateTables();
PublicationTypes PubTypes = new PublicationTypes(
AppDomain.CurrentDomain.BaseDirectory + "\\Unit Tests\\TestPublicationTypes",
"PublicationTypes.csv"
);
PubTypes.WriteToDB(DB);
reports = new Reports(DB, AppDomain.CurrentDomain.BaseDirectory + "\\Unit Tests\\TestReports\\pubmed_jifs.xls");
Assert.IsTrue(reports.Weights.Count == 10);
TestHarvester.GetPublicationsFromInput1XLS_Using_MockNCBI(false, new string[] { "eng" }, 22);
people = new People(DB);
// Write the report
StreamWriter writer = new StreamWriter(AppDomain.CurrentDomain.BaseDirectory + "\\TestEntireReport.csv");
Reports.ReportStatus StatusCallback = delegate(int number, int total, Person person, bool ProgressBarOnly) {
//
};
Reports.ReportMessage MessageCallback = delegate(string Message)
{
//
};
reports.PeopleReport(null, writer, StatusCallback, MessageCallback);
writer.Close();
// Read the report into an array
var lines = File.ReadAllLines($"{AppDomain.CurrentDomain.BaseDirectory}\\TestEntireReport.csv")
.Select(line => line.Split(new char[] { ',' }));
var header = lines.First();
var data = lines.Skip(1).ToList();
Assert.AreEqual(85, data.Count);
string ReportData(string setnb, string year, string column)
{
var index = Array.IndexOf(header, column);
var row = data.Where(line => line[0] == setnb && line[1] == year);
Assert.AreEqual(1, row.Count(), $"Unable to find setnb={setnb} year={year} in TestEntireReport.csv");
return(row.First()[index]);
}
var q = ReportData("A5401532", "1997", "wghtd_pubcount_pos1");
// Read the report file that was generated by hand (TestEntireReport_Data.xls)
// and check each value against the report that was generated by Reports()
string[] Columns =
{
"setnb", "year", "pubcount", "wghtd_pubcount", "pubcount_pos1",
"wghtd_pubcount_pos1", "pubcount_posN", "wghtd_pubcount_posN",
"pubcount_posM", "wghtd_pubcount_posM", "pubcount_posNTL",
"wghtd_pubcount_posNTL", "pubcount_pos2", "wghtd_pubcount_pos2",
"123pubcount", "wghtd_123pubcount", "123pubcount_pos1",
"wghtd_123pubcount_pos1", "123pubcount_posN", "wghtd_123pubcount_posN",
"123pubcount_posM", "wghtd_123pubcount_posM", "123pubcount_posNTL",
"wghtd_123pubcount_posNTL", "123pubcount_pos2", "wghtd_123pubcount_pos2",
"1pubcount", "wghtd_1pubcount", "1pubcount_pos1", "wghtd_1pubcount_pos1",
"1pubcount_posN", "wghtd_1pubcount_posN", "1pubcount_posM",
"wghtd_1pubcount_posM", "1pubcount_posNTL", "wghtd_1pubcount_posNTL",
"1pubcount_pos2", "wghtd_1pubcount_pos2", "2pubcount", "wghtd_2pubcount",
"2pubcount_pos1", "wghtd_2pubcount_pos1", "2pubcount_posN",
"wghtd_2pubcount_posN", "2pubcount_posM", "wghtd_2pubcount_posM",
"2pubcount_posNTL", "wghtd_2pubcount_posNTL", "2pubcount_pos2",
"wghtd_2pubcount_pos2", "3pubcount", "wghtd_3pubcount", "3pubcount_pos1",
"wghtd_3pubcount_pos1", "3pubcount_posN", //"wghtd_3pubcount_posN",
"3pubcount_posM", "wghtd_3pubcount_posM", "3pubcount_posNTL",
"wghtd_3pubcount_posNTL", "3pubcount_pos2", "wghtd_3pubcount_pos2",
"4pubcount", "wghtd_4pubcount", "4pubcount_pos1", "wghtd_4pubcount_pos1",
"4pubcount_posN", "wghtd_4pubcount_posN", "4pubcount_posM",
"wghtd_4pubcount_posM", "4pubcount_posNTL", "wghtd_4pubcount_posNTL",
"4pubcount_pos2", "wghtd_4pubcount_pos2"
};
DataTable HandGeneratedData = NpoiHelper.ReadExcelFileToDataTable(AppDomain.CurrentDomain.BaseDirectory +
"\\Unit Tests\\TestReports", "TestEntireReport_Data.xls");
Assert.AreEqual(HandGeneratedData.Rows.Count, 85);
var valuesChecked = 0;
for (int RowNum = 0; RowNum < HandGeneratedData.Rows.Count; RowNum++)
{
// Find the rows in the hand-generated data and the report
DataRow HandGeneratedRow = HandGeneratedData.Rows[RowNum];
Setnb = HandGeneratedRow[0].ToString();
int Year = Convert.ToInt32(HandGeneratedRow[1]);
for (int i = 2; i < Columns.Length; i++)
{
valuesChecked++;
String columnName = Columns[i];
var actualValue = ReportData(Setnb, Year.ToString(), columnName);
string expectedValue = HandGeneratedRow[columnName].ToString();
Assert.AreEqual(expectedValue, actualValue, Setnb + "/" + Year.ToString() + "/" + columnName + " -- hand generated has " + expectedValue + ", report has" + actualValue);
}
}
Assert.AreEqual((HandGeneratedData.Rows.Count) * (Columns.Length - 2), valuesChecked);
// Use BackupReportAndGetSetnbs to back up the report -- check that it
// returns the correct list of Setnbs and removes the last one from
// the file. The last Setnb should still be in Setnb.
ArrayList Setnbs = Reports.BackupReportAndGetSetnbs(AppDomain.CurrentDomain.BaseDirectory + "\\TestEntireReport.csv");
// Read the backup file that was created, make sure that the last setnb in the
// file isn't contained and the others are
StreamReader reader = new StreamReader(AppDomain.CurrentDomain.BaseDirectory + "\\TestEntireReport.csv.bak");
string Line = reader.ReadLine(); //skip the header row
while ((Line = reader.ReadLine()) != null) // Find the last setnb in the original file
{
Setnb = Line.Substring(0, 8);
}
string RemovedSetnb = Setnb;
Assert.IsFalse(Setnbs.Contains(RemovedSetnb));
reader.Close();
// Verify that the new file contains only the other setnbs
Assert.IsTrue(Setnbs.Count == 3);
Assert.IsFalse(Setnbs.Contains(RemovedSetnb));
reader = new StreamReader(AppDomain.CurrentDomain.BaseDirectory + "\\TestEntireReport.csv");
Line = reader.ReadLine(); //skip the header row
while ((Line = reader.ReadLine()) != null)
{
Setnb = Line.Substring(0, 8);
Assert.IsTrue(Setnbs.Contains(Setnb));
Assert.IsFalse(Setnb == RemovedSetnb);
}
reader.Close();
// Delete the temporary files
File.Delete(AppDomain.CurrentDomain.BaseDirectory + "\\TestEntireReport.csv");
File.Delete(AppDomain.CurrentDomain.BaseDirectory + "\\TestEntireReport.csv.bak");
}
private void LoadFile(string file)
{
var dt = NpoiHelper.ImportExcelFile(file);
dgv.DataSource = dt;
}
/// <summary>
/// 上传主函数
/// </summary>
public void Upload()
{
DataTable dt = new DataTable();
dt.Columns.Add("result");
dt.Columns.Add("message");
if (context.Request.Files.Count == 0)
{
dt.Rows.Add(new object[] { "False", "文件过大或提交的信息有误!" });
}
else
{
string strFileName = Path.GetFileName(context.Request.Files[0].FileName);
string strFileNameSelfDef = "";
if (context.Request["fileName"] != null && context.Request["fileName"].ToString() != "")
{
strFileNameSelfDef = context.Request["fileName"].ToString();
}
string strExtension = Path.GetExtension(context.Request.Files[0].FileName).ToLower();
if (strFileNameSelfDef == "")
{
strFileName = strFileName.Substring(0, strFileName.Length - strExtension.Length) + "-" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + strExtension;
}
else
{
strFileName = strFileNameSelfDef + strExtension;
}
string strFilePath = context.Request["filepath"];
if (string.IsNullOrEmpty(strFilePath))
{
dt.Rows.Add(new object[] { "False", "filepath不能为空" });
}
else
{
int fileSize = 1024 * 1024 * 20; //20M
HttpPostedFile postedFile = context.Request.Files[0];
if (postedFile.ContentLength > fileSize)
{
dt.Rows.Add(new object[] { "False", "最大只能上传20M文件" });
}
else
{
strFilePath = "/" + strFilePath + "/" + strFileName;
string strLocation = context.Server.MapPath("upload") + strFilePath;
context.Request.Files[0].SaveAs(strLocation);
if (GetFileType(strFileName) == FileType.Excel)
{
//读取EXCEL信息
NpoiHelper excel = new NpoiHelper();
m_dsExcelData = excel.ReadExcel(strLocation);
}
else if (GetFileType(strFileName) == FileType.Pic)
{
if (context.Request["filepath"] == "HZY_mat")
{
CreateThumbnail(strLocation, 200, 200);
}
dt.Rows.Add(new object[] { "True", "/upload" + strFilePath + "" });
dt.Rows.Add(new object[] { "文件大小", postedFile.ContentLength });
}
else
{
dt.Rows.Add(new object[] { "False", "文件类型不认识！" });
}
}
}
}
if (context.Request["WSID"] == null)
{
string strJSON = HZY.COM.Common.JsonHelper.CreateJsonParameters(dt);
context.Response.Write(strJSON);
}
}
public ActionResult ExportCsOrder(CsOrderView.CsOrderWhere para)
{
var data = GetList(para, false);
if (!data.Data.Any())
{
return(Json(new ResModel
{
Data = "没有任何可以导出数据",
ResStatus = ResStatue.No
}));
}
var list = new List <CsOrderView.CsOrderExcel>();
var detail = _csOrderDetailBll.GetModelList($" AND OrderId IN ({string.Join(",", data.Data.Select(x => x.OrderId))})");
// 配件/螃蟹/套餐
var parts = _csPartsBll.GetModelList($" AND PartId IN ({string.Join(",", detail.Where(x => x.ChoseType == ChoseType.配件.GetHashCode()).Select(x => x.ProductId)).ShowNullOrEmpty("0")}) AND PartType = {PartType.可选配件.GetHashCode()}");
var products = _csProductsBll.GetModelList($" AND ProductId IN ({string.Join(",", detail.Where(x => x.ChoseType == ChoseType.螃蟹.GetHashCode()).Select(x => x.ProductId)).ShowNullOrEmpty("0")})");
IEnumerable <CsPackage> packages = null;
if (detail.Any(x => x.ChoseType == ChoseType.套餐.GetHashCode()))
{
packages = _csPackageBll.GetModelList($" AND PackageId IN ({string.Join(",", detail.Where(x => x.ChoseType == ChoseType.套餐.GetHashCode()).Select(x => x.ProductId))})");
}
foreach (var order in data.Data)
{
var total = string.Empty;
foreach (var d in detail.Where(x => x.OrderId == order.OrderId).OrderBy(x => x.ChoseType))
{
if (d.ChoseType == ChoseType.套餐.GetHashCode())
{
total += packages?.FirstOrDefault(x => x.PackageId == d.ProductId)?.PackageNumber + "" + d.ProductNumber;
continue;
}
if (d.ChoseType == ChoseType.螃蟹.GetHashCode())
{
total += products.FirstOrDefault(x => x.ProductId == d.ProductId)?.ProductNumber + "" + d.ProductNumber;
continue;
}
if (total.IndexOf("P", StringComparison.Ordinal) < 0 &&
total.IndexOf("-", StringComparison.Ordinal) < 0)
{
total += "-";
}
total += parts.FirstOrDefault(x => x.PartId == d.ProductId)?.PartNumber;
}
var sendInfo = order.SendAddress.Split('$');
var putInfo = order.OrderAddress.Split('$');
list.Add(new CsOrderView.CsOrderExcel
{
用户订单号 = order.OrderNumber,
寄件公司 = "-",
寄联系人 = sendInfo.Length > 0 ? sendInfo[0] : "-",
寄联系电话 = sendInfo.Length > 1 ? sendInfo[1] : "-",
寄件地址 = sendInfo.Length > 2 ? sendInfo[2] : "-",
收件公司 = putInfo.Length > 0 ? putInfo[0] : "-",
联系人 = putInfo.Length > 1 ? putInfo[1] : "-",
联系电话 = putInfo.Length > 2 ? putInfo[2] : "-",
手机号码 = putInfo.Length > 3 ? putInfo[3] : "-",
收件详细地址 = putInfo.Length > 4 ? putInfo[4] : "-",
付款方式 = "寄付月结",
第三方付月结卡号 = "",
寄托物品 = "其他",
寄托物内容 = total.TrimEnd('-').IsNullOrEmpty() ? "" : $"{total.TrimEnd('-')}-T{detail.Where(x => x.OrderId == order.OrderId && (x.ChoseType == ChoseType.螃蟹.GetHashCode() || x.ChoseType == ChoseType.套餐.GetHashCode())).Sum(x => x.ProductNumber)}",
寄托物编号 = "",
寄托物数量 = order.CargoNumber.ToString(),
件数 = order.OrderCopies.ToString(),
实际重量单位KG = "-",
计费重量单位KG = "-",
业务类型 = "大闸蟹专递",
扩展字段1 = detail.Any(x => x.OrderId == order.OrderId && x.ChoseType == ChoseType.套餐.GetHashCode()) ? "企业团购" : "电商代发",
扩展字段2 = order.OrderRemarks
});
}
var path = $"excel/{DateTime.Now:yyyyMMddHHmmssffff}.xls";
try
{
NpoiHelper.ExportToExcel(list.ToDataTable(), "D:/" + path);
}
catch (Exception e)
{
return(Json(new ResModel
{
Data = e.Message,
ResStatus = ResStatue.No
}));
}
return(Json(new ResModel
{
Data = "../" + path,
ResStatus = ResStatue.Yes
}));
}
/// <summary>
/// 导出运单发票
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btn_DeriveSince_Click(object sender, EventArgs e)
{
HAWB hawb = _hawbService.FindHAWBByBarCode(Txt_BarCode.Text.Trim());
var NpoiHelper = new NpoiHelper(hawb, Txt_BarCode.Text);
NpoiHelper.ExportInvoice();
var str = (MemoryStream)NpoiHelper.RenderToExcel();
if (str == null) return;
var data = str.ToArray();
var resp = Page.Response;
resp.Buffer = true;
resp.Clear();
resp.Charset = "utf-8";
resp.ContentEncoding = System.Text.Encoding.UTF8;
resp.ContentType = "application/ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(String.Format("{0}.xls", Txt_BarCode.Text+"运单发票"), System.Text.Encoding.UTF8));
HttpContext.Current.Response.BinaryWrite(data);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
public JsonResult ImpTeams(string matchid)
{
try
{
DataTable data = NpoiHelper.XlSToDataTable(@"C:\Work\Vki\MyDoc\MyFile\dx_dev\定向赛团体报名统计表.xlsx", "test", 0);
if (data == null || data.Rows.Count < 1)
{
return(RepReurnError("上传的文件中没有数据"));
}
StringBuilder sbtError = new StringBuilder();
List <tblusers> lstUsers = new List <tblusers>();
List <tblteams> lstTeams = new List <tblteams>();
List <tblmatchusers> lstMatchusers = new List <tblmatchusers>();
int sn = 0;
string lineid = "";
string teamno = "";
string teamname = "";
string company = "";
foreach (DataRow row in data.Rows)
{
//记录序号，以标记团队
if (!string.IsNullOrEmpty(row["序号"].ToString().Trim()))
{
sn = int.Parse(row["序号"].ToString().Trim());
}
if (!string.IsNullOrEmpty(row["线路号"].ToString().Trim()))
{
lineid = row["线路号"].ToString().Trim();
}
if (!string.IsNullOrEmpty(row["队列号"].ToString().Trim()))
{
teamno = row["队列号"].ToString().Trim();
}
if (!string.IsNullOrEmpty(row["队名(6个字符以内)"].ToString().Trim()))
{
teamname = row["队名(6个字符以内)"].ToString().Trim();
}
if (!string.IsNullOrEmpty(row["公司名称"].ToString().Trim()))
{
company = row["公司名称"].ToString().Trim();
}
if (string.IsNullOrEmpty(row["队员编号"].ToString().Trim()))
{
continue;
}
if (string.IsNullOrEmpty(row["性别"].ToString().Trim()))
{
sbtError.Append("[性别]不能为空;");
}
if (string.IsNullOrEmpty(row["身份证/护照"].ToString().Trim()))
{
sbtError.Append("[身份证/护照]不能为空;");
}
if (!System.Text.RegularExpressions.Regex.IsMatch(row["手机号"].ToString().Trim(), @"^[1]+[0-9]+\d{9}"))
{
sbtError.AppendFormat("[手机号:{0}]格式错误;", row["手机号"]);
}
tblusers usr = new tblusers();
usr.cardtype = "1";
usr.Mobile = row["手机号"].ToString().Trim();
usr.sexy = row["性别"].ToString().Trim() == "男" ? "1" : "0";
usr.Status = 1;
usr.userid = Guid.NewGuid().ToString();
lstUsers.Add(usr);
tblteams tm = new tblteams();
tm.teamid = Guid.NewGuid().ToString();
tm.Company = "";
tm.Createtime = DateTime.Now;
tm.Eventid = 1;
tm.Lineid = lineid;
tm.match_id = matchid;
tm.Status = 1;
tm.Teamname = teamname;
tm.Teamno = teamno;
//tm.Userid
lstTeams.Add(tm);
}
}
catch (Exception ex)
{
ILog log = LogManager.GetLogger(this.GetType());
log.Error(ex);
return(RepReurnError("导入过程中出现错误"));
}
return(RepReurnOK());
}
/// <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>
/// 导出承运公司清单
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btn_DeriveAccept_Click(object sender, EventArgs e)
{
MAWB mawb = _mawbService.FindMAWBByBarcode(lbl_MAWBBarCode.Text);
IList<HAWB> hawbs = _hawbService.FindHAWBsByMID(mawb.MID.ToString());
if (hawbs.Count != 0)
{
var NpoiHelper = new NpoiHelper(mawb, hawbs, ExportType.承运单号);
NpoiHelper.ExportMAWB();
var str = (MemoryStream)NpoiHelper.RenderToExcel();
if (str == null) return;
var data = str.ToArray();
var resp = Page.Response;
resp.Buffer = true;
resp.Clear();
resp.Charset = "utf-8";
resp.ContentEncoding = System.Text.Encoding.UTF8;
resp.ContentType = "application/ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(String.Format("{0}.xls", lbl_MAWBBarCode.Text + "承运清单"), System.Text.Encoding.UTF8));
HttpContext.Current.Response.BinaryWrite(data);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
else
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('该总运单下没有运单，不能导出!')</script>");
}
}
/// <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>
/// 数据源操作
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Gv_HAWB_RowCommand(object sender, GridViewCommandEventArgs e)
{
Guid id = (Guid)Session["UserID"];
ModulePrivilege Authority = _SysUserManagementService.GetPrivilegeByUserid(id);
if (e.CommandName == "Eidt")
{
bool aPrivilege = (bool)Authority[Privilege.修改.ToString()];
bool aPrivilege1 = (bool)Authority[Privilege.导出.ToString()];
int index = Convert.ToInt32(e.CommandArgument);
string barCode = Gv_HAWB.DataKeys[index].Value.ToString();
Response.Redirect("HAWBDetails.aspx?BarCode=" + barCode + "&Privilege=" + aPrivilege + "&Privilege1=" + aPrivilege1 + "");
}
else if (e.CommandName == "Updata")
{
string barCode = e.CommandArgument.ToString();
int Update = 1;
Response.Redirect("HAWBAdd.aspx?BarCode=" + barCode + "&update=" + Update + "");
}
else if (e.CommandName == "Del")
{
string barCode = e.CommandArgument.ToString();
//_hawbService.RemoveHAWB(barCode);
}
else if (e.CommandName == "Derive")
{
string barCode = e.CommandArgument.ToString();
HAWB hawb = _hawbService.FindHAWBByBarCode(barCode);
var NpoiHelper = new NpoiHelper(hawb, barCode);
NpoiHelper.ExportInvoice();
var str = (MemoryStream)NpoiHelper.RenderToExcel();
if (str == null) return;
var data = str.ToArray();
var resp = Page.Response;
resp.Buffer = true;
resp.Clear();
resp.Charset = "utf-8";
resp.ContentEncoding = System.Text.Encoding.UTF8;
resp.ContentType = "application/ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(String.Format("{0}.xls", barCode+"运单发票"), System.Text.Encoding.UTF8));
HttpContext.Current.Response.BinaryWrite(data);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
else if (e.CommandName == "DeriveAccept")
{
int index = ((GridViewRow)((LinkButton)(e.CommandSource)).Parent.Parent).RowIndex;
string CarrierHAWBBarCode = e.CommandArgument.ToString();
string barCode = Gv_HAWB.DataKeys[index].Value.ToString();
HAWB hawb = _hawbService.FindHAWBByBarCode(barCode);
if (!string.IsNullOrEmpty(CarrierHAWBBarCode))
{
var NpoiHelper = new NpoiHelper(hawb, CarrierHAWBBarCode);
NpoiHelper.ExportInvoice();
var str = (MemoryStream)NpoiHelper.RenderToExcel();
if (str == null) return;
var data = str.ToArray();
var resp = Page.Response;
resp.Buffer = true;
resp.Clear();
resp.Charset = "utf-8";
resp.ContentEncoding = System.Text.Encoding.UTF8;
resp.ContentType = "application/ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(String.Format("{0}.xls", barCode+"承运发票"), System.Text.Encoding.UTF8));
HttpContext.Current.Response.BinaryWrite(data);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
else
{
ScriptManager.RegisterStartupScript(UpdatePanel1, UpdatePanel1.GetType(), "", "alert('没有承运公司编号，不能导出！')", true);
}
}
}
private void Btn_Change_Click(object sender, EventArgs e)
{
//获取列名称
string columnname = txt_columnname.Text.Trim();
if (columnname == "")
{
MessageBox.Show("请填写列名", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (!dt.Columns.Contains(columnname))
{
MessageBox.Show("不存在此列名", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//条件判断，智能取其中一个
#region 条件一
if (Ckb_isuseone.Checked)
{
//查找相同 删除重复数据，并把重复数据导入到新建的excel中
if (ckb_issame.Checked)
{
string path = txt_filepath.Text.Trim();
string exportpath = txt_exportpath.Text.Trim();
DataTable table = dt.Clone();
List <string> names = new List <string>();
Regex reg = new Regex(@"\s");
for (int i = 0; i < dt.Rows.Count; i++)
{
string name = reg.Replace(dt.Rows[i][columnname].ToString(), "", 999);
//去除中间空格及左右空格，防止数据错误
dt.Rows[i][columnname] = name;
//判断是否已经处理过这名字
if (names.Contains(name))
{
continue;
}
names.Add(name);
List <DataRow> rows = dt.Select().Where(t => reg.Replace(t[columnname].ToString(), "", 999) == name).ToList();
if (rows.Count > 1)
{
foreach (DataRow item in rows)
{
//删除空格
item[columnname] = reg.Replace(item[columnname].ToString(), "", 999);
//解决该行已经属于另一个表
table.Rows.Add(item.ItemArray);
//删除重复数据
dt.Rows.Remove(item);
}
}
}
NpoiHelper.DataTableToExcel(table, "mysheet", true, exportpath);
MessageBox.Show("导出成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
#endregion
#region 条件二
if (Ckb_isusetwo.Checked)
{
string where_one = txt_whereone.Text.Trim();
string where_two = txt_wheretwo.Text.Trim();
string where_three = txt_wherethree.Text.Trim();
for (int i = 0; i < dt.Rows.Count; i++)
{
string value = dt.Rows[i][columnname].ToString();
//去除中间空格及左右空格，防止数据错误
value = value.Trim().Replace(" ", "");
if (value == where_one)
{
value = txt_resultone.Text.Trim();
}
else if (value == where_two)
{
value = txt_resulttwo.Text.Trim();
}
else if (value == where_three)
{
value = txt_resultthree.Text.Trim();
}
dt.Rows[i][columnname] = value;
}
}
#endregion
#region 条件三
if (Ckb_isusethree.Checked)
{
//是否是身份证
if (ckb_isidcard.Checked)
{
ValidateHelper validateHelper = new ValidateHelper();
for (int i = 0; i < dt.Rows.Count; i++)
{
string value = dt.Rows[i][columnname].ToString();
//去除中间空格及左右空格，防止数据错误
value = value.Trim().Replace(" ", "");
if (value != "")
{
bool istrue = validateHelper.IsIdCard(value);
if (!istrue)
{
dt.Rows[i][columnname] = value + "数据错误";
}
}
}
}
}
#endregion
MessageBox.Show("更改成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
public ActionResult ImportCsOrder(string path)
{
path = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + path.Replace("..", "");
var fileArr = path.Split(new[] { "/" }, StringSplitOptions.RemoveEmptyEntries);
var fileName = fileArr[fileArr.Length - 1];
IList <CsOrderView.CsOrderImport> orders;
try
{
orders = NpoiHelper.ReadExcel(path.Replace("/" + fileName, ""), fileName).ToList <CsOrderView.CsOrderImport>();
}
catch (Exception ex)
{
LogHelper.Log(ex.ToJson(), "execl文件读取失败");
return(Json(new ResModel
{
ResStatus = ResStatue.No,
Data = "execl 文件读取失败,请确认文件数据正确, 请检查日志描述"
}));
}
if (!orders.Any())
{
return(Json(new ResModel
{
ResStatus = ResStatue.No,
Data = "当前Excel中没有数据,请确认文件是否包含规定完整的数据"
}));
}
var users = _csUsersBll.GetModelList($" AND (UserName IN ('{string.Join("','", orders.Select(x => x.收货人))}') OR UserPhone IN ('{string.Join("','", orders.Select(x => x.收货人电话))}'))");
var products = _csProductsBll.GetModelList("");
var parts = _csPartsBll.GetModelList("");
var data = new List <CsOrderView.CsOrderAndDetail>();
var item = new CsOrderView.CsOrderAndDetail();
var lastType = ExcelRow.Other; // 记录上一行状态
var endEmpty = new List <int>(); // 记录尾部空行
var overEndEmpty = false; // 尾部空行是否结束
for (var i = orders.Count - 1; i >= 0; i--)
{
var type = ExcelRowType(orders[i]);
if (type == ExcelRow.Empty &&
!overEndEmpty)
{
endEmpty.Add(i);
}
else
{
overEndEmpty = true;
}
if (type == ExcelRow.Empty &&
lastType == ExcelRow.Empty &&
overEndEmpty)
{
return(Json(new ResModel
{
ResStatus = ResStatue.No,
Data = "当前Excel中,两个订单信息中间,存在连续多空行.请仔细确认数据, 数据一旦上传将无法撤回"
}));
}
lastType = type;
}
// 将尾部空行删除只保留一个
endEmpty.ForEach(x => orders.RemoveAt(x));
orders.Add(new CsOrderView.CsOrderImport());
foreach (var order in orders)
{
var type = ExcelRowType(order);
if (type == ExcelRow.Other)
{
return(Json(new ResModel
{
ResStatus = ResStatue.No,
Data = "当前Excel中不满足格式的单元格,请仔细确认数据, 数据一旦上传将无法撤回"
}));
}
if (type == ExcelRow.Empty)
{
data.Add(item);
item = new CsOrderView.CsOrderAndDetail();
continue;
}
var product = products.FirstOrDefault(x => x.ProductNumber == order.商品编码);
var part = parts.FirstOrDefault(x => x.PartNumber == order.商品编码);
var pId = 0; // 商品Id
var cType = ChoseType.螃蟹; // 蟹或配件
if (product == null && part == null)
{
return(Json(new ResModel
{
ResStatus = ResStatue.No,
Data = $"商品编码:{order.商品编码},不存在与数据库中,请仔细确认数据"
}));
}
if (product != null)
{
pId = product.ProductId;
cType = ChoseType.螃蟹;
}
if (part != null)
{
pId = part.PartId;
cType = ChoseType.配件;
}
if (type == ExcelRow.Order)
{
var user = users.FirstOrDefault(x => x.UserPhone == order.收货人电话 && x.UserName == order.收货人);
int userId;
if (user == null)
{
user = new CsUsers
{
UserSex = "先生",
UserName = order.收货人,
UserPhone = order.收货人电话,
OpenId = "",
Remarks = "",
TotalWight = 0,
UserBalance = 0,
UserState = 1
};
userId = _csUsersBll.Add(user);
}
else
{
userId = user.UserId;
}
Thread.Sleep(5);
item.CsOrder = new CsOrder
{
RowStatus = RowStatus.效.GetHashCode(),
DeleteDescribe = "",
OrderState = OrderState.已发货.GetHashCode(),
UserId = userId,
ActualMoney = order.实收金额.ToDecimal(),
OrderDelivery = order.货运单号,
SendAddress = $"{order.发货人}${order.发货人电话}",
OrderAddress = $"${order.收货人}" + $"({user.UserSex})$${order.收货人电话}${order.收货地址})",
DeleteDate = "1900-1-1".ToDate(),
DiscountMoney = order.总金额.ToDecimal() - order.实收金额.ToDecimal(),
OrderDate = DateTime.Now,
OrderNumber = DateTime.Now.ToString(OrderNumberFormat),
TotalMoney = order.总金额.ToDecimal()
};
item.CsOrderDetails.Add(new CsOrderDetail
{
ProductId = pId,
ProductNumber = order.数量.ToInt(),
ChoseType = cType.GetHashCode(),
TotalPrice = order.单价.ToDecimal() * order.数量.ToInt(),
UnitPrice = order.单价.ToDecimal()
});
}
if (type == ExcelRow.Detail)
{
item.CsOrderDetails.Add(new CsOrderDetail
{
ProductId = pId,
ProductNumber = order.数量.ToInt(),
ChoseType = cType.GetHashCode(),
TotalPrice = order.单价.ToDecimal() * order.数量.ToInt(),
UnitPrice = order.单价.ToDecimal()
});
}
}
var count = 0;
var countDetail = 0;
foreach (var d in data)
{
if (d.CsOrder.RowStatus == RowStatus.无效.GetHashCode())
{
return(Json(new ResModel
{
ResStatus = ResStatue.No,
Data = "存在无效订单,请检查表格总的数据是否满足要求"
}));
}
var orderId = _csOrderBll.Add(d.CsOrder);
foreach (var detail in d.CsOrderDetails)
{
detail.OrderId = orderId;
_csOrderDetailBll.Add(detail);
countDetail++;
}
count++;
}
return(Json(new ResModel
{
ResStatus = ResStatue.Yes,
Data = $"导入{count}条订单记录,以及共计{countDetail}条商品记录"
}));
}
/// <summary>
/// 导出承运单发票
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btn_DeriveAccept_Click(object sender, EventArgs e)
{
HAWB hawb = _hawbService.FindHAWBByBarCode(Txt_BarCode.Text.Trim());
if (!string.IsNullOrEmpty(Txt_CarrierHAWBBarCode.Text))
{
var NpoiHelper = new NpoiHelper(hawb, Txt_CarrierHAWBBarCode.Text);
NpoiHelper.ExportInvoice();
var str = (MemoryStream)NpoiHelper.RenderToExcel();
if (str == null) return;
var data = str.ToArray();
var resp = Page.Response;
resp.Buffer = true;
resp.Clear();
resp.Charset = "utf-8";
resp.ContentEncoding = System.Text.Encoding.UTF8;
resp.ContentType = "application/ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(String.Format("{0}.xls", Txt_BarCode.Text + "承运发票"), System.Text.Encoding.UTF8));
HttpContext.Current.Response.BinaryWrite(data);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
else
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('没有承运公司编号,不能导出！')</script>");
}
}
/// <summary>
/// 导出报关文件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnExport_Click(object sender, EventArgs e)
{
IList<HAWB> hawbs = new List<HAWB>();//实例化
for (int i = 0; i < RGMAWB.MasterTableView.Items.Count; i++)
{
GridDataItem row = RGMAWB.MasterTableView.Items[i];
if (((CheckBox)row.FindControl("CBSelect")).Checked)
{
//获取选中的运单编号
string hawbBarcode =
RGMAWB.MasterTableView.DataKeyValues[row.ItemIndex]["BarCode"].ToString();
//获取运单信息
HAWB hawb = _hawbService.FindHAWBByBarCode(hawbBarcode);
hawbs.Add(hawb);
}
}
MAWB mawb = _mawbService.FindMAWBByBarcode(MAWBNo);
var NpoiHelper = new NpoiHelper(mawb, hawbs);
NpoiHelper.ExportClearance();
var str = (MemoryStream)NpoiHelper.RenderToExcel();
if (str == null) return;
var data = str.ToArray();
var resp = Page.Response;
resp.Buffer = true;
resp.Clear();
resp.Charset = "utf-8";
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.ContentType = "application/ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode("电子出口清单.xls", Encoding.UTF8));
HttpContext.Current.Response.BinaryWrite(data);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
