使用Apache POI导出数据到Excel表格

引言：什么是Apache POI？百度百科上的解释是这样的：Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。简单点来说就是可以利用POI来进行数据的导入导出操作，本篇文章主要说明使用Apache POI导出数据到Excel表格。

1.使用Apache POI需要导入相关的依赖，在pom.xml文件中添加如下依赖：

 <!-- 导出Excel依赖 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.11</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.11</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.11</version>
        </dependency>

2.工具类代码如下：

package cn.stylefeng.guns.core.util;

import org.apache.poi.hssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;


public class ExcelUtils {
    static final short borderpx = 1;

    /**
     * 导出excel表格
     * @param head
     * @param body
     * @return
     */
    public static HSSFWorkbook expExcel(List<String> head, List<List<String>> body) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Sheet1");
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell= null;
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        setBorderStyle(cellStyle, borderpx);
        cellStyle.setFont(setFontStyle(workbook, "黑体", (short) 14));
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        sheet.createFreezePane(0,1,0,1);

        for (int i = 0; i<head.size(); i++) {
            cell = row.createCell(i);
            cell.setCellValue(head.get(i));
            cell.setCellStyle(cellStyle);
        }

        HSSFCellStyle cellStyle2 = workbook.createCellStyle();
        setBorderStyle(cellStyle2, borderpx);
        cellStyle2.setFont(setFontStyle(workbook, "宋体", (short) 12));
        cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        for (int i = 0; i < body.size(); i++) {
            row = sheet.createRow(i + 1);
            List<String> paramList = body.get(i);
            for (int p = 0; p < paramList.size(); p++) {
                cell = row.createCell(p);
                cell.setCellValue(paramList.get(p));
                cell.setCellStyle(cellStyle2);
            }
        }
        for (int i = 0, isize = head.size(); i < isize; i++) {
            sheet.autoSizeColumn(i);
        }
        return workbook;
    }

    /**
     * 文件输出
     * @param workbook 填充好的workbook
     * @param path 存放的位置
     */
    public static void outFile(HSSFWorkbook workbook, String path, HttpServletResponse response) {
        SimpleDateFormat fdate=new SimpleDateFormat("yyyyMMdd-HH点mm分");
        path = path.substring(0, path.lastIndexOf(".")) + fdate.format(new Date()) + path.substring(path.lastIndexOf("."));
        OutputStream os=null;
        File file = null;
        try {
            file = new File(path);
            String filename = file.getName();
            os = new FileOutputStream(file);
            response.addHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(filename, "UTF-8"));
            os= new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            workbook.write(os);
        } catch (FileNotFoundException e1) {
            e1.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            os.flush();
            os.close();
            System.gc();
            System.out.println(file.delete());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 设置字体样式
     * @param workbook 工作簿
     * @param name 字体类型
     * @param height 字体大小
     * @return HSSFFont
     */
    private static HSSFFont setFontStyle(HSSFWorkbook workbook, String name, short height) {
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints(height);
        font.setFontName(name);
        return font;
    }

    /**
     * 设置单元格样式
     * @param cellStyle 工作簿
     * @param border border样式
     */
    private static void setBorderStyle(HSSFCellStyle cellStyle, short border) {
        cellStyle.setBorderBottom(border); // 下边框
        cellStyle.setBorderLeft(border);// 左边框
        cellStyle.setBorderTop(border);// 上边框
        cellStyle.setBorderRight(border);// 右边框
    }
}

3.在控制器中我们根据自己需要导出的数据，封装成list集合，再遍利该集合添加到报表数据集合中，其代码如下：

/**
     * excel导出报表
     */
    @RequestMapping("/excel")
    public void testExcel2(HttpServletResponse response,@RequestParam(required = false) String beginTime, @RequestParam(required = false) String endTime){
        ArrayList<Integer> constructionIdList=new ArrayList<>();
        //初始化工地名称集合
        constructionIdList=dataStatisticsService.getConstructionId();
        //初始化告警类型vo集合
        ArrayList<AlarmTypeVo> alarmTypeVoList=new ArrayList<>();
        //便利所有告警工地id
        for(Integer id:constructionIdList){
            //初始化告警类型vo类
            AlarmTypeVo alarmTypeVo=new AlarmTypeVo();
            alarmTypeVo.setBeginTime(beginTime);
            alarmTypeVo.setEndTime(endTime);

            //根据告警工地id得到告警工地名称
            String conStructionName=dataStatisticsService.getConstructionNameByConstructionId(id);
            alarmTypeVo.setConstructionSite(conStructionName);

            //根据工地id查询告警类型为0（安全帽）的总数
            int safetyHatCount=dataStatisticsService.getCameraTypeNumber(id,0,beginTime,endTime);
            alarmTypeVo.setSafetyHatCount(safetyHatCount);

            //根据工地id查询告警类型为1（工作服）的总数
            int coverallCount=dataStatisticsService.getCameraTypeNumber(id,1,beginTime,endTime);
            alarmTypeVo.setCoverallCount(coverallCount);

            //根据工地id查询告警类型为2（安全带）的总数
            int safetyBeltCount=dataStatisticsService.getCameraTypeNumber(id,2,beginTime,endTime);
            alarmTypeVo.setSafetyBeltCount(safetyBeltCount);

            //根据工地id查询告警类型为3（越界施工）的总数
            int crossBoundaryCount=dataStatisticsService.getCameraTypeNumber(id,3,beginTime,endTime);
            alarmTypeVo.setCrossBoundaryCount(crossBoundaryCount);

            //加入list中
            alarmTypeVoList.add(alarmTypeVo);
        }
        //创建报表数据头
        List<String> head = new ArrayList<>();
        head.add("开始日期");
        head.add("结束日期");
        head.add("工地地址");
        head.add("安全帽");
        head.add("工作服");
        head.add("安全带");
        head.add("越界施工");
        //创建报表体
        List<List<String>> body = new ArrayList<>();
        //便利需要导出的数据
        for (AlarmTypeVo alarmTypeVo : alarmTypeVoList) {
            List<String> bodyValue = new ArrayList<>();
            bodyValue.add(alarmTypeVo.getBeginTime());
            bodyValue.add(alarmTypeVo.getEndTime());
            bodyValue.add(alarmTypeVo.getConstructionSite());
            bodyValue.add(String.valueOf(alarmTypeVo.getSafetyHatCount()));
            bodyValue.add(String.valueOf(alarmTypeVo.getCoverallCount()));
            bodyValue.add(String.valueOf(alarmTypeVo.getSafetyBeltCount()));
            bodyValue.add(String.valueOf(alarmTypeVo.getCrossBoundaryCount()));

            //将数据添加到报表体中
            body.add(bodyValue);
        }
        String fileName = "工地预警信息统计.xls";
        HSSFWorkbook excel = ExcelUtils.expExcel(head,body);
        ExcelUtils.outFile(excel,"D:\\"+fileName,response);
    }

4.页面导出控件以及JavaScript代码如下：

<#button name="导出excel" icon="fa-plus" clickFun="excel()"/>
var foo = 'bar';

<script>
 //点击导出Excel事件
    function  excel() {
        //得到开始时间
        var beginTime=$("#beginTime").val();
        //得到结束时间
        var endTime=$("#endTime").val();
        //传入开始时间与结束时间
        location.href=Feng.ctxPath + "/dataStatistics/excel?beginTime="+beginTime+"&endTime="+endTime;

    }
</script>

5.页面效果如下图所示：

6.不输入查询条件点击导出按钮，导出表格如下图所示：


7.点击查询后数据会随着查询条件变化而变化，这时再点击导出，excel数据也会发生变化，如下图所示：