package com.casic.smbss.ncp.application.common.util; import io.swagger.annotations.ApiOperation; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.io.UnsupportedEncodingException; import java.util.*; import java.util.zip.ZipEntry; import java.util.zip.ZipOutputStream; @RestController @RequestMapping(value = "excelDown") public class ExcelDown { private static final String ZIP_NAME = "myfile.zip"; @ApiOperation(value = "导出") @GetMapping("down") public void down(HttpServletResponse response) throws Exception { Map<String, HSSFWorkbook> map = createExcel(); downloadExcelForZip(response, map, ZIP_NAME); } /** * 以压缩包的方式下载excel * * @param response * @param workbookMap */ public static void downloadExcelForZip(HttpServletResponse response, Map<String, HSSFWorkbook> workbookMap, String zipName) { // 文件名外的双引号处理firefox的空格截断问题 try { response.setContentType("application/*"); response.setHeader("content-disposition", "attachment;filename=" + new String(zipName.getBytes("gb2312"), "ISO8859-1")); response.setCharacterEncoding("UTF-8"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } try { ServletOutputStream outputStream = response.getOutputStream(); ZipOutputStream out = new ZipOutputStream(outputStream); for (String fileName : workbookMap.keySet()) { HSSFWorkbook workbook = workbookMap.get(fileName); ZipEntry entry = new ZipEntry(fileName); out.putNextEntry(entry); // 这里讲一下,workBook.write会指定关闭数据流,如果这里直接用workbook.write(out), // 下次就会抛出out已被关闭的异常,所有用ByteArrayOutputStream来拷贝一下。 ByteArrayOutputStream bos = new ByteArrayOutputStream(); workbook.write(bos); bos.writeTo(out); // 关闭输入流 out.closeEntry(); } if (out != null) { out.flush(); out.close(); } } catch (IOException e) { e.printStackTrace(); } } public Map<String, HSSFWorkbook> createExcel() throws Exception { // 模拟查询数据 List<String> lists = getData(); // excel tiltle,模拟一列 List<String> titles = new ArrayList<>(Arrays.asList("数据")); Map<String, HSSFWorkbook> workbookMap = new HashMap<>(); List<String> sp = new ArrayList<>(); for (int i = 1; i <= lists.size(); i++) { sp.add(lists.get(i - 1)); // 模拟2条数据一个excel文件 if ( i % 2 ==0 || (i == lists.size())) { // 创建一个webbook,对应一个Excel文件 HSSFWorkbook wk = new HSSFWorkbook(); // 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet HSSFSheet sheet = wk.createSheet(); // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short HSSFRow row = sheet.createRow(0); // 添加excel title HSSFCell cell = null; for (int t = 0, size = titles.size(); t < size; t++) { cell = row.createCell(t); cell.setCellValue(titles.get(t)); } HSSFRow r = null; HSSFCell c = null; for (int j = 1; j <= sp.size(); j++) { r = sheet.createRow(j); c = r.createCell(0); c.setCellValue(sp.get(j - 1)); } String excelName = UUID.randomUUID().toString() + ".xls"; workbookMap.put(excelName, wk); sp = new ArrayList<>(); } } return workbookMap; } public static List<String> getData() { List<String> strings = new ArrayList<>(); for (int i = 0; i <= 5; i++) { strings.add(String.valueOf(i)); } return strings; } }
查询数据库数据生成多个poi excel,压缩成一个文件下载
最新推荐文章于 2023-03-16 10:30:44 发布