packageavicit.mms.mes.mesexceptionbillz.service;importjava.io.Serializable;importjava.text.SimpleDateFormat;importjava.util.List;importjava.util.Map;importorg.apache.poi.hssf.usermodel.HSSFCellStyle;importorg.apache.poi.hssf.util.HSSFColor;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.CellStyle;importorg.apache.poi.ss.usermodel.Font;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.ss.util.CellRangeAddress;importorg.apache.poi.xssf.streaming.SXSSFSheet;importorg.apache.poi.xssf.streaming.SXSSFWorkbook;importorg.slf4j.Logger;importorg.slf4j.LoggerFactory;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.stereotype.Service;importavicit.mms.mes.mesexceptionbillz.dao.MesExceptionBillzDao;importavicit.mms.mes.mesexceptionbillz.dto.MesExceptionBillzDTO;importavicit.platform6.api.sysuser.SysUserAPI;importavicit.platform6.core.exception.DaoException;importavicit.platform6.core.mybatis.pagehelper.Page;importavicit.platform6.core.mybatis.pagehelper.PageHelper;importavicit.platform6.core.rest.msg.QueryReqBean;importavicit.platform6.core.rest.msg.QueryRespBean;importavicit.platform6.core.sfn.intercept.SelfDefinedQuery;
@Servicepublic class MesExceptionBillzService implementsSerializable {private static final Logger LOGGER = LoggerFactory.getLogger(MesExceptionBillzService.class);private static final long serialVersionUID = 1L;
@AutowiredprivateMesExceptionBillzDao mesExceptionBillDao;
@AutowiredprivateSysUserAPI sysUserAPI;/*** 异常问题 导出,带回复
*@paramworkbook
*@paramsheetNum
*@paramsheetTitle
*@paramheaders
*@paramresult
*@paramresultSend
*@throwsException*/
public void exportData(SXSSFWorkbook workbook, int sheetNum, String sheetTitle, String[] headers, List resultSend) throwsException {//生成一个表格
Sheet sheet =workbook.createSheet();/************ ***********第一行标题start****************************/
//产生表格标题行 单元格范围 参数(int firstRow, int lastRow, int firstCol, intlastCol)//这里设置标题单元格的合并范围,0,0,4,6代表第0行的4-6列合并为一列,0,1,0,0代表第0列的第0行到第1行合并为一行
CellRangeAddress region1 = new CellRangeAddress(0, 0, 4, 6);
CellRangeAddress region2= new CellRangeAddress(0, 1, 7, 7);
CellRangeAddress region3= new CellRangeAddress(0, 1, 8, 8);
CellRangeAddress region4= new CellRangeAddress(0, 1, 9, 9);
CellRangeAddress region5= new CellRangeAddress(0, 1, 10, 10);
CellRangeAddress region6= new CellRangeAddress(0, 1, 11, 11);
CellRangeAddress region7= new CellRangeAddress(0, 1, 12, 12);
CellRangeAddress region8= new CellRangeAddress(0, 1, 0, 0);
CellRangeAddress region9= new CellRangeAddress(0, 1, 1, 1);
CellRangeAddress region10= new CellRangeAddress(0, 1, 2, 2);
CellRangeAddress region11= new CellRangeAddress(0, 1, 3, 3);//在sheet里将表头单元格合并
sheet.addMergedRegion(region1);
sheet.addMergedRegion(region2);
sheet.addMergedRegion(region3);
sheet.addMergedRegion(region4);
sheet.addMergedRegion(region5);
sheet.addMergedRegion(region6);
sheet.addMergedRegion(region7);
sheet.addMergedRegion(region8);
sheet.addMergedRegion(region9);
sheet.addMergedRegion(region10);
sheet.addMergedRegion(region11);//设置sheet名字
workbook.setSheetName(sheetNum, "异常问题导出");/***********************第二行标题start****************************/
//表格第二行标题
Row headTitleSecond = sheet.createRow(0);//设置样式
CellStyle secondRowcellStyle =workbook.createCellStyle();
secondRowcellStyle.setFillForegroundColor(HSSFColor.LIME.index);//设置背景色//
secondRowcellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);//上下居中
secondRowcellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
secondRowcellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
secondRowcellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
secondRowcellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
secondRowcellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
secondRowcellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
Font font=workbook.createFont();
font.setFontName("宋体");
font.setFontHeight((short) 220);
font.setBoldweight((short) 700);
secondRowcellStyle.setFont(font);
Cell cel= headTitleSecond.createCell(0);
cel.setCellStyle(secondRowcellStyle);
cel.setCellValue("异常类型");//设置样式
Cell cel1 = headTitleSecond.createCell(1);
cel1.setCellStyle(secondRowcellStyle);
cel1.setCellValue("产品项目");
Cell ce2= headTitleSecond.createCell(2);
ce2.setCellStyle(secondRowcellStyle);
ce2.setCellValue("异常标题");
Cell cel3= headTitleSecond.createCell(3);
cel3.setCellStyle(secondRowcellStyle);
cel3.setCellValue("异常内容");
Cell cel4= headTitleSecond.createCell(4);
cel4.setCellStyle(secondRowcellStyle);
cel4.setCellValue("异常回复");
Cell cel5= headTitleSecond.createCell(7);
cel5.setCellStyle(secondRowcellStyle);
cel5.setCellValue("是否归零");
Cell cel6= headTitleSecond.createCell(8);
cel6.setCellStyle(secondRowcellStyle);
cel6.setCellValue("发布人");
Cell cel7= headTitleSecond.createCell(9);
cel7.setCellStyle(secondRowcellStyle);
cel7.setCellValue("发布时间");
Cell cel8= headTitleSecond.createCell(10);
cel8.setCellStyle(secondRowcellStyle);
cel8.setCellValue("负责人");
Cell cel9= headTitleSecond.createCell(11);
cel9.setCellStyle(secondRowcellStyle);
cel9.setCellValue("设计人员");
Cell cel10= headTitleSecond.createCell(12);
cel10.setCellStyle(secondRowcellStyle);
cel10.setCellValue("是否有附件");//表格第二行标题
Row headTitle = sheet.createRow(1);
Cell cel116= headTitle.createCell(4);
cel116.setCellStyle(secondRowcellStyle);
cel116.setCellValue("回复内容");
Cell cel117= headTitle.createCell(5);
cel117.setCellStyle(secondRowcellStyle);
cel117.setCellValue("回复人");
Cell cel118= headTitle.createCell(6);
cel118.setCellStyle(secondRowcellStyle);
cel118.setCellValue("回复时间");
SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Row row= null;
String curid= "";
String lastid= "";//遍历集合数据,产生数据行
if (resultSend != null) {int index = 2;int begin = 2;int end = 2;for (int i = 0; i < resultSend.size(); i++) {
row=sheet.createRow(index);
MesExceptionBillzDTO dto=resultSend.get(i);
curid=dto.getId();if (i != 0) {
lastid= resultSend.get(i - 1).getId();
}else{
lastid=curid;
}
row.setHeightInPoints(22.0f);
row.createCell(0).setCellValue((dto.getTypeName() != null) ? dto.getTypeName() : "");
row.createCell(1).setCellValue((dto.getTypeNameL() != null) ? dto.getTypeNameL() : "");
row.createCell(2).setCellValue((dto.getExTittle() != null) ? dto.getExTittle() : "");
row.createCell(3).setCellValue((dto.getExDescribeInfo() != null) ? dto.getExDescribeInfo() : "");
row.createCell(4).setCellValue((dto.getExReplyInfo() != null) ? dto.getExReplyInfo() : "");
row.createCell(5).setCellValue((dto.getReplierName() != null) ? dto.getReplierName() : "");
row.createCell(6).setCellValue((dto.getLastUpdateDate() != null) ? sdf.format(dto.getLastUpdateDate()) : "");
row.createCell(7).setCellValue((dto.getStatus() != null) ? (dto.getStatus().equals("N") ? "未归零" : "已归零") : "");
row.createCell(8).setCellValue(sysUserAPI.getSysUserNameById((dto.getCreatedBy() != null) ? dto.getCreatedBy() : ""));
row.createCell(9).setCellValue(sdf.format((dto.getCreationDate() != null) ? dto.getCreationDate() : ""));
row.createCell(10).setCellValue((dto.getDutyManNames() != null) ? dto.getDutyManNames() : "");
row.createCell(11).setCellValue(sysUserAPI.getSysUserNameById((dto.getDesignerNames() != null) ? dto.getDesignerNames() : ""));
row.createCell(12).setCellValue(dto.getCountAttachment()>0?"是":"否");if(lastid.equals(curid)) {if (i != 0) {
end+= 1;
}
}else{for (int a = 0; a < 13; a++) {if (a < 4 || a > 6) {//合并
CellRangeAddress callRange = newCellRangeAddress(begin, end, a, a);
sheet.addMergedRegion(callRange);
}
}
end+= 1;
begin=end;
}if (i % 100 == 0) {
((SXSSFSheet) sheet).flushRows();
}
index++;
}for (int i = 0; i < 13; i++) {if (i < 4 || i > 6) {//合并
CellRangeAddress callRange1 = newCellRangeAddress(begin, end, i, i);
sheet.addMergedRegion(callRange1);
}
}
}
}/*** 异常问题 导出
*@paramworkbook
*@paramsheetNum
*@paramsheetTitle
*@paramheaders
*@paramresult
*@paramresultSend
*@throwsException*/
public void exportDataNoReply(SXSSFWorkbook workbook, int sheetNum, String sheetTitle, String[] headers, List resultSend) throwsException {//生成一个表格
Sheet sheet =workbook.createSheet();//设置sheet名字
workbook.setSheetName(sheetNum, "异常问题导出");/***********************第二行标题start****************************/
//表格第二行标题
Row headTitleSecond = sheet.createRow(0);//设置样式
CellStyle firstRowcellStyle =workbook.createCellStyle();
firstRowcellStyle.setFillForegroundColor(HSSFColor.LIME.index);//设置背景色
firstRowcellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);//上下居中
firstRowcellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
firstRowcellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
firstRowcellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
firstRowcellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
firstRowcellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
firstRowcellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
Font font=workbook.createFont();
font.setFontName("宋体");
font.setFontHeight((short) 220);
font.setBoldweight((short) 700);
firstRowcellStyle.setFont(font);
Cell cel= headTitleSecond.createCell(0);
cel.setCellStyle(firstRowcellStyle);
cel.setCellValue("异常类型");
Cell cel1= headTitleSecond.createCell(1);
cel1.setCellStyle(firstRowcellStyle);
cel1.setCellValue("产品项目");
Cell ce2= headTitleSecond.createCell(2);
ce2.setCellStyle(firstRowcellStyle);
ce2.setCellValue("异常标题");
Cell cel3= headTitleSecond.createCell(3);
cel3.setCellStyle(firstRowcellStyle);
cel3.setCellValue("异常内容");
Cell cel5= headTitleSecond.createCell(4);
cel5.setCellStyle(firstRowcellStyle);
cel5.setCellValue("是否归零");
Cell cel6= headTitleSecond.createCell(5);
cel6.setCellStyle(firstRowcellStyle);
cel6.setCellValue("发布人");
Cell cel7= headTitleSecond.createCell(6);
cel7.setCellStyle(firstRowcellStyle);
cel7.setCellValue("发布时间");
Cell cel8= headTitleSecond.createCell(7);
cel8.setCellStyle(firstRowcellStyle);
cel8.setCellValue("负责人");
Cell cel9= headTitleSecond.createCell(8);
cel9.setCellStyle(firstRowcellStyle);
cel9.setCellValue("设计人员");
Cell cel10= headTitleSecond.createCell(9);
cel10.setCellStyle(firstRowcellStyle);
cel10.setCellValue("是否有附件");
SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Row row= null;//遍历集合数据,产生数据行
if (resultSend != null) {int index = 1;for (int i = 0; i < resultSend.size(); i++) {
row=sheet.createRow(index);
MesExceptionBillzDTO dto=resultSend.get(i);
row.setHeightInPoints(22.0f);
row.createCell(0).setCellValue((dto.getTypeName() != null) ? dto.getTypeName() : "");
row.createCell(1).setCellValue((dto.getTypeNameL() != null) ? dto.getTypeNameL() : "");
row.createCell(2).setCellValue((dto.getExTittle() != null) ? dto.getExTittle() : "");
row.createCell(3).setCellValue((dto.getExDescribeInfo() != null) ? dto.getExDescribeInfo() : "");
row.createCell(4).setCellValue((dto.getStatus() != null) ? (dto.getStatus().equals("N") ? "未归零" : "已归零") : "");
row.createCell(5).setCellValue(sysUserAPI.getSysUserNameById((dto.getCreatedBy() != null) ? dto.getCreatedBy() : ""));
row.createCell(6).setCellValue(sdf.format((dto.getCreationDate() != null) ? dto.getCreationDate() : ""));
row.createCell(7).setCellValue((dto.getDutyManNames() != null) ? dto.getDutyManNames() : "");
row.createCell(8).setCellValue(sysUserAPI.getSysUserNameById((dto.getDesignerNames() != null) ? dto.getDesignerNames() : ""));
row.createCell(9).setCellValue(dto.getCountAttachment()>0?"是":"否");if (i % 100 == 0) {
((SXSSFSheet) sheet).flushRows();
}
index++;
}
}
}
}