POI导出实体类到Excel

POI 导出实体类到Excel

基础API介绍：

https://blog.youkuaiyun.com/w893932747/article/details/89354979

public class ExcelWriterUtil<T> {

  public void write(String title, List<T> dataset, OutputStream out) {
    if (dataset.size() <= 0) {
      return;
    }
    long startTime = System.currentTimeMillis();
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet(title);
    sheet.setDefaultColumnWidth(20);

    String[] headers = getHeaders(dataset.get(0));
    // 设置第一行标题
    XSSFRow row = sheet.createRow(0);
    XSSFCell cell;
    XSSFRichTextString text;
    for (short i = 0; i < headers.length; i++) {
      cell = row.createCell(i);
      text = new XSSFRichTextString(headers[i]);
      cell.setCellValue(text);
    }

    Field field;
    String fieldName;
    String getMethodName;
    Method method;
    CellStyle cellStyle;
    DataFormat dataFormat;
    Field[] fields;
    XSSFRichTextString richString;
    // 遍历dataset
    for (int i = 0; i < dataset.size(); i++) {
      // row = sheet.createRow(i + startIndex);
      row = getRow(sheet, 1 + i);
      T t = dataset.get(i);
      fields = t.getClass().getDeclaredFields();
      for (int j = 0; j < fields.length; j++) {
        cell = row.createCell(j);
        field = fields[j];
        fieldName = field.getName();
        // 拼接get方法名,getXxx()
        getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
        try {
          method = t.getClass().getMethod(getMethodName);
          Class<? extends Converter> converter =
                  field.getAnnotation(ExcelHeader.class).converter();

          Object value = null;
          try {
            value = converter.newInstance().converter(method.invoke(t));
          } catch (InstantiationException e) {
            e.printStackTrace();
          }


          if (value != null) {
            if (value instanceof Date) {
              cellStyle = workbook.createCellStyle();
              dataFormat = workbook.createDataFormat();
              cellStyle.setDataFormat(dataFormat.getFormat("yyyy-MM-dd"));
              cell.setCellStyle(cellStyle);
              cell.setCellValue((Date) value);
            } else if (value instanceof Long) {
              cell.setCellValue((Long) value);
              cell.setCellType(CellType.NUMERIC);
              dataFormat = workbook.createDataFormat();
              cellStyle = workbook.createCellStyle();
              cellStyle.setDataFormat(dataFormat.getFormat("0"));
              cell.setCellStyle(cellStyle);
            } else if (value instanceof Integer) {
              cell.setCellValue((Integer) value);
              cell.setCellType(CellType.NUMERIC);
              cellStyle = workbook.createCellStyle();
              dataFormat = workbook.createDataFormat();
              cellStyle.setDataFormat(dataFormat.getFormat("0"));
              cell.setCellStyle(cellStyle);
            } else if (value instanceof Double) {
              cell.setCellValue((Double) value);
              cell.setCellType(CellType.NUMERIC);
              cellStyle = workbook.createCellStyle();
              dataFormat = workbook.createDataFormat();
              cellStyle.setDataFormat(dataFormat.getFormat("#,###"));
              cell.setCellStyle(cellStyle);
            } else if (value instanceof Float) {
              cell.setCellValue((Float) value);
              cell.setCellType(CellType.NUMERIC);
              cellStyle = workbook.createCellStyle();
              dataFormat = workbook.createDataFormat();
              cellStyle.setDataFormat(dataFormat.getFormat("#,###"));
              cell.setCellStyle(cellStyle);
            } else if (value instanceof String) {
              richString = new XSSFRichTextString((String) value);
              cell.setCellValue(richString);
            } /* else if (value instanceof Boolean) {
                if (value.equals(true)) {
                  cell.setCellValue(1);
                } else {
                  cell.setCellValue(0);
                }
              }*/
          } else {
            cell.setCellValue("");
          }
        } catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
          e.printStackTrace();
        }
      }
    }
    try {
      // System.out.println("开始写入Excel~");
      workbook.write(out);
      long countTime = System.currentTimeMillis() - startTime;
      System.out.println("写入Excel完成,耗时：" + countTime + "ms");
    } catch (IOException e) {
      // e.printStackTrace();
    }
  }



  private synchronized XSSFRow getRow(XSSFSheet sheet, int rowIndex) {
    return sheet.createRow(rowIndex);
  }

  /**
   * 设置日期格式
   *
   * @param workbook
   * @return cellStyle
   */
  public CellStyle getDateStyle(XSSFWorkbook workbook) {
    CellStyle cellStyle = getBorderStyle(workbook);
    DataFormat dataFormat = workbook.createDataFormat();
    cellStyle.setDataFormat(dataFormat.getFormat("yyyy/mm/dd"));
    return cellStyle;
  }

  /**
   * 设置数值格式
   *
   * @param workbook
   * @return cellStyle
   */
  public CellStyle getNumericStyle(XSSFWorkbook workbook) {
    CellStyle cellStyle = getBorderStyle(workbook);
    XSSFDataFormat dataFormat = workbook.createDataFormat();
    cellStyle.setDataFormat(dataFormat.getFormat("0"));
    return cellStyle;
  }

  /**
   * 设置边框
   *
   * @param workbook
   * @return cellStyle
   */
  public CellStyle getBorderStyle(XSSFWorkbook workbook) {
    CellStyle cellStyle = workbook.createCellStyle();
    // 上边框
    /*cellStyle.setBorderTop(BorderStyle.THIN);
    //下边框
    cellStyle.setBorderBottom(BorderStyle.THIN);
    //左边框
    cellStyle.setBorderLeft(BorderStyle.THIN);
    //右边框
    cellStyle.setBorderRight(BorderStyle.THIN);*/
    // 居中
    // cellStyle.setAlignment(HorizontalAlignment.CENTER);
    return cellStyle;
  }

  /**
   * 对表头设置格式
   *
   * @param workbook
   * @return cellStyle
   */
  public CellStyle getHeaderStyle(XSSFWorkbook workbook) {
    CellStyle cellStyle = getBorderStyle(workbook);
    // cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.SKY_BLUE.getIndex());
    // cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    cellStyle.setFont(getHeaderFont(workbook));
    return cellStyle;
  }

  /**
   * 设置正文格式
   *
   * @param workbook
   * @return cellStyle
   */
  public CellStyle getContentStyle(XSSFWorkbook workbook) {
    CellStyle cellStyle = getBorderStyle(workbook);
    cellStyle.setFont(getContentFont(workbook));
    return cellStyle;
  }

  /**
   * 设置表头字体
   *
   * @param workbook
   * @return font
   */
  public Font getHeaderFont(XSSFWorkbook workbook) {
    XSSFFont font = workbook.createFont();
    font.setFontHeightInPoints((short) 10);
    font.setBold(false);
    return font;
  }

  /**
   * 设置正文字体
   *
   * @param workbook
   * @return font
   */
  public Font getContentFont(XSSFWorkbook workbook) {
    XSSFFont font = workbook.createFont();
    font.setFontHeightInPoints((short) 10);
    font.setBold(false);
    return font;
  }

  /**
   * 获取实体类字段对应表头
   *
   * @param t
   * @return filename[]
   */
  public String[] getHeaders(T t) {
    Field[] fields = t.getClass().getDeclaredFields();
    String[] headers = new String[fields.length];
    ExcelHeader head;
    for (Field field : fields) {
      head = field.getAnnotation(ExcelHeader.class);
      headers[head.index()] = head.value();
    }
    return headers;
  }
}