//将比对结果添加到汇总表
public string CollectToFile(string path, int list_id)
{
int rowIndex = 0; //excel新建行的行号
IWorkbook workbook;
IRow row;
ICell cell;
ICellStyle style, style2;
//HSSFCellStyle celStyle = getCellStyle();
try
{
using (FileStream file = new FileStream(System.Environment.CurrentDirectory + "\\Templates\\汇总表.xlsx", FileMode.Open, FileAccess.Read))
{
workbook = WorkbookFactory.Create(file);
}
if (workbook is HSSFWorkbook)
{
style = ((HSSFWorkbook)workbook).CreateCellStyle();
style2 = ((HSSFWorkbook)workbook).CreateCellStyle();
}
else
{
style = ((XSSFWorkbook)workbook).CreateCellStyle();
style2 = ((XSSFWorkbook)workbook).CreateCellStyle();
}
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style.BottomBorderColor = 128;
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.WrapText = true;
style2.CloneStyleFrom(style);
style2.Alignment = HorizontalAlignment.Left;
ISheet sheet = workbook.GetSheetAt(0);
BLL.query_detail bDetail = new BLL.query_detail();
List <Model.query_detail> list = bDetail.GetModelList(String.Format("where list_id={0} and parent_id=0", list_id));
BLL.compare_result bResult = new BLL.compare_result();
for (int i = 0; i < list.Count; i++)
{
if (list[i].is_compare)
{
style.FillBackgroundColor = 128;
}
else
{
style.FillBackgroundColor = 10;
}
rowIndex = 3 + i;
row = sheet.CreateRow(rowIndex);
for (int j = 0; j < 17; j++)
{
cell = row.CreateCell(j);
cell.CellStyle = style;
}
sheet.GetRow(rowIndex).GetCell(6).CellStyle = style2;
sheet.GetRow(rowIndex).GetCell(8).CellStyle = style2;
sheet.GetRow(rowIndex).GetCell(10).CellStyle = style2;
sheet.GetRow(rowIndex).GetCell(12).CellStyle = style2;
sheet.GetRow(rowIndex).GetCell(14).CellStyle = style2;
cell = sheet.GetRow(rowIndex).GetCell(0);
cell.SetCellValue(i + 1);
cell = sheet.GetRow(rowIndex).GetCell(2);
cell.SetCellValue(list[i].full_name);
cell = sheet.GetRow(rowIndex).GetCell(3);
cell.SetCellValue(list[i].work_unit);
cell = sheet.GetRow(rowIndex).GetCell(4);
cell.SetCellValue(list[i].post);
List <Model.compare_result> mResult = bResult.GetModelList(string.Format("query_id={0}", list[i].id));
foreach (Model.compare_result item in mResult)
{
switch (item.result_type)
{
case "护照":
case "港澳台通行证":
case "出国":
case "出境":
case "国外生活":
if (0 <= item.compare_str.IndexOf("基本一致"))
{
cell = sheet.GetRow(rowIndex).GetCell(6);
if ("" == cell.StringCellValue)
{
cell = sheet.GetRow(rowIndex).GetCell(5);
cell.SetCellValue("√");
}
}
else
{
cell = sheet.GetRow(rowIndex).GetCell(5);
if ("√" == cell.StringCellValue)
{
cell.SetCellValue("");
}
cell = sheet.GetRow(rowIndex).GetCell(6);
if ("" == cell.StringCellValue)
{
cell.SetCellValue(item.compare_str);
}
else
{
cell.SetCellValue(cell.StringCellValue + "\r\n" + item.compare_str);
}
}
break;
case "房产":
if (0 <= item.compare_str.IndexOf("基本一致"))
{
cell = sheet.GetRow(rowIndex).GetCell(7);
cell.SetCellValue("√");
}
else
{
cell = sheet.GetRow(rowIndex).GetCell(8);
cell.SetCellValue(item.compare_str);
}
break;
case "股票":
case "基金":
if (0 <= item.compare_str.IndexOf("基本一致"))
{
cell = sheet.GetRow(rowIndex).GetCell(10);
if ("" == cell.StringCellValue)
{
cell = sheet.GetRow(rowIndex).GetCell(9);
cell.SetCellValue("√");
}
}
else
{
cell = sheet.GetRow(rowIndex).GetCell(9);
if ("√" == cell.StringCellValue)
{
cell.SetCellValue("");
}
cell = sheet.GetRow(rowIndex).GetCell(10);
if ("" == cell.StringCellValue)
{
cell.SetCellValue(item.compare_str);
}
else
{
cell.SetCellValue(cell.StringCellValue + "\r\n" + item.compare_str);
}
}
break;
case "保险":
if (0 <= item.compare_str.IndexOf("基本一致"))
{
cell = sheet.GetRow(rowIndex).GetCell(11);
cell.SetCellValue("√");
}
else
{
cell = sheet.GetRow(rowIndex).GetCell(12);
cell.SetCellValue(item.compare_str);
}
break;
case "工商":
if (0 <= item.compare_str.IndexOf("基本一致"))
{
cell = sheet.GetRow(rowIndex).GetCell(13);
cell.SetCellValue("√");
}
else
{
cell = sheet.GetRow(rowIndex).GetCell(14);
cell.SetCellValue(item.compare_str);
}
break;
}
}
}
using (FileStream fileStream = File.Open(path + "\\汇总表.xlsx", FileMode.Create, FileAccess.Write))
{
workbook.Write(fileStream);
fileStream.Close();
}
workbook.Close();
return("OK");
}
catch (Exception e)
{
return(e.Message);
}
}
