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 void DoWork(JobParameterStruct pS)
{
string inputFi = pS.InputFile;
string inputFo = pS.AutoInputFolder;
string outputFo = pS.AutoOutputFolder;
string inputEx = pS.AutoExtension;
int cbsi = pS.DataPattern;
if (pS.ActionType < 0 || pS.ActionType > 4)
{
if (MessageBox.Show(@"没有选择任何操作类型，请点击确定后返回重试", "提示", MessageBoxButtons.OK) == DialogResult.OK) { }
}
else if (pS.ActionType < 1 && ((int)pS.FixType < 0 || (int)pS.FixType > 1))
{
if (MessageBox.Show(@"没有选择温度气压修正选项，请点击确定后返回重试", "提示", MessageBoxButtons.OK) == DialogResult.OK) { }
}
else if (MessageBox.Show(@"处理成功将会删除原纪录，是否要继续？", "提示", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
List<string> probfile = new List<string>();
UpdateProgressDisplay(0);
if (inputFi != "")
{
if (!inputFi.Contains(@"~$"))
{
currentFile = inputFi;
ToolStripStatusLabel_SetText(new FileInfo(inputFi).Name);
try
{
dataerrorNum = 0;
exceptionNum = 0;
dod(inputFi, pS, ref probfile);
}
catch (Exception ex)
{
probfile.Add(inputFi);
if (exceptionNum > 0)
{
exceptionNum = 0;
}
else
{
LogHelper.AddLog(@"***************************************************************", true);
}
LogHelper.AddLog(@"* 文件名称：" + inputFi, true);
LogHelper.AddLog(@"* 异常消息：" + ex.Message, true);
LogHelper.AddLog(@"* 异常方法：" + ex.TargetSite, true);
LogHelper.AddLog(@"***************************************************************", true);
}
if (dataerrorNum > 0)
{
dataerrorNum = 0;
LogHelper.AddLog(@"***************************************************************", true);
}
ToolStripStatusLabel_SetText("");
}
UpdateProgressDisplay(100);
if (probfile.Count > 0)
{
LogHelper.AddLog(@"结果02", "共处理文件数：1", true);
LogHelper.AddLog(@"结果03", "异常文件总数：" + probfile.Count, true);
foreach (string item in probfile)
{
LogHelper.AddLog(@"结果04", item, true);
}
}
else
{
LogHelper.AddLog(@"结果05", "没有发现任何异常，共处理文件数：1", true);
}
MessageBox.Show("共处理文件" + "1" + "个。" + Environment.NewLine + "其中成功" + (1-probfile.Count).ToString() + "个，失败" + probfile.Count + "个。", "处理完成", MessageBoxButtons.OK);
}
else if (inputFo != "")
{
DirectoryInfo di = new DirectoryInfo(inputFo);
FileInfo[] fis = di.GetFiles(inputEx, SearchOption.AllDirectories);
probfile.Clear();
int doneNumber = 0;
if (fis.Length > 0)
{
double Step = 100.0 / (double)fis.Length;
double values = 0;
foreach (FileInfo item in fis)
{
if (!item.Name.StartsWith(@"~$"))
{
currentFile = item.FullName;
ToolStripStatusLabel_SetText(new FileInfo(item.FullName).Name);
try
{
dataerrorNum = 0;
exceptionNum = 0;
dod(item.FullName, pS, ref probfile);
}
catch (Exception ex)
{
probfile.Add(item.FullName);
if (exceptionNum > 0)
{
exceptionNum = 0;
}
else
{
LogHelper.AddLog(@"***************************************************************", true);
}
LogHelper.AddLog(@"* 文件名称：" + item.FullName, true);
LogHelper.AddLog(@"* 异常消息：" + ex.Message, true);
LogHelper.AddLog(@"* 异常方法：" + ex.TargetSite, true);
LogHelper.AddLog(@"***************************************************************", true);
}
if (dataerrorNum > 0)
{
dataerrorNum = 0;
LogHelper.AddLog(@"***************************************************************", true);
}
ToolStripStatusLabel_SetText("");
}
values += Step;
UpdateProgressDisplay(values);
doneNumber++;
if (isStopping)
{
break;
}
}
if (probfile.Count > 0)
{
LogHelper.AddLog(@"结果06", "异常文件总数：" + probfile.Count, true);
foreach (string item in probfile)
{
LogHelper.AddLog(@"结果07", item, true);
}
}
else
{
LogHelper.AddLog(@"结果08", "没有发现任何异常，共处理文件数：" + doneNumber, true);
}
MessageBox.Show("共处理文件" + fis.Length + "个。" + Environment.NewLine + "其中成功" + (fis.Length - probfile.Count).ToString() + "个，失败" + probfile.Count + "个。", "处理完成", MessageBoxButtons.OK);
}
else
{
MessageBox.Show(@"输入文件夹没有找到待处理的文件");
}
}
else
{
MessageBox.Show(@"未指定输入文件");
}
}
isWorking = false;
isStopping = false;
Button_SetText(@"开始");
LogHelper.AddLog(Environment.NewLine + Environment.NewLine, true);
}
/// <summary>
///
/// </summary>
/// <param name="filePath"></param>
/// <param name="pattern"></param>
/// <param name="text1"></param>
/// <param name="text7"></param>
/// <param name="text3"></param>
/// <param name="problemFilesList"></param>
public void VerificateOne(string filePath, JobParameterStruct pS, ref List<string> problemFilesList)
{
int pattern = pS.DataPattern;
Dictionary<int, string> exSheets = new Dictionary<int, string>();
Dictionary<int, string> cert = new Dictionary<int, string>();
List<string> sheetsName = new List<string>();
string fileName = "";
bool pass = false;
ExcelUtility _sr = new ExcelUtility(filePath, out checkClear);
if (!checkClear)
{
LogHelper.AddException(@"Excel文档无法打开", true);
_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
{
//TODO: 单独校验处校验是否可选
fileName = _sr.Verification(_sr.ExcelWorkbook, true, pattern, out pass);
}
catch (Exception ex)
{
LogHelper.AddException(@"校验时遇到异常：" + ex.Message, true);
}
finally
{
if (pass)
{
FileInfo fi = new FileInfo(filePath);
fileName = DataUtility.DataUtility.PathRightFileName(fi.DirectoryName, fileName, fi.Extension, "_new");
if (filePath != fileName)
{
_sr.ExcelWorkbook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, MSExcel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Thread.Sleep(1000);
File.Delete(filePath);
}
else
{
_sr.ExcelWorkbook.Save();
}
}
_sr.ExcelWorkbook.Saved = true;
_sr.TryClose();
if (exceptionNum > 0)
{
LogHelper.AddLog(@"***************************************************************", true);
problemFilesList.Add(filePath);
exceptionNum = 0;
dataerrorNum = 0;
}
}
}
/// <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 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 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);
}
}