public static void SingleExcel2Class() { string path = AssetDatabase.GetAssetPath(Selection.activeObject); FileInfo file = new FileInfo(path); string fname = Path.GetFileNameWithoutExtension(file.FullName).ToLower(); fname = UpperFirst(fname); string destPath = Path.GetDirectoryName(file.FullName) + "\\" + fname + Path.GetExtension(file.FullName); // //判断是否重名 string oldPath = "Assets" + file.FullName.Replace('\\', '/').Replace(Application.dataPath, ""); string newPath = "Assets" + destPath.Replace('\\', '/').Replace(Application.dataPath, ""); if (!oldPath.Equals(newPath)) { AssetDatabase.CopyAsset(oldPath, newPath); } AssetDatabase.Refresh(); string f = file.FullName; var excel = new ExcelUtility(f); var json = excel.GetJson(); var statements = excel.GetLine(0); Json2Class(f, json, statements); Debug.Log("导出:" + f); EditorUtility.DisplayDialog("提示", "生成完成!", "确定"); AssetDatabase.Refresh(); }
public HttpResponseMessage GetReportExcel(ReportFilterModel model) { try { var report = _reportService.GetReportById(model.ReportId); var data = GetReportData(model); string filePath = ExcelUtility.createXSL(data, report.Title); byte[] file = System.IO.File.ReadAllBytes(filePath); var response = new HttpResponseMessage(HttpStatusCode.OK) { Content = new ByteArrayContent(file) }; response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.ms-excel"); response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment") { FileName = filePath }; return(response); } catch (Exception ex) { var response = new HttpResponseMessage(HttpStatusCode.InternalServerError); return(response); } }
private void loadfile_btn_Click(object sender, EventArgs e) { var mb = MessageBox.Show(@"Le chrgement de structures depuis le canvas annuleras toute structure prédefini dans la BD pour cet evenement.\n Etes-Vous sure de continuer?", "Demande De Confirmation", MessageBoxButtons.OKCancel); if (mb == DialogResult.OK) { //Call Excel Reader var sheet = ExcelUtility.GetSheetsNames(file_tb.Text)[0]; var datas = ExcelUtility.GetDatasFromSheet(sheet); var even = servEvents.GetByCode(Convert.ToInt32(evenements_liste.SelectedValue.ToString())); //affecter le code Table foreach (var item in datas) { item.Evenement = even; } //Bind Datas liste = new BindingList <Parametre>(datas); bs = new BindingSource(liste, null); structures_Grid.DataSource = bs; //desactiver le panel des modification modifyField_grid.DataSource = null; fields_panel.Enabled = false; //Permettre la modification structures_Grid.ReadOnly = false; //annuler le rowclick structures_Grid.CellMouseClick -= structures_Grid_CellMouseClick; submit_btn.Enabled = true; } }
private void UploadFileDialog_FileOk(object sender, CancelEventArgs e) { try { this.txtFileName.Text = uploadFileDialog.FileName; if (string.IsNullOrEmpty(this.txtFileName.Text)) { return; } if (!this.txtFileName.Text.EndsWith(".xls") && !this.txtFileName.Text.EndsWith(".xlsx")) { throw new Exception("只支持Excel"); } Data = ExcelUtility.ExcelToDataTable(this.txtFileName.Text, true); if (Data == null) { return; } dgView.DataSource = Data; dgView.Refresh(); } catch (Exception ex) { MessageBox.Show(ex.Message, "错误信息", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void CreateExcelInvoice(int accountId) { var mgr = GetExternalInvoiceManager(); var inv = mgr.GetInvoices(accountId).First(); string filePath; if (StartPeriod >= July2009) { string alert = string.Empty; filePath = ExcelUtility.GenerateInvoiceExcelReport(inv, CurrentUser.ClientID, string.Empty, true, ref alert); ShowAlert(alert); } else { filePath = ExcelUtility.MakeSpreadSheet(inv.Header.AccountID, inv.Header.InvoiceNumber, inv.Header.DeptRef, inv.Header.OrgName, CurrentUser.ClientID, StartPeriod, EndPeriod); } // display excel spreadsheet if (!string.IsNullOrEmpty(filePath)) { //only include the file name Response.Redirect(GetVirtualPath(Path.GetFileName(filePath), null)); } }
protected void BtnDownloadAll_Click(object sender, EventArgs e) { MakeOrgGrid(false); var items = (IEnumerable <ExternalInvoice>)rptInvoice.DataSource; bool del = true; if (items.Count() > 0) { var mgr = GetExternalInvoiceManager(); var invoices = mgr.GetInvoices(); foreach (ExternalInvoice i in items) { int accountId = i.Header.AccountID; // get datarows for Org and Account var inv = invoices.First(x => x.Header.AccountID == accountId); string alert = string.Empty; string fileName = ExcelUtility.GenerateInvoiceExcelReport(i, CurrentUser.ClientID, "Zip", del, ref alert); del = false; } Response.Redirect("~/RepInvoice.aspx?DownloadAll=" + "ExternalInvoices_" + StartPeriod.ToString("yyyy-MM")); } }
/// <summary> /// Sheet Data Add /// </summary> /// <param name="dic"></param> /// <param name="sheet"></param> private void InsertSheetData(UI902ViewModel model) { _dic = new Dictionary <string, string>(); using ExcelUtility excel = new ExcelUtility(model.FileDictionary); ExcelWorksheet sheet = excel.GetSheet(); for (int i = 2, j = 1; ; i++) { if (i == 2 && string.IsNullOrWhiteSpace(sheet.Cells[i, j].Text)) { break; } if (string.IsNullOrWhiteSpace(sheet.Cells[i, j].Text)) { i = 1; j += 3; continue; } try { _dic.Add(sheet.Cells[i, j].Text, sheet.Cells[i, j + 1].Text); } catch { } } }
public static void AddNewEmployeeTest() { IWebDriver wd = new ChromeDriver(Constants.ChromeDriver); wd.Manage().Timeouts().ImplicitWait = TimeSpan.FromSeconds(9); wd.Manage().Window.Maximize(); Debug.WriteLine("Driver inicijalizovan HA!"); HumanityLogin.Login(wd, Constants.UserName, Constants.Password); System.Threading.Thread.Sleep(6000); HumanityMenu.ClickStaff(wd); System.Threading.Thread.Sleep(3000); HumanityStaff.ClickAddEmployees(wd); ExcelUtility.OpenFile(Constants.XLPath); ExcelUtility.LoadWorkSheet(0); for (int i = 1; i <= 3; i++) { HumanityStaff.SendFirstName(wd, ExcelUtility.GetDataAt(i, 0), i); HumanityStaff.SendLastName(wd, ExcelUtility.GetDataAt(i, 1), i); HumanityStaff.SendEmail(wd, ExcelUtility.GetDataAt(i, 3), i); } HumanityStaff.ClickSaveEmployees(wd); Assert.AreNotEqual(wd.Url, "https://zemicka.humanity.com/app/staff/add/"); Console.WriteLine("Zaposleni Uspesno dodati!"); wd.Quit(); }
private void OnExport() { if (SaleList == null || !SaleList.Any()) { MvvmUtility.ShowMessageAsync("没有要导出的销售单"); return; } var columnDefinitions = new List <ColumnDefinition <SaleDto> > { new ColumnDefinition <SaleDto>("订单号", dto => dto.OrderNo), new ColumnDefinition <SaleDto>("销售单号", dto => dto.SaleOrderNo), new ColumnDefinition <SaleDto>("渠道订单号", dto => dto.TransNo), new ColumnDefinition <SaleDto>("订单渠道", dto => dto.OrderSource), new ColumnDefinition <SaleDto>("销售状态", dto => dto.SaleStatus), new ColumnDefinition <SaleDto>("销售单状态", dto => dto.StatusName), new ColumnDefinition <SaleDto>("收银状态", dto => dto.CashStatusName), new ColumnDefinition <SaleDto>("销售时间", dto => dto.SellDate), new ColumnDefinition <SaleDto>("销售单金额", dto => dto.SalesAmount), new ColumnDefinition <SaleDto>("销售单数量", dto => dto.SalesCount), new ColumnDefinition <SaleDto>("门店", dto => dto.StoreName), new ColumnDefinition <SaleDto>("收银流水号", dto => dto.CashNum), new ColumnDefinition <SaleDto>("收银时间", dto => dto.CashDate) }; ExcelUtility.Export(SaleList, columnDefinitions, "销售单"); }
public void ThenTheValuesShouldBeEdited_() { // edit test cases EditTestPage edit = new EditTestPage(driver); Validations val = new Validations(driver); string code = ExcelUtility.ReadData(2, "Validate"); //aisha shaik-validate bool isFound = val.ValidateTimeMaterial(code, true, false); if (isFound) { edit.EditTimeAndMaterials(); bool isEdited = val.ValidateTimeMaterial(ExcelUtility.ReadData(2, "editValidate"), false, false); //112233 = edit validate Assert.AreEqual(true, isEdited); Assert.Pass("Successfully edited the row."); } else { Assert.Fail($"Code {code} not found."); } IWebElement editSave = driver.FindElement(By.XPath("//input[contains(@id,'SaveButton')]")); editSave.Click(); driver.Quit(); }
private void buttonImportExcel_Click(object sender, EventArgs e) { var ofd = new OpenFileDialog(); ofd.AddExtension = true; ofd.Filter = "Excel文件|*.xlsx"; if (ofd.ShowDialog() == DialogResult.OK) { var excelFilepath = ofd.FileName; try { dataGridView1.Rows.Clear(); dataGridView1.Columns.Clear(); dataList = ExcelUtility.GetFromExcel(excelFilepath); Utility.LoadData(dataGridView1, dataList); _colNames = dataList[0]; buttonMapping.Enabled = true; Text = _title + " 路径:" + excelFilepath; } catch (Exception ex) { MessageBoxUtil.Error(ex.Message); } } }
internal static PeriodicSeries GetPeriodicSeries(string sheetName) { var Table = ExcelUtility.Read(xlsFileName(), sheetName); var rval = new PeriodicSeries(Table); return(rval); }
internal static System.Data.DataTable ControlPointTableFromName(string text, string lookupColumnName) { var tbl = ExcelUtility.Read(xlsFileName(), "ControlPoints"); tbl = DataTableUtility.Select(tbl, lookupColumnName + " = '" + text + "'", ""); return(tbl); }
bool SingleConvert(string excelPath, string coding, FormatOption format) { ExcelUtility excel = new ExcelUtility(excelPath); Encoding encoding = Encoding.GetEncoding(coding); bool convertFlag = false; switch (format) { case FormatOption.JSON: convertFlag = excel.ConvertToJson(ExportPath, encoding); break; case FormatOption.XML: convertFlag = excel.ConvertToXml(ExportPath); break; case FormatOption.CVS: convertFlag = excel.ConvertToCSV(ExportPath, encoding); break; default: break; } return(convertFlag); }
private void btnAverageExport() { var workbook = new HSSFWorkbook(); if (Session[SessionKeys.gvTimeSpanStatisticsGroupByDeptSource] as DataTable != null) { DataTable dtgvTimeSpanStatisticsGroupByDeptSource = Session[SessionKeys.gvTimeSpanStatisticsGroupByDeptSource] as DataTable; ExcelUtility.RenderDataTableToExcel(workbook, dtgvTimeSpanStatisticsGroupByDeptSource, "按月统计部门工资"); ExcelUtility.EmbedImage(workbook, workbook.GetSheet("按月统计部门工资") as HSSFSheet, ExcelExportUtility.GetImagePath(Session[SessionKeys.TimeSpanStatisticsGroupByDeptLineChartFileNameAndExp].ToString()), true, new int[] { 0, 10, 0, 10 }); } if (Session[SessionKeys.gvAverageStatisticsTableSource] as DataTable != null) { DataTable dtgvAverageStatisticsTableSource = Session[SessionKeys.gvAverageStatisticsTableSource] as DataTable; ExcelUtility.RenderDataTableToExcel(workbook, dtgvAverageStatisticsTableSource, "人均统计"); ExcelUtility.EmbedImage(workbook, workbook.GetSheet("人均统计") as HSSFSheet, ExcelExportUtility.GetImagePath(Session[SessionKeys.AverageStatisticsBarChartFileNameAndExp].ToString()), true, new int[] { 0, 10, 0, 10 }); } MemoryStream ms = new MemoryStream(); workbook.Write(ms); ms.Flush(); ms.Position = 0; workbook = null; ExcelExportUtility.OutputExcel(Server, Response, "员工工资人均统计", ms); }
protected override void ExcuteSelf() { var fileStream = ExcelUtility.FileToStream(_FilePath.Trim()); var dataTable = ExcelUtility.RenderDataTableFromExcel(fileStream, 0, 0, true); InsertUpdateEmployeeAccountSet(dataTable); }
public static void Main(string[] args) { IWorkbook crosswise = ExcelUtility.GetWorkBook(GetConfig("crosswise")); IWorkbook lengthways = ExcelUtility.GetWorkBook(GetConfig("lengthways")); IWorkbook paper = ExcelUtility.GetWorkBook(GetConfig("paper")); IWorkbook targetTemplate = ExcelUtility.GetWorkBook(GetConfig("targetTemplate")); Dictionary <string, AcademicAchievements> resultDictionary = new Dictionary <string, AcademicAchievements>(); //ISheet templateSheet = targetTemplate.GetSheetAt(0); //professorNames = getProfessorNameFromTemplate(templateSheet); foreach (var item in professorNames) { resultDictionary.Add(item, new AcademicAchievements(item)); } ISheet paperSheet = paper.GetSheetAt(0); getPaperInfo(paperSheet, resultDictionary); ISheet crosswiseSheet = crosswise.GetSheetAt(0); getProjectInfo(crosswiseSheet, resultDictionary); ISheet resultSheet = targetTemplate.GetSheetAt(0); setResultValue(resultSheet, resultDictionary); List <ISheet> sheets = new List <ISheet>() { resultSheet }; IWorkbook workbook = ExcelUtility.CloneSheets(sheets); ExcelUtility.WriteWorkbookToFile(workbook, GetConfig("result")); }
protected void btn_ExportExcel_Click(object sender, EventArgs e) { try { bindgrid(); try { string result; if (ddl_reportList.SelectedItem.Value == "SP_FACULTY_TURNITIN") { result = ExcelUtility.WithoutColumnToExcel(gV_View.DataSource); } else { result = ExcelUtility.ToExcel(gV_View.DataSource); } ExcelUtility.ExportToExcel(result); } catch { // lbl_ack.Visible = true; // lbl_ack.Text = "Please check Excel configuration !!!"; } } catch { // lbl_ack.Visible = true; // lbl_ack.Text = "Please Select all the values"; } }
/// <summary> /// Create Series that references data in Excel /// </summary> public ExcelDataReaderSeries(string filename, string sheetName, string dateColumn, string valueColumn, string units = "") : base() { workbook = ExcelUtility.GetWorkbookReference(filename); Init(workbook, sheetName, dateColumn, valueColumn, units); }
public static void GenSQLite(string outPath) { var tablePath = IPath.Combine(Application.dataPath, "Resource/Table"); var tableDir = Path.GetDirectoryName(tablePath); var xlslFiles = Directory.GetFiles(tableDir, "*.xlsx", SearchOption.AllDirectories); // if (Directory.Exists(outPath) == false) { Directory.CreateDirectory(outPath); } var _path = IPath.Combine(outPath, "Local.db"); // sql = new SQLiteService(SqliteLoder.CreateConnetion(_path)); foreach (var f in xlslFiles) { var excel = new ExcelUtility(f); var json = excel.GetJson(); Json2Sqlite(f, json); } sql.Close(); EditorUtility.ClearProgressBar(); Debug.Log("导出Sqlite完成!"); AssetDatabase.Refresh(); }
public void DownloadExl() { var newsletter = new DataTable(); newsletter.Columns.Add(new DataColumn("Name", Type.GetType("System.String"))); newsletter.Columns.Add(new DataColumn("Email", Type.GetType("System.String"))); newsletter.Columns.Add(new DataColumn("Message", Type.GetType("System.String"))); newsletter.Columns.Add(new DataColumn("DateCreated", Type.GetType("System.String"))); var registerduser = db.ContactUs.OrderByDescending(n => n.DateCreated); foreach (var item in registerduser) { object[] data = new Object[4]; data[0] = item.Name; data[1] = item.Email; data[2] = item.Message; data[3] = item.DateCreated.ToString("yyyy-MM-dd"); newsletter.Rows.Add(data); } MemoryStream stream = ExcelUtility.GetExcel(newsletter); var filename = DateTime.Now + "_ContactUs.xlsx"; var contenttype = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.Clear(); Response.ContentType = contenttype; Response.AddHeader("content-disposition", "attachment;filename=" + filename); Response.Cache.SetCacheability(HttpCacheability.NoCache); Response.BinaryWrite(stream.ToArray()); Response.End(); }
public void EditTest() { HomeTestPage home = new HomeTestPage(driver); home.HomeTestCases1(); // edit test cases EditTestPage edit = new EditTestPage(driver); Validations val = new Validations(driver); string code = ExcelUtility.ReadData(2, "Validate"); //aisha shaik-validate bool isFound = val.ValidateTimeMaterial(code, true, false); if (isFound) { edit.EditTimeAndMaterials(); bool isEdited = val.ValidateTimeMaterial(ExcelUtility.ReadData(2, "editValidate"), false, false); //112233 = edit validate Assert.AreEqual(true, isEdited); Assert.Pass("Successfully edited the row."); } else { Assert.Fail($"Code {code} not found."); } }
public static string[] GetNames() { var tbl = ExcelUtility.Read(xlsFileName(), "ControlPoints"); tbl = DataTableUtility.Select(tbl, " Enabled = true", ""); return(DataTableUtility.Strings(tbl, "", "Name")); }
/// <summary> /// Extracts DateTime[] used for label positions /// </summary> /// <param name="curveName"></param> /// <returns>list of dates using year 2000</returns> public static DateTime[] GetVariableForecastLabelDates(string curveName) { var rval = new List <DateTime>(); var tbl = ExcelUtility.Read(xlsFileName(), "VariableForecastLevels"); tbl = DataTableUtility.Select(tbl, " RuleCurve = '" + curveName + "'", ""); //stored in excel as single string for example: /// 6/15,5/15,5/15,4/15,4/15, 3/15, 2/15, 1/15 if (tbl.Rows.Count > 0) { var s = tbl.Rows[0][2].ToString(); var tokens = s.Split(','); for (int i = 0; i < tokens.Length; i++) { var x = tokens[i].Split('/'); if (x.Length != 2) { break; } int m, d; if (int.TryParse(x[0], out m) && int.TryParse(x[1], out d)) { rval.Add(new DateTime(2000, m, d)); } } } return(rval.ToArray()); }
public override IEnumerable <string> GetAllRecordTypes() { if (_recordTypes == null) { _recordTypes = ExcelUtility.GetExcelTabNames(ExcelNameQualified); } return(_recordTypes); }
private string CreateEmptyFile(ExcelWorkbookType workbookType) { var emptyFilePath = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName() + ".xlsx"); ExcelUtility.CreateWorkbook(emptyFilePath, workbookType); return(emptyFilePath); }
void SaveToFile() { Debug.Log("SaveToFile"); string pathSave = System.IO.Path.Combine(Application.streamingAssetsPath, "Save/excelTestSave"); ExcelUtility.ToExcelAndWriteFile <ExcelItem>(pathSave, items, "testName", true); }
/// <summary> /// 转换Excel文件 /// </summary> private static void Convert() { foreach (string assetsPath in excelList) { //获取Excel文件的绝对路径 string excelPath = Application.dataPath.Replace("Assets", "") + assetsPath; //构造Excel工具类 ExcelUtility excel = new ExcelUtility(excelPath); //判断编码类型 Encoding encoding = null; if (indexOfEncoding == 0 || indexOfEncoding == 3) { encoding = Encoding.GetEncoding("utf-8"); } else if (indexOfEncoding == 1) { encoding = Encoding.GetEncoding("gb2312"); } //判断输出类型 string output = ""; if (indexOfFormat == 0) { output = excelPath.Replace(".xlsx", ".json"); excel.ConvertToJson(output, encoding); } else if (indexOfFormat == 1) { output = excelPath.Replace(".xlsx", ".csv"); excel.ConvertToCSV(output, encoding); } else if (indexOfFormat == 2) { output = excelPath.Replace(".xlsx", ".xml"); excel.ConvertToXml(output); } else if (indexOfFormat == 3) { output = excelPath.Replace(".xlsx", ".lua"); excel.ConvertToLua(output, encoding); } else if (indexOfFormat == 4) { output = excelPath.Replace(".xlsx", "CSV.cs"); excel.ConvertToCSharp(output, encoding); } //判断是否保留源文件 if (!keepSource) { FileUtil.DeleteFileOrDirectory(excelPath); } //刷新本地资源 AssetDatabase.Refresh(); } }
/// <summary> /// 转换Excel文件 /// </summary> private static void Convert() { foreach (string assetsPath in excelList) { //获取Excel文件的绝对路径 string excelPath = pathRoot + "/" + assetsPath; //构造Excel工具类 ExcelUtility excel = new ExcelUtility(excelPath); //判断编码类型 Encoding encoding = null; if (indexOfEncoding == 0 || indexOfEncoding == 3) { encoding = Encoding.GetEncoding("utf-8"); } else if (indexOfEncoding == 1) { encoding = Encoding.GetEncoding("gb2312"); } //判断输出类型 string output = ""; if (indexOfFormat == 0) { output = excelPath.Replace(".xlsx", ".json"); excel.ConvertToJson(output, encoding); } else if (indexOfFormat == 1) { output = excelPath.Replace(".xlsx", ".csv"); excel.ConvertToCSV(output, encoding); } else if (indexOfFormat == 2) { output = excelPath.Replace(".xlsx", ".xml"); excel.ConvertToXml(output); } else if (indexOfFormat == 3) { output = excelPath.Replace(".xlsx", ".lua"); excel.ConvertToLua(output, encoding); } //判断是否保留源文件 if (!keepSource) { FileUtil.DeleteFileOrDirectory(excelPath); } //刷新本地资源 AssetDatabase.Refresh(); } //转换完后关闭插件 //这样做是为了解决窗口 //再次点击时路径错误的Bug instance.Close(); }
public void XLSX() { var fn = Path.Combine(Globals.TestDataPath, "CalculationTests.xlsx"); Console.WriteLine(fn); var tbl = ExcelUtility.Read(fn, 0); Console.WriteLine(tbl.Rows.Count); }
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) { AddException(@"Excel文档无法打开", true); if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; return; } _sr.ExcelApp.DisplayAlerts = false; _sr.ExcelApp.AlertBeforeOverwriting = false; //第一次循环:获取信息,并规范每页的标签 foreach (MSExcel.Worksheet item in _sr.ExcelWorkbook.Sheets) { //规范sheet标签名为证书编号 certId = _sr.GetText(_sr.ExcelWorkbook, item.Index, "L2", out checkClear).Trim(); 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, "A4", out checkClear); if (!item.Name.Contains(@"标准模板") && rr.Text.ToString().Trim().StartsWith(@"送校单位")) { //有记录不包含规范的证书编号 AddException(@"该文档有实验数据不包含证书编号", true); noIdNumber++; } } } if (exSheets.Count == 0) { if (noIdNumber == 0) { AddException(@"该文档可能是空文档", true); } if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; return; } else if (exSheets.Count + noIdNumber > 1) { 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 == "") { AddException(@"送校单位信息未提取到", true); checkClear = true; } if (strType == "") { AddException(@"仪器型号信息未提取到", true); checkClear = true; } if (strMacSerial == "") { AddException(@"主机编号信息未提取到", true); checkClear = true; } if (checkClear) { AddLog(@"***************************************************************", true); if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; return; } GetFixState(_sr, stateIndex, pattern, out needFix, out shouldFix); //判断目标行数 if (pattern < 2) { startDestiRowIndex = 18; } else if (pattern == 2) { //TODO: kV修正项 } else { AddException(@"数据类型无效", true); if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; return; } try { //寻找目标统计文件 existFile = GetFilesFromType(output, _sr.GetText(_sr.ExcelWorkbook, stateIndex, "F5", out checkClear), ext, out checkClear); if (!checkClear) { if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; return; } //output = Path.Combine(output, _sr.GetText(_sr.ExcelWorkbook, stateIndex, "F5", out checkClear)); //if (!Directory.Exists(output)) //{ // AddException("无法识别的仪器类型:" + _sr.GetText(_sr.ExcelWorkbook, stateIndex, "F5", out checkClear), true); // if (_sr != null && _sr.ExcelWorkbook != null) // { // _sr.ExcelWorkbook.Saved = true; // _sr.TryClose(); // } // AddLog(@"***************************************************************", true); // problemFilesList.Add(filePath); // exceptionNum = 0; // dataerrorNum = 0; // return; //} //existFile = (new DirectoryInfo(output)).GetFiles(@"*.xls*", SearchOption.AllDirectories); temp_fi = SearchForFile(filePath, strType, strMacSerial, strSensorSerial, existFile, out hasArchieved); if (temp_fi == null) { if (createNew) { //对新记录建档 needTestGeCe = false; if (hasArchieved) { //不存在,复制当前记录过去 newName = DataUtility.DataUtility.PathRightFileName(output, 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); Statistic(_sr, pattern, Perfect, strCompany, strType, tempName, certId); //TODO: 生成证书处校验是否可选(不存在,复制当前记录过去) _sr.Verification(_sr.ExcelWorkbook, true, pattern, out checkClear); fileText = newName; } else { //不存在,但搜索到有可疑项,暂不处理 fileText = filePath; } } else { //对新记录不建档,退出 AddException("没有在历史数据记录中发现匹配项,暂不处理。", true); if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } 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) { 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(); } AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; return; } _eu.ExcelApp.DisplayAlerts = false; _eu.ExcelApp.AlertBeforeOverwriting = false; 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); AddLog(@"***************************************************************", true); exceptionNum = 0; dataerrorNum = 0; return; } _eu.ExcelWorkbook.Save(); _eu.ExcelWorkbook.Saved = true; canGeCe = 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 { AddException(@"文件不存在:" + temp_fi.FullName, true); fileText = filePath; } } catch (Exception ex) { 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); AddLog(@"***************************************************************", true); exceptionNum = 0; dataerrorNum = 0; } else { //有以前记录,需要看是否超差 并且 超差不通过 并且 超差不通过的提示选择不生成证书 时选择退出 if (needTestGeCe && !canGeCe && (MessageBox.Show("检测到有数据超差,是否继续生成证书?", "问题", MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.No)) { 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); 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) { AddException(@"Excel文档无法打开", true); if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; return; } //打开成功 _sr.ExcelApp.DisplayAlerts = false; _sr.ExcelApp.AlertBeforeOverwriting = false; } //找到原记录里的数据页,记下序号 stateIndex = -1; string temp = ""; foreach (MSExcel.Worksheet item in _sr.ExcelWorkbook.Sheets) { temp = _sr.GetText(_sr.ExcelWorkbook, item.Index, "L2", out checkClear); if (certId == item.Name || certId == temp) { stateIndex = item.Index; } } //找到序号的话,加入校核人的签名,删除其他sheet if (stateIndex > 0 && stateIndex < _sr.ExcelWorkbook.Worksheets.Count) { _sr.WriteImage(_sr.ExcelWorkbook, stateIndex, 29, 9, person, 45, 28, out success); 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; } //出证书 GenerateCert(_sr, stateIndex, pS.DataPattern, pS.CertTemplateFilePath, pS.CertFolder, pS.PDFDataFolder, pS.TempFolder, shouldFix, out success); if (!success) { AddException("生成证书失败", true); if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } problemFilesList.Add(fileText); 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) { AddException(@"生成证书时遇到异常:" + ex.Message, true); } if (dataerrorNum > 0) { dataerrorNum = 0; AddLog(@"***************************************************************", true); } }
//public void DoWork(JobParameterStruct pS) //{ // string inputFi = pS.InputFile; // string inputFo = pS.AutoInputFolder; // string outputFo = pS.AutoOutputFolder; // string inputEx = pS.AutoExtension; // int cbsi = pS.DataPattern; // if (pS.ActionType < 0 || pS.ActionType > 4) // { // if (MessageBox.Show(@"没有选择任何操作类型,请点击确定后返回重试", "提示", MessageBoxButtons.OK) == DialogResult.OK) { } // } // else if (pS.ActionType < 1 && (pS.FixType < 0 || pS.FixType > 1)) // { // if (MessageBox.Show(@"没有选择温度气压修正选项,请点击确定后返回重试", "提示", MessageBoxButtons.OK) == DialogResult.OK) { } // } // else if (MessageBox.Show(@"处理成功将会删除原纪录,是否要继续?", "提示", MessageBoxButtons.YesNo) == DialogResult.Yes) // { // List<string> probfile = new List<string>(); // UpdateProgress(0); // if (inputFi != "") // { // if (!inputFi.Contains(@"~$")) // { // currentFile = inputFi; // ToolStripStatusLabel_SetText(new FileInfo(inputFi).Name); // try // { // dataerrorNum = 0; // exceptionNum = 0; // dod(inputFi, pS, ref probfile); // } // catch (Exception ex) // { // probfile.Add(inputFi); // if (exceptionNum > 0) // { // exceptionNum = 0; // } // else // { // AddLog(@"***************************************************************", true); // } // AddLog(@"* 文件名称:" + inputFi, true); // AddLog(@"* 异常消息:" + ex.Message, true); // AddLog(@"* 异常方法:" + ex.TargetSite, true); // AddLog(@"***************************************************************", true); // } // if (dataerrorNum > 0) // { // dataerrorNum = 0; // AddLog(@"***************************************************************", true); // } // ToolStripStatusLabel_SetText(""); // } // UpdateProgress(100); // if (probfile.Count > 0) // { // AddLog(@"结果02", "共处理文件数:1", true); // AddLog(@"结果03", "异常文件总数:" + probfile.Count, true); // foreach (string item in probfile) // { // AddLog(@"结果04", item, true); // } // } // else // { // AddLog(@"结果05", "没有发现任何异常,共处理文件数:1", true); // } // MessageBox.Show("共处理文件" + "1" + "个。" + Environment.NewLine + "其中成功" + (1 - probfile.Count).ToString() + "个,失败" + probfile.Count + "个。", "处理完成", MessageBoxButtons.OK); // } // else if (inputFo != "") // { // DirectoryInfo di = new DirectoryInfo(inputFo); // FileInfo[] fis = di.GetFiles(inputEx, SearchOption.AllDirectories); // probfile.Clear(); // int doneNumber = 0; // if (fis.Length > 0) // { // double Step = 100.0 / (double)fis.Length; // double values = 0; // foreach (FileInfo item in fis) // { // if (!item.Name.StartsWith(@"~$")) // { // currentFile = item.FullName; // ToolStripStatusLabel_SetText(new FileInfo(item.FullName).Name); // try // { // dataerrorNum = 0; // exceptionNum = 0; // dod(item.FullName, pS, ref probfile); // } // catch (Exception ex) // { // probfile.Add(item.FullName); // if (exceptionNum > 0) // { // exceptionNum = 0; // } // else // { // AddLog(@"***************************************************************", true); // } // AddLog(@"* 文件名称:" + item.FullName, true); // AddLog(@"* 异常消息:" + ex.Message, true); // AddLog(@"* 异常方法:" + ex.TargetSite, true); // AddLog(@"***************************************************************", true); // } // if (dataerrorNum > 0) // { // dataerrorNum = 0; // AddLog(@"***************************************************************", true); // } // ToolStripStatusLabel_SetText(""); // } // values += Step; // UpdateProgress(values); // doneNumber++; // if (isStopping) // { // break; // } // } // if (probfile.Count > 0) // { // AddLog(@"结果06", "异常文件总数:" + probfile.Count, true); // foreach (string item in probfile) // { // AddLog(@"结果07", item, true); // } // } // else // { // AddLog(@"结果08", "没有发现任何异常,共处理文件数:" + doneNumber, true); // } // MessageBox.Show("共处理文件" + fis.Length + "个。" + Environment.NewLine + "其中成功" + (fis.Length - probfile.Count).ToString() + "个,失败" + probfile.Count + "个。", "处理完成", MessageBoxButtons.OK); // } // else // { // MessageBox.Show(@"输入文件夹没有找到待处理的文件"); // } // } // else // { // MessageBox.Show(@"未指定输入文件"); // } // } // isWorking = false; // isStopping = false; // Button_SetText(@"开始"); // AddLog(Environment.NewLine + Environment.NewLine, true); //} ///// <summary> ///// pattern 1 KV ///// pattern 2 Dose ///// pattern 3 CT ///// pattern 4 ///// </summary> ///// <param name="filePath"></param> ///// <param name="pattern"></param> //public void StatisticsOne(string filePath, int pattern, string text1, string text7, string text3, ref List<string> problemFilesList) //{ // Dictionary<int, string> exSheets = new Dictionary<int, string>(); // Dictionary<int, string> cert = new Dictionary<int, string>(); // List<string> sheetsName = new List<string>(); // bool Perfect; // int stateIndex = -1; // FileInfo temp_fi = null; // int lineIndex = 8; // int countIndex = 0; // bool firstTime = true; // bool needInitialState = true; // string tempName; // string insNumber = ""; // string standardInsNumber = ""; // string sourceDirectory = "";//, temp_text3 = ""; // string strCompany = "", strType = "", strMacSerial = "", strSensorSerial = ""; // string temp_strCompany = "", temp_strType = "", temp_strMacSerial = "", temp_strSensorSerial = ""; // ExcelUtility _eu = new ExcelUtility(filePath, out checkClear); // if (!checkClear) { return; } // _eu.ExcelApp.DisplayAlerts = false; // _eu.ExcelApp.AlertBeforeOverwriting = false; // Object format = MSExcel.XlFileFormat.xlWorkbookDefault; // FileInfo fi = new FileInfo(_eu.path); // //输入文件的目录 // if (text1 == "") // { // sourceDirectory = fi.DirectoryName.Replace(text7, text3) + @"\"; // } // else // { // sourceDirectory = text3 + @"\"; // } // try // { // //1.初始化:规范统计sheet结构,写入固定内容 // stateIndex = _eu.InitialStatisticSheet(out exSheets, out needInitialState, out checkClear); // if (!checkClear) { return; } // _eu.WriteTitle(pattern, stateIndex); // switch (pattern) // { // case 0: // //2.搬运数据 // countIndex = 0; // foreach (int item in exSheets.Keys) // { // countIndex++; // cert.Add(lineIndex, exSheets[item]); // _eu.CopyOneYearData(item, stateIndex, lineIndex, exSheets[item], out insNumber); // tempName = _eu.PerfectFileName(item, out temp_strCompany, out temp_strType, out temp_strMacSerial, out temp_strSensorSerial, out Perfect); // if (temp_strCompany.Length > strCompany.Length) // { // strCompany = temp_strCompany; // } // if (temp_strMacSerial.Length > strMacSerial.Length) // { // strMacSerial = temp_strMacSerial; // } // if (temp_strType.Length > strType.Length) // { // strType = temp_strType; // } // if (temp_strSensorSerial.Length > strSensorSerial.Length) // { // strSensorSerial = temp_strSensorSerial; // } // if (countIndex == 1) // { // standardInsNumber = insNumber; // } // else // { // if (GetPureNumber(standardInsNumber, true) != GetPureNumber(insNumber, true)) // { // AddException(cert[8] + "和" + cert[lineIndex] + "的仪器编号不同:", true); // AddLog(@"错误", " " + cert[8] + @":" + standardInsNumber, true); // AddLog(@"错误", " " + cert[lineIndex] + @":" + insNumber, true); // } // } // if (firstTime && Perfect) // { // _eu.DesiredName = CleanName(tempName); // //写入送校单位 // _eu.WriteValue(stateIndex, 2, 2, strCompany, out checkClear); // //写入仪器名称 // _eu.WriteValue(stateIndex, 3, 2, strType, out checkClear); // firstTime = false; // } // lineIndex++; // } // if (firstTime) // { // //写入送校单位 // _eu.WriteValue(stateIndex, 2, 2, strCompany, out checkClear); // //写入仪器名称 // _eu.WriteValue(stateIndex, 3, 2, strType, out checkClear); // //文件名 // _eu.DesiredName = CleanName(strCompany + "_" + strType + "_" + strMacSerial + ((strSensorSerial.Replace(@"/", "").Trim() != "") ? "+" + strSensorSerial : "")); // } // //3.分析数据 // if (exSheets.Count > 1) // { // _eu.WriteValue(stateIndex, lineIndex, 1, @"重复性", out checkClear); // //计算平均值和年稳定性 // _eu.StatisticsOneColumn(stateIndex, lineIndex, 4, cert, "RQR4(60kV)", exSheets.Count > 1); // _eu.StatisticsOneColumn(stateIndex, lineIndex, 6, cert, "RQR5(70kV)", exSheets.Count > 1); // _eu.StatisticsOneColumn(stateIndex, lineIndex, 8, cert, "RQR6(80kV)", exSheets.Count > 1); // _eu.StatisticsOneColumn(stateIndex, lineIndex, 10, cert, "RQR8(100kV)", exSheets.Count > 1); // _eu.StatisticsOneColumn(stateIndex, lineIndex, 12, cert, "RQR9(120kV)", exSheets.Count > 1); // } // if (exceptionNum > 0) // { // problemFilesList.Add(_eu.DesiredName); // } // break; // case 1: // break; // } // temp_fi = new FileInfo(RightFileName(sourceDirectory, _eu.DesiredName, fi.Extension, "-new")); // if (!temp_fi.Directory.Exists) // { // Directory.CreateDirectory(temp_fi.DirectoryName); // } // _eu.ExcelDoc.SaveAs(temp_fi.FullName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, MSExcel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); // } // catch (Exception ex) // { // exceptionNum++; // if (exceptionNum == 1) // { // AddLog(@"信息", "原文件名:" + filePath, true); // } // AddLog(@"异常", ex.Message, true); // AddLog(@"异常", " " + ex.TargetSite.ToString(), true); // } // finally // { // //移动前的输出文件路径 // sourceDirectory = sourceDirectory + _eu.DesiredName + fi.Extension; // _eu.ExcelDoc.Saved = true; // _eu.TryClose(); // if (exceptionNum > 0) // { // //移动后的输出文件路径 // tempName = RightFileName(Application.StartupPath + @"\有问题整理后\", _eu.DesiredName, fi.Extension, "-new"); // AddLog(@"信息", "新文件名:" + tempName, true); // AddLog(@"***************************************************************", true); // if (File.Exists(sourceDirectory)) File.Move(sourceDirectory, tempName); // if (text1 == "") // { // temp_fi = new FileInfo(fi.FullName.Replace(text7, Application.StartupPath + @"\有问题")); // if (!temp_fi.Directory.Exists) // { // Directory.CreateDirectory(temp_fi.DirectoryName); // } // File.Copy(fi.FullName, temp_fi.FullName, true); // } // else // { // File.Copy(fi.FullName, Application.StartupPath + @"\有问题\" + fi.Name + fi.Extension, true); // } // } // } //} #endregion #region TestingFunction #region GeneratingForm public void GeneratingForm(string filePath, JobParameterStruct pS, ref List<string> problemFilesList) { //int pattern = pS.DataPattern; string output = pS.AutoOutputFolder; bool needFix = pS.AutoFixType; string templateName = pS.DataTemplateFilePath; string macType = pS.MacType; bool success = true; int templateIndex = -1; MSExcel.Worksheet ws1 = null; WordUtility _wu = new WordUtility(filePath, out success); if (!success) { AddException("Word文档打开失败", true); return; } string tempName = _wu.GetText(_wu.WordDocument, 7);//B4:送校单位 string tempSerial = _wu.GetText(_wu.WordDocument, 15).Trim();//F5:仪器型号 //string tempNum = _wu.GetText(_wu.WordDocument, 19);//*H5:仪器编号 string tempQiju = _wu.GetText(_wu.WordDocument, 11);//B5:仪器名称 string tempZhsh = _wu.GetText(_wu.WordDocument, 3);//L2:证书编号 string tempStress = _wu.GetText(_wu.WordDocument, 27);//F4:联系地址 _wu.TryClose(); if (tempSerial != "" && tempSerial != macType) { AddDataError("证书中包含的仪器型号与指定的仪器型号不符", true); } string str = tempZhsh.Substring(8); string strSavename = Path.Combine(output, tempName + "_" + macType + "_" + str + ".xlsx"); if (File.Exists(strSavename)) { if (MessageBox.Show(@"文件已存在,是否覆盖?" + Environment.NewLine + strSavename, "提示", MessageBoxButtons.YesNo) == DialogResult.Yes) { File.Delete(strSavename); } else { success = false; return; } } File.Copy(templateName, strSavename); ExcelUtility _sr = new ExcelUtility(strSavename, out checkClear); if (!checkClear) { AddException(@"Excel文档无法打开", true); if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; return; } _sr.ExcelApp.DisplayAlerts = false; _sr.ExcelApp.AlertBeforeOverwriting = false; try { foreach (MSExcel.Worksheet item in _sr.ExcelWorkbook.Sheets) { if (item.Name == @"标准模板") { templateIndex = item.Index; } else if (item.Name.Contains(@"标准模板")) { AddException(@"发现多余的标准模板", true); } } if (templateIndex > -1) { ws1 = (MSExcel.Worksheet)_sr.ExcelWorkbook.Sheets[templateIndex]; ws1.Copy(ws1, Type.Missing); ws1 = (MSExcel.Worksheet)_sr.ExcelWorkbook.Sheets[templateIndex]; if (!ws1.Name.Contains(@"标准模板")) { AddException(@"标准模板复制出错", true); success = false; return; } else { ws1.Name = str; } } else { AddException(@"找不到模板excel中的标准模板页", true); } _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, 4, 2, tempName, out success); _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, 5, 6, macType, out success); _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, 5, 2, tempQiju, out success); _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, 2, 12, str, out success); _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, 4, 6, tempStress, out success); if (needFix) { _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, 8, 13, "修正", out success); } else { //电离室->半导体 MSExcel.Range rr = _sr.GetRange(_sr.ExcelWorkbook, ws1.Index, "L8", out success); rr.FormulaLocal = ""; rr.Formula = ""; rr.FormulaArray = ""; _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, 8, 12, "1.000000", "@", out success); _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, 8, 13, "不修正", out success); } //写入记录者 _sr.WriteImage(_sr.ExcelWorkbook, ws1.Index, 29, 7, person, 45, 28, out success); _sr.ExcelWorkbook.Save(); } catch (Exception ex) { AddException("生成证书时遇到异常:" + ex.Message, true); } finally { //关闭Excel if (_sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } //有重大失误的情况下报错,没有失误就删除源word文件 if (exceptionNum > 0) { AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; } else { File.Delete(filePath); } } }
/// <summary> /// 转换Excel文件 /// </summary> private static void Convert() { foreach(string assetsPath in excelList) { //获取Excel文件的绝对路径 string excelPath=pathRoot + "/" + assetsPath; //构造Excel工具类 ExcelUtility excel=new ExcelUtility(excelPath); //判断编码类型 Encoding encoding=null; if(indexOfEncoding==0){ encoding=Encoding.GetEncoding("utf-8"); }else if(indexOfEncoding==1){ encoding=Encoding.GetEncoding("gb2312"); } //判断输出类型 string output=""; if(indexOfFormat==0){ output=excelPath.Replace(".xlsx",".json"); excel.ConvertToJson(output,encoding); }else if(indexOfFormat==1){ output=excelPath.Replace(".xlsx",".csv"); excel.ConvertToCSV(output,encoding); }else if(indexOfFormat==2){ output=excelPath.Replace(".xlsx",".xml"); excel.ConvertToXml(output); } //判断是否保留源文件 if(!keepSource) { FileUtil.DeleteFileOrDirectory(excelPath); } //刷新本地资源 AssetDatabase.Refresh(); } //转换完后关闭插件 //这样做是为了解决窗口 //再次点击时路径错误的Bug instance.Close(); }
public 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) { bool checkClear; double temp_double = 0.0; string text; MSExcel.Range rr = null; KVCriterion dataCri = KVCriterion.Null; sc = true; //规范 rr = sourceEx.GetRange(sourceEx.ExcelWorkbook, sourceIndex, DataUtility.DataUtility.PositionString(13, columnIndex), out checkClear); text = rr.Text.ToString().Trim(); rr = destiEx.GetRange(destiEx.ExcelWorkbook, destiIndex, DataUtility.DataUtility.PositionString(13, columnIndex), out checkClear); rr.Value2 = text; //标准值 sourceEx.GetCriterion(sourceEx.ExcelWorkbook, sourceIndex, columnIndex, true, out text, out dataCri); //仪器滤片 if (standardRowIndex > 12) { rr = sourceEx.GetRange(sourceEx.ExcelWorkbook, sourceIndex, DataUtility.DataUtility.PositionString(standardRowIndex, columnIndex), out checkClear); if (rr != null) { text = rr.Text.ToString().Trim(); rr = destiEx.GetRange(destiEx.ExcelWorkbook, destiIndex, DataUtility.DataUtility.PositionString(15, columnIndex), out checkClear); rr.Value2 = text; } else { rr = destiEx.GetRange(destiEx.ExcelWorkbook, destiIndex, DataUtility.DataUtility.PositionString(15, columnIndex), out checkClear); rr.Value2 = @"/"; } } else { rr = destiEx.GetRange(destiEx.ExcelWorkbook, destiIndex, DataUtility.DataUtility.PositionString(15, columnIndex), out checkClear); rr.Value2 = @"/"; } //复制数据 if (style == "") { for (int i = 0; i < 3; i++) { rr = sourceEx.GetRange(sourceEx.ExcelWorkbook, sourceIndex, startSourceRowIndex + i, columnIndex, out checkClear); if (rr.Value2 == null || string.IsNullOrWhiteSpace(rr.Value2.ToString())) { sourceEx.WriteValue(sourceEx.ExcelWorkbook, sourceIndex, startSourceRowIndex + i, columnIndex, @"/", out checkClear); sourceEx.ExcelWorkbook.Save(); sourceEx.ExcelWorkbook.Saved = true; text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, startSourceRowIndex + i, columnIndex, out checkClear).Trim(); } else { text = rr.Value2.ToString().Trim(); } if (double.TryParse(text, out temp_double) || text == @"/") { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, startDestiRowIndex + i, columnIndex, text, out checkClear); } else { AddException(@"第" + columnIndex + "列第" + (startSourceRowIndex + i).ToString() + "行不包含有效数据", true); sc = false; } } } else { for (int i = 0; i < 3; i++) { rr = sourceEx.GetRange(sourceEx.ExcelWorkbook, sourceIndex, startSourceRowIndex + i, columnIndex, out checkClear); if (rr.Value2 == null || string.IsNullOrWhiteSpace(rr.Value2.ToString())) { sourceEx.WriteValue(sourceEx.ExcelWorkbook, sourceIndex, startSourceRowIndex + i, columnIndex, @"/", out checkClear); sourceEx.ExcelWorkbook.Save(); sourceEx.ExcelWorkbook.Saved = true; text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, startSourceRowIndex + i, columnIndex, out checkClear).Trim(); } else { text = rr.Value2.ToString().Trim(); } if (double.TryParse(text, out temp_double) || text == @"/") { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, startDestiRowIndex + i, columnIndex, text, style, out checkClear); } else { AddException(@"第" + columnIndex + "列第" + (startSourceRowIndex + i).ToString() + "行不包含有效数据", true); sc = false; } } } }
public void CopyOneData(ExcelUtility sourceEx, int sourceIndex, ExcelUtility destiEx, int destiIndex, int rowIndex, int columnIndex, string defaultValue, string style, out bool sc) { bool checkClear; string text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, rowIndex, columnIndex, out checkClear).Trim(); if (text == "") { sourceEx.WriteValue(sourceEx.ExcelWorkbook, sourceIndex, rowIndex, columnIndex, defaultValue, out checkClear); } else if (style == "") { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, rowIndex, columnIndex, text, out checkClear); } else { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, rowIndex, columnIndex, text, style, out checkClear); } if (checkClear) { sc = true; } else { sc = false; } }
public void CopyData(ExcelUtility sourceEx, int sourceIndex, ExcelUtility destiEx, int pattern, string certIdori, bool needFix, bool shouldFix, int startDestiRowIndex, out bool success) { int index = 0; CopyData(sourceEx, sourceIndex, destiEx, pattern, certIdori, needFix, shouldFix, startDestiRowIndex, out index, out success); }
/// <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 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 success1; bool noNeed = false; int templateIndex = -1; int startSourceRowIndex = -1; int destiIndex = -1; MSExcel.Range rr = null; MSExcel.Worksheet ws1 = null; Dictionary<int, string> exSheets = new Dictionary<int, string>(); string temp; string text = ""; string certId = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, "L2", out success1); if (!success1) { 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(@"标准模板")) { AddDataError(@"第" + item.Index + "页发现多余的标准模板", true); } else { temp = destiEx.GetText(destiEx.ExcelWorkbook, item.Index, "L2", out success1).Trim(); if (temp.StartsWith(@"20") && (temp.Length == 9 || temp.Length == 10)) { //找到有证书编号的数据页 if (item.Name == certId) { if (MessageBox.Show("在历史数据记录的Excel中已发现了证书编号为" + certId + "的页面,是否覆盖?选择是,进行覆盖。选择否,停止对本Excel的处理", "是否覆盖", MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.Yes) { ws1 = item; } else { AddException(@"要合并入的数据已存在于第" + item.Index + "页", true); newSheetIndex = -1; success = false; return; } } else { exSheets.Add(item.Index, temp); } } } } if (!noNeed) { if (templateIndex == -1) { 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(@"标准模板")) { 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, i, 3, out success1).Trim(); if (text == "1") { text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, i + 1, 3, out success1).Trim(); if (text == "2") { text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, i + 2, 3, out success1).Trim(); if (text == "3") { startSourceRowIndex = i; break; } } } } if (startSourceRowIndex == -1) { AddException(@"找不到原始数据所在的行", true); success = false; newSheetIndex = -1; return; } //拷贝数据 CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 2, 12, "@", out success1); if (!success1) AddException(@"《证书编号》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 4, 1, 4, 2, new string[] { @"送校单位:", @"单位名称:" }, "@", out success1); if (!success1) AddException(@"《送校单位》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 4, 5, 4, 6, @"联系地址:", "@", out success1); //if (!success1) AddException(@"《联系地址》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 5, 1, 5, 2, @"仪器名称:", "@", out success1); if (!success1) AddException(@"《仪器名称》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 5, 5, 5, 6, @"型号:", "@", out success1); if (!success1) AddException(@"《型号》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 5, 7, 5, 8, new string[] { @"主机编号:", @"编号:" }, "@", out success1); if (!success1) AddException(@"《主机编号》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 5, 9, 5, 10, @"厂家:", "@", out success1); if (!success1) AddException(@"《厂家》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 5, 11, 5, 12, new string[] { @"探测器编号:", "电离室号:", "探测器号:" }, "@", out success1); if (!success1) AddException(@"《探测器编号》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 31, 7, "", out success1); //if (!success1) AddException(@"《记录者》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 31, 9, "", out success1); //if (!success1) AddException(@"《校对者》数据复制错误", true); CopyDate(sourceEx, sourceIndex, destiEx, ws1.Index, out success1); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 7, 11, "0.000", out success1); if (needFix && !success1) AddException(@"《温度》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 7, 13, "0.0%", out success1); if (needFix && !success1) AddException(@"《湿度》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 8, 10, "", out success1); if (needFix && !success1) AddException(@"《气压》数据复制错误", true); rr = destiEx.GetRange(destiEx.ExcelWorkbook, ws1.Index, "M8", out success1); if (needFix) { destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, 8, 13, "修正", out success1); } else { //电离室->半导体 rr = destiEx.GetRange(destiEx.ExcelWorkbook, ws1.Index, "L8", out success1); rr.FormulaLocal = ""; rr.Formula = ""; rr.FormulaArray = ""; destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, 8, 12, "1.000000", "@", out success1); if (shouldFix) { destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, 8, 13, "自修正", out success1); } else { destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, 8, 13, "不修正", out success1); } } ArrayList dataStructList = new ArrayList(); 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, p, 1, out success1).Trim() + sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, p, 2, out success1).Trim() + sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, p, 3, out success1).Trim(); if (text.ToLower().Contains(keyword)) { standardRowIndex = p; break; } } switch (pattern) { case 0: //Dose CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 27, 13, "@", out success1); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 12, 4, "", out success1); if (!success1) AddException(@"《量程》数据复制错误", true); text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, 12, 4, out success1).Trim(); dataStructList.Add(CopyThreeDoseData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 4, startDestiRowIndex, "", standardRowIndex, text, pattern, out success1)); dataStructList.Add(CopyThreeDoseData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 6, startDestiRowIndex, "", standardRowIndex, text, pattern, out success1)); dataStructList.Add(CopyThreeDoseData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 8, startDestiRowIndex, "", standardRowIndex, text, pattern, out success1)); dataStructList.Add(CopyThreeDoseData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 10, startDestiRowIndex, "", standardRowIndex, text, pattern, out success1)); dataStructList.Add(CopyThreeDoseData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 12, startDestiRowIndex, "", standardRowIndex, text, pattern, out success1)); text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, 12, 12, out success1).Trim(); if (text.StartsWith("单位")) { CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 12, 13, "", out success1); if (!success1) AddException(@"《单位》数据复制错误", true); } else { dataStruct = DataStruct.CalDataRange(dataStructList, sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, 12, 4, out success1).Trim(), pattern, out success); switch (dataStruct.DataRanges) { case DataRange.cGy: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, 12, 13, "cGy", out success1); break; case DataRange.mGy: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, 12, 13, "mGy", out success1); break; case DataRange.mR: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, 12, 13, "mR", out success1); break; case DataRange.R: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, 12, 13, "R", out success1); break; case DataRange.uGy: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, 12, 13, "μGy", out success1); break; case DataRange.Unknown: AddException("无法判断数据单位", true); break; } switch (dataStruct.Distance) { case Distance.d1: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, 6, 13, "1.0m", out success1); break; case Distance.d1_5: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, 6, 13, "1.5m", out success1); break; case Distance.Unknown: AddException("无法获取标准值,判断测试距离", true); break; } } break; case 1: //CT CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 27, 13, "@", out success1); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 12, 4, "", out success1); if (!success1) AddException(@"《量程》数据复制错误", true); text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, 12, 4, out success1).Trim(); dataStructList.Add(CopyThreeCTData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 4, startDestiRowIndex, "", standardRowIndex, text, pattern, out success1)); dataStructList.Add(CopyThreeCTData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 6, startDestiRowIndex, "", standardRowIndex, text, pattern, out success1)); dataStructList.Add(CopyThreeCTData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 8, startDestiRowIndex, "", standardRowIndex, text, pattern, out success1)); dataStructList.Add(CopyThreeCTData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 10, startDestiRowIndex, "", standardRowIndex, text, pattern, out success1)); dataStructList.Add(CopyThreeCTData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 12, startDestiRowIndex, "", standardRowIndex, text, pattern, out success1)); text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, 12, 12, out success1).Trim(); if (text.StartsWith("单位")) { CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 12, 13, "", out success1); if (!success1) AddException(@"《单位》数据复制错误", true); } else { dataStruct = DataStruct.CalDataRange(dataStructList, sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, 12, 4, out success1).Trim(), pattern, out success); switch (dataStruct.DataRanges) { case DataRange.mGycm: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, 12, 13, "mGycm", out success1); break; case DataRange.mGy: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, 12, 13, "mGy", out success1); break; case DataRange.cGy: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, 12, 13, "cGy", out success1); break; case DataRange.mR: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, 12, 13, "mR", out success1); break; case DataRange.R: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, 12, 13, "R", out success1); break; case DataRange.uGy: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, 12, 13, "μGy", out success1); break; case DataRange.cGycm: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, 12, 13, "cGycm", out success1); break; case DataRange.Rcm: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, 12, 13, "Rcm", out success1); break; case DataRange.Unknown: AddException("无法判断数据单位", true); break; } switch (dataStruct.Distance) { case Distance.d1: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, 6, 13, "1.0m", out success1); break; case Distance.d1_5: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, 6, 13, "1.5m", out success1); break; case Distance.Unknown: AddException("无法获取标准值,判断测试距离", true); break; } } //CopyCTData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, startDestiRowIndex, "", out success1); break; case 2: //KV CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 28, 13, "@", out success1); //if (!success1) AddException(@"《备注》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 12, 4, @"/", "", out success1); if (!success1) AddException(@"《量程》数据复制错误", true); text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, 12, 4, out success1).Trim(); CopyThreeKVData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 4, startDestiRowIndex, "", standardRowIndex, text, pattern, out success1); CopyThreeKVData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 6, startDestiRowIndex, "", standardRowIndex, text, pattern, out success1); CopyThreeKVData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 8, startDestiRowIndex, "", standardRowIndex, text, pattern, out success1); CopyThreeKVData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 10, startDestiRowIndex, "", standardRowIndex, text, pattern, out success1); CopyThreeKVData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 12, startDestiRowIndex, "", standardRowIndex, text, pattern, out success1); break; default: break; } } } else { newSheetIndex = 1; } success = true; }
/// <summary> /// 判断CT记录中3个数据位置的数据是否为有效的检定数据 /// </summary> /// <param name="sourceEx"></param> /// <param name="sourceIndex"></param> /// <param name="startSourceRowIndex"></param> /// <param name="columnIndex"></param> /// <returns></returns> public 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, DataUtility.DataUtility.PositionString(startSourceRowIndex, columnIndex), out checkClear).Trim(); 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, DataUtility.DataUtility.PositionString(startSourceRowIndex + 1, columnIndex), out checkClear).Trim(); 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, DataUtility.DataUtility.PositionString(startSourceRowIndex + 2, columnIndex), out checkClear).Trim(); 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)) { 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 bool GetFixState(ExcelUtility sourceEx, int sourceIndex, int pattern, out bool needFix, out bool shouldFix) { if (pattern > 1) { needFix = false; shouldFix = false; return true; } bool checkClear; string tt1 = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, "M8", out checkClear); string tt2 = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, "L8", out checkClear); MSExcel.Range rr1 = sourceEx.GetRange(sourceEx.ExcelWorkbook, sourceIndex, "L8", out checkClear); 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 { AddException("无法判断电离室与半导体类型", true); needFix = false; shouldFix = true; return false; } }
public 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 checkClear; bool conti = true; double temp_double = 0.0; double diValue = 0.00001; string text; MSExcel.Range rr = null; DoseCriterion dataCri = DoseCriterion.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, DataUtility.DataUtility.PositionString(13, columnIndex), out checkClear); 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, DataUtility.DataUtility.PositionString(13, columnIndex), DataUtility.DataUtility.PositionString(13, columnIndex + 1), out checkClear); rr.Value2 = text; //电压写入14行 destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, 14, columnIndex, dataCri.Voltage, out checkClear); //半值层写入15行 destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, 15, columnIndex, dataCri.HalfValueLayer, out checkClear); //标准值 if (standardRowIndex > 12) { rr = sourceEx.GetRange(sourceEx.ExcelWorkbook, sourceIndex, DataUtility.DataUtility.PositionString(standardRowIndex, columnIndex), out checkClear); 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, DataUtility.DataUtility.PositionString(16, columnIndex), DataUtility.DataUtility.PositionString(16, columnIndex + 1), out checkClear); 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, startSourceRowIndex + i, columnIndex, out checkClear); if (rr.Value2 == null || string.IsNullOrWhiteSpace(rr.Value2.ToString())) { sourceEx.WriteValue(sourceEx.ExcelWorkbook, sourceIndex, startSourceRowIndex + i, columnIndex, @"/", out checkClear); sourceEx.ExcelWorkbook.Save(); sourceEx.ExcelWorkbook.Saved = true; text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, startSourceRowIndex + i, columnIndex, out checkClear).Trim(); } 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, startDestiRowIndex + i, columnIndex, text, out checkClear); if (text != @"/") { count++; daValue += temp_double; } } else { 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, startSourceRowIndex + i, columnIndex, out checkClear); if (rr.Value2 == null || string.IsNullOrWhiteSpace(rr.Value2.ToString())) { sourceEx.WriteValue(sourceEx.ExcelWorkbook, sourceIndex, startSourceRowIndex + i, columnIndex, @"/", out checkClear); sourceEx.ExcelWorkbook.Save(); sourceEx.ExcelWorkbook.Saved = true; text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, startSourceRowIndex + i, columnIndex, out checkClear).Trim(); } 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, startDestiRowIndex + i, columnIndex, text, style, out checkClear); if (text != @"/") { count++; daValue += temp_double; } } else { AddException(@"第" + columnIndex + "列第" + (startSourceRowIndex + i).ToString() + "行不包含有效数据", true); sc = false; } } if (count > 0) { daValue /= count; return new DataStruct(daValue, diValue, range, pattern); } } } else { AddException("规范内容有误:" + text, true); } } sc = false; return null; }
/// <summary> /// 生成证书和pdf记录 /// </summary> /// <param name="excel">excel记录文件</param> /// <param name="sourceIndex">excel记录页索引号</param> /// <param name="wordPath">证书模板文件</param> /// <param name="savePath">证书记录存储文件夹</param> /// <param name="pdfPath">pdf记录存储文件夹</param> /// <param name="success">成功信号</param> public void GenerateCert(ExcelUtility excel, int sourceIndex, int pattern, string wordPath, string savePath, string pdfPath, string tempFolder, bool shouldFix, out bool success) { //GenerateCert(_sr, stateIndex, path, pS.CertFolder, pS.PDFDataFolder, out success); try { WordUtility wu = new WordUtility(wordPath, out success); if (!success) { Log.LogHelper.AddException("Word文档打开失败", true); return; } string stemp1 = excel.GetText(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("L2")); object otemp1; string wdName = "DYjl" + stemp1 + Path.GetExtension(wordPath); string pdfName = "DYjl" + stemp1 + "_" + excel.GetText(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B4")) + ".pdf"; switch (pattern) { case 0: //检定 //检定依据 wu.WriteValue(wu.WordDocument, "m_JDYJ", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B8"))); //有效日期 string[] dates = excel.GetText(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("K29")).Split(new string[] { "年", "月", "日", " " }, StringSplitOptions.RemoveEmptyEntries); wu.WriteValue(wu.WordDocument, "m_YXRQ1", dates[0].Substring(2)); wu.WriteValue(wu.WordDocument, "m_YXRQ2", dates[1]); wu.WriteValue(wu.WordDocument, "m_YXRQ3", dates[2]); //检定日期 dates = excel.GetText(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("K30")).Split(new string[] { "年", "月", "日", " " }, StringSplitOptions.RemoveEmptyEntries); wu.WriteValue(wu.WordDocument, "m_JDRQ1", dates[0].Substring(2)); wu.WriteValue(wu.WordDocument, "m_JDRQ2", dates[1]); wu.WriteValue(wu.WordDocument, "m_JDRQ3", dates[2]); //备注 wu.WriteValue(wu.WordDocument, "m_BZ", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B27"))); break; case 1: //校准 //客户地址 wu.WriteValue(wu.WordDocument, "m_KHDZ", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("F4"))); //校准依据 wu.WriteValue(wu.WordDocument, "m_JZYJ", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B8"))); //校准日期 wu.WriteValue(wu.WordDocument, "m_JZRQ", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("K30"))); //扩展不确定度 wu.WriteValue(wu.WordDocument, "m_KZBQDD", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("K26"))); break; default: Log.LogHelper.AddException("生成证书时指定了不存在的检定类型", true); break; } /// <summary> /// 类型1:普通复制 /// </summary> wu.WriteValue(wu.WordDocument, "m_SJDW", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B4"))); wu.WriteValue(wu.WordDocument, "m_QJMC", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("B5"))); wu.WriteValue(wu.WordDocument, "m_XHGG", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("F5"))); wu.WriteValue(wu.WordDocument, "m_SCCS", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("J5"))); wu.WriteValue(wu.WordDocument, "m_LC", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("D12"))); wu.WriteValue(wu.WordDocument, "m_QY", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("J8"))); wu.WriteValue(wu.WordDocument, "m_WD", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("K7"))); wu.WriteValue(wu.WordDocument, "m_ZSBH1", stemp1); wu.WriteValue(wu.WordDocument, "m_ZSBH2", stemp1); wu.WriteValue(wu.WordDocument, "m_ZSBH3", stemp1); /// <summary> /// 类型2:百分比换算后复制 /// </summary> otemp1 = (object)excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("M7")).Value; if (otemp1 == null) { stemp1 = "/"; } else { stemp1 = string.Format("{0:F1}", float.Parse(otemp1.ToString()) * 100); } wu.WriteValue(wu.WordDocument, "m_SD", stemp1); /// <summary> /// 类型3:仪器编号两段合并后复制 /// </summary> otemp1 = (object)excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("H5")).Value; if (otemp1 == null) { stemp1 = ""; } else { stemp1 = otemp1.ToString(); } otemp1 = (object)excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("L5")).Value; if (otemp1 == null) { if (stemp1 == "") { stemp1 = "/"; } } else if (otemp1.ToString() == "/") { if (stemp1 == "") { stemp1 = "/"; } } else { stemp1 = stemp1 + " + " + otemp1.ToString(); } wu.WriteValue(wu.WordDocument, "m_CCBH", stemp1); /// <summary> /// 类型4:数据部分 /// </summary> wu.WriteDataValue(wu.WordDocument, "m_DATA1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("D24")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "m_DATA2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("F24")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "m_DATA3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("H24")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "m_DATA4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("J24")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "m_DATA5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("L24")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "m_DATA6", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("D54")), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "m_DATA7", excel.GetRange(excel.ExcelWorkbook, sourceIndex, new ExcelPosition("F54")), "{0:F3}"); //小数点后三位 //另存word和pdf wu.WordDocument.SaveAs2(Path.Combine(savePath, DataUtility.DataUtility.FileNameCleanName(wdName))); excel.SaveAsPDF(excel.ExcelWorkbook, Path.Combine(pdfPath, DataUtility.DataUtility.FileNameCleanName(pdfName)), tempFolder, out success); wu.WordDocument.Saved = true; wu.TryClose(); } catch (Exception ex) { success = false; Log.LogHelper.AddException("生成证书时出现错误:" + ex.Message, true); } }
public override void DoTheJob() { int pattern = jobParam.DataPattern; string output = jobParam.AutoOutputFolder; string ext = jobParam.AutoExtension; bool createNew = jobParam.CreateNew; string tempFo = jobParam.TempFolder; FileInfo[] existFile = null; Dictionary<int, string> exSheets = new Dictionary<int, string>(); bool Perfect; bool success; 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 success); ExcelUtility _eu = null; MSExcel.Range rr = null; if (!success) { LogHelper.AddException(@"Excel文档无法打开", true); if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } LogHelper.AddProblemFilesAndReset(filePath); 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.AddProblemFilesAndReset(filePath); 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); success = false; if (strCompany == "") { LogHelper.AddException(@"送校单位信息未提取到", true); success = true; } if (strType == "") { LogHelper.AddException(@"仪器型号信息未提取到", true); success = true; } if (strMacSerial == "") { LogHelper.AddException(@"主机编号信息未提取到", true); success = true; } if (success) { if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } LogHelper.AddProblemFilesAndReset(filePath); return; } JobMethods.GetFixState(_sr, stateIndex, pattern, out needFix, out shouldFix); //目标行数 startDestiRowIndex = 18; try { //寻找目标统计文件 existFile = JobMethods.GetFilesFromType(output, _sr.GetText(_sr.ExcelWorkbook, stateIndex, new ExcelPosition("F5")), ext, out success); if (!success) { if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } LogHelper.AddProblemFilesAndReset(filePath); 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 success); fileText = newName; } else { //不存在,但搜索到有可疑项,暂不处理 fileText = filePath; } } else { //对新记录不建档,退出 LogHelper.AddException("没有在历史数据记录中发现匹配项,暂不处理。", true); if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } LogHelper.AddProblemFilesAndReset(filePath); return; } } else if (File.Exists(temp_fi.FullName)) { DataUtility.DataUtility.BackupFile(tempFo, temp_fi.FullName, out backupKey); _eu = new ExcelUtility(temp_fi.FullName, out success); if (!success) { 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.AddProblemFilesAndReset(filePath); return; } _eu.ExcelApp.DisplayAlerts = false; _eu.ExcelApp.AlertBeforeOverwriting = false; JobMethods.CopyData(_sr, stateIndex, _eu, pattern, certId, needFix, shouldFix, startDestiRowIndex, out success); if (!success) { 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.AddProblemFilesAndReset(fileText); 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 success); fileText = temp_fi.FullName; } else { LogHelper.AddException(@"文件不存在:" + temp_fi.FullName, true); fileText = filePath; } } catch (Exception ex) { LogHelper.AddException(@"生成证书时合并一步遇到异常:" + ex.Message, true); } try { //有重大失误,关闭两个excel,报错退出。没有失误继续运行 if (LogHelper.HasException) { 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.AddProblemFilesAndReset(fileText); } else { //有以前记录,需要看是否超差 并且 超差不通过 并且 超差不通过的提示选择不生成证书 时选择退出 if (needTestGeCe && !canGeCe && !FormOperator.MessageBox_Show_YesNo("检测到有数据超差,是否继续生成证书?", "问题")) { 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); LogHelper.AddProblemFilesAndReset(fileText); } else { 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 success); if (!success) { LogHelper.AddException(@"Excel文档无法打开", true); if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } LogHelper.AddProblemFilesAndReset(filePath); 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(30, 9), Util.PathExt.PathCombine(ProgramConfiguration.ProgramFolder, 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; } //出证书 GenerateCert(_sr, stateIndex, jobParam.DataPattern, jobParam.CertTemplateFilePath, jobParam.CertFolder, jobParam.PDFDataFolder, jobParam.TempFolder, shouldFix, out success); if (!success) { LogHelper.AddException("生成证书失败", true); if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } LogHelper.AddProblemFilesAndReset(fileText); } 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 (LogHelper.HasDataError) { LogHelper.ResetError(); 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) { AddException(@"Excel文档无法打开", true); if (_sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; return; } _sr.ExcelApp.DisplayAlerts = false; _sr.ExcelApp.AlertBeforeOverwriting = false; 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, "L2", out checkClear).Trim(); 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, "A4", out checkClear); if (!item.Name.Contains(@"标准模板") && rr.Text.ToString().Trim().StartsWith(@"送校单位")) { //有记录不包含规范的证书编号 AddException(@"该文档有实验数据不包含证书编号", true); noIdNumber++; } } } if (exSheets.Count == 0) { if (noIdNumber == 0) { AddException(@"该文档可能是空文档", true); } if (_sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; return; } else if (exSheets.Count + noIdNumber > 1) { AddException(@"该文档包含多个数据sheet,默认处理第一个", true); } 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 == "") AddException(@"送校单位信息未提取到", true); if (temp_strType == "") AddException(@"仪器型号信息未提取到", true); if ((temp_strSensorSerial.Length + temp_strMacSerial.Length) == 0) AddException(@"主机编号和探头编号信息未提取到", true); if (temp_strMacSerial == "" || temp_strMacSerial == "/") AddException(@"主机编号信息未提取到", true); 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: AddException(@"数据类型无效", true); if (_sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } 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)) { AddException(@"拷贝模板失败", true); if (_sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; return; } else { temp_fi = new FileInfo(tempName); } } else { AddException(@"模板不存在:" + templateName, true); if (_sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; return; } exSheets.Clear(); ExcelUtility _eu = new ExcelUtility(temp_fi.FullName, out checkClear); if (!checkClear) { 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); 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; CopyData(_sr, stateIndex, _eu, pattern, certId, needFix, shouldFix, startDestiRowIndex, out newSheetIndex, out checkClear); if (newSheetIndex > 0 && checkClear) { bool testflag; text = _eu.GetText(_eu.ExcelWorkbook, newSheetIndex, 2, 11, out testflag); if (!text.ToLower().EndsWith("dyjl")) { AddException("证书编号不以DYjl开始", true); } if (certId.StartsWith("200")) { _eu.WriteValue(_eu.ExcelWorkbook, newSheetIndex, 2, 11, "证书编号:DYjx", "", out testflag); } } else { AddException(@"无法找到新合并入数据的位置", true); } _eu.ExcelWorkbook.Save(); } catch (Exception ex) { 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); AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; } else { canDelete = true; } } if (canDelete) { try { File.Delete(filePathNew); } catch (Exception ex) { AddDataError("已处理完成,但原文件无法正常删除!遇到异常:" + ex.Message, true); } } }
public void CopyDate(ExcelUtility sourceEx, int sourceIndex, ExcelUtility destiEx, int destiIndex, out bool sc) { bool checkClear; string text1, text2, text3, text4; string text = ""; for (int i = 33; i > 26; i--) { text1 = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, i, 10, out checkClear).Trim(); text2 = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, i, 11, out checkClear).Trim(); text3 = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, i, 12, out checkClear).Trim(); text4 = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, i, 13, out checkClear).Trim(); text = text1 + text2 + text3 + text4; if (text != "") { break; } } if (text == "") { sc = false; AddException(@"《日期》数据复制错误:" + text, true); return; } else { text = text.Replace(@" ", "").Replace(@"年", " 年 ").Replace(@"月", " 月 ").Replace(@"日", " 日"); } destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, 31, 11, text, out checkClear); sc = true; }
/// <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) { AddException(@"Excel文档无法打开", true); if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; return; } _sr.ExcelApp.DisplayAlerts = false; _sr.ExcelApp.AlertBeforeOverwriting = false; //第一次循环:获取信息,并规范每页的标签 foreach (MSExcel.Worksheet item in _sr.ExcelWorkbook.Sheets) { //规范sheet标签名为证书编号 certId = _sr.GetText(_sr.ExcelWorkbook, item.Index, "L2", out checkClear).Trim(); 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, "A4", out checkClear); if (!item.Name.Contains(@"标准模板") && rr.Text.ToString().Trim().StartsWith(@"送校单位")) { //有记录不包含规范的证书编号 AddException(@"该文档有实验数据不包含证书编号", true); noIdNumber++; } } renameStr = _sr.GetText(_sr.ExcelWorkbook, item.Index, 5, 7, out checkClear).Trim().ToLower(); if (renameStr.StartsWith(@"编号:")) { _sr.WriteValue(_sr.ExcelWorkbook, item.Index, 5, 7, renameStr.Replace(@"编号:", @"主机编号:"), out checkClear); } renameStr = _sr.GetText(_sr.ExcelWorkbook, item.Index, 5, 11, out checkClear).Trim().ToLower(); if (renameStr.StartsWith(@"电离室号:")) { _sr.WriteValue(_sr.ExcelWorkbook, item.Index, 5, 11, renameStr.Replace(@"电离室号:", @"探测器编号:"), out checkClear); } else if (renameStr.StartsWith(@"探测器号:")) { _sr.WriteValue(_sr.ExcelWorkbook, item.Index, 5, 11, renameStr.Replace(@"探测器号:", @"探测器编号:"), out checkClear); } } _sr.ExcelWorkbook.Save(); _sr.ExcelWorkbook.Saved = true; if (exSheets.Count == 0) { if (noIdNumber == 0) { AddException(@"该文档可能是空文档", true); } if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; return; } else if (exSheets.Count + noIdNumber > 1) { 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 == "") { AddException(@"送校单位信息未提取到", true); checkClear = true; } if (strType == "") { AddException(@"仪器型号信息未提取到", true); checkClear = true; } if (strMacSerial == "" || strMacSerial == "主机_/") { AddException(@"主机编号信息未提取到", true); checkClear = true; } if (checkClear) { AddLog(@"***************************************************************", true); if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; return; } GetFixState(_sr, stateIndex, pattern, out needFix, out shouldFix); if (pattern < 2) { startDestiRowIndex = 18; } else if (pattern == 2) { startDestiRowIndex = 17; } else { AddException(@"数据类型无效", true); if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; return; } try { //寻找目标统计文件 temp_fi = SearchForFile(filePath, strType, strMacSerial, strSensorSerial, existFile, out isArchieved); if (temp_fi == null) { if (isArchieved) { //不存在,复制当前记录过去 //1.合成新文件名,按新名另存,定义为temp_fi newName = DataUtility.DataUtility.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.统计新文件 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) { AddException(@"Excel文档无法打开", true); _sr.ExcelWorkbook.Saved = true; _eu.ExcelWorkbook.Saved = true; _sr.TryClose(); _eu.TryClose(); AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; return; } _eu.ExcelApp.DisplayAlerts = false; _eu.ExcelApp.AlertBeforeOverwriting = false; CopyData(_sr, stateIndex, _eu, pattern, certId, needFix, shouldFix, startDestiRowIndex, out checkClear); if (checkClear) { _eu.ExcelWorkbook.Save(); _eu.ExcelWorkbook.Saved = true; 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 { AddException(@"文件不存在:" + temp_fi.FullName, true); fileText = filePath; } } catch (Exception ex) { 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); AddLog(@"***************************************************************", true); exceptionNum = 0; dataerrorNum = 0; } else { if (dataerrorNum > 0) { AddLog(@"该文件只有非致命错误,已经成功处理", true); AddLog(@"***************************************************************", true); exceptionNum = 0; dataerrorNum = 0; } File.Delete(filePath); } } }
/// <summary> /// 统计,采集真实的规范列表 /// </summary> /// <param name="eu"></param> /// <param name="pattern"></param> /// <returns></returns> public bool Statistic(ExcelUtility eu, int pattern) { int stateIndex = -1; int lineIndex = 0; int limitPosition = 14; bool needInitialState = true; bool checkClear; bool firstTime = true; bool canPass = true, canPass1 = false; string insNumber = ""; Dictionary<int, string> exSheets = null; Dictionary<int, string> cert = new Dictionary<int, string>(); Dictionary<DoseCriterion, int> existCriList = new Dictionary<DoseCriterion, int>(); //1.初始化:规范统计sheet结构,写入固定内容 stateIndex = eu.InitialStatisticSheet(eu.ExcelWorkbook, out exSheets, out needInitialState, out existCriList, out limitPosition, out checkClear); if (!checkClear) { return false; } if (!needInitialState) { eu.WriteStateTitle(eu.ExcelWorkbook, pattern, stateIndex, existCriList, limitPosition); switch (pattern) { case 0: //1.搬运数据:先根据年份排序,然后复制数据 int[] indexDose = new int[exSheets.Count]; lineIndex = 0; foreach (int item in exSheets.Keys) { indexDose[lineIndex] = item; lineIndex++; } DataUtility.DataUtility.QuickSort(indexDose, exSheets, 0, indexDose.Length - 1); lineIndex = 8; for (int i = 0; i < exSheets.Count; i++) { cert.Add(lineIndex, exSheets[indexDose[i]]); eu.CopyDoseOneYearData(eu.ExcelWorkbook, indexDose[i], stateIndex, lineIndex, exSheets[indexDose[i]], limitPosition, existCriList, out insNumber); if (firstTime) { firstTime = false; } lineIndex++; } //2.分析数据 if (exSheets.Count > 1) { eu.WriteValue(eu.ExcelWorkbook, stateIndex, lineIndex, 1, @"重复性", out checkClear); eu.ExcelWorkbook.Save(); eu.ExcelWorkbook.Saved = true; //计算平均值和年稳定性 canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 4, cert, "RQR2(40kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 6, cert, "RQR3(50kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 8, cert, "RQR4(60kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 10, cert, "RQR5(70kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 12, cert, "RQR6(80kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 14, cert, "RQR7(90kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 16, cert, "RQR8(100kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 18, cert, "RQR9(120kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 20, cert, "RQR(140kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 22, cert, "RQR10(150kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; } break; case 1: //TODO: CT统计代码 //1.搬运数据:先根据年份排序,然后复制数据 //indexCT[数字序号]=页序号 //exSheets[页序号]=证书编号 int[] indexCT = new int[exSheets.Count]; lineIndex = 0; foreach (int item in exSheets.Keys) { indexCT[lineIndex] = item; lineIndex++; } DataUtility.DataUtility.QuickSort(indexCT, exSheets, 0, indexCT.Length - 1); lineIndex = 8; for (int i = 0; i < exSheets.Count; i++) { cert.Add(lineIndex, exSheets[indexCT[i]]); //TODO:复制数据格式 eu.CopyCTOneYearData(eu.ExcelWorkbook, indexCT[i], stateIndex, i + 8, exSheets[indexCT[i]], limitPosition, existCriList, out insNumber); if (firstTime) { firstTime = false; } lineIndex++; } //2.分析数据 if (exSheets.Count > 1) { eu.WriteValue(eu.ExcelWorkbook, stateIndex, lineIndex, 1, @"重复性", out checkClear); eu.ExcelWorkbook.Save(); eu.ExcelWorkbook.Saved = true; //计算平均值和年稳定性 canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 4, cert, "RQR2(40kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 6, cert, "RQR3(50kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 8, cert, "RQR4(60kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 10, cert, "RQR5(70kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 12, cert, "RQR6(80kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 14, cert, "RQR7(90kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 16, cert, "RQR8(100kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 18, cert, "RQR9(120kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 20, cert, "RQR(140kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 22, cert, "RQR10(150kV)", exSheets.Count > 1, true, -1); canPass = canPass1 && canPass; } break; case 2: //TODO:KV统计代码 break; } } eu.ExcelWorkbook.Save(); return canPass; }
/// <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) { AddException(@"Excel文档无法打开", true); _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; return; } _sr.ExcelApp.DisplayAlerts = false; _sr.ExcelApp.AlertBeforeOverwriting = false; try { //TODO: 单独校验处校验是否可选 fileName = _sr.Verification(_sr.ExcelWorkbook, true, pattern, out pass); } catch (Exception ex) { 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) { AddLog(@"***************************************************************", true); problemFilesList.Add(filePath); exceptionNum = 0; dataerrorNum = 0; } } }
/// <summary> /// 统计,使用固定的规范列表 /// </summary> /// <param name="eu"></param> /// <param name="pattern"></param> /// <param name="Perfect"></param> /// <param name="strCompany"></param> /// <param name="strType"></param> /// <param name="tempName"></param> /// <returns></returns> public bool Statistic(ExcelUtility eu, int pattern, bool Perfect, string strCompany, string strType, string tempName, string certstr) { int stateIndex = -1; int lineIndex = 0; int limitPosition = 24; int newline = -1; bool needInitialState = true; bool checkClear; bool firstTime = true; bool canPass = true, canPass1 = false; string insNumber = ""; Dictionary<int, string> exSheets = null; Dictionary<int, string> cert = new Dictionary<int, string>(); Dictionary<DoseCriterion, int> existDoseCriList = new Dictionary<DoseCriterion, int>() { { DoseCriterion.RQR2_40 , DoseCriterion.RQR2_40.Column }, { DoseCriterion.RQR3_50 , DoseCriterion.RQR3_50.Column }, { DoseCriterion.RQR4_60 , DoseCriterion.RQR4_60.Column }, { DoseCriterion.RQR5_70 , DoseCriterion.RQR5_70.Column }, { DoseCriterion.RQR6_80 , DoseCriterion.RQR6_80.Column }, { DoseCriterion.RQR7_90 , DoseCriterion.RQR7_90.Column }, { DoseCriterion.RQR8_100 , DoseCriterion.RQR8_100.Column }, { DoseCriterion.RQR9_120 , DoseCriterion.RQR9_120.Column }, { DoseCriterion.RQR_140 , DoseCriterion.RQR_140.Column }, { DoseCriterion.RQR10_150, DoseCriterion.RQR10_150.Column} }; Dictionary<KVCriterion, int> existKVCriList = new Dictionary<KVCriterion, int>() { { KVCriterion.RQR2_40 , KVCriterion.RQR2_40.Column }, { KVCriterion.RQR3_50 , KVCriterion.RQR3_50.Column }, { KVCriterion.RQR4_60 , KVCriterion.RQR4_60.Column }, { KVCriterion.RQR5_70 , KVCriterion.RQR5_70.Column }, { KVCriterion.RQR6_80 , KVCriterion.RQR6_80.Column }, { KVCriterion.RQR7_90 , KVCriterion.RQR7_90.Column }, { KVCriterion.RQR8_100 , KVCriterion.RQR8_100.Column }, { KVCriterion.RQR9_120 , KVCriterion.RQR9_120.Column }, { KVCriterion.RQR_140 , KVCriterion.RQR_140.Column }, { KVCriterion.RQR10_150, KVCriterion.RQR10_150.Column} }; //1.初始化:规范统计sheet结构,写入固定内容 stateIndex = eu.InitialStatisticSheet(eu.ExcelWorkbook, out exSheets, out needInitialState, out checkClear); if (!checkClear) { return false; } switch (pattern) { case 0: if (needInitialState) { eu.WriteStateTitle(eu.ExcelWorkbook, pattern, stateIndex, existDoseCriList, limitPosition); //写入送校单位 eu.WriteValue(eu.ExcelWorkbook, stateIndex, 2, 2, strCompany, out checkClear); //写入仪器名称 eu.WriteValue(eu.ExcelWorkbook, stateIndex, 3, 2, strType, out checkClear); } //1.搬运数据:先根据年份排序,然后复制数据 int[] indexDose = new int[exSheets.Count]; foreach (int item in exSheets.Keys) { indexDose[lineIndex] = item; lineIndex++; } DataUtility.DataUtility.QuickSort(indexDose, exSheets, 0, indexDose.Length - 1); lineIndex = 8; for (int i = 0; i < exSheets.Count; i++) { cert.Add(lineIndex, exSheets[indexDose[i]]); eu.CopyDoseOneYearData(eu.ExcelWorkbook, indexDose[i], stateIndex, lineIndex, exSheets[indexDose[i]], limitPosition, existDoseCriList, out insNumber); if (exSheets[indexDose[i]] == certstr) { newline = lineIndex; } if (firstTime && Perfect) { eu.DesiredName = tempName; firstTime = false; } lineIndex++; } //2.分析数据 if (exSheets.Count > 1) { eu.WriteValue(eu.ExcelWorkbook, stateIndex, lineIndex, 1, @"重复性", out checkClear); eu.ExcelWorkbook.Save(); eu.ExcelWorkbook.Saved = true; //计算平均值和年稳定性 canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 4, cert, "RQR2(40kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 6, cert, "RQR3(50kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 8, cert, "RQR4(60kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 10, cert, "RQR5(70kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 12, cert, "RQR6(80kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 14, cert, "RQR7(90kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 16, cert, "RQR8(100kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 18, cert, "RQR9(120kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 20, cert, "RQR(140kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 22, cert, "RQR10(150kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; } break; case 1: //TODO: CT统计代码 if (needInitialState) { eu.WriteStateTitle(eu.ExcelWorkbook, pattern, stateIndex, existDoseCriList, limitPosition); //写入送校单位 eu.WriteValue(eu.ExcelWorkbook, stateIndex, 2, 2, strCompany, out checkClear); //写入仪器名称 eu.WriteValue(eu.ExcelWorkbook, stateIndex, 3, 2, strType, out checkClear); } //1.搬运数据:先根据年份排序,然后复制数据 int[] indexCT = new int[exSheets.Count]; foreach (int item in exSheets.Keys) { indexCT[lineIndex] = item; lineIndex++; } DataUtility.DataUtility.QuickSort(indexCT, exSheets, 0, indexCT.Length - 1); lineIndex = 8; for (int i = 0; i < exSheets.Count; i++) { cert.Add(lineIndex, exSheets[indexCT[i]]); //TODO:复制数据格式 eu.CopyCTOneYearData(eu.ExcelWorkbook, indexCT[i], stateIndex, lineIndex, exSheets[indexCT[i]], limitPosition, existDoseCriList, out insNumber); if (exSheets[indexCT[i]] == certstr) { newline = lineIndex; } if (firstTime && Perfect) { eu.DesiredName = tempName; firstTime = false; } lineIndex++; } //2.分析数据 if (exSheets.Count > 1) { eu.WriteValue(eu.ExcelWorkbook, stateIndex, lineIndex, 1, @"重复性", out checkClear); eu.ExcelWorkbook.Save(); eu.ExcelWorkbook.Saved = true; //计算平均值和年稳定性 canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 4, cert, "RQR2(40kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 6, cert, "RQR3(50kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 8, cert, "RQR4(60kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 10, cert, "RQR5(70kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 12, cert, "RQR6(80kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 14, cert, "RQR7(90kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 16, cert, "RQR8(100kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 18, cert, "RQR9(120kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 20, cert, "RQR(140kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsCTDoseOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 22, cert, "RQR10(150kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; } break; case 2: //TODO:KV统计代码 limitPosition = 44; if (needInitialState) { eu.WriteStateTitle(eu.ExcelWorkbook, pattern, stateIndex, existKVCriList, limitPosition); //写入送校单位 eu.WriteValue(eu.ExcelWorkbook, stateIndex, 2, 2, strCompany, out checkClear); //写入仪器名称 eu.WriteValue(eu.ExcelWorkbook, stateIndex, 3, 2, strType, out checkClear); } //1.搬运数据:先根据年份排序,然后复制数据 int[] indexKV = new int[exSheets.Count]; foreach (int item in exSheets.Keys) { indexKV[lineIndex] = item; lineIndex++; } DataUtility.DataUtility.QuickSort(indexKV, exSheets, 0, indexKV.Length - 1); lineIndex = 8; for (int i = 0; i < exSheets.Count; i++) { cert.Add(lineIndex, exSheets[indexKV[i]]); //TODO:复制数据格式 if (exSheets[indexKV[i]] == certstr) { eu.CopyKVOneYearData(eu.ExcelWorkbook, indexKV[i], stateIndex, lineIndex, exSheets[indexKV[i]], limitPosition, existKVCriList, true, out insNumber); } else { eu.CopyKVOneYearData(eu.ExcelWorkbook, indexKV[i], stateIndex, lineIndex, exSheets[indexKV[i]], limitPosition, existKVCriList, false, out insNumber); } if (exSheets[indexKV[i]] == certstr) { newline = lineIndex; } if (firstTime && Perfect) { eu.DesiredName = tempName; firstTime = false; } lineIndex++; } //2.分析数据 eu.ExcelWorkbook.Save(); eu.ExcelWorkbook.Saved = true; //计算平均值和年稳定性 canPass1 = eu.StatisticsKVOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 4, cert, "RQR2(40kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsKVOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 8, cert, "RQR3(50kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsKVOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 12, cert, "RQR4(60kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsKVOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 16, cert, "RQR5(70kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsKVOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 20, cert, "RQR6(80kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsKVOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 24, cert, "RQR7(90kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsKVOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 28, cert, "RQR8(100kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsKVOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 32, cert, "RQR9(120kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsKVOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 36, cert, "RQR(140kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; canPass1 = eu.StatisticsKVOneColumn(eu.ExcelWorkbook, stateIndex, lineIndex, 40, cert, "RQR10(150kV)", exSheets.Count > 1, true, newline); canPass = canPass1 && canPass; break; } eu.ExcelWorkbook.Save(); return canPass; }
public void CopyOneData(ExcelUtility sourceEx, int sourceIndex, ExcelUtility destiEx, int destiIndex, int rowIndex, int columnIndex, string style, bool checkDouble, out bool sc) { bool checkClear; double temp_double = 0.0; string text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, rowIndex, columnIndex, out checkClear).Trim(); bool isDouble = double.TryParse(text, out temp_double); if (text == "" || !isDouble) { sc = false; return; } if (style == "") { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, rowIndex, columnIndex, text, out checkClear); } else { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, rowIndex, columnIndex, text, style, out checkClear); } if (checkClear) { sc = true; } else { sc = false; } }
/// <summary> /// /// </summary> /// <param name="sourceEx"></param> /// <param name="stateIndex"></param> public void TypeStandardize(ExcelUtility sourceEx, int stateIndex) { string str = sourceEx.GetMergeContent(sourceEx.ExcelWorkbook, stateIndex, 5, 5, 5, 6, @"型号:", out checkClear).Trim().ToLower(); bool DoNotHave = true; if (str.Contains(@"unfors") || sourceEx.GetMergeContent(sourceEx.ExcelWorkbook, stateIndex, 5, 9, 5, 10, @"厂家:", out checkClear).Trim().ToLower().Contains(@"unfors")) { sourceEx.WriteValue(sourceEx.ExcelWorkbook, stateIndex, 5, 5, @"型号:", out checkClear); sourceEx.WriteValue(sourceEx.ExcelWorkbook, stateIndex, 5, 6, @"Xi", out checkClear); sourceEx.ExcelWorkbook.Save(); sourceEx.ExcelWorkbook.Saved = true; str = sourceEx.GetMergeContent(sourceEx.ExcelWorkbook, stateIndex, 5, 5, 5, 6, @"型号:", out checkClear).Trim().ToLower(); } if (str.Contains(@"baracuda")) { sourceEx.WriteValue(sourceEx.ExcelWorkbook, stateIndex, 5, 5, @"型号:", out checkClear); sourceEx.WriteValue(sourceEx.ExcelWorkbook, stateIndex, 5, 6, @"Barracuda", out checkClear); sourceEx.ExcelWorkbook.Save(); sourceEx.ExcelWorkbook.Saved = true; str = sourceEx.GetMergeContent(sourceEx.ExcelWorkbook, stateIndex, 5, 5, 5, 6, @"型号:", out checkClear).Trim().ToLower(); } if (str.Contains(@"prianha")) { sourceEx.WriteValue(sourceEx.ExcelWorkbook, stateIndex, 5, 5, @"型号:", out checkClear); sourceEx.WriteValue(sourceEx.ExcelWorkbook, stateIndex, 5, 6, @"Piranha", out checkClear); sourceEx.ExcelWorkbook.Save(); sourceEx.ExcelWorkbook.Saved = true; str = sourceEx.GetMergeContent(sourceEx.ExcelWorkbook, stateIndex, 5, 5, 5, 6, @"型号:", out checkClear).Trim().ToLower(); } foreach (string item in InstrumentType.AllTypes) { if (item.ToLower() == str) { DoNotHave = false; break; } } if (DoNotHave) { AddException(@"仪器类型可能出现手误", true); AddLog("错误47", " 仪器类型:" + str, true); } }
public void CopyOneData(ExcelUtility sourceEx, int sourceIndex, ExcelUtility destiEx, int destiIndex, int rowIndex, int columnIndex, int new_row, int new_column, string[] pre, string style, out bool sc) { bool checkClear; string text = sourceEx.GetMergeContent(sourceEx.ExcelWorkbook, sourceIndex, rowIndex, columnIndex, new_row, new_column, pre, out checkClear); if (text == "") { sc = false; return; } if (style == "") { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, new_row, new_column, text, out checkClear); } else { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, new_row, new_column, text, style, out checkClear); } if (checkClear) { sc = true; } else { sc = false; } }
/// <summary> /// 生成证书和pdf记录 /// </summary> /// <param name="excel">excel记录文件</param> /// <param name="sourceIndex">excel记录页索引号</param> /// <param name="wordPath">证书模板文件</param> /// <param name="savePath">证书记录存储文件夹</param> /// <param name="pdfPath">pdf记录存储文件夹</param> /// <param name="success">成功信号</param> public void GenerateCert(ExcelUtility excel, int sourceIndex, int pattern, string wordPath, string savePath, string pdfPath, string tempFolder, bool shouldFix, out bool success) { //GenerateCert(_sr, stateIndex, path, pS.CertFolder, pS.PDFDataFolder, out success); try { WordUtility wu = new WordUtility(wordPath, out success); if (!success) { AddException("Word文档打开失败", true); return; } string stemp1 = excel.GetText(excel.ExcelWorkbook, sourceIndex, "L2", out success); object otemp1; string wdName = "DYjl" + stemp1 + Path.GetExtension(wordPath); string pdfName = "DYjl" + stemp1 + "_" + excel.GetText(excel.ExcelWorkbook, sourceIndex, "B4", out success) + ".pdf"; switch (pattern) { case 0: //Dose //识别半导体和电离室,对证书模板做对应改动 if (shouldFix) { wu.WriteValue(wu.WordDocument, "M_JZJGR", ",并修正到标准温度、气压"); } //1.普通复制(单位),剂量和CT独有 wu.WriteValue(wu.WordDocument, "M_DW", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "M12", out success)); //2.读取小数点后三位(剂量值和校准因子),剂量和CT独有 wu.WriteDataValue(wu.WordDocument, "M_NC1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "D24", out success), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_NC2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "F24", out success), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_NC3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "H24", out success), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_NC4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "J24", out success), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_NC5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "L24", out success), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_LY1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "D15", out success), "{0:F1}"); //小数点后一位 wu.WriteDataValue(wu.WordDocument, "M_LY2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "F15", out success), "{0:F1}"); //小数点后一位 wu.WriteDataValue(wu.WordDocument, "M_LY3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "H15", out success), "{0:F1}"); //小数点后一位 wu.WriteDataValue(wu.WordDocument, "M_LY4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "J15", out success), "{0:F1}"); //小数点后一位 wu.WriteDataValue(wu.WordDocument, "M_LY5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "L15", out success), "{0:F1}"); //小数点后一位 //3.普通复制(备注说明),剂量CT在B27,KV在B29 if (excel.GetRange(excel.ExcelWorkbook, sourceIndex, "B27", out success) == null || excel.GetRange(excel.ExcelWorkbook, sourceIndex, "B27", out success).ToString() == @"/") { wu.WriteValue(wu.WordDocument, "M_BZSM", "无"); } else { wu.WriteValue(wu.WordDocument, "M_BZSM", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "B27", out success)); } break; case 1: //CT //识别半导体和电离室,对证书模板做对应改动 if (shouldFix) { wu.WriteValue(wu.WordDocument, "M_JZTJ3", Environment.NewLine + "3、电离室戴保护管在辐射野中全照射。"); wu.WriteValue(wu.WordDocument, "M_JZJGR", ",并修正到标准温度、气压"); } //备注说明普通复制,剂量CT在B27,KV在B29 if (excel.GetRange(excel.ExcelWorkbook, sourceIndex, "B27", out success) == null || excel.GetRange(excel.ExcelWorkbook, sourceIndex, "B27", out success).ToString() == @"/") { wu.WriteValue(wu.WordDocument, "M_BZSM", "无"); } else { wu.WriteValue(wu.WordDocument, "M_BZSM", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "B27", out success)); } //校准因子单位写入 if (excel.GetRange(excel.ExcelWorkbook, sourceIndex, "M27", out success) == null || excel.GetRange(excel.ExcelWorkbook, sourceIndex, "M27", out success).ToString().ToLower() == "false") { wu.WriteValue(wu.WordDocument, "M_JZYZ", "无量纲"); } else { wu.WriteValue(wu.WordDocument, "M_JZYZ", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "M27", out success)); } //1.普通复制(单位),剂量和CT独有 wu.WriteValue(wu.WordDocument, "M_DW", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "M12", out success)); //2.读取小数点后三位(剂量值和校准因子),剂量和CT独有 wu.WriteDataValue(wu.WordDocument, "M_NC1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "D24", out success), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_NC2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "F24", out success), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_NC3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "H24", out success), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_NC4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "J24", out success), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_NC5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "L24", out success), "{0:F3}"); //小数点后三位 wu.WriteDataValue(wu.WordDocument, "M_LY1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "D15", out success), "{0:F1}"); //小数点后一位 wu.WriteDataValue(wu.WordDocument, "M_LY2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "F15", out success), "{0:F1}"); //小数点后一位 wu.WriteDataValue(wu.WordDocument, "M_LY3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "H15", out success), "{0:F1}"); //小数点后一位 wu.WriteDataValue(wu.WordDocument, "M_LY4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "J15", out success), "{0:F1}"); //小数点后一位 wu.WriteDataValue(wu.WordDocument, "M_LY5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "L15", out success), "{0:F1}"); //小数点后一位 break; case 2: //TODO: KV //1.普通复制(测试类型),KV独有 wu.WriteValue(wu.WordDocument, "M_CSLX", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "K12", out success)); //2.读取小数点后两位(实际峰值电压PPV和被测仪器显示值) wu.WriteDataValue(wu.WordDocument, "M_PPV1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "D14", out success), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_PPV2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "F14", out success), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_PPV3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "H14", out success), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_PPV4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "J14", out success), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_PPV5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "L14", out success), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_VALUE1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "D20", out success), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_VALUE2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "F20", out success), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_VALUE3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "H20", out success), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_VALUE4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "J20", out success), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_VALUE5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "L20", out success), "{0:F2}"); //小数点后两位 //3.两位小数的百分数(相对固有误差和过滤影响) wu.WriteDataValue(wu.WordDocument, "M_XDGYWC1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "D22", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_XDGYWC2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "F22", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_XDGYWC3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "H22", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_XDGYWC4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "J22", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_XDGYWC5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "L22", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX21", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "D82", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX31", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "E82", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX22", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "F82", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX32", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "G82", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX23", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "H82", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX33", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "I82", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX24", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "J82", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX34", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "K82", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX25", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "L82", out success), "{0:0.00%}"); //两位小数的百分数 wu.WriteDataValue(wu.WordDocument, "M_GLYX35", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "M82", out success), "{0:0.00%}"); //两位小数的百分数 //4.读取小数点后两位(辐照工作下限) wu.WriteDataValue(wu.WordDocument, "M_FFGZXX1", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "D52", out success), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_FFGZXX2", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "F52", out success), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_FFGZXX3", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "H52", out success), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_FFGZXX4", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "J52", out success), "{0:F2}"); //小数点后两位 wu.WriteDataValue(wu.WordDocument, "M_FFGZXX5", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "L52", out success), "{0:F2}"); //小数点后两位 //5.两位小数的百分数(80KV重复性) wu.WriteDataValue(wu.WordDocument, "M_BSKVCFX", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "B28", out success), "{0:0.00%}"); //两位小数的百分数 //6.普通复制(备注说明),剂量CT在B27,KV在B29 if (excel.GetRange(excel.ExcelWorkbook, sourceIndex, "B29", out success) == null || excel.GetRange(excel.ExcelWorkbook, sourceIndex, "B29", out success).ToString() == @"/") { wu.WriteValue(wu.WordDocument, "M_BZSM", "无"); } else { wu.WriteValue(wu.WordDocument, "M_BZSM", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "B29", out success)); } break; default: AddException("生成证书时指定了不存在的检定类型", true); break; } /// <summary> /// 类型1:普通复制 /// </summary> wu.WriteValue(wu.WordDocument, "M_NAME", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "B4", out success)); wu.WriteValue(wu.WordDocument, "M_SERIAL", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "F5", out success)); wu.WriteValue(wu.WordDocument, "M_PRODUCT", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "J5", out success)); wu.WriteValue(wu.WordDocument, "M_QIJU", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "B5", out success)); wu.WriteValue(wu.WordDocument, "M_LNGCH", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "D12", out success)); wu.WriteValue(wu.WordDocument, "M_DATE", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "K31", out success)); wu.WriteValue(wu.WordDocument, "M_STRESS", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "F4", out success)); wu.WriteValue(wu.WordDocument, "M_QIYA", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "J8", out success)); wu.WriteValue(wu.WordDocument, "M_TEMP", excel.GetRange(excel.ExcelWorkbook, sourceIndex, "K7", out success)); wu.WriteValue(wu.WordDocument, "M_ZHSH", stemp1); wu.WriteValue(wu.WordDocument, "M_ZHSH2", stemp1); wu.WriteValue(wu.WordDocument, "M_ZHSH3", stemp1); /// <summary> /// 类型2:百分比换算后复制 /// </summary> otemp1 = (object)excel.GetRange(excel.ExcelWorkbook, sourceIndex, "M7", out success).Value; if (otemp1 == null) { stemp1 = "/"; } else { stemp1 = string.Format("{0:F1}", float.Parse(otemp1.ToString()) * 100); } wu.WriteValue(wu.WordDocument, "M_SHIDU", stemp1); /// <summary> /// 类型3:仪器编号两段合并后复制 /// </summary> otemp1 = (object)excel.GetRange(excel.ExcelWorkbook, sourceIndex, "H5", out success).Value; if (otemp1 == null) { stemp1 = ""; } else { stemp1 = otemp1.ToString(); } otemp1 = (object)excel.GetRange(excel.ExcelWorkbook, sourceIndex, "L5", out success).Value; if (otemp1 == null) { if (stemp1 == "") { stemp1 = "/"; } } else if (otemp1.ToString() == "/") { if (stemp1 == "") { stemp1 = "/"; } } else { stemp1 = stemp1 + " + " + otemp1.ToString(); } wu.WriteValue(wu.WordDocument, "M_NUM", stemp1); //另存word和pdf wu.WordDocument.SaveAs2(Path.Combine(savePath, DataUtility.DataUtility.FileNameCleanName(wdName))); excel.SaveAsPDF(excel.ExcelWorkbook, Path.Combine(pdfPath, DataUtility.DataUtility.FileNameCleanName(pdfName)), tempFolder, out success); wu.WordDocument.Saved = true; wu.TryClose(); } catch (Exception ex) { success = false; AddException("生成证书时出现错误:" + ex.Message, true); } }
public 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) { bool checkClear; 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, DataUtility.DataUtility.PositionString(13, columnIndex), out checkClear); text = rr.Text.ToString().Trim(); rr = destiEx.GetRange(destiEx.ExcelWorkbook, destiIndex, DataUtility.DataUtility.PositionString(13, columnIndex), DataUtility.DataUtility.PositionString(13, columnIndex + 1), out checkClear); rr.Value2 = text; //标准值 if (standardRowIndex > 12) { rr = sourceEx.GetRange(sourceEx.ExcelWorkbook, sourceIndex, DataUtility.DataUtility.PositionString(standardRowIndex, columnIndex), out checkClear); text = rr.Text.ToString().Trim(); rr = destiEx.GetRange(destiEx.ExcelWorkbook, destiIndex, DataUtility.DataUtility.PositionString(16, columnIndex), DataUtility.DataUtility.PositionString(16, columnIndex + 1), out checkClear); 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, startSourceRowIndex + i, columnIndex, out checkClear); if (rr.Value2 == null || string.IsNullOrWhiteSpace(rr.Value2.ToString())) { sourceEx.WriteValue(sourceEx.ExcelWorkbook, sourceIndex, startSourceRowIndex + i, columnIndex, @"/", out checkClear); sourceEx.ExcelWorkbook.Save(); sourceEx.ExcelWorkbook.Saved = true; text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, startSourceRowIndex + i, columnIndex, out checkClear).Trim(); } else { text = rr.Value2.ToString().Trim(); } if (double.TryParse(text, out temp_double) || text == @"/") { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, startDestiRowIndex + i, columnIndex, text, out checkClear); if (text != @"/") { count++; daValue += temp_double; } } else { 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, startSourceRowIndex + i, columnIndex, out checkClear); if (rr.Value2 == null || string.IsNullOrWhiteSpace(rr.Value2.ToString())) { sourceEx.WriteValue(sourceEx.ExcelWorkbook, sourceIndex, startSourceRowIndex + i, columnIndex, @"/", out checkClear); sourceEx.ExcelWorkbook.Save(); sourceEx.ExcelWorkbook.Saved = true; text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, startSourceRowIndex + i, columnIndex, out checkClear).Trim(); } else { text = rr.Value2.ToString().Trim(); } if (double.TryParse(text, out temp_double) || text == @"/") { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, startDestiRowIndex + i, columnIndex, text, style, out checkClear); if (text != @"/") { count++; daValue += temp_double; } } else { 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 override void DoTheJob() { string output = jobParam.AutoOutputFolder; FixType fix = jobParam.FixType; bool needFix = jobParam.AutoFixType; string templateName = jobParam.DataTemplateFilePath; string macType = jobParam.MacType; bool success = true; int templateIndex = -1; MSExcel.Worksheet ws1 = null; WordUtility _wu = new WordUtility(filePath, out success); if (!success) { LogHelper.AddException("Word文档打开失败", true); return; } string tempZhsh = _wu.GetText(_wu.WordDocument, 3);//L2:证书编号 string tempName = _wu.GetText(_wu.WordDocument, 7);//B4:送校单位 string tempQiju = _wu.GetText(_wu.WordDocument, 11);//B5:仪器名称 string tempSerial = _wu.GetText(_wu.WordDocument, 15).Trim();//F5:仪器型号 _wu.TryClose(); if (tempSerial != "" && tempSerial != macType) { LogHelper.AddDataError("证书中包含的仪器型号与指定的仪器型号不符" + Environment.NewLine + "证书仪器型号: " + tempSerial + Environment.NewLine + "指定仪器型号: " + macType, true); } string str = tempZhsh.Substring(8); string strSavename = PathExt.PathCombine(output, tempName + "_" + macType + "_" + str + ".xlsx"); if (File.Exists(strSavename)) { if (FormOperator.MessageBox_Show_YesNo(@"文件已存在,是否覆盖?" + Environment.NewLine + strSavename, "提示")) { File.Delete(strSavename); } else { success = false; return; } } File.Copy(templateName, strSavename); ExcelUtility _sr = new ExcelUtility(strSavename, out success); if (!success) { if (_sr != null && _sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } LogHelper.AddException(@"Excel文档无法打开", true); LogHelper.AddProblemFilesAndReset(filePath); return; } _sr.ExcelApp.DisplayAlerts = false; _sr.ExcelApp.AlertBeforeOverwriting = false; try { foreach (MSExcel.Worksheet item in _sr.ExcelWorkbook.Sheets) { if (item.Name == @"标准模板") { templateIndex = item.Index; } else if (item.Name.Contains(@"标准模板")) { LogHelper.AddException(@"发现多余的标准模板", true); } } LogHelper.State.Push("找到标准模板:" + templateIndex.ToString()); if (templateIndex > -1) { ws1 = (MSExcel.Worksheet)_sr.ExcelWorkbook.Sheets[templateIndex]; ws1.Copy(ws1, Type.Missing); ws1 = (MSExcel.Worksheet)_sr.ExcelWorkbook.Sheets[templateIndex]; if (!ws1.Name.Contains(@"标准模板")) { LogHelper.AddException(@"标准模板复制出错", true); success = false; return; } else { ws1.Name = str; } LogHelper.State.Push("复制标准模板完成"); } else { LogHelper.AddException(@"找不到模板excel中的标准模板页", true); } _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, new ExcelPosition(4, 2), tempName); _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, new ExcelPosition(5, 6), macType); _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, new ExcelPosition(5, 2), tempQiju); _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, new ExcelPosition(2, 12), str); LogHelper.State.Push("写入4个信息"); _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, new ExcelPosition(8, 13), EnumExt.GetDescriptionFromEnumValue<FixType>(fix)); LogHelper.State.Push("写入修正信息"); if (!needFix) { //电离室->半导体 MSExcel.Range rr = _sr.GetRange(_sr.ExcelWorkbook, ws1.Index, new ExcelPosition("L8")); rr.FormulaLocal = ""; rr.Formula = ""; rr.FormulaArray = ""; LogHelper.State.Push("不修正时清空公式"); _sr.WriteValue(_sr.ExcelWorkbook, ws1.Index, new ExcelPosition(8, 12), "1.000000", "@"); LogHelper.State.Push("写入1"); } LogHelper.State.Push("准备写入记录者图片"); //写入记录者 _sr.WriteImage(_sr.ExcelWorkbook, ws1.Index, new ExcelPosition(29, 7), PathExt.PathCombine(ProgramConfiguration.ProgramFolder, person.Path), 45, 28); LogHelper.State.Push("写完记录者图片"); _sr.ExcelWorkbook.Save(); LogHelper.State.Push("保存完毕"); } catch (Exception ex) { Log.LogHelper.AddException("生成证书时遇到异常:" + ex.Message, true); Log.LogHelper.AddLog("执行位置:" + LogHelper.State.Peek(), true); } finally { //关闭Excel if (_sr.ExcelWorkbook != null) { _sr.ExcelWorkbook.Saved = true; _sr.TryClose(); } //有重大失误的情况下报错,没有失误就删除源word文件 if (LogHelper.HasException) { LogHelper.AddProblemFilesAndReset(filePath); } else { File.Delete(filePath); } } }