public Dictionary <string, object> ImportTaxBusiness(string filePath, string importOrgCode, string importOrgName, DateTime dateMonth, string userId) { Dictionary <string, object> result = new Dictionary <string, object>(); List <ExcelDataRow> rows = new List <ExcelDataRow>(); List <ImportTaxBusiness> list = new List <ImportTaxBusiness>(); try { init(filePath); rows = ExcelConverter.Convert <ImportTaxBusiness>(sheet, HeaderRow, 1); foreach (var row in rows) { list.Add(hardCode(row));//将excel数据转换为List对象 } result["message"] = db.createTaxBusiness(list, dateMonth, userId, importOrgCode, importOrgName); if (result["message"].ToString() == "") { result["code"] = 2000; } else { result["code"] = -1; } } catch (Exception e) { result["code"] = -1; result["message"] = e.Message; } return(result); }
public Dictionary <string, object> importdata(string filePath, string orgCode, string orgName, DateTime dateMonth, string userId) { Dictionary <string, object> result = new Dictionary <string, object>(); List <ExcelDataRow> rows = new List <ExcelDataRow>(); List <importService> list = new List <importService>(); init(filePath); rows = ExcelConverter.Convert <importService>(sheet, HeaderRow, 1); foreach (ExcelDataRow row in rows) { list.Add(HardCode(row)); } try { string msg = dB.createImportData(list, orgCode, orgName, dateMonth, userId); if (!string.IsNullOrEmpty(msg)) { result["code"] = -1; result["message"] = msg; } else { result["code"] = 2000; result["message"] = "成功"; } } catch (Exception e) { result["code"] = -1; result["message"] = e.Message; } return(result); }
public void Convert_DtoWithNoTags_ShouldCreateExcelWith3Sheets() { // Arrange var exportDto = new ExportDto( new List <ExportTagDto>(), _usedFilterDto); // Act var xlStream = _dut.Convert(exportDto); // Assert var workbook = AssertWorkbookFromStream(xlStream, _expected3Sheets); AssertFiltersSheet(workbook.Worksheets.Worksheet(_filtersSheet), exportDto.UsedFilter); AssertSheetExists(workbook, _historySheet, false); }
public Dictionary <string, object> ImportOneTimeBonus(string filePath, string importOrgCode, string importOrgName, DateTime dateMonth, string userId) { Dictionary <string, object> r = new Dictionary <string, object>(); //导入结果 List <ExcelDataRow> rows = new List <ExcelDataRow>(); //导入数据转成List集合 List <ImportOneTimeBonus> list = new List <ImportOneTimeBonus>(); //一次性奖金数据集合 // DataTable taxSalary = null;//部门工资表 try { Init(filePath);//初始化数据 //taxSalary = taxSalarydb.getTaxSalaryInfo(importOrgCode, dateMonth); rows = ExcelConverter.Convert <ImportOneTimeBonus>(Sheet, HeaderRow, 1); foreach (var item in rows) { list.Add(HardCode(item));//将excel数据转换为List对象 } //foreach (var bonus in list) //{ // DataRow[] taxRows = taxSalary.Select("S_WorkerCode = '" + bonus.S_WorkerCode + "'"); // if (taxRows != null && taxRows.Length == 1) // { // bonus.S_OrgName = taxRows[0]["S_OrgName"].ToString(); // bonus.S_OrgCode = taxRows[0]["S_OrgCode"].ToString(); // } //} r["message"] = db.createOneTimeBonus(list, dateMonth, userId, importOrgCode, importOrgName); if (!string.IsNullOrEmpty(r["message"].ToString())) { r["code"] = -1; } else { r["code"] = 2000; } } catch (Exception ex) { r["code"] = -1; r["message"] = ex.Message; return(r); } return(r); }
static void Main(string[] args) { string path = @"D:\VisualStudioProjects\UntitledOfficeConverter\TestFiles\sven.docx"; using (var wrd = new WordConverter()) { wrd.Convert(path); } path = @"D:\VisualStudioProjects\UntitledOfficeConverter\TestFiles\a.pptx"; using (var wrd = new PowerPointConverter()) { wrd.Convert(path); } path = @"D:\VisualStudioProjects\UntitledOfficeConverter\TestFiles\hist.xlsx"; using (var wrd = new ExcelConverter()) { wrd.Convert(path); } }
/// <summary> /// 验证导入奖金 /// </summary> /// <param name="filePath">导入文件路径</param> /// <param name="importOrgCode">导入部门编码</param> /// <param name="dateMonth">导入月份</param> /// <returns></returns> public Dictionary <string, object> ValidateOneTimeBonus(string filePath, string importOrgCode, DateTime dateMonth) { Dictionary <string, object> r = new Dictionary <string, object>(); //导入结果 //string validateSalary = "";//检验导入的工资合法性 string validateIdNum = ""; //检验导入的身份证号 string validateBonus = ""; //检验导入的调整合法性 string validateOnce = ""; //检验唯一性 string validateMsg = ""; //验证信息 List <ExcelDataRow> rows = new List <ExcelDataRow>(); //导入数据转成List集合 List <ImportOneTimeBonus> list = new List <ImportOneTimeBonus>(); //一次性奖金数据集合 //DataTable taxSalary = null;//工资表 DataTable onetimeBonus = null; //一次性奖金表 try { Init(filePath);//初始化数据 //taxSalary = taxSalarydb.getTaxSalaryInfo(importOrgCode, dateMonth); //taxSalary = taxSalarydb.getTaxSalaryByYear(dateMonth); onetimeBonus = db.validateOnetimeTaxByWorkDate(dateMonth); string msg = validateTemp(HeaderRow);//检验模板是否匹配 if (msg == "") { rows = ExcelConverter.Convert <ImportOneTimeBonus>(Sheet, HeaderRow, 1); foreach (var item in rows) { list.Add(HardCode(item));//将excel数据转换为List对象 } #region 验证是否允许导入 foreach (var bonus in list) { //DataRow[] taxRows = taxSalary.Select("S_WorkerCode = '" + bonus.S_WorkerCode + "'"); //if (taxRows == null || taxRows.Length == 0) //{ // validateBonus += "【" + bonus.S_WorkerCode + ":" + bonus.S_WorkerName + "】"; //} Regex reg = new Regex(@"^[1-9]\d{5}(18|19|([23]\d))\d{2}((0[1-9])|(10|11|12))(([0-2][1-9])|10|20|30|31)\d{3}[0-9Xx]$"); if (bonus.IdTypeName.Contains("身份证")) { if (!reg.IsMatch(bonus.IdNumber.ToString())) { validateIdNum += "【" + bonus.S_WorkerCode + ":" + bonus.S_WorkerName + "】"; } } //else if (taxRows.Length != 1) //{ // validateSalary += "【" + bonus.S_WorkerCode + ":" + bonus.S_WorkerName + "】"; //} DataRow[] onceRows = onetimeBonus.Select("S_WorkerCode = '" + bonus.S_WorkerCode + "'"); if (onceRows.Length != 0) { validateOnce += "【" + bonus.S_WorkerCode + ":" + bonus.S_WorkerName + "】"; } } //if (!string.IsNullOrEmpty(validateBonus)) //{ // validateBonus = "不存在" + validateBonus + "工资信息!"; // validateMsg += validateBonus; //} //if (!string.IsNullOrEmpty(validateSalary)) //{ // validateSalary = validateSalary + "工资信息重复!"; // validateMsg += validateSalary; //} if (!string.IsNullOrEmpty(validateIdNum)) { validateIdNum = validateBonus + "身份证号不正确!"; validateMsg += validateIdNum; } if (!string.IsNullOrEmpty(validateOnce)) { validateOnce = validateOnce + "已存在一次性奖金记录,无法重复导入!"; validateMsg += validateOnce; } if (!string.IsNullOrEmpty(validateMsg)) { validateMsg = "全年一次性奖金导入失败," + validateMsg; r["code"] = -1; r["message"] = validateMsg; return(r); } else { r["code"] = 2000; r["message"] = "验证通过"; r["item"] = filePath; } #endregion } else { r["code"] = -1; r["message"] = msg; return(r); } } catch (Exception ex) { r["code"] = -1; r["message"] = ex.Message; } r["item"] = filePath; return(r); }
/// <summary> /// 验证导入调整 /// </summary> /// <param name="filePath">导入文件路径</param> /// <param name="importOrgCode">导入部门编码</param> /// <param name="dateMonth">导入月份</param> /// <returns></returns> public Dictionary <string, object> ValidateTaxAdjust(string filePath, string importOrgCode, DateTime dateMonth) { Dictionary <string, object> r = new Dictionary <string, object>(); //导入结果 string validateSalary = ""; //检验导入的工资合法性 string validateAdjust = ""; //检验导入的调整合法性 string validateMsg = ""; //验证信息 string errorMsg = ""; //错误行数信息 List <ExcelDataRow> rows = new List <ExcelDataRow>(); //导入数据转成List集合 List <ImportTaxAdjust> list = new List <ImportTaxAdjust>(); //个税调整数据集合 DataTable taxSalary = null; //部门工资表 try { Init(filePath); //初始化数据 taxSalary = taxSalarydb.getTaxSalaryInfo(importOrgCode, dateMonth); string msg = validateTemp(HeaderRow); //检验模板是否匹配 if (msg == "") { rows = ExcelConverter.Convert <ImportTaxAdjust>(Sheet, HeaderRow, 1); foreach (var item in rows) { list.Add(HardCode(item));//将excel数据转换为List对象 errorMsg = "第" + (list.Count() + 1) + "条数据有误,转换失败!"; } errorMsg = ""; #region 验证是否允许导入 foreach (var adjust in list) { if (!string.IsNullOrEmpty(adjust.S_WorkerCode)) { DataRow[] taxRows = taxSalary.Select("S_WorkerCode = '" + adjust.S_WorkerCode + "'"); if (taxRows == null || taxRows.Length == 0) { validateAdjust += "【" + adjust.S_WorkerCode + ":" + adjust.S_WorkerName + "】"; } else if (taxRows.Length != 1) { validateSalary += "【" + adjust.S_WorkerCode + ":" + adjust.S_WorkerName + "】"; } } else { r["code"] = -1; r["message"] = adjust.S_WorkerName + "工号不能为空!"; return(r); } } if (!string.IsNullOrEmpty(validateAdjust)) { validateAdjust = "不存在" + validateAdjust + "工资信息!"; validateMsg += validateAdjust; } if (!string.IsNullOrEmpty(validateSalary)) { validateSalary = validateSalary + "工资信息重复!"; validateMsg += validateSalary; } if (!string.IsNullOrEmpty(validateMsg)) { validateMsg = "调整导入失败," + dateMonth.ToString("yyyy-MM") + "," + validateMsg; r["code"] = -1; r["message"] = validateMsg; return(r); } else { r["code"] = 2000; r["message"] = "验证通过"; r["item"] = filePath; } #endregion } else { r["code"] = -1; r["message"] = msg; return(r); } } catch (Exception ex) { r["code"] = -1; r["message"] = ex.Message + errorMsg; } r["item"] = filePath; return(r); }
public Dictionary <string, object> validateExcel(string filePath, string importOrgCode, string importOrgName, DateTime dateMonth) { Dictionary <string, object> result = new Dictionary <string, object>(); string workcoderepeat = ""; //验证导入excel内是否有重复人员 string Idrepeat = ""; //验证导入excel内是否有重复身份账号 List <ExcelDataRow> rows = new List <ExcelDataRow>(); List <ImportTaxBusiness> list = new List <ImportTaxBusiness>(); string hardCodeMsg = ""; try { init(filePath); hardCodeMsg = validateHeader(HeaderRow); if (!string.IsNullOrEmpty(hardCodeMsg)) { result["code"] = -1; result["message"] = hardCodeMsg; return(result); } rows = ExcelConverter.Convert <ImportTaxBusiness>(sheet, HeaderRow, 1); foreach (var row in rows) { list.Add(hardCode(row)); hardCodeMsg = "第" + list.Count() + 1 + "条数据错误!"; } var workcode = list.GroupBy(t => t.WorkNumber).Where(t => t.Count() > 1).ToList(); foreach (var item in workcode) { workcoderepeat += "【" + item.Key + "】"; } if (!string.IsNullOrEmpty(workcoderepeat)) { workcoderepeat = "员工编号:" + workcoderepeat + "金税人员信息重复,请确认导入信息"; result["code"] = -1; result["message"] = "导入失败!" + workcoderepeat; return(result); } var idNumber = list.GroupBy(t => t.IDNumber).Where(t => t.Count() > 1).ToList(); foreach (var item in idNumber) { Idrepeat += "【" + item.Key + "】"; } if (!string.IsNullOrEmpty(Idrepeat)) { Idrepeat = "员工身份证号:" + Idrepeat + "金税人员信息重复,请确认导入信息"; result["code"] = -1; result["message"] = "导入失败!" + Idrepeat; return(result); } //验证数据库中本月是否存在重复人员的金税信息 //DataTable dt = db.judgeTaxInfo( dateMonth); //foreach (var item in list) //{ // DataRow[] dr = dt.Select("S_WorkNumber='" + item.WorkNumber + "'"); // if (dr.Count() > 0) // { // result["code"] = -1; // result["message"] = "金税人员重复,请检查工号为" + dr[0]["S_WorkNumber"].ToString() + "姓名为" + dr[0]["S_WorkName"].ToString() + "的员工信息"; // return result; // } // DataRow[] du = dt.Select("IDNumber='" + item.IDNumber + "'"); // if (du.Count() > 0) // { // result["code"] = -1; // result["message"] = "金税人员重复,请检查身份证号为" + du[0]["IDNumber"].ToString() + "姓名为" + du[0]["S_WorkName"].ToString() + "的员工信息"; // return result; // } //} result["code"] = 2000; result["item"] = filePath; } catch (Exception e) { result["code"] = -1; result["message"] = e.Message + hardCodeMsg; } return(result); }
public Dictionary <string, object> validate(string filepath, string orgCode, string orgName, DateTime dateMonth) { Dictionary <string, object> result = new Dictionary <string, object>(); string errorMsg = ""; string coderepeat = ""; string idrepeat = ""; string headerMsg = ""; List <ExcelDataRow> rows = new List <ExcelDataRow>(); List <importService> list = new List <importService>(); try { init(filepath); headerMsg = validateHeader(HeaderRow); if (!string.IsNullOrEmpty(headerMsg)) { result["code"] = -1; result["message"] = headerMsg; return(result); } rows = ExcelConverter.Convert <importService>(sheet, HeaderRow, 1); foreach (ExcelDataRow row in rows) { list.Add(HardCode(row)); errorMsg = "第" + (list.Count() + 1) + "条数据有误!"; } errorMsg = ""; #region 验证必填项是否为空 foreach (importService item in list) { if (item.IDtype == "" || item.IDNumber == "" || item.IncomeItem == "" || item.Income == "") { errorMsg += "工号为【" + item.WorkerCode + "】姓名为【" + item.WorkerName + "】的员工必填项为空,请仔细检查数据表!"; } } if (!string.IsNullOrEmpty(errorMsg)) { result["code"] = -1; result["message"] = errorMsg; return(result); } errorMsg = ""; #endregion //#region 工号验证重复 //var workcode = list.GroupBy(t => t.WorkerCode).Where(t => t.Count() > 1).ToList(); //foreach (var item in workcode) //{ // coderepeat = "【" + item.Key + "】"; //} //if (!string.IsNullOrEmpty(coderepeat)) //{ // result["code"] = -1; // result["message"] = "工号" + coderepeat + "重复!"; // return result; //} //#endregion #region 身份证规则验证 Regex reg = new Regex(@"^[1-9]\d{5}(18|19|([23]\d))\d{2}((0[1-9])|(10|11|12))(([0-2][1-9])|10|20|30|31)\d{3}[0-9Xx]$"); foreach (var item in list) { if (item.IDtype.Contains("居民身份证")) { if (!reg.IsMatch(item.IDNumber)) { idrepeat += "姓名【" + item.WorkerName + "】" + "身份证号为【" + item.IDNumber + "】身份证格式不正确!"; } } } if (!string.IsNullOrEmpty(idrepeat)) { result["code"] = -1; result["message"] = idrepeat; return(result); } #endregion } catch (Exception e) { result["code"] = -1; result["message"] = e.Message + errorMsg; } result["code"] = 2000; result["message"] = "成功"; result["item"] = filepath; return(result); }
public Dictionary <string, object> ImportTaxSalary(string filePath, string userId, string importModel, string taxOffice) { Dictionary <string, object> r = new Dictionary <string, object>(); //导入结果 List <ExcelDataRow> rows = new List <ExcelDataRow>(); //导入数据转成List集合 List <ImportTaxSalary1> list1 = new List <ImportTaxSalary1>(); //模板一数据集合 List <ImportTaxSalary2> list2 = new List <ImportTaxSalary2>(); //模板二数据集合 try { Init(filePath);//初始化数据 if (importModel == "样表一") { rows = ExcelConverter.Convert <ImportTaxSalary1>(Sheet, HeaderRow, 1); foreach (var item in rows) { list1.Add(HardCode1(item));//将excel数据转换为List对象 } string guid = Guid.NewGuid().ToString(); string result = db.createTaxSalary1(list1, userId, guid); if (result == "") { //调用(全年平均核算)计算存储过程 string salary = db.SalaryTaxCalculator(guid, taxOffice); string[] arr = salary.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries); r["YeaTax"] = arr[0].ToString(); r["MonthTax"] = arr[1].ToString(); r["message"] = "成功"; r["code"] = 2000; } else { r["YeaTax"] = ""; r["MonthTax"] = ""; r["message"] = "成功"; r["code"] = 2000; } } else if (importModel == "样表二") { rows = ExcelConverter.Convert <ImportTaxSalary2>(Sheet, HeaderRow, 1); foreach (var item in rows) { list2.Add(HardCode2(item));//将excel数据转换为List对象 } string guid = Guid.NewGuid().ToString(); string result = db.createTaxSalary2(list2, userId, guid); if (result == "") { //调用(全年平均核算)计算存储过程 string salary = db.SalaryTaxCalculator(guid, taxOffice); string[] arr = salary.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries); r["YeaTax"] = arr[0].ToString(); r["MonthTax"] = arr[1].ToString(); r["message"] = "成功"; r["code"] = 2000; } else { r["YeaTax"] = ""; r["MonthTax"] = ""; r["message"] = "成功"; r["code"] = 2000; } } } catch (Exception ex) { r["SalaryTax"] = 0; r["code"] = -1; r["message"] = ex.Message; return(r); } return(r); }