javaEE excel导出功能实现

1. 用例

在开发系统时,一般涉及到查询统计数据,都有导出数据到Excel中并下载到客户端的功能.

2. 流程

2.1 浏览器端点击导出数据到excel.

2.2 服务器接收到该请求后,处理请求,查询数据.

2.3 服务器通过调用poi API,产生excel数据.

2.4 修改response 表头属性.

2.5 把excel数据写入response输出流中.

3. 配置及类

3.1 controller方法

//导出excel    @RequestMapping("/hello/export")    public void export(HttpServletResponse response) {        List<UserDto> list=userInfoService.findUserDtoListByKeyWord(getPagination(new SearchDto(), 1));        ExportTableDto dto=ExportTableHandler.getExportTableDtoByType("user");        try {            ExcelExportUtils.exportExcel(response,list,dto.getTitle(),dto.getMap());        } catch (IOException e) {            throw new PropertyException(e.getMessage());        }    }

3.2 excel导出工具类

public class ExcelExportUtils {    private static Logger logger = LogManager.getLogger(ExcelExportUtils.class);    /**     * 设置头信息     *     * @param response     * @param fileName     */    private static void setResponseHeader(HttpServletResponse response, String fileName) {        response.setContentType("application/octet-stream;charset=utf-8");        try {            response.setHeader("Content-Disposition", "attachment;filename="                    + java.net.URLEncoder.encode(fileName + "_" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()) + ".xlsx", "utf-8"));//  客户端不缓存             response.addHeader("Pragma", "no-cache");            response.addHeader("Cache-Control", "no-cache");        } catch (UnsupportedEncodingException e) {            e.printStackTrace();        }    }    public static void exportExcel(HttpServletResponse response, List list, String title, Map<String, String> headColumn) throws IOException {        setResponseHeader(response, title);        exportExcel(response.getOutputStream(), list, title, headColumn);    }    /**     * 导出excel到输出流中     *     * @param outputStream     * @param list         数据集合     * @param title        标题     * @param headColumn   表头及对应数据属性名     */    public static void exportExcel(OutputStream outputStream, List list, String title, Map<String, String> headColumn) {        if (outputStream instanceof HttpServletResponse) {            setResponseHeader((HttpServletResponse) outputStream, title);        }        List<String> columnData = new LinkedList<String>();        XSSFWorkbook wb = new XSSFWorkbook();        Sheet sheet = wb.createSheet();        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headColumn.size() - 1));//合并第一行行单元格        Row titleRow = sheet.createRow(0);        CellStyle headStyle = wb.createCellStyle();        headStyle.setAlignment(CellStyle.ALIGN_CENTER);        XSSFFont headerFont = wb.createFont();        headerFont.setBold(true);        headStyle.setFont(headerFont);        titleRow.setRowStyle(headStyle);        Cell titleCell = titleRow.createCell(0);        titleCell.setCellStyle(headStyle);        titleCell.setCellValue(title);        Row headerRow = sheet.createRow(1);        headerRow.setRowStyle(headStyle);        int cellIndex = 0;        for (Map.Entry<String, String> entry : headColumn.entrySet()) {            sheet.setColumnWidth(cellIndex, entry.getValue().length() * 750);            Cell headerCell = headerRow.createCell(cellIndex++);            headerCell.setCellValue(entry.getValue());            headerCell.setCellStyle(headStyle);            columnData.add(entry.getKey());        }        for (int i = 0; i < list.size(); i++) {            cellIndex = 0;            //行            Row row = sheet.createRow(i + 2);            for (String column : columnData) {                row.createCell(cellIndex++).setCellValue(getValue(list.get(i), column));            }        }        try {            wb.write(outputStream);        } catch (IOException e) {            logger.error(e);        }    }    /**     * @param obj 操作的对象     * @param att 操作的属性     */    private static String getValue(Object obj, String att) {        if (obj instanceof Map) {            return ((Map) obj).get(att).toString();        }        att = att.substring(0, 1).toUpperCase() + att.substring(1);        String value = "";        Method method = null;        try {            method = obj.getClass().getMethod("get" + att);        } catch (NoSuchMethodException e) {            logger.error("导出对象中不存在属性:" + att, e);            throw new PropertyException("导出对象中不存在属性:" + att);        }        Object objValue = ReflectionUtils.invokeMethod(method, obj);        value = objValue == null ? "" : objValue.toString();        return value;    }}

3.3 读取excel导出配置文件类

public class ExportTableHandler {    private static ExportTableHandler exportTableHandler = null;    private Map<String, ExportTableDto> map;    private ExportTableHandler() {        ClassPathResource resource = new ClassPathResource("config/export-table-conf.xml");        try {            map = new HashMap<String, ExportTableDto>();            Document document = new SAXReader().read(resource.getInputStream());            Element root = document.getRootElement();            List<Element> elementList = root.elements();            for (Element element : elementList) {                ExportTableDto exportTableDto = new ExportTableDto(element.attributeValue("title"));                List<Element> childList = element.elements();                for (Element child : childList) {                    exportTableDto.addData(child.attributeValue("property"), child.getStringValue());                }                map.put(element.attributeValue("type"), exportTableDto);            }        } catch (Exception e) {            e.printStackTrace();        }    }    public static ExportTableHandler getExportTableHandler() {        if (exportTableHandler == null) {            exportTableHandler = new ExportTableHandler();        }        return exportTableHandler;    }    //根据导出类型返回配置信息    public static ExportTableDto getExportTableDtoByType(String type) {        return getExportTableHandler().map.get(type);    }}

public class ExportTableDto{    public ExportTableDto(String title){        this.title=title;    }    private String title;    private Map<String,String> map=new LinkedHashMap<String, String>();    public void addData(String key,String name){        map.put(key,name);    }    //标题    public String getTitle() {        return title;    }    //标题属性Map集合    public Map<String, String> getMap() {        return map;    }}

3.4 excle 配置文件

<?xml version="1.0" encoding="utf-8"?><root>    <table type='user' title='用户信息'>        <td property='username'>登录名</td>        <td property='realName'>姓 名</td>        <td property='tel'>电 话</td>    </table></root>