private void FrmCompare_Load(object sender, EventArgs e) { BLL.query_detail bll = new BLL.query_detail(); Model.query_detail model = bll.GetModel(this.uBusinesControl1.query_id); this.Text = String.Format("数据比对(姓名:{0},工作单位:{1},职务:{2}", model.full_name, model.work_unit, model.post); }
//导出个人比对表到文件 public string PersonToFile(string path, int query_id) { IWorkbook workbook; try { using (FileStream file = new FileStream(System.Environment.CurrentDirectory + "\\Templates\\比对表.xlsx", FileMode.Open, FileAccess.Read)) { workbook = WorkbookFactory.Create(file); } ISheet sheet = workbook.GetSheetAt(0); BLL.query_detail bDetail = new BLL.query_detail(); Model.query_detail mDetail = bDetail.GetModel(query_id); int[] cellpos = NPOI_CellChange("B3"); ICell cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); IRow row; cell.SetCellValue(mDetail.full_name); cellpos = NPOI_CellChange("E3"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mDetail.work_unit); cellpos = NPOI_CellChange("G3"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mDetail.post); BLL.compare_result bResult = new BLL.compare_result(); //护照比对情况 Model.compare_result mResult = bResult.GetModal(string.Format("query_id={0} and result_type='护照'", query_id)); if (mResult != null) { cellpos = NPOI_CellChange("C5"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.report_str.Replace("\r\n", "")); cellpos = NPOI_CellChange("D5"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.feedback_str.Replace("\r\n", "")); cellpos = NPOI_CellChange("F5"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.compare_str.Replace("\r\n", "")); if (mResult.report_str.Length < 30 && mResult.feedback_str.Length < 30 && mResult.compare_str.Length < 30) { row = sheet.GetRow(cellpos[0]); row.Height = 25 * 20; } } //港澳通行证比对情况 mResult = bResult.GetModal(string.Format("query_id={0} and result_type='港澳台通行证'", query_id)); if (mResult != null) { cellpos = NPOI_CellChange("C6"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.report_str.Replace("\r\n", "")); cellpos = NPOI_CellChange("D6"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.feedback_str.Replace("\r\n", "")); cellpos = NPOI_CellChange("F6"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.compare_str.Replace("\r\n", "")); if (mResult.report_str.Length < 30 && mResult.feedback_str.Length < 30 && mResult.compare_str.Length < 30) { row = sheet.GetRow(cellpos[0]); row.Height = 50 * 20; } } //出国比对情况 mResult = bResult.GetModal(string.Format("query_id={0} and result_type='出国'", query_id)); if (mResult != null) { cellpos = NPOI_CellChange("C7"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.report_str.Replace("\r\n", "")); cellpos = NPOI_CellChange("D7"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.feedback_str.Replace("\r\n", "")); cellpos = NPOI_CellChange("F7"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.compare_str.Replace("\r\n", "")); if (mResult.report_str.Length < 15 && mResult.feedback_str.Length < 15 && mResult.compare_str.Length < 15) { row = sheet.GetRow(cellpos[0]); row.Height = 25 * 20; } } //出境比对情况 mResult = bResult.GetModal(string.Format("query_id={0} and result_type='出境'", query_id)); if (mResult != null) { cellpos = NPOI_CellChange("C8"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.report_str.Replace("\r\n", "")); cellpos = NPOI_CellChange("D8"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.feedback_str.Replace("\r\n", "")); cellpos = NPOI_CellChange("F8"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.compare_str.Replace("\r\n", "")); if (mResult.report_str.Length < 15 && mResult.feedback_str.Length < 15 && mResult.compare_str.Length < 15) { row = sheet.GetRow(cellpos[0]); row.Height = 25 * 20; } } //在国外连续生活一年以上比对情况 mResult = bResult.GetModal(string.Format("query_id={0} and result_type='国外生活'", query_id)); if (mResult != null) { cellpos = NPOI_CellChange("C9"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.report_str.Replace("\r\n", "")); cellpos = NPOI_CellChange("D9"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.feedback_str.Replace("\r\n", "")); cellpos = NPOI_CellChange("F9"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.compare_str.Split('|')[0].Replace("\r\n", "")); if (mResult.report_str.Length < 15 && mResult.feedback_str.Length < 15 && mResult.compare_str.Length < 15) { row = sheet.GetRow(cellpos[0]); row.Height = 25 * 20; } } //房产比对情况 mResult = bResult.GetModal(string.Format("query_id={0} and result_type='房产'", query_id)); if (mResult != null) { cellpos = NPOI_CellChange("C10"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.report_str); cellpos = NPOI_CellChange("D10"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.feedback_str); cellpos = NPOI_CellChange("F10"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.compare_str); if (mResult.report_str.Length < 15 && mResult.feedback_str.Length < 15 && mResult.compare_str.Length < 15) { row = sheet.GetRow(cellpos[0]); row.Height = 25 * 20; } } //股票比对情况 mResult = bResult.GetModal(string.Format("query_id={0} and result_type='股票'", query_id)); if (mResult != null) { cellpos = NPOI_CellChange("C11"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.report_str); cellpos = NPOI_CellChange("D11"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.feedback_str); cellpos = NPOI_CellChange("F11"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.compare_str); if (mResult.report_str.Length < 15 && mResult.feedback_str.Length < 15 && mResult.compare_str.Length < 15) { row = sheet.GetRow(cellpos[0]); row.Height = 25 * 20; } } //基金比对情况 mResult = bResult.GetModal(string.Format("query_id={0} and result_type='基金'", query_id)); if (mResult != null) { cellpos = NPOI_CellChange("C11"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(cell.StringCellValue + "\r\n" + mResult.report_str); cellpos = NPOI_CellChange("D11"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(cell.StringCellValue + "\r\n" + mResult.feedback_str); cellpos = NPOI_CellChange("F11"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); if (!mResult.compare_str.Contains("基本一致")) { if (cell.StringCellValue.Contains("基本一致")) { cell.SetCellValue(mResult.compare_str); } else { cell.SetCellValue(cell.StringCellValue + "\r\n" + mResult.compare_str); } } if (mResult.report_str.Length < 15 && mResult.feedback_str.Length < 15 && mResult.compare_str.Length < 15) { row = sheet.GetRow(cellpos[0]); row.Height = 25 * 20; } } //保险比对情况 mResult = bResult.GetModal(string.Format("query_id={0} and result_type='保险'", query_id)); if (mResult != null) { cellpos = NPOI_CellChange("C12"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.report_str); cellpos = NPOI_CellChange("D12"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.feedback_str); cellpos = NPOI_CellChange("F12"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.compare_str); if (mResult.report_str.Length < 15 && mResult.feedback_str.Length < 15 && mResult.compare_str.Length < 15) { row = sheet.GetRow(cellpos[0]); row.Height = 25 * 20; } } //工商比对情况 mResult = bResult.GetModal(string.Format("query_id={0} and result_type='工商'", query_id)); if (mResult != null) { cellpos = NPOI_CellChange("C13"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.report_str); cellpos = NPOI_CellChange("D13"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.feedback_str); cellpos = NPOI_CellChange("F13"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cell.SetCellValue(mResult.compare_str); if (mResult.report_str.Length < 15 && mResult.feedback_str.Length < 15 && mResult.compare_str.Length < 15) { row = sheet.GetRow(cellpos[0]); row.Height = 25 * 20; } } //认定比对结果 cellpos = NPOI_CellChange("C14"); cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); cellpos = NPOI_CellChange("C15"); ICell cell2 = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]); if (!mDetail.is_match) { DataSet ds = bResult.GetList(string.Format("query_id={0} and compare_str not like ('%基本一致%')", query_id)); cell.SetCellValue("比对结果不一致:\r\n"); string cellValue = ""; int rowcount = 6;//单元格字符行数,用于估算行高 for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { cellValue += (i + 1).ToString() + "、" + ds.Tables[0].Rows[i]["compare_str"].ToString().Replace("\r\n", "") + "\r\n"; } cell2.SetCellValue(cell2.StringCellValue.Replace("{MatchStr}", cellValue)); } else { cell.SetCellValue(EqualStr); cell2.SetCellValue(cell2.StringCellValue.Replace("{MatchStr}", "")); } cell2.SetCellValue(cell2.StringCellValue.Replace("{CompareDate}", DateTime.Now.ToLongDateString())); using (FileStream fileStream = File.Open(path, FileMode.Create, FileAccess.Write)) { workbook.Write(fileStream); fileStream.Close(); } return("OK"); } catch (Exception e) { return(e.Message); } }