public String reportExportfive(){
try {
//新建一个工作页
HSSFWorkbook book = new HSSFWorkbook();
//新建一个表格
HSSFSheet sheet1 = book.createSheet("评价表");
//新建行
HSSFRow row = null;
//创建列
HSSFCell cell = null;
//设置单元格样式
HSSFCellStyle head = book.createCellStyle();
HSSFCellStyle style = book.createCellStyle();
//设置居中
head.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
head.setAlignment(HSSFCellStyle.ALIGN_CENTER);
head.setWrapText(true);//自动换行
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setWrapText(true);//自动换行
//
//设置边框
head.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
head.setBottomBorderColor((short) 56);
head.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
head.setLeftBorderColor((short) 56);
head.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
head.setTopBorderColor((short) 56);
head.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
head.setRightBorderColor((short) 56);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
style.setBottomBorderColor((short) 56);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style.setLeftBorderColor((short) 56);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style.setTopBorderColor((short) 56);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style.setRightBorderColor((short) 56);
//设置字体
HSSFFont font = book.createFont();
font.setFontName("黑体");
font.setFontHeightInPoints((short) 10);//设置字体大小
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
//选择需要用到的字体格式
head.setFont(font);
style.setFont(font);
/***数据区start*/
UserSession userSession = AuthorityUtil.getSysUserSession();
String comCode=userSession.getComCode();
String hql1;
hql1="FROM SysCompany where comcode='"+comCode+"'";
List<SysCompany> com =sysCompanyService.findSysCompanyList(hql1);
String comname=com.get(0).getComCname();
String comtype=com.get(0).getAgentTypeCode();
//标题
//String[] pingfen=new String[4];
/*if("302".equals(comtype)){
String[] pingfen = {"年财产险公司评价表","","",""};
}else{
}*/
String[] pingfen = {"年人身险公司评价表","","",""};
int pingjiaCount = pingfen.length;
HSSFRow prow = sheet1.createRow(0);
for (short i = 0; i < pingjiaCount; i++){
HSSFCell cell1 = prow.createCell(i);
cell1.setCellStyle(head);
HSSFRichTextString pingtext = new HSSFRichTextString(pingfen[i]);
cell1.setCellValue(pingtext);
}
String[] pingjia3 = {"公司机构代码","公司名称","类别","得分"};
int pingCount3 = pingjia3.length;
HSSFRow prow3 = sheet1.createRow(1);
for (short i = 0; i < pingCount3; i++){
HSSFCell cell1 = prow3.createCell(i);
cell1.setCellStyle(head);
HSSFRichTextString pingtext = new HSSFRichTextString(pingjia3[i]);
cell1.setCellValue(pingtext);
}
//查询版次号
String hqlbcNum="FROM UplTaskInfo where TASKNO='"+taskNo+"' and VALIDSTATUS='01' ";
List<UplTaskInfo> yearNumList=queryUplTaskService.findInfo(hqlbcNum.toString());
String verNo =yearNumList.get(0).getReverse1();
//查询评价指标
String hql3=" select distinct a.comcode,b.COMCNAME,a.comType,a.score "
+ "from (SELECT comcode,comType ,score FROM Mer_Evaltable where comcode='"+comCode+"' and VERSIONORDER='"+verNo+"') a "
+ "left join (select comcode,COMCNAME from syscompany) b on a.COMCODE=b.COMCODE ";
List<MerEvaltable> evaltableList=merEvalTableService.findBySql(hql3);
List<MerEvaltable> evaltableList2 =this.dataDetailQueries(evaltableList);
int pingjiacount=evaltableList2.size();
MerEvaltable evaltable=null;
HSSFRichTextString text =null;
for (int i = 0; i < pingjiacount; i++) {
row = sheet1.createRow(i+2);
evaltable = evaltableList2.get(i);
cell=row.createCell(0);
text=new HSSFRichTextString(evaltable.getComcode());
cell.setCellStyle(style);
cell.setCellValue(text);//序号
cell=row.createCell(1);
text=new HSSFRichTextString(evaltable.getReverse1());
cell.setCellStyle(style);
cell.setCellValue(text);//指标名称
cell=row.createCell(2);
if("01".equals(evaltable.getComType())){
text=new HSSFRichTextString("财产险公司");
}else{
text=new HSSFRichTextString("人身险公司");
}
cell.setCellStyle(style);
cell.setCellValue(text);//单位
cell=row.createCell(3);
text=new HSSFRichTextString(evaltable.getScore());
cell.setCellStyle(style);
cell.setCellValue(text);//指标值
}
/**end*/
/**合并单元格*/
//起始行 //结束行 //起始列 //结束列
CellRangeAddress region = new CellRangeAddress(0,0,0,3);
sheet1.addMergedRegion(region);
/*CellRangeAddress region1 = new CellRangeAddress(1,1,1,3);
sheet1.addMergedRegion(region1);*/
/*CellRangeAddress region2 = new CellRangeAddress(2,2,0,2);
sheet1.addMergedRegion(region2);*/
/**end*/
//excel表格样式
for(int w = 0; w < pingCount3; w++){
if(w == 0 || w == (pingCount3-1)){
sheet1.setColumnWidth(w, 30 * 225);
}else{
sheet1.setColumnWidth(w, 25 * 225);
}
}
Date now = new Date();
SimpleDateFormat fmt = new SimpleDateFormat("yyyyMMdd");
String d =fmt.format(now);
//生成excel文件
if("302".equals(comtype)){
ExcelUtil.writeExcel(book, "财产险公司评价表"+d+".xls");
}else{
ExcelUtil.writeExcel(book, "人身险公司评价表"+d+".xls");
}
//打印日志
String info = LoggerUtil.getInfoMsg("将公司经营评价查看评分表生成excel文件并提供下载功能!");
log.info(info);
return null;
} catch (Exception e) {
//组织异常信息
this.exceptionMessage.setError(e.toString());
this.exceptionMessage.setClassName(this.getClass().getName());
this.exceptionMessage.setMessage("将用户信息结果生成excel文件并提供下载功能出现异常!");
log.error(e.toString());
return ERROR;
}
}
生成excel文件
最新推荐文章于 2024-09-03 19:16:19 发布