spring集成poi完成电子表格报表完全版

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

<!-- ======================================================== -->
    <!--           Spring MVC DispatcherServlet Servlet           -->
    <!-- ======================================================== -->
    <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>
<!-- ======================================================== -->
    <!--                  Spring MVC Config 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>
    <!-- ======================================================== -->
    <!-- =========       封装Xls HssfMap          ================ -->
    <!-- ======================================================== -->
    <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>

    <!-- ======================================================== -->
    <!-- =========       Handle of Map           ================ -->
    <!-- ======================================================== -->
    <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>

    <!-- ======================================================== -->
    <!-- =========       Mapping of URL          ================ -->
    <!-- ======================================================== -->
    <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
        } // 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

希望对大家有点 帮助