/// <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 GeneratingCertificateOne(string filePath, JobParameterStruct pS, ref List<string> problemFilesList)
{
int pattern = pS.DataPattern;
string output = pS.AutoOutputFolder;
string ext = pS.AutoExtension;
bool createNew = pS.CreateNew;
string tempFo = pS.TempFolder;
Dictionary<int, string> exSheets = new Dictionary<int, string>();
bool Perfect;
bool hasArchieved = false;
bool needFix = true;
bool shouldFix = true;
bool needTestGeCe = true, canGeCe = false;
int stateIndex = -1;
int noIdNumber = 0;
int startDestiRowIndex = -1;
string tempName;
string certId;
string newName = "";
string fileText = "";
string strCompany = "", strType = "", strMacSerial = "", strSensorSerial = "";
string backupKey = "";
FileInfo temp_fi = null;
FileInfo fi = new FileInfo(filePath);
Object format = MSExcel.XlFileFormat.xlWorkbookDefault;
ExcelUtility _sr = new ExcelUtility(filePath, out 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++;
}
}
}
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 == "")
{
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
{
//寻找目标统计文件
existFile = JobMethods.GetFilesFromType(output, _sr.GetText(_sr.ExcelWorkbook, stateIndex, new ExcelPosition("F5")), ext, out checkClear);
if (!checkClear)
{
if (_sr != null && _sr.ExcelWorkbook != null)
{
_sr.ExcelWorkbook.Saved = true;
_sr.TryClose();
}
LogHelper.AddLog(@"***************************************************************", true);
problemFilesList.Add(filePath);
exceptionNum = 0;
dataerrorNum = 0;
return;
}
temp_fi = JobMethods.SearchForFile(filePath, strType, strMacSerial, strSensorSerial, existFile, out hasArchieved);
if (temp_fi == null)
{
if (createNew)
{
//对新记录建档
needTestGeCe = false;
if (hasArchieved)
{
//不存在，复制当前记录过去
newName = DataUtility.DataUtility.PathRightFileName(Util.PathExt.PathCombine(output, strType), tempName, fi.Extension, "_new");
_sr.ExcelWorkbook.SaveAs(newName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, MSExcel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
temp_fi = new FileInfo(newName);
JobMethods.Statistic(_sr, pattern, Perfect, strCompany, strType, tempName, certId);
//TODO: 生成证书处校验是否可选（不存在，复制当前记录过去）
_sr.Verification(_sr.ExcelWorkbook, true, pattern, out checkClear);
fileText = newName;
}
else
{
//不存在，但搜索到有可疑项，暂不处理
fileText = filePath;
}
}
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;
}
}
else if (File.Exists(temp_fi.FullName))
{
DataUtility.DataUtility.BackupFile(tempFo, temp_fi.FullName, out backupKey);
_eu = new ExcelUtility(temp_fi.FullName, out checkClear);
if (!checkClear)
{
LogHelper.AddException(@"Excel文档无法打开", true);
if (_sr != null && _sr.ExcelWorkbook != null)
{
_sr.ExcelWorkbook.Saved = true;
_sr.TryClose();
}
if (_eu != null && _eu.ExcelWorkbook != null)
{
_eu.ExcelWorkbook.Saved = true;
_eu.TryClose();
}
LogHelper.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)
{
if (_sr != null && _sr.ExcelWorkbook != null)
{
_sr.ExcelWorkbook.Saved = true;
_sr.TryClose();
}
if (_eu != null && _eu.ExcelWorkbook != null)
{
_eu.ExcelWorkbook.Saved = true;
_eu.TryClose();
}
problemFilesList.Add(fileText);
LogHelper.AddLog(@"***************************************************************", true);
exceptionNum = 0;
dataerrorNum = 0;
return;
}
_eu.ExcelWorkbook.Save();
_eu.ExcelWorkbook.Saved = true;
canGeCe = JobMethods.Statistic(_eu, pattern, Perfect, strCompany, strType, tempName, certId);
_eu.ExcelWorkbook.Save();
_eu.ExcelWorkbook.Saved = true;
//TODO: 生成证书处校验是否可选（存在，合并入原记录）
_eu.Verification(_eu.ExcelWorkbook, false, pattern, out checkClear);
fileText = temp_fi.FullName;
}
else
{
LogHelper.AddException(@"文件不存在：" + temp_fi.FullName, true);
fileText = filePath;
}
}
catch (Exception ex)
{
LogHelper.AddException(@"生成证书时合并一步遇到异常：" + ex.Message, true);
}
try
{
//有重大失误，关闭两个excel，报错退出。没有失误继续运行
if (exceptionNum > 0)
{
if (_sr != null && _sr.ExcelWorkbook != null)
{
_sr.ExcelWorkbook.Saved = true;
_sr.TryClose();
}
if (_eu != null && _eu.ExcelWorkbook != null)
{
_eu.ExcelWorkbook.Saved = true;
_eu.TryClose();
}
problemFilesList.Add(fileText);
LogHelper.AddLog(@"***************************************************************", true);
exceptionNum = 0;
dataerrorNum = 0;
}
else
{
//有以前记录，需要看是否超差 并且 超差不通过 并且 超差不通过的提示选择不生成证书 时选择退出
if (needTestGeCe && !canGeCe && (MessageBox.Show("检测到有数据超差，是否继续生成证书？", "问题", MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.No))
{
LogHelper.AddException("有实验数据超差，暂时保留原记录，不做任何处理。", true);
if (_sr != null && _sr.ExcelWorkbook != null)
{
_sr.ExcelWorkbook.Saved = true;
_sr.TryClose();
}
if (_eu != null && _eu.ExcelWorkbook != null)
{
_eu.ExcelWorkbook.Saved = true;
_eu.TryClose();
}
DataUtility.DataUtility.RestoreFile(tempFo, backupKey);
problemFilesList.Add(fileText);
LogHelper.AddLog(@"***************************************************************", true);
exceptionNum = 0;
dataerrorNum = 0;
}
else
{
bool success = false;
//关闭归档后的Excel
if (_eu != null && _eu.ExcelWorkbook != null)
{
_eu.ExcelWorkbook.Saved = true;
_eu.TryClose();
}
//新文档因为将sr另存为合并后的文档，需要关闭后重新打开原文档
if (!needTestGeCe)
{
//关闭原来另存为成合并好的excel文件
if (_sr != null && _sr.ExcelWorkbook != null)
{
_sr.ExcelWorkbook.Saved = true;
_sr.TryClose();
}
//重新打开待合并的excel文件
_sr = new ExcelUtility(filePath, out 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;
}
//找到原记录里的数据页，记下序号
stateIndex = -1;
string temp = "";
foreach (MSExcel.Worksheet item in _sr.ExcelWorkbook.Sheets)
{
temp = _sr.GetText(_sr.ExcelWorkbook, item.Index, new ExcelPosition("L2"));
if (certId == item.Name || certId == temp)
{
stateIndex = item.Index;
}
}
//找到序号的话，加入校核人的签名，删除其他sheet
if (stateIndex > 0 && stateIndex < _sr.ExcelWorkbook.Worksheets.Count)
{
_sr.WriteImage(_sr.ExcelWorkbook, stateIndex, new ExcelPosition(29, 9), Util.PathExt.PathCombine(Application.StartupPath, person.Path), 45, 28);
for (int i = _sr.ExcelWorkbook.Worksheets.Count; i > 0; i--)
{
if (i != stateIndex)
{
((MSExcel.Worksheet)_sr.ExcelApp.ActiveWorkbook.Sheets[i]).Delete();
}
}
_sr.ExcelWorkbook.Save();
_sr.ExcelWorkbook.Saved = true;
}
//出证书
JobMethods.GenerateCert(_sr, stateIndex, pS.DataPattern, pS.CertTemplateFilePath, pS.CertFolder, pS.PDFDataFolder, pS.TempFolder, shouldFix, out success);
if (!success)
{
LogHelper.AddException("生成证书失败", true);
if (_sr != null && _sr.ExcelWorkbook != null)
{
_sr.ExcelWorkbook.Saved = true;
_sr.TryClose();
}
problemFilesList.Add(fileText);
LogHelper.AddLog(@"***************************************************************", true);
exceptionNum = 0;
dataerrorNum = 0;
}
else
{
if (_sr != null && _sr.ExcelWorkbook != null)
{
_sr.ExcelWorkbook.Saved = true;
_sr.TryClose();
}
File.Delete(filePath);
}
}
}
}
catch (Exception ex)
{
LogHelper.AddException(@"生成证书时遇到异常：" + ex.Message, true);
}
if (dataerrorNum > 0)
{
dataerrorNum = 0;
LogHelper.AddLog(@"***************************************************************", true);
}
}
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 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 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 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>
/// 生成证书和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);
}
}
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;
}
/// <summary>
/// 判断CT记录中3个数据位置的数据是否为有效的检定数据
/// </summary>
/// <param name="sourceEx"></param>
/// <param name="sourceIndex"></param>
/// <param name="startSourceRowIndex"></param>
/// <param name="columnIndex"></param>
/// <returns></returns>
public static bool TestFlag(ExcelUtility sourceEx, int sourceIndex, int startSourceRowIndex, int columnIndex)
{
Flag a, b, c;
bool conti = false;
double dig;
string text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(startSourceRowIndex, columnIndex));
bool isDouble = double.TryParse(text, out dig);
if (text == "/")
{
a = Flag.Zero;
}
else if (isDouble)
{
a = Flag.Pos;
}
else
{
a = Flag.Neg;
}
text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(startSourceRowIndex + 1, columnIndex));
isDouble = double.TryParse(text, out dig);
if (text == "/")
{
b = Flag.Zero;
}
else if (isDouble)
{
b = Flag.Pos;
}
else
{
b = Flag.Neg;
}
text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(startSourceRowIndex + 2, columnIndex));
isDouble = double.TryParse(text, out dig);
if (text == "/")
{
c = Flag.Zero;
}
else if (isDouble)
{
c = Flag.Pos;
}
else
{
c = Flag.Neg;
}
if (((int)a + (int)b + (int)c > 0) && ((int)a * (int)b * (int)c > -1))
{
conti = true;
}
else
{
conti = false;
if (((int)a + (int)b + (int)c + (int)a * (int)b * (int)c == 0) && !(a == Flag.Zero && b == Flag.Zero && c == Flag.Zero))
{
LogHelper.AddException("数据记录有未识别的数据", true);
}
}
return conti;
}
/// <summary>
///
/// </summary>
/// <param name="sourceEx"></param>
/// <param name="sourceIndex"></param>
/// <param name="pattern"></param>
/// <param name="shouldFix">需要修正但不需要进行修正操作</param>
/// <param name="needFix">需要进行修正操作</param>
/// <returns></returns>
public static bool GetFixState(ExcelUtility sourceEx, int sourceIndex, int pattern, out bool needFix, out bool shouldFix)
{
if (pattern > 1)
{
needFix = false;
shouldFix = false;
return true;
}
string tt1 = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition("M8"));
string tt2 = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition("L8"));
MSExcel.Range rr1 = sourceEx.GetRange(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition("L8"));
double temp_double;
bool isDouble = double.TryParse(tt2, out temp_double);
if (tt1 == "修正")
{
needFix = true;
shouldFix = true;
return true;
}
else if (tt1.Contains("不修正") || tt2.Contains("不修正") || (tt1.Contains("不") && (tt1.Contains("修") || tt1.Contains("休"))) || (tt2.Contains("不") && (tt2.Contains("修") || tt2.Contains("休"))))
{
needFix = false;
shouldFix = false;
return true;
}
else if (tt1.Contains("自修正") || tt2.Contains("自修正"))
{
needFix = false;
shouldFix = true;
return true;
}
else if ((bool)rr1.HasFormula && isDouble)
{
needFix = true;
shouldFix = true;
return true;
}
else
{
LogHelper.AddException("无法判断电离室与半导体类型", true);
needFix = false;
shouldFix = true;
return false;
}
}
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;
}