Excel数据导出功能-白红宇

Excel数据导出功能

阅读量：7088 次

发布时间：2019-06-28

本文共 9455 字，大约阅读时间需要 31 分钟。

package com.sysware.task.util;import org.apache.poi.hssf.usermodel.*;import javax.servlet.ServletOutputStream;import javax.servlet.http.HttpServletResponse;import java.io.*;import java.text.SimpleDateFormat;import java.util.Date;import java.util.List;import java.util.Map;/** * 导出文件工具类 */public class ExportFileUtil {    /**     * 导出统计详情EXCEL     */    public static void exportDetailExcel(List
     
      > list, HttpServletResponse response) throws Exception {        // 创建表格        HSSFWorkbook wb = createStatisticsDetailSheet(list);        // 导出文件        exportFile(wb, response);    }    /**     * 导出月度计划任务EXCEL     */    public static void exportYMTaskExcel(boolean isOwnpower, List
      
       
        > list, HttpServletResponse response) throws Exception {        // 创建表格        HSSFWorkbook wb = createYMTaskSheet(isOwnpower, list);        // 导出文件        exportFile(wb, response);    }    /**     * 创建统计详情EXCEL表格     *     * @param list     * @throws Exception     */    public static HSSFWorkbook createStatisticsDetailSheet(List
        
         
          > list) { // 创建工作薄 HSSFWorkbook wb = new HSSFWorkbook(); // 创建工作表 HSSFSheet sheet = wb.createSheet("统计详情"); HSSFRow row = sheet.createRow((short) 0); sheet.createFreezePane(0, 1); // EXCEL样式 HSSFCellStyle cellstyle = wb.createCellStyle(); cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); cteateCell(row, (short) 0, "任务路径", cellstyle); cteateCell(row, (short) 1, "名称", cellstyle); cteateCell(row, (short) 2, "状态", cellstyle); cteateCell(row, (short) 3, "完成形式", cellstyle); cteateCell(row, (short) 4, "责任人", cellstyle); cteateCell(row, (short) 5, "责任部门", cellstyle); cteateCell(row, (short) 6, "计划开始", cellstyle); cteateCell(row, (short) 7, "计划结束", cellstyle); cteateCell(row, (short) 8, "所属专业", cellstyle); cteateCell(row, (short) 9, "所属阶段", cellstyle); cteateCell(row, (short) 10, "创建人", cellstyle); if (list != null && list.size() > 0) { for (int i = 0; i < list.size(); i++) { Map
          
            map = list.get(i); if (map != null && map.size() > 0) { row = sheet.createRow(i + 1); cteateCell(row, (short) 0, map.get("wbsCode") + "", cellstyle); cteateCell(row, (short) 1, map.get("name") + "", cellstyle); cteateCell(row, (short) 2, map.get("statusName") + "", cellstyle); cteateCell(row, (short) 3, map.get("completedType") + "", cellstyle); cteateCell(row, (short) 4, map.get("chargeManName") + "", cellstyle); cteateCell(row, (short) 5, map.get("chargeDepartmentName") + "", cellstyle); cteateCell(row, (short) 6, map.get("plannedStartTime") == null ? "" : map.get("plannedStartTime") + "", cellstyle); cteateCell(row, (short) 7, map.get("plannedEndTime") == null ? "" : map.get("plannedEndTime") + "", cellstyle); cteateCell(row, (short) 8, map.get("discipline") + "", cellstyle); cteateCell(row, (short) 9, map.get("projectPhaseName") + "", cellstyle); cteateCell(row, (short) 10, map.get("creatorName") + "", cellstyle); } } } return wb; } /** * 创建月度计划管理EXCEL表格 * * @param list * @throws Exception */ public static HSSFWorkbook createYMTaskSheet(boolean isOwnpower, List
           
            
             > list) { // 日期格式化 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); // 创建工作薄 HSSFWorkbook wb = new HSSFWorkbook(); // 创建工作表 HSSFSheet sheet = wb.createSheet("月度计划管理"); HSSFRow row = sheet.createRow((short) 0); sheet.createFreezePane(0, 1); // EXCEL样式 HSSFCellStyle cellstyle = wb.createCellStyle(); cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); cteateCell(row, (short) 0, "任务路径", cellstyle); cteateCell(row, (short) 1, "类型", cellstyle); cteateCell(row, (short) 2, "名称", cellstyle); cteateCell(row, (short) 3, "项目名称", cellstyle); cteateCell(row, (short) 4, "完成形式", cellstyle); cteateCell(row, (short) 5, "责任人(工作系数)", cellstyle); cteateCell(row, (short) 6, "责任部门", cellstyle); cteateCell(row, (short) 7, "协作人(工作系数)", cellstyle); cteateCell(row, (short) 8, "协作部门", cellstyle); cteateCell(row, (short) 9, "计划开始时间", cellstyle); cteateCell(row, (short) 10, "计划结束时间", cellstyle); cteateCell(row, (short) 11, "实际开始时间", cellstyle); cteateCell(row, (short) 12, "实际结束时间", cellstyle); if (list != null && list.size() > 0) { for (int i = 0; i < list.size(); i++) { Map
             
               map = list.get(i); if (map != null && map.size() > 0) { row = sheet.createRow(i + 1); cteateCell(row, (short) 0, map.get("WBSCODE") + "", cellstyle); cteateCell(row, (short) 1, map.get("MODELNAME") + "", cellstyle); cteateCell(row, (short) 2, map.get("NAME") + "", cellstyle); cteateCell(row, (short) 3, map.get("PROJECTNAME") + "", cellstyle); cteateCell(row, (short) 4, map.get("COMPLETEDTYPE") == null ? "" : map.get("COMPLETEDTYPE").toString(), cellstyle); cteateCell(row, (short) 5, map.get("CHARGEMANNAME") == null ? "" : map.get("CHARGEMANNAME").toString(), cellstyle); cteateCell(row, (short) 6, map.get("CHARGEDEPARTMENTNAME") == null ? "" : map.get("CHARGEDEPARTMENTNAME") + "", cellstyle); cteateCell(row, (short) 7, map.get("PARTICIPATEMANNAME") == null ? "" : map.get("PARTICIPATEMANNAME") + "", cellstyle); cteateCell(row, (short) 8, map.get("PARTICIPATEDEPARTMENTNAME") == null ? "" : map.get("PARTICIPATEDEPARTMENTNAME") + "", cellstyle); cteateCell(row, (short) 9, map.get("PLANNEDSTARTTIME") == null ? "" : sdf.format(map.get("PLANNEDSTARTTIME")), cellstyle); cteateCell(row, (short) 10, map.get("PLANNEDENDTIME") == null ? "" : sdf.format(map.get("PLANNEDENDTIME")), cellstyle); cteateCell(row, (short) 11, map.get("ACTUALSTARTTIME") == null ? "" : sdf.format(map.get("ACTUALSTARTTIME")), cellstyle); cteateCell(row, (short) 12, map.get("ACTUALENDTIME") == null ? "" : sdf.format(map.get("ACTUALENDTIME")), cellstyle); } } } return wb; } /** * 导出文件 * * @param response * @param wb 表格 * @throws IOException */ public static void exportFile(HSSFWorkbook wb, HttpServletResponse response) throws IOException { // 初始化流 ByteArrayOutputStream os = new ByteArrayOutputStream(); BufferedInputStream bis = null; BufferedOutputStream bos = null; try { // 表格定稿数据 wb.write(os); byte[] content = os.toByteArray(); InputStream is = new ByteArrayInputStream(content); // 设置response参数 response.reset(); response.setContentType("application/vnd.ms-excel;charset=utf-8"); response.setHeader("Content-Disposition", "attachment;filename=" + new String("统计详情_".getBytes("GB2312"), "ISO_8859_1") + getTimeStamp() + ".xls"); ServletOutputStream out = response.getOutputStream(); bis = new BufferedInputStream(is); bos = new BufferedOutputStream(out); byte[] buff = new byte[2048]; int bytesRead; while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) { bos.write(buff, 0, bytesRead); } } catch (Exception e) { e.printStackTrace(); } finally { // 关闭流 if (bis != null) bis.close(); if (bos != null) bos.close(); if (os != null) os.close(); } } /** * 方法重载,防止过度样式添加而导致的错误 * * @param row 行 * @param col 列 * @param val 值 * @param cellstyle 样式 */ private static void cteateCell(HSSFRow row, short col, String val, HSSFCellStyle cellstyle) { HSSFCell cell = row.createCell(col); cell.setCellValue(val); cell.setCellStyle(cellstyle); } /** * 创建单元格,设置表头且居中 * * @param wb 工作簿 * @param row 行 * @param col 列 * @param val 值 */ private static void cteateCell(HSSFWorkbook wb, HSSFRow row, short col, String val) { HSSFCell cell = row.createCell(col); cell.setCellValue(val); HSSFCellStyle cellstyle = wb.createCellStyle(); cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); cell.setCellStyle(cellstyle); } /** * 该方法用来产生一个时间字符串（即：时间戳） * * @return */ public static String getTimeStamp() { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:MM:ss"); Date date = new Date(); return dateFormat.format(date); }}

后端调用

/**     * 导出统计详情数据     *     * @param params 查询任务详情参数集合     */    @RequestMapping(value = "exportDetailData/{params}", method = RequestMethod.GET)    public void exportDetailData(@PathVariable String params, HttpServletResponse response) {        try {            // 解码、转Map            Map
    
      queryParams = JSON.parseObject(URLDecoder.decode(params, "UTF-8"));            // 获取任务详情数据            List
     
      
       > list = getStatisticsTaskDetailData("exportType", queryParams);            // 导出EXCEL            ExportFileUtil.exportDetailExcel(list, response);        } catch (Exception e) {            e.printStackTrace();        }    }

前端调用

// 导出任务点击事件    $("#detailExport").bind("click", function () {        var params = {            "queryType": "<%=queryType%>",            "chargeDepartmentId": "<%=chargeDepartmentId%>",            "projectId": "<%=projectId%>",            "statusId": "<%=statusId%>",            "folderType": "<%=folderType%>",            "modelId": "<%=modelId%>",            "createDepartmentId": "<%=createDepartmentId%>",            "creatorId": "<%=creatorId%>",            "chargeManId": "<%=chargeManId%>",            "planStartTime0": "<%=planStartTime0%>",            "planStartTime1": "<%=planStartTime1%>",            "planEndTime0": "<%=planEndTime0%>",            "planEndTime1": "<%=planEndTime1%>",            "actualStartTime0": "<%=actualStartTime0%>",            "actualStartTime1": "<%=actualStartTime1%>",            "actualEndTime0": "<%=actualEndTime0%>",            "actualEndTime1": "<%=actualEndTime1%>"        };        window.location.href = basePath + "/task/businessProjectStatistics/exportDetailData/" + encodeURI(encodeURI(JSON.stringify(params)))+".action";    });

remark

1.AJAX调用方法不会弹下载EXCEL窗口,所以选择了window.location.href调用方式2.window.location.href调用方式在数据传输的时候会乱码,所以用了加密方法

转载于:https://my.oschina.net/Tsher2015/blog/1825944

你可能感兴趣的文章

Minimum Inversion Number

Let's encrypt申请泛域名证书以及报错处理

查看>>

centos6.5 安装jdk7和tomcat7

mysql基本数据类型(mysql学习笔记三)

查看>>

mongo数据删除和游标

查看>>

[原]Unity3D深入浅出 - 认识开发环境中的Layers面板

查看>>

【leetcode】719. Find K-th Smallest Pair Distance

查看>>

jmeter使用csv传参进行并发测试验证

查看>>

Laravel踩坑笔记——illuminate/html被抛弃

查看>>

C++的重载（overload）与重写（override）

【DFS】UVa10129 - Play on Words

查看>>