public override void DoTheJob() { int pattern = jobParam.DataPattern; string output = jobParam.AutoOutputFolder; string ext = jobParam.AutoExtension; bool createNew = jobParam.CreateNew; string tempFo = jobParam.TempFolder; FileInfo[] existFile = null; Dictionary <int, string> exSheets = new Dictionary <int, string>(); bool Perfect; bool success; bool hasArchieved = false; bool needFix = true; bool shouldFix = true; bool needTestGeCe = true, canGeCe = false; int stateIndex = -1; int noIdNumber = 0; int startDestiRowIndex = -1; string tempName; string certId; string newName = ""; string fileText = ""; string strCompany = "", strType = "", strMacSerial = "", strSensorSerial = ""; string backupKey = ""; FileInfo temp_fi = null; FileInfo fi = new FileInfo(filePath); Object format = MSExcel.XlFileFormat.xlWorkbookDefault; ExcelUtility _sr = new ExcelUtility(filePath, out success); ExcelUtility _eu = null; MSExcel.Range rr = null; if (!success) { LogHelper.AddException(@"Excel文档无法打开", true); if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } LogHelper.AddProblemFilesAndReset(filePath); return; } _sr.ExcelApp.DisplayAlerts = false; _sr.ExcelApp.AlertBeforeOverwriting = false; //第一次循环:获取信息,并规范每页的标签 foreach (MSExcel.Worksheet item in _sr.ExcelWorkbook.Sheets) { //规范sheet标签名为证书编号 certId = _sr.GetText(_sr.ExcelWorkbook, item.Index, new ExcelPosition("L2")); if (certId.StartsWith(@"20") && (certId.Length == 9 || certId.Length == 10)) { //有规范的证书号 exSheets.Add(item.Index, certId); stateIndex = item.Index; } else { //无规范的证书号 rr = _sr.GetRange(_sr.ExcelWorkbook, item.Index, new ExcelPosition("A4")); if (!item.Name.Contains(@"标准模板") && rr.Text.ToString().Trim().StartsWith(@"送校单位")) { //有记录不包含规范的证书编号 LogHelper.AddException(@"该文档有实验数据不包含证书编号", true); noIdNumber++; } } } if (exSheets.Count == 0) { if (noIdNumber == 0) { LogHelper.AddException(@"该文档可能是空文档", true); } if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } LogHelper.AddProblemFilesAndReset(filePath); return; } else if (exSheets.Count + noIdNumber > 1) { LogHelper.AddException(@"该文档包含多个数据sheet,默认处理第一个", true); } certId = exSheets[stateIndex]; tempName = _sr.GenerateFileName(_sr.ExcelWorkbook, stateIndex, out strCompany, out strType, out strMacSerial, out strSensorSerial, out Perfect); success = false; if (strCompany == "") { LogHelper.AddException(@"送校单位信息未提取到", true); success = true; } if (strType == "") { LogHelper.AddException(@"仪器型号信息未提取到", true); success = true; } if (strMacSerial == "") { LogHelper.AddException(@"主机编号信息未提取到", true); success = true; } if (success) { if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } LogHelper.AddProblemFilesAndReset(filePath); return; } JobMethods.GetFixState(_sr, stateIndex, pattern, out needFix, out shouldFix); //目标行数 startDestiRowIndex = 18; try { //寻找目标统计文件 existFile = JobMethods.GetFilesFromType(output, _sr.GetText(_sr.ExcelWorkbook, stateIndex, new ExcelPosition("F5")), ext, out success); if (!success) { if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } LogHelper.AddProblemFilesAndReset(filePath); return; } temp_fi = JobMethods.SearchForFile(filePath, strType, strMacSerial, strSensorSerial, existFile, out hasArchieved); if (temp_fi == null) { if (createNew) { //对新记录建档 needTestGeCe = false; if (hasArchieved) { //不存在,复制当前记录过去 newName = DataUtility.DataUtility.PathRightFileName(Util.PathExt.PathCombine(output, strType), tempName, fi.Extension, "_new"); _sr.ExcelWorkbook.SaveAs(newName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, MSExcel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); temp_fi = new FileInfo(newName); JobMethods.Statistic(_sr, pattern, Perfect, strCompany, strType, tempName, certId); //TODO: 生成证书处校验是否可选(不存在,复制当前记录过去) _sr.Verification(_sr.ExcelWorkbook, true, pattern, out success); fileText = newName; } else { //不存在,但搜索到有可疑项,暂不处理 fileText = filePath; } } else { //对新记录不建档,退出 LogHelper.AddException("没有在历史数据记录中发现匹配项,暂不处理。", true); if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } LogHelper.AddProblemFilesAndReset(filePath); return; } } else if (File.Exists(temp_fi.FullName)) { DataUtility.DataUtility.BackupFile(tempFo, temp_fi.FullName, out backupKey); _eu = new ExcelUtility(temp_fi.FullName, out success); if (!success) { LogHelper.AddException(@"Excel文档无法打开", true); if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } if (_eu != null && _eu.ExcelWorkbook != null) { _eu.ExcelWorkbook.Saved = true; _eu.TryClose(); } LogHelper.AddProblemFilesAndReset(filePath); return; } _eu.ExcelApp.DisplayAlerts = false; _eu.ExcelApp.AlertBeforeOverwriting = false; JobMethods.CopyData(_sr, stateIndex, _eu, pattern, certId, needFix, shouldFix, startDestiRowIndex, out success); if (!success) { if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } if (_eu != null && _eu.ExcelWorkbook != null) { _eu.ExcelWorkbook.Saved = true; _eu.TryClose(); } LogHelper.AddProblemFilesAndReset(fileText); return; } _eu.ExcelWorkbook.Save(); _eu.ExcelWorkbook.Saved = true; canGeCe = JobMethods.Statistic(_eu, pattern, Perfect, strCompany, strType, tempName, certId); _eu.ExcelWorkbook.Save(); _eu.ExcelWorkbook.Saved = true; //TODO: 生成证书处校验是否可选(存在,合并入原记录) _eu.Verification(_eu.ExcelWorkbook, false, pattern, out success); fileText = temp_fi.FullName; } else { LogHelper.AddException(@"文件不存在:" + temp_fi.FullName, true); fileText = filePath; } } catch (Exception ex) { LogHelper.AddException(@"生成证书时合并一步遇到异常:" + ex.Message, true); } try { //有重大失误,关闭两个excel,报错退出。没有失误继续运行 if (LogHelper.HasException) { if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } if (_eu != null && _eu.ExcelWorkbook != null) { _eu.ExcelWorkbook.Saved = true; _eu.TryClose(); } LogHelper.AddProblemFilesAndReset(fileText); } else { //有以前记录,需要看是否超差 并且 超差不通过 并且 超差不通过的提示选择不生成证书 时选择退出 if (needTestGeCe && !canGeCe && !FormOperator.MessageBox_Show_YesNo("检测到有数据超差,是否继续生成证书?", "问题")) { LogHelper.AddException("有实验数据超差,暂时保留原记录,不做任何处理。", true); if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } if (_eu != null && _eu.ExcelWorkbook != null) { _eu.ExcelWorkbook.Saved = true; _eu.TryClose(); } DataUtility.DataUtility.RestoreFile(tempFo, backupKey); LogHelper.AddProblemFilesAndReset(fileText); } else { success = false; //关闭归档后的Excel if (_eu != null && _eu.ExcelWorkbook != null) { _eu.ExcelWorkbook.Saved = true; _eu.TryClose(); } //新文档因为将sr另存为合并后的文档,需要关闭后重新打开原文档 if (!needTestGeCe) { //关闭原来另存为成合并好的excel文件 if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } //重新打开待合并的excel文件 _sr = new ExcelUtility(filePath, out success); if (!success) { LogHelper.AddException(@"Excel文档无法打开", true); if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } LogHelper.AddProblemFilesAndReset(filePath); return; } //打开成功 _sr.ExcelApp.DisplayAlerts = false; _sr.ExcelApp.AlertBeforeOverwriting = false; } //找到原记录里的数据页,记下序号 stateIndex = -1; string temp = ""; foreach (MSExcel.Worksheet item in _sr.ExcelWorkbook.Sheets) { temp = _sr.GetText(_sr.ExcelWorkbook, item.Index, new ExcelPosition("L2")); if (certId == item.Name || certId == temp) { stateIndex = item.Index; } } //找到序号的话,加入校核人的签名,删除其他sheet if (stateIndex > 0 && stateIndex < _sr.ExcelWorkbook.Worksheets.Count) { _sr.WriteImage(_sr.ExcelWorkbook, stateIndex, new ExcelPosition(30, 9), Util.PathExt.PathCombine(ProgramConfiguration.ProgramFolder, person.Path), 45, 28); for (int i = _sr.ExcelWorkbook.Worksheets.Count; i > 0; i--) { if (i != stateIndex) { ((MSExcel.Worksheet)_sr.ExcelApp.ActiveWorkbook.Sheets[i]).Delete(); } } _sr.ExcelWorkbook.Save(); _sr.ExcelWorkbook.Saved = true; } //出证书 GenerateCert(_sr, stateIndex, jobParam.DataPattern, jobParam.CertTemplateFilePath, jobParam.CertFolder, jobParam.PDFDataFolder, jobParam.TempFolder, shouldFix, out success); if (!success) { LogHelper.AddException("生成证书失败", true); if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } LogHelper.AddProblemFilesAndReset(fileText); } else { if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } File.Delete(filePath); } } } } catch (Exception ex) { LogHelper.AddException(@"生成证书时遇到异常:" + ex.Message, true); } if (LogHelper.HasDataError) { LogHelper.ResetError(); LogHelper.AddLog(@"***************************************************************", true); } }
public override void DoTheJob() { string output = jobParam.AutoOutputFolder; FixType fix = jobParam.FixType; bool needFix = jobParam.AutoFixType; string templateName = jobParam.DataTemplateFilePath; string macType = jobParam.MacType; bool success = true; int templateIndex = -1; MSExcel.Worksheet ws1 = null; WordUtility _wu = new WordUtility(filePath, out success); if (!success) { LogHelper.AddException("Word文档打开失败", true); return; } string tempZhsh = _wu.GetText(_wu.WordDocument, 3); //L2:证书编号 string tempName = _wu.GetText(_wu.WordDocument, 7); //B4:送校单位 string tempQiju = _wu.GetText(_wu.WordDocument, 11); //B5:仪器名称 string tempSerial = _wu.GetText(_wu.WordDocument, 15).Trim(); //F5:仪器型号 _wu.TryClose(); if (tempSerial != "" && tempSerial != macType) { LogHelper.AddDataError("证书中包含的仪器型号与指定的仪器型号不符" + Environment.NewLine + "证书仪器型号: " + tempSerial + Environment.NewLine + "指定仪器型号: " + macType, true); } string str = tempZhsh.Substring(8); string strSavename = PathExt.PathCombine(output, tempName + "_" + macType + "_" + str + ".xlsx"); if (File.Exists(strSavename)) { if (FormOperator.MessageBox_Show_YesNo(@"文件已存在,是否覆盖?" + Environment.NewLine + strSavename, "提示")) { File.Delete(strSavename); } else { success = false; return; } } File.Copy(templateName, strSavename); ExcelUtility _sr = new ExcelUtility(strSavename, out success); if (!success) { if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } LogHelper.AddException(@"Excel文档无法打开", true); LogHelper.AddProblemFilesAndReset(filePath); return; } _sr.ExcelApp.DisplayAlerts = false; _sr.ExcelApp.AlertBeforeOverwriting = false; try { foreach (MSExcel.Worksheet item in _sr.ExcelWorkbook.Sheets) { if (item.Name == @"标准模板") { templateIndex = item.Index; } else if (item.Name.Contains(@"标准模板")) { LogHelper.AddException(@"发现多余的标准模板", true); } } LogHelper.State.Push("找到标准模板:" + templateIndex.ToString()); if (templateIndex > -1) { ws1 = (MSExcel.Worksheet)_sr.ExcelWorkbook.Sheets[templateIndex]; ws1.Copy(ws1, Type.Missing); ws1 = (MSExcel.Worksheet)_sr.ExcelWorkbook.Sheets[templateIndex]; if (!ws1.Name.Contains(@"标准模板")) { LogHelper.AddException(@"标准模板复制出错", true); success = false; return; } else { ws1.Name = str; } LogHelper.State.Push("复制标准模板完成"); } else { LogHelper.AddException(@"找不到模板excel中的标准模板页", true); } _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, new ExcelPosition(4, 2), tempName); _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, new ExcelPosition(5, 6), macType); _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, new ExcelPosition(5, 2), tempQiju); _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, new ExcelPosition(2, 12), str); LogHelper.State.Push("写入4个信息"); _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, new ExcelPosition(8, 13), EnumExt.GetDescriptionFromEnumValue <FixType>(fix)); LogHelper.State.Push("写入修正信息"); if (!needFix) { //电离室->半导体 MSExcel.Range rr = _sr.GetRange(_sr.ExcelWorkbook, ws1.Index, new ExcelPosition("L8")); rr.FormulaLocal = ""; rr.Formula = ""; rr.FormulaArray = ""; LogHelper.State.Push("不修正时清空公式"); _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, new ExcelPosition(8, 12), "1.000000", "@"); LogHelper.State.Push("写入1"); } LogHelper.State.Push("准备写入记录者图片"); //写入记录者 _sr.WriteImage(_sr.ExcelWorkbook, ws1.Index, new ExcelPosition(29, 7), PathExt.PathCombine(ProgramConfiguration.ProgramFolder, person.Path), 45, 28); LogHelper.State.Push("写完记录者图片"); _sr.ExcelWorkbook.Save(); LogHelper.State.Push("保存完毕"); } catch (Exception ex) { Log.LogHelper.AddException("生成证书时遇到异常:" + ex.Message, true); Log.LogHelper.AddLog("执行位置:" + LogHelper.State.Peek(), true); } finally { //关闭Excel if (_sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } //有重大失误的情况下报错,没有失误就删除源word文件 if (LogHelper.HasException) { LogHelper.AddProblemFilesAndReset(filePath); } else { File.Delete(filePath); } } }