using Forp.App.BaseData.En;
using Forp.App.Loan.Da;
using Forp.App.Loan.En;
using Forp.Core.Base.En;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
namespace Forp.App.Loan.Bl
{
/// <summary>
/// 功能描述: Excel导出类
/// </summary>
/// 创建者: 杨武
/// 创建时间: 2014年11月03日
public class ExcelExportBl
{
#region 导出行长查询数据
/// <summary>
/// 导出行长查询数据
/// </summary>
/// <param name="loanBillList"></param>
/// <param name="filePath"></param>
/// <param name="fileName"></param>
/// <returns></returns>
public static string CreateLoanBillExcel(List<LoanBillEn> loanBillList, string filePath, out string fileName)
{
fileName = string.Empty;
if (loanBillList == null) return string.Empty;
AuditFilter auditFilter = new AuditFilter();
List<AuditEn> auditList = AuditBl.GetDataList(auditFilter);
//AuditDetailFilter auditDetailFilter = new AuditDetailFilter();
//List<AuditDetailEn> auditDetailList = AuditDetailBl.GetDataList(auditDetailFilter);
IWorkbook workbook = null;
ISheet sheet = null;
try
{
if (!Directory.Exists(filePath))
Directory.CreateDirectory(filePath);
fileName = "行长查询数据";
string newFileName = filePath + fileName;
if (File.Exists(newFileName))
File.Delete(newFileName);
workbook = new HSSFWorkbook();
IRow row = null;//标题
IRow row0 = null;//标题
#region 单元格样式
ICellStyle style = workbook.CreateCellStyle();
//表头单元格背景色
style.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
//表头字体设置
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;//字号
font.Boldweight = 600;//加粗
//font.Color = HSSFColor.WHITE.index;//颜色
style.SetFont(font);
//居中
style.Alignment = HorizontalAlignment.CENTER;//居中
style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中
style.WrapText = true;//自动换行
style.BorderBottom = BorderStyle.THIN;
style.BorderLeft = BorderStyle.THIN;
style.BorderRight = BorderStyle.THIN;
style.BorderTop = BorderStyle.THIN;
ICellStyle rowStyle = workbook.CreateCellStyle();
rowStyle.Alignment = HorizontalAlignment.CENTER;//居中
rowStyle.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中
rowStyle.WrapText = true;//自动换行
//边框
rowStyle.BorderBottom = BorderStyle.THIN;
rowStyle.BorderLeft = BorderStyle.THIN;
rowStyle.BorderRight = BorderStyle.THIN;
rowStyle.BorderTop = BorderStyle.THIN;
ICellStyle rowStyle1 = workbook.CreateCellStyle();
rowStyle1.BorderBottom = BorderStyle.THIN;
rowStyle1.BorderLeft = BorderStyle.THIN;
rowStyle1.BorderRight = BorderStyle.THIN;
rowStyle1.BorderTop = BorderStyle.THIN;
#endregion
#region 填充数据
IRow row1 = null;//行
if (loanBillList != null)
{
#region 行长查询
sheet = workbook.CreateSheet("行长查询");
//设置列宽
sheet.SetColumnWidth(0, 15 * 256);
sheet.SetColumnWidth(1, 15 * 256);
sheet.SetColumnWidth(2, 15 * 256);
sheet.SetColumnWidth(3, 20 * 256);
//设置行高
sheet.DefaultRowHeight = 20 * 20;
LoanBillEn loanBill = null;
string str = string.Empty;
AuditEn audit = null;
AuditEn newAudit = null;
//List<AuditEn> newAuditList = null;
List<int> phaseList = new List<int>();
List<string> phaseNameList = new List<string>();
for (int i = 0; i < loanBillList.Count(); i++)
{
loanBill = loanBillList[i];
if (loanBill == null)
{
continue;
}
#region 标题
//创建标题
row = sheet.CreateRow(i * 3 + 0);
ICell cell0 = row.CreateCell(0);
cell0.SetCellValue("ID");
cell0.CellStyle = style;
ICell cell1 = row.CreateCell(1);
cell1.SetCellValue("客户姓名");
cell1.CellStyle = style;
ICell cell2 = row.CreateCell(2);
cell2.SetCellValue("贷款品种");
cell2.CellStyle = style;
ICell cell3 = row.CreateCell(3);
cell3.SetCellValue("担保方式");
cell3.CellStyle = style;
//创建副标题
row0 = sheet.CreateRow(i * 3 + 1);
ICell cell0_1 = row0.CreateCell(0);
cell0_1.SetCellValue("");
cell0_1.CellStyle = style;
ICell cell1_1 = row0.CreateCell(1);
cell1_1.SetCellValue("");
cell1_1.CellStyle = style;
ICell cell2_1 = row0.CreateCell(2);
cell2_1.SetCellValue("");
cell2_1.CellStyle = style;
ICell cell3_1 = row0.CreateCell(3);
cell3_1.SetCellValue("");
cell3_1.CellStyle = style;
//合并单元格
sheet.AddMergedRegion(new CellRangeAddress(i * 3 + 0, i * 3 + 1, 0, 0));
sheet.AddMergedRegion(new CellRangeAddress(i * 3 + 0, i * 3 + 1, 1, 1));
sheet.AddMergedRegion(new CellRangeAddress(i * 3 + 0, i * 3 + 1, 2, 2));
sheet.AddMergedRegion(new CellRangeAddress(i * 3 + 0, i * 3 + 1, 3, 3));
int count = 0;
if (auditList != null)
{
phaseList = auditList.Where(a => a.LoanBillID == loanBill.ID).GroupBy(a => a.BizType).Select(a => a.Key).ToList();
phaseNameList = auditList.Where(a => a.LoanBillID == loanBill.ID).GroupBy(a => a.PhaseName).Select(a => a.Key).ToList();
if (phaseList != null && phaseList.Count()>0)
{
int j = 0;
count = phaseList.Count();
foreach (int phaseID in phaseList)
{
sheet.SetColumnWidth(4 + j * 4, 15 * 256);
sheet.SetColumnWidth(5 + j * 4, 15 * 256);
sheet.SetColumnWidth(6 + j * 4, 15 * 256);
//标题
ICell cell4 = row.CreateCell(4 + j * 4);
cell4.SetCellValue(phaseNameList[j]);
cell4.CellStyle = style;
ICell cell5 = row.CreateCell(5 + j * 4);
cell5.SetCellValue("");
cell5.CellStyle = style;
ICell cell6 = row.CreateCell(6 + j * 4);
cell6.SetCellValue("");
cell6.CellStyle = style;
//副标题
ICell cell4_1 = row0.CreateCell(4 + j * 4);
cell4_1.SetCellValue("操作员");
cell4_1.CellStyle = style;
ICell cell5_1 = row0.CreateCell(5 + j * 4);
cell5_1.SetCellValue("作业时间");
cell5_1.CellStyle = style;
ICell cell6_1 = row0.CreateCell(6 + j * 4);
cell6_1.SetCellValue("累计暂停时间");
cell6_1.CellStyle = style;
//合并单元格
sheet.AddMergedRegion(new CellRangeAddress(i * 3 + 0, i * 3 + 0, 4 + j * 4, 6 + j * 4));
//if (phaseID != (int)BusinessPhaseEnum.Archiving)//归档
//{
ICell cell7 = row.CreateCell(7 + j * 4);
cell7.SetCellValue("累计物流时间");
cell7.CellStyle = style;
ICell cell7_1 = row0.CreateCell(7 + j * 4);
cell7_1.SetCellValue("");
cell7_1.CellStyle = style;
sheet.SetColumnWidth(7 + j * 4, 15 * 256);
//合并单元格
sheet.AddMergedRegion(new CellRangeAddress(i * 3 + 0, i * 3 + 1, 7 + j * 4, 7 + j * 4));
//}
j++;
}
}
}
ICell cell8 = row.CreateCell(8 + (count - 1) * 4);
cell8.SetCellValue("工作时间总计");
cell8.CellStyle = style;
ICell cell9 = row.CreateCell(9 + (count - 1) * 4);
cell9.SetCellValue("物流时间总计");
cell9.CellStyle = style;
ICell cel10 = row.CreateCell(10 + (count - 1) * 4);
cel10.SetCellValue("暂停时间总计");
cel10.CellStyle = style;
ICell cell8_1 = row0.CreateCell(8 + (count - 1) * 4);
cell8_1.SetCellValue("");
cell8_1.CellStyle = style;
ICell cell9_1 = row0.CreateCell(9 + (count - 1) * 4);
cell9_1.SetCellValue("");
cell9_1.CellStyle = style;
ICell cel10_1 = row0.CreateCell(10 + (count - 1) * 4);
cel10_1.SetCellValue("");
cel10_1.CellStyle = style;
sheet.SetColumnWidth(8 + (count - 1) * 4, 15 * 256);
sheet.SetColumnWidth(9 + (count - 1) * 4, 15 * 256);
sheet.SetColumnWidth(10 + (count - 1) * 4, 15 * 256);
//合并单元格
sheet.AddMergedRegion(new CellRangeAddress(i * 3 + 0, i * 3 + 1, 8 + (count - 1) * 4, 8 + (count - 1) * 4));
sheet.AddMergedRegion(new CellRangeAddress(i * 3 + 0, i * 3 + 1, 9 + (count - 1) * 4, 9 + (count - 1) * 4));
sheet.AddMergedRegion(new CellRangeAddress(i * 3 + 0, i * 3 + 1, 10 + (count - 1) * 4, 10 + (count - 1) * 4));
#endregion
#region 数据行
row1 = sheet.CreateRow(i * 3 + 2);
ICell rowCell0 = row1.CreateCell(0);
rowCell0.SetCellValue(loanBill.LoanBillICode);
rowCell0.CellStyle = rowStyle;
ICell rowCell1 = row1.CreateCell(1);
if (loanBill.CustomerManage != null)
rowCell1.SetCellValue(loanBill.Customer.Name);
rowCell1.CellStyle = rowStyle;
ICell rowCell2 = row1.CreateCell(2);
if (loanBill.Customer != null)
rowCell2.SetCellValue(loanBill.LoanType.Name);
rowCell2.CellStyle = rowStyle;
ICell rowCell3 = row1.CreateCell(3);
if (loanBill.OneLevelAssure != null)
str = loanBill.OneLevelAssure.Name;
if (loanBill.TwoLevelAssure != null && !string.IsNullOrEmpty(loanBill.TwoLevelAssure.Name))
str += "-" + loanBill.TwoLevelAssure.Name;
rowCell3.SetCellValue(str);
rowCell3.CellStyle = rowStyle;
double tempStopTotalHours = 0;//累计暂停时间合计
double workTotalHours = 0;//作业时间合计
double flowTotalHours = 0;//物流时间合计
if (phaseList != null)
{
int j = 0;
DateTime? startDate = null;
DateTime? endDate = null;
DateTime currentDate = DateTime.Now;
foreach (int phaseID in phaseList)
{
double tempStopHours = 0;//累计暂停时间
double workHours = 0;//作业时间
double flowHours = 0;//物流时间
audit = new AuditEn();
var newAuditList = auditList.Where(a => a.LoanBillID == loanBill.ID && a.BizType == phaseID).Select(a => a);
if (newAuditList != null && newAuditList.Count() > 0)
audit = newAuditList.First();
if (audit != null)
{
#region 评审中的已删除单据,按照删除日期计算
if (j == phaseList.Count() - 1)
{
if (loanBill.IsDelete == 1)//已删除
{
if (audit.AuditState == (int)AuditStateEnum.WaitAudit || audit.AuditState == (int)AuditStateEnum.Auditing || audit.AuditState == (int)AuditStateEnum.Pause)//1.审核中(已接收) 2.审核中(已保存) 6.暂停
{
if (loanBill.DeleteDate != null)
{
currentDate = loanBill.DeleteDate.Value;
}
}
}
}
#endregion
#region 计算暂停时间
if (audit.ListDetail != null && audit.ListDetail.Count > 0)
{
if (audit.ListDetail.Count % 2 == 1)
{
AuditDetailEn newDetail = new AuditDetailEn();
newDetail.AuditDate = currentDate;
newDetail.AuditState = 1;
audit.ListDetail.Add(newDetail);
}
//int index = 0;
//foreach (AuditDetailEn item in audit.ListDetail)
for (int index = 0; index < audit.ListDetail.Count(); )
{
AuditDetailEn detail = audit.ListDetail[index];
AuditDetailEn nextDetail = audit.ListDetail[index + 1];
DateTime AuditStartDate = detail.AuditDate.HasValue ? Convert.ToDateTime(detail.AuditDate) : new DateTime(1900, 1, 1);//开始时间
DateTime AuditEndDate = nextDetail.AuditDate.HasValue ? Convert.ToDateTime(nextDetail.AuditDate) : new DateTime(1900, 1, 1);//结束时间
TimeSpan ts1 = AuditEndDate - AuditStartDate;
tempStopHours += ts1.TotalHours;
index += 2;
}
}
#endregion
TimeSpan ts = new TimeSpan();
#region 作业时间
startDate = audit.StartDate;
if (audit.AuditState == (int)AuditStateEnum.Pause)//6.暂停
{
endDate = currentDate;
}
else
{
endDate = audit.EndDate != null ? audit.EndDate.Value : currentDate;
}
if (startDate != null)
{
ts = endDate.Value - startDate.Value;
}
workHours = ts.TotalHours - tempStopHours;
#endregion
#region 物流时间
if (audit.AuditState == (int)AuditStateEnum.AuditPass)//4.审核通过
{
if (j == phaseList.Count() - 1 && loanBill.IsDelete == 1)//已删除的单据,物流时间不计算
{
flowHours = 0;
}
else
{
if (phaseID == (int)BusinessPhaseEnum.Archiving)//归档,流程结束,不计算结束后的物流时间
{
flowHours = 0;
}
else
{
newAudit = new AuditEn();
var newAuditList1 = auditList.Where(a => a.LoanBillID == loanBill.ID && a.BizType > phaseID).Select(a => a);
if (newAuditList1 != null && newAuditList1.Count() > 0)
newAudit = newAuditList1.First();
if (newAudit != null)
{
startDate = newAudit.StartDate;
if (startDate != null)
{
ts = startDate.Value - endDate.Value;
flowHours = ts.TotalHours;
}
else
{
ts = currentDate - endDate.Value;
flowHours = ts.TotalHours;
}
}
else
{
ts = currentDate - endDate.Value;
flowHours = ts.TotalHours;
}
}
}
}
else//2.评审中 3.退回 5.拒绝 流程结束,不计算结束后的物流时间
{
flowHours = 0;
}
#endregion
ICell rowCell4 = row1.CreateCell(4 + j * 4);
rowCell4.SetCellValue(audit.AuditUser.Name);
rowCell4.CellStyle = rowStyle;
ICell rowCell5 = row1.CreateCell(5 + j * 4);
rowCell5.SetCellValue(workHours.ToString("F2"));
rowCell5.CellStyle = rowStyle;
ICell rowCell6 = row1.CreateCell(6 + j * 4);
rowCell6.SetCellValue(tempStopHours.ToString("F2"));
rowCell6.CellStyle = rowStyle;
//if (phaseID != (int)BusinessPhaseEnum.Archiving)//归档
//{
ICell rowCell7 = row1.CreateCell(7 + j * 4);
rowCell7.SetCellValue(flowHours.ToString("F2"));
rowCell7.CellStyle = rowStyle;
//}
tempStopTotalHours += Convert.ToDouble(tempStopHours.ToString("F2"));
workTotalHours += Convert.ToDouble(workHours.ToString("F2"));
flowTotalHours += Convert.ToDouble(flowHours.ToString("F2"));
}
j++;
}
}
ICell rowCell8 = row1.CreateCell(8 + (count - 1) * 4);
rowCell8.SetCellValue(workTotalHours);
rowCell8.CellStyle = rowStyle;
ICell rowCell9 = row1.CreateCell(9 + (count - 1) * 4);
rowCell9.SetCellValue(flowTotalHours);
rowCell9.CellStyle = rowStyle;
ICell rowCell10 = row1.CreateCell(10 + (count - 1) * 4);
rowCell10.SetCellValue(tempStopTotalHours);
rowCell10.CellStyle = rowStyle;
#endregion
}
sheet.HorizontallyCenter = true;
sheet.VerticallyCenter = true;
#endregion
}
#endregion
newFileName = newFileName + "(" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ")";
FileStream stream = File.Create(newFileName + ".xls");
workbook.Write(stream);
stream.Close();
return newFileName;
}
finally
{
workbook = null;
sheet = null;
}
}
#endregion
#region 导出业务管理数据
/// <summary>
/// 导出业务管理数据
/// </summary>
/// <param name="loanBillList"></param>
/// <param name="filePath"></param>
/// <param name="fileName"></param>
/// <returns></returns>
public static string CreateBusinessExcel(List<LoanBillEn> loanBillList, string filePath, out string fileName)
{
fileName = string.Empty;
if (loanBillList == null) return string.Empty;
IWorkbook workbook = null;
ISheet sheet = null;
try
{
if (!Directory.Exists(filePath))
Directory.CreateDirectory(filePath);
fileName = "业务管理";
string newFileName = filePath + fileName;
if (File.Exists(newFileName))
File.Delete(newFileName);
workbook = new HSSFWorkbook();
IRow row = null;//标题
#region 单元格样式
ICellStyle style = workbook.CreateCellStyle();
//表头单元格背景色
style.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
//表头字体设置
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;//字号
font.Boldweight = 600;//加粗
//font.Color = HSSFColor.WHITE.index;//颜色
style.SetFont(font);
//居中
style.Alignment = HorizontalAlignment.CENTER;//居中
style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中
style.WrapText = true;//自动换行
style.BorderBottom = BorderStyle.THIN;
style.BorderLeft = BorderStyle.THIN;
style.BorderRight = BorderStyle.THIN;
style.BorderTop = BorderStyle.THIN;
ICellStyle rowStyle = workbook.CreateCellStyle();
rowStyle.Alignment = HorizontalAlignment.CENTER;//居中
rowStyle.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中
rowStyle.WrapText = true;//自动换行
//边框
rowStyle.BorderBottom = BorderStyle.THIN;
rowStyle.BorderLeft = BorderStyle.THIN;
rowStyle.BorderRight = BorderStyle.THIN;
rowStyle.BorderTop = BorderStyle.THIN;
ICellStyle rowStyle1 = workbook.CreateCellStyle();
rowStyle1.BorderBottom = BorderStyle.THIN;
rowStyle1.BorderLeft = BorderStyle.THIN;
rowStyle1.BorderRight = BorderStyle.THIN;
rowStyle1.BorderTop = BorderStyle.THIN;
#endregion
#region 填充数据
IRow row1 = null;//行
if (loanBillList != null)
{
#region 业务管理
sheet = workbook.CreateSheet("业务管理");
//设置列宽
sheet.SetColumnWidth(0, 15 * 256);
sheet.SetColumnWidth(1, 15 * 256);
sheet.SetColumnWidth(2, 15 * 256);
sheet.SetColumnWidth(3, 20 * 256);
sheet.SetColumnWidth(4, 15 * 256);
sheet.SetColumnWidth(5, 15 * 256);
sheet.SetColumnWidth(6, 15 * 256);
sheet.SetColumnWidth(7, 15 * 256);
//设置行高
sheet.DefaultRowHeight = 20 * 20;
#region 标题
//创建标题
row = sheet.CreateRow(0);
ICell cell0 = row.CreateCell(0);
cell0.SetCellValue("序号");
cell0.CellStyle = style;
ICell cell1 = row.CreateCell(1);
cell1.SetCellValue("ID");
cell1.CellStyle = style;
ICell cell2 = row.CreateCell(2);
cell2.SetCellValue("客户姓名");
cell2.CellStyle = style;
ICell cell3 = row.CreateCell(3);
cell3.SetCellValue("身份证后八位");
cell3.CellStyle = style;
ICell cell4 = row.CreateCell(4);
cell4.SetCellValue("贷款品种");
cell4.CellStyle = style;
ICell cell5 = row.CreateCell(5);
cell5.SetCellValue("担保方式");
cell5.CellStyle = style;
ICell cell6 = row.CreateCell(6);
cell6.SetCellValue("所属机构");
cell6.CellStyle = style;
ICell cell7 = row.CreateCell(7);
cell7.SetCellValue("当前进度");
cell7.CellStyle = style;
#endregion
#region 数据行
LoanBillEn loanBill = null;
string str = string.Empty;
for (int i = 0; i < loanBillList.Count(); i++)
{
loanBill = loanBillList[i];
if (loanBill == null)
{
continue;
}
row1 = sheet.CreateRow(i + 1);
ICell rowCell0 = row1.CreateCell(0);
rowCell0.SetCellValue(loanBill.SortID);
rowCell0.CellStyle = rowStyle;
ICell rowCell1 = row1.CreateCell(1);
if (loanBill.CreateUser != null)
rowCell1.SetCellValue(loanBill.LoanBillICode);
rowCell1.CellStyle = rowStyle;
ICell rowCell2 = row1.CreateCell(2);
if (loanBill.Customer != null)
rowCell2.SetCellValue(loanBill.Customer.Name);
rowCell2.CellStyle = rowStyle;
ICell rowCell3 = row1.CreateCell(3);
rowCell3.SetCellValue(loanBill.IDCard);
rowCell3.CellStyle = rowStyle;
ICell rowCell4 = row1.CreateCell(4);
if (loanBill.LoanType != null)
rowCell4.SetCellValue(loanBill.LoanType.Name);
rowCell4.CellStyle = rowStyle;
ICell rowCell5 = row1.CreateCell(5);
if (loanBill.OneLevelAssure != null)
str = loanBill.OneLevelAssure.Name;
if (loanBill.TwoLevelAssure != null && !string.IsNullOrEmpty(loanBill.TwoLevelAssure.Name))
str += "-" + loanBill.TwoLevelAssure.Name;
rowCell5.SetCellValue(str);
rowCell5.CellStyle = rowStyle;
ICell rowCell6 = row1.CreateCell(6);
rowCell6.SetCellValue(loanBill.UserLevelName);
rowCell6.CellStyle = rowStyle;
ICell rowCell7 = row1.CreateCell(7);
rowCell7.SetCellValue(loanBill.State.Name);
rowCell7.CellStyle = rowStyle;
}
#endregion
sheet.HorizontallyCenter = true;
sheet.VerticallyCenter = true;
#endregion
}
#endregion
newFileName = newFileName + "(" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ")";
FileStream stream = File.Create(newFileName + ".xls");
workbook.Write(stream);
stream.Close();
return newFileName;
}
finally
{
workbook = null;
sheet = null;
}
}
#endregion
}
}
浙公网安备 33010602011771号