//public void DoWork(JobParameterStruct pS) //{ // string inputFi = pS.InputFile; // string inputFo = pS.AutoInputFolder; // string outputFo = pS.AutoOutputFolder; // string inputEx = pS.AutoExtension; // int cbsi = pS.DataPattern; // if (pS.ActionType < 0 || pS.ActionType > 4) // { // if (MessageBox.Show(@"没有选择任何操作类型,请点击确定后返回重试", "提示", MessageBoxButtons.OK) == DialogResult.OK) { } // } // else if (pS.ActionType < 1 && (pS.FixType < 0 || pS.FixType > 1)) // { // if (MessageBox.Show(@"没有选择温度气压修正选项,请点击确定后返回重试", "提示", MessageBoxButtons.OK) == DialogResult.OK) { } // } // else if (MessageBox.Show(@"处理成功将会删除原纪录,是否要继续?", "提示", MessageBoxButtons.YesNo) == DialogResult.Yes) // { // List<string> probfile = new List<string>(); // UpdateProgress(0); // if (inputFi != "") // { // if (!inputFi.Contains(@"~$")) // { // currentFile = inputFi; // ToolStripStatusLabel_SetText(new FileInfo(inputFi).Name); // try // { // dataerrorNum = 0; // exceptionNum = 0; // dod(inputFi, pS, ref probfile); // } // catch (Exception ex) // { // probfile.Add(inputFi); // if (exceptionNum > 0) // { // exceptionNum = 0; // } // else // { // AddLog(@"***************************************************************", true); // } // AddLog(@"* 文件名称:" + inputFi, true); // AddLog(@"* 异常消息:" + ex.Message, true); // AddLog(@"* 异常方法:" + ex.TargetSite, true); // AddLog(@"***************************************************************", true); // } // if (dataerrorNum > 0) // { // dataerrorNum = 0; // AddLog(@"***************************************************************", true); // } // ToolStripStatusLabel_SetText(""); // } // UpdateProgress(100); // if (probfile.Count > 0) // { // AddLog(@"结果02", "共处理文件数:1", true); // AddLog(@"结果03", "异常文件总数:" + probfile.Count, true); // foreach (string item in probfile) // { // AddLog(@"结果04", item, true); // } // } // else // { // AddLog(@"结果05", "没有发现任何异常,共处理文件数:1", true); // } // MessageBox.Show("共处理文件" + "1" + "个。" + Environment.NewLine + "其中成功" + (1 - probfile.Count).ToString() + "个,失败" + probfile.Count + "个。", "处理完成", MessageBoxButtons.OK); // } // else if (inputFo != "") // { // DirectoryInfo di = new DirectoryInfo(inputFo); // FileInfo[] fis = di.GetFiles(inputEx, SearchOption.AllDirectories); // probfile.Clear(); // int doneNumber = 0; // if (fis.Length > 0) // { // double Step = 100.0 / (double)fis.Length; // double values = 0; // foreach (FileInfo item in fis) // { // if (!item.Name.StartsWith(@"~$")) // { // currentFile = item.FullName; // ToolStripStatusLabel_SetText(new FileInfo(item.FullName).Name); // try // { // dataerrorNum = 0; // exceptionNum = 0; // dod(item.FullName, pS, ref probfile); // } // catch (Exception ex) // { // probfile.Add(item.FullName); // if (exceptionNum > 0) // { // exceptionNum = 0; // } // else // { // AddLog(@"***************************************************************", true); // } // AddLog(@"* 文件名称:" + item.FullName, true); // AddLog(@"* 异常消息:" + ex.Message, true); // AddLog(@"* 异常方法:" + ex.TargetSite, true); // AddLog(@"***************************************************************", true); // } // if (dataerrorNum > 0) // { // dataerrorNum = 0; // AddLog(@"***************************************************************", true); // } // ToolStripStatusLabel_SetText(""); // } // values += Step; // UpdateProgress(values); // doneNumber++; // if (isStopping) // { // break; // } // } // if (probfile.Count > 0) // { // AddLog(@"结果06", "异常文件总数:" + probfile.Count, true); // foreach (string item in probfile) // { // AddLog(@"结果07", item, true); // } // } // else // { // AddLog(@"结果08", "没有发现任何异常,共处理文件数:" + doneNumber, true); // } // MessageBox.Show("共处理文件" + fis.Length + "个。" + Environment.NewLine + "其中成功" + (fis.Length - probfile.Count).ToString() + "个,失败" + probfile.Count + "个。", "处理完成", MessageBoxButtons.OK); // } // else // { // MessageBox.Show(@"输入文件夹没有找到待处理的文件"); // } // } // else // { // MessageBox.Show(@"未指定输入文件"); // } // } // isWorking = false; // isStopping = false; // Button_SetText(@"开始"); // AddLog(Environment.NewLine + Environment.NewLine, true); //} ///// <summary> ///// pattern 1 KV ///// pattern 2 Dose ///// pattern 3 CT ///// pattern 4 ///// </summary> ///// <param name="filePath"></param> ///// <param name="pattern"></param> //public void StatisticsOne(string filePath, int pattern, string text1, string text7, string text3, ref List<string> problemFilesList) //{ // Dictionary<int, string> exSheets = new Dictionary<int, string>(); // Dictionary<int, string> cert = new Dictionary<int, string>(); // List<string> sheetsName = new List<string>(); // bool Perfect; // int stateIndex = -1; // FileInfo temp_fi = null; // int lineIndex = 8; // int countIndex = 0; // bool firstTime = true; // bool needInitialState = true; // string tempName; // string insNumber = ""; // string standardInsNumber = ""; // string sourceDirectory = "";//, temp_text3 = ""; // string strCompany = "", strType = "", strMacSerial = "", strSensorSerial = ""; // string temp_strCompany = "", temp_strType = "", temp_strMacSerial = "", temp_strSensorSerial = ""; // ExcelUtility _eu = new ExcelUtility(filePath, out checkClear); // if (!checkClear) { return; } // _eu.ExcelApp.DisplayAlerts = false; // _eu.ExcelApp.AlertBeforeOverwriting = false; // Object format = MSExcel.XlFileFormat.xlWorkbookDefault; // FileInfo fi = new FileInfo(_eu.path); // //输入文件的目录 // if (text1 == "") // { // sourceDirectory = fi.DirectoryName.Replace(text7, text3) + @"\"; // } // else // { // sourceDirectory = text3 + @"\"; // } // try // { // //1.初始化:规范统计sheet结构,写入固定内容 // stateIndex = _eu.InitialStatisticSheet(out exSheets, out needInitialState, out checkClear); // if (!checkClear) { return; } // _eu.WriteTitle(pattern, stateIndex); // switch (pattern) // { // case 0: // //2.搬运数据 // countIndex = 0; // foreach (int item in exSheets.Keys) // { // countIndex++; // cert.Add(lineIndex, exSheets[item]); // _eu.CopyOneYearData(item, stateIndex, lineIndex, exSheets[item], out insNumber); // tempName = _eu.PerfectFileName(item, out temp_strCompany, out temp_strType, out temp_strMacSerial, out temp_strSensorSerial, out Perfect); // if (temp_strCompany.Length > strCompany.Length) // { // strCompany = temp_strCompany; // } // if (temp_strMacSerial.Length > strMacSerial.Length) // { // strMacSerial = temp_strMacSerial; // } // if (temp_strType.Length > strType.Length) // { // strType = temp_strType; // } // if (temp_strSensorSerial.Length > strSensorSerial.Length) // { // strSensorSerial = temp_strSensorSerial; // } // if (countIndex == 1) // { // standardInsNumber = insNumber; // } // else // { // if (GetPureNumber(standardInsNumber, true) != GetPureNumber(insNumber, true)) // { // AddException(cert[8] + "和" + cert[lineIndex] + "的仪器编号不同:", true); // AddLog(@"错误", " " + cert[8] + @":" + standardInsNumber, true); // AddLog(@"错误", " " + cert[lineIndex] + @":" + insNumber, true); // } // } // if (firstTime && Perfect) // { // _eu.DesiredName = CleanName(tempName); // //写入送校单位 // _eu.WriteValue(stateIndex, 2, 2, strCompany, out checkClear); // //写入仪器名称 // _eu.WriteValue(stateIndex, 3, 2, strType, out checkClear); // firstTime = false; // } // lineIndex++; // } // if (firstTime) // { // //写入送校单位 // _eu.WriteValue(stateIndex, 2, 2, strCompany, out checkClear); // //写入仪器名称 // _eu.WriteValue(stateIndex, 3, 2, strType, out checkClear); // //文件名 // _eu.DesiredName = CleanName(strCompany + "_" + strType + "_" + strMacSerial + ((strSensorSerial.Replace(@"/", "").Trim() != "") ? "+" + strSensorSerial : "")); // } // //3.分析数据 // if (exSheets.Count > 1) // { // _eu.WriteValue(stateIndex, lineIndex, 1, @"重复性", out checkClear); // //计算平均值和年稳定性 // _eu.StatisticsOneColumn(stateIndex, lineIndex, 4, cert, "RQR4(60kV)", exSheets.Count > 1); // _eu.StatisticsOneColumn(stateIndex, lineIndex, 6, cert, "RQR5(70kV)", exSheets.Count > 1); // _eu.StatisticsOneColumn(stateIndex, lineIndex, 8, cert, "RQR6(80kV)", exSheets.Count > 1); // _eu.StatisticsOneColumn(stateIndex, lineIndex, 10, cert, "RQR8(100kV)", exSheets.Count > 1); // _eu.StatisticsOneColumn(stateIndex, lineIndex, 12, cert, "RQR9(120kV)", exSheets.Count > 1); // } // if (exceptionNum > 0) // { // problemFilesList.Add(_eu.DesiredName); // } // break; // case 1: // break; // } // temp_fi = new FileInfo(RightFileName(sourceDirectory, _eu.DesiredName, fi.Extension, "-new")); // if (!temp_fi.Directory.Exists) // { // Directory.CreateDirectory(temp_fi.DirectoryName); // } // _eu.ExcelDoc.SaveAs(temp_fi.FullName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, MSExcel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); // } // catch (Exception ex) // { // exceptionNum++; // if (exceptionNum == 1) // { // AddLog(@"信息", "原文件名:" + filePath, true); // } // AddLog(@"异常", ex.Message, true); // AddLog(@"异常", " " + ex.TargetSite.ToString(), true); // } // finally // { // //移动前的输出文件路径 // sourceDirectory = sourceDirectory + _eu.DesiredName + fi.Extension; // _eu.ExcelDoc.Saved = true; // _eu.TryClose(); // if (exceptionNum > 0) // { // //移动后的输出文件路径 // tempName = RightFileName(Application.StartupPath + @"\有问题整理后\", _eu.DesiredName, fi.Extension, "-new"); // AddLog(@"信息", "新文件名:" + tempName, true); // AddLog(@"***************************************************************", true); // if (File.Exists(sourceDirectory)) File.Move(sourceDirectory, tempName); // if (text1 == "") // { // temp_fi = new FileInfo(fi.FullName.Replace(text7, Application.StartupPath + @"\有问题")); // if (!temp_fi.Directory.Exists) // { // Directory.CreateDirectory(temp_fi.DirectoryName); // } // File.Copy(fi.FullName, temp_fi.FullName, true); // } // else // { // File.Copy(fi.FullName, Application.StartupPath + @"\有问题\" + fi.Name + fi.Extension, true); // } // } // } //} #endregion #region TestingFunction #region GeneratingForm public void GeneratingForm(string filePath, JobParameterStruct pS, ref List<string> problemFilesList) { //int pattern = pS.DataPattern; string output = pS.AutoOutputFolder; bool needFix = pS.AutoFixType; string templateName = pS.DataTemplateFilePath; string macType = pS.MacType; bool success = true; int templateIndex = -1; MSExcel.Worksheet ws1 = null; WordUtility _wu = new WordUtility(filePath, out success); if (!success) { AddException("Word文档打开失败", true); return; } string tempName = _wu.GetText(_wu.WordDocument, 7);//B4:送校单位 string tempSerial = _wu.GetText(_wu.WordDocument, 15).Trim();//F5:仪器型号 //string tempNum = _wu.GetText(_wu.WordDocument, 19);//*H5:仪器编号 string tempQiju = _wu.GetText(_wu.WordDocument, 11);//B5:仪器名称 string tempZhsh = _wu.GetText(_wu.WordDocument, 3);//L2:证书编号 string tempStress = _wu.GetText(_wu.WordDocument, 27);//F4:联系地址 _wu.TryClose(); if (tempSerial != "" && tempSerial != macType) { AddDataError("证书中包含的仪器型号与指定的仪器型号不符", true); } string str = tempZhsh.Substring(8); string strSavename = Path.Combine(output, tempName + "_" + macType + "_" + str + ".xlsx"); if (File.Exists(strSavename)) { if (MessageBox.Show(@"文件已存在,是否覆盖?" + Environment.NewLine + strSavename, "提示", MessageBoxButtons.YesNo) == DialogResult.Yes) { File.Delete(strSavename); } else { success = false; return; } } File.Copy(templateName, strSavename); ExcelUtility _sr = new ExcelUtility(strSavename, out checkClear); if (!checkClear) { AddException(@"Excel文档无法打开", true); if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; 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(@"标准模板")) { AddException(@"发现多余的标准模板", true); } } 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(@"标准模板")) { AddException(@"标准模板复制出错", true); success = false; return; } else { ws1.Name = str; } } else { AddException(@"找不到模板excel中的标准模板页", true); } _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, 4, 2, tempName, out success); _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, 5, 6, macType, out success); _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, 5, 2, tempQiju, out success); _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, 2, 12, str, out success); _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, 4, 6, tempStress, out success); if (needFix) { _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, 8, 13, "修正", out success); } else { //电离室->半导体 MSExcel.Range rr = _sr.GetRange(_sr.ExcelWorkbook, ws1.Index, "L8", out success); rr.FormulaLocal = ""; rr.Formula = ""; rr.FormulaArray = ""; _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, 8, 12, "1.000000", "@", out success); _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, 8, 13, "不修正", out success); } //写入记录者 _sr.WriteImage(_sr.ExcelWorkbook, ws1.Index, 29, 7, person, 45, 28, out success); _sr.ExcelWorkbook.Save(); } catch (Exception ex) { AddException("生成证书时遇到异常:" + ex.Message, true); } finally { //关闭Excel if (_sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } //有重大失误的情况下报错,没有失误就删除源word文件 if (exceptionNum > 0) { AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; } else { File.Delete(filePath); } } }
/// <summary> /// 生成证书和pdf记录 /// </summary> /// <param name="excel">excel记录文件</param> /// <param name="sourceIndex">excel记录页索引号</param> /// <param name="wordPath">证书模板文件</param> /// <param name="savePath">证书记录存储文件夹</param> /// <param name="pdfPath">pdf记录存储文件夹</param> /// <param name="success">成功信号</param> public void GenerateCert(ExcelUtility excel, int sourceIndex, int pattern, string wordPath, string savePath, string pdfPath, string tempFolder, bool shouldFix, out bool success) { //GenerateCert(_sr, stateIndex, path, pS.CertFolder, pS.PDFDataFolder, out success); try { WordUtility wu = new WordUtility(wordPath, out success); if (!success) { AddException("Word文档打开失败", true); return; } string stemp1 = excel.GetText(excel.ExcelWorkbook, sourceIndex, "L2", out success); object otemp1; string wdName = "DYjl" + stemp1 + Path.GetExtension(wordPath); string pdfName = "DYjl" + stemp1 + "_" + excel.GetText(excel.ExcelWorkbook, sourceIndex, "B4", out success) + ".pdf"; switch (pattern) { case 0: //Dose //识别半导体和电离室,对证书模板做对应改动 if (shouldFix) { wu.WriteValue(wu.WordDocument, "M_JZJGR", ",并修正到标准温度、气压"); } //1.普通复制(单位),剂量和CT独有 wu.WriteValue(wu.WordDocument, "M_DW", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "M12", out success)); //2.读取小数点后三位(剂量值和校准因子),剂量和CT独有 wu.WriteDataValue(wu.WordDocument, "M_NC1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "D24", out success), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_NC2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "F24", out success), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_NC3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "H24", out success), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_NC4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "J24", out success), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_NC5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "L24", out success), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_LY1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "D15", out success), "{0:F1}"); //小数点后一位 wu.WriteDataValue(wu.WordDocument, "M_LY2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "F15", out success), "{0:F1}"); //小数点后一位 wu.WriteDataValue(wu.WordDocument, "M_LY3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "H15", out success), "{0:F1}"); //小数点后一位 wu.WriteDataValue(wu.WordDocument, "M_LY4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "J15", out success), "{0:F1}"); //小数点后一位 wu.WriteDataValue(wu.WordDocument, "M_LY5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "L15", out success), "{0:F1}"); //小数点后一位 //3.普通复制(备注说明),剂量CT在B27,KV在B29 if (excel.GetRange(excel.ExcelWorkbook, sourceIndex, "B27", out success) == null || excel.GetRange(excel.ExcelWorkbook, sourceIndex, "B27", out success).ToString() == @"/") { wu.WriteValue(wu.WordDocument, "M_BZSM", "无"); } else { wu.WriteValue(wu.WordDocument, "M_BZSM", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "B27", out success)); } break; case 1: //CT //识别半导体和电离室,对证书模板做对应改动 if (shouldFix) { wu.WriteValue(wu.WordDocument, "M_JZTJ3", Environment.NewLine + "3、电离室戴保护管在辐射野中全照射。"); wu.WriteValue(wu.WordDocument, "M_JZJGR", ",并修正到标准温度、气压"); } //备注说明普通复制,剂量CT在B27,KV在B29 if (excel.GetRange(excel.ExcelWorkbook, sourceIndex, "B27", out success) == null || excel.GetRange(excel.ExcelWorkbook, sourceIndex, "B27", out success).ToString() == @"/") { wu.WriteValue(wu.WordDocument, "M_BZSM", "无"); } else { wu.WriteValue(wu.WordDocument, "M_BZSM", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "B27", out success)); } //校准因子单位写入 if (excel.GetRange(excel.ExcelWorkbook, sourceIndex, "M27", out success) == null || excel.GetRange(excel.ExcelWorkbook, sourceIndex, "M27", out success).ToString().ToLower() == "false") { wu.WriteValue(wu.WordDocument, "M_JZYZ", "无量纲"); } else { wu.WriteValue(wu.WordDocument, "M_JZYZ", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "M27", out success)); } //1.普通复制(单位),剂量和CT独有 wu.WriteValue(wu.WordDocument, "M_DW", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "M12", out success)); //2.读取小数点后三位(剂量值和校准因子),剂量和CT独有 wu.WriteDataValue(wu.WordDocument, "M_NC1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "D24", out success), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_NC2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "F24", out success), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_NC3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "H24", out success), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_NC4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "J24", out success), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_NC5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "L24", out success), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_LY1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "D15", out success), "{0:F1}"); //小数点后一位 wu.WriteDataValue(wu.WordDocument, "M_LY2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "F15", out success), "{0:F1}"); //小数点后一位 wu.WriteDataValue(wu.WordDocument, "M_LY3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "H15", out success), "{0:F1}"); //小数点后一位 wu.WriteDataValue(wu.WordDocument, "M_LY4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "J15", out success), "{0:F1}"); //小数点后一位 wu.WriteDataValue(wu.WordDocument, "M_LY5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "L15", out success), "{0:F1}"); //小数点后一位 break; case 2: //TODO: KV //1.普通复制(测试类型),KV独有 wu.WriteValue(wu.WordDocument, "M_CSLX", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "K12", out success)); //2.读取小数点后两位(实际峰值电压PPV和被测仪器显示值) wu.WriteDataValue(wu.WordDocument, "M_PPV1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "D14", out success), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_PPV2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "F14", out success), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_PPV3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "H14", out success), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_PPV4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "J14", out success), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_PPV5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "L14", out success), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_VALUE1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "D20", out success), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_VALUE2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "F20", out success), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_VALUE3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "H20", out success), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_VALUE4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "J20", out success), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_VALUE5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "L20", out success), "{0:F2}"); //小数点后两位 //3.两位小数的百分数(相对固有误差和过滤影响) wu.WriteDataValue(wu.WordDocument, "M_XDGYWC1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "D22", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_XDGYWC2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "F22", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_XDGYWC3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "H22", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_XDGYWC4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "J22", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_XDGYWC5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "L22", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX21", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "D82", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX31", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "E82", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX22", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "F82", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX32", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "G82", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX23", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "H82", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX33", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "I82", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX24", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "J82", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX34", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "K82", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX25", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "L82", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX35", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "M82", out success), "{0:0.00%}"); //两位小数的百分数 //4.读取小数点后两位(辐照工作下限) wu.WriteDataValue(wu.WordDocument, "M_FFGZXX1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "D52", out success), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_FFGZXX2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "F52", out success), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_FFGZXX3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "H52", out success), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_FFGZXX4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "J52", out success), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_FFGZXX5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "L52", out success), "{0:F2}"); //小数点后两位 //5.两位小数的百分数(80KV重复性) wu.WriteDataValue(wu.WordDocument, "M_BSKVCFX", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "B28", out success), "{0:0.00%}"); //两位小数的百分数 //6.普通复制(备注说明),剂量CT在B27,KV在B29 if (excel.GetRange(excel.ExcelWorkbook, sourceIndex, "B29", out success) == null || excel.GetRange(excel.ExcelWorkbook, sourceIndex, "B29", out success).ToString() == @"/") { wu.WriteValue(wu.WordDocument, "M_BZSM", "无"); } else { wu.WriteValue(wu.WordDocument, "M_BZSM", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "B29", out success)); } break; default: AddException("生成证书时指定了不存在的检定类型", true); break; } /// <summary> /// 类型1:普通复制 /// </summary> wu.WriteValue(wu.WordDocument, "M_NAME", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "B4", out success)); wu.WriteValue(wu.WordDocument, "M_SERIAL", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "F5", out success)); wu.WriteValue(wu.WordDocument, "M_PRODUCT", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "J5", out success)); wu.WriteValue(wu.WordDocument, "M_QIJU", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "B5", out success)); wu.WriteValue(wu.WordDocument, "M_LNGCH", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "D12", out success)); wu.WriteValue(wu.WordDocument, "M_DATE", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "K31", out success)); wu.WriteValue(wu.WordDocument, "M_STRESS", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "F4", out success)); wu.WriteValue(wu.WordDocument, "M_QIYA", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "J8", out success)); wu.WriteValue(wu.WordDocument, "M_TEMP", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "K7", out success)); wu.WriteValue(wu.WordDocument, "M_ZHSH", stemp1); wu.WriteValue(wu.WordDocument, "M_ZHSH2", stemp1); wu.WriteValue(wu.WordDocument, "M_ZHSH3", stemp1); /// <summary> /// 类型2:百分比换算后复制 /// </summary> otemp1 = (object)excel.GetRange(excel.ExcelWorkbook, sourceIndex, "M7", out success).Value; if (otemp1 == null) { stemp1 = "/"; } else { stemp1 = string.Format("{0:F1}", float.Parse(otemp1.ToString()) * 100); } wu.WriteValue(wu.WordDocument, "M_SHIDU", stemp1); /// <summary> /// 类型3:仪器编号两段合并后复制 /// </summary> otemp1 = (object)excel.GetRange(excel.ExcelWorkbook, sourceIndex, "H5", out success).Value; if (otemp1 == null) { stemp1 = ""; } else { stemp1 = otemp1.ToString(); } otemp1 = (object)excel.GetRange(excel.ExcelWorkbook, sourceIndex, "L5", out success).Value; if (otemp1 == null) { if (stemp1 == "") { stemp1 = "/"; } } else if (otemp1.ToString() == "/") { if (stemp1 == "") { stemp1 = "/"; } } else { stemp1 = stemp1 + " + " + otemp1.ToString(); } wu.WriteValue(wu.WordDocument, "M_NUM", stemp1); //另存word和pdf wu.WordDocument.SaveAs2(Path.Combine(savePath, DataUtility.DataUtility.FileNameCleanName(wdName))); excel.SaveAsPDF(excel.ExcelWorkbook, Path.Combine(pdfPath, DataUtility.DataUtility.FileNameCleanName(pdfName)), tempFolder, out success); wu.WordDocument.Saved = true; wu.TryClose(); } catch (Exception ex) { success = false; AddException("生成证书时出现错误:" + ex.Message, true); } }
/// <summary> /// 生成证书和pdf记录 /// </summary> /// <param name="excel">excel记录文件</param> /// <param name="sourceIndex">excel记录页索引号</param> /// <param name="wordPath">证书模板文件</param> /// <param name="savePath">证书记录存储文件夹</param> /// <param name="pdfPath">pdf记录存储文件夹</param> /// <param name="success">成功信号</param> public void GenerateCert(ExcelUtility excel, int sourceIndex, int pattern, string wordPath, string savePath, string pdfPath, string tempFolder, bool shouldFix, out bool success) { //GenerateCert(_sr, stateIndex, path, pS.CertFolder, pS.PDFDataFolder, out success); try { WordUtility wu = new WordUtility(wordPath, out success); if (!success) { Log.LogHelper.AddException("Word文档打开失败", true); return; } string stemp1 = excel.GetText(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("L2")); object otemp1; string wdName = "DYjl" + stemp1 + Path.GetExtension(wordPath); string pdfName = "DYjl" + stemp1 + "_" + excel.GetText(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B4")) + ".pdf"; switch (pattern) { case 0: //检定 //检定依据 wu.WriteValue(wu.WordDocument, "m_JDYJ", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B8"))); //有效日期 string[] dates = excel.GetText(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("K29")).Split(new string[] { "年", "月", "日", " " }, StringSplitOptions.RemoveEmptyEntries); wu.WriteValue(wu.WordDocument, "m_YXRQ1", dates[0].Substring(2)); wu.WriteValue(wu.WordDocument, "m_YXRQ2", dates[1]); wu.WriteValue(wu.WordDocument, "m_YXRQ3", dates[2]); //检定日期 dates = excel.GetText(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("K30")).Split(new string[] { "年", "月", "日", " " }, StringSplitOptions.RemoveEmptyEntries); wu.WriteValue(wu.WordDocument, "m_JDRQ1", dates[0].Substring(2)); wu.WriteValue(wu.WordDocument, "m_JDRQ2", dates[1]); wu.WriteValue(wu.WordDocument, "m_JDRQ3", dates[2]); //备注 wu.WriteValue(wu.WordDocument, "m_BZ", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B27"))); break; case 1: //校准 //客户地址 wu.WriteValue(wu.WordDocument, "m_KHDZ", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("F4"))); //校准依据 wu.WriteValue(wu.WordDocument, "m_JZYJ", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B8"))); //校准日期 wu.WriteValue(wu.WordDocument, "m_JZRQ", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("K30"))); //扩展不确定度 wu.WriteValue(wu.WordDocument, "m_KZBQDD", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("K26"))); break; default: Log.LogHelper.AddException("生成证书时指定了不存在的检定类型", true); break; } /// <summary> /// 类型1:普通复制 /// </summary> wu.WriteValue(wu.WordDocument, "m_SJDW", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B4"))); wu.WriteValue(wu.WordDocument, "m_QJMC", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B5"))); wu.WriteValue(wu.WordDocument, "m_XHGG", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("F5"))); wu.WriteValue(wu.WordDocument, "m_SCCS", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("J5"))); wu.WriteValue(wu.WordDocument, "m_LC", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("D12"))); wu.WriteValue(wu.WordDocument, "m_QY", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("J8"))); wu.WriteValue(wu.WordDocument, "m_WD", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("K7"))); wu.WriteValue(wu.WordDocument, "m_ZSBH1", stemp1); wu.WriteValue(wu.WordDocument, "m_ZSBH2", stemp1); wu.WriteValue(wu.WordDocument, "m_ZSBH3", stemp1); /// <summary> /// 类型2:百分比换算后复制 /// </summary> otemp1 = (object)excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("M7")).Value; if (otemp1 == null) { stemp1 = "/"; } else { stemp1 = string.Format("{0:F1}", float.Parse(otemp1.ToString()) * 100); } wu.WriteValue(wu.WordDocument, "m_SD", stemp1); /// <summary> /// 类型3:仪器编号两段合并后复制 /// </summary> otemp1 = (object)excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("H5")).Value; if (otemp1 == null) { stemp1 = ""; } else { stemp1 = otemp1.ToString(); } otemp1 = (object)excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("L5")).Value; if (otemp1 == null) { if (stemp1 == "") { stemp1 = "/"; } } else if (otemp1.ToString() == "/") { if (stemp1 == "") { stemp1 = "/"; } } else { stemp1 = stemp1 + " + " + otemp1.ToString(); } wu.WriteValue(wu.WordDocument, "m_CCBH", stemp1); /// <summary> /// 类型4:数据部分 /// </summary> wu.WriteDataValue(wu.WordDocument, "m_DATA1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("D24")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "m_DATA2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("F24")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "m_DATA3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("H24")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "m_DATA4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("J24")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "m_DATA5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("L24")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "m_DATA6", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("D54")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "m_DATA7", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("F54")), "{0:F3}"); //小数点后三位 //另存word和pdf wu.WordDocument.SaveAs2(Path.Combine(savePath, DataUtility.DataUtility.FileNameCleanName(wdName))); excel.SaveAsPDF(excel.ExcelWorkbook, Path.Combine(pdfPath, DataUtility.DataUtility.FileNameCleanName(pdfName)), tempFolder, out success); wu.WordDocument.Saved = true; wu.TryClose(); } catch (Exception ex) { success = false; Log.LogHelper.AddException("生成证书时出现错误:" + ex.Message, 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); } } }
/// <summary> /// 生成证书和pdf记录 /// </summary> /// <param name="excel">excel记录文件</param> /// <param name="sourceIndex">excel记录页索引号</param> /// <param name="wordPath">证书模板文件</param> /// <param name="savePath">证书记录存储文件夹</param> /// <param name="pdfPath">pdf记录存储文件夹</param> /// <param name="success">成功信号</param> public void GenerateCert(ExcelUtility excel, int sourceIndex, int pattern, string wordPath, string savePath, string pdfPath, string tempFolder, bool shouldFix, out bool success) { //GenerateCert(_sr, stateIndex, path, pS.CertFolder, pS.PDFDataFolder, out success); try { WordUtility wu = new WordUtility(wordPath, out success); if (!success) { Log.LogHelper.AddException("Word文档打开失败", true); return; } string stemp1 = excel.GetText(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("L2")); object otemp1; string wdName = "DYjl" + stemp1 + Path.GetExtension(wordPath); string pdfName = "DYjl" + stemp1 + "_" + excel.GetText(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B4")) + ".pdf"; switch (pattern) { case 0: //检定 //检定依据 wu.WriteValue(wu.WordDocument, "m_JDYJ", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B8"))); //有效日期 string[] dates = excel.GetText(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("K29")).Split(new string[] { "年", "月", "日", " " }, StringSplitOptions.RemoveEmptyEntries); wu.WriteValue(wu.WordDocument, "m_YXRQ1", dates[0].Substring(2)); wu.WriteValue(wu.WordDocument, "m_YXRQ2", dates[1]); wu.WriteValue(wu.WordDocument, "m_YXRQ3", dates[2]); //检定日期 dates = excel.GetText(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("K30")).Split(new string[] { "年", "月", "日", " " }, StringSplitOptions.RemoveEmptyEntries); wu.WriteValue(wu.WordDocument, "m_JDRQ1", dates[0].Substring(2)); wu.WriteValue(wu.WordDocument, "m_JDRQ2", dates[1]); wu.WriteValue(wu.WordDocument, "m_JDRQ3", dates[2]); //备注 wu.WriteValue(wu.WordDocument, "m_BZ", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B27"))); break; case 1: //校准 //客户地址 wu.WriteValue(wu.WordDocument, "m_KHDZ", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("F4"))); //校准依据 wu.WriteValue(wu.WordDocument, "m_JZYJ", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B8"))); //校准日期 wu.WriteValue(wu.WordDocument, "m_JZRQ", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("K30"))); //扩展不确定度 wu.WriteValue(wu.WordDocument, "m_KZBQDD", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("K26"))); break; default: Log.LogHelper.AddException("生成证书时指定了不存在的检定类型", true); break; } /// <summary> /// 类型1:普通复制 /// </summary> wu.WriteValue(wu.WordDocument, "m_SJDW", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B4"))); wu.WriteValue(wu.WordDocument, "m_QJMC", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B5"))); wu.WriteValue(wu.WordDocument, "m_XHGG", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("F5"))); wu.WriteValue(wu.WordDocument, "m_SCCS", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("J5"))); wu.WriteValue(wu.WordDocument, "m_LC", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("D12"))); wu.WriteValue(wu.WordDocument, "m_QY", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("J8"))); wu.WriteValue(wu.WordDocument, "m_WD", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("K7"))); wu.WriteValue(wu.WordDocument, "m_ZSBH1", stemp1); wu.WriteValue(wu.WordDocument, "m_ZSBH2", stemp1); wu.WriteValue(wu.WordDocument, "m_ZSBH3", stemp1); /// <summary> /// 类型2:百分比换算后复制 /// </summary> otemp1 = (object)excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("M7")).Value; if (otemp1 == null) { stemp1 = "/"; } else { stemp1 = string.Format("{0:F1}", float.Parse(otemp1.ToString()) * 100); } wu.WriteValue(wu.WordDocument, "m_SD", stemp1); /// <summary> /// 类型3:仪器编号两段合并后复制 /// </summary> otemp1 = (object)excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("H5")).Value; if (otemp1 == null) { stemp1 = ""; } else { stemp1 = otemp1.ToString(); } otemp1 = (object)excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("L5")).Value; if (otemp1 == null) { if (stemp1 == "") { stemp1 = "/"; } } else if (otemp1.ToString() == "/") { if (stemp1 == "") { stemp1 = "/"; } } else { stemp1 = stemp1 + " + " + otemp1.ToString(); } wu.WriteValue(wu.WordDocument, "m_CCBH", stemp1); /// <summary> /// 类型4:数据部分 /// </summary> wu.WriteDataValue(wu.WordDocument, "m_DATA1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("D24")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "m_DATA2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("F24")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "m_DATA3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("H24")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "m_DATA4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("J24")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "m_DATA5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("L24")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "m_DATA6", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("D54")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "m_DATA7", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("F54")), "{0:F3}"); //小数点后三位 //另存word和pdf wu.WordDocument.SaveAs2(Path.Combine(savePath, DataUtility.DataUtility.FileNameCleanName(wdName))); excel.SaveAsPDF(excel.ExcelWorkbook, Path.Combine(pdfPath, DataUtility.DataUtility.FileNameCleanName(pdfName)), tempFolder, out success); wu.WordDocument.Saved = true; wu.TryClose(); } catch (Exception ex) { success = false; Log.LogHelper.AddException("生成证书时出现错误:" + ex.Message, 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); } } }