/// <summary> /// 存档合并模式 /// </summary> /// <param name="filePath">需要处理的文件名</param> /// <param name="pS">处理过程需要的参数集</param> /// <param name="problemFilesList">出问题的文件列表</param> public void ArchievingMergeOne(string filePath, JobParameterStruct pS, ref List<string> problemFilesList) { int pattern = pS.DataPattern; string output = pS.OutputFolder; Dictionary<int, string> exSheets = new Dictionary<int, string>(); bool Perfect; bool isArchieved = false; bool needFix = true; bool shouldFix = true; int stateIndex = -1; int noIdNumber = 0; int startDestiRowIndex = -1; string tempName; string certId; string newName = ""; string fileText = ""; string renameStr; string strCompany = "", strType = "", strMacSerial = "", strSensorSerial = ""; FileInfo temp_fi = null; FileInfo fi = new FileInfo(filePath); Object format = MSExcel.XlFileFormat.xlWorkbookDefault; existFile = (new DirectoryInfo(output)).GetFiles(@"*.xls*", SearchOption.AllDirectories); ExcelUtility _sr = new ExcelUtility(filePath, out checkClear); ExcelUtility _eu = null; MSExcel.Range rr = null; 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; //第一次循环:获取信息,并规范每页的标签 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++; } } renameStr = _sr.GetText(_sr.ExcelWorkbook, item.Index, new ExcelPosition(5, 7)).ToLower(); if (renameStr.StartsWith(@"编号:")) { _sr.WriteValue(_sr.ExcelWorkbook, item.Index, new ExcelPosition(5, 7), renameStr.Replace(@"编号:", @"主机编号:")); } renameStr = _sr.GetText(_sr.ExcelWorkbook, item.Index, new ExcelPosition(5, 11)).ToLower(); if (renameStr.StartsWith(@"电离室号:")) { _sr.WriteValue(_sr.ExcelWorkbook, item.Index, new ExcelPosition(5, 11), renameStr.Replace(@"电离室号:", @"探测器编号:")); } else if (renameStr.StartsWith(@"探测器号:")) { _sr.WriteValue(_sr.ExcelWorkbook, item.Index, new ExcelPosition(5, 11), renameStr.Replace(@"探测器号:", @"探测器编号:")); } } _sr.ExcelWorkbook.Save(); _sr.ExcelWorkbook.Saved = true; if (exSheets.Count == 0) { if (noIdNumber == 0) { LogHelper.AddException(@"该文档可能是空文档", true); } if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } LogHelper.AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; 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); checkClear = false; if (strCompany == "") { LogHelper.AddException(@"送校单位信息未提取到", true); checkClear = true; } if (strType == "") { LogHelper.AddException(@"仪器型号信息未提取到", true); checkClear = true; } if (strMacSerial == "" || strMacSerial == "主机_/") { LogHelper.AddException(@"主机编号信息未提取到", true); checkClear = true; } if (checkClear) { LogHelper.AddLog(@"***************************************************************", true); if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; return; } JobMethods.GetFixState(_sr, stateIndex, pattern, out needFix, out shouldFix); if (pattern < 2) { startDestiRowIndex = 18; } else if (pattern == 2) { startDestiRowIndex = 17; } else { LogHelper.AddException(@"数据类型无效", true); if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } LogHelper.AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; return; } try { //寻找目标统计文件 temp_fi = JobMethods.SearchForFile(filePath, strType, strMacSerial, strSensorSerial, existFile, out isArchieved); if (temp_fi == null) { if (isArchieved) { //不存在,复制当前记录过去 //1.合成新文件名,按新名另存,定义为temp_fi newName = Util.PathExt.PathCombineFolderFileExtension(output, DataUtility.DataUtility.FileNameCleanName(tempName), fi.Extension); fileText = newName; _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); //2.统计新文件 JobMethods.Statistic(_sr, pattern, Perfect, strCompany, strType, tempName, certId); //3.对新文件进行校验 _sr.Verification(_sr.ExcelWorkbook, true, pattern, out checkClear); } else { //不存在,但搜索到有可疑项,暂不处理 fileText = filePath; } } else if (File.Exists(temp_fi.FullName)) { fileText = temp_fi.FullName; _eu = new ExcelUtility(temp_fi.FullName, out checkClear); if (!checkClear) { LogHelper.AddException(@"Excel文档无法打开", true); _sr.ExcelWorkbook.Saved = true; _eu.ExcelWorkbook.Saved = true; _sr.TryClose(); _eu.TryClose(); LogHelper.AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; return; } _eu.ExcelApp.DisplayAlerts = false; _eu.ExcelApp.AlertBeforeOverwriting = false; JobMethods.CopyData(_sr, stateIndex, _eu, pattern, certId, needFix, shouldFix, startDestiRowIndex, out checkClear); if (checkClear) { _eu.ExcelWorkbook.Save(); _eu.ExcelWorkbook.Saved = true; JobMethods.Statistic(_eu, pattern, Perfect, strCompany, strType, tempName, certId); _eu.ExcelWorkbook.Save(); _eu.ExcelWorkbook.Saved = true; //TODO: 存档合并处校验是否可选(存在,合并入原记录) _eu.Verification(_eu.ExcelWorkbook, false, pattern, out checkClear); } } else { LogHelper.AddException(@"文件不存在:" + temp_fi.FullName, true); fileText = filePath; } } catch (Exception ex) { LogHelper.AddException(@"存档合并时遇到异常:" + ex.Message, true); } finally { if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } if (_eu != null && _eu.ExcelWorkbook != null) { _eu.ExcelWorkbook.Saved = true; _eu.TryClose(); } if (exceptionNum > 0) { problemFilesList.Add(fileText); LogHelper.AddLog(@"***************************************************************", true); exceptionNum = 0; dataerrorNum = 0; } else { if (dataerrorNum > 0) { LogHelper.AddLog(@"该文件只有非致命错误,已经成功处理", true); LogHelper.AddLog(@"***************************************************************", true); exceptionNum = 0; dataerrorNum = 0; } File.Delete(filePath); } } }
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); } } }
public static DataStruct CopyThreeDoseData(ExcelUtility sourceEx, int sourceIndex, ExcelUtility destiEx, int destiIndex, int startSourceRowIndex, int columnIndex, int startDestiRowIndex, string style, int standardRowIndex, string range, int pattern, out bool sc) { double temp_double = 0.0; double diValue = 0.00001; string text; MSExcel.Range rr = null; sc = true; //伦琴 //text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, "D12", out checkClear).Trim().ToLower(); //if (text.EndsWith(@" mr")) //{ // rr = destiEx.GetRange(destiEx.ExcelWorkbook, destiIndex, startDestiRowIndex + 6, columnIndex, out checkClear); // if ((bool)rr.HasFormula) // { // text = rr.FormulaLocal.ToString().Trim() + @"/33.97/0.000258"; // destiEx.WriteFormula(destiEx.ExcelWorkbook, destiIndex, startDestiRowIndex + 6, columnIndex, text, out checkClear); // } //} //规范 rr = sourceEx.GetRange(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(13, columnIndex)); text = rr.Text.ToString().Trim(); rr = destiEx.GetRange(destiEx.ExcelWorkbook, destiIndex, new ExcelPosition(13, columnIndex), new ExcelPosition(13, columnIndex + 1)); rr.Value2 = text; //标准值 if (standardRowIndex > 12) { rr = sourceEx.GetRange(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(standardRowIndex, columnIndex)); text = rr.Text.ToString().Trim(); rr = destiEx.GetRange(destiEx.ExcelWorkbook, destiIndex, new ExcelPosition(16, columnIndex), new ExcelPosition(16, columnIndex + 1)); rr.Value2 = text; if (double.TryParse(text, out temp_double)) { diValue = temp_double; } } //destiEx.WriteValue(destiIndex, 16, columnIndex, text, @"0.00000000_", out checkClear); //复制数据 int count = 0; double daValue = 0; if (style == "") { for (int i = 0; i < 3; i++) { rr = sourceEx.GetRange(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(startSourceRowIndex + i, columnIndex)); if (rr.Value2 == null || string.IsNullOrWhiteSpace(rr.Value2.ToString())) { sourceEx.WriteValue(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(startSourceRowIndex + i, columnIndex), @"/"); sourceEx.ExcelWorkbook.Save(); sourceEx.ExcelWorkbook.Saved = true; text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(startSourceRowIndex + i, columnIndex)); } else { text = rr.Value2.ToString().Trim(); } if (double.TryParse(text, out temp_double) || text == @"/") { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, new ExcelPosition(startDestiRowIndex + i, columnIndex), text); if (text != @"/") { count++; daValue += temp_double; } } else { LogHelper.AddException(@"第" + columnIndex + "列第" + (startSourceRowIndex + i).ToString() + "行不包含有效数据", true); sc = false; } } if (count > 0) { daValue /= count; return new DataStruct(daValue, diValue, range, pattern); } else { sc = false; return null; } } else { for (int i = 0; i < 3; i++) { rr = sourceEx.GetRange(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(startSourceRowIndex + i, columnIndex)); if (rr.Value2 == null || string.IsNullOrWhiteSpace(rr.Value2.ToString())) { sourceEx.WriteValue(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(startSourceRowIndex + i, columnIndex), @"/"); sourceEx.ExcelWorkbook.Save(); sourceEx.ExcelWorkbook.Saved = true; text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(startSourceRowIndex + i, columnIndex)); } else { text = rr.Value2.ToString().Trim(); } if (double.TryParse(text, out temp_double) || text == @"/") { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, new ExcelPosition(startDestiRowIndex + i, columnIndex), text, style); if (text != @"/") { count++; daValue += temp_double; } } else { LogHelper.AddException(@"第" + columnIndex + "列第" + (startSourceRowIndex + i).ToString() + "行不包含有效数据", true); sc = false; } } if (count > 0) { daValue /= count; return new DataStruct(daValue, diValue, range, pattern); } else { sc = false; return null; } } }
public void StandardizeOne(string filePath, JobParameterStruct pS, ref List<string> problemFilesList) { int pattern = pS.DataPattern; string output = pS.OutputFolder; string templateName = pS.DataTemplateFilePath; Dictionary<int, string> exSheets = new Dictionary<int, string>(); Dictionary<int, string> cert = new Dictionary<int, string>(); List<string> sheetsName = new List<string>(); int stateIndex = -1; int noIdNumber = 0; int startDestiRowIndex = -1; bool Perfect; bool needFix = true; bool shouldFix = true; bool hasTemplate = false; bool canDelete = false; string year = ""; string certId; string tempName; string text; string temp_strCompany = "", temp_strType = "", temp_strMacSerial = "", temp_strSensorSerial = ""; string filePathNew = Path.Combine(Path.GetDirectoryName(filePath), Path.GetFileNameWithoutExtension(filePath) + "_处理中" + Path.GetExtension(filePath)); FileInfo temp_fi = null; File.Move(filePath, filePathNew); ExcelUtility _sr = new ExcelUtility(filePathNew, out checkClear); if (!checkClear) { LogHelper.AddException(@"Excel文档无法打开", true); if (_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; MSExcel.Range rr = null; sheetsName = new List<string>(); exSheets = new Dictionary<int, string>(); //第一次循环:获取信息,并规范每页的标签 foreach (MSExcel.Worksheet item in _sr.ExcelWorkbook.Sheets) { if (!hasTemplate && item.Name == @"标准模板") { hasTemplate = true; } //规范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.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } LogHelper.AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; return; } else if (exSheets.Count + noIdNumber > 1) { LogHelper.AddException(@"该文档包含多个数据sheet,默认处理第一个", true); } JobMethods.TypeStandardize(_sr, stateIndex); certId = exSheets[stateIndex]; year = certId.Substring(0, 4); tempName = year + "_" + _sr.GenerateFileName(_sr.ExcelWorkbook, stateIndex, out temp_strCompany, out temp_strType, out temp_strMacSerial, out temp_strSensorSerial, out Perfect); if (temp_strCompany == "") LogHelper.AddException(@"送校单位信息未提取到", true); if (temp_strType == "") LogHelper.AddException(@"仪器型号信息未提取到", true); if ((temp_strSensorSerial.Length + temp_strMacSerial.Length) == 0) LogHelper.AddException(@"主机编号和探头编号信息未提取到", true); if (temp_strMacSerial == "" || temp_strMacSerial == "/") LogHelper.AddException(@"主机编号信息未提取到", true); JobMethods.GetFixState(_sr, stateIndex, pattern, out needFix, out shouldFix); switch (pattern) { case 0: //TODO : 剂量 更改路径 startDestiRowIndex = 18; //templateName = DataUtility.DataUtility.PathClassifiedCombineName(Application.StartupPath + @"\试验记录模板", pattern, @"剂量证书实验记录模板.xlsx"); break; case 1: //TODO : CT 更改路径 startDestiRowIndex = 18; //templateName = DataUtility.DataUtility.PathClassifiedCombineName(Application.StartupPath + @"\试验记录模板", pattern, @"ct证书实验记录模板.xlsx"); break; case 2: //TODO : KV 更改路径 startDestiRowIndex = 17; //templateName = DataUtility.DataUtility.PathClassifiedCombineName(Application.StartupPath + @"\试验记录模板", pattern, @"KV校准证书实验记录模板.xlsx"); break; default: LogHelper.AddException(@"数据类型无效", true); if (_sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } LogHelper.AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; return; } if (File.Exists(templateName)) { tempName = DataUtility.DataUtility.PathRightFileName(output, tempName, ".xlsx", "_new"); File.Copy(templateName, tempName, true); if (!File.Exists(tempName)) { LogHelper.AddException(@"拷贝模板失败", true); if (_sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } LogHelper.AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; return; } else { temp_fi = new FileInfo(tempName); } } else { LogHelper.AddException(@"模板不存在:" + templateName, true); if (_sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } LogHelper.AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; return; } exSheets.Clear(); ExcelUtility _eu = new ExcelUtility(temp_fi.FullName, out checkClear); if (!checkClear) { LogHelper.AddException(@"Excel文档无法打开", true); if (_sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } if (_eu.ExcelWorkbook != null) { _eu.ExcelWorkbook.Saved = true; _eu.TryClose(); } File.Delete(temp_fi.FullName); LogHelper.AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; return; } _eu.ExcelApp.DisplayAlerts = false; _eu.ExcelApp.AlertBeforeOverwriting = false; Object format = MSExcel.XlFileFormat.xlWorkbookDefault; try { int newSheetIndex = -1; JobMethods.CopyData(_sr, stateIndex, _eu, pattern, certId, needFix, shouldFix, startDestiRowIndex, out newSheetIndex, out checkClear); if (newSheetIndex > 0 && checkClear) { text = _eu.GetText(_eu.ExcelWorkbook, newSheetIndex, new ExcelPosition(2, 11)); if (!text.ToLower().EndsWith("dyjl")) { LogHelper.AddException("证书编号不以DYjl开始", true); } if (certId.StartsWith("200")) { _eu.WriteValue(_eu.ExcelWorkbook, newSheetIndex, new ExcelPosition(2, 11), "证书编号:DYjx", ""); } } else { LogHelper.AddException(@"无法找到新合并入数据的位置", true); } _eu.ExcelWorkbook.Save(); } catch (Exception ex) { LogHelper.AddException(@"标准化时遇到异常:" + ex.Message, true); } finally { if (_sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } if (_eu.ExcelWorkbook != null) { _eu.ExcelWorkbook.Saved = true; _eu.TryClose(); } if (exceptionNum > 0) { File.Delete(temp_fi.FullName); LogHelper.AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; } else { canDelete = true; } } if (canDelete) { try { File.Delete(filePathNew); } catch (Exception ex) { LogHelper.AddDataError("已处理完成,但原文件无法正常删除!遇到异常:" + ex.Message, true); } } }
public static void CopyOneData(ExcelUtility sourceEx, int sourceIndex, ExcelUtility destiEx, int destiIndex, ExcelPosition startPosition, ExcelPosition endPosition, string[] pre, string style, out bool sc) { bool checkClear; string text = sourceEx.GetMergeContent(sourceEx.ExcelWorkbook, sourceIndex, startPosition, endPosition, pre, out checkClear); if (text == "") { sc = false; return; } if (style == "") { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, endPosition, text); } else { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, endPosition, text, style); } sc = true; }
public static void CopyThreeKVData(ExcelUtility sourceEx, int sourceIndex, ExcelUtility destiEx, int destiIndex, int startSourceRowIndex, int columnIndex, int startDestiRowIndex, string style, int standardRowIndex, string range, int pattern, out bool sc) { double temp_double = 0.0; string text; MSExcel.Range rr = null; KVCriterion dataCri = KVCriterion.Null; sc = true; //规范 rr = sourceEx.GetRange(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(13, columnIndex)); text = rr.Text.ToString().Trim(); rr = destiEx.GetRange(destiEx.ExcelWorkbook, destiIndex, new ExcelPosition(13, columnIndex)); rr.Value2 = text; //标准值 sourceEx.GetCriterion(sourceEx.ExcelWorkbook, sourceIndex, columnIndex, true, out text, out dataCri); //仪器滤片 if (standardRowIndex > 12) { rr = sourceEx.GetRange(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(standardRowIndex, columnIndex)); if (rr != null) { text = rr.Text.ToString().Trim(); rr = destiEx.GetRange(destiEx.ExcelWorkbook, destiIndex, new ExcelPosition(15, columnIndex)); rr.Value2 = text; } else { rr = destiEx.GetRange(destiEx.ExcelWorkbook, destiIndex, new ExcelPosition(15, columnIndex)); rr.Value2 = @"/"; } } else { rr = destiEx.GetRange(destiEx.ExcelWorkbook, destiIndex, new ExcelPosition(15, columnIndex)); rr.Value2 = @"/"; } ExcelPosition position; //复制数据 if (style == "") { for (int i = 0; i < 3; i++) { position = new ExcelPosition(startSourceRowIndex + i, columnIndex); rr = sourceEx.GetRange(sourceEx.ExcelWorkbook, sourceIndex, position); if (rr.Value2 == null || string.IsNullOrWhiteSpace(rr.Value2.ToString())) { sourceEx.WriteValue(sourceEx.ExcelWorkbook, sourceIndex, position, @"/"); sourceEx.ExcelWorkbook.Save(); sourceEx.ExcelWorkbook.Saved = true; text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, position); } else { text = rr.Value2.ToString().Trim(); } if (double.TryParse(text, out temp_double) || text == @"/") { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, position, text); } else { LogHelper.AddException(position.PositionString + "不包含有效数据", true); sc = false; } } } else { for (int i = 0; i < 3; i++) { position = new ExcelPosition(startSourceRowIndex + i, columnIndex); rr = sourceEx.GetRange(sourceEx.ExcelWorkbook, sourceIndex, position); if (rr.Value2 == null || string.IsNullOrWhiteSpace(rr.Value2.ToString())) { sourceEx.WriteValue(sourceEx.ExcelWorkbook, sourceIndex, position, @"/"); sourceEx.ExcelWorkbook.Save(); sourceEx.ExcelWorkbook.Saved = true; text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, position); } else { text = rr.Value2.ToString().Trim(); } if (double.TryParse(text, out temp_double) || text == @"/") { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, position, text, style); } else { LogHelper.AddException(position.PositionString + "不包含有效数据", true); sc = false; } } } }
public static void CopyOneData(ExcelUtility sourceEx, int sourceIndex, ExcelUtility destiEx, int destiIndex, ExcelPosition position, string style, bool checkDouble, out bool sc) { double temp_double = 0.0; string text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, position); bool isDouble = double.TryParse(text, out temp_double); if (text == "" || !isDouble) { sc = false; return; } if (style == "") { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, position, text); } else { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, position, text, style); } sc = true; }
public static void CopyOneData(ExcelUtility sourceEx, int sourceIndex, ExcelUtility destiEx, int destiIndex, ExcelPosition position, string defaultValue, string style) { string text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, position); if (text == "") { sourceEx.WriteValue(sourceEx.ExcelWorkbook, sourceIndex, position, defaultValue); } else if (style == "") { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, position, text); } else { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, position, text, style); } }
/// <summary> /// 找模板页,加入 /// /// 搬运内容: /// 01、L2 02 12 证书编号 /// 02、B4 -D4 04 02-04 送校单位 /// 03、F4 04 06 联系地址 /// 04、B5 05 02 仪器名称 /// 05、F5 05 06 型号 /// 06、H5 05 08 编号 /// 07、J5 05 10 厂家 /// 08、L5 05 12 电离室号 /// 09、K7 07 11 温度 /// 10、M7 07 13 湿度 /// 11、J8 08 10 气压 /// 12、D12-M12 12 04-13 量程 /// 13、D18 18 04 数据 /// 14、F18 18 06 数据 /// 15、H18 18 08 数据 /// 16、J18 18 10 数据 /// 17、L18 18 13 数据 /// 18、D19 19 04 数据 /// 19、F19 19 06 数据 /// 20、H19 19 08 数据 /// 21、J19 19 10 数据 /// 22、L19 19 12 数据 /// 23、D20 20 04 数据 /// 24、F20 20 06 数据 /// 25、H20 20 08 数据 /// 26、J20 20 10 数据 /// 27、L20 20 12 数据 /// 28、G31 31 07 记录者签名 /// 29、I31 31 09 校对者签名 /// 30、K31 31 11 日期 /// </summary> /// <param name="sourceEx"></param> /// <param name="sourceIndex"></param> /// <param name="destiEx"></param> public static void CopyData(ExcelUtility sourceEx, int sourceIndex, ExcelUtility destiEx, int pattern, string certIdori, bool needFix, bool shouldFix, int startDestiRowIndex, out int newSheetIndex, out bool success) { bool noNeed = false; int templateIndex = -1; int startSourceRowIndex = -1; int destiIndex = -1; MSExcel.Range rr = null; MSExcel.Worksheet ws1 = null; bool checkClear; Dictionary<int, string> exSheets = new Dictionary<int, string>(); string temp; string text = ""; string certId = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition("L2")); if (!IsValidCertificate(certId)) { LogHelper.AddException(@"无法提取到证书编号", true); success = false; newSheetIndex = -1; return; } foreach (MSExcel.Worksheet item in destiEx.ExcelWorkbook.Sheets) { if (item.Name == "统计") { destiIndex = item.Index; } else if (item.Name == @"标准模板") { //找到模板页 templateIndex = item.Index; } else if (item.Name.Contains(@"标准模板")) { LogHelper.AddDataError(@"第" + item.Index + "页发现多余的标准模板", true); } else { temp = destiEx.GetText(destiEx.ExcelWorkbook, item.Index, new ExcelPosition("L2")); if (temp.StartsWith(@"20") && (temp.Length == 9 || temp.Length == 10)) { //找到有证书编号的数据页 if (item.Name == certId) { if (FormOperator.MessageBox_Show_YesNo("在历史数据记录的Excel中已发现了证书编号为" + certId + "的页面,是否覆盖?选择是,进行覆盖。选择否,停止对本Excel的处理", "是否覆盖")) { ws1 = item; } else { LogHelper.AddException(@"要合并入的数据已存在于第" + item.Index + "页", true); newSheetIndex = -1; success = false; return; } } else { exSheets.Add(item.Index, temp); } } } } if (!noNeed) { if (templateIndex == -1) { LogHelper.AddException(@"找不到数据的标准模板", true); success = false; newSheetIndex = -1; return; } else { //有模板页。没找到参考证书编号页 if (ws1 == null) { ws1 = (MSExcel.Worksheet)destiEx.ExcelWorkbook.Sheets[templateIndex]; if (exSheets.Count > 0) { //有有效数据页 if (destiIndex < 1) { //没有统计页,把有效数据页里最前面的序号赋给destiIndex foreach (int item in exSheets.Keys) { if (destiIndex < 1 || destiIndex > item) { destiIndex = item; } } } foreach (int item in exSheets.Keys) { if (DataUtility.DataUtility.LargerThan(certId, exSheets[item]) && destiIndex < item) { //在所有比参考编号小的页面里,挑一个最靠后的序号给destiIndex destiIndex = item; } } //在destiIndex右侧复制模板页 ws1.Copy(Type.Missing, destiEx.ExcelWorkbook.Sheets[destiIndex]); //把新复制的模板页赋给ws1 ws1 = (MSExcel.Worksheet)destiEx.ExcelWorkbook.Sheets[destiIndex + 1]; } else { //没有有效数据页时,在模板页左侧复制模板页 ws1.Copy(destiEx.ExcelWorkbook.Sheets[templateIndex], Type.Missing); //把原来模板页,现在的模板复制页的位置给了ws1 ws1 = (MSExcel.Worksheet)destiEx.ExcelWorkbook.Sheets[templateIndex]; } if (!ws1.Name.Contains(@"标准模板")) { LogHelper.AddException(@"标准模板复制出错", true); success = false; newSheetIndex = -1; return; } if (!exSheets.ContainsValue(certId)) { ws1.Name = certId; } } newSheetIndex = ws1.Index; //确定原始数据的数据行 for (int i = 15; i < 22; i++) { text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(i, 3)); if (text == "1") { text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(i + 1, 3)); if (text == "2") { text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(i + 2, 3)); if (text == "3") { startSourceRowIndex = i; break; } } } } if (startSourceRowIndex == -1) { LogHelper.AddException(@"找不到原始数据所在的行", true); success = false; newSheetIndex = -1; return; } //拷贝数据 CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(2, 12), "@", out checkClear); if (!checkClear) LogHelper.AddException(@"《证书编号》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(4, 1), new ExcelPosition(4, 2), new string[] { @"送校单位:", @"单位名称:" }, "@", out checkClear); if (!checkClear) LogHelper.AddException(@"《送校单位》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(4, 5), new ExcelPosition(4, 6), @"联系地址:", "@", out checkClear); //if (!checkClear) AddException(@"《联系地址》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(5, 1), new ExcelPosition(5, 2), @"仪器名称:", "@", out checkClear); if (!checkClear) LogHelper.AddException(@"《仪器名称》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(5, 5), new ExcelPosition(5, 6), @"型号:", "@", out checkClear); if (!checkClear) LogHelper.AddException(@"《型号》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(5, 7), new ExcelPosition(5, 8), new string[] { @"主机编号:", @"编号:" }, "@", out checkClear); if (!checkClear) LogHelper.AddException(@"《主机编号》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(5, 9), new ExcelPosition(5, 10), @"厂家:", "@", out checkClear); if (!checkClear) LogHelper.AddException(@"《厂家》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(5, 11), new ExcelPosition(5, 12), new string[] { @"探测器编号:", "电离室号:", "探测器号:" }, "@", out checkClear); if (!checkClear) LogHelper.AddException(@"《探测器编号》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(31, 7), "", out checkClear); //if (!checkClear) AddException(@"《记录者》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(31, 9), "", out checkClear); //if (!checkClear) AddException(@"《校对者》数据复制错误", true); CopyDate(sourceEx, sourceIndex, destiEx, ws1.Index, out checkClear); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(7, 11), "0.000", out checkClear); if (needFix && !checkClear) LogHelper.AddException(@"《温度》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(7, 13), "0.0%", out checkClear); if (needFix && !checkClear) LogHelper.AddException(@"《湿度》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(8, 10), "", out checkClear); if (needFix && !checkClear) LogHelper.AddException(@"《气压》数据复制错误", true); rr = destiEx.GetRange(destiEx.ExcelWorkbook, ws1.Index, new ExcelPosition("M8")); if (needFix) { destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, new ExcelPosition(8, 13), "修正"); } else { //电离室->半导体 rr = destiEx.GetRange(destiEx.ExcelWorkbook, ws1.Index, new ExcelPosition("L8")); rr.FormulaLocal = ""; rr.Formula = ""; rr.FormulaArray = ""; destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, new ExcelPosition(8, 12), "1.000000", "@"); if (shouldFix) { destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, new ExcelPosition(8, 13), "自修正"); } else { destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, new ExcelPosition(8, 13), "不修正"); } } List<DataStruct> dataStructList = new List<DataStruct>(); DataStruct dataStruct; int standardRowIndex = -1; string keyword = "标准"; switch (pattern) { case 2: keyword = "滤片"; break; } for (int p = 13; p < 18; p++) { text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(p, 1)) + sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(p, 2)) + sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(p, 3)); if (text.ToLower().Contains(keyword)) { standardRowIndex = p; break; } } ExcelPosition dataRangePosition = new ExcelPosition(12, 13); ExcelPosition dataDistancePosition = new ExcelPosition(6, 13); switch (pattern) { case 0: //Dose CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(27, 13), "@", out checkClear); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(12, 4), "", out checkClear); if (!checkClear) LogHelper.AddException(@"《量程》数据复制错误", true); text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(12, 4)); dataStructList.Add(CopyThreeDoseData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 4, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear)); dataStructList.Add(CopyThreeDoseData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 6, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear)); dataStructList.Add(CopyThreeDoseData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 8, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear)); dataStructList.Add(CopyThreeDoseData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 10, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear)); dataStructList.Add(CopyThreeDoseData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 12, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear)); text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(12, 12)); if (text.StartsWith("单位")) { CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, dataRangePosition, "", out checkClear); if (!checkClear) LogHelper.AddException(@"《单位》数据复制错误", true); } else { dataStruct = DataStruct.CalDataRange(dataStructList, sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(12, 4)), pattern, out success); switch (dataStruct.DataRanges) { case DataRange.cGy: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataRangePosition, "cGy"); break; case DataRange.mGy: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataRangePosition, "mGy"); break; case DataRange.mR: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataRangePosition, "mR"); break; case DataRange.R: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataRangePosition, "R"); break; case DataRange.uGy: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataRangePosition, "μGy"); break; case DataRange.Unknown: LogHelper.AddException("无法判断数据单位", true); break; } switch (dataStruct.Distance) { case Distance.d1: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataDistancePosition, "1.0m"); break; case Distance.d1_5: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataDistancePosition, "1.5m"); break; case Distance.Unknown: LogHelper.AddException("无法获取标准值,判断测试距离", true); break; } } break; case 1: //CT CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(27, 13), "@", out checkClear); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(12, 4), "", out checkClear); if (!checkClear) LogHelper.AddException(@"《量程》数据复制错误", true); text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(12, 4)); dataStructList.Add(CopyThreeCTData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 4, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear)); dataStructList.Add(CopyThreeCTData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 6, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear)); dataStructList.Add(CopyThreeCTData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 8, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear)); dataStructList.Add(CopyThreeCTData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 10, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear)); dataStructList.Add(CopyThreeCTData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 12, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear)); text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(12, 12)); if (text.StartsWith("单位")) { CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, dataRangePosition, "", out checkClear); if (!checkClear) LogHelper.AddException(@"《单位》数据复制错误", true); } else { dataStruct = DataStruct.CalDataRange(dataStructList, sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(12, 4)), pattern, out success); switch (dataStruct.DataRanges) { case DataRange.mGycm: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataRangePosition, "mGycm"); break; case DataRange.mGy: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataRangePosition, "mGy"); break; case DataRange.cGy: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataRangePosition, "cGy"); break; case DataRange.mR: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataRangePosition, "mR"); break; case DataRange.R: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataRangePosition, "R"); break; case DataRange.uGy: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataRangePosition, "μGy"); break; case DataRange.cGycm: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataRangePosition, "cGycm"); break; case DataRange.Rcm: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataRangePosition, "Rcm"); break; case DataRange.Unknown: LogHelper.AddException("无法判断数据单位", true); break; } switch (dataStruct.Distance) { case Distance.d1: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataDistancePosition, "1.0m"); break; case Distance.d1_5: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataDistancePosition, "1.5m"); break; case Distance.Unknown: LogHelper.AddException("无法获取标准值,判断测试距离", true); break; } } //CopyCTData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, startDestiRowIndex, "", out checkClear); break; case 2: //KV CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(28, 13), "@", out checkClear); //if (!checkClear) AddException(@"《备注》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(12, 4), @"/", ""); if (!checkClear) LogHelper.AddException(@"《量程》数据复制错误", true); text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(12, 4)); CopyThreeKVData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 4, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear); CopyThreeKVData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 6, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear); CopyThreeKVData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 8, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear); CopyThreeKVData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 10, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear); CopyThreeKVData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 12, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear); //text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, 12, 12, out checkClear).Trim(); //if (text.StartsWith("单位")) //{ // //CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 12, 13, "", out checkClear); // CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 12, 4, "", out checkClear); // if (!checkClear) AddException(@"《量程》数据复制错误", true); // if (!checkClear) AddException(@"《单位》数据复制错误", true); //} //else //{ // destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, 12, 13, "KV", out checkClear); //} //text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, 12, 10, out checkClear).Trim(); //if (text.StartsWith("测试类型")) //{ // CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 12, 11, "", out checkClear); // if (!checkClear) AddException(@"《测试类型》数据复制错误", true); //} //else //{ // text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, 5, 6, out checkClear).Trim().ToLower(); // switch (text) // { // case "iba": // keyword = "ppV"; // break; // case "radcal": // keyword = "kVp"; // break; // default: // keyword = @""; // break; // } // destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, 12, 11, keyword, out checkClear); //} break; default: break; } } } else { newSheetIndex = 1; } success = true; }
public static void CopyOneData(ExcelUtility sourceEx, int sourceIndex, ExcelUtility destiEx, int destiIndex, ExcelPosition position, string style, out bool sc) { string text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, position); if (text == "") { sc = false; return; } if (style == "") { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, position, text); } else { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, position, text, style); } sc = true; }
/// <summary> /// /// </summary> /// <param name="sourceEx"></param> /// <param name="stateIndex"></param> public static void TypeStandardize(ExcelUtility sourceEx, int stateIndex) { bool checkClear; string str = sourceEx.GetMergeContent(sourceEx.ExcelWorkbook, stateIndex, new ExcelPosition(5, 5), new ExcelPosition(5, 6), @"型号:", out checkClear).Trim().ToLower(); bool DoNotHave = true; if (str.Contains(@"unfors") || sourceEx.GetMergeContent(sourceEx.ExcelWorkbook, stateIndex, new ExcelPosition(5, 9), new ExcelPosition(5, 10), @"厂家:", out checkClear).Trim().ToLower().Contains(@"unfors")) { sourceEx.WriteValue(sourceEx.ExcelWorkbook, stateIndex, new ExcelPosition(5, 5), @"型号:"); sourceEx.WriteValue(sourceEx.ExcelWorkbook, stateIndex, new ExcelPosition(5, 6), @"Xi"); sourceEx.ExcelWorkbook.Save(); sourceEx.ExcelWorkbook.Saved = true; str = sourceEx.GetMergeContent(sourceEx.ExcelWorkbook, stateIndex, new ExcelPosition(5, 5), new ExcelPosition(5, 6), @"型号:", out checkClear).Trim().ToLower(); } if (str.Contains(@"baracuda")) { sourceEx.WriteValue(sourceEx.ExcelWorkbook, stateIndex, new ExcelPosition(5, 5), @"型号:"); sourceEx.WriteValue(sourceEx.ExcelWorkbook, stateIndex, new ExcelPosition(5, 6), @"Barracuda"); sourceEx.ExcelWorkbook.Save(); sourceEx.ExcelWorkbook.Saved = true; str = sourceEx.GetMergeContent(sourceEx.ExcelWorkbook, stateIndex, new ExcelPosition(5, 5), new ExcelPosition(5, 6), @"型号:", out checkClear).Trim().ToLower(); } if (str.Contains(@"prianha")) { sourceEx.WriteValue(sourceEx.ExcelWorkbook, stateIndex, new ExcelPosition(5, 5), @"型号:"); sourceEx.WriteValue(sourceEx.ExcelWorkbook, stateIndex, new ExcelPosition(5, 6), @"Piranha"); sourceEx.ExcelWorkbook.Save(); sourceEx.ExcelWorkbook.Saved = true; str = sourceEx.GetMergeContent(sourceEx.ExcelWorkbook, stateIndex, new ExcelPosition(5, 5), new ExcelPosition(5, 6), @"型号:", out checkClear).Trim().ToLower(); } foreach (string item in TestedInstrument.AllTypes) { if (item.ToLower() == str) { DoNotHave = false; break; } } if (DoNotHave) { LogHelper.AddException(@"仪器类型可能出现手误", true); LogHelper.AddLog("错误47", " 仪器类型:" + str, true); } }
/// <summary> /// 统计,使用固定的规范列表 /// </summary> /// <param name="eu"></param> /// <param name="pattern"></param> /// <param name="Perfect"></param> /// <param name="strCompany"></param> /// <param name="strType"></param> /// <param name="tempName"></param> /// <returns></returns> public static bool Statistic(ExcelUtility eu, int pattern, bool Perfect, string strCompany, string strType, string tempName, string certstr) { int stateIndex = -1; int lineIndex = 0; int limitPosition = 24; int newline = -1; bool needInitialState = true; bool checkClear; bool firstTime = true; bool canPass = true, canPass1 = false; string insNumber = ""; Dictionary<int, string> exSheets = null; Dictionary<int, string> cert = new Dictionary<int, string>(); Dictionary<NormalDoseCriterion, int> existDoseCriList = new Dictionary<NormalDoseCriterion, int>() { { NormalDoseCriterion.RQR2_40 , NormalDoseCriterion.RQR2_40.Column }, { NormalDoseCriterion.RQR3_50 , NormalDoseCriterion.RQR3_50.Column }, { NormalDoseCriterion.RQR4_60 , NormalDoseCriterion.RQR4_60.Column }, { NormalDoseCriterion.RQR5_70 , NormalDoseCriterion.RQR5_70.Column }, { NormalDoseCriterion.RQR6_80 , NormalDoseCriterion.RQR6_80.Column }, { NormalDoseCriterion.RQR7_90 , NormalDoseCriterion.RQR7_90.Column }, { NormalDoseCriterion.RQR8_100 , NormalDoseCriterion.RQR8_100.Column }, { NormalDoseCriterion.RQR9_120 , NormalDoseCriterion.RQR9_120.Column }, { NormalDoseCriterion.RQR_140 , NormalDoseCriterion.RQR_140.Column }, { NormalDoseCriterion.RQR10_150, NormalDoseCriterion.RQR10_150.Column} }; Dictionary<KVCriterion, int> existKVCriList = new Dictionary<KVCriterion, int>() { { KVCriterion.RQR2_40 , KVCriterion.RQR2_40.Column }, { KVCriterion.RQR3_50 , KVCriterion.RQR3_50.Column }, { KVCriterion.RQR4_60 , KVCriterion.RQR4_60.Column }, { KVCriterion.RQR5_70 , KVCriterion.RQR5_70.Column }, { KVCriterion.RQR6_80 , KVCriterion.RQR6_80.Column }, { KVCriterion.RQR7_90 , KVCriterion.RQR7_90.Column }, { KVCriterion.RQR8_100 , KVCriterion.RQR8_100.Column }, { KVCriterion.RQR9_120 , KVCriterion.RQR9_120.Column }, { KVCriterion.RQR_140 , KVCriterion.RQR_140.Column }, { KVCriterion.RQR10_150, KVCriterion.RQR10_150.Column} }; //1.初始化:规范统计sheet结构,写入固定内容 stateIndex = eu.InitialStatisticSheet(eu.ExcelWorkbook, out exSheets, out needInitialState, out checkClear); if (!checkClear) { return false; } switch (pattern) { case 0: if (needInitialState) { eu.WriteStateTitle(eu.ExcelWorkbook, pattern, stateIndex, existDoseCriList, limitPosition); //写入送校单位 eu.WriteValue(eu.ExcelWorkbook, stateIndex, new ExcelPosition(2, 2), strCompany); //写入仪器名称 eu.WriteValue(eu.ExcelWorkbook, stateIndex, new ExcelPosition(3, 2), strType); } //1.搬运数据:先根据年份排序,然后复制数据 int[] indexDose = new int[exSheets.Count]; foreach (int item in exSheets.Keys) { indexDose[lineIndex] = item; lineIndex++; } DataUtility.DataUtility.QuickSort(indexDose, exSheets, 0, indexDose.Length - 1); lineIndex = 8; for (int i = 0; i < exSheets.Count; i++) { cert.Add(lineIndex, exSheets[indexDose[i]]); eu.CopyDoseOneYearData(eu.ExcelWorkbook, indexDose[i], stateIndex, lineIndex, exSheets[indexDose[i]], limitPosition, existDoseCriList, out insNumber); if (exSheets[indexDose[i]] == certstr) { newline = lineIndex; } if (firstTime && Perfect) { eu.DesiredName = tempName; firstTime = false; } lineIndex++; } //2.分析数据 if (exSheets.Count > 1) { eu.WriteValue(eu.ExcelWorkbook, stateIndex, new ExcelPosition(lineIndex, 1), @"重复性"); eu.ExcelWorkbook.Save(); eu.ExcelWorkbook.Saved = true; //计算平均值和年稳定性 canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 4, cert, "RQR2(40kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 6, cert, "RQR3(50kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 8, cert, "RQR4(60kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 10, cert, "RQR5(70kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 12, cert, "RQR6(80kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 14, cert, "RQR7(90kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 16, cert, "RQR8(100kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 18, cert, "RQR9(120kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 20, cert, "RQR(140kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 22, cert, "RQR10(150kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; } break; case 1: //TODO: CT统计代码 if (needInitialState) { eu.WriteStateTitle(eu.ExcelWorkbook, pattern, stateIndex, existDoseCriList, limitPosition); //写入送校单位 eu.WriteValue(eu.ExcelWorkbook, stateIndex, new ExcelPosition(2, 2), strCompany); //写入仪器名称 eu.WriteValue(eu.ExcelWorkbook, stateIndex, new ExcelPosition(3, 2), strType); } //1.搬运数据:先根据年份排序,然后复制数据 int[] indexCT = new int[exSheets.Count]; foreach (int item in exSheets.Keys) { indexCT[lineIndex] = item; lineIndex++; } DataUtility.DataUtility.QuickSort(indexCT, exSheets, 0, indexCT.Length - 1); lineIndex = 8; for (int i = 0; i < exSheets.Count; i++) { cert.Add(lineIndex, exSheets[indexCT[i]]); //TODO:复制数据格式 eu.CopyCTOneYearData(eu.ExcelWorkbook, indexCT[i], stateIndex, lineIndex, exSheets[indexCT[i]], limitPosition, existDoseCriList, out insNumber); if (exSheets[indexCT[i]] == certstr) { newline = lineIndex; } if (firstTime && Perfect) { eu.DesiredName = tempName; firstTime = false; } lineIndex++; } //2.分析数据 if (exSheets.Count > 1) { eu.WriteValue(eu.ExcelWorkbook, stateIndex, new ExcelPosition(lineIndex, 1), @"重复性"); eu.ExcelWorkbook.Save(); eu.ExcelWorkbook.Saved = true; //计算平均值和年稳定性 canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 4, cert, "RQR2(40kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 6, cert, "RQR3(50kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 8, cert, "RQR4(60kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 10, cert, "RQR5(70kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 12, cert, "RQR6(80kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 14, cert, "RQR7(90kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 16, cert, "RQR8(100kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 18, cert, "RQR9(120kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 20, cert, "RQR(140kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 22, cert, "RQR10(150kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; } break; case 2: //TODO:KV统计代码 limitPosition = 44; if (needInitialState) { eu.WriteStateTitle(eu.ExcelWorkbook, pattern, stateIndex, existKVCriList, limitPosition); //写入送校单位 eu.WriteValue(eu.ExcelWorkbook, stateIndex, new ExcelPosition(2, 2), strCompany); //写入仪器名称 eu.WriteValue(eu.ExcelWorkbook, stateIndex, new ExcelPosition(3, 2), strType); } //1.搬运数据:先根据年份排序,然后复制数据 int[] indexKV = new int[exSheets.Count]; foreach (int item in exSheets.Keys) { indexKV[lineIndex] = item; lineIndex++; } DataUtility.DataUtility.QuickSort(indexKV, exSheets, 0, indexKV.Length - 1); lineIndex = 8; for (int i = 0; i < exSheets.Count; i++) { cert.Add(lineIndex, exSheets[indexKV[i]]); //TODO:复制数据格式 if (exSheets[indexKV[i]] == certstr) { eu.CopyKVOneYearData(eu.ExcelWorkbook, indexKV[i], stateIndex, lineIndex, exSheets[indexKV[i]], limitPosition, existKVCriList, true, out insNumber); } else { eu.CopyKVOneYearData(eu.ExcelWorkbook, indexKV[i], stateIndex, lineIndex, exSheets[indexKV[i]], limitPosition, existKVCriList, false, out insNumber); } if (exSheets[indexKV[i]] == certstr) { newline = lineIndex; } if (firstTime && Perfect) { eu.DesiredName = tempName; firstTime = false; } lineIndex++; } //2.分析数据 eu.ExcelWorkbook.Save(); eu.ExcelWorkbook.Saved = true; //计算平均值和年稳定性 canPass1 = eu.StatisticsKVOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 4, cert, "RQR2(40kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsKVOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 8, cert, "RQR3(50kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsKVOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 12, cert, "RQR4(60kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsKVOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 16, cert, "RQR5(70kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsKVOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 20, cert, "RQR6(80kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsKVOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 24, cert, "RQR7(90kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsKVOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 28, cert, "RQR8(100kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsKVOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 32, cert, "RQR9(120kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsKVOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 36, cert, "RQR(140kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsKVOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 40, cert, "RQR10(150kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; break; } eu.ExcelWorkbook.Save(); return canPass; }
/// <summary> /// 统计,采集真实的规范列表 /// </summary> /// <param name="eu"></param> /// <param name="pattern"></param> /// <returns></returns> public static bool Statistic(ExcelUtility eu, int pattern) { int stateIndex = -1; int lineIndex = 0; int limitPosition = 14; bool needInitialState = true; bool checkClear; bool firstTime = true; bool canPass = true, canPass1 = false; string insNumber = ""; Dictionary<int, string> exSheets = null; Dictionary<int, string> cert = new Dictionary<int, string>(); Dictionary<NormalDoseCriterion, int> existCriList = new Dictionary<NormalDoseCriterion, int>(); //1.初始化:规范统计sheet结构,写入固定内容 stateIndex = eu.InitialStatisticSheet(eu.ExcelWorkbook, out exSheets, out needInitialState, out existCriList, out limitPosition, out checkClear); if (!checkClear) { return false; } if (!needInitialState) { eu.WriteStateTitle(eu.ExcelWorkbook, pattern, stateIndex, existCriList, limitPosition); switch (pattern) { case 0: //1.搬运数据:先根据年份排序,然后复制数据 int[] indexDose = new int[exSheets.Count]; lineIndex = 0; foreach (int item in exSheets.Keys) { indexDose[lineIndex] = item; lineIndex++; } DataUtility.DataUtility.QuickSort(indexDose, exSheets, 0, indexDose.Length - 1); lineIndex = 8; for (int i = 0; i < exSheets.Count; i++) { cert.Add(lineIndex, exSheets[indexDose[i]]); eu.CopyDoseOneYearData(eu.ExcelWorkbook, indexDose[i], stateIndex, lineIndex, exSheets[indexDose[i]], limitPosition, existCriList, out insNumber); if (firstTime) { firstTime = false; } lineIndex++; } //2.分析数据 if (exSheets.Count > 1) { eu.WriteValue(eu.ExcelWorkbook, stateIndex, new ExcelPosition(lineIndex, 1), @"重复性"); eu.ExcelWorkbook.Save(); eu.ExcelWorkbook.Saved = true; //计算平均值和年稳定性 canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 4, cert, "RQR2(40kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 6, cert, "RQR3(50kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 8, cert, "RQR4(60kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 10, cert, "RQR5(70kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 12, cert, "RQR6(80kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 14, cert, "RQR7(90kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 16, cert, "RQR8(100kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 18, cert, "RQR9(120kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 20, cert, "RQR(140kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 22, cert, "RQR10(150kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; } break; case 1: //TODO: CT统计代码 //1.搬运数据:先根据年份排序,然后复制数据 //indexCT[数字序号]=页序号 //exSheets[页序号]=证书编号 int[] indexCT = new int[exSheets.Count]; lineIndex = 0; foreach (int item in exSheets.Keys) { indexCT[lineIndex] = item; lineIndex++; } DataUtility.DataUtility.QuickSort(indexCT, exSheets, 0, indexCT.Length - 1); lineIndex = 8; for (int i = 0; i < exSheets.Count; i++) { cert.Add(lineIndex, exSheets[indexCT[i]]); //TODO:复制数据格式 eu.CopyCTOneYearData(eu.ExcelWorkbook, indexCT[i], stateIndex, i + 8, exSheets[indexCT[i]], limitPosition, existCriList, out insNumber); if (firstTime) { firstTime = false; } lineIndex++; } //2.分析数据 if (exSheets.Count > 1) { eu.WriteValue(eu.ExcelWorkbook, stateIndex, new ExcelPosition(lineIndex, 1), @"重复性"); eu.ExcelWorkbook.Save(); eu.ExcelWorkbook.Saved = true; //计算平均值和年稳定性 canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 4, cert, "RQR2(40kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 6, cert, "RQR3(50kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 8, cert, "RQR4(60kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 10, cert, "RQR5(70kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 12, cert, "RQR6(80kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 14, cert, "RQR7(90kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 16, cert, "RQR8(100kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 18, cert, "RQR9(120kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 20, cert, "RQR(140kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 22, cert, "RQR10(150kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; } break; case 2: //TODO:KV统计代码 break; } } eu.ExcelWorkbook.Save(); return canPass; }
public static void CopyDate(ExcelUtility sourceEx, int sourceIndex, ExcelUtility destiEx, int destiIndex, out bool sc) { string text = ""; for (int i = 33; i > 26; i--) { text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(i, 10)) + sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(i, 11)) + sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(i, 12)) + sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(i, 13)); if (text != "") { break; } } if (text == "") { sc = false; LogHelper.AddException(@"《日期》数据复制错误:" + text, true); return; } else { text = text.Replace(@" ", "").Replace(@"年", " 年 ").Replace(@"月", " 月 ").Replace(@"日", " 日"); } destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, new ExcelPosition(31, 11), text); sc = true; }
public static DataStruct CopyThreeCTData(ExcelUtility sourceEx, int sourceIndex, ExcelUtility destiEx, int destiIndex, int startSourceRowIndex, int columnIndex, int startDestiRowIndex, string style, int standardRowIndex, string range, int pattern, out bool sc) { bool conti = true; double temp_double = 0.0; double diValue = 0.00001; string text; MSExcel.Range rr = null; NormalDoseCriterion dataCri = NormalDoseCriterion.Null; sc = true; conti = TestFlag(sourceEx, sourceIndex, startSourceRowIndex, columnIndex); if (conti) { //确定数据的规范 if (sourceEx.GetCriterion(sourceEx.ExcelWorkbook, sourceIndex, columnIndex, true, out text, out dataCri)) { //规范 rr = sourceEx.GetRange(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(13, columnIndex)); text = rr.Text.ToString().Trim(); //rr = destiEx.GetRange(destiEx.ExcelWorkbook, destiIndex, DataUtility.DataUtility.PositionString(13, sourceEx.GetColumnByCriterion(dataCri, 2)), DataUtility.DataUtility.PositionString(13, sourceEx.GetColumnByCriterion(dataCri, 2) + 1), out checkClear); rr = destiEx.GetRange(destiEx.ExcelWorkbook, destiIndex, new ExcelPosition(13, columnIndex), new ExcelPosition(13, columnIndex + 1)); rr.Value2 = text; //电压写入14行 destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, new ExcelPosition(14, columnIndex), dataCri.Voltage); //半值层写入15行 destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, new ExcelPosition(15, columnIndex), dataCri.HalfValueLayer); //标准值 if (standardRowIndex > 12) { rr = sourceEx.GetRange(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(standardRowIndex, columnIndex)); text = rr.Text.ToString().Trim(); //rr = destiEx.GetRange(destiEx.ExcelWorkbook, destiIndex, DataUtility.DataUtility.PositionString(16, sourceEx.GetColumnByCriterion(dataCri, 2)), DataUtility.DataUtility.PositionString(16, sourceEx.GetColumnByCriterion(dataCri, 2) + 1), out checkClear); rr = destiEx.GetRange(destiEx.ExcelWorkbook, destiIndex, new ExcelPosition(16, columnIndex), new ExcelPosition(16, columnIndex + 1)); rr.Value2 = text; if (double.TryParse(text, out temp_double)) { diValue = temp_double; } //destiEx.WriteValue(destiIndex, 16, columnIndex, text, @"0.00000000_", out checkClear); } //复制数据 int count = 0; double daValue = 0; if (style == "") { for (int i = 0; i < 3; i++) { rr = sourceEx.GetRange(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(startSourceRowIndex + i, columnIndex)); if (rr.Value2 == null || string.IsNullOrWhiteSpace(rr.Value2.ToString())) { sourceEx.WriteValue(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(startSourceRowIndex + i, columnIndex), @"/"); sourceEx.ExcelWorkbook.Save(); sourceEx.ExcelWorkbook.Saved = true; text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(startSourceRowIndex + i, columnIndex)); } else { text = rr.Value2.ToString().Trim(); } if (double.TryParse(text, out temp_double) || text == @"/") { //destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, startDestiRowIndex + i, sourceEx.GetColumnByCriterion(dataCri, 2), text, out checkClear); destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, new ExcelPosition(startDestiRowIndex + i, columnIndex), text); if (text != @"/") { count++; daValue += temp_double; } } else { LogHelper.AddException(@"第" + columnIndex + "列第" + (startSourceRowIndex + i).ToString() + "行不包含有效数据", true); sc = false; } } if (count > 0) { daValue /= count; return new DataStruct(daValue, diValue, range, pattern); } } else { for (int i = 0; i < 3; i++) { rr = sourceEx.GetRange(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(startSourceRowIndex + i, columnIndex)); if (rr.Value2 == null || string.IsNullOrWhiteSpace(rr.Value2.ToString())) { sourceEx.WriteValue(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(startSourceRowIndex + i, columnIndex), @"/"); sourceEx.ExcelWorkbook.Save(); sourceEx.ExcelWorkbook.Saved = true; text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(startSourceRowIndex + i, columnIndex)); } else { text = rr.Value2.ToString().Trim(); } if (double.TryParse(text, out temp_double) || text == @"/") { //destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, startDestiRowIndex + i, sourceEx.GetColumnByCriterion(dataCri, 2), text, "", out checkClear); destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, new ExcelPosition(startDestiRowIndex + i, columnIndex), text, style); if (text != @"/") { count++; daValue += temp_double; } } else { LogHelper.AddException(@"第" + columnIndex + "列第" + (startSourceRowIndex + i).ToString() + "行不包含有效数据", true); sc = false; } } if (count > 0) { daValue /= count; return new DataStruct(daValue, diValue, range, pattern); } } } else { LogHelper.AddException("规范内容有误:" + text, true); } } sc = false; return null; }