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);
}
}
