java使用jxl实现Excel导出

基本说明

1. 需求:在查询条件下 实现页面选取个别字段导出,实现页面分页导出以及全部导出
2. 出参:文件
3. 入参:
   （1）fileName 文件名
   （2）propertyMap 映射关系 表头-实际字段名 key-value形式参数来实现字段数据的转换
   （3）selectMap 查询条件 pageNum和pageSize参数来实现当前页导出 如不传则为全部导出

操作步骤

1. 导包

        <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.12</version>
        </dependency>

2. ExcelUtils类

package com.boerkang.project.chronicdet.util;

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.servlet.http.HttpServletResponse;
import com.boerkang.common.utils.StringUtils;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;


public class ExcelUtils {
    private HttpServletResponse response;
    
    private String fileName;
    
    private Map<String, String> propertyMap;
    
    public ExcelUtils(HttpServletResponse response, String fileName, Map<String, String> propertyMap) {
        this.response = response;
        this.fileName = StringUtils.isNotEmpty(fileName)?fileName:getFileName();
        this.propertyMap = propertyMap;

    }
    /**
     * 把集合对象写入到excel
     *
     * @param list 要写入的集合
     */
    @SuppressWarnings("unchecked")
    public void write(List list) throws Exception {
        String format = "yyyy-MM-dd HH:mm:ss";
        write(list, format);
    }
    /**
     * * 把集合对象写入到excel
     * * @param list 要写入的集合
     * * @param dateFormat 时间格式
     */
    @SuppressWarnings("unchecked")
    public void write(List list, String dateFormat) throws Exception {
        setExcelContentType();
        WritableWorkbook wwbook = null;
        OutputStream os = null;
        try {
            os = response.getOutputStream();
            wwbook = jxl.Workbook.createWorkbook(os);
            WritableSheet wsheet = wwbook.createSheet("Sheet1", 0);
            // 拿到表头数据
            Set<String> propertySet = propertyMap.keySet();

            int columnIndex = 0;
            for (Iterator<String> headIterator = propertySet.iterator(); headIterator.hasNext(); ) {// 添加表头
                String head = headIterator.next();
                jxl.write.Label titleCell = new jxl.write.Label(columnIndex,
                        0, head);
                wsheet.addCell(titleCell);
                wsheet.setColumnView(columnIndex, 25);
                columnIndex++;
            }
            if (list != null) {
                String methodName;
                int rowIndex = 0;
                for (Object o : list) {
                    rowIndex++;
                    columnIndex = 0;
                    for (String head : propertySet) {// 添加行记录
                        methodName = "get" + toFirstLetterUpperCase(propertyMap.get(head));
                        Object value = o.getClass().getMethod(methodName).invoke(o);
                        String cellValue = (value != null) ? value.toString() : "";
                        if (value instanceof Date) {//对时间格式做格式化输出
                            SimpleDateFormat sf = new SimpleDateFormat(dateFormat);
                            Date date = (Date) value;
                            cellValue = sf.format(date);
                        }
                        jxl.write.Label valueCell = new jxl.write.Label(
                                columnIndex++, rowIndex, cellValue);
                        wsheet.addCell(valueCell);//创建、添加Excel单元格
                    }
                }
            }
            wwbook.write();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (RowsExceededException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } finally {
            try {
                wwbook.close();
                os.close();
            } catch (IOException ie) {
                ie.printStackTrace();
            } catch (WriteException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * * 设置头信息
     * *
     * * @param response
     * *            HttpServletResponse
     * * @param fileName
     * *            默认的文件名称
     */
    private void setExcelContentType() {
        try {
            fileName = new String(fileName.getBytes("utf-8"),"iso8859-1");
        } catch (UnsupportedEncodingException e) {
        }
        response.reset();
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
    }

    /**
     * 产生一个以当前服务器时间的文件名称
     */
    private String getFileName() {
        SimpleDateFormat sf = new SimpleDateFormat("yyyyMMddHHmmss");
        return sf.format(System.currentTimeMillis());
    }

    /**
     * 首字母大写
     */
    private String toFirstLetterUpperCase(String str) {
        if (str == null || str.length() < 2) {
            return str;
        }
        String firstLetter = str.substring(0, 1).toUpperCase();
        return firstLetter + str.substring(1, str.length());
    }
}

3. service 代码

    @Override
    public void exportExcel(String fileName, JSONObject propertyJson, JSONObject selectJson, HttpServletResponse response) {
        HashMap<String, String> propertyMap = new HashMap<>();
        Iterator it = propertyJson.entrySet().iterator();
        while (it.hasNext()) {
            Map.Entry<String, String> entry = (Map.Entry<String, String>) it.next();
            propertyMap.put(entry.getKey(), entry.getValue());
        }
        HashMap<String, String> selectMap = new HashMap<>();
        it = selectJson.entrySet().iterator();
        while (it.hasNext()) {
            Map.Entry<String, String> entry = (Map.Entry<String, String>) it.next();
            selectMap.put(entry.getKey(), entry.getValue());
        }
        List list = getListData(selectMap);
        ExcelUtils utils = new ExcelUtils(response, fileName, propertyMap);
        try {
            utils.write(list);
        } catch (Exception e) {
            logger.error("exportExcel excel error In document "+ this.getClass().getName());
        }
    }

4. controller 代码

    /**
     * excel导出
     *
     * @return
     */
    @PostMapping("exportExcel")
    public void exportExcel(@RequestBody JSONObject jsonObject, HttpServletResponse response) {
        String fileName = "";
        if (jsonObject.get("fileName") != null){
            fileName = jsonObject.getString("fileName");
        }
        JSONObject propertyJson = jsonObject.getJSONObject("propertyMap");
        JSONObject selectJson = jsonObject.getJSONObject("selectMap");
        if (selectJson.get("pageNum")!=null && StringUtils.isNotEmpty(selectJson.getString("pageNum"))){
            PageEntity pageEntity = new PageEntity(selectJson.getInteger("pageNum"),selectJson.getInteger("pageSize"));
            startPage(pageEntity);
        }
        feritinOutdService.exportExcel(fileName, propertyJson, selectJson, response);
    }

实现效果

注:postman 下载文件 文件名一律为response
如有更好的实现 欢迎留言讨论