数据库表导出到excel

数据库表导出到excel:前置知识1 ALL_TAB_COLS
数据库表导出到excel:前置知识2 Quartz基本使用
数据库表导出到excel:前置知识3 项目封装的Quartz实现动态定时任务
数据库表导出到excel:前置知识4 业务和效果

	发起清单下载

control层InventoryDownloadLogController

/*
* 
*/
package com.njry.sjzl.busi.rest;import com.njry.annotation.Log;
import com.njry.sjzl.busi.domain.InventoryDownloadLog;
import com.njry.sjzl.busi.service.InventoryDownloadLogService;
import com.njry.sjzl.busi.domain.vo.InventoryDownloadLogQueryCriteria;
import lombok.RequiredArgsConstructor;
import java.util.List;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;
import io.swagger.annotations.*;
import java.io.IOException;
import javax.servlet.http.HttpServletResponse;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.njry.utils.PageResult;/**
* @author wj
* @date 2024-06-20
**/
@RestController
@RequiredArgsConstructor
@Api(tags = "清单任务下载表管理")
@RequestMapping("/api/inventoryDownloadLog")
public class InventoryDownloadLogController {private final InventoryDownloadLogService inventoryDownloadLogService;@Log("导出数据")@ApiOperation("导出数据")@GetMapping(value = "/download")@PreAuthorize("@el.check('inventoryDownloadLog:list')")public void exportInventoryDownloadLog(HttpServletResponse response, InventoryDownloadLogQueryCriteria criteria) throws IOException {inventoryDownloadLogService.download(inventoryDownloadLogService.queryAll(criteria), response);}@Log("下载文件")@ApiOperation("下载文件")@GetMapping(value = "/downloadFile")@PreAuthorize("@el.check('inventoryDownloadLog:list')")public void exportInventoryDownloadLogFile(HttpServletResponse response, InventoryDownloadLogQueryCriteria criteria) throws IOException {String attachmentId = criteria.getAttachmentId();inventoryDownloadLogService.downloadFile(attachmentId, response);}@GetMapping@Log("查询清单任务下载日志表")@ApiOperation("查询清单任务下载日志表")@PreAuthorize("@el.check('inventoryDownloadLog:list')")public ResponseEntity<PageResult<InventoryDownloadLog>> queryInventoryDownloadLog(InventoryDownloadLogQueryCriteria criteria, Page<Object> page){return new ResponseEntity<>(inventoryDownloadLogService.queryAll(criteria,page),HttpStatus.OK);}@PostMapping@Log("新增清单任务下载日志表--发起-----------调度任务异步把table数据变成文件")@ApiOperation("新增清单任务下载日志表--发起-----------调度任务异步把table数据变成文件")@PreAuthorize("@el.check('inventoryDownloadLog:add')")public ResponseEntity<Object> createInventoryDownloadLog(@Validated @RequestBody InventoryDownloadLog resources){inventoryDownloadLogService.create(resources);return new ResponseEntity<>(HttpStatus.CREATED);}//    @PutMapping
//    @Log("修改清单任务下载日志表")
//    @ApiOperation("修改清单任务下载日志表")
//    @PreAuthorize("@el.check('inventoryDownloadLog:edit')")
//    public ResponseEntity<Object> updateInventoryDownloadLog(@Validated @RequestBody InventoryDownloadLog resources){
//        inventoryDownloadLogService.update(resources);
//        return new ResponseEntity<>(HttpStatus.NO_CONTENT);
//    }@DeleteMapping@Log("删除清单任务下载日志表")@ApiOperation("删除清单任务下载日志表")@PreAuthorize("@el.check('inventoryDownloadLog:del')")public ResponseEntity<Object> deleteInventoryDownloadLog(@RequestBody List<Long> ids) {inventoryDownloadLogService.deleteAll(ids);return new ResponseEntity<>(HttpStatus.OK);}
}

service层

/*
*
*/
package com.njry.sjzl.busi.service;import com.njry.sjzl.busi.domain.InventoryDownloadLog;
import com.njry.sjzl.busi.domain.vo.InventoryDownloadLogQueryCriteria;
import java.util.Map;
import java.util.List;
import java.io.IOException;
import javax.servlet.http.HttpServletResponse;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.IService;
import com.njry.utils.PageResult;/**
* @description 服务接口
* @author wj
* @date 2024-06-20
**/
public interface InventoryDownloadLogService extends IService<InventoryDownloadLog> {/*** 查询数据分页* @param criteria 条件* @param page 分页参数* @return PageResult*/PageResult<InventoryDownloadLog> queryAll(InventoryDownloadLogQueryCriteria criteria, Page<Object> page);/*** 查询所有数据不分页* @param criteria 条件参数* @return List<InventoryDownloadLogDto>*/List<InventoryDownloadLog> queryAll(InventoryDownloadLogQueryCriteria criteria);/*** 创建* @param resources /*/void create(InventoryDownloadLog resources);//    /**
//    * 编辑
//    * @param resources /
//    */
//    void update(InventoryDownloadLog resources);/*** 多选删除* @param ids /*/void deleteAll(List<Long> ids);/*** 导出数据* @param all 待导出的数据* @param response /* @throws IOException /*/void download(List<InventoryDownloadLog> all, HttpServletResponse response) throws IOException;/*** 导出文件* @param attachmentId 文件名* @param response /* @throws IOException /*/void downloadFile(String attachmentId, HttpServletResponse response) throws IOException;}

实现层impl(主要看create方法)

/*
*
*/
package com.njry.sjzl.busi.service.impl;import cn.hutool.core.date.DateTime;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.njry.config.FileProperties;
import com.njry.sjzl.busi.attachment.domain.Attachment;
import com.njry.sjzl.busi.attachment.domain.vo.AttachmentQueryCriteria;
import com.njry.sjzl.busi.attachment.mapper.AttachmentMapper;
import com.njry.sjzl.busi.domain.Inventory;
import com.njry.sjzl.busi.domain.InventoryAuth;
import com.njry.sjzl.busi.domain.InventoryDownloadLog;
import com.njry.sjzl.busi.domain.vo.InventoryQueryCriteria;
import com.njry.sjzl.busi.mapper.AtomBusiCategoryMapper;
import com.njry.sjzl.busi.mapper.InventoryAuthMapper;
import com.njry.sjzl.busi.mapper.InventoryMapper;
import com.njry.sjzl.busi.task.GenerateExcelTask;
import com.njry.sjzl.busi.task.vo.TransferVo;
import com.njry.utils.*;
import lombok.RequiredArgsConstructor;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.njry.sjzl.busi.service.InventoryDownloadLogService;
import com.njry.sjzl.busi.domain.vo.InventoryDownloadLogQueryCriteria;
import com.njry.sjzl.busi.mapper.InventoryDownloadLogMapper;
import org.apache.poi.util.IOUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;import java.io.File;
import java.io.FileInputStream;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.*;
import java.io.IOException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;/**
* @description 服务实现
* @author wj
* @date 2024-06-20
**/
@Service
@RequiredArgsConstructor
public class InventoryDownloadLogServiceImpl extends ServiceImpl<InventoryDownloadLogMapper, InventoryDownloadLog> implements InventoryDownloadLogService {private final InventoryDownloadLogMapper inventoryDownloadLogMapper;private final InventoryMapper inventoryMapper;private final AttachmentMapper attachmentMapper;private final FileProperties properties;//    递归查询所属资产private final AtomBusiCategoryMapper atomBusiCategoryMapper;@Overridepublic PageResult<InventoryDownloadLog> queryAll(InventoryDownloadLogQueryCriteria criteria, Page<Object> page){IPage<InventoryDownloadLog> all = inventoryDownloadLogMapper.findAll(criteria, page);List<InventoryDownloadLog> records = all.getRecords();//        根据每条数据类的busiSort递归向下查找归属业务分类(回显)if(records.size() > 0 ){for (int i = 0; i < records.size(); i++) {Set<String> taskSetResult  = new LinkedHashSet<>();Long categoryId = records.get(i).getCategoryId();if(categoryId != null){List<String> subCategory = atomBusiCategoryMapper.findSubCategory(categoryId);String currentCategoryName = atomBusiCategoryMapper.findCategoryNameByCateforyId(categoryId);taskSetResult.addAll(subCategory);taskSetResult.add(currentCategoryName);String temp = "";for(String item : taskSetResult){temp += ","+item;}String result = temp.substring(1);records.get(i).setCategoryName(result);}}}return PageUtil.toPage(all);}@Overridepublic List<InventoryDownloadLog> queryAll(InventoryDownloadLogQueryCriteria criteria){return inventoryDownloadLogMapper.findAll(criteria);}@Override@Transactional(rollbackFor = Exception.class)public void create(InventoryDownloadLog resources) {
//        前端发起只传inventoryId,别的基本数据这里查完赋值Long inventoryId = resources.getInventoryId();Inventory inventory = inventoryMapper.queryDetailByInventoryId(inventoryId);resources.setInventoryName(inventory.getInventoryName());resources.setCategoryId(inventory.getCategoryId());//        新增的时候把操作人和操作时间Timestamp timestamp = DateTime.now().toTimestamp();
//        String username = "System";
//        try {username = SecurityUtils.getCurrentUsername();}catch (Exception ignored){}
//        操作人resources.setOperId(SecurityUtils.getCurrentUserId());resources.setOperDate(timestamp);
//        状态：0 待生成  2 正在生成  1 数据生成完成resources.setStatus(2);
//        正在生成没有附件idresources.setAttachmentId("");Long seq = inventoryDownloadLogMapper.getSeq();resources.setId(seq);save(resources);
//        异步生成文件(查询数据转成excel文件)Long currentUserId = SecurityUtils.getCurrentUserId();//        获取系统定义的文件路径String dirPath = properties.getPath().getInventory();//系统文件路径下inventoryString separator = properties.getPath().getSeparator();//获取系统分隔符
//        /inventory/日期/Date currentDate = new Date();SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM");String currentYearMonth = formatter.format(currentDate);System.out.println(currentYearMonth);dirPath += currentYearMonth + separator;//        生成需要的参数加入队列TransferVo transferVo = new TransferVo();transferVo.setDirPath(dirPath);transferVo.setFileSuffix("xlsx");transferVo.setSeq(seq);transferVo.setInventoryId(inventoryId);transferVo.setSeparator(separator);
//        transferVo.setTableHead(tableHead);//费时间,放在异步里面
//        transferVo.setMaps(maps);//费时间,放在异步里面transferVo.setCurrentUserId(currentUserId);GenerateExcelTask.addDataList(transferVo);}//    @Override
//    @Transactional(rollbackFor = Exception.class)
//    public void update(InventoryDownloadLog resources) {
//        InventoryDownloadLog inventoryDownloadLog = getById(resources.getId());
//        inventoryDownloadLog.copy(resources);
//        saveOrUpdate(inventoryDownloadLog);
//    }@Override@Transactional(rollbackFor = Exception.class)public void deleteAll(List<Long> ids) {removeBatchByIds(ids);}@Overridepublic void download(List<InventoryDownloadLog> all, HttpServletResponse response) throws IOException {List<Map<String, Object>> list = new ArrayList<>();for (InventoryDownloadLog inventoryDownloadLog : all) {Map<String,Object> map = new LinkedHashMap<>();map.put("唯一标识  序列", inventoryDownloadLog.getLogId());map.put("清单ID", inventoryDownloadLog.getInventoryId());map.put("清单名称", inventoryDownloadLog.getInventoryName());map.put("归属业务类别   T_atom_busi_category.CATEGORY_ID", inventoryDownloadLog.getCategoryId());map.put("操作人", inventoryDownloadLog.getOperId());map.put("操作时间", inventoryDownloadLog.getOperDate());map.put("状态：0 发起任务  2 正在生成  1 数据生成完成", inventoryDownloadLog.getStatus());map.put("附件编号", inventoryDownloadLog.getAttachmentId());map.put("提醒手机号码", inventoryDownloadLog.getMobile());list.add(map);}FileUtil.downloadExcel(list, response);}//        将文件转成响应流给前端@Overridepublic void downloadFile(String attachmentId, HttpServletResponse response) throws IOException {
//        根据文件id查找文件所在位置   文件名String fileName = "";String dirPath = "";String separator = properties.getPath().getSeparator();//获取系统分隔符AttachmentQueryCriteria attachmentQueryCriteria = new AttachmentQueryCriteria();attachmentQueryCriteria.setAttachmentId(attachmentId);Attachment attachment = attachmentMapper.queryAllWithCondition(attachmentQueryCriteria);if(attachment != null){dirPath = attachment.getAttachmentPath();fileName = attachment.getFileName();}
//        因为保存的时候是在默认路径下加一个watermarkPath文件夹下生成同名的文件,这里取得时候也得取水印的文件String watermarkPath = dirPath + "watermarkPath" + separator;FileInputStream watermarkFileInput = null;//response为HttpServletResponse对象response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");//test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码response.setHeader("Content-Disposition", "attachment;filename=file.xlsx");ServletOutputStream out = response.getOutputStream();File watermarkFile = new File(watermarkPath, fileName);watermarkFileInput = new FileInputStream(watermarkFile);IOUtils.copy(watermarkFileInput,out);watermarkFile.deleteOnExit();watermarkFileInput.close();}
}

生成的文件位置

/** */
package com.njry.config;import lombok.Data;
import com.njry.utils.ElConstant;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;/*** @author njry*/
@Data
@Configuration
@ConfigurationProperties(prefix = "file")
public class FileProperties {/** 文件大小限制 */private Long maxSize;/** 头像大小限制 */private Long avatarMaxSize;private ElPath mac;private ElPath linux;private ElPath windows;public ElPath getPath(){
//        System.getProperty() 方法用于获取系统属性的值String os = System.getProperty("os.name");if(os.toLowerCase().startsWith(ElConstant.WIN)) {return windows;} else if(os.toLowerCase().startsWith(ElConstant.MAC)){return mac;}return linux;}@Datapublic static class ElPath{private String path;private String avatar;private String inventory;private String separator;}
}

配置文件里写

# 文件存储路径
file:mac:path: ~/file/avatar: ~/avatar/inventory: /file/inventory/separator: /linux:path: /home/eladmin/file/avatar: /home/eladmin/avatar/inventory: /eladmin/file/inventory/separator: /windows:path: D:\sjzl\file\avatar: D:\sjzl\avatar\
#    inventory: D:\sjzl\file\inventory\ ## 不设置固定路径,相对路径自动找到项目所在盘位置inventory: \sjzl\file\inventory\separator: \# 文件大小 /MmaxSize: 100avatarMaxSize: 5

mapper层

/*
* 
*/
package com.njry.sjzl.busi.mapper;import com.njry.sjzl.busi.domain.InventoryDownloadLog;
import com.njry.sjzl.busi.domain.vo.InventoryDownloadLogQueryCriteria;
import java.util.List;
import java.util.Map;import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;/**
* @author wj
* @date 2024-06-20
**/
@Mapper
public interface InventoryDownloadLogMapper extends BaseMapper<InventoryDownloadLog> {IPage<InventoryDownloadLog> findAll(@Param("criteria") InventoryDownloadLogQueryCriteria criteria, Page<Object> page);List<InventoryDownloadLog> findAll(@Param("criteria") InventoryDownloadLogQueryCriteria criteria);List<String> findTableHead(@Param("tableName") String tableName,@Param("owner")  String owner);List<Map<String, Object>> commonSql(@Param("selectColumnSql") String selectColumnSql,@Param("tableName") String tableName,@Param("whereSql") String whereSql);Long getSeq();
}

xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.njry.sjzl.busi.mapper.InventoryDownloadLogMapper"><resultMap id="BaseResultMap" type="com.njry.sjzl.busi.domain.InventoryDownloadLog"><result column="LOG_ID" property="logId"/><result column="LOG_ID" property="id"/><result column="INVENTORY_ID" property="inventoryId"/><result column="INVENTORY_NAME" property="inventoryName"/><result column="CATEGORY_ID" property="categoryId"/><result column="OPER_ID" property="operId"/><result column="OPER_NAME" property="operName"/><result column="OPER_DATE" property="operDate"/><result column="STATUS" property="status"/><result column="ATTACHMENT_ID" property="attachmentId"/><result column="MOBILE" property="mobile"/></resultMap><sql id="Base_Column_List">LOG_ID, INVENTORY_ID, INVENTORY_NAME, CATEGORY_ID, OPER_ID, OPER_DATE, STATUS, ATTACHMENT_ID, MOBILE</sql><sql id="Prefix_Base_Column_List">tidl.LOG_ID, tidl.INVENTORY_ID, tidl.INVENTORY_NAME, tidl.CATEGORY_ID, tidl.OPER_ID, tidl.OPER_DATE, tidl.STATUS, tidl.ATTACHMENT_ID, tidl.MOBILE</sql><select id="findAll" resultMap="BaseResultMap">select tuser.name OPER_NAME,<include refid="Prefix_Base_Column_List"/>from T_INVENTORY_DOWNLOAD_LOG tidlleft join T_USER tuser on tuser.user_id = tidl.OPER_ID<where><if test="criteria.inventoryId != null">and tidl.INVENTORY_ID = #{criteria.inventoryId}</if><if test="criteria.inventoryName != null">and tidl.INVENTORY_NAME like concat('%'||#{criteria.inventoryName},'%')</if><if test="criteria.status != null">and tidl.STATUS = #{criteria.status}</if><if test="criteria.mobile != null">and tidl.MOBILE = #{criteria.mobile}</if></where></select><select id="findTableHead" resultType="java.lang.String">select a.column_name from all_tab_cols awhere a.table_name=upper(#{tableName}) and owner=#{owner} order by a.column_id</select><select id="commonSql" resultType="java.util.Map">select ${selectColumnSql} from ${tableName}<if test="whereSql != ''">where ${whereSql}</if></select><select id="getSeq" resultType="java.lang.Long">select  seq_T_INVENTORY_DOWNLOAD_LOG.nextval user_user_id from dual</select>
</mapper>

自己定义了一个任务调度然后可以启动调度

package com.njry.sjzl.busi.task;import cn.hutool.core.date.DateTime;
import com.njry.sjzl.busi.attachment.domain.Attachment;
import com.njry.sjzl.busi.attachment.mapper.AttachmentMapper;
import com.njry.sjzl.busi.domain.Inventory;
import com.njry.sjzl.busi.domain.InventoryAuth;
import com.njry.sjzl.busi.domain.InventoryDownloadLog;
import com.njry.sjzl.busi.mapper.InventoryAuthMapper;
import com.njry.sjzl.busi.mapper.InventoryDownloadLogMapper;
import com.njry.sjzl.busi.mapper.InventoryMapper;
import com.njry.sjzl.busi.task.vo.TransferVo;
import com.njry.utils.GenerateExcelToFile;
import com.njry.utils.SecurityUtils;
import lombok.RequiredArgsConstructor;
import org.springframework.security.core.context.SecurityContext;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Service;import java.io.IOException;
import java.sql.Timestamp;
import java.util.ArrayDeque;
import java.util.List;
import java.util.Map;
import java.util.Queue;@Service
@RequiredArgsConstructor
public class GenerateExcelTask {/*** 定义一个队列*/// 定义一个队列static Queue<TransferVo> queue = new ArrayDeque<>();private final AttachmentMapper attachmentMapper;private final InventoryDownloadLogMapper inventoryDownloadLogMapper;private final InventoryAuthMapper inventoryAuthMapper;private final InventoryMapper inventoryMapper;public static void addDataList(TransferVo transferVo){queue.offer(transferVo);}public static TransferVo pollDataList(){TransferVo poll = queue.poll();// poll 方法用于获取队列头部的元素并移除它return poll;}public void run(){System.out.println("在sjzl-busi下面的bean被任务调度");
//        异步生成exceltry {TransferVo transferVo = GenerateExcelTask.pollDataList();System.out.println("取出一个数据后队列长度:"+queue.size());if(transferVo != null){String dirPath = transferVo.getDirPath();
//                List<Map<String, Object>> maps = transferVo.getMaps();
//                List<String> tableHead = transferVo.getTableHead();String fileSuffix = transferVo.getFileSuffix();Long seq = transferVo.getSeq();Long currentUserId = transferVo.getCurrentUserId();Long inventoryId = transferVo.getInventoryId();String separator = transferVo.getSeparator();
//        根据清单inventoryId和当前用户判断拥有的权限(要不要放where条件)  清单用户权限T_INVENTORY_AUTH
//        AUTH_TYPE  权限类别：1 全量 不放where  2 单元 (清单选择的列名和权限里的单元编号作为where条件)
//        AREA_ID    单元编号InventoryAuth inventoryAuth = inventoryAuthMapper.findByUserIdAndInventoryId(currentUserId, inventoryId);Inventory inventory = inventoryMapper.queryDetailByInventoryId(inventoryId);Integer authType = inventoryAuth.getAuthType();Long areaId = inventoryAuth.getAreaId();String whereSql = "";if(authType == 2){String queryColName = inventory.getQueryColName();whereSql = queryColName + "=" + areaId;}String tableName = inventory.getTableName();
//        tableName = "t_organization";//写死测试
//                tableName = "atom_base_info";//写死测试分sheet
//                whereSql = "";//写死测试(配置的列名称不对)String owner = "NJDATA";//从配置里读取//        获取表的列名-------作为select  xxx,xxx,xxx,xxx from 表名
//        select a.column_name from all_tab_cols a
//        where a.table_name=upper('t_organization') and owner='NJDATA' order by a.column_id;List<String> tableHead = inventoryDownloadLogMapper.findTableHead(tableName, owner);String selectColumnSql = String.join(",",tableHead);//      通用查询sql,传入查询字段,表名和where条件(区分#{}和${}的时候了)List<Map<String, Object>> maps = inventoryDownloadLogMapper.commonSql(selectColumnSql, tableName, whereSql);//             将数据生成excel文件里面Map<String, Object> resultMap = GenerateExcelToFile.dataToExcel(maps, tableHead, dirPath, fileSuffix,separator);Attachment attachment = new Attachment();
//            将文件数据保存文件附件表 T_ATTACHMENTattachment.setAttachmentId(resultMap.get("attachmentId").toString());attachment.setFileName(resultMap.get("fileName").toString());attachment.setFileType(resultMap.get("fileType").toString());attachment.setFileExtension(resultMap.get("fileExtension").toString());attachment.setFileSize(resultMap.get("fileSize").toString());attachment.setAttachmentPath(resultMap.get("attachmentPath").toString());//        新增的时候把操作人和操作时间Timestamp timestampAgain = DateTime.now().toTimestamp();attachment.setOperDate(timestampAgain);//        操作人
//                SecurityContext context = SecurityContextHolder.getContext();
//                System.out.println("SecurityContext:这里可以获取到吗"+context);
//                attachment.setOperId(SecurityUtils.getCurrentUserId());attachment.setOperId(currentUserId);attachmentMapper.insert(attachment);
//            在文件生成记录T_INVENTORY_DOWNLOAD_LOG 修改文件生成状态 和添加附件编号InventoryDownloadLog inventoryDownloadLog = new InventoryDownloadLog();inventoryDownloadLog.setId(seq);inventoryDownloadLog.setAttachmentId(resultMap.get("attachmentId").toString());inventoryDownloadLog.setStatus(1);inventoryDownloadLogMapper.updateById(inventoryDownloadLog);//            发送短信通知用户生成结束}else{
//            这里应该降低任务执行调度的频率System.out.println("暂时没有可以执行的生成文件");}} catch (IOException e) {e.printStackTrace();}}
}

工具类(将数据库数据转成file保存)

package com.njry.utils;import cn.hutool.core.util.IdUtil;
import com.njry.config.FileProperties;
import com.njry.exception.BadRequestException;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;public class GenerateExcelToFile {/**** @param list  数据库查询出来的信息* @param tableHead  数据库的表头(简单表头)* @param dirPath  保存的文件路径* @param fileSuffix 生成的excel格式后缀* @param separator 生成的excel带水印格要多加一层文件路径* @throws IOException*/public static Map<String, Object> dataToExcel(List<Map<String, Object>> list,List<String> tableHead,String dirPath, String fileSuffix, String separator) throws IOException {Map<String, Object> resultMap = new HashMap<>();Workbook wb = null;FileInputStream tempInput = null;String watermarkPath = dirPath + "watermarkPath" + separator;int type = 3;//默认用type区分一个sheet有多少行if("xlsx".equals(fileSuffix)){wb = new XSSFWorkbook();type = 1;}if("xls".equals(fileSuffix)){wb = new HSSFWorkbook();type = 2;}Workbook exportWorkbook = export(wb, list, tableHead, type);String attachmentId = IdUtil.simpleUUID();String fileSize = "";String to_file_name = attachmentId + "." + fileSuffix;	// 结果文件名称
//        判断保存的文件路径是否存在,不存在就创建File outFileExist = new File(dirPath);if(!outFileExist.exists()){outFileExist.mkdirs();}File outFile = new File(dirPath, to_file_name);try {FileOutputStream outStream = new FileOutputStream(outFile);// 写入Workbook到文件exportWorkbook.write(outStream);
//            也可以通过流获取大小
//            long size = outStream.getChannel().size();// 强制刷新文件流，确保所有数据都被写入到文件中outStream.flush();// 获取文件对象File outputFile = new File(dirPath, to_file_name);long length = outputFile.length();fileSize =  length  + " bytes";outStream.close();} catch (Exception e) {throw new BadRequestException("导出结果文件异常：" + e);}//        将有数据的excel的文件再加水印File tempFile = new File(dirPath, to_file_name);tempInput = new FileInputStream(tempFile);XSSFWorkbook xSSFWorkbook = new XSSFWorkbook(tempInput);
//            将导出的数据加水印放到另一个文件watermarkPath里面File watermarkFileExist = new File(watermarkPath, to_file_name);PoiSecurity.addWatermarkToXlsx(new String[]{"test"},xSSFWorkbook,watermarkFileExist);xSSFWorkbook.close();tempInput.close();// 终止后删除临时文件tempFile.deleteOnExit();
//        处理文件信息返回resultMap.put("attachmentId",attachmentId);resultMap.put("fileName",to_file_name);resultMap.put("fileType","excel");resultMap.put("fileExtension",fileSuffix);resultMap.put("fileSize",fileSize);resultMap.put("attachmentPath",dirPath);return resultMap;}/**** @param wb 操作的Workbook* @param list 数据库查询出来的信息* @param tableHead 数据库的表头(简单表头)* @param type excel类型  xlsx  1   xls 2   -------区分sheet最大告诉* @return*/public static Workbook export(Workbook wb,List<Map<String, Object>> list, List<String> tableHead,int type) {HSSFWorkbook HSSwb = null;XSSFWorkbook XSSwb = null;
//        不定义sheet名字,自生成
//        Excel 2003及更早的版本中，行数上限是65,536行
//        2007开始，行数上限增加到了1,048,576行int maxRow = 49999;//去除一个表头行if(type == 1){maxRow = 1048575;//去除一个表头行XSSwb  = (XSSFWorkbook)wb;}if(type == 2){maxRow = 65535;//去除一个表头行HSSwb  = (HSSFWorkbook)wb;}maxRow = 49999;//去除一个表头行(无论啥格式默认都是50000一个sheet)
//        处理数据需要多少个sheetint size = list.size();int result = size / maxRow + 1;if(result == 0){result = 1;}
//        循环sheetfor (int i = 0; i < result; i++) {if(type == 1){XSSFSheet sheet = XSSwb.createSheet();//            处理每个sheet的表头XSSFRow row = sheet.createRow((short) 0);Cell cell = null;for (int j = 0; j < tableHead.size(); j++) {cell = row.createCell(j);
//                cell.setCellStyle(headStyle);cell.setCellValue(tableHead.get(j));}
//          写入数据for (int n = 0 + maxRow * i; n < maxRow * (i + 1); n++) {
//                判断数据list的大小是否大于要创建的行if(size - 1 >= n ){//下面list.get(n)就取不到数据,不应该继续创建行  size 14 get(n)时候 n只能到13row = sheet.createRow(n % maxRow + 1);Cell dataCell = null;for (int m = 0; m < tableHead.size(); m++) {dataCell = row.createCell(m);dataCell.setCellValue(StringUtils.notEmpty(list.get(n).get(tableHead.get(m))));}}}}if(type == 2){HSSFSheet sheet = HSSwb.createSheet();//            处理每个sheet的表头HSSFRow row = sheet.createRow((short) 0);Cell cell = null;for (int j = 0; j < tableHead.size(); j++) {cell = row.createCell(j);
//                cell.setCellStyle(headStyle);cell.setCellValue(tableHead.get(j));}
//          写入数据for (int n = 0 + maxRow * i; n < maxRow * (i + 1); n++) {
//                判断数据list的大小是否大于要创建的行if(size - 1 >= n ){//下面list.get(n)就取不到数据,不应该继续创建行  size 14 get(n)时候 n只能到13row = sheet.createRow(n % maxRow + 1);Cell dataCell = null;for (int m = 0; m < tableHead.size(); m++) {dataCell = row.createCell(m);dataCell.setCellValue(StringUtils.notEmpty(list.get(n).get(tableHead.get(m))));}}}}}return wb;}
}

工具类PoiSecurity(之前贴过)

package com.njry.utils;import java.awt.*;
import java.awt.font.FontRenderContext;
import java.awt.geom.Rectangle2D;
import java.awt.image.BufferedImage;
import java.io.*;
import java.security.GeneralSecurityException;import javax.imageio.ImageIO;import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.openxml4j.opc.PackagePartName;
import org.apache.poi.openxml4j.opc.PackageRelationship;
import org.apache.poi.openxml4j.opc.TargetMode;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.crypt.EncryptionInfoBuilder;
import org.apache.poi.poifs.crypt.EncryptionMode;
import org.apache.poi.poifs.crypt.Encryptor;
import org.apache.poi.poifs.crypt.binaryrc4.BinaryRC4EncryptionInfoBuilder;
import org.apache.poi.poifs.crypt.binaryrc4.BinaryRC4Encryptor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.core.io.ClassPathResource;public class PoiSecurity {public static void addWatermarkToXlsx(String[] content,XSSFWorkbook xssfworkbook,File outfile) throws IOException{ByteArrayOutputStream bytestream = createWaterMark(content,300,250);
//		XSSFWorkbook下面有几个sheetint sheetsize = xssfworkbook.getNumberOfSheets();for(int index=0;index<sheetsize;index++){XSSFSheet sheet = xssfworkbook.getSheetAt(index);XSSFWorkbook workbook = sheet.getWorkbook();
//			bytestream.toByteArray()(前置知识)
//			下面这一串看不懂(没看官网文档的下场)int pictureIdx = workbook.addPicture(bytestream.toByteArray(), Workbook.PICTURE_TYPE_PNG);POIXMLDocumentPart poixmlDocumentPart = workbook.getAllPictures().get(pictureIdx);PackagePartName ppn = poixmlDocumentPart.getPackagePart().getPartName();String relType = XSSFRelation.IMAGES.getRelation();PackageRelationship pr = sheet.getPackagePart().addRelationship(ppn, TargetMode.INTERNAL, relType, null);sheet.getCTWorksheet().addNewPicture().setId(pr.getId());}if(!outfile.exists()){File par = outfile.getParentFile();if(!par.exists()){par.mkdirs();}outfile.createNewFile();}FileOutputStream out = new FileOutputStream(outfile);xssfworkbook.write(out);out.close();}public static void addImageToXlsx(String[] content,XSSFWorkbook xssfworkbook,File outfile) throws IOException{int imgW = 300,imgH = 250;ByteArrayOutputStream bytestream = createWaterMark(content,imgW,imgH);byte[] bytes = bytestream.toByteArray();int sheetsize = xssfworkbook.getNumberOfSheets();for(int index=0;index<sheetsize;index++){XSSFSheet sheet = xssfworkbook.getSheetAt(index);int[] wh = getSheetWidthAndHeight(sheet);int width = wh[0];int height = wh[1];XSSFDrawing drawingPatriarch = sheet.createDrawingPatriarch();//根据宽高进行插入for(int i = 0;i< (width / imgW) + 1;i++) {	//x轴for(int j = 0; j <( height / imgH) + 1;j++){	//y轴int x = i * imgW;int y = j * imgH;//根据图片插入/*XSSFClientAnchor anchor = new XSSFClientAnchor(x,y,x+width,y+height,0,0,1,1);int pIndex = xssfworkbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);drawingPatriarch.createPicture(anchor,pIndex);*/}}}if(!outfile.exists()){File par = outfile.getParentFile();if(!par.exists()){par.mkdirs();}outfile.createNewFile();}FileOutputStream out = new FileOutputStream(outfile);xssfworkbook.write(out);out.close();}/*** 获取sheet的宽高* @param sheet* @return*/private static int[] getSheetWidthAndHeight(Sheet sheet){float height = 0f;float width = 0f;int firstRowNum = sheet.getFirstRowNum();int lastRowNum = sheet.getLastRowNum();for (int i = firstRowNum; i < lastRowNum; i++) {Row row = sheet.getRow(i);if(i == firstRowNum){	//获取宽度short firstCellNum = row.getFirstCellNum();short lastCellNum = row.getLastCellNum();for(int j = firstCellNum;j<lastCellNum;j++){width = width + sheet.getColumnWidthInPixels(j);}}height = height+ row.getHeightInPoints();}return new int[]{Math.round(width),Math.round(height)};}private static ByteArrayOutputStream createWaterMark(String[] content,int width,int height) throws IOException {ClassPathResource resource = new ClassPathResource("font/simsun.ttc");//宋体InputStream resourceAsStream = null;Font font = null;try{resourceAsStream = resource.getInputStream();font = Font.createFont(Font.TRUETYPE_FONT, resourceAsStream);font = font.deriveFont(20f);}catch (Exception e){throw new IOException("PoiSecurity createWaterMark error",e);} finally {if(resourceAsStream!=null){resourceAsStream.close();}}
//		(前置知识)BufferedImage image = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);// 获取bufferedImage对象
//        设置字体(前置知识)Graphics2D g2d = image.createGraphics(); // 获取Graphics2d对象(前置知识)
//		这里实在看不懂,为啥销毁后用BufferedImage重新获取Graphics2d对象image = g2d.getDeviceConfiguration().createCompatibleImage(width, height, Transparency.TRANSLUCENT);g2d.dispose();g2d = image.createGraphics();g2d.setColor(new java.awt.Color(0, 0, 0, 40));//设置字体颜色和透明度，最后一个参数为透明度(前置知识)g2d.setStroke(new BasicStroke(1)); // 设置字体g2d.setFont(font); // 设置字体类型  加粗 大小g2d.rotate(-0.5, (double) image.getWidth() / 2, (double) image.getHeight() / 2);//设置倾斜度
//		要想得到表示屏幕设备字体属性的对象(前置知识)FontRenderContext context = g2d.getFontRenderContext();
//        循环设置水印文字位置(看不懂)for (int i = 0; i < content.length; i++) {
//        	返回包围字符串的矩形(前置知识)if(content[i] == null){continue;}Rectangle2D bounds = font.getStringBounds(content[i], context);
//            宽度double x = (width - bounds.getWidth()) / 2;
//            高度double y = (height - bounds.getHeight()) / 2;
//            上坡度double ascent = -bounds.getY();double baseY = y + ascent;// 写入水印文字原定高度过小，所以累计写水印，增加高度g2d.drawString(content[i], (int) x, (int) baseY+(30*i));}// 设置透明度(前置知识)
//        g2d.setComposite(AlphaComposite.getInstance(AlphaComposite.SRC_OVER));g2d.setComposite(AlphaComposite.getInstance(AlphaComposite.DST_OVER));// 释放对象g2d.dispose();
//		ByteArrayOutputStream(前置知识)ByteArrayOutputStream os = new ByteArrayOutputStream();
//		BufferedImage –> byte[](前置知识)ImageIO.write(image, "png", os);return os;}public static void encXlsx(String paasword,File file) throws InvalidFormatException, IOException, GeneralSecurityException{try (POIFSFileSystem fs = new POIFSFileSystem()) {EncryptionInfo info = new EncryptionInfo(EncryptionMode.agile);// EncryptionInfo info = new EncryptionInfo(EncryptionMode.agile, CipherAlgorithm.aes192, HashAlgorithm.sha384, -1, -1, null);Encryptor enc = info.getEncryptor();enc.confirmPassword(paasword);// Read in an existing OOXML file and write to encrypted output stream// don't forget to close the output stream otherwise the padding bytes aren't addedtry (OPCPackage opc = OPCPackage.open(file, PackageAccess.READ_WRITE);OutputStream os = enc.getDataStream(fs)) {opc.save(os);}// Write out the encrypted versiontry (FileOutputStream fos = new FileOutputStream(file)) {fs.writeFilesystem(fos);}}}}