//下载原始报表
public void download(){
try {
//创建临时文件夹
String name="D:/经营评价汇总表";
File file=new File(name);
if(!file.exists()){//如果文件夹不存在
file.mkdir();//创建文件夹
}
//调用生成报表方法
//基础指标汇总
this.reportone();
//基础指标月度汇总
this.reporttwo();
//评价指标(含中位数及均值)
this.reportthree("3");
//评分计算表
this.downloadexl55();
//评价表
this.reportfive();
//复核报表
//this.reportExport();
//打包 因浏览器不支持多文件下载,所以将生成好的文件进行打包
String pathName ="经营评价汇总表.zip";
String fileName="D:/经营评价汇总表";
AppUtil.compress(fileName, pathName);
AppUtil.delFolder(fileName);
//web服务器的HTTP请求
HttpServletResponse response = ServletActionContext.getResponse();
File file2 = new File(pathName);
String filename = file2.getName();
InputStream fis;
fis = new BufferedInputStream(new FileInputStream(pathName));
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
// 清空response
response.reset();
// 设置response的Header
response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes("gbk"),"iso-8859-1"));
response.addHeader("Content-Length", "" + file2.length());
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/octet-stream");
toClient.write(buffer);
toClient.flush();
toClient.close();
AppUtil.delFile(pathName);
//打印日志
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;
}
}
//下载表5
public String reportfive(){
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 = mmerList.get(0).getComType().equals("01")?"财产险":"人身险";
//标题
//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 hql3=" select distinct a.comcode,b.COMCNAME,a.comType,a.score "
+ "from (SELECT comcode,comType ,score,versionOrder,versionNo FROM Mer_Evaltable) a "
+ "left join (select comcode,COMCNAME from syscompany) b on a.COMCODE=b.COMCODE "
+ " and a.versionNo='"+versionNo+"' and a.versionOrder='"+versionOrder+"' ";
List<MerEvaltable> evaltableList=merEvalTableService.findBySql(hql3);
List<MerEvaltable> evaltableList2 =this.dataDetailQueries5(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);
//临时文件夹路径
String fileName="";
/* if("01".equals(comtype)){
//ExcelUtil.writeExcel(book, "财产险公司评价表"+d+".xls");
fileName="D:/经营评价汇总表/财产险公司经营评价表_"+d+".xls";
}else{
//ExcelUtil.writeExcel(book, "人身险公司评价表"+d+".xls");
fileName="D:/经营评价汇总表/人身险公司经营评价表_"+d+".xls";
}*/
fileName="D:/经营评价汇总表/"+comType+"经营评价表_"+d+".xls";
//将HSSFWorkbook生成的excel文件存入到临时文件夹下
File file=new File(fileName);
FileOutputStream fOut = null;
fOut = new FileOutputStream(file);
book.write(fOut);
fOut.flush();
fOut.close();
//打印日志
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;
}
}
public String downloadexl55(){
try{
StringBuffer hql = new StringBuffer(200);
hql.append("FROM MerScorecaltable WHERE 1 = 1 ");
hql.append("and versionNo ='"+versionNo+"'");
hql.append("and versionOrder ='"+versionOrder+"'" );
hql.append("order by comcode asc" );
List<MerScorecaltable> merScorecaltableList = selectReviewService.findByHql4(hql.toString());
String comType = merScorecaltableList.get(0).getComType().equals("01")?"财产险":"人身险";
String dataYear = merScorecaltableList.get(0).getDataYear();
this.comcodeList = selectReviewService.comcodeQuery2(versionNo,versionOrder);
//新建一个工作页
HSSFWorkbook book = new HSSFWorkbook();
//新建一个表格
HSSFSheet sheet5 = book.createSheet(dataYear+"年"+comType+"公司经营评价核算评分计算表");
//新建行
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_MEDIUM);//下边框
head.setBottomBorderColor((short) 56);
head.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);//左边框
head.setLeftBorderColor((short) 56);
head.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);//上边框
head.setTopBorderColor((short) 56);
head.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);//右边框
head.setRightBorderColor((short) 56);
style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);//下边框
style.setBottomBorderColor((short) 56);
style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);//左边框
style.setLeftBorderColor((short) 56);
style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);//上边框
style.setTopBorderColor((short) 56);
style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);//右边框
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);
//设置背景色
head.setFillForegroundColor((short) 22);
head.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//合并单元格CellRangeAddress构造参数依次表示起始行,截至行,起始列, 截至列
sheet5.addMergedRegion(new CellRangeAddress( 0 , 0 , 0 , 14 ));
//在sheet里创建第一行,参数为行索引(excel的行),可以是0~65535之间的任何一个
HSSFRow row1=sheet5.createRow( 0 );
//创建单元格(excel的单元格,参数为列索引,可以是0~255之间的任何一个
HSSFCell cell1=row1.createCell( 0 );
//设置单元格内容
String a = dataYear+"年"+comType+"公司经营评价核算评分计算表";
cell1.setCellStyle(head);
cell1.setCellValue(a);
String[] exUser2 = {"公司机构代码","公司名称","总分","保费增长率","自留保费增长率","总资产增长率","综合成本率","综合赔付率","综合投资收益率","净资产收益率","百元保费经营活动净现金流","风险保障贡献度","赔付贡献度","纳税增长率","增加值增长率"};
int colCount2 = exUser2.length;
//第二行
HSSFRow row2 = sheet5.createRow(1);
int two = 0;
for (short j = 0; j< colCount2; j++){
HSSFCell cell2 = row2.createCell(two);
cell2.setCellStyle(head);
HSSFRichTextString text = new HSSFRichTextString(exUser2[j]);
cell2.setCellValue(text);
two++;
}
//excel的总内容行数
int rowCount = merScorecaltableList.size();
MerScorecaltable su = null;
HSSFRichTextString text =null;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//内容
for (int i = 0; i < rowCount; i++) {
row = sheet5.createRow(i+2);
su = merScorecaltableList.get(i);
String comcname = comcodeList.get(i).getComcName();
// cell=row.createCell(0);
// text=new HSSFRichTextString(i + 1 + "");
// cell.setCellStyle(style);
// cell.setCellValue(text);//序号
cell=row.createCell(0);
text=new HSSFRichTextString(su.getComcode());
cell.setCellStyle(style);
cell.setCellValue(text);//公司机构代码
cell=row.createCell(1);
text=new HSSFRichTextString(comcname);
cell.setCellStyle(style);
cell.setCellValue(text);//公司名称
cell=row.createCell(2);
text=new HSSFRichTextString(su.getTotalscore());
cell.setCellStyle(style);
cell.setCellValue(text);//总分
cell=row.createCell(3);
text=new HSSFRichTextString(su.getGrowthratecare());
cell.setCellStyle(style);
cell.setCellValue(text);//保费增长率
cell=row.createCell(4);
text=new HSSFRichTextString(su.getGrowthrateip());
cell.setCellStyle(style);
cell.setCellValue(text);//自留保费增长率
cell=row.createCell(5);
text=new HSSFRichTextString(su.getGrowthrateta());
cell.setCellStyle(style);
cell.setCellValue(text);//总资产增长率
cell=row.createCell(6);
text=new HSSFRichTextString(su.getComprehensivecostrate());
cell.setCellStyle(style);
cell.setCellValue(text);//综合成本率
cell=row.createCell(7);
text=new HSSFRichTextString(su.getCombinedratio());
cell.setCellStyle(style);
cell.setCellValue(text);//综合赔付率
cell=row.createCell(8);
text=new HSSFRichTextString(su.getCfroi());
cell.setCellStyle(style);
cell.setCellValue(text);//综合投资收益率
cell=row.createCell(9);
text=new HSSFRichTextString(su.getRoe());
cell.setCellStyle(style);
cell.setCellValue(text);//净资产收益率
cell=row.createCell(10);
text=new HSSFRichTextString(su.getNetoperatingcashflow());
cell.setCellStyle(style);
cell.setCellValue(text);//百元保费经营活动净现金流
cell=row.createCell(11);
text=new HSSFRichTextString(su.getRiskguaranteecontribution());
cell.setCellStyle(style);
cell.setCellValue(text);//风险保障贡献度
cell=row.createCell(12);
text=new HSSFRichTextString(su.getCompensationcontribution());
cell.setCellStyle(style);
cell.setCellValue(text);//赔付贡献度
cell=row.createCell(13);
text=new HSSFRichTextString(su.getTaxgrowthrate());
cell.setCellStyle(style);
cell.setCellValue(text);//纳税增长率
cell=row.createCell(14);
text=new HSSFRichTextString(su.getGrowthrateaddedvalue());
cell.setCellStyle(style);
cell.setCellValue(text);//增加值增长率
}
//excel表格样式
for(int w = 0; w < colCount2; w++){
if(w == 0){
sheet5.setColumnWidth(w, 16 * 225);
}else{
sheet5.setColumnWidth(w, 25 * 225);
}
}
//sheet.setDefaultRowHeightInPoints( 15 ); //设置缺省列高sheet.setDefaultColumnWidth(20);//设置缺省列宽
Date now = new Date();
SimpleDateFormat fmt = new SimpleDateFormat("yyyyMMdd");
String d =fmt.format(now);
//临时文件夹路径
String fileName="";
/* if("302".equals(comtype)){
//ExcelUtil.writeExcel(book, "财产险公司评价表"+d+".xls");
fileName="D:/经营评价汇总表/"+comType+"经营评价指标体系基础指标汇总表_"+d+".xls";
}else{
//ExcelUtil.writeExcel(book, "人身险公司评价表"+d+".xls");
fileName="D:/经营评价汇总表/"+comType+"经营评价指标体系基础指标汇总表_"+d+".xls";
}*/
fileName="D:/经营评价汇总表/"+dataYear+"年"+comType+"公司经营评价核算评分计算表_"+d+".xls";
//将HSSFWorkbook生成的excel文件存入到临时文件夹下
File file=new File(fileName);
FileOutputStream fOut = null;
fOut = new FileOutputStream(file);
book.write(fOut);
fOut.flush();
fOut.close();
//打印日志
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;
}
}
private List<MerEvaltable> dataDetailQueries5(List list){
List<MerEvaltable> result = new ArrayList<MerEvaltable>();
if(list!=null && list.size()>0) {
for(Object obj : list) {
Object[] ot =(Object[]) obj;
MerEvaltable ddv = new MerEvaltable();
ddv.setComcode(ot[0]!=null?ot[0].toString():"");
ddv.setReverse1(ot[1]!=null?ot[1].toString():"");
ddv.setComType(ot[2]!=null?ot[2].toString():"");
ddv.setScore(ot[3]!=null?ot[3].toString():"");
result.add(ddv);
}
}
return result;
}
//原始报表1
public String reportone(){
try{
this.mmerList = selectReviewService.mmerQuery(versionNo,versionOrder);
String comType2 = mmerList.get(0).getComType();
this.comcodeList = selectReviewService.comcodeQuery(versionNo,versionOrder);
this.merList = selectReviewService.merQuery(versionNo,versionOrder,comType2,comcodeList);
String dataYear = mmerList.get(0).getDataYear();
String comType = mmerList.get(0).getComType().equals("01")?"财产险":"人身险";
//新建一个工作页
HSSFWorkbook book = new HSSFWorkbook();
//新建一个表格
HSSFSheet sheet = book.createSheet(dataYear+"年"+comType+"经营评价指标体系基础指标汇总表");
//新建行
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_MEDIUM);//下边框
head.setBottomBorderColor((short) 56);
head.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);//左边框
head.setLeftBorderColor((short) 56);
head.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);//上边框
head.setTopBorderColor((short) 56);
head.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);//右边框
head.setRightBorderColor((short) 56);
style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);//下边框
style.setBottomBorderColor((short) 56);
style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);//左边框
style.setLeftBorderColor((short) 56);
style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);//上边框
style.setTopBorderColor((short) 56);
style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);//右边框
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);
//设置背景色
head.setFillForegroundColor((short) 22);
head.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// UserSession userSession = AuthorityUtil.getSysUserSession();
// String comCode=userSession.getComCode();
// String hql1;
// hql1="FROM SysCompany where comcode='"+comCode+"'";
// List<SysCompany> com =selectReviewService.findSysCompanyList(hql1);
// String comname=com.get(0).getComCname();
// String comtype=com.get(0).getAgentTypeCode();
//合并单元格CellRangeAddress构造参数依次表示起始行,截至行,起始列, 截至列
MerQuery merQuery = merList.get(0);
String cc = merQuery.getCc();
String[] strArray = cc.split(",");
sheet.addMergedRegion(new CellRangeAddress( 0 , 0 , 0 , 4+strArray.length ));
//在sheet里创建第一行,参数为行索引(excel的行),可以是0~65535之间的任何一个
HSSFRow row1=sheet.createRow( 0 );
//创建单元格(excel的单元格,参数为列索引,可以是0~255之间的任何一个
HSSFCell cell1=row1.createCell( 0 );
//设置单元格内容
String a = dataYear+"年"+comType+"经营评价指标体系基础指标汇总表";
cell1.setCellStyle(head);
cell1.setCellValue(a);
String[] exUser2 = new String[4+strArray.length+1];
exUser2[0] = "指标代码";
exUser2[1] = "指标名称";
exUser2[2] = "汇总值1";
exUser2[3] = "汇总值2";
exUser2[4] = "汇总值3";
for (int i = 0; i < comcodeList.size(); i++) {
exUser2[5+i+i] = comcodeList.get(i).getComcode();
exUser2[6+i+i] = comcodeList.get(i).getComcode();
}
int colCount2 = exUser2.length;
//第二行
HSSFRow row2 = sheet.createRow(1);
int two = 0;
for (short j = 0; j< colCount2; j++){
HSSFCell cell2 = row2.createCell(two);
cell2.setCellStyle(head);
HSSFRichTextString text = new HSSFRichTextString(exUser2[j]);
cell2.setCellValue(text);
two++;
}
String[] exUser3 = new String[4+strArray.length+1];
exUser3[0] = "";
exUser3[1] = "";
exUser3[2] = "所有公司";
exUser3[3] = "所有公司";
exUser3[4] = "本年公司";
for (int i = 0; i < comcodeList.size(); i++) {
exUser3[5+i+i] = comcodeList.get(i).getComcName();
exUser3[6+i+i] = comcodeList.get(i).getComcName();
}
int colCount3 = exUser3.length;
//第三行
HSSFRow row3 = sheet.createRow(2);
int thr=0;
for (short s = 0; s < colCount3; s++){
HSSFCell cell3 = row3.createCell(thr);
cell3.setCellStyle(head);
HSSFRichTextString text = new HSSFRichTextString(exUser3[s]);
cell3.setCellValue(text);
thr++;
}
String[] exUser4 = new String[4+strArray.length+1];
exUser4[0] = "";
exUser4[1] = "";
exUser4[2] = "上年实际";
exUser4[3] = "本年实际";
exUser4[4] = "与上年同口径";
for (int i = 0; i < comcodeList.size(); i++) {
exUser4[5+i+i] = "本年度";
exUser4[6+i+i] = "上一年度";
}
int colCount4 = exUser4.length;
//第四行
HSSFRow row4 = sheet.createRow(3);
int fo=0;
for (short z = 0; z < colCount4; z++){
HSSFCell cell4 = row4.createCell(fo);
cell4.setCellStyle(head);
HSSFRichTextString text = new HSSFRichTextString(exUser4[z]);
cell4.setCellValue(text);
fo++;
}
//excel的总内容行数
int rowCount = merList.size();
MerQuery su = null;
HSSFRichTextString text =null;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//内容
for (int i = 0; i < rowCount; i++) {
row = sheet.createRow(i+4);
su = merList.get(i);
// cell=row.createCell(0);
// text=new HSSFRichTextString(i + 1 + "");
// cell.setCellStyle(style);
// cell.setCellValue(text);//序号
cell=row.createCell(0);
text=new HSSFRichTextString(su.getTargetcode());
cell.setCellStyle(style);
cell.setCellValue(text);//指标代码
cell=row.createCell(1);
text=new HSSFRichTextString(su.getTergetname());
cell.setCellStyle(style);
cell.setCellValue(text);//指标名称
cell=row.createCell(2);
text=new HSSFRichTextString(su.getAggrevaluelast());
cell.setCellStyle(style);
cell.setCellValue(text);
cell=row.createCell(3);
text=new HSSFRichTextString(su.getAggrevaluethis());
cell.setCellStyle(style);
cell.setCellValue(text);
cell=row.createCell(4);
text=new HSSFRichTextString(su.getAggrevalueSamecaliber());
cell.setCellStyle(style);
cell.setCellValue(text);
MerQuery merQuery1 = merList.get(i);
String cc1 = merQuery1.getCc();
String[] strArray1 = cc1.split(",");
for (int j = 5; j < strArray1.length+5; j++) {
cell=row.createCell(j);
text=new HSSFRichTextString(strArray1[j-5]);
cell.setCellStyle(style);
cell.setCellValue(text);
}
}
//excel表格样式
for(int w = 0; w < colCount4; w++){
if(w == 0){
sheet.setColumnWidth(w, 16 * 225);
}else{
sheet.setColumnWidth(w, 25 * 225);
}
}
//sheet.setDefaultRowHeightInPoints( 15 ); //设置缺省列高sheet.setDefaultColumnWidth(20);//设置缺省列宽
Date now = new Date();
SimpleDateFormat fmt = new SimpleDateFormat("yyyyMMdd");
String d =fmt.format(now);
//临时文件夹路径
String fileName="";
/* if("302".equals(comtype)){
//ExcelUtil.writeExcel(book, "财产险公司评价表"+d+".xls");
fileName="D:/经营评价汇总表/"+comType+"经营评价指标体系基础指标汇总表_"+d+".xls";
}else{
//ExcelUtil.writeExcel(book, "人身险公司评价表"+d+".xls");
fileName="D:/经营评价汇总表/"+comType+"经营评价指标体系基础指标汇总表_"+d+".xls";
}*/
fileName="D:/经营评价汇总表/"+comType+"经营评价指标体系基础指标汇总表_"+d+".xls";
//将HSSFWorkbook生成的excel文件存入到临时文件夹下
File file=new File(fileName);
FileOutputStream fOut = null;
fOut = new FileOutputStream(file);
book.write(fOut);
fOut.flush();
fOut.close();
//打印日志
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-07-25 19:54:12 发布