java导出Excel通过response返回Excel.xls文件

╰+攻爆jí腚メ 2021-11-05 20:46 1770阅读 0赞

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import java.io.File;
    import java.io.FileOutputStream;
    import java.io.OutputStream;
    import javax.servlet.http.HttpServletResponse;
    
    
    public String export(Record record, HttpServletResponse response) throws Exception {
       List<Record> list = selList(record);
       //生成Excel
       HSSFWorkbook workbook = null;
       ExcelUtil excelUtil = new ExcelUtil();
       List<Map<String, Object>> mapList = this.getMapList(list);
       String saveBaseUrl = FileUtils.getProjectPath() + File.separator;
       String timeStr = System.currentTimeMillis();
       workbook = excelUtil.mapListToExcel(workbook, mapList, "统计");
    
       if (workbook != null) {
          try {
             //将excel写到文件夹中
             String fileName = UUIDGenerator.getId() + ".xls";
             File excelFile = new File(saveBaseUrl + "temp" + File.separator + "exportFile" + timeStr + File.separator + fileName);
             FileUtils.createFile(excelFile.getAbsolutePath());
             OutputStream outputStream = new FileOutputStream(excelFile);
             workbook.write(outputStream);
             outputStream.flush();
             outputStream.close();
             //导出到前端
             FileUtils.downloadFile(saveBaseUrl + "temp" + File.separator + "exportFile" + timeStr + File.separator, fileName, response);
             System.out.println("导出结束");
             //删除临时文件
             File file = new File(saveBaseUrl + "temp" + File.separator + "exportFile" + timeStr + File.separator + fileName);
             FileUtils.deleteFile(file);
          } catch (IOException e) {
             e.printStackTrace();
          }
       }
       return null;
    }

private List<Map<String, Object>> getMapList(List<Record> list) {
       List<Map<String, Object>> mapList = new ArrayList<>();
       if (list != null && list.size() > 0) {
          for (Record sta : list) {
             Map<String, Object> result = new LinkedHashMap<>();
             result.put("姓名", sta.getPersonName());
             result.put("学号", sta.getPersonCode());
             result.put("部门", sta.getDeptName());
             result.put("时间", sta.getSnapTime());
             mapList.add(result);
          }
       }
       return mapList;
    }

--------------------

以下属于工具类（可直接复制）：

## 工具类ExcelUtil.java ##

import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    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.hssf.util.HSSFColor;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.List;
    import java.util.Map;
    
    /**
     * excel工具类
     */
    public class ExcelUtil {
    
       /*将list<map<String,Object>>转换为Excel对象*/
       public HSSFWorkbook mapListToExcel(HSSFWorkbook workbook, List<Map<String, Object>> mapLists, String sheetName) {
    
          if (mapLists == null || mapLists.size() == 0) {
             System.out.println("结果集为null或者为空！");
             return null;
          }
          /*获取所有的列名*/
          Map<String, Object> map = mapLists.get(0);
          List<String> colNames = new ArrayList<>();
          colNames.addAll(map.keySet());
    
          /*创建表头*/
          /*创建Excel对象*/
          if (workbook == null) {
             workbook = new HSSFWorkbook();
          }
    
          HSSFSheet sheet = sheetName == null ? workbook.createSheet("sheet") : workbook.createSheet(sheetName);
    
          HSSFCellStyle hssfCellStyle = setHeadCellStyle(workbook);
    
          /*创建第一行作为表头*/
          HSSFRow headRow = sheet.createRow(0);
          headRow.setHeightInPoints(18);
          for (int i = 0; i < colNames.size(); i++) {
             //设置列宽
             sheet.setColumnWidth(i, 5000);
             HSSFCell cell = headRow.createCell(i);
             cell.setCellValue(colNames.get(i));
             cell.setCellStyle(hssfCellStyle);
          }
          /*填充所有行*/
          for (int i = 0; i < mapLists.size(); i++) {
             hssfCellStyle = setCellStyle(workbook);
             HSSFRow bodyRow = sheet.createRow(i + 1);
             bodyRow.setHeightInPoints(16);
             Map<String, Object> rowMap = mapLists.get(i);
             for (int j = 0; j < colNames.size(); j++) {
                HSSFCell cell = bodyRow.createCell(j);
                String cellValue = getCellValue(workbook.getSheet(sheetName).getRow(0).getCell(j));
                if (rowMap.get(cellValue) instanceof Integer) {
                   cell.setCellValue((Integer) rowMap.get(cellValue));
                } else if (rowMap.get(cellValue) instanceof Long) {
                   cell.setCellValue((Long) rowMap.get(cellValue));
                } else if (rowMap.get(cellValue) instanceof Float) {
                   cell.setCellValue((Float) rowMap.get(cellValue));
                } else if (rowMap.get(cellValue) instanceof Double) {
                   cell.setCellValue((Double) rowMap.get(cellValue));
                } else if (rowMap.get(cellValue) instanceof Date) {
                   cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(rowMap.get(cellValue)));
                } else {
                   cell.setCellValue((String) rowMap.get(cellValue));
                }
                cell.setCellStyle(hssfCellStyle);
             }
          }
          return workbook;
       }
    
       public HSSFCellStyle setCellStyle(HSSFWorkbook workbook) {
          HSSFCellStyle style = workbook.createCellStyle();
          style.setFont(setFount(workbook));
          style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居中
          style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中
          style.setWrapText(true);
          style.setLeftBorderColor(HSSFColor.BLACK.index);
          style.setBorderLeft((short) 1);
          style.setRightBorderColor(HSSFColor.BLACK.index);
          style.setBorderRight((short) 1);
          style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体
          style.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色．
          style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
          style.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);// 设置单元格的背景颜色．
    
          return style;
       }
    
       /*将单元格中的内容转换为字符串格式*/
       private static String getCellValue(HSSFCell cell) {
          if (cell == null) {
             return "";
          }
          String cellValue = null;
          switch (cell.getCellType()) {
             case HSSFCell.CELL_TYPE_BLANK: {
                cellValue = "";
                break;
             }
             case HSSFCell.CELL_TYPE_BOOLEAN: {
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
             }
             case HSSFCell.CELL_TYPE_ERROR: {
                cellValue = String.valueOf(cell.getErrorCellValue());
                break;
             }
             case HSSFCell.CELL_TYPE_FORMULA: {
                cellValue = String.valueOf(cell.getCellFormula());
                break;
             }
             case HSSFCell.CELL_TYPE_NUMERIC: {
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
             }
             case HSSFCell.CELL_TYPE_STRING: {
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
             }
             default:
                break;
          }
          return cellValue;
       }
    
       /**
        * 设置头行单元格样式
        */
    
       public HSSFCellStyle setHeadCellStyle(HSSFWorkbook workbook) {
          HSSFCellStyle columnHeadStyle = workbook.createCellStyle();
          columnHeadStyle.setFont(setHeadFount(workbook));
          columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
          columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
          columnHeadStyle.setLocked(true);
          columnHeadStyle.setWrapText(true);  //自动换行
          columnHeadStyle.setLeftBorderColor(HSSFColor.BLACK.index);// 左边框的颜色
          columnHeadStyle.setBorderLeft((short) 1);// 边框的大小
          columnHeadStyle.setRightBorderColor(HSSFColor.BLACK.index);// 右边框的颜色
          columnHeadStyle.setBorderRight((short) 1);// 边框的大小
          columnHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体
          columnHeadStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色
          // 设置单元格的背景颜色（单元格的样式会覆盖列或行的样式）
          columnHeadStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
          columnHeadStyle.setFillForegroundColor(HSSFColor.LEMON_CHIFFON.index);
    
          return columnHeadStyle;
       }
    
       public HSSFFont setFount(HSSFWorkbook workbook) {
          HSSFFont headfont = workbook.createFont();
          headfont.setFontName("宋体");
          headfont.setFontHeightInPoints((short) 10);// 字体大小
          headfont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
    
          return headfont;
       }
    
       /**
        * 设置头行字体
        */
       public HSSFFont setHeadFount(HSSFWorkbook workbook) {
          HSSFFont columnHeadFont = workbook.createFont();
          columnHeadFont.setFontName("宋体");
          columnHeadFont.setFontHeightInPoints((short) 11);
          columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    
          return columnHeadFont;
       }
    }

## 工具类FileUtils.java ##

import java.io.BufferedInputStream;
    import java.io.BufferedOutputStream;
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.io.OutputStream;
    import javax.servlet.http.HttpServletResponse;
    
    public class FileUtils extends org.apache.commons.io.FileUtils {
        /**
         * 递归删除文件夹
         */
        public static void deleteFile(File file) {
            if (file.exists()) {
                if (file.isFile()) {
                    file.delete();
                } else if (file.isDirectory()) {
                    File[] files = file.listFiles();
                    if (files != null) {
                        for (File file1 : files) {
                            deleteFile(file1);
                        }
                    }
                    file.delete();
                }
            }
        }
    
        /**
         * 创建文件，如果存在多级目录，就新增目录，然后再创建文件
         *
         * @param filePath
         * @return
         */
        public static Boolean createFile(String filePath) {
            File file = new File(filePath);
            return createFile(file);
        }
    
        public static Boolean createFile(File file) {
            File parentFile = file.getParentFile();
            if (!parentFile.exists()) {
                parentFile.mkdirs();
            }
            if (!file.exists()) {
                try {
                    file.createNewFile();
                    return true;
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            return false;
        }
    
        /**
         * 以压缩文件导出
         *
         * @param zipName
         * @param zipFilePath
         * @param response
         */
        public static void downloadFile(String zipFilePath, String zipName, HttpServletResponse response) {
            response.setCharacterEncoding("utf-8");
            try {
                File file = new File(zipFilePath + zipName);
                // 以流的形式下载文件。
                BufferedInputStream fis = new BufferedInputStream(new FileInputStream(file.getPath()));
                byte[] buffer = new byte[fis.available()];
                fis.read(buffer);
                fis.close();
                // 清空response
                response.reset();
                OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
                response.setContentType("application/octet-stream");
                response.setHeader("Content-Disposition", "attachment;filename=" + zipName);
                toClient.write(buffer);
                toClient.flush();
                toClient.close();
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }
    }