使用POI导出Excel报表

前端

var prefix = "/mediadata/mediaLibrary"
//导出报表
function Export() {
    location.href=prefix+'/export';
}

后端

/**
     * 导出报表
     *
     * @param params
     * @return
     */
    @GetMapping("/export")
    @RequiresPermissions("mediadata:mediaLibrary:mediaLibrary")
    public void export(@RequestParam Map<String, Object> params, HttpServletResponse response) throws IOException {
        List<MediaLibraryDO> mediaLibraryList = mediaLibraryService.export(params);
        ExcelWriter writer = ExcelUtil.getBigWriter();
        writer.addHeaderAlias("id","编号");
        writer.addHeaderAlias("titleName","标题名称");
        writer.addHeaderAlias("type","类型");
        writer.addHeaderAlias("onlineTime","上线时间");
        writer.addHeaderAlias("offlineTime","下线时间");
        writer.addHeaderAlias("onlineStatus","上/下线状态");
        writer.addHeaderAlias("source","来源");
        writer.addHeaderAlias("tariff","资费");
        writer.addHeaderAlias("provinceOutsideStatus","外省ITV上线状态");
        writer.addHeaderAlias("internetStatus","互联网上线状态");
        writer.addHeaderAlias("isCharge","外省ITV上线状态");
        writer.addHeaderAlias("provinceOutsideStatus","是否收费");
        writer.addHeaderAlias("updateProgress","更新进度");
        writer.addHeaderAlias("customLabel","自定义标签");
        writer.addHeaderAlias("remark","备注");
        writer.addHeaderAlias("createTime","创建时间");
        writer.write(mediaLibraryList, true);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        String fileName = new String("媒资信息表格.xlsx"
                .getBytes("utf-8"), "iso8859-1");
        response.setHeader("Content-Disposition", "attachment;filename="+fileName);
        ServletOutputStream out = response.getOutputStream();
        writer.flush(out, true);
        writer.close();
        IoUtil.close(out);
    }

方法二：

 @Log("导出媒资统计报表")
    @GetMapping("/export")
    @RequiresPermissions("form:visualizationTable:export")
    public void export(@RequestParam Map<String, Object> params, HttpServletResponse response) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        //sheet名称
        HSSFSheet sheet = workbook.createSheet();
        //获取表数据，根据自己实际情况获取
        List<MediaStatisticsDO> mediaStatisticsList = mediaStatisticsService.list(params);
        //设置要导出的文件的名字
        String fileName = new String("媒资统计报表.xlsx"
                .getBytes("utf-8"), "iso8859-1");
        //新增数据行，并且设置单元格数据
        int rowNum = 1;
        String[] headers = {"id", "已上线/有上线计划且时间未到", "逾期依旧未上线的媒资", "无版权媒资", "不上线的媒资", "未反馈的媒资",
                "时间","备注"};
        //headers表示excel表中第一行的表头
        HSSFRow row = sheet.createRow(0);
        //在excel表中添加表头
        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        //在表中存放查询到的数据放入对应的列
        for (MediaStatisticsDO mediaStatisticsDO : mediaStatisticsList) {
            HSSFRow row1 = sheet.createRow(rowNum);
            row1.createCell(0).setCellValue(mediaStatisticsDO.getId());
            row1.createCell(1).setCellValue(mediaStatisticsDO.getTimearrived());
            row1.createCell(2).setCellValue(mediaStatisticsDO.getOverdue());
            row1.createCell(3).setCellValue(mediaStatisticsDO.getNotcopyright());
            row1.createCell(4).setCellValue(mediaStatisticsDO.getNotlive());
            row1.createCell(5).setCellValue(mediaStatisticsDO.getNotfeedback());
            row1.createCell(6).setCellValue(format.format(mediaStatisticsDO.getCreateTime()));
            row1.createCell(7).setCellValue(mediaStatisticsDO.getRemark());
            rowNum++;
        }
        //设置自动列宽
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 13 / 10);
        }
        response.setContentType("application/octet-stream;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        workbook.write(response.getOutputStream());
    }

 <select id="export" resultType="com.bootdo.mediadata.domain.MediaLibraryDO">
		select `id`,`title_name`,`type`,`online_time`,`offline_time`,IF(`online_status`=0,'上线','下线') as online_status,`source`,
		`tariff`,if(`province_outside_status`=0,'上线','下线')as province_outside_status,
		if(`Internet_status`=0,'上线','下线')as Internet_status,if(`is_charge`=0,'是','否')as is_charge,`update_progress`,
		`custom_label`,`remark`,`create_time`,`state` from media_library
		<where>
			<if test="id != null and id != ''"> and id = #{id} </if>
			<if test="titleName != null and titleName != ''"> and title_name  like CONCAT('%',#{titleName},'%') </if>
			<if test="type != null and type != ''"> and type = #{type} </if>
			<if test="onlineTime != null and onlineTime != ''"> and online_time = #{onlineTime} </if>
			<if test="offlineTime != null and offlineTime != ''"> and offline_time = #{offlineTime} </if>
			<if test="onlineStatus != null and onlineStatus != ''"> and online_status = #{onlineStatus} </if>
			<if test="source != null and source != ''"> and source = #{source} </if>
			<if test="tariff != null and tariff != ''"> and tariff = #{tariff} </if>
			<if test="provinceOutsideStatus != null and provinceOutsideStatus != ''"> and province_outside_status = #{provinceOutsideStatus} </if>
			<if test="internetStatus != null and internetStatus != ''"> and Internet_status = #{internetStatus} </if>
			<if test="isCharge != null and isCharge != ''"> and is_charge = #{isCharge} </if>
			<if test="updateProgress != null and updateProgress != ''"> and update_progress = #{updateProgress} </if>
			<if test="customLabel != null and customLabel != ''"> and custom_label = #{customLabel} </if>
			<if test="remark != null and remark != ''"> and remark = #{remark} </if>
			<if test="createTime != null and createTime != ''"> and create_time = #{createTime} </if>
			<if test="state != null and state != ''"> and state = #{state} </if>
		</where>
		<choose>
			<when test="sort != null and sort.trim() != ''">
				order by ${sort} ${order}
			</when>
			<otherwise>
				order by id desc
			</otherwise>
		</choose>
		<if test="offset != null and limit != null">
			limit #{offset}, #{limit}
		</if>
	</select>