productDev/yunyibao.git

			@@ -3,10 +3,8 @@
			import com.doumee.core.constants.ResponseStatus;
			import com.doumee.core.exception.BusinessException;
			import com.doumee.core.utils.Constants;
			import com.doumee.dao.business.model.ApplyChagneDetail;
			import com.doumee.dao.business.model.ApplyChange;
			import com.doumee.dao.business.model.ApplyDetail;
			import com.doumee.dao.business.model.InsuranceApply;
			import com.doumee.dao.business.model.*;
			import com.github.xiaoymin.knife4j.core.util.CollectionUtils;
			import lombok.AllArgsConstructor;
			import lombok.Data;
			import org.apache.commons.lang3.StringUtils;
			@@ -14,17 +12,20 @@
			import org.apache.poi.ss.usermodel.*;
			import org.apache.poi.ss.util.CellRangeAddress;
			import org.apache.poi.xssf.streaming.SXSSFWorkbook;
			import org.checkerframework.checker.units.qual.C;
			import org.springframework.core.annotation.AnnotationConfigurationException;

			import javax.servlet.http.HttpServletResponse;
			import java.io.IOException;
			import java.io.OutputStream;
			import javax.swing.*;
			import java.io.*;
			import java.lang.reflect.Field;
			import java.lang.reflect.Method;
			import java.net.URL;
			import java.net.URLEncoder;
			import java.nio.charset.Charset;
			import java.text.SimpleDateFormat;
			import java.util.*;
			import java.util.stream.Collectors;

			/**
			* Excel导出实现
			@@ -232,6 +233,10 @@
			this.exportWithFirstAndEnd(data, fileName, DEFAULT_SHEET_NAME, first,end,response);
			}


			public void exportWithSheetAndFirstAndEnd (List<T> data, String fileName,String first,String end, String sheetName, HttpServletResponse response) {
			this.exportWithFirstAndEnd(data, fileName, sheetName, first,end,response);
			}
			/**O
			* 获取列集合
			*/
			@@ -252,11 +257,34 @@
			}
			return new ArrayList<>(sortedFields.values());
			}

			/**
			* 配置数据单元格
			*/
			private void configDataCell (SXSSFWorkbook workbook, Cell cell ) {
			if(cell == null){
			return;
			}
			CellStyle style = workbook.createCellStyle();
			style.setAlignment(HorizontalAlignment.CENTER);
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			// 设置背景
			// style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			// style.setFillForegroundColor(columnConfig.dataBackgroundColor().getIndex());
			// 字体
			Font font = workbook.createFont();
			style.setFont(font);
			// 边框
			configCellBorder(style);
			style.setWrapText(true);
			cell.setCellStyle(style);
			}
			/**
			* 配置数据单元格
			*/
			private void configDataCell (SXSSFWorkbook workbook, Cell cell, ExcelColumn columnConfig) {
			if(cell == null){
			return;
			}
			CellStyle style = workbook.createCellStyle();
			style.setAlignment(columnConfig.align());
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			@@ -283,6 +311,9 @@
			* 配置列头单元格
			*/
			private void configHeaderCell (SXSSFWorkbook workbook, Cell cell, ExcelColumn columnConfig) {
			if(cell == null){
			return;
			}
			CellStyle style = workbook.createCellStyle();
			style.setAlignment(columnConfig.align());
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			@@ -305,17 +336,63 @@
			* 配置列头单元格
			*/
			private void configFirstCell (SXSSFWorkbook workbook, Cell cell ) {
			if(cell == null){
			return;
			}
			CellStyle style = workbook.createCellStyle();
			style.setAlignment(HorizontalAlignment.CENTER);
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			// 设置背景
			style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
			// 字体
			Font font = workbook.createFont();
			font.setFontHeightInPoints((short)12);
			font.setBold(true);
			style.setFont(font);
			// 设置边框
			configCellBorder(style);
			cell.setCellStyle(style);
			}
			private void configTitleCell (SXSSFWorkbook workbook, Cell cell ) {
			if(cell == null){
			return;
			}
			CellStyle style = workbook.createCellStyle();
			style.setAlignment(HorizontalAlignment.CENTER);
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			// 设置背景
			style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
			// 字体
			Font font = workbook.createFont();
			font.setFontHeightInPoints((short)12);
			font.setBold(true);
			style.setFont(font);
			// 设置边框
			configCellBorder(style);
			// configCellBorder(style);
			cell.setCellStyle(style);
			}
			/**
			* 配置列头单元格
			*/
			private void configInfoCell (SXSSFWorkbook workbook, Cell cell,HorizontalAlignment p ) {
			if(cell == null){
			return;
			}
			CellStyle style = workbook.createCellStyle();
			style.setAlignment(p);
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			// 设置背景
			style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			// style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
			// 字体
			Font font = workbook.createFont();
			font.setFontHeightInPoints((short)24);
			font.setFontHeightInPoints((short)12);
			font.setBold(true);
			style.setFont(font);
			style.setWrapText(true);
			// 设置边框
			configCellBorder(style);
			cell.setCellStyle(style);
			@@ -324,6 +401,9 @@
			* 配置列头单元格
			*/
			private void configEndCell (SXSSFWorkbook workbook, Cell cell ) {
			if(cell == null){
			return;
			}
			CellStyle style = workbook.createCellStyle();
			style.setAlignment(HorizontalAlignment.RIGHT);
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			@@ -426,49 +506,67 @@
			try {
			sxssfWorkbook = new SXSSFWorkbook();
			Sheet sheet = sxssfWorkbook.createSheet(sheetName);
			for (int i = 0; i < 6; i++) {
			sheet.setColumnWidth(i, (i==5?20:15) * 2 * 256);
			}
			sheet.createFreezePane(0, 1);
			sheet.addMergedRegion(new CellRangeAddress(0 ,0,0,6));
			Row title = sheet.createRow(0);
			title.setHeight((short) 1000);
			Cell c = title.createCell(0);
			c.setCellValue(sheetName);
			configFirstCell(sxssfWorkbook,c);
			title.setHeight((short) 800);
			for (int i = 0; i < 6; i++) {
			title.createCell(i).setCellValue(i==0?sheetName:"");
			configTitleCell(sxssfWorkbook,title.getCell(i));
			}
			sheet.addMergedRegion(new CellRangeAddress(0 ,0,0,5));
			Row header1 = sheet.createRow(1);
			header1.createCell(0).setCellValue("投保企业");
			header1.setHeight((short) 500);
			header1.createCell(0).setCellValue(Constants.equalsInteger(data.getSolutionType(), Constants.ZERO)?"投保企业":"被保险人");
			header1.createCell(1).setCellValue("保险方案");
			header1.createCell(2).setCellValue("保险生效起期");
			header1.createCell(3).setCellValue("保险生效止期");
			header1.createCell(4).setCellValue("投保人数");
			header1.createCell(2).setCellValue(Constants.getApplyTimeTitle(1,data));
			header1.createCell(3).setCellValue(Constants.getApplyTimeTitle(0,data));
			header1.createCell(4).setCellValue("在保/投保人数");
			header1.createCell(5).setCellValue("总费用（元）");
			for (int i = 0; i < 6; i++) {
			configFirstCell(sxssfWorkbook,header1.getCell(i));
			}
			Row header2 = sheet.createRow(2);
			header2.createCell(0).setCellValue(data.getCompanyName());
			header2.createCell(1).setCellValue(data.getSolutionsName());
			header2.createCell(2).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getStartTime()));
			header2.createCell(3).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getEndTime()));
			header2.createCell(4).setCellValue(data.getInsureNum());
			header2.createCell(4).setCellValue(data.getGuaranteeNum()+" / "+data.getInsureNum());
			header2.createCell(5).setCellValue(Constants.formatBigdecimal2Float(data.getFee()).toString());
			//空白行
			Row header3 = sheet.createRow(3);
			sheet.addMergedRegion(new CellRangeAddress(3 ,3,0,6));

			Row header4 = sheet.createRow(4);
			for (int i = 0; i < 6; i++) {
			configDataCell(sxssfWorkbook, header2.getCell(i));
			}
			Row header4 = sheet.createRow(3);
			header4.createCell(0).setCellValue("序号");
			header4.createCell(1).setCellValue("员工姓名");
			header4.createCell(2).setCellValue("性别");
			header4.createCell(3).setCellValue("身份证号");
			header4.createCell(4).setCellValue("派遣单位");
			header4.createCell(5).setCellValue("所属工种");
			// header4.createCell(2).setCellValue("性别");
			header4.createCell(2).setCellValue("身份证号");
			header4.createCell(3).setCellValue("派遣单位");
			header4.createCell(4).setCellValue("所属工种");
			header4.createCell(5).setCellValue("备注");
			header4.setHeight((short) 500);
			for (int i = 0; i < 6; i++) {
			configFirstCell(sxssfWorkbook,header4.getCell(i));
			}
			if(data.getApplyDetailList()!=null){
			// 创建数据记录
			for (int rowIndex = 0; rowIndex < data.getApplyDetailList().size(); rowIndex++) {
			ApplyDetail d = data.getApplyDetailList().get(rowIndex);
			Row header5 = sheet.createRow(rowIndex + 4);
			// header5.setHeight((short) 300);
			header5.createCell(0).setCellValue(rowIndex+1);
			header5.createCell(1).setCellValue(StringUtils.defaultString(d.getMemberName(),""));
			header5.createCell(2).setCellValue(Constants.equalsInteger(d.getSex(),0)?"男":(Constants.equalsInteger(d.getSex(),1)?"女":"-"));
			header5.createCell(3).setCellValue(StringUtils.defaultString(d.getIdcardNo(),""));
			header5.createCell(4).setCellValue(StringUtils.defaultString(d.getDuName(),""));
			header5.createCell(5).setCellValue(StringUtils.defaultString(d.getWorkTypeName(),""));
			// header5.createCell(2).setCellValue(Constants.equalsInteger(d.getSex(),0)?"男":(Constants.equalsInteger(d.getSex(),1)?"女":"-"));
			header5.createCell(2).setCellValue(StringUtils.defaultString(d.getIdcardNo(),""));
			header5.createCell(3).setCellValue(StringUtils.defaultString(d.getDuName(),""));
			header5.createCell(4).setCellValue(StringUtils.defaultString(d.getWorkTypeName(),""));
			header5.createCell(5).setCellValue(StringUtils.defaultString(d.getRemark(),""));
			// 设置数据单元格
			for (int i = 0; i < 6; i++) {
			configDataCell(sxssfWorkbook, header5.getCell(i));
			}
			}
			}
			sxssfWorkbook.write(os);
			@@ -505,6 +603,9 @@
			}
			}




			/**
			* 加减申请表导出
			* @param model
			@@ -517,7 +618,7 @@
			response.setContentType("application/octet-stream");
			response.setHeader("eva-opera-type", "download");
			response.setHeader("eva-download-filename", encodeFileName);
			this.exportJiajianBaoExcelDo(model,"加减保申请表_", response.getOutputStream());
			this.exportJiajianBaoExcelDo(model,"加减保申请表", response.getOutputStream());
			} catch (IOException e) {
			throw new BusinessException(ResponseStatus.EXPORT_EXCEL_ERROR, e);
			}
			@@ -534,57 +635,87 @@
			try {
			sxssfWorkbook = new SXSSFWorkbook();
			Sheet sheet = sxssfWorkbook.createSheet(sheetName);
			for (int i = 0; i < 12; i++) {
			sheet.setColumnWidth(i, ((i==2\|\| i== 3 \|\| i==8\|\| i==9\|\| i==10)?16:8) * 2 * 256);
			}
			sheet.createFreezePane(0, 1);
			sheet.addMergedRegion(new CellRangeAddress(0 ,0,0,8));
			sheet.addMergedRegion(new CellRangeAddress(0 ,0,0,11));
			Row title = sheet.createRow(0);
			title.setHeight((short) 1000);
			title.setHeight((short) 800);
			Cell c = title.createCell(0);
			c.setCellValue(sheetName);
			configFirstCell(sxssfWorkbook,c);
			for (int i = 0; i < 12; i++) {
			// title.createCell(i).setCellValue(i==0?sheetName:"");
			configTitleCell(sxssfWorkbook,title.getCell(i));
			}
			Row header1 = sheet.createRow(1);
			header1.createCell(0).setCellValue("投保企业");
			header1.createCell(3).setCellValue("保险方案");
			header1.createCell(4).setCellValue("保单号");
			header1.createCell(5).setCellValue("保险生效起期");
			header1.createCell(1).setCellValue("");
			header1.createCell(2).setCellValue("保险方案");
			header1.createCell(3).setCellValue("保单号");
			header1.createCell(4).setCellValue("保险生效起期");
			header1.createCell(5).setCellValue("");
			header1.createCell(6).setCellValue("保险生效止期");
			header1.createCell(9).setCellValue("加保人数");
			header1.createCell(10).setCellValue("减保人数");
			header1.createCell(11).setCellValue("期望批单生效期");
			sheet.addMergedRegion(new CellRangeAddress(1 ,1,0,2));
			sheet.addMergedRegion(new CellRangeAddress(1 ,1,6,8));

			header1.createCell(7).setCellValue("");
			header1.createCell(8).setCellValue(Constants.getChangeApplyTimeTitle(1,data));
			header1.createCell(9).setCellValue(Constants.getChangeApplyTimeTitle(0,data));
			header1.createCell(10).setCellValue("加保人数");
			header1.createCell(11).setCellValue("减保人数");
			for (int i = 0; i < 12; i++) {
			configFirstCell(sxssfWorkbook,header1.getCell(i));
			}
			sheet.addMergedRegion(new CellRangeAddress(1 ,1,0,1));
			sheet.addMergedRegion(new CellRangeAddress(1 ,1,4,5));
			sheet.addMergedRegion(new CellRangeAddress(1 ,1,6,7));

			Row header2 = sheet.createRow(2);
			header2.createCell(0).setCellValue(data.getCompanyName());
			header2.createCell(3).setCellValue(data.getSolutionsName());
			header2.createCell(4).setCellValue(data.getApplyCode());
			header2.createCell(5).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getStartTime()));
			header2.createCell(1).setCellValue("");
			header2.createCell(2).setCellValue(data.getSolutionsName());
			header2.createCell(3).setCellValue(data.getApplyCode());
			header2.createCell(4).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getStartTime()));
			header2.createCell(5).setCellValue("");
			header2.createCell(6).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getEndTime()));
			header2.createCell(9).setCellValue(data.getAddNum());
			header2.createCell(10).setCellValue(data.getDelNum());
			header2.createCell(11).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getValidTime()));
			sheet.addMergedRegion(new CellRangeAddress(2 ,2,0,2));
			sheet.addMergedRegion(new CellRangeAddress(2 ,2,6,8));
			//空白行
			header2.createCell(7).setCellValue("");
			header2.createCell(8).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getValidTime()));
			header2.createCell(9).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getDelValidTime()));
			header2.createCell(10).setCellValue(data.getAddNum());
			header2.createCell(11).setCellValue(data.getDelNum());
			sheet.addMergedRegion(new CellRangeAddress(2 ,2,0,1));
			sheet.addMergedRegion(new CellRangeAddress(2 ,2,4,5));
			sheet.addMergedRegion(new CellRangeAddress(2 ,2,6,7));
			for (int i = 0; i < 12; i++) {
			configDataCell(sxssfWorkbook, header2.getCell(i));
			}
			Row header3 = sheet.createRow(3);
			header2.createCell(0).setCellValue("加保人员");
			header2.createCell(6).setCellValue("减保人员");
			for (int i = 0; i < 12; i++) {
			header3.createCell(i).setCellValue("");
			}
			header3.createCell(0).setCellValue("加保人员");
			header3.createCell(6).setCellValue("减保人员");
			sheet.addMergedRegion(new CellRangeAddress(3 ,3,0,5));
			sheet.addMergedRegion(new CellRangeAddress(3 ,3,6,11));

			for (int i = 0; i < 12; i++) {
			configFirstCell(sxssfWorkbook, header3.getCell(i));
			}
			Row header4 = sheet.createRow(4);
			header4.createCell(0).setCellValue("序号");
			header4.createCell(1).setCellValue("员工姓名");
			header4.createCell(2).setCellValue("性别");
			header4.createCell(3).setCellValue("身份证号");
			header4.createCell(4).setCellValue("派遣单位");
			header4.createCell(5).setCellValue("所属工种");
			// header4.createCell(2).setCellValue("性别");
			header4.createCell(2).setCellValue("身份证号");
			header4.createCell(3).setCellValue("派遣单位");
			header4.createCell(4).setCellValue("所属工种");
			header4.createCell(5).setCellValue("备注");
			header4.createCell(6).setCellValue("序号");
			header4.createCell(7).setCellValue("员工姓名");
			header4.createCell(8).setCellValue("性别");
			header4.createCell(9).setCellValue("身份证号");
			header4.createCell(10).setCellValue("派遣单位");
			header4.createCell(11).setCellValue("所属工种");
			// header4.createCell(8).setCellValue("性别");
			header4.createCell(8).setCellValue("身份证号");
			header4.createCell(9).setCellValue("派遣单位");
			header4.createCell(10).setCellValue("所属工种");
			header4.createCell(11).setCellValue("备注");
			for (int i = 0; i < 12; i++) {
			configFirstCell(sxssfWorkbook,header4.getCell(i));
			}
			int maxNum = data.getAddNum()>data.getDelNum()?data.getAddNum():data.getDelNum();
			// 创建数据记录
			for (int rowIndex = 0; rowIndex <maxNum; rowIndex++) {
			@@ -594,18 +725,31 @@
			if(addModel!=null){
			header5.createCell(0).setCellValue(rowIndex+1);
			header5.createCell(1).setCellValue(StringUtils.defaultString(addModel.getMemberName(),""));
			header5.createCell(2).setCellValue(Constants.equalsInteger(addModel.getSex(),0)?"男":(Constants.equalsInteger(addModel.getSex(),1)?"女":"-"));
			header5.createCell(3).setCellValue(StringUtils.defaultString(addModel.getIdcardNo(),""));
			header5.createCell(4).setCellValue(StringUtils.defaultString(addModel.getDuName(),""));
			header5.createCell(5).setCellValue(StringUtils.defaultString(addModel.getWorkTypeName(),""));
			// header5.createCell(2).setCellValue(Constants.equalsInteger(addModel.getSex(),0)?"男":(Constants.equalsInteger(addModel.getSex(),1)?"女":"-"));
			header5.createCell(2).setCellValue(StringUtils.defaultString(addModel.getMemberIdcardNo(),""));
			header5.createCell(3).setCellValue(StringUtils.defaultString(addModel.getDuName(),""));
			header5.createCell(4).setCellValue(StringUtils.defaultString(addModel.getWorkTypeName(),""));
			header5.createCell(5).setCellValue(StringUtils.defaultString(addModel.getRemark(),""));
			}else{
			for (int i = 0; i < 6; i++) {
			header5.createCell(i).setCellValue("");
			}
			}
			if(delModel!=null){
			header5.createCell(6).setCellValue(rowIndex+1);
			header5.createCell(7).setCellValue(StringUtils.defaultString(delModel.getMemberName(),""));
			header5.createCell(8).setCellValue(Constants.equalsInteger(delModel.getSex(),0)?"男":(Constants.equalsInteger(delModel.getSex(),1)?"女":"-"));
			header5.createCell(9).setCellValue(StringUtils.defaultString(delModel.getIdcardNo(),""));
			header5.createCell(10).setCellValue(StringUtils.defaultString(delModel.getDuName(),""));
			header5.createCell(11).setCellValue(StringUtils.defaultString(delModel.getWorkTypeName(),""));
			// header5.createCell(8).setCellValue(Constants.equalsInteger(delModel.getSex(),0)?"男":(Constants.equalsInteger(delModel.getSex(),1)?"女":"-"));
			header5.createCell(8).setCellValue(StringUtils.defaultString(delModel.getMemberIdcardNo(),""));
			header5.createCell(9).setCellValue(StringUtils.defaultString(delModel.getDuName(),""));
			header5.createCell(10).setCellValue(StringUtils.defaultString(delModel.getWorkTypeName(),""));
			header5.createCell(11).setCellValue(StringUtils.defaultString(delModel.getRemark(),""));
			}else{
			for (int i =6; i < 12; i++) {
			header5.createCell(i).setCellValue("");
			}
			}
			for (int i = 0; i < 12; i++) {
			configDataCell(sxssfWorkbook, header5.getCell(i));
			}
			}
			sxssfWorkbook.write(os);
			@@ -622,6 +766,179 @@
			}
			}
			}

			/**
			* 导出加减保申请详单
			* @param data 数据
			* @param sheetName Sheet名称
			*/
			public String exportJiajianBaoExcelToPdf (ApplyChange data, String sheetName,String cellValue) {
			SXSSFWorkbook sxssfWorkbook;
			try {
			sxssfWorkbook = new SXSSFWorkbook();
			Sheet sheet = sxssfWorkbook.createSheet(sheetName);
			for (int i = 0; i < 12; i++) {
			sheet.setColumnWidth(i, ((i==2\|\| i== 3 \|\| i==8\|\| i==9\|\| i==10)?16:8) * 2 * 256);
			}
			sheet.createFreezePane(0, 1);
			sheet.addMergedRegion(new CellRangeAddress(0 ,0,0,11));
			Row title = sheet.createRow(0);
			title.setHeight((short) 800);
			Cell c = title.createCell(0);
			c.setCellValue(sheetName);
			for (int i = 0; i < 12; i++) {
			// title.createCell(i).setCellValue(i==0?sheetName:"");
			configTitleCell(sxssfWorkbook,title.getCell(i));
			}
			Row header1 = sheet.createRow(1);
			header1.createCell(0).setCellValue(cellValue);
			header1.createCell(1).setCellValue("");
			header1.createCell(2).setCellValue("保险方案");
			header1.createCell(3).setCellValue("保单号");
			header1.createCell(4).setCellValue("保险生效起期");
			header1.createCell(5).setCellValue("");
			header1.createCell(6).setCellValue("保险生效止期");
			header1.createCell(7).setCellValue("");
			header1.createCell(8).setCellValue("期望批增生效日期");
			header1.createCell(9).setCellValue("期望批减生效日期");
			header1.createCell(10).setCellValue("加保人数");
			header1.createCell(11).setCellValue("减保人数");
			for (int i = 0; i < 12; i++) {
			configFirstCell(sxssfWorkbook,header1.getCell(i));
			}
			sheet.addMergedRegion(new CellRangeAddress(1 ,1,0,1));
			sheet.addMergedRegion(new CellRangeAddress(1 ,1,4,5));
			sheet.addMergedRegion(new CellRangeAddress(1 ,1,6,7));

			Row header2 = sheet.createRow(2);
			header2.createCell(0).setCellValue(data.getCompanyName());
			header2.createCell(1).setCellValue("");
			header2.createCell(2).setCellValue(data.getSolutionsName());
			header2.createCell(3).setCellValue(data.getApplyCode());
			header2.createCell(4).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getStartTime()));
			header2.createCell(5).setCellValue("");
			header2.createCell(6).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getEndTime()));
			header2.createCell(7).setCellValue("");
			header2.createCell(8).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getApplyStartTime()));
			header2.createCell(9).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getDelValidTime()));
			header2.createCell(10).setCellValue(data.getAddNum());
			header2.createCell(11).setCellValue(data.getDelNum());
			sheet.addMergedRegion(new CellRangeAddress(2 ,2,0,1));
			sheet.addMergedRegion(new CellRangeAddress(2 ,2,4,5));
			sheet.addMergedRegion(new CellRangeAddress(2 ,2,6,7));
			for (int i = 0; i < 12; i++) {
			configDataCell(sxssfWorkbook, header2.getCell(i));
			}
			Row header3 = sheet.createRow(3);
			header3.createCell(0).setCellValue("加保人员");
			header3.createCell(6).setCellValue("减保人员");
			sheet.addMergedRegion(new CellRangeAddress(3 ,3,0,5));
			sheet.addMergedRegion(new CellRangeAddress(3 ,3,6,11));
			for (int i = 0; i < 12; i++) {
			configFirstCell(sxssfWorkbook, header3.getCell(i));
			}
			Row header4 = sheet.createRow(4);
			header4.createCell(0).setCellValue("序号");
			header4.createCell(1).setCellValue("员工姓名");
			// header4.createCell(2).setCellValue("性别");
			header4.createCell(2).setCellValue("身份证号");
			header4.createCell(3).setCellValue("派遣单位");
			header4.createCell(4).setCellValue("所属工种");
			header4.createCell(5).setCellValue("备注");
			header4.createCell(6).setCellValue("序号");
			header4.createCell(7).setCellValue("员工姓名");
			// header4.createCell(8).setCellValue("性别");
			header4.createCell(8).setCellValue("身份证号");
			header4.createCell(9).setCellValue("派遣单位");
			header4.createCell(10).setCellValue("所属工种");
			header4.createCell(11).setCellValue("备注");
			for (int i = 0; i < 12; i++) {
			configFirstCell(sxssfWorkbook,header4.getCell(i));
			}
			int maxNum = data.getAddNum()>data.getDelNum()?data.getAddNum():data.getDelNum();
			// 创建数据记录
			for (int rowIndex = 0; rowIndex <maxNum; rowIndex++) {
			ApplyChagneDetail addModel = data.getAddDetailList().size()> rowIndex?data.getAddDetailList().get(rowIndex):null;
			ApplyChagneDetail delModel = data.getDelDetailList().size()> rowIndex?data.getDelDetailList().get(rowIndex):null;
			Row header5 = sheet.createRow(rowIndex + 5);
			if(addModel!=null){
			header5.createCell(0).setCellValue(rowIndex+1);
			header5.createCell(1).setCellValue(StringUtils.defaultString(addModel.getMemberName(),""));
			// header5.createCell(2).setCellValue(Constants.equalsInteger(addModel.getSex(),0)?"男":(Constants.equalsInteger(addModel.getSex(),1)?"女":"-"));
			header5.createCell(2).setCellValue(StringUtils.defaultString(addModel.getMemberIdcardNo(),""));
			header5.createCell(3).setCellValue(StringUtils.defaultString(addModel.getDuName(),""));
			header5.createCell(4).setCellValue(StringUtils.defaultString(addModel.getWorkTypeName(),""));
			header5.createCell(5).setCellValue(StringUtils.defaultString(addModel.getRemark(),""));
			}else{
			for (int i = 0; i < 6; i++) {
			header5.createCell(i).setCellValue("");
			}
			}
			if(delModel!=null){
			header5.createCell(6).setCellValue(rowIndex+1);
			header5.createCell(7).setCellValue(StringUtils.defaultString(delModel.getMemberName(),""));
			// header5.createCell(8).setCellValue(Constants.equalsInteger(delModel.getSex(),0)?"男":(Constants.equalsInteger(delModel.getSex(),1)?"女":"-"));
			header5.createCell(8).setCellValue(StringUtils.defaultString(delModel.getMemberIdcardNo(),""));
			header5.createCell(9).setCellValue(StringUtils.defaultString(delModel.getDuName(),""));
			header5.createCell(10).setCellValue(StringUtils.defaultString(delModel.getWorkTypeName(),""));
			header5.createCell(11).setCellValue(StringUtils.defaultString(delModel.getRemark(),""));
			}else{
			for (int i =6; i < 12; i++) {
			header5.createCell(i).setCellValue("");
			}
			}
			for (int i = 0; i < 12; i++) {
			configDataCell(sxssfWorkbook, header5.getCell(i));
			}
			}
			int rowIndex = 5+maxNum;
			Row header6= sheet.createRow(rowIndex);
			header6.createCell(0).setCellValue("投保企业申明:\n" +
			"1、在申请书上填写的一切内容属实，否则本企业承担一切法律责任。\n" +
			"2、由于变更受益人产生的法律纠纷本企业愿承担一切法律责任。\n" +
			"3、本申请表的签章为本企业亲自签署，如由此产生的法律纠纷本企业愿承担一切法律责任。\n");
			for (int i = 1; i < 12; i++) {
			header6.createCell(i).setCellValue("");
			}
			for (int i =0; i < 12; i++) {
			if(i>0){
			header6.createCell(i).setCellValue("");
			}
			configInfoCell(sxssfWorkbook,header6.getCell(i),HorizontalAlignment.LEFT);
			}
			header6.setHeight((short) 2000);
			sheet.addMergedRegion(new CellRangeAddress(rowIndex ,rowIndex,0,11));
			Row header7= sheet.createRow(rowIndex+1);
			header7.setHeight((short) 3000);
			header7.createCell(0).setCellValue(cellValue+"签章： \n \n \n" +"日期：年月日 ");
			for (int i =0; i < 12; i++) {
			if(i>0){
			header7.createCell(i).setCellValue("");
			}
			configInfoCell(sxssfWorkbook,header7.getCell(i),HorizontalAlignment.RIGHT);
			}
			sheet.addMergedRegion(new CellRangeAddress(rowIndex+1 ,rowIndex+1,0,11));

			//临时缓冲区
			ByteArrayOutputStream out = new ByteArrayOutputStream();
			//创建临时文件
			sxssfWorkbook.write(out);
			byte [] bookByteAry = out.toByteArray();
			InputStream in = new ByteArrayInputStream(bookByteAry);
			String tempExcel = saveIsToFile(in);
			String tempPdf =System.getProperty("java.io.tmpdir")+File.separator+UUID.randomUUID().toString()+".pdf";
			// ExcelToPdfToolNew.excelToPdf(in,tempPdf);//转PDF
			ExcelToPdfToolNew.excelToPdf(tempExcel,tempPdf);//转PDF
			File f = new File(tempPdf);
			if(f!=null && f.isFile() && f.length()>0){
			return tempPdf;
			}
			} catch (Exception e) {
			e.printStackTrace();
			// throw new BusinessException(ResponseStatus.EXPORT_EXCEL_ERROR, e);
			}
			return null;
			}
			/**
			* 导出加减保申请详单
			* @param data 数据
			@@ -633,54 +950,460 @@
			try {
			sxssfWorkbook = new SXSSFWorkbook();
			Sheet sheet = sxssfWorkbook.createSheet(sheetName);
			for (int i = 0; i < 8; i++) {
			sheet.setColumnWidth(i, ((i==2\|\| i==3 \|\| i==6\|\| i==7\|\| i==4\|\| i==5)?16:8) * 2 * 256);
			}
			sheet.createFreezePane(0, 1);
			sheet.addMergedRegion(new CellRangeAddress(0 ,0,0,8));
			sheet.addMergedRegion(new CellRangeAddress(0 ,0,0,7));
			Row title = sheet.createRow(0);
			title.setHeight((short) 1000);
			Cell c = title.createCell(0);
			c.setCellValue(sheetName);
			configFirstCell(sxssfWorkbook,c);
			for (int i = 0; i < 8; i++) {
			if(i>0){
			title.createCell(i).setCellValue("");
			}
			configTitleCell(sxssfWorkbook,title.getCell(i));
			}
			Row header1 = sheet.createRow(1);
			header1.createCell(0).setCellValue("投保企业");
			header1.createCell(1).setCellValue("保险方案");
			header1.createCell(1).setCellValue("");
			header1.createCell(2).setCellValue("保险方案");
			header1.createCell(3).setCellValue("保单号");
			header1.createCell(4).setCellValue("保险生效起期");
			header1.createCell(5).setCellValue("保险生效止期");
			header1.createCell(6).setCellValue("换厂人数");
			header1.createCell(7).setCellValue("期望批单生效期");
			sheet.addMergedRegion(new CellRangeAddress(1 ,1,1,2));
			header1.createCell(7).setCellValue(Constants.getChangeApplyTimeTitle(2,data));
			for (int i = 0; i < 8; i++) {
			configFirstCell(sxssfWorkbook,header1.getCell(i));
			}
			sheet.addMergedRegion(new CellRangeAddress(1 ,1,0,1));

			Row header2 = sheet.createRow(2);
			header2.createCell(0).setCellValue(data.getCompanyName());
			header2.createCell(1).setCellValue(data.getSolutionsName());
			header2.createCell(1).setCellValue("");
			header2.createCell(2).setCellValue(data.getSolutionsName());
			header2.createCell(3).setCellValue(data.getApplyCode());
			header2.createCell(4).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getStartTime()));
			header2.createCell(5).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getEndTime()));
			header2.createCell(6).setCellValue(data.getChangeNum());
			header2.createCell(7).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getValidTime()));
			sheet.addMergedRegion(new CellRangeAddress(2 ,2,1,2));

			header2.createCell(7).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getApplyStartTime()));
			sheet.addMergedRegion(new CellRangeAddress(2 ,2,0,1));
			for (int i = 0; i < 8; i++) {
			configDataCell(sxssfWorkbook,header2.getCell(i));
			}
			Row header4 = sheet.createRow(3);
			header4.createCell(0).setCellValue("序号");
			header4.createCell(1).setCellValue("员工姓名");
			header4.createCell(2).setCellValue("性别");
			header4.createCell(3).setCellValue("身份证号");
			header4.createCell(4).setCellValue("原派遣单位");
			header4.createCell(5).setCellValue("原所所属工种");
			header4.createCell(6).setCellValue("更改后派遣单位");
			header4.createCell(7).setCellValue("更改后所属工种");
			// header4.createCell(2).setCellValue("性别");
			header4.createCell(2).setCellValue("身份证号");
			header4.createCell(3).setCellValue("原派遣单位");
			header4.createCell(4).setCellValue("原所所属工种");
			header4.createCell(5).setCellValue("更改后派遣单位");
			header4.createCell(6).setCellValue("更改后所属工种");
			header4.createCell(7).setCellValue("备注");
			for (int i = 0; i < 8; i++) {
			configFirstCell(sxssfWorkbook,header4.getCell(i));
			}
			// 创建数据记录
			for (int rowIndex = 0; rowIndex <data.getChangeDetailList().size(); rowIndex++) {
			ApplyChagneDetail addModel = data.getChangeDetailList().get(rowIndex);
			Row header5 = sheet.createRow(rowIndex + 4);
			header5.createCell(0).setCellValue(rowIndex+1);
			header5.createCell(1).setCellValue(StringUtils.defaultString(addModel.getMemberName(),""));
			header5.createCell(2).setCellValue(Constants.equalsInteger(addModel.getSex(),0)?"男":(Constants.equalsInteger(addModel.getSex(),1)?"女":"-"));
			header5.createCell(3).setCellValue(StringUtils.defaultString(addModel.getIdcardNo(),""));
			header5.createCell(4).setCellValue(StringUtils.defaultString(addModel.getOldDuName(),""));
			header5.createCell(5).setCellValue(StringUtils.defaultString(addModel.getOldWorkTypeName(),""));
			header5.createCell(6).setCellValue(StringUtils.defaultString(addModel.getDuName(),""));
			header5.createCell(7).setCellValue(StringUtils.defaultString(addModel.getWorkTypeName(),""));
			// header5.createCell(2).setCellValue(Constants.equalsInteger(addModel.getSex(),0)?"男":(Constants.equalsInteger(addModel.getSex(),1)?"女":"-"));
			header5.createCell(2).setCellValue(StringUtils.defaultString(addModel.getMemberIdcardNo(),""));
			header5.createCell(3).setCellValue(StringUtils.defaultString(addModel.getOldDuName(),""));
			header5.createCell(4).setCellValue(StringUtils.defaultString(addModel.getOldWorkTypeName(),""));
			header5.createCell(5).setCellValue(StringUtils.defaultString(addModel.getDuName(),""));
			header5.createCell(6).setCellValue(StringUtils.defaultString(addModel.getWorkTypeName(),""));
			header5.createCell(7).setCellValue(StringUtils.defaultString(addModel.getRemark(),""));
			for (int i = 0; i < 8; i++) {
			configDataCell(sxssfWorkbook,header5.getCell(i));
			}
			}
			sxssfWorkbook.write(os);
			os.close();
			} catch (Exception e) {
			throw new BusinessException(ResponseStatus.EXPORT_EXCEL_ERROR, e);
			} finally {
			if (os != null) {
			try {
			os.close();
			} catch (IOException e) {
			e.printStackTrace();
			}
			}
			}
			}



			/**
			* 导出加减保申请详单
			* @param data 数据
			* @param sheetName Sheet名称
			*/
			public String exportChangeUnitExcelToPdf (ApplyChange data, String sheetName,String cellValue) {
			SXSSFWorkbook sxssfWorkbook;
			try {
			sxssfWorkbook = new SXSSFWorkbook();
			Sheet sheet = sxssfWorkbook.createSheet(sheetName);
			for (int i = 0; i < 8; i++) {
			sheet.setColumnWidth(i, ((i==2\|\| i==3 \|\| i==6\|\| i==7\|\| i==4\|\| i==5)?16:8) * 2 * 256);
			}
			sheet.createFreezePane(0, 1);
			sheet.addMergedRegion(new CellRangeAddress(0 ,0,0,7));
			Row title = sheet.createRow(0);
			title.setHeight((short) 1000);
			Cell c = title.createCell(0);
			c.setCellValue(sheetName);
			configFirstCell(sxssfWorkbook,c);
			for (int i = 0; i < 8; i++) {
			if(i>0){
			title.createCell(i).setCellValue("");
			}
			configTitleCell(sxssfWorkbook,title.getCell(i));
			}
			Row header1 = sheet.createRow(1);
			header1.createCell(0).setCellValue(cellValue);
			header1.createCell(1).setCellValue("");
			header1.createCell(2).setCellValue("保险方案");
			header1.createCell(3).setCellValue("保单号");
			header1.createCell(4).setCellValue("保险生效起期");
			header1.createCell(5).setCellValue("保险生效止期");
			header1.createCell(6).setCellValue("换厂人数");
			header1.createCell(7).setCellValue("期望批单生效期");
			for (int i = 0; i < 8; i++) {
			configFirstCell(sxssfWorkbook,header1.getCell(i));
			}
			sheet.addMergedRegion(new CellRangeAddress(1 ,1,0,1));

			Row header2 = sheet.createRow(2);
			header2.createCell(0).setCellValue(data.getCompanyName());
			header2.createCell(1).setCellValue("");
			header2.createCell(2).setCellValue(data.getSolutionsName());
			header2.createCell(3).setCellValue(data.getApplyCode());
			header2.createCell(4).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getStartTime()));
			header2.createCell(5).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getEndTime()));
			header2.createCell(6).setCellValue(data.getChangeNum());
			header2.createCell(7).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getApplyStartTime()));
			sheet.addMergedRegion(new CellRangeAddress(2 ,2,0,1));
			for (int i = 0; i < 8; i++) {
			configDataCell(sxssfWorkbook,header2.getCell(i));
			}
			Row header4 = sheet.createRow(3);
			header4.createCell(0).setCellValue("序号");
			header4.createCell(1).setCellValue("员工姓名");
			// header4.createCell(2).setCellValue("性别");
			header4.createCell(2).setCellValue("身份证号");
			header4.createCell(3).setCellValue("原派遣单位");
			header4.createCell(4).setCellValue("原所所属工种");
			header4.createCell(5).setCellValue("更改后派遣单位");
			header4.createCell(6).setCellValue("更改后所属工种");
			header4.createCell(7).setCellValue("备注");
			for (int i = 0; i < 8; i++) {
			configFirstCell(sxssfWorkbook,header4.getCell(i));
			}
			// 创建数据记录
			for (int rowIndex = 0; rowIndex <data.getChangeDetailList().size(); rowIndex++) {
			ApplyChagneDetail addModel = data.getChangeDetailList().get(rowIndex);
			Row header5 = sheet.createRow(rowIndex + 4);
			header5.createCell(0).setCellValue(rowIndex+1);
			header5.createCell(1).setCellValue(StringUtils.defaultString(addModel.getMemberName(),""));
			// header5.createCell(2).setCellValue(Constants.equalsInteger(addModel.getSex(),0)?"男":(Constants.equalsInteger(addModel.getSex(),1)?"女":"-"));
			header5.createCell(2).setCellValue(StringUtils.defaultString(addModel.getMemberIdcardNo(),""));
			header5.createCell(3).setCellValue(StringUtils.defaultString(addModel.getOldDuName(),""));
			header5.createCell(4).setCellValue(StringUtils.defaultString(addModel.getOldWorkTypeName(),""));
			header5.createCell(5).setCellValue(StringUtils.defaultString(addModel.getDuName(),""));
			header5.createCell(6).setCellValue(StringUtils.defaultString(addModel.getWorkTypeName(),""));
			header5.createCell(7).setCellValue(StringUtils.defaultString(addModel.getRemark(),""));
			for (int i = 0; i < 8; i++) {
			configDataCell(sxssfWorkbook,header5.getCell(i));
			}
			}
			int rowIndex = 4+data.getChangeDetailList().size();
			Row header6= sheet.createRow(rowIndex);
			header6.createCell(0).setCellValue("投保企业申明:\n" +
			"1、在申请书上填写的一切内容属实，否则本企业承担一切法律责任。\n" +
			"2、由于变更受益人产生的法律纠纷本企业愿承担一切法律责任。\n" +
			"3、本申请表的签章为本企业亲自签署，如由此产生的法律纠纷本企业愿承担一切法律责任。\n");
			for (int i = 1; i < 8; i++) {
			header6.createCell(i).setCellValue("");
			}
			for (int i =0; i < 8; i++) {
			if(i>0){
			header6.createCell(i).setCellValue("");
			}
			configInfoCell(sxssfWorkbook,header6.getCell(i),HorizontalAlignment.LEFT);
			}
			header6.setHeight((short) 2000);
			sheet.addMergedRegion(new CellRangeAddress(rowIndex ,rowIndex,0,7));
			Row header7= sheet.createRow(rowIndex+1);
			header7.setHeight((short) 3000);
			header7.createCell(0).setCellValue(cellValue + "签章： \n \n \n" +"日期：年月日 ");
			for (int i =0; i < 8; i++) {
			if(i>0){
			header7.createCell(i).setCellValue("");
			}
			configInfoCell(sxssfWorkbook,header7.getCell(i),HorizontalAlignment.RIGHT);
			}
			sheet.addMergedRegion(new CellRangeAddress(rowIndex+1 ,rowIndex+1,0,7));

			//临时缓冲区
			ByteArrayOutputStream out = new ByteArrayOutputStream();
			//创建临时文件
			sxssfWorkbook.write(out);
			byte [] bookByteAry = out.toByteArray();
			InputStream in = new ByteArrayInputStream(bookByteAry);
			String tempExcel = saveIsToFile(in);
			String tempPdf =System.getProperty("java.io.tmpdir")+File.separator+UUID.randomUUID().toString()+".pdf";
			// ExcelToPdfToolNew.excelToPdf(in,tempPdf);//转PDF
			ExcelToPdfToolNew.excelToPdf(tempExcel,tempPdf);//转PDF
			File f = new File(tempPdf);
			if(f!=null && f.isFile() && f.length()>0){
			return tempPdf;
			}
			} catch (Exception e) {
			// throw new BusinessException(ResponseStatus.EXPORT_EXCEL_ERROR, e);
			}
			return null;
			}


			/**
			* 导出投保申请详单
			* @param insuranceApply 数据
			* @param sheetName Sheet名称
			*/
			public String exportApplyExcelToPdf(InsuranceApply insuranceApply, String sheetName) {
			SXSSFWorkbook sxssfWorkbook;
			try {
			sxssfWorkbook = new SXSSFWorkbook();
			Sheet sheet = sxssfWorkbook.createSheet(sheetName);
			for (int i = 0; i < 6; i++) {
			sheet.setColumnWidth(i, ((i==3\|\| i== 4\|\| i== 5 )?16:8) * 2 * 256);
			}
			sheet.createFreezePane(0, 1);
			sheet.addMergedRegion(new CellRangeAddress(0 ,0,0,5));
			Row title = sheet.createRow(0);
			title.setHeight((short) 2000);
			Cell c = title.createCell(0);
			c.setCellValue(sheetName);
			configFirstCell(sxssfWorkbook,c);
			for (int i = 0; i < 6; i++) {
			if(i>0){
			title.createCell(i).setCellValue("");
			}
			configTitleCell(sxssfWorkbook,title.getCell(i));
			}
			Row header1 = sheet.createRow(1);
			header1.createCell(0).setCellValue("委托人");//投保人
			header1.createCell(1).setCellValue("");
			header1.createCell(2).setCellValue("被保险人");
			header1.createCell(3).setCellValue("");
			header1.createCell(4).setCellValue("保险方案");
			header1.createCell(5).setCellValue("申请提交日期");
			// header1.createCell(5).setCellValue("保险生效止期");
			for (int i = 0; i < 6; i++) {
			configFirstCell(sxssfWorkbook,header1.getCell(i));
			}
			sheet.addMergedRegion(new CellRangeAddress(1 ,1,0,1));
			sheet.addMergedRegion(new CellRangeAddress(1 ,1,2,3));

			Row header2 = sheet.createRow(2);
			header2.createCell(0).setCellValue(insuranceApply.getShopName());
			header2.createCell(1).setCellValue("");
			header2.createCell(2).setCellValue(insuranceApply.getCompanyName());
			header2.createCell(3).setCellValue("");
			header2.createCell(4).setCellValue(insuranceApply.getSolutionsName());
			header2.createCell(5).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(insuranceApply.getCreateDate()));
			sheet.addMergedRegion(new CellRangeAddress(2 ,2,0,1));
			sheet.addMergedRegion(new CellRangeAddress(2 ,2,2,3));
			for (int i = 0; i < 6; i++) {
			configDataCell(sxssfWorkbook,header2.getCell(i));
			}
			Row header4 = sheet.createRow(3);
			header4.createCell(0).setCellValue("序号");
			header4.createCell(1).setCellValue("员工姓名");
			// header4.createCell(2).setCellValue("性别");
			header4.createCell(2).setCellValue("身份证号");
			header4.createCell(3).setCellValue("派遣单位");
			header4.createCell(4).setCellValue("所属工种");
			header4.createCell(5).setCellValue("备注");
			for (int i = 0; i < 6; i++) {
			configFirstCell(sxssfWorkbook,header4.getCell(i));
			}
			// 创建数据记录
			for (int rowIndex = 0; rowIndex <insuranceApply.getApplyDetailList().size(); rowIndex++) {
			ApplyDetail addModel = insuranceApply.getApplyDetailList().get(rowIndex);
			Row header5 = sheet.createRow(rowIndex + 4);
			header5.createCell(0).setCellValue(rowIndex+1);
			header5.createCell(1).setCellValue(StringUtils.defaultString(addModel.getMemberName(),""));
			// header5.createCell(2).setCellValue(Constants.equalsInteger(addModel.getSex(),0)?"男":(Constants.equalsInteger(addModel.getSex(),1)?"女":"-"));
			header5.createCell(2).setCellValue(StringUtils.defaultString(addModel.getIdcardNo(),""));
			header5.createCell(3).setCellValue(StringUtils.defaultString(addModel.getDuName(),""));
			header5.createCell(4).setCellValue(StringUtils.defaultString(addModel.getWorkTypeName(),""));
			header5.createCell(5).setCellValue(StringUtils.defaultString(addModel.getRemark(),""));
			for (int i = 0; i < 6; i++) {
			configDataCell(sxssfWorkbook,header5.getCell(i));
			}
			}
			int rowIndex = 4+insuranceApply.getApplyDetailList().size();
			Row header6= sheet.createRow(rowIndex);
			header6.createCell(0).setCellValue("投保企业申明:\n" +
			"1、在申请书上填写的一切内容属实，否则本企业承担一切法律责任。\n" +
			"2、由于变更受益人产生的法律纠纷本企业愿承担一切法律责任。\n" +
			"3、本申请表的签章为本企业亲自签署，如由此产生的法律纠纷本企业愿承担一切法律责任。\n");
			for (int i = 1; i < 6; i++) {
			header6.createCell(i).setCellValue("");
			}
			for (int i =0; i < 6; i++) {
			if(i>0){
			header6.createCell(i).setCellValue("");
			}
			configInfoCell(sxssfWorkbook,header6.getCell(i),HorizontalAlignment.LEFT);
			}
			header6.setHeight((short) 2000);
			sheet.addMergedRegion(new CellRangeAddress(rowIndex ,rowIndex,0,5));
			Row header7= sheet.createRow(rowIndex+1);
			header7.setHeight((short) 3000);
			header7.createCell(0).setCellValue("被保险人签章： \n \n \n" +"日期：年月日 ");
			for (int i =0; i < 6; i++) {
			if(i>0){
			header7.createCell(i).setCellValue("");
			}
			configInfoCell(sxssfWorkbook,header7.getCell(i),HorizontalAlignment.RIGHT);
			}
			sheet.addMergedRegion(new CellRangeAddress(rowIndex+1 ,rowIndex+1,0,5));

			//临时缓冲区
			ByteArrayOutputStream out = new ByteArrayOutputStream();
			//创建临时文件
			sxssfWorkbook.write(out);
			byte [] bookByteAry = out.toByteArray();
			InputStream in = new ByteArrayInputStream(bookByteAry);
			String tempExcel = saveIsToFile(in);
			String tempPdf =System.getProperty("java.io.tmpdir")+File.separator+UUID.randomUUID().toString()+".pdf";
			// ExcelToPdfToolNew.excelToPdf(in,tempPdf);//转PDF
			ExcelToPdfToolNew.excelToPdf(tempExcel,tempPdf);//转PDF
			File f = new File(tempPdf);
			if(f!=null && f.isFile() && f.length()>0){
			return tempPdf;
			}
			} catch (Exception e) {
			// throw new BusinessException(ResponseStatus.EXPORT_EXCEL_ERROR, e);
			}
			return null;
			}

			private String saveIsToFile(InputStream inputStream) {
			try {
			String fileName =System.getProperty("java.io.tmpdir")+File.separator+UUID.randomUUID().toString()+".xlsx";
			File file = new File(fileName); // 指定保存到本地的文件名及路径
			file.createNewFile();
			OutputStream outputStream = new FileOutputStream(file);
			byte[] buffer = new byte[1024];
			int bytesRead;
			while ((bytesRead = inputStream.read(buffer)) != -1) {
			outputStream.write(buffer, 0, bytesRead);
			}

			outputStream.close();
			inputStream.close();
			return fileName;
			}catch (Exception e){

			}
			return null;
			}

			public void exportUnionApplyDetail(UnionApply model, HttpServletResponse response) {
			try {
			String encodeFileName = URLEncoder.encode("投保详情单_"+model.getCompanyName()+"_"+model.getSolutionName(), Charset.forName("UTF-8").toString()) + ".xlsx";
			response.setHeader("Content-Disposition","attachment;filename=" + encodeFileName);
			response.setContentType("application/octet-stream");
			response.setHeader("eva-opera-type", "download");
			response.setHeader("eva-download-filename", encodeFileName);
			this.exportUnionApplyDetailDo(model,"投保申请表", response.getOutputStream());
			} catch (IOException e) {
			throw new BusinessException(ResponseStatus.EXPORT_EXCEL_ERROR, e);
			}
			}


			public void exportUnionApplyDetailDo (UnionApply data, String sheetName, OutputStream os) {
			SXSSFWorkbook sxssfWorkbook;
			try {
			sxssfWorkbook = new SXSSFWorkbook();
			Sheet sheet = sxssfWorkbook.createSheet(sheetName);
			for (int i = 0; i <7; i++) {
			sheet.setColumnWidth(i, (i==5?20:15) * 2 * 256);
			}
			sheet.createFreezePane(0, 1);
			Row title = sheet.createRow(0);
			title.setHeight((short) 800);
			for (int i = 0; i < 7; i++) {
			title.createCell(i).setCellValue(i==0?sheetName:"");
			configTitleCell(sxssfWorkbook,title.getCell(i));
			}
			sheet.addMergedRegion(new CellRangeAddress(0 ,0,0,6));
			Row header1 = sheet.createRow(1);
			header1.setHeight((short) 500);
			header1.createCell(0).setCellValue("委托人");//投保人
			header1.createCell(1).setCellValue("被保险人");
			header1.createCell(2).setCellValue("保险方案");
			header1.createCell(3).setCellValue("保险生效起期");
			header1.createCell(4).setCellValue("保险生效止期");
			header1.createCell(5).setCellValue("在保/投保人数");
			header1.createCell(6).setCellValue("总费用（元）");
			for (int i = 0; i < 7; i++) {
			configFirstCell(sxssfWorkbook,header1.getCell(i));
			}
			Row header2 = sheet.createRow(2);
			header2.createCell(0).setCellValue(data.getCompanyName());
			header2.createCell(1).setCellValue(data.getCompanyNames());
			header2.createCell(2).setCellValue(data.getSolutionName());
			header2.createCell(3).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getStartTime()));
			header2.createCell(4).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getEndTime()));
			header2.createCell(5).setCellValue(data.getGuaranteeNum()+" / "+data.getInsureNum());
			header2.createCell(6).setCellValue(Constants.formatBigdecimal2Float(data.getFee()).toString());
			for (int i = 0; i < 7; i++) {
			configDataCell(sxssfWorkbook, header2.getCell(i));
			}
			Row header4 = sheet.createRow(3);
			header4.createCell(0).setCellValue("序号");
			header4.createCell(1).setCellValue("员工姓名");
			// header4.createCell(2).setCellValue("性别");
			header4.createCell(2).setCellValue("身份证号");
			header4.createCell(3).setCellValue("被保险人");
			header4.createCell(4).setCellValue("派遣单位");
			header4.createCell(5).setCellValue("所属工种");
			header4.createCell(6).setCellValue("备注");
			header4.setHeight((short) 500);
			for (int i = 0; i < 7; i++) {
			configFirstCell(sxssfWorkbook,header4.getCell(i));
			}
			if(data.getApplyDetailList()!=null){
			// 创建数据记录
			for (int rowIndex = 0; rowIndex < data.getApplyDetailList().size(); rowIndex++) {
			ApplyDetail d = data.getApplyDetailList().get(rowIndex);
			Row header5 = sheet.createRow(rowIndex + 4);
			// header5.setHeight((short) 300);
			header5.createCell(0).setCellValue(rowIndex+1);
			header5.createCell(1).setCellValue(StringUtils.defaultString(d.getMemberName(),""));
			// header5.createCell(2).setCellValue(Constants.equalsInteger(d.getSex(),0)?"男":(Constants.equalsInteger(d.getSex(),1)?"女":"-"));
			header5.createCell(2).setCellValue(StringUtils.defaultString(d.getIdcardNo(),""));
			header5.createCell(3).setCellValue(StringUtils.defaultString(d.getCompanyName(),""));
			header5.createCell(4).setCellValue(StringUtils.defaultString(d.getDuName(),""));
			header5.createCell(5).setCellValue(StringUtils.defaultString(d.getWorkTypeName(),""));
			header5.createCell(6).setCellValue(StringUtils.defaultString(d.getRemark(),""));
			// 设置数据单元格
			for (int i = 0; i < 7; i++) {
			configDataCell(sxssfWorkbook, header5.getCell(i));
			}
			}
			}
			sxssfWorkbook.write(os);
			os.close();
			@@ -707,4 +1430,883 @@
			private Field field;
			}



			/**
			* 导出合并单换厂申请详单
			* @param data 数据
			* @param sheetName Sheet名称
			*/
			public String exportUnionChangeOtherUnitExcelToPdf (UnionChange data, String sheetName) {
			SXSSFWorkbook sxssfWorkbook;
			try {
			sxssfWorkbook = new SXSSFWorkbook();
			Sheet sheet = sxssfWorkbook.createSheet(sheetName);
			for (int i = 0; i < 9; i++) {
			sheet.setColumnWidth(i, ((i==3\|\| i== 4\|\| i== 5 \|\|i== 6 \|\| i==7\|\| i==8 \|\| i==9)?16:8) * 2 * 256);
			}
			sheet.createFreezePane(0, 1);
			sheet.addMergedRegion(new CellRangeAddress(0 ,0,0,8));
			Row title = sheet.createRow(0);
			title.setHeight((short) 1000);
			Cell c = title.createCell(0);
			c.setCellValue(sheetName);
			configFirstCell(sxssfWorkbook,c);
			for (int i = 0; i < 9; i++) {
			if(i>0){
			title.createCell(i).setCellValue("");
			}
			configTitleCell(sxssfWorkbook,title.getCell(i));
			}
			Row header1 = sheet.createRow(1);
			header1.createCell(0).setCellValue("委托人");//投保人
			header1.createCell(1).setCellValue("");
			header1.createCell(2).setCellValue("保险方案");
			header1.createCell(3).setCellValue("");
			header1.createCell(4).setCellValue("保单号");
			header1.createCell(5).setCellValue("保险生效起期");
			header1.createCell(6).setCellValue("保险生效止期");
			header1.createCell(7).setCellValue("换厂人数");
			header1.createCell(8).setCellValue("期望批单生效期");

			sheet.addMergedRegion(new CellRangeAddress(1 ,1,0,1));
			sheet.addMergedRegion(new CellRangeAddress(1 ,1,2,3));

			for (int i = 0; i < 9; i++) {
			configFirstCell(sxssfWorkbook,header1.getCell(i));
			}
			Row header2 = sheet.createRow(2);
			header2.createCell(0).setCellValue(data.getShopName());
			header2.createCell(1).setCellValue("");
			header2.createCell(2).setCellValue(data.getSolutionsName());
			header2.createCell(3).setCellValue("");
			header2.createCell(4).setCellValue(data.getApplyCode());
			header2.createCell(5).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getStartTime()));
			header2.createCell(6).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getEndTime()));
			header2.createCell(7).setCellValue(data.getApplyChagneDetailList().stream().filter(i->i.getType().equals(Constants.TWO)).collect(Collectors.toList()).size());
			header2.createCell(8).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getValidTime()));
			// sheet.addMergedRegion(new CellRangeAddress(2 ,2,1,3));

			sheet.addMergedRegion(new CellRangeAddress(2 ,2,0,1));
			sheet.addMergedRegion(new CellRangeAddress(2 ,2,2,3));
			for (int i = 0; i < 9; i++) {
			configDataCell(sxssfWorkbook,header2.getCell(i));
			}
			Row header3 = sheet.createRow(3);
			header3.createCell(0).setCellValue("被保险人");
			header3.createCell(1).setCellValue("");
			header3.createCell(2).setCellValue(data.getCompanyNames());
			header3.createCell(3).setCellValue("");
			header3.createCell(4).setCellValue("");
			header3.createCell(5).setCellValue("");
			header3.createCell(6).setCellValue("");
			header3.createCell(7).setCellValue("");
			header3.createCell(8).setCellValue("");
			for (int i = 0; i < 2; i++) {
			configFirstCell(sxssfWorkbook,header3.getCell(i));
			}
			for (int i = 2; i < 9; i++) {
			configDataCell(sxssfWorkbook,header3.getCell(i));
			}
			sheet.addMergedRegion(new CellRangeAddress(3 ,3,0,1));
			sheet.addMergedRegion(new CellRangeAddress(3 ,3,2,8));


			List<ApplyChagneDetail> otherChangeDetailList = data.getApplyChagneDetailList().stream().filter(i->i.getType().equals(Constants.TWO)).collect(Collectors.toList());

			Integer row = 3;

			if(CollectionUtils.isNotEmpty(otherChangeDetailList)){
			row = row + 1;
			Row header5 = sheet.createRow(row);
			header5.createCell(0).setCellValue("序号");
			header5.createCell(1).setCellValue("员工姓名");
			// header5.createCell(2).setCellValue("性别");
			header5.createCell(2).setCellValue("身份证号");
			header5.createCell(3).setCellValue("被保险人");
			header5.createCell(4).setCellValue("原派遣单位");
			header5.createCell(5).setCellValue("原所属工种");
			header5.createCell(6).setCellValue("更改后派遣单位");
			header5.createCell(7).setCellValue("更改后所属工种");
			header5.createCell(8).setCellValue("备注");
			for (int i = 0; i < 9; i++) {
			configFirstCell(sxssfWorkbook,header5.getCell(i));
			}
			for (int rowIndex = 0; rowIndex < otherChangeDetailList.size(); rowIndex++) {
			row = row + 1;
			Row header6 = sheet.createRow(row);
			ApplyChagneDetail applyChagneDetail = otherChangeDetailList.get(rowIndex);
			header6.createCell(0).setCellValue(rowIndex + 1);
			header6.createCell(1).setCellValue(applyChagneDetail.getMemberName());
			// header6.createCell(2).setCellValue(Constants.equalsInteger(applyChagneDetail.getSex(),0)?"男":(Constants.equalsInteger(applyChagneDetail.getSex(),1)?"女":"-"));
			header6.createCell(2).setCellValue(applyChagneDetail.getIdcardNo());
			header6.createCell(3).setCellValue(applyChagneDetail.getCompanyName());
			header6.createCell(4).setCellValue(applyChagneDetail.getOldDuName());
			header6.createCell(5).setCellValue(applyChagneDetail.getOldWorkTypeName());
			header6.createCell(6).setCellValue(applyChagneDetail.getDuName());
			header6.createCell(7).setCellValue(applyChagneDetail.getWorkTypeName());
			header6.createCell(8).setCellValue(applyChagneDetail.getRemark());
			for (int i = 0; i < 9; i++) {
			configDataCell(sxssfWorkbook,header6.getCell(i));
			}
			}
			}
			int rowIndex = row + 1 ;
			Row header6= sheet.createRow(rowIndex);
			header6.createCell(0).setCellValue("投保企业申明:\n" +
			"1、在申请书上填写的一切内容属实，否则本企业承担一切法律责任。\n" +
			"2、由于变更受益人产生的法律纠纷本企业愿承担一切法律责任。\n" +
			"3、本申请表的签章为本企业亲自签署，如由此产生的法律纠纷本企业愿承担一切法律责任。\n");
			for (int i = 1; i < 9; i++) {
			header6.createCell(i).setCellValue("");
			}
			for (int i =0; i < 9; i++) {
			if(i>0){
			header6.createCell(i).setCellValue("");
			}
			configInfoCell(sxssfWorkbook,header6.getCell(i),HorizontalAlignment.LEFT);
			}
			header6.setHeight((short) 2000);
			sheet.addMergedRegion(new CellRangeAddress(rowIndex ,rowIndex,0,8));
			Row header7= sheet.createRow(rowIndex+1);
			header7.setHeight((short) 3000);
			header7.createCell(0).setCellValue("投保人签章： \n \n \n" +"日期：年月日 ");
			for (int i =0; i < 9; i++) {
			if(i>0){
			header7.createCell(i).setCellValue("");
			}
			configInfoCell(sxssfWorkbook,header7.getCell(i),HorizontalAlignment.RIGHT);
			}
			sheet.addMergedRegion(new CellRangeAddress(rowIndex+1 ,rowIndex+1,0,8));

			//临时缓冲区
			ByteArrayOutputStream out = new ByteArrayOutputStream();
			//创建临时文件
			sxssfWorkbook.write(out);
			byte [] bookByteAry = out.toByteArray();
			InputStream in = new ByteArrayInputStream(bookByteAry);
			String tempExcel = saveIsToFile(in);
			String tempPdf =System.getProperty("java.io.tmpdir")+File.separator+UUID.randomUUID().toString()+".pdf";
			// ExcelToPdfToolNew.excelToPdf(in,tempPdf);//转PDF
			ExcelToPdfToolNew.excelToPdf(tempExcel,tempPdf);//转PDF
			File f = new File(tempPdf);
			if(f!=null && f.isFile() && f.length()>0){
			return tempPdf;
			}
			} catch (Exception e) {
			// throw new BusinessException(ResponseStatus.EXPORT_EXCEL_ERROR, e);
			}
			return null;
			}



			/**
			* 导出合并单加减保申请详单
			* @param data 数据
			* @param sheetName Sheet名称
			*/
			public String exportUnionChangeExcelToPdf (UnionChange data, String sheetName) {
			SXSSFWorkbook sxssfWorkbook;
			try {
			sxssfWorkbook = new SXSSFWorkbook();
			Sheet sheet = sxssfWorkbook.createSheet(sheetName);
			for (int i = 0; i < 9; i++) {
			sheet.setColumnWidth(i, ((i==0\|\| i==1\|\| i== 3\|\| i== 4\|\| i== 7\|\| i== 8)?16:8) * 2 * 256);
			}
			sheet.createFreezePane(0, 1);
			sheet.addMergedRegion(new CellRangeAddress(0 ,0,0,8));
			Row title = sheet.createRow(0);
			title.setHeight((short) 2000);
			for (int i = 0; i < 9; i++) {
			title.createCell(i).setCellValue(i==0?sheetName:"");
			configTitleCell(sxssfWorkbook,title.getCell(i));
			}
			Row header1 = sheet.createRow(1);
			header1.createCell(0).setCellValue("委托人");//投保人
			header1.createCell(1).setCellValue("保险方案");
			header1.createCell(2).setCellValue("保单号");
			header1.createCell(3).setCellValue("保险生效起期");
			header1.createCell(4).setCellValue("保险生效止期");
			header1.createCell(5).setCellValue("加保人数");
			header1.createCell(6).setCellValue("减保人数");
			header1.createCell(7).setCellValue("批增生效日期");
			header1.createCell(8).setCellValue("批减生效日期");
			for (int i = 0; i < 9; i++) {
			configFirstCell(sxssfWorkbook,header1.getCell(i));
			}

			Row header2 = sheet.createRow(2);
			header2.createCell(0).setCellValue(data.getShopName());
			header2.createCell(1).setCellValue(data.getSolutionsName());
			header2.createCell(2).setCellValue(data.getApplyCode());
			header2.createCell(3).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getStartTime()));
			header2.createCell(4).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getEndTime()));
			header2.createCell(5).setCellValue(data.getApplyChagneDetailList().stream().filter(i->i.getType().equals(Constants.ZERO)).collect(Collectors.toList()).size());
			header2.createCell(6).setCellValue(data.getApplyChagneDetailList().stream().filter(i->i.getType().equals(Constants.ONE)).collect(Collectors.toList()).size());
			header2.createCell(7).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getApplyStartTime()));
			header2.createCell(8).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getDelValidTime()));
			for (int i = 0; i < 9; i++) {
			configDataCell(sxssfWorkbook, header2.getCell(i));
			}

			Row header3 = sheet.createRow(3);
			header3.createCell(0).setCellValue("被保险人");
			header3.createCell(1).setCellValue(data.getCompanyNames());
			header3.createCell(2).setCellValue("");
			header3.createCell(3).setCellValue("");
			header3.createCell(4).setCellValue("");
			header3.createCell(5).setCellValue("");
			header3.createCell(6).setCellValue("");
			header3.createCell(7).setCellValue("");
			header3.createCell(8).setCellValue("");
			for (int i = 0; i < 9; i++) {
			configFirstCell(sxssfWorkbook,header3.getCell(i));
			}
			sheet.addMergedRegion(new CellRangeAddress(3 ,3,1,8));
			Integer row = 3;
			List<ApplyChagneDetail> addChangeDetailList = data.getApplyChagneDetailList().stream().filter(i->i.getType().equals(Constants.ZERO)).collect(Collectors.toList());
			if(CollectionUtils.isNotEmpty(addChangeDetailList)){
			row = row + 1;
			Row header4 = sheet.createRow(row);
			header4.createCell(0).setCellValue("加保人员");
			for (int i = 0; i < 8; i++) {
			configFirstCell(sxssfWorkbook, header4.getCell(i));
			}
			sheet.addMergedRegion(new CellRangeAddress(row ,row,0,8));
			row = row + 1;
			Row header5 = sheet.createRow(row);
			header5.createCell(0).setCellValue("序号");
			header5.createCell(1).setCellValue("员工姓名");
			header5.createCell(2).setCellValue("性别");
			header5.createCell(3).setCellValue("身份证号");
			header5.createCell(4).setCellValue("被保险人");
			header5.createCell(5).setCellValue("派遣单位");
			header5.createCell(6).setCellValue("");
			header5.createCell(7).setCellValue("所属工种");
			header5.createCell(8).setCellValue("备注");
			for (int i = 0; i < 9; i++) {
			configFirstCell(sxssfWorkbook,header5.getCell(i));
			}
			sheet.addMergedRegion(new CellRangeAddress(row ,row,5,6));
			for (int rowIndex = 0; rowIndex < addChangeDetailList.size(); rowIndex++) {
			row = row + 1 ;
			Row header6 = sheet.createRow(row);
			ApplyChagneDetail applyChagneDetail = addChangeDetailList.get(rowIndex);
			header6.createCell(0).setCellValue(rowIndex + 1);
			header6.createCell(1).setCellValue(applyChagneDetail.getMemberName());
			header6.createCell(2).setCellValue(Constants.equalsInteger(applyChagneDetail.getSex(),0)?"男":(Constants.equalsInteger(applyChagneDetail.getSex(),1)?"女":"-"));
			header6.createCell(3).setCellValue(applyChagneDetail.getIdcardNo());
			header6.createCell(4).setCellValue(applyChagneDetail.getCompanyName());
			header6.createCell(5).setCellValue(applyChagneDetail.getDuName());
			header6.createCell(6).setCellValue("");
			header6.createCell(7).setCellValue(applyChagneDetail.getWorkTypeName());
			header6.createCell(8).setCellValue(applyChagneDetail.getRemark());
			for (int i = 0; i < 9; i++) {
			configDataCell(sxssfWorkbook,header6.getCell(i));
			}
			sheet.addMergedRegion(new CellRangeAddress(row ,row,5,6));
			}
			}

			List<ApplyChagneDetail> reduceChangeDetailList = data.getApplyChagneDetailList().stream().filter(i->i.getType().equals(Constants.ONE)).collect(Collectors.toList());
			row = row + 1;
			if(CollectionUtils.isNotEmpty(reduceChangeDetailList)){
			Row header4 = sheet.createRow(row);
			header4.createCell(0).setCellValue("减保人员");
			for (int i = 0; i < 9; i++) {
			configFirstCell(sxssfWorkbook, header4.getCell(i));
			}
			sheet.addMergedRegion(new CellRangeAddress(row ,row,0,8));

			row = row + 1;
			Row header5 = sheet.createRow(row);
			header5.createCell(0).setCellValue("序号");
			header5.createCell(1).setCellValue("员工姓名");
			header5.createCell(2).setCellValue("性别");
			header5.createCell(3).setCellValue("身份证号");
			header5.createCell(4).setCellValue("被保险人");
			header5.createCell(5).setCellValue("派遣单位");
			header5.createCell(6).setCellValue("");
			header5.createCell(7).setCellValue("所属工种");
			header5.createCell(8).setCellValue("备注");
			for (int i = 0; i < 9; i++) {
			configFirstCell(sxssfWorkbook,header5.getCell(i));
			}
			sheet.addMergedRegion(new CellRangeAddress(row ,row,5,6));

			row = row + 1 ;
			for (int rowIndex = 0; rowIndex < reduceChangeDetailList.size(); rowIndex++) {
			Row header6 = sheet.createRow(row);
			ApplyChagneDetail applyChagneDetail = reduceChangeDetailList.get(rowIndex);
			header6.createCell(0).setCellValue(rowIndex + 1);
			header6.createCell(1).setCellValue(applyChagneDetail.getMemberName());
			header6.createCell(2).setCellValue(Constants.equalsInteger(applyChagneDetail.getSex(),0)?"男":(Constants.equalsInteger(applyChagneDetail.getSex(),1)?"女":"-"));
			header6.createCell(3).setCellValue(applyChagneDetail.getIdcardNo());
			header6.createCell(4).setCellValue(applyChagneDetail.getCompanyName());
			header6.createCell(5).setCellValue(applyChagneDetail.getDuName());
			header6.createCell(6).setCellValue("");
			header6.createCell(7).setCellValue(applyChagneDetail.getWorkTypeName());
			header6.createCell(8).setCellValue(applyChagneDetail.getRemark());
			for (int i = 0; i < 9; i++) {
			configDataCell(sxssfWorkbook,header6.getCell(i));
			}
			sheet.addMergedRegion(new CellRangeAddress(row ,row,5,6));
			row = row + 1;
			}
			}

			int rowIndex = row + 1;
			Row header6= sheet.createRow(rowIndex);
			header6.createCell(0).setCellValue("投保企业申明:\n" +
			"1、在申请书上填写的一切内容属实，否则本企业承担一切法律责任。\n" +
			"2、由于变更受益人产生的法律纠纷本企业愿承担一切法律责任。\n" +
			"3、本申请表的签章为本企业亲自签署，如由此产生的法律纠纷本企业愿承担一切法律责任。\n");
			for (int i = 1; i < 9; i++) {
			header6.createCell(i).setCellValue("");
			}
			for (int i =0; i < 9; i++) {
			if(i>0){
			header6.createCell(i).setCellValue("");
			}
			configInfoCell(sxssfWorkbook,header6.getCell(i),HorizontalAlignment.LEFT);
			}
			header6.setHeight((short) 2000);
			sheet.addMergedRegion(new CellRangeAddress(rowIndex ,rowIndex,0,8));
			Row header7= sheet.createRow(rowIndex+1);
			header7.setHeight((short) 3000);
			header7.createCell(0).setCellValue("投保人签章： \n \n \n" +"日期：年月日 ");
			for (int i =0; i < 9; i++) {
			if(i>0){
			header7.createCell(i).setCellValue("");
			}
			configInfoCell(sxssfWorkbook,header7.getCell(i),HorizontalAlignment.RIGHT);
			}
			sheet.addMergedRegion(new CellRangeAddress(rowIndex+1 ,rowIndex+1,0,8));

			//临时缓冲区
			ByteArrayOutputStream out = new ByteArrayOutputStream();
			//创建临时文件
			sxssfWorkbook.write(out);
			byte [] bookByteAry = out.toByteArray();
			InputStream in = new ByteArrayInputStream(bookByteAry);
			String tempExcel = saveIsToFile(in);
			String tempPdf = System.getProperty("java.io.tmpdir")+File.separator+UUID.randomUUID().toString()+".pdf"; //"d:/1.docx";
			// ExcelToPdfToolNew.excelToPdf(in,tempPdf);//转PDF
			ExcelToPdfToolNew.excelToPdf(tempExcel,tempPdf);//转PDF
			File f = new File(tempPdf);
			if(f!=null && f.isFile() && f.length()>0){
			return tempPdf;
			}
			} catch (Exception e) {
			e.printStackTrace();
			// throw new BusinessException(ResponseStatus.EXPORT_EXCEL_ERROR, e);
			}
			return null;
			}




			public void exportUnionChangeDetail(UnionChange model, HttpServletResponse response) {
			try {
			String fileName = "加减保详情单";
			if(model.getType().equals(Constants.ONE)){
			fileName = "更换派遣单位详情单";
			}
			String encodeFileName = URLEncoder.encode(fileName +"_"+model.getShopName(), Charset.forName("UTF-8").toString()) + ".xlsx";
			response.setHeader("Content-Disposition","attachment;filename=" + encodeFileName);
			response.setContentType("application/octet-stream");
			response.setHeader("eva-opera-type", "download");
			response.setHeader("eva-download-filename", encodeFileName);
			if(model.getType().equals(Constants.ZERO)){
			this.exportUnionChangeAddDetailDo(model,"加减保申请表", response.getOutputStream());
			}else{
			this.exportUnionChangeDuDetailDo(model,"更换派遣单位申请表", response.getOutputStream());
			}

			} catch (IOException e) {
			throw new BusinessException(ResponseStatus.EXPORT_EXCEL_ERROR, e);
			}
			}

			public void exportUnionChangeAddDetailDo (UnionChange data, String sheetName, OutputStream os) {
			SXSSFWorkbook sxssfWorkbook;
			try {
			sxssfWorkbook = new SXSSFWorkbook();
			Sheet sheet = sxssfWorkbook.createSheet(sheetName);
			for (int i = 0; i < 9; i++) {
			sheet.setColumnWidth(i, ((i==0\|\| i==1\|\| i== 3\|\| i== 4\|\| i== 7\|\| i== 8)?16:8) * 2 * 256);
			}
			sheet.createFreezePane(0, 1);
			sheet.addMergedRegion(new CellRangeAddress(0 ,0,0,8));
			Row title = sheet.createRow(0);
			title.setHeight((short) 2000);
			for (int i = 0; i < 9; i++) {
			title.createCell(i).setCellValue(i==0?sheetName:"");
			configTitleCell(sxssfWorkbook,title.getCell(i));
			}
			Row header1 = sheet.createRow(1);
			header1.createCell(0).setCellValue("委托人");//投保人
			header1.createCell(1).setCellValue("保险方案");
			header1.createCell(2).setCellValue("保单号");
			header1.createCell(3).setCellValue("保险生效起期");
			header1.createCell(4).setCellValue("保险生效止期");
			header1.createCell(5).setCellValue("加保人数");
			header1.createCell(6).setCellValue("减保人数");
			header1.createCell(7).setCellValue(Constants.getChangeApplyTimeTitle(1,data));
			header1.createCell(8).setCellValue(Constants.getChangeApplyTimeTitle(0,data));
			for (int i = 0; i < 9; i++) {
			configFirstCell(sxssfWorkbook,header1.getCell(i));
			}

			Row header2 = sheet.createRow(2);
			header2.createCell(0).setCellValue(data.getShopName());
			header2.createCell(1).setCellValue(data.getSolutionsName());
			header2.createCell(2).setCellValue(data.getApplyCode());
			header2.createCell(3).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getStartTime()));
			header2.createCell(4).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getEndTime()));
			header2.createCell(5).setCellValue(data.getApplyChagneDetailList().stream().filter(i->i.getType().equals(Constants.ZERO)).collect(Collectors.toList()).size());
			header2.createCell(6).setCellValue(data.getApplyChagneDetailList().stream().filter(i->i.getType().equals(Constants.ONE)).collect(Collectors.toList()).size());
			header2.createCell(7).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getApplyStartTime()));
			header2.createCell(8).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getDelValidTime()));
			for (int i = 0; i < 9; i++) {
			configDataCell(sxssfWorkbook, header2.getCell(i));
			}

			Row header3 = sheet.createRow(3);
			header3.createCell(0).setCellValue("被保险人");
			header3.createCell(1).setCellValue(data.getCompanyNames());
			header3.createCell(2).setCellValue("");
			header3.createCell(3).setCellValue("");
			header3.createCell(4).setCellValue("");
			header3.createCell(5).setCellValue("");
			header3.createCell(6).setCellValue("");
			header3.createCell(7).setCellValue("");
			header3.createCell(8).setCellValue("");
			for (int i = 0; i < 9; i++) {
			configFirstCell(sxssfWorkbook,header3.getCell(i));
			}
			sheet.addMergedRegion(new CellRangeAddress(3 ,3,1,8));
			Integer row = 4;
			List<ApplyChagneDetail> addChangeDetailList = data.getApplyChagneDetailList().stream().filter(i->i.getType().equals(Constants.ZERO)).collect(Collectors.toList());
			if(CollectionUtils.isNotEmpty(addChangeDetailList)){
			Row header4 = sheet.createRow(row);
			header4.createCell(0).setCellValue("加保人员");
			for (int i = 0; i < 8; i++) {
			configFirstCell(sxssfWorkbook, header4.getCell(i));
			}
			sheet.addMergedRegion(new CellRangeAddress(row ,row,0,8));
			row = row + 1;
			Row header5 = sheet.createRow(row);
			header5.createCell(0).setCellValue("序号");
			header5.createCell(1).setCellValue("员工姓名");
			header5.createCell(2).setCellValue("性别");
			header5.createCell(3).setCellValue("身份证号");
			header5.createCell(4).setCellValue("被保险人");
			header5.createCell(5).setCellValue("派遣单位");
			header5.createCell(6).setCellValue("");
			header5.createCell(7).setCellValue("所属工种");
			header5.createCell(8).setCellValue("备注");
			for (int i = 0; i < 9; i++) {
			configFirstCell(sxssfWorkbook,header5.getCell(i));
			}
			sheet.addMergedRegion(new CellRangeAddress(row ,row,5,6));
			for (int rowIndex = 0; rowIndex < addChangeDetailList.size(); rowIndex++) {
			row = row + 1 ;
			Row header6 = sheet.createRow(row);
			ApplyChagneDetail applyChagneDetail = addChangeDetailList.get(rowIndex);
			header6.createCell(0).setCellValue(rowIndex + 1);
			header6.createCell(1).setCellValue(applyChagneDetail.getMemberName());
			header6.createCell(2).setCellValue(Constants.equalsInteger(applyChagneDetail.getSex(),0)?"男":(Constants.equalsInteger(applyChagneDetail.getSex(),1)?"女":"-"));
			header6.createCell(3).setCellValue(applyChagneDetail.getIdcardNo());
			header6.createCell(4).setCellValue(applyChagneDetail.getCompanyName());
			header6.createCell(5).setCellValue(applyChagneDetail.getDuName());
			header6.createCell(6).setCellValue("");
			header6.createCell(7).setCellValue(applyChagneDetail.getWorkTypeName());
			header6.createCell(8).setCellValue(applyChagneDetail.getRemark());
			for (int i = 0; i < 9; i++) {
			configDataCell(sxssfWorkbook,header6.getCell(i));
			}
			sheet.addMergedRegion(new CellRangeAddress(row ,row,5,6));
			}
			}

			List<ApplyChagneDetail> reduceChangeDetailList = data.getApplyChagneDetailList().stream().filter(i->i.getType().equals(Constants.ONE)).collect(Collectors.toList());
			row = row + 1;
			if(CollectionUtils.isNotEmpty(reduceChangeDetailList)){
			Row header4 = sheet.createRow(row);
			header4.createCell(0).setCellValue("减保人员");
			for (int i = 0; i < 9; i++) {
			configFirstCell(sxssfWorkbook, header4.getCell(i));
			}
			sheet.addMergedRegion(new CellRangeAddress(row ,row,0,8));

			row = row + 1;
			Row header5 = sheet.createRow(row);
			header5.createCell(0).setCellValue("序号");
			header5.createCell(1).setCellValue("员工姓名");
			header5.createCell(2).setCellValue("性别");
			header5.createCell(3).setCellValue("身份证号");
			header5.createCell(4).setCellValue("被保险人");
			header5.createCell(5).setCellValue("派遣单位");
			header5.createCell(6).setCellValue("");
			header5.createCell(7).setCellValue("所属工种");
			header5.createCell(8).setCellValue("备注");
			for (int i = 0; i < 9; i++) {
			configFirstCell(sxssfWorkbook,header5.getCell(i));
			}
			sheet.addMergedRegion(new CellRangeAddress(row ,row,5,6));

			row = row + 1 ;
			for (int rowIndex = 0; rowIndex < reduceChangeDetailList.size(); rowIndex++) {
			row = row + rowIndex;
			Row header6 = sheet.createRow(row);
			ApplyChagneDetail applyChagneDetail = reduceChangeDetailList.get(rowIndex);
			header6.createCell(0).setCellValue(rowIndex + 1);
			header6.createCell(1).setCellValue(applyChagneDetail.getMemberName());
			header6.createCell(2).setCellValue(Constants.equalsInteger(applyChagneDetail.getSex(),0)?"男":(Constants.equalsInteger(applyChagneDetail.getSex(),1)?"女":"-"));
			header6.createCell(3).setCellValue(applyChagneDetail.getIdcardNo());
			header6.createCell(4).setCellValue(applyChagneDetail.getCompanyName());
			header6.createCell(5).setCellValue(applyChagneDetail.getDuName());
			header6.createCell(6).setCellValue("");
			header6.createCell(7).setCellValue(applyChagneDetail.getWorkTypeName());
			header6.createCell(8).setCellValue(applyChagneDetail.getRemark());
			for (int i = 0; i < 9; i++) {
			configDataCell(sxssfWorkbook,header6.getCell(i));
			}
			sheet.addMergedRegion(new CellRangeAddress(row ,row,5,6));
			}
			}
			sxssfWorkbook.write(os);
			os.close();
			} catch (Exception e) {
			throw new BusinessException(ResponseStatus.EXPORT_EXCEL_ERROR, e);
			} finally {
			if (os != null) {
			try {
			os.close();
			} catch (IOException e) {
			e.printStackTrace();
			}
			}
			}
			}


			public void exportUnionChangeDuDetailDo (UnionChange data, String sheetName, OutputStream os) {
			SXSSFWorkbook sxssfWorkbook;
			try {
			sxssfWorkbook = new SXSSFWorkbook();
			Sheet sheet = sxssfWorkbook.createSheet(sheetName);
			for (int i = 0; i < 9; i++) {
			sheet.setColumnWidth(i, ((i==3\|\| i== 4\|\| i== 5 \|\|i== 6 \|\| i==7\|\| i==8 \|\| i==9)?16:8) * 2 * 256);
			}
			sheet.createFreezePane(0, 1);
			sheet.addMergedRegion(new CellRangeAddress(0 ,0,0,8));
			Row title = sheet.createRow(0);
			title.setHeight((short) 1000);
			Cell c = title.createCell(0);
			c.setCellValue(sheetName);
			configFirstCell(sxssfWorkbook,c);
			for (int i = 0; i < 9; i++) {
			if(i>0){
			title.createCell(i).setCellValue("");
			}
			configTitleCell(sxssfWorkbook,title.getCell(i));
			}
			Row header1 = sheet.createRow(1);
			header1.createCell(0).setCellValue("委托人");//投保人
			header1.createCell(1).setCellValue("");
			header1.createCell(2).setCellValue("保险方案");
			header1.createCell(3).setCellValue("");
			header1.createCell(4).setCellValue("保单号");
			header1.createCell(5).setCellValue("保险生效起期");
			header1.createCell(6).setCellValue("保险生效止期");
			header1.createCell(7).setCellValue("换厂人数");
			header1.createCell(8).setCellValue(Constants.getChangeApplyTimeTitle(2,data));

			sheet.addMergedRegion(new CellRangeAddress(1 ,1,0,1));
			sheet.addMergedRegion(new CellRangeAddress(1 ,1,2,3));

			for (int i = 0; i < 9; i++) {
			configFirstCell(sxssfWorkbook,header1.getCell(i));
			}
			Row header2 = sheet.createRow(2);
			header2.createCell(0).setCellValue(data.getShopName());
			header2.createCell(1).setCellValue("");
			header2.createCell(2).setCellValue(data.getSolutionsName());
			header2.createCell(3).setCellValue("");
			header2.createCell(4).setCellValue(data.getApplyCode());
			header2.createCell(5).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getStartTime()));
			header2.createCell(6).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getEndTime()));
			header2.createCell(7).setCellValue(data.getApplyChagneDetailList().stream().filter(i->i.getType().equals(Constants.TWO)).collect(Collectors.toList()).size());
			header2.createCell(8).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getApplyStartTime()));
			// sheet.addMergedRegion(new CellRangeAddress(2 ,2,1,3));

			sheet.addMergedRegion(new CellRangeAddress(2 ,2,0,1));
			sheet.addMergedRegion(new CellRangeAddress(2 ,2,2,3));
			for (int i = 0; i < 9; i++) {
			configDataCell(sxssfWorkbook,header2.getCell(i));
			}
			Row header3 = sheet.createRow(3);
			header3.createCell(0).setCellValue("被保险人");
			header3.createCell(1).setCellValue("");
			header3.createCell(2).setCellValue(data.getCompanyNames());
			header3.createCell(3).setCellValue("");
			header3.createCell(4).setCellValue("");
			header3.createCell(5).setCellValue("");
			header3.createCell(6).setCellValue("");
			header3.createCell(7).setCellValue("");
			header3.createCell(8).setCellValue("");
			for (int i = 0; i < 2; i++) {
			configFirstCell(sxssfWorkbook,header3.getCell(i));
			}
			for (int i = 2; i < 9; i++) {
			configDataCell(sxssfWorkbook,header3.getCell(i));
			}
			sheet.addMergedRegion(new CellRangeAddress(3 ,3,0,1));
			sheet.addMergedRegion(new CellRangeAddress(3 ,3,2,8));


			List<ApplyChagneDetail> otherChangeDetailList = data.getApplyChagneDetailList().stream().filter(i->i.getType().equals(Constants.TWO)).collect(Collectors.toList());

			Integer row = 3;

			if(CollectionUtils.isNotEmpty(otherChangeDetailList)){
			row = row + 1;
			Row header5 = sheet.createRow(row);
			header5.createCell(0).setCellValue("序号");
			header5.createCell(1).setCellValue("员工姓名");
			// header5.createCell(2).setCellValue("性别");
			header5.createCell(2).setCellValue("身份证号");
			header5.createCell(3).setCellValue("被保险人");
			header5.createCell(4).setCellValue("原派遣单位");
			header5.createCell(5).setCellValue("原所属工种");
			header5.createCell(6).setCellValue("更改后派遣单位");
			header5.createCell(7).setCellValue("更改后所属工种");
			header5.createCell(8).setCellValue("备注");
			for (int i = 0; i < 9; i++) {
			configFirstCell(sxssfWorkbook,header5.getCell(i));
			}
			for (int rowIndex = 0; rowIndex < otherChangeDetailList.size(); rowIndex++) {
			row = row + 1;
			Row header6 = sheet.createRow(row);
			ApplyChagneDetail applyChagneDetail = otherChangeDetailList.get(rowIndex);
			header6.createCell(0).setCellValue(rowIndex + 1);
			header6.createCell(1).setCellValue(applyChagneDetail.getMemberName());
			// header6.createCell(2).setCellValue(Constants.equalsInteger(applyChagneDetail.getSex(),0)?"男":(Constants.equalsInteger(applyChagneDetail.getSex(),1)?"女":"-"));
			header6.createCell(2).setCellValue(applyChagneDetail.getIdcardNo());
			header6.createCell(3).setCellValue(applyChagneDetail.getCompanyName());
			header6.createCell(4).setCellValue(applyChagneDetail.getOldDuName());
			header6.createCell(5).setCellValue(applyChagneDetail.getOldWorkTypeName());
			header6.createCell(6).setCellValue(applyChagneDetail.getDuName());
			header6.createCell(7).setCellValue(applyChagneDetail.getWorkTypeName());
			header6.createCell(8).setCellValue(applyChagneDetail.getRemark());
			for (int i = 0; i < 9; i++) {
			configDataCell(sxssfWorkbook,header6.getCell(i));
			}
			}
			}

			sxssfWorkbook.write(os);
			os.close();
			} catch (Exception e) {
			throw new BusinessException(ResponseStatus.EXPORT_EXCEL_ERROR, e);
			} finally {
			if (os != null) {
			try {
			os.close();
			} catch (IOException e) {
			e.printStackTrace();
			}
			}
			}
			}

			public void exportApplyJiajianBaoExcel(InsuranceApply insuranceApply,List<ApplyChange> applyChangeList, HttpServletResponse response) {
			try {
			String encodeFileName = URLEncoder.encode(insuranceApply.getCode(), Charset.forName("UTF-8").toString()) + ".xlsx";
			response.setHeader("Content-Disposition","attachment;filename=" + encodeFileName);
			response.setContentType("application/octet-stream");
			response.setHeader("eva-opera-type", "download");
			response.setHeader("eva-download-filename", encodeFileName);
			this.exportApplyJiajianBaoExcelDo(applyChangeList, response.getOutputStream());
			} catch (IOException e) {
			throw new BusinessException(ResponseStatus.EXPORT_EXCEL_ERROR, e);
			}
			}


			public String getSheetName(List<String> nameList ,String sheetName){
			if(CollectionUtils.isEmpty(nameList)){
			return sheetName;
			}
			Integer count = nameList.stream().filter(i->i.equals(sheetName)).collect(Collectors.toList()).size();
			if(count>Constants.ZERO){
			return sheetName + "("+(count)+")";
			}else{
			return sheetName;
			}

			}


			/**
			* 导出保单下加减保申请详单
			* @param applyChangeList 数据
			* @param sheetName Sheet名称
			* @param os 输出流
			*/
			public void exportApplyJiajianBaoExcelDo (List<ApplyChange> applyChangeList, OutputStream os) {
			SXSSFWorkbook sxssfWorkbook;
			try {
			sxssfWorkbook = new SXSSFWorkbook();
			List<String> nameList = new ArrayList<>();
			for (int x = 0; x < applyChangeList.size(); x++) {

			ApplyChange data = applyChangeList.get(x);
			Sheet sheet = sxssfWorkbook.createSheet(getSheetName(nameList,com.doumee.core.utils.DateUtil.getDate(data.getCreateDate(),"yyyy-MM-dd")));
			for (int i = 0; i < 13; i++) {
			sheet.setColumnWidth(i, ((i==2\|\| i== 3 \|\| i==8\|\| i==9\|\| i==10)?16:8) * 2 * 256);
			}
			sheet.createFreezePane(0, 1);
			sheet.addMergedRegion(new CellRangeAddress(0 ,0,0,12));
			Row title = sheet.createRow(0);
			title.setHeight((short) 800);
			Cell c = title.createCell(0);
			c.setCellValue("加减保记录");
			nameList.add(com.doumee.core.utils.DateUtil.getDate(data.getCreateDate(),"yyyy-MM-dd"));
			for (int i = 0; i < 13; i++) {
			// title.createCell(i).setCellValue(i==0?sheetName:"");
			configTitleCell(sxssfWorkbook,title.getCell(i));
			}
			Row header1 = sheet.createRow(1);
			header1.createCell(0).setCellValue("投保企业");
			header1.createCell(1).setCellValue("");
			header1.createCell(2).setCellValue("保险方案");
			header1.createCell(3).setCellValue("保单号");
			header1.createCell(4).setCellValue("保险生效起期");
			header1.createCell(5).setCellValue("");
			header1.createCell(6).setCellValue("保险生效止期");
			header1.createCell(7).setCellValue("");
			header1.createCell(8).setCellValue(Constants.getChangeApplyTimeTitle(1,data));
			header1.createCell(9).setCellValue(Constants.getChangeApplyTimeTitle(0,data));
			header1.createCell(10).setCellValue("加保人数");
			header1.createCell(11).setCellValue("减保人数");
			header1.createCell(12).setCellValue("批改金额");
			for (int i = 0; i < 13; i++) {
			configFirstCell(sxssfWorkbook,header1.getCell(i));
			}
			sheet.addMergedRegion(new CellRangeAddress(1 ,1,0,1));
			sheet.addMergedRegion(new CellRangeAddress(1 ,1,4,5));
			sheet.addMergedRegion(new CellRangeAddress(1 ,1,6,7));

			Row header2 = sheet.createRow(2);
			header2.createCell(0).setCellValue(data.getCompanyName());
			header2.createCell(1).setCellValue("");
			header2.createCell(2).setCellValue(data.getSolutionsName());
			header2.createCell(3).setCellValue(data.getApplyCode());
			header2.createCell(4).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getStartTime()));
			header2.createCell(5).setCellValue("");
			header2.createCell(6).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getEndTime()));
			header2.createCell(7).setCellValue("");
			header2.createCell(8).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getValidTime()));
			header2.createCell(9).setCellValue(com.doumee.core.utils.DateUtil.getPlusTime2(data.getDelValidTime()));
			header2.createCell(10).setCellValue(data.getAddNum());
			header2.createCell(11).setCellValue(data.getDelNum());
			header2.createCell(12).setCellValue(data.getFee()+"元");
			sheet.addMergedRegion(new CellRangeAddress(2 ,2,0,1));
			sheet.addMergedRegion(new CellRangeAddress(2 ,2,4,5));
			sheet.addMergedRegion(new CellRangeAddress(2 ,2,6,7));
			for (int i = 0; i < 13; i++) {
			configDataCell(sxssfWorkbook, header2.getCell(i));
			}
			Row header3 = sheet.createRow(3);
			for (int i = 0; i < 13; i++) {
			header3.createCell(i).setCellValue("");
			}
			header3.createCell(0).setCellValue("加保人员");
			header3.createCell(6).setCellValue("减保人员");
			sheet.addMergedRegion(new CellRangeAddress(3 ,3,0,5));
			sheet.addMergedRegion(new CellRangeAddress(3 ,3,6,12));
			for (int i = 0; i < 13; i++) {
			configFirstCell(sxssfWorkbook, header3.getCell(i));
			}
			Row header4 = sheet.createRow(4);
			header4.createCell(0).setCellValue("序号");
			header4.createCell(1).setCellValue("员工姓名");
			// header4.createCell(2).setCellValue("性别");
			header4.createCell(2).setCellValue("身份证号");
			header4.createCell(3).setCellValue("派遣单位");
			header4.createCell(4).setCellValue("所属工种");
			header4.createCell(5).setCellValue("备注");
			header4.createCell(6).setCellValue("序号");
			header4.createCell(7).setCellValue("员工姓名");
			// header4.createCell(8).setCellValue("性别");
			header4.createCell(8).setCellValue("身份证号");
			header4.createCell(9).setCellValue("派遣单位");
			header4.createCell(10).setCellValue("所属工种");
			header4.createCell(11).setCellValue("备注");
			header4.createCell(12).setCellValue("");
			sheet.addMergedRegion(new CellRangeAddress(4 ,4,11,12));
			for (int i = 0; i < 13; i++) {
			configFirstCell(sxssfWorkbook,header4.getCell(i));
			}
			int maxNum = data.getAddNum()>data.getDelNum()?data.getAddNum():data.getDelNum();
			// 创建数据记录
			for (int rowIndex = 0; rowIndex <maxNum; rowIndex++) {
			ApplyChagneDetail addModel = data.getAddDetailList().size()> rowIndex?data.getAddDetailList().get(rowIndex):null;
			ApplyChagneDetail delModel = data.getDelDetailList().size()> rowIndex?data.getDelDetailList().get(rowIndex):null;
			Row header5 = sheet.createRow(rowIndex + 5);
			if(addModel!=null){
			header5.createCell(0).setCellValue(rowIndex+1);
			header5.createCell(1).setCellValue(StringUtils.defaultString(addModel.getMemberName(),""));
			// header5.createCell(2).setCellValue(Constants.equalsInteger(addModel.getSex(),0)?"男":(Constants.equalsInteger(addModel.getSex(),1)?"女":"-"));
			header5.createCell(2).setCellValue(StringUtils.defaultString(addModel.getMemberIdcardNo(),""));
			header5.createCell(3).setCellValue(StringUtils.defaultString(addModel.getDuName(),""));
			header5.createCell(4).setCellValue(StringUtils.defaultString(addModel.getWorkTypeName(),""));
			header5.createCell(5).setCellValue(StringUtils.defaultString(addModel.getRemark(),""));
			}else{
			for (int i = 0; i < 6; i++) {
			header5.createCell(i).setCellValue("");
			}
			}
			if(delModel!=null){
			header5.createCell(6).setCellValue(rowIndex+1);
			header5.createCell(7).setCellValue(StringUtils.defaultString(delModel.getMemberName(),""));
			// header5.createCell(8).setCellValue(Constants.equalsInteger(delModel.getSex(),0)?"男":(Constants.equalsInteger(delModel.getSex(),1)?"女":"-"));
			header5.createCell(8).setCellValue(StringUtils.defaultString(delModel.getMemberIdcardNo(),""));
			header5.createCell(9).setCellValue(StringUtils.defaultString(delModel.getDuName(),""));
			header5.createCell(10).setCellValue(StringUtils.defaultString(delModel.getWorkTypeName(),""));
			header5.createCell(11).setCellValue(StringUtils.defaultString(delModel.getRemark(),""));
			header5.createCell(12).setCellValue("");
			sheet.addMergedRegion(new CellRangeAddress(rowIndex + 5 ,rowIndex + 5,11,12));
			}else{
			for (int i =6; i < 13; i++) {
			header5.createCell(i).setCellValue("");
			}
			}
			for (int i = 0; i < 13; i++) {
			configDataCell(sxssfWorkbook, header5.getCell(i));
			}
			}
			}
			sxssfWorkbook.write(os);
			os.close();
			} catch (Exception e) {
			throw new BusinessException(ResponseStatus.EXPORT_EXCEL_ERROR, e);
			} finally {
			if (os != null) {
			try {
			os.close();
			} catch (IOException e) {
			e.printStackTrace();
			}
			}
			}
			}



			}