生成Excel的Action
1.添加依赖
<!--poi生成excel表格--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.15</version> </dependency> 2.写Action package cn.zhu.list_to_excel.action; import cn.zhu.list_to_excel.entity.user.TUser; import cn.zhu.list_to_excel.mapper.user.TUserMapper; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.util.CellRangeAddress; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import java.io.File; import java.io.FileOutputStream; import java.text.SimpleDateFormat; import java.util.*; @Controller public class QueryNoticeListAction { public static final Logger log = LoggerFactory.getLogger(QueryNoticeListAction.class); @Autowired(required = false) TUserMapper tUserMapper; public void excute(){ // 查询出的数据列表 List<TUser> tUsers = tUserMapper.selectAll(); log.info("tUsers===>"+tUsers); // 生成excel表格 String title = "所有用户列表"; Map<Integer, String> headMap = new HashMap<Integer, String>(); headMap.put(0, "用户ID"); headMap.put(1, "用户名"); headMap.put(2, "密码"); headMap.put(3, "盐值"); headMap.put(4, "电话"); headMap.put(5, "邮箱"); headMap.put(6, "性别"); headMap.put(7, "头像"); headMap.put(8, "是否已删除"); headMap.put(9, "创建人"); headMap.put(10, "创建时间"); headMap.put(11, "修改人"); headMap.put(12, "修改时间"); List<Map<Integer, String>> bodyMapList = new ArrayList<Map<Integer, String>>(); for (TUser user : tUsers) { Map<Integer, String> bodyMap = new HashMap<Integer, String>(); bodyMap.put(0, user.getUid().toString()); bodyMap.put(1, user.getUsername()); bodyMap.put(2, user.getPassword()); bodyMap.put(3, user.getSalt()); bodyMap.put(4, user.getPhone()); bodyMap.put(5, user.getEmail()); bodyMap.put(6, user.getGender().toString()); bodyMap.put(7, user.getAvatar()); bodyMap.put(8, user.getIsDelete().toString()); bodyMap.put(9, user.getCreazhuser()); bodyMap.put(10, (new SimpleDateFormat("yyyy-MM-dd")).format(user.getCreatedTime())); bodyMap.put(11, user.getModefiedUser()); bodyMap.put(12, (new SimpleDateFormat("yyyy-MM-dd")).format(user.getModefiedTime())); bodyMapList.add(bodyMap); } //创建文件夹和文件名 String locDir = "C:/mr/"+(new SimpleDateFormat("yyyy-MM-dd")).format(new Date())+"/"; File file = new File(locDir); if(!file.exists()){ file.mkdirs(); } String locFle = "noticeInfo_" + (new SimpleDateFormat("yyyy-MM-dd")).format(new Date()) + ".xls"; String filePath = locDir+locFle; createExcelWhitTil(13, title, headMap, bodyMapList, filePath); } /** * * @param colNum excel表格总列数 * @param title excel表格标题 * @param headMap excel表格表头 * @param bodyMapList excel表格数据体集合 * @param filePath 文件名称（含路径） */ private boolean createExcelWhitTil(int colNum, String title, Map<Integer, String> headMap, List<Map<Integer, String>> bodyMapList, String filePath) { // 创建工作簿对象 HSSFWorkbook workbook = new HSSFWorkbook(); // 创建sheet对象 String sheetName = "Sheet1"; HSSFSheet sheet = workbook.createSheet(sheetName); //设置列宽度 sheet.setColumnWidth(0, 3766); sheet.setColumnWidth(1, 3766); sheet.setColumnWidth(2, 3766); sheet.setColumnWidth(3, 3766); sheet.setColumnWidth(4, 3766); sheet.setColumnWidth(5, 6766); sheet.setColumnWidth(6, 6766); sheet.setColumnWidth(7, 3766); sheet.setColumnWidth(8, 5766); sheet.setColumnWidth(9, 5766); sheet.setColumnWidth(10, 5766); sheet.setColumnWidth(11, 5766); sheet.setColumnWidth(12, 5766); // 合并单元格，设置表格标题 sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colNum - 1)); // excel内容写入 // 0.表格标题栏写入 HSSFCellStyle tabTitleStyle= workbook.createCellStyle(); tabTitleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//设置水平居中 tabTitleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//设置垂直居中 HSSFFont titleFont =workbook.createFont();//字体对象 titleFont.setFontName("黑体"); titleFont.setFontHeightInPoints((short)20);//设置字体大小 titleFont.setColor(HSSFColor.RED.index); tabTitleStyle.setFont(titleFont); tabTitleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//设置内容居中 HSSFRow tableTitlerow=sheet.createRow(0);//将表格标题设置在第一行（0） HSSFCell titleCell = tableTitlerow.createCell(0);//选中合并后的单元格作为表格标题信息存储的那个单元格 titleCell.setCellValue(title);//设置表格标题内容 titleCell.setCellStyle(tabTitleStyle);//为单元格设置样式 // 1.数据表头写入 HSSFRow headRow = sheet.createRow(1); for (int i = 0; i < colNum; i++) { HSSFCell headCell = headRow.createCell(i, HSSFCell.CELL_TYPE_STRING); /*设置表头的内容*/ headCell.setCellValue(new HSSFRichTextString(headMap.get(i))); /*定义表头样式对象*/ HSSFCellStyle headStyle= workbook.createCellStyle(); headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//设置水平居中 headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//设置垂直居中 headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 HSSFFont headFont =workbook.createFont();//字体对象 headFont.setFontName("黑体"); headFont.setFontHeightInPoints((short)12);//设置字体大小 headFont.setColor(HSSFColor.GREEN.index); headStyle.setFont(headFont); /*设置表头的样式*/ headCell.setCellStyle(headStyle); } // 2.数据内容写入 Integer bodyLine = 2;// excel数据体初始行数 for (Map<Integer, String> bodyMap : bodyMapList) { HSSFRow bodyRow = sheet.createRow(bodyLine); for (int i = 0; i < colNum; i++) { HSSFCell bodyCell = bodyRow .createCell(i, HSSFCell.CELL_TYPE_STRING); /*设置body的内容*/ bodyCell.setCellValue(new HSSFRichTextString(bodyMap.get(i))); /*定义body样式对象*/ HSSFCellStyle bodyStyle= workbook.createCellStyle(); bodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//设置水平居中 bodyStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//设置垂直居中 bodyStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 bodyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 bodyStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 bodyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 HSSFFont bodyFont =workbook.createFont();//字体对象 bodyFont.setFontName("宋体"); bodyFont.setFontHeightInPoints((short)12);//设置字体大小 bodyStyle.setFont(bodyFont); /*设置body的样式*/ bodyCell.setCellStyle(bodyStyle); } bodyLine++; } FileOutputStream out = null; log.info("源文件路径---" + filePath); try { // 生成Excel的文件名 out = new FileOutputStream(new File(filePath)); log.info("导入文件输出流"); workbook.write(out); log.info("刷新文件输出流"); out.flush(); log.info("关闭文件输出流"); out.close(); } catch (Exception e) { log.info("文件生成失败", e); return false; } log.info("-------生成excel文件成功------"); return true; } }
将上面的Action设置为定时执行
1.在SpringBoot启动类上添加注解
@EnableScheduling //表示应用可以支持定时任务
2.写定时类
package cn.zhu.list_to_excel.task; import cn.zhu.list_to_excel.action.QueryNoticeListAction; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.scheduling.annotation.Scheduled; import org.springframework.stereotype.Component; @Component public class CreateExcel { private Logger logger = LoggerFactory.getLogger(CreateExcel.class); @Autowired QueryNoticeListAction queryNoticeListAction; @Scheduled(cron = "0 0/2 * * * ?") //每2分钟生成一次Excel public void createExcelAtMr(){ logger.info("---开始生成Excel---"); queryNoticeListAction.excute(); logger.info("---完成生成Excel---"); } }
