本文主要是介绍Jxls 实现动态导出功能，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
目录
- 引言
- 前端页面
- 后端代码
- excel模板
- 导出效果
引言
在实际做项目的过程中，导出报表时需要根据每个人所关注的点不一样，所需导出的字段也不一样，这时后端就需要根据每个所选的字段去相应的报表，这就是本文要讲的动态导出报表。
前端页面
- 用户在页面上选择要导出的字段，后端根据所选的字段进行导出
- 将要导出的所有字段做成字典进行管理方便后端进行转换，具体思路请看后端代码
后端代码
- 请求参数实体 OutOrderParam.java
package com.hw.admin.domain.outer.vo;import com.alibaba.fastjson.JSONObject;
import lombok.Data;import java.util.Date;
import java.util.List;/**1. @Description2. @Author liqinglong3. @DateTime 2022-03-30 19:054. @Version 1.0*/
@Data
public class OutOrderParam {/** 搜索关键字 */private String searchValue;/** 出库单状态 */private Integer status;/** 创建日期 */private Date createDate;/** 扫描条码 */private String scanBarcode;/** 导出字段 */private JSONObject fields;private Integer pageSize;private Integer startIndex;private List<String> exportFields;
}
- controller方法
/*** 出库单导出** @param response* @param request*/@Log(title = "出库单导出", businessType = BusinessType.EXPORT)@GetMapping("exportOutOrderXls")public void downStockExcel(HttpServletResponse response,HttpServletRequest request,OutOrderParam param) throws IOException, NoSuchFieldException, IllegalAccessException {String fileFullName = airOutOrderService.exportOutOrderXls(param);String fileName = fileFullName.split("\\|")[1];FileUtils.downloadFile(fileFullName.split("\\|")[0], fileName, response, request);}
- 服务层方法
接口 IOutOrderService.java
/*** 出库单服务接口* @author liql* @date 2022-03-30 16:29:15*/
public interface IOutOrderService extends IBaseService<OutOrder> {/*** @Description 服务层导出出库单方法* @param param* @return java.lang.String*/
String exportOutOrderXls(OutOrderParam param) throws NoSuchFieldException, IllegalAccessException;
}
实现类 OutOrderServiceImpl.java
Slf4j
@Service
public class OutOrderServiceImpl extends IBaseServiceImpl<OutOrderMapper, AirOutOrder> implements IOutOrderService {@Overridepublic String exportOutOrderXls(OutOrderParam param) throws NoSuchFieldException, IllegalAccessException {log.info("开始执行导出出库单，返回参数：{}",param);//1、获取参数-导出数据限制条数SysConfig config = new SysConfig();config.setConfigKey("export.excel.count");SysConfig retConfig = configMapper.selectConfig(config);int exportCount = Integer.parseInt(retConfig.getConfigValue());//2、获取导出记录总条数int total = mapper.countOutTotal(param);//3、导出的总条数不能超过限制的总条数total = total > exportCount ? exportCount : total;//4、获取选取的字段，默认导出所有字段List<String> fieldsList = param.getExportFields();//获取字典字段列表List<SysDictData> outFieldList = sysDictTypeService.selectDictDataByType("out_field_list");JSONObject paramObject = new JSONObject();//表头List<String> headerList = new ArrayList();if(ObjectUtils.isEmpty(fieldsList)){fieldsList = new ArrayList<>();for (SysDictData dicData:outFieldList) {paramObject.put(dicData.getDictValue(),1);headerList.add(dicData.getDictLabel());fieldsList.add(dicData.getDictValue());}}else{for (String field: fieldsList) {paramObject.put(field,1);for (SysDictData dicData:outFieldList) {if(field.equals(dicData.getDictValue())){headerList.add(dicData.getDictLabel());break;}}}}param.setFields(paramObject);//5、获取数据字典转换字段//出库状态Map<String,String> statusDictMap = new HashMap();List<SysDictData> statusDictDatas = sysDictTypeService.selectDictDataByType("outbound_status");for(SysDictData dictData : statusDictDatas){statusDictMap.put(dictData.getDictValue(),dictData.getDictLabel());}//出库类型Map<String,String> outTypeMap = new HashMap();List<SysDictData> outTypeDatas = sysDictTypeService.selectDictDataByType("outbound_type");for(SysDictData dictData : outTypeDatas){outTypeMap.put(dictData.getDictValue(),dictData.getDictLabel());}//6、计算分页查询次数//每次查询条数int pageSize = 1000;int totalPage = (total / pageSize) + (total % pageSize > 0 ? 1 : 0);//7、循环查询数据//excel表实际上是一个二维表List<List<Object>> lastResult = new ArrayList<>();param.setPageSize(pageSize);for(int i = 0;i < totalPage;i++){param.setStartIndex(i * pageSize);List<ExportOutOrderVo> outOrderList = mapper.queryExportOutOrderList(param);for(ExportOutOrderVo orderVo:outOrderList){// 出库类型转化为中文orderVo.setOutTypeStr(outTypeMap.get(String.valueOf(orderVo.getOutType())));// 出库状态转化为中文orderVo.setStatusStr(statusDictMap.get(String.valueOf(orderVo.getStatus())));//excel中的一行数据List<Object> rowList = new ArrayList<>();for (String header:fieldsList){Field field = orderVo.getClass().getDeclaredField(header);field.setAccessible(true);if("tabId".equals(header)){//将长整型转化为字符串rowList.add(String.valueOf(field.get(orderVo)));}else if("outTime".equals(header)){//将出库时间格式化Date outTime = (Date) field.get(orderVo);if(ObjectUtils.isEmpty(field.get(orderVo))){rowList.add(field.get(orderVo));}else{rowList.add(DateUtils.formatDate(outTime,"yyyy-MM-dd HH:mm:ss"));}}else{rowList.add(field.get(orderVo));}}lastResult.add(rowList);}}//8、生成exelMap<String, Object> model = new HashMap<>();model.put("cols",headerList);model.put("orders", lastResult);String xlsName = "出库单_"+ DateUtils.formatDate(new Date(),"yyyyMMddHHmmss") + ".xlsx";String filePath = "";String geneXlsName = "";try {String orderFileNameTemp = "exportOutOrder.xlsx";try {filePath = new File(ResourceUtils.getURL("classpath:").getPath()).getParentFile().getParentFile().getParent();filePath += File.separator + "report_file" + File.separator;} catch (FileNotFoundException e) {log.error("执行导出出库单，系统异常：" + e);e.printStackTrace();}File exportFilePath = new File(filePath);if (exportFilePath.exists() == false) {exportFilePath.mkdirs();}geneXlsName = filePath + xlsName;JxlsUtils.geneExcel(orderFileNameTemp, geneXlsName, model);} catch (IOException e) {e.printStackTrace();}log.info("结束执行导出出库单，返回参数：" + geneXlsName);return geneXlsName + "|" + xlsName;}}
mapper OrderInfoSpeMapper.java
public interface OrderInfoSpeMapper extends BaseMapper<OrderInfo> {/*** 查询订单导出列表** @param queryVo 订单查询信息* @return 订单导出列表*/List<OrderExportResultVo> selectOrderListExport(OrderQueryVo queryVo);}
xml OrderInfoSpeMapper.xml
<resultMap type="com.hw.admin.domain.order.vo.OrderExportResultVo" id="OrderExportResult"></resultMap><!-- 查询订单导出列表 --><select id="selectOrderListExport" parameterType="com.hw.admin.domain.order.vo.OrderQueryVo" resultMap="OrderExportResult">SELECTo.id,o.cust_name,o.order_status,o.order_type,o.create_time,o.oper_name,b.address,d.logistics_no,e.id_number,e.contact_phone,f.mat_code,f.goods_name,f.unit_name,sum(f.sale_volume) sale_volume,sum(f.sale_amount) sale_amountFROMair_order_info oLEFT JOIN air_out_order d ON d.tab_id = o.idleft join air_order_receive b on b.order_id = o.idleft join air_customer e on e.id = o.cust_idleft join air_order_goods f on f.order_id = o.id<where>o.del_flag = 0and o.source_type = 0AND o.order_type in (0,1)<if test="searchValue != null and searchValue != ''">AND (o.order_name like concat('%', #{searchValue}, '%')OR o.cust_name like concat('%', #{searchValue}, '%'))</if><if test="beginTime != null and beginTime != ''">AND o.create_time >= STR_TO_DATE(#{beginTime}, '%Y-%m-%d %H:%i:%s')</if><if test="endTime != null and endTime != ''">AND o.create_time <= STR_TO_DATE(#{endTime}, '%Y-%m-%d %H:%i:%s')</if><if test="bottleCode != null and bottleCode != ''">AND exists (select 1from air_out_order_detail e,air_out_good_record fwhere e.order_id = d.idand f.order_detail_id = e.idAND (f.scan_bottlecode like concat('%', #{bottleCode}, '%') OR f.scan_barcode like concat('%', #{bottleCode}, '%')))</if></where>group byo.id,o.cust_name,o.order_status,o.order_type,o.create_time,o.oper_name,b.address,d.logistics_no,e.id_number,e.contact_phone,f.mat_code,f.goods_name,f.unit_nameorder by o.id desc<if test="indexPage != null">LIMIT #{indexPage}, #{sizePage}</if></select>
vo ExportOutOrderVo.java
package com.hw.admin.domain.order.vo;import com.fasterxml.jackson.annotation.JsonFormat;
import com.hw.common.annotation.SensitiveEntity;
import com.hw.common.annotation.SensitiveField;
import lombok.Data;import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;/*** 功能描述: 导出结果信息** @author: liqinglong* @date: 2023/10/9* */
@Data
public class OrderExportResultVo implements Serializable {private static final long serialVersionUID = 1L;/*** 订单id*/private Long id;/*** 订单id*/private String idDesc;/*** 客户名称*/private String custName;/*** 订单状态: 0-审核中 1-待付款 2-待发货 3-待收货 4-已完成 5-售后 6-已关闭 7-酒业出库 8-物流出库*/private Integer orderStatus;/*** 订单状态: 0-审核中 1-待付款 2-待发货 3-待收货 4-已完成 5-售后 6-已关闭 7-出库 8-物流出库*/private String orderStatusDesc;/*** 订单类型 0-普通 1-换货销售*/private Integer orderType;/*** 订单类型 0-普通 1-换货销售*/private String orderTypeDesc;/*** 创建时间*/@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")private Date createTime;/*** 创建时间*/private String createTimeDesc;/*** 操作员姓名*/private String operName;/*** 收货地址*/private String address;/*** 运单号*/private String logisticsNo;/*** 身份证号码*/@SensitiveFieldprivate String idNumber;/*** 联系人电话*/@SensitiveFieldprivate String contactPhone;/*** 物料编码*/private String matCode;/*** 商品名称，多个商品名称合并，如飞天茅台等产品*/private String goodsName;/*** 商品单位*/private String unitName;/*** 商品销售数量*/private Long saleVolume;/*** 销售金额*/private BigDecimal saleAmount;/*** 扫描物流条码*/private String scanBarcode;/*** 扫描物流瓶码*/private String scanBottlecode;
}
excel模板
A1的注解
jx:area(lastCell="A3") //区域范围
jx:mergeCells(cols="cols.size()" lastCell="A1") //标题合并居中A2的注解
jx:grid(lastCell="A3" headers="cols" data="orders" areas=["A2:A2,"A3:A3"])
注意：headers 设置为代码中的key :“cols”, data 设置为代码中的key:“orders”
导出效果
测试数据，仅供参考
这篇关于Jxls 实现动态导出功能的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
