private static final String SHEET_NAME = "培养计划表";

/**

 * @param response

 * @param trainingName

 * @return

 */

@RequestMapping("/exportTrainingPlanMessageToExcel.do_")

@ResponseBody

public void exportTrainingPlanMessageToExcel(HttpServletResponse response, @RequestParam("trainingName") String trainingName) {

    //文件的默认保存名

    String fileName = "exportTrainingPlanMessageToExcel.xls";

    List<List<Object>> paramList = trainingPlanService.getParamList(trainingName);

    ExportEmployeeMessageToExcel.exportDataToExcel(fileName, response, paramList, SHEET_NAME);

}

@Override

public List<List<Object>> getParamList(String trainingName) {

    Map<String, Object> paramMap = new HashMap<>();

    paramMap.put("trainingName", trainingName);

    List<List<Object>> resultList = new ArrayList<>();

    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    List<TrainingPlan> dataList = trainingPlanDao.getTrainingPlanByTrainingName(paramMap);

    //添加excel文件表头

    List<Object> headList = new ArrayList<>();

    headList.add("创建时间");

    headList.add("知识技能");

    resultList.add(headList);

    //添加excel文件数据

    for (TrainingPlan trainingPlan : dataList) {

        list.add(sdf.format(trainingPlan.getCreateTime()));

        list.add(trainingPlan.getKnowledgeSkills());

        resultList.add(list);

    }

    return resultList;

}

public class ExportEmployeeMessageToExcel {

    private static final Logger log = LoggerFactory.getLogger(ExportEmployeeMessageToExcel.class);

    /**

     * 批量将数据导入到excel中

     * @param fileName 生成文件的名字

     * @param response

     * @param paramList 要导入到excel中所有的数据

     * @param sheetName excel sheet名字

     * @return

     */

    public static void exportDataToExcel(String fileName, HttpServletResponse response, List<List<Object>> paramList, String sheetName){

        /**

         * 以下为生成Excel操作

         */

        // 1.创建一个workbook，对应一个Excel文件

        HSSFWorkbook wb = new HSSFWorkbook();

        // 2.在workbook中添加一个sheet，对应Excel中的一个sheet

        HSSFSheet sheet = wb.createSheet(sheetName);

        // 3.在sheet中添加表头第0行，老版本poi对excel行数列数有限制short

        HSSFRow row = sheet.createRow(0);

        /*// 设置表头

        HSSFCell cell = row.createCell(0);

        cell.setCellValue("表头1");

        cell.setCellStyle(style);

        cell = row.createCell(1);

        cell.setCellValue("表头2");

        cell.setCellStyle(style);

        cell = row.createCell(2);

        cell.setCellValue("表头3");

        cell.setCellStyle(style);

        cell = row.createCell(3);

        cell.setCellValue("表头4");

        cell.setCellStyle(style);

        cell = row.createCell(4);

        cell.setCellValue("表头5");

        cell.setCellStyle(style);*/

        // 循环将数据写入Excel，包括表头

        for (int i = 0; i < paramList.size(); i++) {

            row = sheet.createRow((int) i);

            List list= paramList.get(i);

            // 创建单元格，设置值

            for (int j = 0; j < list.size(); j++) {

                row.createCell(j).setCellValue(String.valueOf(list.get(j)));

            }

        }

        downloadExcel(wb, response, fileName);

    }

    /**

     * 将导好数据的excel文件下载下来，并打开下载页面

     * @param fileName 生成文件的名字

     * @param response

     * @return

     */

    public static void downloadExcel(HSSFWorkbook wb, HttpServletResponse response, String fileName){

        try {

            ByteArrayOutputStream os = new ByteArrayOutputStream();

            wb.write(os);

            byte[] content = os.toByteArray();

            InputStream is = new ByteArrayInputStream(content);

            // 设置response参数，可以打开下载页面

            response.reset();

            response.setContentType("application/octet-stream; charset=UTF-8");

            response.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes(),"ISO8859-1"));

            ServletOutputStream out = response.getOutputStream();

            BufferedInputStream bis = null;

            BufferedOutputStream bos = null;

            try {

                bis = new BufferedInputStream(is);

                bos = new BufferedOutputStream(out);

                byte[] buff = new byte[1024];

                int bytesRead;

                // Simple read/write loop.

                while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {

                    bos.write(buff, 0, bytesRead);

                }

            } catch (Exception e) {

                log.error("异常情况为：" + e.getMessage());

            } finally {

                if (wb != null) {

                    wb.close();

                }

                if (bis != null) {

                    bis.close();

                }

                if (bos != null) {

                    bos.close();

                }

            }

        } catch (IOException e) {

            log.error("异常情况为：" + e.getMessage());

        }

    }

}

<!-- poi,excel解析xls格式 -->

<dependency>

    <groupId>org.apache.poi</groupId>

    <artifactId>poi</artifactId>

    <version>3.17</version>

</dependency>

<!-- poi-ooxml,excel解析xlsx格式 -->

<dependency>

    <groupId>org.apache.poi</groupId>

    <artifactId>poi-ooxml</artifactId>

    <version>3.17</version>

</dependency>

<dependency>

    <groupId>net.sf.json-lib</groupId>

    <artifactId>json-lib</artifactId>

    <version>2.4</version>

    <classifier>jdk15</classifier>

</dependency>

<!--excel文件不需要转码成二进制-->

<plugin>

    <groupId>org.apache.maven.plugins</groupId>

    <artifactId>maven-resources-plugin</artifactId>

    <version>3.0.2</version>

    <configuration>

        <encoding>UTF-8</encoding>

        <nonFilteredFileExtensions>

            <nonFilteredFileExtension>xls</nonFilteredFileExtension>

            <nonFilteredFileExtension>xlsx</nonFilteredFileExtension>

        </nonFilteredFileExtensions>

    </configuration>

</plugin>