sql2java-excel(二):基于apache poi实现数据库表的导出的spring web支持

import static gu.sql2java.Managers.instanceOf;

@RestController
@Api(value="ExcelController",tags={"Excel Export Controller"})
public class ExcelController {
	/** 
	 * 这里必须指定produces,否则swagger下载的excel文件不正确,
	 * 参见 <a href="https://blog.csdn.net/iuie_sl/article/details/114904170 ">《使用swagger api 下载excel，excel打不开》</a>
	 */
    @ApiOperation(value = "导出设备列表", notes = "",httpMethod="GET",produces=MediaType.APPLICATION_OCTET_STREAM_VALUE)
    @RequestMapping(method=RequestMethod.GET, value="/ExcelController/exportDevices")
	public void exportDevices(HttpServletResponse response) throws IOException {
		response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
		SimpleDateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd_HHmmss");
		String currentDateTime = dateFormatter.format(new Date());
		/** 设置下载文件文件名 */
		String headerKey = "Content-Disposition";
		String headerValue = "attachment; filename=device_" + currentDateTime + ".xlsx";
		response.setHeader(headerKey, headerValue);
		/** 从数据库中获取记录 **/
		List<DeviceBean> beans = instanceOf(IDeviceManager.class).loadAllAsList();
		ExcelGenerator<DeviceBean> generator = new ExcelGenerator<DeviceBean>(beans){};
        /** 输出到 http response */ 
		generator.generate(response);
	}

}

import static gu.sql2java.Managers.instanceOf;

@RestController
@Api(value="ExcelController",tags={"Excel Export Controller"})
public class ExcelController {
	/** 
	 * 这里必须指定produces,否则swagger下载的excel文件不正确,
	 * 参见 <a href="https://blog.csdn.net/iuie_sl/article/details/114904170 ">《使用swagger api 下载excel，excel打不开》</a>
	 */
    @ApiOperation(value = "导出设备列表", notes = "",httpMethod="GET",produces=MediaType.APPLICATION_OCTET_STREAM_VALUE)
    @RequestMapping(method=RequestMethod.GET, value="/ExcelController/exportDevices")
	public void exportDevices(HttpServletResponse response) throws IOException {
		response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
		SimpleDateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd_HHmmss");
		String currentDateTime = dateFormatter.format(new Date());
		/** 设置下载文件文件名 */
		String headerKey = "Content-Disposition";
		String headerValue = "attachment; filename=device_" + currentDateTime + ".xlsx";
		response.setHeader(headerKey, headerValue);
		/** 从数据库中获取记录 **/
		List<DeviceBean> beans = instanceOf(IDeviceManager.class).loadAllAsList();
		ExcelGenerator<DeviceBean> generator = new ExcelGenerator<DeviceBean>(beans){};
        /** excel 导出配置对象 */
        SheetConfig sheetConfig = generator.getSheetConfig();
        /** 设置exce表的标题 */
    	sheetConfig.setTitle("设备表记录");
        /** 设置输出字段列名,如果不指定则输出原始的英文字段名 */
        sheetConfig.configOf("physicalAddress").getColumnConfig().setName("物理地址");
    	sheetConfig.configOf("addressType").getColumnConfig().setName("地址类型");
    	sheetConfig.configOf("iotCard").getColumnConfig().setName("物联网卡编号");
    	sheetConfig.configOf("status").getColumnConfig().setName("设备状态").setReadConverterExp("ENABLE=正常,DISABLE=禁用,MAINTAIN=维护,PENDING=挂起(待审核)");
    	sheetConfig.configOf("fixedMode").getColumnConfig().setName("安装方式").setReadConverterExp("HANG=悬挂,FLOOR=落地");
    	sheetConfig.configOf("name").getColumnConfig().setName("设备名称");
    	sheetConfig.configOf("groupId").getColumnConfig().setName("设备组ID");
    	sheetConfig.configOf("model").getColumnConfig().setName("设备型号");
    	sheetConfig.configOf("vendor").getColumnConfig().setName("设备供应商");
    	sheetConfig.configOf("osArch").getColumnConfig().setName("操作系统平台");
    	sheetConfig.configOf("versionInfo").getColumnConfig().setName("版本");
    	sheetConfig.configOf("planId").getColumnConfig().setName("当前节目ID");
    	sheetConfig.configOf("targetId").getColumnConfig().setName("目标节目ID");
    	sheetConfig.configOf("screenInfo").getColumnConfig().setName("屏幕信息").setHandler(ScreenInfoFormatter.class);
    	sheetConfig.configOf("createTime").getColumnConfig().setName("记录创建时间");
    	sheetConfig.configOf("updateTime").getColumnConfig().setName("记录更新时间");
    	sheetConfig.configOf("remark").getColumnConfig().setName("备注");
        /** 设置子成员输出字段名 */
    	sheetConfig.addNestedColumn("props.last_active_time","上次在线时间");
    	sheetConfig.addNestedColumn("props.disk_capacity","磁盘容量");
    	sheetConfig.addNestedColumn("props.network","网络连接类型");
    	sheetConfig.addNestedColumn("props.status_comment","状态变更说明");
    	sheetConfig.addNestedColumn("device_detail.device_name","产品名称");
    	sheetConfig.addNestedColumn("device_detail.manufacturer","制造商");
    	sheetConfig.addNestedColumn("device_detail.made_date","生产日期");
        /** 设置不需要导出的隐藏字段 */
    	sheetConfig.setHideColumns("props","token_time","device_detail");
        /** 输出到 http response */ 
		generator.generate(response);
	}
}

@SpringBootApplication
@ComponentScan({"net.facelib.eam.devicecenter","net.facelib.eam.web.handler","gu.sql2java.excel.aspect"})
public class RestfulService {
	//
}

.	/** 
	 * 设备表导出EXCEL<br>
	 */
	@ExcelSheet()
	/** 
	 * 这里必须指定produces,否则swagger下载的excel文件不正确,
	 * 参见 <a href="https://blog.csdn.net/iuie_sl/article/details/114904170 ">《使用swagger api 下载excel，excel打不开》</a>
	 */
	@ApiOperation(value = "设备表导出EXCEL", notes = "设备表导出EXCEL",httpMethod="GET",produces="application/octet-stream")
    @RequestMapping(method=RequestMethod.GET,produces={"application/octet-stream")
	public List<JSONObject> exportDevices() {
		return instanceOf(IDeviceManager.class).loadAllAsList();

	}

	/** 
	 * 设备表导出EXCEL<br>
	 */
	@ExcelSheet(title="设备表记录",
			fileNamePrefix = "device_",
			hideColumns={"props","token_time","device_detail"},
			defaultIncludeColumns={"id","provinces","city","top_group_name","name",
					"physicalAddress","addressType","iotCard","network","screenInfo","fixedMode","model","osArch","versionInfo",
					"status","props.disk_capacity","props.last_active_time","createTime","remark"})
	@ExcelColumn(columnName="physicalAddress",name="物理地址")
	@ExcelColumn(columnName="addressType",name="地址类型")
	@ExcelColumn(columnName="iotCard",name="物联网卡编号")
	@ExcelColumn(columnName="status",name="设备状态",readConverterExp="ENABLE=正常,DISABLE=禁用,MAINTAIN=维护,PENDING=挂起(待审核)")
	@ExcelColumn(columnName="fixedMode",name="安装方式",readConverterExp="HANG=悬挂,FLOOR=落地")
	@ExcelColumn(columnName="name",name="设备名称")
	@ExcelColumn(columnName="groupId",name="设备组ID")
	@ExcelColumn(columnName="provinces",name="省/自治区/直辖市")
	@ExcelColumn(columnName="city",name="市")
	@ExcelColumn(columnName="top_group_name",name="设备组名称")
	@ExcelColumn(columnName="screenInfo",name="屏幕信息")
	@ExcelColumn(columnName="name",name="设备名称")
	@ExcelColumn(columnName="model",name="设备型号")
	@ExcelColumn(columnName="vendor",name="设备供应商")
	@ExcelColumn(columnName="osArch",name="操作系统平台")
	@ExcelColumn(columnName="network",name="网络连接类型")
	@ExcelColumn(columnName="versionInfo",name="版本")
	@ExcelColumn(columnName="planId",name="当前节目ID")
	@ExcelColumn(columnName="targetId",name="目标节目ID")
	@ExcelColumn(columnName="createTime",name="记录创建时间")
	@ExcelColumn(columnName="updateTime",name="记录修改时间")
	@ExcelColumn(columnName="remark",name="备注")
	@ExcelColumn(columnName="props.last_active_time",name="上次在线时间")
	@ExcelColumn(columnName="props.disk_capacity",name="磁盘容量")
	@ExcelColumn(columnName="props.status_comment",name="状态变更说明")
	@ExcelColumn(columnName="device_detail.device_name",name="产品名称")
	@ExcelColumn(columnName="device_detail.manufacturer",name="制造商")
	@ExcelColumn(columnName="device_detail.made_date",name="生产日期")
	/** 
	 * 这里必须指定produces,否则swagger下载的excel文件不正确,
	 * 参见 <a href="https://blog.csdn.net/iuie_sl/article/details/114904170 ">《使用swagger api 下载excel，excel打不开》</a>
	 */
	@ApiOperation(value = "设备表导出EXCEL", notes = "设备表导出EXCEL",httpMethod="GET",produces="application/octet-stream")
    @RequestMapping(method=RequestMethod.GET,produces="application/octet-stream")
	public List<DeviceBean> exportDevices() {
		return instanceOf(IDeviceManager.class).loadAllAsList();
	}

	/** 
	 * 设备表导出EXCEL<br>
	 */
	@ExcelSheet(title="设备表记录",
			fileNamePrefix = "device_",
			hideColumns={"props","token_time","device_detail"},
			defaultIncludeColumns={"id","provinces","city","top_group_name","name",
					"physicalAddress","addressType","iotCard","network","screenInfo","fixedMode","model","osArch","versionInfo",
					"status","props.disk_capacity","props.last_active_time","createTime","remark"})
	@ExcelColumn(columnName="physicalAddress",name="物理地址")
	@ExcelColumn(columnName="addressType",name="地址类型")
	@ExcelColumn(columnName="iotCard",name="物联网卡编号")
	@ExcelColumn(columnName="status",name="设备状态",readConverterExp="ENABLE=正常,DISABLE=禁用,MAINTAIN=维护,PENDING=挂起(待审核)")
	@ExcelColumn(columnName="fixedMode",name="安装方式",readConverterExp="HANG=悬挂,FLOOR=落地")
	@ExcelColumn(columnName="name",name="设备名称")
	@ExcelColumn(columnName="groupId",name="设备组ID")
	@ExcelColumn(columnName="provinces",name="省/自治区/直辖市")
	@ExcelColumn(columnName="city",name="市")
	@ExcelColumn(columnName="top_group_name",name="设备组名称")
	@ExcelColumn(columnName="screenInfo",name="屏幕信息")
	@ExcelColumn(columnName="name",name="设备名称")
	@ExcelColumn(columnName="model",name="设备型号")
	@ExcelColumn(columnName="vendor",name="设备供应商")
	@ExcelColumn(columnName="osArch",name="操作系统平台")
	@ExcelColumn(columnName="network",name="网络连接类型")
	@ExcelColumn(columnName="versionInfo",name="版本")
	@ExcelColumn(columnName="planId",name="当前节目ID")
	@ExcelColumn(columnName="targetId",name="目标节目ID")
	@ExcelColumn(columnName="createTime",name="记录创建时间")
	@ExcelColumn(columnName="updateTime",name="记录修改时间")
	@ExcelColumn(columnName="remark",name="备注")
	@ExcelColumn(columnName="props.last_active_time",name="上次在线时间")
	@ExcelColumn(columnName="props.disk_capacity",name="磁盘容量")
	@ExcelColumn(columnName="props.status_comment",name="状态变更说明")
	@ExcelColumn(columnName="device_detail.device_name",name="产品名称")
	@ExcelColumn(columnName="device_detail.manufacturer",name="制造商")
	@ExcelColumn(columnName="device_detail.made_date",name="生产日期")
	/** 
	 * 这里必须指定produces,否则swagger下载的excel文件不正确,
	 * 参见 <a href="https://blog.csdn.net/iuie_sl/article/details/114904170 ">《使用swagger api 下载excel，excel打不开》</a>
	 */
	@ApiOperation(value = "设备表导出EXCEL", notes = "设备表导出EXCEL",httpMethod="GET",produces="application/octet-stream,application/json")
    @RequestMapping(method=RequestMethod.GET,produces={"application/octet-stream","application/json"})
	public List<DeviceBean> exportDevices(Integer getParameter, String exportFileName, 
			String sheetName, 
			String title, String titleFontName, Integer titleFontHeight, IndexedColors titleFontColor, IndexedColors titleFillColor, HorizontalAlignment titleHorizontalAlign, 
			String headerFontName, Integer headerFontHeight, IndexedColors headerFontColor, IndexedColors headerFillColor, HorizontalAlignment headerHorizontalAlign, Boolean firstBold, 
			Integer fontHeight, String fontName, IndexedColors fontColor, IndexedColors fillColor, HorizontalAlignment horizontalAlign, 
			String integralFormat, String dateTimeFormat, String dateFormat, String timeFormat, String timestampFormat, 
			List<String> includeColumns, List<String> excludeColumns) {
		return instanceOf(IDeviceManager.class).loadAllAsList();
	}

	/** 
	 * 设备表导出EXCEL<br>
	 *  查询参数说明:
	 * <ul>
	 * <li>orderBy 排序字段名,格式:${字段1}[DESC|ASC]...,${字段N}[DESC|ASC]), 默认排序字段:id</li>
	 * <li>provinces        ----不为null时过滤设备所属顶级设备组的省份名称</li>
	 * <li>city        ----不为null时过滤设备所属顶级设备组的城市名称</li>
	 * <li>group_id 不为null时过滤grup_id(所属设备组id)字段</li>
	 * <li>status 不为null时过滤status(设备状态)字段,可用值:ENABLE:正常,DISABLE:禁用,MAINTAIN:维护,PENDING:挂起(待审核)</li>
	 * <li>address_type 不为null时过滤address_type(设备物理地址类型)字段(),可用值:MAC(网卡MAC地址),IMEI(国际移动设备识别码)</li>
	 * <li>physical_address 不为null时过滤physical_address(设备物理地址)字段,模糊匹配所有physical_address字段包含${physical_address}的记录</li>
	 * <li>iot_card 不为null时过滤iot_card(设备物联网卡编号)字段,模糊匹配所有iot_card字段包含${iot_card}的记录</li>
	 * <li>screen_info 不为null时过滤screen_info(设备屏幕信息)字段,模糊匹配所有screen_info字段包含${screen_info}的记录,格式示例:15H1080x960--15(英)寸横屏分辨率1080x960</li>
	 * <li>os_arch 不为null时过滤os_arch(操作系统名称及版本及硬件架构名称)字段,模糊匹配所有os_arch字段包含${os_arch}的记录,例如:Windows-x86_64,Linux-x86_64,Android-arm</li>
	 * <li>network   ----不为null时过滤network(设备网络连接类型)字段,目前支持类型:4G,WIFI,ETHERNET</li>
	 * <li>version_info 不为null时过滤version_info(设备端应用程序的版本号)字段,模糊匹配所有version_info字段包含${version_info}的记录</li>
	 * <li>model 不为null时过滤model(设备产品型号)字段,模糊匹配所有model字段包含${model}的记录</li>
	 * <li>vendor 不为null时过滤vendor(设备供应商)字段,模糊匹配所有vendor字段包含${vendor}的记录</li>
	 * <li>name 不为null时过滤设备名称字段,模糊匹配所有name字段包含${name}的记录</li>
	 * <li>create_time_max 不为null时过滤create_time(设备记录创建时间字段),查询所有create_time早于create_time_max的记录</li>
	 * <li>create_time_min 不为null时过滤create_time字段(设备记录创建时间),查询所有create_time晚于create_time_max的记录</li>
	 * <li>update_time_max 不为null时过滤update_time(设备记录更新时间字段),查询所有update_time早于update_time_max的记录</li>
	 * <li>update_time_min 不为null时过滤update_time(设备记录更新时间字段),查询所有update_time晚于update_time_max的记录</li>
	 * </ul>
	 */
	@ExcelSheet(title="设备表记录",
			fileNamePrefix = "device_",
			hideColumns={"props","token_time","device_detail"},
			defaultIncludeColumns={"id","provinces","city","top_group_name","name",
					"physicalAddress","addressType","iotCard","network","screenInfo","fixedMode","model","osArch","versionInfo",
					"status","props.disk_capacity","props.last_active_time","createTime","remark"})
	@ExcelColumn(columnName="physicalAddress",name="物理地址")
	@ExcelColumn(columnName="addressType",name="地址类型")
	@ExcelColumn(columnName="iotCard",name="物联网卡编号")
	@ExcelColumn(columnName="status",name="设备状态",readConverterExp="ENABLE=正常,DISABLE=禁用,MAINTAIN=维护,PENDING=挂起(待审核)")
	@ExcelColumn(columnName="fixedMode",name="安装方式",readConverterExp="HANG=悬挂,FLOOR=落地")
	@ExcelColumn(columnName="name",name="设备名称")
	@ExcelColumn(columnName="groupId",name="设备组ID")
	@ExcelColumn(columnName="provinces",name="省/自治区/直辖市")
	@ExcelColumn(columnName="city",name="市")
	@ExcelColumn(columnName="top_group_name",name="设备组名称")
	@ExcelColumn(columnName="screenInfo",name="屏幕信息")
	@ExcelColumn(columnName="name",name="设备名称")
	@ExcelColumn(columnName="model",name="设备型号")
	@ExcelColumn(columnName="vendor",name="设备供应商")
	@ExcelColumn(columnName="osArch",name="操作系统平台")
	@ExcelColumn(columnName="network",name="网络连接类型")
	@ExcelColumn(columnName="versionInfo",name="版本")
	@ExcelColumn(columnName="planId",name="当前节目ID")
	@ExcelColumn(columnName="targetId",name="目标节目ID")
	@ExcelColumn(columnName="createTime",name="记录创建时间")
	@ExcelColumn(columnName="updateTime",name="记录修改时间")
	@ExcelColumn(columnName="remark",name="备注")
	@ExcelColumn(columnName="props.last_active_time",name="上次在线时间")
	@ExcelColumn(columnName="props.disk_capacity",name="磁盘容量")
	@ExcelColumn(columnName="props.status_comment",name="状态变更说明")
	@ExcelColumn(columnName="device_detail.device_name",name="产品名称")
	@ExcelColumn(columnName="device_detail.manufacturer",name="制造商")
	@ExcelColumn(columnName="device_detail.made_date",name="生产日期")
	/** 启用WhereHelper动态生成SQL语句 */
	@EnableWhereHelper(/* 设置调试输出 */debuglog=false)
	/** 定义WhereHelper生成SQL语句的表达式 */
	@OrderBy("id")
	@Equal("group_id")
	@Equal("status")
	@Equal("address_type")
	@Equal("network")
	@IfElse(test="!isEmpty(${provinces})",doStatement=" dgrp.provinces = '${provinces}'")
	@IfElse(test="!isEmpty(${city})",doStatement=" dgrp.city = '${city}'")
	@IfElse(test="!isEmpty(${name})",doStatement=" dc_device.name like '%${name}%'")
	@IfElse(test="!isEmpty(${physical_address})",doStatement=" physical_address like '%${physical_address}%'")
	@IfElse(test="!isEmpty(${iot_card})",doStatement=" iot_card like '%${iot_card}%'")
	@IfElse(test="!isEmpty(${screen_info})",doStatement=" screen_info like '%${screen_info}%'")
	@IfElse(test="!isEmpty(${os_arch})",doStatement=" os_arch like '%${os_arch}%'")
	@IfElse(test="!isEmpty(${version_info})",doStatement=" version_info like '%${version_info}%'")
	@IfElse(test="!isEmpty(${model})",doStatement=" model like '%${model}%'")
	@IfElse(test="!isEmpty(${vendor})",doStatement=" model like '%${vendor}%'")
	@IfElse(test="!isEmpty(${create_time_max})",doStatement=" dc_device.create_time <= '${create_time_max}'")
	@IfElse(test="!isEmpty(${create_time_min})",doStatement=" dc_device.create_time >= '${create_time_min}'")
	@IfElse(test="!isEmpty(${update_time_max})",doStatement=" dc_device.update_time <= '${update_time_max}'")
	@IfElse(test="!isEmpty(${update_time_min})",doStatement=" dc_device.update_time >= '${update_time_min}'")
	@Expression("dc_device.name !='$DUMMY_DEVICE_DONOT_DELETE$'")
	/** 
	 * 这里必须指定produces,否则swagger下载的excel文件不正确,
	 * 参见 <a href="https://blog.csdn.net/iuie_sl/article/details/114904170 ">《使用swagger api 下载excel，excel打不开》</a>
	 */
	@ApiOperation(value = "设备表导出EXCEL", notes = "设备表导出EXCEL",httpMethod="GET",produces="application/octet-stream,application/json")
    @RequestMapping(method=RequestMethod.GET,produces={"application/octet-stream","application/json"})
	public List<DeviceBean> exportDevices(Integer getParameter, String exportFileName, 
			String sheetName, 
			String title, String titleFontName, Integer titleFontHeight, IndexedColors titleFontColor, IndexedColors titleFillColor, HorizontalAlignment titleHorizontalAlign, 
			String headerFontName, Integer headerFontHeight, IndexedColors headerFontColor, IndexedColors headerFillColor, HorizontalAlignment headerHorizontalAlign, Boolean firstBold, 
			Integer fontHeight, String fontName, IndexedColors fontColor, IndexedColors fillColor, HorizontalAlignment horizontalAlign, 
			String integralFormat, String dateTimeFormat, String dateFormat, String timeFormat, String timestampFormat, 
			List<String> includeColumns, List<String> excludeColumns) {
		return instanceOf(IDeviceManager.class).loadByWhereAsList(PageHelper.getWhere());
	}

    @RequestMapping(method=RequestMethod.GET,produces={MediaType.APPLICATION_OCTET_STREAM_VALUE,MediaType.APPLICATION_JSON_VALUE})
	public List<JSONObject> exportDevices() {
        /** 从数据库读取的记录类型为sql2java生成的 DeviceBean,包含了@ExcelColumn,@ExcelSheet注解 */
		List<DeviceBean> beans = dm.daoLoadDeviceByJoinWhere("JOIN dc_device_group AS dgrp ON group_id = dgrp.id",PageHelper.getWhere());
        /** 将DeviceBean对象转换成了JSONObject,并根据需要添加了一些新的字段,服务方法返回的类型为JSONObject*/
		List<JSONObject> maps = Lists.transform(beans,l->dm.formatAsJson(l, dm.deviceFormater, false));
		return maps;

	}

    @RequestMapping(method=RequestMethod.GET,produces={MediaType.APPLICATION_OCTET_STREAM_VALUE,MediaType.APPLICATION_JSON_VALUE})
	public List<JSONObject> exportDevices() {
		List<DeviceBean> beans = dm.daoLoadDeviceByJoinWhere("JOIN dc_device_group AS dgrp ON group_id = dgrp.id",PageHelper.getWhere());
		List<JSONObject> maps = Lists.transform(beans,l->dm.formatAsJson(l, dm.deviceFormater, false));
		/** 
		 * 服务方法的返回类型为JSONObject,无法将 DeviceBean 中@ExcelSheet,@ExcelColumn注解传递ExcelHelperAround,
		 * 所以这里通过调用 ExcelHelperAround#setBeanClass 方法告诉 ExcelHelperAround对象数据记录的原始的数据类型,
		 * 这样ExcelHelperAround就可以通过该类型获取 JSON 字段的Excel配置参数
		 */
		ExcelHelperAround.setBeanClass(DeviceBean.class);
		return maps;
	}