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) { LogHelper.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) { LogHelper.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) { LogHelper.AddException(@"Excel文档无法打开", true); if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } LogHelper.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(@"标准模板")) { LogHelper.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(@"标准模板")) { LogHelper.AddException(@"标准模板复制出错", true); success = false; return; } else { ws1.Name = str; } } 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); _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, new ExcelPosition(4, 6), tempStress); if (needFix) { _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, new ExcelPosition(8, 13), "修正"); } else { //电离室->半导体 MSExcel.Range rr = _sr.GetRange(_sr.ExcelWorkbook, ws1.Index, new ExcelPosition("L8")); rr.FormulaLocal = ""; rr.Formula = ""; rr.FormulaArray = ""; _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, new ExcelPosition(8, 12), "1.000000", "@"); _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, new ExcelPosition(8, 13), "不修正"); } //写入记录者 _sr.WriteImage(_sr.ExcelWorkbook, ws1.Index, new ExcelPosition(29, 7), Util.PathExt.PathCombine(Application.StartupPath, person.Path), 45, 28); _sr.ExcelWorkbook.Save(); } catch (Exception ex) { LogHelper.AddException("生成证书时遇到异常:" + ex.Message, true); } finally { //关闭Excel if (_sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } //有重大失误的情况下报错,没有失误就删除源word文件 if (exceptionNum > 0) { LogHelper.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 static 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) { 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: //Dose //识别半导体和电离室,对证书模板做对应改动 if (shouldFix) { wu.WriteValue(wu.WordDocument, "M_JZJGR", ",并修正到标准温度、气压"); } //1.普通复制(单位),剂量和CT独有 wu.WriteValue(wu.WordDocument, "M_DW", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("M12"))); //2.读取小数点后三位(剂量值和校准因子),剂量和CT独有 wu.WriteDataValue(wu.WordDocument, "M_NC1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("D24")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_NC2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("F24")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_NC3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("H24")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_NC4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("J24")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_NC5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("L24")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_LY1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("D15")), "{0:F1}"); //小数点后一位 wu.WriteDataValue(wu.WordDocument, "M_LY2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("F15")), "{0:F1}"); //小数点后一位 wu.WriteDataValue(wu.WordDocument, "M_LY3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("H15")), "{0:F1}"); //小数点后一位 wu.WriteDataValue(wu.WordDocument, "M_LY4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("J15")), "{0:F1}"); //小数点后一位 wu.WriteDataValue(wu.WordDocument, "M_LY5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("L15")), "{0:F1}"); //小数点后一位 //3.普通复制(备注说明),剂量CT在B27,KV在B29 if (excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B27")) == null || excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B27")).ToString() == @"/") { wu.WriteValue(wu.WordDocument, "M_BZSM", "无"); } else { wu.WriteValue(wu.WordDocument, "M_BZSM", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B27"))); } 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, new ExcelPosition("B27")) == null || excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B27")).ToString() == @"/") { wu.WriteValue(wu.WordDocument, "M_BZSM", "无"); } else { wu.WriteValue(wu.WordDocument, "M_BZSM", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B27"))); } //校准因子单位写入 if (excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("M27")) == null || excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("M27")).ToString().ToLower() == "false") { wu.WriteValue(wu.WordDocument, "M_JZYZ", "无量纲"); } else { wu.WriteValue(wu.WordDocument, "M_JZYZ", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("M27"))); } //1.普通复制(单位),剂量和CT独有 wu.WriteValue(wu.WordDocument, "M_DW", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("M12"))); //2.读取小数点后三位(剂量值和校准因子),剂量和CT独有 wu.WriteDataValue(wu.WordDocument, "M_NC1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("D24")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_NC2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("F24")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_NC3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("H24")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_NC4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("J24")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_NC5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("L24")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_LY1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("D15")), "{0:F1}"); //小数点后一位 wu.WriteDataValue(wu.WordDocument, "M_LY2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("F15")), "{0:F1}"); //小数点后一位 wu.WriteDataValue(wu.WordDocument, "M_LY3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("H15")), "{0:F1}"); //小数点后一位 wu.WriteDataValue(wu.WordDocument, "M_LY4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("J15")), "{0:F1}"); //小数点后一位 wu.WriteDataValue(wu.WordDocument, "M_LY5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("L15")), "{0:F1}"); //小数点后一位 break; case 2: //TODO: KV //1.普通复制(测试类型),KV独有 wu.WriteValue(wu.WordDocument, "M_CSLX", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("K12"))); //2.读取小数点后两位(实际峰值电压PPV和被测仪器显示值) wu.WriteDataValue(wu.WordDocument, "M_PPV1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("D14")), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_PPV2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("F14")), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_PPV3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("H14")), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_PPV4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("J14")), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_PPV5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("L14")), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_VALUE1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("D20")), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_VALUE2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("F20")), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_VALUE3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("H20")), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_VALUE4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("J20")), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_VALUE5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("L20")), "{0:F2}"); //小数点后两位 //3.两位小数的百分数(相对固有误差和过滤影响) wu.WriteDataValue(wu.WordDocument, "M_XDGYWC1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("D22")), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_XDGYWC2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("F22")), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_XDGYWC3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("H22")), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_XDGYWC4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("J22")), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_XDGYWC5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("L22")), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX21", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("D82")), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX31", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("E82")), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX22", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("F82")), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX32", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("G82")), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX23", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("H82")), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX33", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("I82")), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX24", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("J82")), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX34", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("K82")), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX25", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("L82")), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX35", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("M82")), "{0:0.00%}"); //两位小数的百分数 //4.读取小数点后两位(辐照工作下限) wu.WriteDataValue(wu.WordDocument, "M_FZGZXX1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("D52")), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_FZGZXX2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("F52")), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_FZGZXX3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("H52")), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_FZGZXX4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("J52")), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_FZGZXX5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("L52")), "{0:F2}"); //小数点后两位 //5.两位小数的百分数(80KV重复性) wu.WriteDataValue(wu.WordDocument, "M_BSKVCFX", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B28")), "{0:0.00%}"); //两位小数的百分数 //6.普通复制(备注说明),剂量CT在B27,KV在B29 if (excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B29")) == null || excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B29")).ToString() == @"/") { wu.WriteValue(wu.WordDocument, "M_BZSM", "无"); } else { wu.WriteValue(wu.WordDocument, "M_BZSM", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B29"))); } break; default: Log.LogHelper.AddException("生成证书时指定了不存在的检定类型", true); break; } /// <summary> /// 类型1:普通复制 /// </summary> wu.WriteValue(wu.WordDocument, "M_NAME", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B4"))); wu.WriteValue(wu.WordDocument, "M_SERIAL", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("F5"))); wu.WriteValue(wu.WordDocument, "M_PRODUCT", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("J5"))); wu.WriteValue(wu.WordDocument, "M_QIJU", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B5"))); wu.WriteValue(wu.WordDocument, "M_LNGCH", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("D12"))); wu.WriteValue(wu.WordDocument, "M_DATE", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("K31"))); wu.WriteValue(wu.WordDocument, "M_STRESS", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("F4"))); wu.WriteValue(wu.WordDocument, "M_QIYA", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("J8"))); wu.WriteValue(wu.WordDocument, "M_TEMP", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("K7"))); 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, new ExcelPosition("M7")).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, 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_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; Log.LogHelper.AddException("生成证书时出现错误:" + ex.Message, true); } }