spring集成poi完成电子表格报表完全版_spring 做报表-优快云博客

本文链接：https://blog.youkuaiyun.com/xiaojie0710/article/details/1809561

由于本人最近完成项目的需要,作基于web方式的报表功能,要是对要求不是很严格的报表来说,spring+poi就可以实现件呵呵:
首先是我的配置文件: 在 web.xml中进行

< servlet >

< servlet-name > ftldpt </ servlet-name >

< servlet-class >

org.springframework.web.servlet.DispatcherServlet

</ servlet-class >

< load-on-startup > 2 </ load-on-startup >

</ servlet >

< servlet >

< servlet-name > xlsdpt </ servlet-name >

< servlet-class >

org.springframework.web.servlet.DispatcherServlet

</ servlet-class >

< load-on-startup > 3 </ load-on-startup >

</ servlet >

< filter-mapping >

< filter-name > encodingFilter </ filter-name >

< url-pattern > *.ftl </ url-pattern >

</ filter-mapping >

< filter-mapping >

< filter-name > encodingFilter </ filter-name >

< url-pattern > *.slt </ url-pattern >

</ filter-mapping >

    
    
    <servlet-mapping>
        <servlet-name>ftldpt</servlet-name>
        <url-pattern>*.ftl</url-pattern>
    </servlet-mapping>
    <servlet-mapping>
        <servlet-name>xlsdpt</servlet-name>
        <url-pattern>*.xls</url-pattern>
    </servlet-mapping>

下面是关于 xls 的配置文件:

<? xml version="1.0" encoding="GB2312" ?>

< beans xmlns ="http://www.springframework.org/schema/beans"

xmlns:xsi ="http://www.w3.org/2001/XMLSchema-instance"

xmlns:util ="http://www.springframework.org/schema/util"

xmlns:aop ="http://www.springframework.org/schema/aop"

xsi:schemaLocation ="

http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd

http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-2.0.xsd

http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.0.xsd" >

< bean id ="xmlViewResolver"

class ="org.springframework.web.servlet.view.XmlViewResolver" >

< property name ="location" >

< value > /WEB-INF/report-views.xml </ value >

</ property >

</ bean >

< bean id ="simBscXlsModelWrapperImpl"

class ="com.ecpay.pojo.xlsmodel.impl.SimBscXlsModelWrapperImpl" >

</ bean >

< bean id ="auditLogXlsModelWrapperImpl"

class ="com.ecpay.pojo.xlsmodel.impl.AuditLogXlsModelWrapperImpl" >

</ bean >

< bean id ="merchantBscXlsModelWrapperImpl"

class ="com.ecpay.pojo.xlsmodel.impl.MerchantBscXlsModelWrapperImpl" >

</ bean >

< bean id ="simBscStatisticXlsViewControllerHandle"

class ="com.ecpay.mvc.xlsview.handle.SimBscStatisticXlsViewControllerHandle" >

< property name ="xlsModelWrapper"

ref ="simBscXlsModelWrapperImpl" >

</ property >

< property name ="simltjmhtViewDAO" ref ="simltjmhtViewDAOImpl" ></ property >

</ bean >

< bean id ="auditLogXlsViewControllerHandle"

class ="com.ecpay.mvc.xlsview.handle.AuditLogXlsViewControllerHandle" >

< property name ="xlsModelWrapper"

ref ="auditLogXlsModelWrapperImpl" >

</ property >

< property name ="adtLfjMhtViewDAO" ref ="adtLfjMhtViewDAOImpl" ></ property >

</ bean >

< bean id ="merchantXlsViewControllerHandle"

class ="com.ecpay.mvc.xlsview.handle.MerchantXlsViewControllerHandle" >

< property name ="xlsModelWrapper"

ref ="merchantBscXlsModelWrapperImpl" >

</ property >

< property name ="merhcantBscDAO" ref ="merchantBscDAOImpl" ></ property >

</ bean >

< bean id ="simBscStatisticXlsViewController"

class ="com.ecpay.mvc.xlsview.SimBscStatisticXlsViewController" >

< property name ="handle"

ref ="simBscStatisticXlsViewControllerHandle" >

</ property >

< property name ="clazzName"

value ="com.ecpay.pojo.dataview.SimLtjMht" >

</ property >

< property name ="viewPath" value ="reportXlsViewResolver" ></ property >

</ bean >

< bean id ="merchantXlsViewController"

class ="com.ecpay.mvc.xlsview.MerchantXlsViewController" >

< property name ="clazzName"

value ="com.ecpay.pojo.database.MerchantBsc" >

</ property >

< property name ="viewPath" value ="reportXlsViewResolver" ></ property >

< property name ="handle" ref ="merchantXlsViewControllerHandle" ></ property >

</ bean >

< bean id ="urlMapping"

class ="org.springframework.web.servlet.handler.SimpleUrlHandlerMapping" >

< property name ="interceptors" >

< list >

< ref bean ="accountRoleHeadOffInterceptor" />

</ list >

</ property >

< property name ="mappings" >

< props >

< prop key ="/report/simBscxls.xls" >

simBscStatisticXlsViewController

</ prop >

< prop key ="/report/merchantBscxls.xls" >

merchantXlsViewController

</ prop >

</ props >

</ property >

</ bean >

</ beans >

xls工厂声明文件:

<? xml version="1.0" encoding="GB2312" ?>

< beans xmlns ="http://www.springframework.org/schema/beans"

xmlns:xsi ="http://www.w3.org/2001/XMLSchema-instance"

xmlns:util ="http://www.springframework.org/schema/util"

xmlns:aop ="http://www.springframework.org/schema/aop"

xsi:schemaLocation ="

http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd

http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-2.0.xsd

http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.0.xsd" >

< bean id ="reportXlsViewResolver"

class ="com.ecpay.mvc.xlsviewresolver.ReportXlsViewResolver" >

</ bean >

</ beans >

好多xls可以共享一个 xls 工厂class

下面是我关于xls工厂类的

import java.util.Iterator;

import java.util.Map;

import java.util.Vector;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFRichTextString;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.springframework.web.servlet.view.document.AbstractExcelView;

import com.ecpay.kitsbag.FinalStaticParam;

import com.ecpay.pojo.xlsmodel.HssfCall;

public class ReportXlsViewResolver extends AbstractExcelView {

private static final Logger logger = Logger

.getLogger(ReportXlsViewResolver.class);

public ReportXlsViewResolver() {

if (logger.isInfoEnabled()) {

logger.info("电子表格制造工厂启动...");

} // end_log

} // end_fun

@SuppressWarnings("unchecked")

@Override

protected void buildExcelDocument(Map rootMap, HSSFWorkbook workbook,

HttpServletRequest request, HttpServletResponse response)

throws Exception {

HSSFSheet hssfSheet = workbook.createSheet("报表");

Vector<Vector<HssfCall>> hssfRowList = (Vector<Vector<HssfCall>>) rootMap

.get(FinalStaticParam.HSSF_ROW_LIST);

if (logger.isDebugEnabled()) {

logger.debug("当前共计 [" + hssfRowList.size() + "] 行电子表格数据");

}

Iterator<Vector<HssfCall>> iterator = hssfRowList.iterator();

int rowIndex = 1;

while (iterator.hasNext()) {

HSSFRow hssfRow = hssfSheet.createRow(rowIndex++);

Vector<HssfCall> cellList = iterator.next();

Iterator<HssfCall> iterators = cellList.iterator();

short cellIndex = 0;

while (iterators.hasNext()) {

HSSFCellStyle cellStyle = workbook.createCellStyle();

cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

HssfCall call = iterators.next();

HSSFCell cell = hssfRow.createCell(cellIndex++);

cell.setCellStyle(cellStyle);

turnCallToHssfCell(call, cell);

} // end_while

if (logger.isDebugEnabled()) {

logger.debug("完成对电子表格的构建...HTTP - 输出到客户端");

} // end_log

} // end_fun

/**

* @param call

* @param cell

* @note 函数功能描述：

private void turnCallToHssfCell(HssfCall call, HSSFCell cell) {

if (call.getCallType() == HssfCall.CALL_TYPE_STRING) {

HSSFRichTextString richTextString = new HSSFRichTextString(String

.valueOf(call.getCallValue()));

cell.setCellValue(richTextString);

cell.setCellType(HSSFCell.CELL_TYPE_STRING);

} else if (call.getCallType() == HssfCall.CALL_TYPE_NUMERIC) {

cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);

cell.setCellValue(Double.valueOf(call.getCallValue()));

} // end_if

} // end_fun

} // end_clazz

电子表格;类代码

package com.ecpay.mvc.xlsview;

import java.util.Map;

import java.util.Vector;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.springframework.web.servlet.ModelAndView;

import com.ecpay.kitsbag.AssembleHqlKit;

import com.ecpay.mvc.AbstractViewController;

import com.ecpay.mvc.xlsview.handle.AuditLogXlsViewControllerHandle;

import com.ecpay.pojo.xlsmodel.HssfCall;

public class AuditLogXlsViewController extends AbstractViewController {

private String clazzName;

private AuditLogXlsViewControllerHandle handle;

public AuditLogXlsViewController() {

}

@Override

protected ModelAndView handleRequestInternal(HttpServletRequest request,

HttpServletResponse response) throws Exception {

ModelAndView mav = new ModelAndView(getViewPath());

String hql = "";

String temp = "";

String condtions = request.getParameter("condtions");

if (condtions != null && (!condtions.equals(""))) {

temp = new String(condtions.getBytes("ISO-8859-1"), "GB2312");

} // end_if

hql = AssembleHqlKit.assembleFindHql(clazzName, temp);

if (logger.isDebugEnabled()) {

logger.debug(" 生成AuditLog报表的hql:" + hql);

} // end_log

Map<String, Vector<Vector<HssfCall>>> rootMap = handle

.handleViewProcess(hql);

mav.addAllObjects(rootMap);

return mav;

} // end_fun

public void setClazzName(String clazzName) {

this.clazzName = clazzName;

}

public void setHandle(AuditLogXlsViewControllerHandle handle) {

this.handle = handle;

}

} // end_clazz

希望对大家有点帮助