基于API导出带有页眉页脚的excel
Posted in 集智
?
集智数据平台报表中设置的页眉页脚在页面中是看不到的,如下图:
?
?
?
页面中的效果:
?
?
在打印的时候,可以看到页眉页脚的效果:
?
?
那么,如果将页眉页脚导入到导出的excel中呢.我们可以通过API来进行设置:
?
<%@ page import=”com.runqian.report4.model.*”%>
<%@ page import=”com.runqian.report4.usermodel.*”%>
<%@ page import=”com.runqian.report4.view.excel.*” %>
<%@ page import=”com.runqian.report4.util.*” %>
<jsp:directive.page import=”org.apache.poi2.hssf.usermodel.HSSFWorkbook”/>
<jsp:directive.page import=”org.apache.poi2.hssf.usermodel.HSSFSheet”/>
<jsp:directive.page import=”org.apache.poi2.hssf.usermodel.HSSFHeader”/>
<jsp:directive.page import=”org.apache.poi2.hssf.usermodel.HSSFFooter”/>
?
<%
?//设置所有report对象的值为GBK转码
?request.setCharacterEncoding(”GBK”);
?
?String reportFile = request.getParameter(”report”);
?if( reportFile==null ){
??reportFile = “report_4.raq”;
?}
?
?//第一步，读取报表模板
?InputStream fis=application.getResourceAsStream(”/reportFiles/”+reportFile);
?ReportDefine rd = (ReportDefine)ReportUtils.read( fis );
?
?//第二步，设置报表授权文件，运算报表
?Context context = new Context();
?//计算前，设置参数与宏
?ParamMetaData pmd = rd.getParamMetaData();
?String paramOrMocrName = “”;
?if(pmd != null){
??for(int i = 0;i <pmd.getParamCount(); i ++){
???paramOrMocrName = pmd.getParam(i).getParamName();
???context.setParamValue(paramOrMocrName ,request.getParameter(paramOrMocrName));
??}
?}
?MacroMetaData mmd = rd.getMacroMetaData();
?if( mmd != null ){
??for(int i = 0; i < mmd.getMacroCount(); i ++){
???paramOrMocrName = mmd.getMacro(i).getMacroName();
???context.setMacroValue(paramOrMocrName,request.getParameter(paramOrMocrName));
??}
?}
?
?Engine enging = new Engine( rd, context);
?IReport iReport = enging.calc();
?
?//生成Excel文件,因为jsp里不能直接用response的out.print()方法，所以我们先在服务器上生成这个文件
?? String saveFile=request.getRealPath(”/reportFiles/”)+”/”+reportFile+”.xls”;
??
?? ExcelReport eReoprt = new ExcelReport();
?? eReoprt.export(iReport);
?
?? FileOutputStream fos = new FileOutputStream( saveFile );
??? eReoprt.saveTo(fos);
?? fos.close();
??
?? HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(saveFile));
?? wb.setSheetName(0,”表1″);
?? HSSFSheet? s=wb.getSheetAt(0);
?? HSSFHeader hss = s.getHeader();
?? //hss.setCenter(”aaaa”);
?? StringBuffer stringBuffer = new StringBuffer();
? ?StringBuffer sb = new StringBuffer();
?? int rowNum = iReport.getRowCount();
?? int colNum = iReport.getColCount();
?? sb.append(”行数：“+rowNum+”;列数：“+colNum);
??
?? //页眉页脚分3部分（左中右），
?? int centerStartCol = colNum/3+1;??????//中间部分开始列
?? int rightStartCol = colNum/3*2+colNum%3+1;???//右边部分开始列
?? sb.append(”;中间开始部分：“+centerStartCol+”;右边开始部分：“+rightStartCol);
? ?//页眉左边部分
? ?
??? for(int i=1,j=rowNum;i<=j;i++){
??? ??IRowCell iRowCell=iReport.getRowCell(i);
? ??if( iRowCell.getRowType()==iRowCell.TYPE_PAGE_HEADER ){
? ???
?? ???if(iRowCell.getRowVisible()!=false ){
?? ????for(int m=1,n=centerStartCol;m<=n;m++){
?? ?????
?? ?????INormalCell cell = iReport.getCell(i,(short)m);
?? ?????sb.append(”;左边部分：“+cell.isMerged());
?? ?????
?? ?????Object obj=cell.getValue();
?? ?????if(obj!=null){
?? ??????stringBuffer.append(obj.toString());
????????HSSFHeader hh=s.getHeader();
????????hh.setLeft(stringBuffer.toString());
???????}
?? ????}
?? ????stringBuffer.append(”\r”);
?? ???}
? ???}
? ??}?
??
??stringBuffer.delete(0,stringBuffer.length());
??//页眉中间部分
??System.out.println(sb.toString());
?
??? for(int i=1,j=rowNum;i<=j;i++){
??? ??IRowCell iRowCell=iReport.getRowCell(i);
? ??if( iRowCell.getRowType()==iRowCell.TYPE_PAGE_HEADER ){
? ???if(iRowCell.getRowVisible()!=false ){
? ????for(int m=rightStartCol,n=rightStartCol;m<=n;m++){
? ?????
? ?????INormalCell cell = iReport.getCell(i,(short)m);
? ?????sb.append(”;中间部分：“+cell.isMerged());
? ?????
? ?????Object obj=cell.getValue();
? ?????if(obj!=null){
? ??????stringBuffer.append(obj.toString());
???????HSSFHeader hh=s.getHeader();
???????hh.setCenter(stringBuffer.toString());
??????}
? ????}
? ????stringBuffer.append(”\r”);
? ???}
? ??}?
? ?}?
?
??stringBuffer.delete(0,stringBuffer.length());
??System.out.println(sb.toString());
??//页眉右边部分
??
??? for(int i=1,j=rowNum;i<=j;i++){
??? ??IRowCell iRowCell=iReport.getRowCell(i);
? ??if( iRowCell.getRowType()==iRowCell.TYPE_PAGE_HEADER ){
? ???if(iRowCell.getRowVisible()!=false ){
? ????for(int m=centerStartCol,n=colNum;m<=n;m++){
? ?????INormalCell cell = iReport.getCell(i,(short)m);
? ?????sb.append(”;右边部分：“+cell.isMerged());
? ?????
? ?????Object obj=cell.getValue();
? ?????if(obj!=null){
? ??????stringBuffer.append(obj.toString());
???????HSSFHeader hh=s.getHeader();
???????hh.setCenter(stringBuffer.toString());
??????}
? ????}
? ????stringBuffer.append(”\r”);
? ???}
? ??}?
? ?}? ??
?? ?for(int i=1,j=iReport.getColCount();i<=j;i++){
?? ??IColCell irc=iReport.getColCell((short)i);
?? ??
?? ??if(irc.getBreakPage()){
?? ??s.setColumnBreak((short)(i-1));
?? ??}
?? ?}
?? //结束分页
?? //页眉开始
??
?? HSSFHeader hh=s.getHeader();
?? System.out.println(”日期&D:”+HSSFHeader.date());
?? System.out.println(”当前页&P:”+HSSFHeader.page());
?? System.out.println(”页数&N:”+HSSFHeader.numPages());
?? System.out.println(”时间&T:”+HSSFHeader.time());
?? System.out.println(”日期&D:”+HSSFHeader.date());
?
?? //页眉结束
?? //设置页脚
?? HSSFFooter footer = s.getFooter();
?? footer.setCenter( “第 ” + HSSFFooter.page() + “??? 共 ” + HSSFFooter.numPages());
?? //页脚结束
?? //标题行
??
?? ?System.out.println(”=========”+stringBuffer);
?? ?System.out.println(”第一行第五列：“+iReport.getCell(1,(short)5).getValue().toString());
?//转跳到我们刚才生成的文件
?wb.write(new FileOutputStream(saveFile));
?System.out.println(”Excel文件生成结束,文件保存在:”+saveFile);
?response.sendRedirect(request.getContextPath()+”/reportFiles/”+reportFile+”.xls”);
?for(int i=1;i<=iReport.getColCount();i++){
??System.out.println(iReport.getCell(1,(short)i).getValue());
?}
%>
?
?
?
更多了解各种复杂问题的处理，可以参考：报表导出打印，展现导出打印各种复杂报表。
?
