spring+struts2+mybatis中poi导出excel数据

1、html

<div id="formDiv">
     <form id="dynamicForm" target="_blank">
     </form>
</div>

<a href="javascript:void(0);" id="exporExcel" class="easyui-linkbutton" iconCls="icon-download" onclick="javascript:exportExcel();">导出excel</a>

2、js

function exportExcel(){
    var rows = $("#msp_table").datagrid("getChecked");
    if(rows.length<1){
        $.messager.alert("提示", "请勾选要导出的数据", "info");
        return;
    }
    var param={};
    for(var i=0,max=rows.length;max>i;i++){
        param[i]=rows[i].sysIdKey;
    }
    DownLoadFile({
        url:basePath+"/daySalePlan/exportExcel.action", //请求的url
        data:param//要发送的数据
    });
}

var DownLoadFile = function (options) {
    var dynamicForm = document.getElementById("dynamicForm");
    dynamicForm.setAttribute("method","post");
    dynamicForm.setAttribute("action",options.url);
    dynamicForm.innerHTML = "";
    for (var key in options.data) {
        var input = document.createElement("input");
        input.setAttribute("type","hidden");
        input.setAttribute("name","details[" + key + "].sysIdKey");
        input.setAttribute("value",options.data[key]);
        dynamicForm.appendChild(input);
    }
    dynamicForm.submit();
}

3、struts2 mxl

　　　　　　<!--Excel下载-->  
           <action name="exportExcel" class="com.icss.action.daysaleplan.DaySalePlanAction" method="exportExcel">  
                <!-- 下载各种题型模板 -->
            <result name="success" type="stream">  
                 <!-- 文件类型 --> 
                 <param name="contentType">application/vnd.ms-excel</param> 
                 <!-- excelStream 与对应action中的输入流的名字要一致 --> 
                 <param name= "inputName">excelStream</param>  
                 <!-- 文件名 与action中fileName一致 --> 
                 <param name="contentDisposition">attachment;filename=" ${fileName}.xls"</param>  
             </result> 
               <result name="error">/WEB-INF/pages/error/export_error.jsp</result> 
          </action>

4、action

　　public String exportExcel(){
        try {
            fileName="excel";
            excelStream=daySalePlanService.exportExcel(details);
        } catch (Exception e) {
            e.printStackTrace();
            return "error";
        }
        return SUCCESS;
    }

5、service

　　@Override
    public InputStream exportExcel(List<DaySalePlanDetail> details) throws Exception {
        String sysIdKeys="";
        for (int i = 0,max=details.size(); i < max; i++) {
            long sysIdKey=details.get(i).getSysIdKey();
            if(sysIdKeys.equals("")){
                sysIdKeys=""+sysIdKey;
            }else{
                sysIdKeys=sysIdKeys+","+sysIdKey;
            }
        }
        //查询所有自主卷烟
        String sql = "select myt.TPRODUCT_ID as CIG_CODE, myt.PLATE from(SELECT TO_CHAR(T.TPRODUCT_ID) AS TPRODUCT_ID, T.PSID, T.PLATE, T1.PRICE AS PRICE, CASE WHEN T1.CLASS = '一类' THEN 1 WHEN T1.CLASS = '二类' THEN 2 WHEN T1.CLASS = '三类' THEN 3 WHEN T1.CLASS = '四类' THEN 4 WHEN T1.CLASS = '五类' THEN 5 WHEN T1.CLASS = '' THEN 99 END AS PRICECLASS FROM (select t.tproduct_id as PRODUCT_ID from wp_cigbrand t where t.state = 1) TTT INNER JOIN WP_CIGBRAND T ON T.TPRODUCT_ID = TTT.PRODUCT_ID LEFT JOIN (SELECT T.TPRODUCT_ID, C.PSID, C.DELI_DATE, T.PLATE, C.PRICE, C.CLASS FROM WP_CIGBRAND T LEFT JOIN (SELECT * FROM C_PRODUCT_CONTROL T1 WHERE T1.DELI_DATE = (SELECT MAX(T2.DELI_DATE) FROM C_PRODUCT_CONTROL T2 WHERE T1.PSID = T2.PSID)) C ON T.PSID = C.PSID) T1 ON T1.TPRODUCT_ID = T.TPRODUCT_ID ORDER BY PRICECLASS ASC, T1.PRICE DESC, T.PLATE ASC) myt  ";
        List<Map<String, Object>> titleList = baseDao.selectSQL(sql);
        
        String[] headers = new String[titleList.size()+4];//excel第一行
        headers[0]="市场";
        headers[1]="到货时间";
        headers[2]="备注";
        headers[3]="合计";
        
        String[] headersId = new String[titleList.size()];//用于计算同一个公司同一个到货日期下的卷烟数量的插入位置
        
        //构造第一行的卷烟和插入卷烟对应记录数
        for (int i = 0,max = titleList.size(); i < max; i++) {
            Map<String, Object> data=titleList.get(i);
            headers[i+4]=data.get("PLATE").toString();
            headersId[i]=data.get("CIG_CODE").toString();
        }
        
        //查询导出的公司数据
        String dataSql="select M.Request_Num,M.ORG_REMARK,D.Cig_Code,v.SHORT_NM,T1.Area_Id,M.Org_Id,D.Confirm_Qty,D.EXPECT_ARRIVAL_DATE from WP_DAY_SALE_PLAN_ORDER M, WP_DAY_SALE_PLAN_ORDPR D, V_ORG V, WP_CIGBRAND C, WP_SALEAREA_ORG_MAPPING T1, WP_SALEAREA T2 where T2.SYS_ID_KEY = T1.AREA_ID AND T1.ORG_ID = M.ORG_ID AND M.SYS_ID_KEY = D.PLAN_MAIN_ID AND M.ORG_ID = V.SYS_ID_KEY AND D.CIG_CODE = C.TPRODUCT_ID AND M.SYS_ID_KEY IN("+sysIdKeys+") ORDER BY v.SHORT_NM,M.REQUEST_NUM, M.ORG_ID, D.CIG_CODE ";
        List<Map<String, Object>> dataList=baseDao.selectSQL(dataSql);
        
        String orgIds="";//导出数据对应的公司ID
        for (int i = 0,max=dataList.size(); i < max; i++) {
            if(i==0){
                orgIds=dataList.get(i).get("ORG_ID").toString();
            }else{
                orgIds=orgIds+","+dataList.get(i).get("ORG_ID");
            }
        }
        
        //查询公司所属的区域
        String areaSql=" SELECT T1.AREA_ID,T2.AREA_NAME FROM WP_SALEAREA_ORG_MAPPING T1,WP_SALEAREA T2 WHERE T2.SYS_ID_KEY=T1.AREA_ID AND T1.ORG_ID IN("+orgIds+") GROUP BY T1.AREA_ID,T2.AREA_NAME ";
        List<Map<String, Object>> areaList=baseDao.selectSQL(areaSql);
        
        List<ExpectArrivalDate> exData=new ArrayList<ExpectArrivalDate>();//excel表内容
        
        this.createData(areaList,exData,dataList,headersId);
        
        Map<String, Map<String, Object>> paramMap = this.getParamData();
        ExportExcel<ExpectArrivalDate> exportExcel=new ExportExcel<ExpectArrivalDate>();
        String pattern="yyyy-MM-dd";
        String title="期望到货日期";
        for (int i = 0,max=exData.size(); i < max; i++) {//合计行数据汇总
            ExpectArrivalDate arrivalDate=exData.get(i);
            arrivalDate.setSum(1);
        }
        
        this.createSumRow(exData,1,"合计(万支)");//添加合计(万支)行
        this.createSumRow(exData,5,"合计(箱)");//添加合计(箱)行
        
        //导出
        return exportExcel.exportExcel(title,headers,exData,pattern,paramMap);
    }

6、util

package com.icss.util;

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.commons.io.output.ByteArrayOutputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

/**
 * 项目名称: gswp <br>
 * 功能描述:导出excel
 * 作者:lanjianqing<lanjianqing@chinasofti.com> <br>
 * 公司: ICCS 中软国际<www.chinasofti.com><br>
 * 创建时间:2017-6-30 下午5:36:28<br>
 * 版本:v1.0<br>
 * 版权所有 chinasofti.com Corporation 2015 <br>
 * 
 * 修改内容:    <br>
 * 修改人: <br>
 * 修改时间:2017-6-30 下午5:36:28<br>
 * 备注: <br>
 */
public class ExportExcel<T>{

    /**
     * 方法名: <br>
     * 功能描述:* 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
     * 
     * @param title
     *            表格标题名
     * @param headers
     *            表格属性列名数组
     * @param dataset
     *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
     *            javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
     * @param out
     *            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
     * @param pattern
     *            如果有时间数据，设定输出格式。默认为"yyy-MM-dd"<br>
     * 作者:lanjianqing<lanjianqing@chinasofti.com> <br>
     * 创建时间:2017-6-30 下午5:37:58<br>
     * 参数:[参数类型][说明]..<br>
     * 返回值:<br>
     * 异常:<br>
     
     * 修改人:<br>
     * 修改时间:2017-6-30 下午5:37:58<br>
     * 备注:<br>
     */
    public InputStream exportExcel(String title, String[] headers, List<T> dataset, String pattern,Map<String, Map<String, Object>> map) throws Exception{
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth(15);
        
        // 声明一个画图的顶级管理器
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        // 定义注释的大小和位置,详见文档
        HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));
        // 设置注释内容
        comment.setString(new HSSFRichTextString("工商网上配货平台"));
        // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
        comment.setAuthor("lanjianqing");

        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        Map<String, Object> titleStyleMap=map.get("titleStyleMap");
        Map<String, Object> titleFontMap=map.get("titleFontMap");
        this.createTitlt(workbook, headers, row,titleStyleMap,titleFontMap);
        sheet.createFreezePane( 0, 1, 0, 1 );//冻结第一行

        // 遍历集合数据，产生数据行
        Iterator<T> it = dataset.iterator();
        Map<String, Object> commentStyleMap=map.get("commentStyleMap");
        Map<String, Object> commentFontMap=map.get("commentFontMap");
        this.createComment(it, sheet, row, pattern, workbook, patriarch,commentStyleMap,commentFontMap);
        //sheet.createFreezePane( 1, 0, 1, 0 );   
        
        ByteArrayOutputStream baos = new ByteArrayOutputStream(); 
        workbook.write(baos); 
        baos.flush(); 
        byte[] byteData = baos.toByteArray(); 
        InputStream excelStream = new ByteArrayInputStream(byteData,0, byteData.length); 
        baos.close();
        return excelStream;
    }
    
    /**
     * 方法名: <br>
     * 功能描述:* 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
     * 
     * @param title
     *            表格标题名
     * @param headers
     *            表格属性列名数组
     * @param dataset
     *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
     *            javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
     * @param out
     *            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
     * @param pattern
     *            如果有时间数据，设定输出格式。默认为"yyy-MM-dd"<br>
     * 作者:lanjianqing<lanjianqing@chinasofti.com> <br>
     * 创建时间:2017-6-30 下午5:37:58<br>
     * 参数:[参数类型][说明]..<br>
     * 返回值:<br>
     * 异常:<br>
     
     * 修改人:<br>
     * 修改时间:2017-6-30 下午5:37:58<br>
     * 备注:<br>
     */
    public InputStream exportExcel(String title,List<T> dataset, String pattern,Map<String, Map<String, Object>> map) throws Exception{
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth(15);
        
        // 声明一个画图的顶级管理器
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        // 定义注释的大小和位置,详见文档
        HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));
        // 设置注释内容
        comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
        // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
        comment.setAuthor("lanjianqing");

        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);

        // 遍历集合数据，产生数据行
        Iterator<T> it = dataset.iterator();
        Map<String, Object> commentStyleMap=map.get("commentStyleMap");
        Map<String, Object> commentFontMap=map.get("commentFontMap");
        this.createComment(it, sheet, row, pattern, workbook, patriarch,commentStyleMap,commentFontMap);
        
        ByteArrayOutputStream baos = new ByteArrayOutputStream(); 
        workbook.write(baos); 
        baos.flush(); 
        byte[] byteData = baos.toByteArray(); 
        InputStream excelStream = new ByteArrayInputStream(byteData,0, byteData.length); 
        baos.close();
        return excelStream;
    }

    /**
     * 方法名: <br>
     * 功能描述:遍历集合数据，产生数据行<br>
     * 作者:lanjianqing<lanjianqing@chinasofti.com> <br>
     * 创建时间:2017-7-3 下午4:42:18<br>
     * 参数:[参数类型][说明]..<br>
     * 返回值:<br>
     * 异常:<br>
     * 修改人:<br>
     * 修改时间:2017-7-3 下午4:42:18<br>
     * 备注:<br>
     */
    private void createComment(Iterator<T> it,HSSFSheet sheet,HSSFRow row,String pattern,HSSFWorkbook workbook,HSSFPatriarch patriarch,Map<String, Object> commentStyleMap,Map<String, Object> commentFontMap) throws Exception, Exception, Exception {
        HSSFCellStyle commentStyle=this.getStyle(workbook, commentStyleMap);
        HSSFFont commentFont=getFont(workbook, commentFontMap);
        commentStyle.setFont(commentFont);
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            T t = (T) it.next();
            // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
            Field[] fields = t.getClass().getDeclaredFields();
            for (int i = 0,max=fields.length; i <max ; i++) {
                HSSFCell cell = row.createCell(i);
                Field field = fields[i];
                String fieldName = field.getName();
                String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                
                Class<? extends Object> tCls = t.getClass();
                Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
                Object value = getMethod.invoke(t, new Object[] {});
                // 判断值的类型后进行强制类型转换
                String textValue = null;
                if (value instanceof Boolean) {
                    boolean bValue = (Boolean) value;
                    if (!bValue) {
                        textValue = "女";
                    }else{
                        textValue = "男";
                    }
                } else if (value instanceof Date) {
                    Date date = (Date) value;
                    SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                    textValue = sdf.format(date);
                } else if (value instanceof byte[]) {
                    // 有图片时，设置行高为60px;
                    row.setHeightInPoints(60);
                    // 设置图片所在列宽度为80px,注意这里单位的一个换算
                    sheet.setColumnWidth(i, (short) (35.7 * 80));
                    // sheet.autoSizeColumn(i);
                    byte[] bsValue = (byte[]) value;
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 6, index, (short) 6, index);
                    anchor.setAnchorType(2);
                    patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
                } else {
                    // 其它数据类型都当作字符串简单处理
                    textValue = value!=null?value.toString():"";
                    
                }
                // 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
                if (textValue != null) {
                    Pattern p = Pattern.compile("^(\\-|\\+)?\\d+(\\.\\d+)?$");
                    Matcher matcher = p.matcher(textValue);
                    if (matcher.matches()) {// 是数字当作double处理
                        if(Double.parseDouble(textValue)==0){
                            cell.setCellValue("");
                        }else{
                            cell.setCellValue(Double.parseDouble(textValue));
                        }
                    } else {
                        HSSFRichTextString richString = new HSSFRichTextString(textValue);
                        HSSFFont font3 = workbook.createFont();
                        font3.setColor(HSSFColor.BLACK.index);
                        richString.applyFont(font3);
                        cell.setCellValue(richString);
                    }
                }
            }
        }
    }

    /**
     * 方法名: <br>
     * 功能描述:产生标题行<br>
     * 作者:lanjianqing<lanjianqing@chinasofti.com> <br>
     * 创建时间:2017-7-3 下午4:43:23<br>
     * 参数:[参数类型][说明]..<br>
     * 返回值:<br>
     * 异常:<br>
     
     * 修改人:<br>
     * 修改时间:2017-7-3 下午4:43:23<br>
     * 备注:<br>
     */
    private void createTitlt(HSSFWorkbook workbook,String[] headers,HSSFRow row,Map<String, Object> styleMap,Map<String, Object> fontMap) {
        HSSFCellStyle titleStyle=this.getStyle(workbook, styleMap);
        HSSFFont titleFont=getFont(workbook, fontMap);
        titleStyle.setFont(titleFont);
        for (int i = 0,max=headers.length; i < max; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(titleStyle);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
    }
    
    /**
     * 方法名: <br>
     * 功能描述:获取样式<br>
     * 作者:lanjianqing<lanjianqing@chinasofti.com> <br>
     * 创建时间:2017-7-3 下午5:53:14<br>
     * 参数:[参数类型][说明]..<br>
     * 返回值:<br>
     * 异常:<br>
     
     * 修改人:<br>
     * 修改时间:2017-7-3 下午5:53:14<br>
     * 备注:<br>
     */
    private HSSFCellStyle getStyle(HSSFWorkbook workbook,Map<String, Object> styleMap){
        // 生成标题样式
        HSSFCellStyle titleStyle = workbook.createCellStyle();
        // 设置背景色
        titleStyle.setFillForegroundColor(Short.valueOf(styleMap.get("fillForegroundColor").toString()));
        titleStyle.setFillPattern(Short.valueOf(styleMap.get("fillPattern").toString()));
        
        // 设置边框
        titleStyle.setBorderBottom(Short.valueOf(styleMap.get("borderBottom").toString()));
        titleStyle.setBorderLeft(Short.valueOf(styleMap.get("borderLeft").toString()));
        titleStyle.setBorderRight(Short.valueOf(styleMap.get("borderRight").toString()));
        titleStyle.setBorderTop(Short.valueOf(styleMap.get("borderTop").toString()));
        // 设置居中
        titleStyle.setAlignment(Short.valueOf(styleMap.get("alignment").toString()));
        
        return titleStyle;
    }
    
    /**
     * 方法名: <br>
     * 功能描述:获取字体<br>
     * 作者:lanjianqing<lanjianqing@chinasofti.com> <br>
     * 创建时间:2017-7-3 下午5:53:34<br>
     * 参数:[参数类型][说明]..<br>
     * 返回值:<br>
     * 异常:<br>
     
     * 修改人:<br>
     * 修改时间:2017-7-3 下午5:53:34<br>
     * 备注:<br>
     */
    private HSSFFont getFont(HSSFWorkbook workbook,Map<String, Object> fontMap){
        HSSFFont font = workbook.createFont();
        font.setColor(Short.valueOf(fontMap.get("color").toString()));
        font.setFontHeightInPoints(Short.valueOf(fontMap.get("fontHeightInPoints").toString()));
        font.setBoldweight(Short.valueOf(fontMap.get("boldweight").toString()));
        return font;
    }
}