POI 功能(二)—— 导出数据到excel 表格中(简单易懂)

╰半夏微凉° 2021-12-05 06:15 301阅读 0赞

### 个人博客网：[https://wushaopei.github.io/][https_wushaopei.github.io]    (你想要这里多有) ###

将去商店刷卡消费的发票记录进行统计，生成excel表格；

最终效果：

![2019071722023877.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70][]

步骤：

接口 —— Controller ：

@RequestMapping("/exporInvoiceOrderXls")
        @ResponseBody
        public Object exporInvoiceOrderXls( HttpServletRequest request, HttpServletResponse response,
                                            @RequestParam(value="invoiceOrders") List<String>  invoiceOrders) {
    
    
            Map<String, Object> map = new HashMap<>();
    
            response.setContentType("octets/stream");
    
            Map<String, Object> map2 = new HashMap<String, Object>();
            map2.put("sheetTitle", "消费记录");
            map2.put("header", new String[]{"订单号", "商店名称", "发票批次", "账户名称", "商店地址", "刷卡账号","商店电话","信用卡开户行名称"});
            map2.put("fields", new String[]{"invoiceOrder", "companyName", "taxNumber", "accountBank", "companyAddress","bankNumber","companyTelephone","accountName"});
    
            List<InvoiceOrder> invoiceOrderss = InvoiceOrderService.queryInvoiceLists(invoiceOrders,0,9999);
            map2.put("data",invoiceOrderss);
            List<Map<String, Object>> list = new ArrayList<>();
            list.add(map2);
    
            String title = "日常消费刷卡发票记录";
    
            Map<String, short[]> mergedRegion = new HashMap<String, short[]>();
            mergedRegion.put("通用标题名称合并", new short[] {0, 0, 0, 100});
    
            ExportExcel<InvoiceOrder> ex = new ExportExcel<>();
    
            // 声明一个工作薄
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 生成一个标题样式
            HSSFCellStyle headerStyle = ex.cellStyle(workbook);
            // 生成一个内容样式
            HSSFCellStyle textStyle = ex.cellStyle(workbook);
            // 生成标题字体
            ex.setFont(workbook, headerStyle, (short) 10, HSSFFont.BOLDWEIGHT_NORMAL);
            // 生成内容字体
            ex.setFont(workbook, textStyle, HSSFFont.BOLDWEIGHT_NORMAL);
    
            try {
                OutputStream out = response.getOutputStream();
                // 导出文件名称添加当前时间
                String date = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
                //防止乱码
    //            response.addHeader("Access-Control-Expose-Headers", "Content-Type,Content-Disposition");
    //            response.addHeader("Content-Disposition",
    //                    "attachment;filename=" + URLEncoder.encode(title, "GBK") + date
    //                            + ".xls");
                response.setHeader("Content-Disposition", "attachment; filename=" + new String((title+".xls").getBytes("GB2312"),"ISO8859-1"));
    
    
                list.forEach(m -> {
                    ExcelModel excelModel = new ExcelModel();
                    excelModel.setWorkbook(workbook);
                    excelModel.setTitle(title);
                    excelModel.setFields((String[]) m.get("fields"));
                    excelModel.setHeader((String[]) m.get("header"));
                    excelModel.setHeaderRow(2);
                    excelModel.setMergedRegion(mergedRegion);
                    excelModel.setHeaderStyle(headerStyle);
                    excelModel.setTextStyle(textStyle);
                    excelModel.setTitles((String) m.get("sheetTitle"));
    
                    // 生成一页表格
                    HSSFSheet sheet = workbook.createSheet((String) m.get("sheetTitle"));
                    excelModel.setSheet(sheet);
                    ex.exportExcel(excelModel, (Collection<InvoiceOrder>) m.get("data"));
                });
    
                try {
                    workbook.write(out);
                } catch (IOException e) {
                    e.printStackTrace();
                }finally {
                    out.close();
                }
    
                map.put("state","0");
                map.put("message", "导出成功");
                return map;
            } catch (IOException e) {
                e.printStackTrace();
                map.put("state", "1");
                map.put("message", e.getMessage());
                return map;
            }
        }

通用导出  excel 工具

package com.example.poiutis.utils.Excel;
    
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.util.CellRangeAddress;
    
    import java.io.OutputStream;
    import java.util.HashMap;
    import java.util.Map;
    import java.util.regex.Matcher;
    import java.util.regex.Pattern;
    
    /**
     *
     * @Description 通用导出excel
     */
    public class ExcelModel {
      /**
       * 工作簿
       */
      private HSSFWorkbook workbook;
      /**
       * 显示的标题
       */
      private String titles;
      /**
       * 标题
       */
      private String title;
      /**
       * 统计之类需要用到的数据
       */
      private String[] headers;
      /**
       * 表格属性列名
       */
      private String[] header;
      /**
       * 列名需要占用多少行
       */
      private Integer headerRow;
      /**
       * 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
       */
      private OutputStream out;
      /**
       * 需要统计的列
       */
      private String[] arr;
      /**
       * 从哪列开始统计
       */
      private Integer beginArr;
      /**
       * 需要合并的单元格
       */
      private Map<String, short[]> mergedRegion;
      /**
       * 列名样式
       */
      private HSSFCellStyle headerStyle;
      /**
       * 内容样式
       */
      private HSSFCellStyle textStyle;
      /**
       * 行索引
       */
      private Integer index;
      /**
       * 总列数
       */
      private Integer totalCell = 0;
      /**
       * 行对象
       */
      private HSSFRow row;
      /**
       * 表格对象
       */
      private HSSFSheet sheet;
      /**
       * 标记
       */
      private Integer flag = 1;
      /**
       * 图片64码
       */
      private String[] base64s;
      /**
       * 过滤字段
       */
      private String[] filter;
      /**
       * 自定义字段顺序
       */
      private String[] fields;
    
      public String[] getFields() {
        return fields;
      }
    
      public void setFields(String[] fields) {
        this.fields = fields;
      }
    
      public ExcelModel() {}
    
      public ExcelModel(HSSFWorkbook workbook, String title, String[] fields, String[] headers, String[] header,
                        Integer headerRow, OutputStream out, String[] arr, Integer beginArr,
                        Map<String, short[]> mergedRegion, HSSFCellStyle headerStyle, HSSFCellStyle textStyle) {
        this.workbook = workbook;
        this.title = title;
        this.fields = fields;
        this.headers = headers;
        this.header = header;
        this.headerRow = headerRow;
        this.out = out;
        this.arr = arr;
        this.beginArr = beginArr;
        this.mergedRegion = mergedRegion;
        this.headerStyle = headerStyle;
        this.textStyle = textStyle;
      }
    
      public ExcelModel(HSSFWorkbook workbook, String title, String[] fields, String[] headers, String[] header,
                        Integer headerRow, OutputStream out, String[] arr, Map<String, short[]> mergedRegion,
                        HSSFCellStyle headerStyle, HSSFCellStyle textStyle) {
        this.workbook = workbook;
        this.title = title;
        this.fields = fields;
        this.headers = headers;
        this.header = header;
        this.headerRow = headerRow;
        this.out = out;
        this.arr = arr;
        this.mergedRegion = mergedRegion;
        this.headerStyle = headerStyle;
        this.textStyle = textStyle;
      }
    
      public String getTitles() {
        return titles;
      }
    
      public void setTitles(String titles) {
        this.titles = titles;
      }
    
      public HSSFWorkbook getWorkbook() {
        return workbook;
      }
    
      public void setWorkbook(HSSFWorkbook workbook) {
        this.workbook = workbook;
      }
    
      public String getTitle() {
        return title;
      }
    
      public void setTitle(String title) {
        this.title = title;
      }
    
      public String[] getHeaders() {
        return headers;
      }
    
      public void setHeaders(String[] headers) {
        this.headers = headers;
      }
    
      public String[] getHeader() {
        return header;
      }
    
      public void setHeader(String[] header) {
        this.header = header;
      }
    
      public Integer getHeaderRow() {
        return headerRow;
      }
    
      public void setHeaderRow(Integer headerRow) {
        this.headerRow = headerRow;
      }
    
      public OutputStream getOut() {
        return out;
      }
    
      public void setOut(OutputStream out) {
        this.out = out;
      }
    
      public String[] getArr() {
        return arr;
      }
    
      public void setArr(String[] arr) {
        this.arr = arr;
      }
    
      public Map<String, short[]> getMergedRegion() {
        return mergedRegion;
      }
    
      public void setMergedRegion(Map<String, short[]> mergedRegion) {
        this.mergedRegion = mergedRegion;
      }
    
      public HSSFCellStyle getHeaderStyle() {
        return headerStyle;
      }
    
      public void setHeaderStyle(HSSFCellStyle headerStyle) {
        this.headerStyle = headerStyle;
      }
    
      public HSSFCellStyle getTextStyle() {
        return textStyle;
      }
    
      public void setTextStyle(HSSFCellStyle textStyle) {
        this.textStyle = textStyle;
      }
    
      public Integer getIndex() {
        return index;
      }
    
      public void setIndex(Integer index) {
        this.index = index;
      }
    
      public Integer getTotalCell() {
        return totalCell;
      }
    
      public void setTotalCell(Integer totalCell) {
        this.totalCell = totalCell;
      }
    
      public HSSFRow getRow() {
        return row;
      }
    
      public void setRow(HSSFRow row) {
        this.row = row;
      }
    
      public HSSFSheet getSheet() {
        return sheet;
      }
    
      public void setSheet(HSSFSheet sheet) {
        this.sheet = sheet;
      }
    
      public Integer getFlag() {
        return flag;
      }
    
      public void setFlag(Integer flag) {
        this.flag = flag;
      }
    
      public String[] getBase64s() {
        return base64s;
      }
    
      public void setBase64s(String[] base64s) {
        this.base64s = base64s;
      }
    
      public Integer getBeginArr() {
        return beginArr;
      }
    
      public void setBeginArr(Integer beginArr) {
        this.beginArr = beginArr;
      }
    
      public String[] getFilter() {
        return filter;
      }
    
      public void setFilter(String[] filter) {
        this.filter = filter;
      }
    
      /**
       * 
       * @Title: setStyleValue
       * @Description: TODO
       * @param title 文件名称
       * @param sheet 表格对象
       * @param cell 单元格位置
       * @param header 表格属性列名数组
       * @param headers 统计之类需要用到的信息
       * @param style 样式
       * @param headerRow 列名需要占用多少行
       * @return: void
       */
      public void setStyleValue(String title, String titles, HSSFSheet sheet, int cell, String header,
                                String[] headers, HSSFCellStyle style, int headerRow) {
        for (int row = 0; row < headerRow; row++) {
          sheet.getRow(row).createCell(cell).setCellStyle(style);
          sheet.getRow(row).getCell(cell).setCellValue(header);
          if(row == 0)
          if(titles != null && titles.length() > 1){
            sheet.getRow(row).getCell(cell).setCellValue(titles);
          }
          sheet.getRow(row).setHeight((short) 400);
        }
      }
    
      /**
       * 
       * @Title: setComment
       * @Description: 生成注释内容
       * @param sheet 表格对象
       * @param format 定义注释的大小和位置
       * @param note 注释内容
       * @param author 作者
       * @return: void
       */
      public void setComment(HSSFSheet sheet, HSSFClientAnchor format, String note, String author) {
        // 创建HSSFPatriarch对象,HSSFPatriarch是所有注释的容器.
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        // 定义注释的大小和位置,详见文档
        HSSFComment comment = patriarch.createComment(format);
        // 设置注释内容
        comment.setString(new HSSFRichTextString(note));
        // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
        comment.setAuthor(author);
      }
    
      // 生成标题样式
      public HSSFCellStyle initStyle(HSSFWorkbook workbook, short fillForegroundColor,
                                     short fillPattern, short borderBottom, short borderLeft, short borderRight, short borderTop,
                                     short alignment) {
        HSSFCellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(fillForegroundColor);
        style.setFillPattern(fillPattern);
        style.setBorderBottom(borderBottom);
        style.setBorderLeft(borderLeft);
        style.setBorderRight(borderRight);
        style.setBorderTop(borderTop);
        style.setAlignment(alignment);
        return style;
      }
    
      public HSSFCellStyle initStyle(HSSFWorkbook workbook, short fillPattern, short borderBottom,
                                     short borderLeft, short borderRight, short borderTop, short alignment) {
        HSSFCellStyle style = workbook.createCellStyle();
        style.setFillPattern(fillPattern);
        style.setBorderBottom(borderBottom);
        style.setBorderLeft(borderLeft);
        style.setBorderRight(borderRight);
        style.setBorderTop(borderTop);
        style.setAlignment(alignment);
        return style;
      }
    
      // 生成内容样式
      public HSSFCellStyle initStyle(HSSFWorkbook workbook, short fillForegroundColor,
                                     short fillPattern, short borderBottom, short borderLeft, short borderRight, short borderTop,
                                     short alignment, short verticalAlignment) {
        HSSFCellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(fillForegroundColor);
        style.setFillPattern(fillPattern);
        style.setBorderBottom(borderBottom);
        style.setBorderLeft(borderLeft);
        style.setBorderRight(borderRight);
        style.setBorderTop(borderTop);
        style.setAlignment(alignment);
        style.setVerticalAlignment(verticalAlignment);
        return style;
      }
    
      public HSSFCellStyle cellStyle(HSSFWorkbook workbook) {
        HSSFCellStyle textStyle = workbook.createCellStyle();
        textStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        textStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
        textStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
        textStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
        textStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
        textStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        textStyle.setWrapText(true);
        return textStyle;
      }
    
      // 生成标题字体
      public void setFont(HSSFWorkbook workbook, HSSFCellStyle style, short color, short fontHeight,
                          short boldweight) {
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setColor(color);
        font.setFontHeightInPoints(fontHeight);
        font.setBoldweight(boldweight);
        // 把字体应用到当前的样式
        style.setFont(font);
      }
    
      public void setFont(HSSFWorkbook workbook, HSSFCellStyle style, short fontHeight, short boldweight) {
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints(fontHeight);
        font.setBoldweight(boldweight);
        // 把字体应用到当前的样式
        style.setFont(font);
      }
    
      // 生成内容字体
      public void setFont(HSSFWorkbook workbook, HSSFCellStyle style, short boldweight) {
        // 生成另一个字体
        HSSFFont font = workbook.createFont();
        font.setBoldweight(boldweight);
        // 把字体应用到当前的样式
        style.setFont(font);
      }
    
      /**
       * 
       * @Title: dataCast
       * @Description: 将上一行的数据移到新创建的行
       * @param totalCell 单元格位置
       * @param sheet 表格对象
       * @param index 行位置
       * @param i 需要添加headers的位置
       * @param headers 统计之类需要用到的数据
       * @param textStyle 内容样式
       * @return: int
       */
      public int dataCast(int totalCell, HSSFSheet sheet, int index, int i, String headers,
                          HSSFCellStyle textStyle) {
        index++;
        // 创建一行
        HSSFRow row = sheet.createRow(index);
        // 遍历数据
        for (int k = 0; k < totalCell; k++) {
          // 取得上一行单元格数据
          HSSFCell h = sheet.getRow(index - 1).getCell(k);
          // 创建单元格
          HSSFCell hs = row.createCell(k);
          // 判断数据是否numeric类型
          if (h.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            // 返回lang类型 长度过大会变成科学计数法形式,所以先返回lang类型
            long longVal = Math.round(h.getNumericCellValue());
            // 强转为int类型添加到单元格中
            hs.setCellValue(new Long(longVal).intValue());
          } else {
            // 否则都将数据转为string类型添加到单元格中
            hs.setCellValue(h.toString());
          }
          // 添加内容样式
          hs.setCellStyle(textStyle);
          // 清空上一行数据
          h.setCellValue("");
          // i: 在什么单元格位置添加,headers: 如果需要在上一行添加统计之类需要用到的数据
          h.setCellValue(k == i ? headers == null ? "" : headers : "");
        }
        return index;
      }
    
      /**
       * 
       * @Title: deleteCharAt
       * @Description: 将需要求和的列名+行数,添加到excel 格式 A1+A2+A3
       * @param sheet 表格对象
       * @param row 行位置
       * @param sheetCell 单元格
       * @param field 需要求和的字符串
       * @return: void
       */
      public void deleteCharAt(HSSFSheet sheet, int row, int sheetCell, StringBuffer field) {
        if (field.length() > 1) {
          field.deleteCharAt(0);
          sheet.getRow((row)).getCell(sheetCell).setCellFormula(field.toString());
        }
      }
    
      /**
       * 
       * @Title: setSubtotal
       * @Description: 将上一次的行位置和当前的行位置之间的行数遍历添加成需要求和的字符串,列名+行数
       * @param sheet 表格对象
       * @param row 上一次的行位置
       * @param k 当前的行位置
       * @param p 列位置
       * @param sheetCell 单元格
       * @param str 列名
       * @param headers 统计之类需要用到的数据
       * @return: void
       */
      public void setSubtotal(HSSFSheet sheet, Integer row, int k, int p, int sheetCell, String str,
                              String headers) {
        StringBuffer field = new StringBuffer("");
        if (row != null) {
          for (int i = row - 1; i > k; i--) {
            if (headers.equals(sheet.getRow(i - 1).getCell(sheetCell).toString())) {
              continue;
            }
            field.append("+" + str + i).toString();
          }
          deleteCharAt(sheet, row - 1, p, field);
        }
      }
    
      /**
       * 
       * @Title: setInsideRow
       * @Description: TODO
       * @param insideRow
       * @param str 列名
       * @return: StringBuffer
       */
      public StringBuffer setInsideRow(Integer insideRow, String str) {
        StringBuffer field = new StringBuffer("");
        for (int i = insideRow - 1; i > 1; i--) {
          field.append("+" + str + i).toString();
        }
        return field;
      }
    
      /**
       * 
       * @Title: createCell
       * @Description: 根据总列数循环创建单元格
       * @param row
       * @param headerStyle
       * @param headers
       * @param totalCell
       * @return: void
       */
      public void createCell(HSSFRow row, HSSFCellStyle headerStyle, String[] headers, int totalCell,
                             String title) {
        for (int i = 0; i < totalCell; i++) {
          HSSFCell cell = row.createCell(i);
          cell.setCellStyle(headerStyle);
          if (headers != null && headers.length > 1 && i == 0) {
            cell.setCellValue(headers[2]);
          }
        }
      }
    
      /**
       * 
       * @Title: typeConversion
       * @Description: 判断值的类型,进行类型转换,插入到excel中
       * @param workbook
       * @param value
       * @param cell
       * @return: void
       */
      public void typeConversion(HSSFWorkbook workbook, Object value, HSSFCell cell, String fieldName) {
        String textValue = null;
        if (value instanceof Integer) {
          int intValue = (Integer) value;
          cell.setCellValue(intValue);
        } else if (value instanceof Float) {
          float fValue = (Float) value;
          cell.setCellValue(fValue);
        } else if (value instanceof Double) {
          double dValue = (Double) value / 100;
          cell.setCellValue(dValue);
          if (fieldName.indexOf("Rate") != -1) {
            HSSFCellStyle cellStyle = cellStyle(workbook);
            cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
            cell.setCellStyle(cellStyle);
          }
        } else if (value instanceof Long) {
          long longValue = (Long) value;
          cell.setCellValue(longValue);
        } else {
          // 其它数据类型都当作字符串简单处理
          textValue = value.toString();
          // 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
          if (textValue != null) {
            Pattern p = Pattern.compile("^//d+(//.//d+)?$");
            Matcher matcher = p.matcher(textValue);
            if (matcher.matches()) {
              // 是数字当作double处理
              cell.setCellValue(Double.parseDouble(textValue));
            } else {
              HSSFRichTextString richString = new HSSFRichTextString(textValue);
              HSSFFont font3 = workbook.createFont();
              font3.setColor(HSSFColor.BLACK.index);
              richString.applyFont(font3);
              cell.setCellValue(richString);
            }
          }
        }
      }
    
      /**
       * 
       * @Title: addMergedRegion
       * @Description: 合并单元格
       * @param excelModel
       * @return: void
       */
      public void addMergedRegion(HSSFSheet sheet, int index, Map<String, short[]> mergedRegion,
                                  int totalCell) {
        // 合并单元格    [0]:起始行号,[1]:起始列号,[2]:结束行号,[3]:终止列号
        if (!mergedRegion.isEmpty()) {
          for (short[] s : mergedRegion.values()) {
            if (mergedRegion.get("TailCell") != null && mergedRegion.get("TailCell") == s) {
              CellRangeAddress region = new CellRangeAddress(index + 1, s[0], index + 1, s[1]);
              sheet.addMergedRegion(region);
            } else {
              // 如果需要合并所有行,改为100
              if(s[2] == 100) {
                sheet.addMergedRegion(new CellRangeAddress(s[0], s[1], index, s[3]));
                continue;
                };
              // 如果需要合并所有列,改为100
              if (s[3] == 100){
                sheet.addMergedRegion(new CellRangeAddress(s[0], s[1], s[2], (short) (totalCell - 1)));
                continue;
              }
              sheet.addMergedRegion(new CellRangeAddress(s[0], s[1], s[2], s[3]));
    
            }
          }
        }
      }
    
      /**
       * 
       * @Title: statisticalColumn
       * @Description: 统计列
       * @param excelModel
       * @return: void
       */
      public void statisticalColumn(ExcelModel excelModel) {
        HSSFSheet sheet = excelModel.getSheet();
        int index = excelModel.getIndex();
        int totalCell = excelModel.getTotalCell();
        // 定义一个空的Excel的列字段
        StringBuffer field = null;
        // 从哪列开始统计
        int p = excelModel.getBeginArr() == null ? 1 : excelModel.getBeginArr();
        // arr:需要统计的列,通过遍历将每列的数据进行统计
        for (String str : excelModel.getArr()) {
          field = new StringBuffer("");
          // 查找打印的数据有多少行
          for (int i = index + 1; i > 2; i--) {
            field.append("+" + str + (i)).toString();
          }
          // 删除前面多出的一个+号
          field.deleteCharAt(0);
          // 如果当前的cell < 总数cell
          if (p < totalCell) {
            // sheet.getRow(index+1).getCell(p).setCellType(HSSFCell.CELL_TYPE_FORMULA);
            sheet.getRow(index + 1).getCell(p).setCellFormula(field.toString());
          }
          p++;
        }
    
    
      }
    
      /**
       * 
       * @Title: setHeaderRows
       * @Description: 创建标题行
       * @param sheet
       * @param row
       * @return: void
       */
      public void setHeaderRows(HSSFSheet sheet, int row) {
        for (int i = 0; i < row; i++) {
          sheet.createRow(i);
        }
      }
    
      public int getbegin(String str) {
        Map<String, Integer> beginArrs = new HashMap<String, Integer>();
        beginArrs.put("A", 1);
        beginArrs.put("B", 2);
        beginArrs.put("C", 3);
        beginArrs.put("D", 4);
        beginArrs.put("E", 5);
        beginArrs.put("F", 6);
        beginArrs.put("G", 7);
        beginArrs.put("H", 8);
        beginArrs.put("I", 9);
        beginArrs.put("J", 10);
        beginArrs.put("K", 11);
        beginArrs.put("L", 12);
        beginArrs.put("M", 13);
        beginArrs.put("N", 14);
        beginArrs.put("O", 15);
        beginArrs.put("P", 16);
        beginArrs.put("Q", 17);
        beginArrs.put("R", 18);
        beginArrs.put("S", 19);
        beginArrs.put("T", 20);
        beginArrs.put("U", 21);
        beginArrs.put("V", 22);
        beginArrs.put("W", 23);
        beginArrs.put("X", 24);
        beginArrs.put("Y", 25);
        beginArrs.put("Z", 26);
        return beginArrs.get(str);
      }
    }

通用导出 excel 2 （反射方法）

package com.example.poiutis.utils.Excel;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    
    import java.lang.reflect.Field;
    import java.lang.reflect.InvocationTargetException;
    import java.lang.reflect.Method;
    import java.util.*;
    
    /**
     *
     * @Description 通用导出excel
     * @param <T>
     */
    public class ExportExcel<T> extends ExcelModel {
      /**
       * 
       * @Title: exportExcel
       * @Description: 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符合一定条件的数据以EXCEL 的形式输出到指定IO设备上
       * @param workbook 工作簿
       * @param title 标题
       * @param headers 统计之类需要用到的数据
       * @param header 表格属性列名数组
       * @param headerRow 列名需要占用多少行
       * @param dataset
       *        需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据
       *        )
       * @param out 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
       * @param arr 需要统计的列
       * @param mergedRegion 需要合并的单元格
       * @param headerStyle 标题列名样式
       * @param textStyle 内容样式
       * @param pattern 如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
       * @return: void
       */
      public void exportExcel(ExcelModel excelModel, Collection<T> dataset) {
    
        HSSFSheet sheet = excelModel.getSheet();
        // 产生表格标题行
        HSSFRow row = null;
        // 创建标题行
        setHeaderRows(sheet, excelModel.getHeaderRow());
        for (short cell = 0; cell < excelModel.getHeader().length; cell++) {
          // 根据数据的宽度改变excel单元格宽度
          sheet.setColumnWidth(cell, excelModel.getHeader()[cell].toString().length() * 600);
          if (excelModel.getHeaders() != null)
            if (excelModel.getHeader()[cell].toString().length() < excelModel.getHeaders()[cell]
                .toString().length()) {
              sheet.setColumnWidth(cell, excelModel.getHeaders()[cell].toString().length() * 600);
            }
          // 将样式,数据 插入到表格中
          setStyleValue(excelModel.getTitle(), excelModel.getTitles(), sheet, cell,
              excelModel.getHeader()[cell], excelModel.getHeaders(), excelModel.getHeaderStyle(),
              excelModel.getHeaderRow());
        }
        excelModel.setSheet(sheet);
        excelModel.setIndex(excelModel.getHeaderRow() - 1);
        // 遍历集合数据，产生数据行
        excelModel = insertData(excelModel, dataset);
        // 合并单元格
        addMergedRegion(sheet, excelModel.getIndex(), excelModel.getMergedRegion(),
                excelModel.getHeader().length);
        if(excelModel.getArr() != null){
          // 产生表格最后一行
          row = sheet.createRow(excelModel.getIndex() + 1);
          // 根据总列数循环创建单元格 lastHeaders
          createCell(row, excelModel.getHeaderStyle(), excelModel.getHeaders(),
              excelModel.getTotalCell(), excelModel.getTitle());
          // 统计列字段
    //      statisticalColumn(excelModel);
        }
        /*
        if (excelModel.getBase64s() != null) {
          // 画图的顶级管理器
          HSSFPatriarch patriarch = excelModel.getSheet().createDrawingPatriarch();
          // 获取图片64码
          String[] base64s = excelModel.getBase64s();
          int k = 25;
          // 保存图片到excel
          for (int i = base64s.length; i >= 1; i--) {
            String imgPath =
                ExcelUtil.generateImage(base64s[base64s.length - i],
                    (base64s.length - i) + excelModel.getTitle() + ".png");
            ExcelUtil.saveMulImage(excelModel.getWorkbook(), sheet, patriarch, imgPath, 0, 0, 0, 0,
                Short.parseShort(String.valueOf((1 + excelModel.getTotalCell()))), // 哪列开始
                (base64s.length - i) * k, // 哪行开始
                (short) (excelModel.getTotalCell() + 15), // 哪列结束
                (1 + base64s.length - i) * k // 哪行结束
            );
            // k -= 5;
          }
        }
        */
      }
    
      /**
       * 
       * @Title: insertData
       * @Description: 插入数据
       * @param excelModel
       * @param dataset
       * @return: ExcelModel
       */
      public ExcelModel insertData(ExcelModel excelModel, Collection<T> dataset) {
        Iterator<T> it = dataset.iterator();
        int index = excelModel.getIndex();
        int totalCell = excelModel.getTotalCell();
        HSSFRow row = null;
        HSSFSheet sheet = excelModel.getSheet();
        HSSFCellStyle textStyle = excelModel.getTextStyle();
        String[] headers = excelModel.getHeaders();
        Map<String, short[]> mergedRegion = excelModel.getMergedRegion();
        int flag = excelModel.getFlag();
        while (it.hasNext()) {
          index++;
          totalCell = 0;
          row = sheet.createRow(index);
          T t = it.next();
          String[] fs = excelModel.getFields();
          // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
          Field[] fields = t.getClass().getDeclaredFields();
          if(t instanceof Map) {//如果传进来的data是map类型
            for(String field : fs) {
              Object value = ((Map) t).get(field);
              HSSFCell cell = row.createCell(totalCell);
              totalCell++;
              cell.setCellStyle(textStyle);
              boolean boo = setValue(value, excelModel, cell, totalCell, sheet, row, field);
              if(!boo) {
                totalCell--;
              }
            }
    
          }else {
            List<Field> fieldList = new ArrayList<Field>();
            if(fs != null) {
              for(String fieldName : fs) {
                for(Field field : fields) {
                  if(field.getName().equals(fieldName)) {
                    fieldList.add(field);
                  }
                }
              }
              fields = fieldList.toArray(new Field[0]);
            }
            f1: for (short i = 0; i < fields.length; i++) {
              Field field = fields[i];
              String fieldName = field.getName();
              if (excelModel.getFilter() != null)
                for (int y = 0; y < excelModel.getFilter().length; y++) {
                  String str = excelModel.getFilter()[y];
                  if (fieldName.equals(str)) {
                    continue f1;
                  }
                }
              String getMethodName =
                      "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
              HSSFCell cell = row.createCell(totalCell);
              totalCell++;
              cell.setCellStyle(textStyle);
              try {
                Class<? extends Object> tCls = t.getClass();
                Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
                Object value = getMethod.invoke(t, new Object[] {});
                boolean boo = setValue(value, excelModel, cell, totalCell, sheet, row, fieldName);
                if(!boo) {
                  totalCell--;
                }
              } catch (SecurityException e) {
                e.printStackTrace();
              } catch (NoSuchMethodException e) {
                e.printStackTrace();
              } catch (IllegalArgumentException e) {
                e.printStackTrace();
              } catch (IllegalAccessException e) {
                e.printStackTrace();
              } catch (InvocationTargetException e) {
                e.printStackTrace();
              } finally {
                // 清理资源
              }
            }
          }
        }
        excelModel.setIndex(index);
        excelModel.setTotalCell(totalCell);
        excelModel.setFlag(flag);
        excelModel.setMergedRegion(mergedRegion);
        return excelModel;
      }
      public boolean setValue(Object value, ExcelModel excelModel, HSSFCell cell, int totalCell, HSSFSheet sheet, HSSFRow row, String fieldName ) {
        if (value != null) {
          if (excelModel.getHeaders() != null) {
            if (value.toString().length() > excelModel.getHeader()[totalCell - 1].toString()
                    .length()
                    && value.toString().length() > excelModel.getHeaders()[totalCell - 1].toString()
                    .length()) {
              // 根据数据的宽度改变excel单元格宽度
              sheet.setColumnWidth(totalCell - 1, (value.toString().length() * 600) > 65280 ? 20000 : value.toString().length() * 600);
            }
          } else if (value.toString().length() > excelModel.getHeader()[totalCell - 1].toString()
                  .length()) {
            sheet.setColumnWidth(totalCell - 1, (value.toString().length() * 600) > 65280 ? 20000 : value.toString().length() * 600);
          }
        } else {
          row.removeCell(cell);
          return false;
        }
        // 判断value值的类型,进行类型转换,插入到excel中
        typeConversion(excelModel.getWorkbook(), value, cell, fieldName);
        return true;
      }
    
    }

完整代码文档 GitHub 链接地址：[https://github.com/wushaopei/SpringBoot\_POIUtils.git][https_github.com_wushaopei_SpringBoot_POIUtils.git]

[https_wushaopei.github.io]: https://wushaopei.github.io/
[2019071722023877.png]: /images/20211205/a7713bac0ccc4523a70e711dbbe3e74d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70]: /images/20211205/61fcc3d0310043168942d970943dcb06.png
[https_github.com_wushaopei_SpringBoot_POIUtils.git]: https://github.com/wushaopei/SpringBoot_POIUtils.git