ExcelUtil-CSDN博客

本文链接：https://blog.csdn.net/sinat_29535523/article/details/128607703

package com.example.demo.excel.utils.poi;

import com.example.demo.excel.annotation.Excel;
import com.example.demo.excel.annotation.Excel.ColumnType;
import com.example.demo.excel.annotation.Excel.Type;
import com.example.demo.excel.entity.AjaxResult;
import com.sun.xml.internal.ws.util.UtilException;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.RegExUtils;
import org.apache.commons.lang3.reflect.FieldUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.time.;
import java.util.;
import java.util.stream.Collectors;

/**

Excel相关处理
@author ruoyi
*/
public class ExcelUtil
{
private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);

public static final String FORMULA_REGEX_STR = “=|-|\+|@”;

public static final String[] FORMULA_STR = { “=”, “-”, “+”, “@” };

/**
- Excel sheet最大行数，默认65536
  */
  public static final int sheetSize = 65536;
/**
- 工作表名称
  */
  private String sheetName;
/**
- 导出类型（EXPORT:导出数据；IMPORT：导入模板）
  */
  private Type type;
/**
- 工作薄对象
  */
  private Workbook wb;
/**
- 工作表对象
  */
  private Sheet sheet;
/**
- 样式列表
  */
  private Map<String, CellStyle> styles;
/**
- 导入导出数据列表
  */
  private List list;
/**
- 注解列表
  */
  private List<Object[]> fields;
/**
- 当前行号
  */
  private int rownum;
/**
- 标题
  */
  private String title;
/**
- 最大高度
  */
  private short maxHeight;
/**
- 合并后最后行数
  */
  private int subMergedLastRowNum = 0;
/**
- 合并后开始行数
  */
  private int subMergedFirstRowNum = 1;
/**
- 对象的子列表方法
  */
  private Method subMethod;
/**
- 对象的子列表属性
  */
  private List subFields;
/**
- 统计列表
  */
  private Map<Integer, Double> statistics = new HashMap<Integer, Double>();
/**
- 实体对象
  */
  public Class clazz;
/**
- 需要排除列属性
  */
  public String[] excludeFields;
public ExcelUtil(Class clazz)
{
this.clazz = clazz;
}

public void init(List list, String sheetName, String title, Type type)
{
if (list == null)
{
list = new ArrayList();
}
this.list = list;
this.sheetName = sheetName;
this.type = type;
this.title = title;
createExcelField();
createWorkbook();
createTitle();
createSubHead();
}

/**
- 创建excel第一行标题
  */
  public void createTitle()
  {
  if (StringUtils.isNotEmpty(title))
  {
  subMergedFirstRowNum++;
  subMergedLastRowNum++;
  int titleLastCol = this.fields.size() - 1;
  if (isSubList())
  {
  titleLastCol = titleLastCol + subFields.size() - 1;
  }
  Row titleRow = sheet.createRow(rownum == 0 ? rownum++ : 0);
  titleRow.setHeightInPoints(30);
  Cell titleCell = titleRow.createCell(0);
  titleCell.setCellStyle(styles.get(“title”));
  titleCell.setCellValue(title);
  sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(), titleLastCol));
  }
  }
/**
- 创建对象的子列表名称
  */
  public void createSubHead()
  {
  if (isSubList())
  {
  subMergedFirstRowNum++;
  subMergedLastRowNum++;
  Row subRow = sheet.createRow(rownum);
  int excelNum = 0;
  for (Object[] objects : fields)
  {
  Excel attr = (Excel) objects[1];
  Cell headCell1 = subRow.createCell(excelNum);
  headCell1.setCellValue(attr.name());
  excelNum++;
  }
  int headFirstRow = excelNum - 1;
  int headLastRow = headFirstRow + subFields.size() - 1;
  if (headLastRow > headFirstRow)
  {
  sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, headFirstRow, headLastRow));
  }
  rownum++;
  }
  }
/**
- 对list数据源将其里面的数据导入到excel表单
- @param list 导出数据集合
- @param sheetName 工作表的名称
- @return 结果
  */
  public AjaxResult exportExcel(List list, String sheetName)
  {
  return exportExcel(list, sheetName, StringUtils.EMPTY);
  }
/**
- 对list数据源将其里面的数据导入到excel表单
- @param list 导出数据集合
- @param sheetName 工作表的名称
- @param title 标题
- @return 结果
  */
  public AjaxResult exportExcel(List list, String sheetName, String title)
  {
  this.init(list, sheetName, title, Type.EXPORT);
  return exportExcel();
  }
/**
- 对list数据源将其里面的数据导入到excel表单
- @param response 返回数据
- @param list 导出数据集合
- @param sheetName 工作表的名称
- @return 结果
  */
  public void exportExcel(HttpServletResponse response, List list, String sheetName)
  {
  exportExcel(response, list, sheetName, StringUtils.EMPTY);
  }
/**
- 对list数据源将其里面的数据导入到excel表单
- @param response 返回数据
- @param list 导出数据集合
- @param sheetName 工作表的名称
- @param title 标题
- @return 结果
  */
  public void exportExcel(HttpServletResponse response, List list, String sheetName, String title)
  {
  response.setContentType(“application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”);
  response.setCharacterEncoding(“utf-8”);
  this.init(list, sheetName, title, Type.EXPORT);
  exportExcel(response);
  }
/**
- 对list数据源将其里面的数据导入到excel表单
- @return 结果
  */
  public void exportExcel(HttpServletResponse response)
  {
  try
  {
  writeSheet();
  wb.write(response.getOutputStream());
  }
  catch (Exception e)
  {
  log.error(“导出Excel异常{}”, e.getMessage());
  }
  finally
  {
  IOUtils.closeQuietly(wb);
  }
  }
/**
- 对list数据源将其里面的数据导入到excel表单
- @return 结果
  */
  public AjaxResult exportExcel()
  {
  OutputStream out = null;
  try
  {
  writeSheet();
  String filename = encodingFilename(sheetName);
  out = new FileOutputStream(getAbsoluteFile(filename));
  wb.write(out);
  return AjaxResult.success(filename);
  }
  catch (Exception e)
  {
  log.error(“导出Excel异常{}”, e.getMessage());
  throw new UtilException(“导出Excel失败，请联系网站管理员！”);
  }
  finally
  {
  IOUtils.closeQuietly(wb);
  IOUtils.closeQuietly(out);
  }
  }
/**
- 创建写入数据到Sheet
  */
  public void writeSheet()
  {
  // 取出一共有多少个sheet.
  int sheetNo = Math.max(1, (int) Math.ceil(list.size() * 1.0 / sheetSize));
  for (int index = 0; index < sheetNo; index++)
  {
  createSheet(sheetNo, index);
```
 // 产生一行
 Row row = sheet.createRow(rownum);
 int column = 0;
 // 写入各个字段的列头名称
 for (Object[] os : fields)
 {
     Field field = (Field) os[0];
     Excel excel = (Excel) os[1];
     if (Collection.class.isAssignableFrom(field.getType()))
     {
         for (Field subField : subFields)
         {
             Excel subExcel = subField.getAnnotation(Excel.class);
             this.createHeadCell(subExcel, row, column++);
         }
     }
     else
     {
         this.createHeadCell(excel, row, column++);
     }
 }
 if (Type.EXPORT.equals(type))
 {
     fillExcelData(index, row);
 }
```
  }
  }
/**
- 填充excel数据
- @param index 序号
- @param row 单元格行
  */
  @SuppressWarnings(“unchecked”)
  public void fillExcelData(int index, Row row)
  {
  int startNo = index * sheetSize;
  int endNo = Math.min(startNo + sheetSize, list.size());
  int rowNo = (1 + rownum) - startNo;
  for (int i = startNo; i < endNo; i++)
  {
  rowNo = isSubList() ? (i > 1 ? rowNo + 1 : rowNo + i) : i + 1 + rownum - startNo;
  row = sheet.createRow(rowNo);
  // 得到导出对象.
  T vo = (T) list.get(i);
  Collection<?> subList = null;
  if (isSubList())
  {
  if (isSubListValue(vo))
  {
  subList = getListCellValue(vo);
  subMergedLastRowNum = subMergedLastRowNum + subList.size();
  }
  else
  {
  subMergedFirstRowNum++;
  subMergedLastRowNum++;
  }
  }
  int column = 0;
  for (Object[] os : fields)
  {
  Field field = (Field) os[0];
  Excel excel = (Excel) os[1];
  if (Collection.class.isAssignableFrom(field.getType()) && StringUtils.isNotNull(subList))
  {
  boolean subFirst = false;
  for (Object obj : subList)
  {
  if (subFirst)
  {
  rowNo++;
  row = sheet.createRow(rowNo);
  }
  List subFields = FieldUtils.getFieldsListWithAnnotation(obj.getClass(), Excel.class);
  int subIndex = 0;
  for (Field subField : subFields)
  {
  if (subField.isAnnotationPresent(Excel.class))
  {
  subField.setAccessible(true);
  Excel attr = subField.getAnnotation(Excel.class);
  this.addCell(attr, row, (T) obj, subField, column + subIndex);
  }
  subIndex++;
  }
  subFirst = true;
  }
  this.subMergedFirstRowNum = this.subMergedFirstRowNum + subList.size();
  }
  else
  {
  this.addCell(excel, row, vo, field, column++);
  }
  }
  }
  }
/**
- 创建表格样式
- @param wb 工作薄对象
- @return 样式列表
  */
  private Map<String, CellStyle> createStyles(Workbook wb)
  {
  // 写入各条记录,每条记录对应excel表中的一行
  Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
  CellStyle style = wb.createCellStyle();
  style.setAlignment(HorizontalAlignment.CENTER);
  style.setVerticalAlignment(VerticalAlignment.CENTER);
  Font titleFont = wb.createFont();
  titleFont.setFontName(“Arial”);
  titleFont.setFontHeightInPoints((short) 16);
  titleFont.setBold(true);
  style.setFont(titleFont);
  styles.put(“title”, style);
  
  style = wb.createCellStyle();
  style.setAlignment(HorizontalAlignment.CENTER);
  style.setVerticalAlignment(VerticalAlignment.CENTER);
  style.setBorderRight(BorderStyle.THIN);
  style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  style.setBorderLeft(BorderStyle.THIN);
  style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  style.setBorderTop(BorderStyle.THIN);
  style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  style.setBorderBottom(BorderStyle.THIN);
  style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  Font dataFont = wb.createFont();
  dataFont.setFontName(“Arial”);
  dataFont.setFontHeightInPoints((short) 10);
  style.setFont(dataFont);
  styles.put(“data”, style);
  
  style = wb.createCellStyle();
  style.setAlignment(HorizontalAlignment.CENTER);
  style.setVerticalAlignment(VerticalAlignment.CENTER);
  Font totalFont = wb.createFont();
  totalFont.setFontName(“Arial”);
  totalFont.setFontHeightInPoints((short) 10);
  style.setFont(totalFont);
  styles.put(“total”, style);
  return styles;
  }
/**
- 创建单元格
  */
  public Cell createHeadCell(Excel attr, Row row, int column)
  {
  // 创建列
  Cell cell = row.createCell(column);
  // 写入列信息
  cell.setCellValue(attr.name());
  if (isSubList())
  {
  if (attr.needMerge())
  {
  sheet.addMergedRegion(new CellRangeAddress(rownum - 1, rownum, column, column));
  }
  }
  return cell;
  }
/**
- 设置单元格信息
- @param value 单元格值
- @param attr 注解相关
- @param cell 单元格信息
  */
  public void setCellVo(Object value, Excel attr, Cell cell)
  {
  if (ColumnType.STRING == attr.cellType())
  {
  String cellValue = toStr(value);
  // 对于任何以表达式触发字符 =-+@开头的单元格，直接使用tab字符作为前缀，防止CSV注入。
  if (StringUtils.startsWithAny(cellValue, FORMULA_STR))
  {
  cellValue = RegExUtils.replaceFirst(cellValue, FORMULA_REGEX_STR, “\t$0”);
  }
  if (value instanceof Collection && StringUtils.equals(“[]”, cellValue))
  {
  cellValue = StringUtils.EMPTY;
  }
  cell.setCellValue(StringUtils.isNull(cellValue) ? attr.defaultValue() : cellValue + attr.suffix());
  }
  else if (ColumnType.NUMERIC == attr.cellType())
  {
  if (StringUtils.isNotNull(value))
  {
  cell.setCellValue(StringUtils.contains(toStr(value), “.”) ? toDouble(value) : toInt(value));
  }
  }
  }
/**
- 添加单元格
  */
  public Cell addCell(Excel attr, Row row, T vo, Field field, int column)
  {
  Cell cell = null;
  try
  {
  // 设置行高
  row.setHeight(maxHeight);
  // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
  if (attr.isExport())
  {
  // 创建cell
  cell = row.createCell(column);
  if (isSubListValue(vo) && getListCellValue(vo).size() > 1 && attr.needMerge())
  {
  CellRangeAddress cellAddress = new CellRangeAddress(subMergedFirstRowNum, subMergedLastRowNum, column, column);
  sheet.addMergedRegion(cellAddress);
  }
  // 用于读取对象中的属性
  Object value = getTargetValue(vo, field, attr);
  String dateFormat = attr.dateFormat();
  if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value))
  {
  cell.setCellValue(parseDateToStr(dateFormat, value));
  }
  else if (value instanceof BigDecimal && -1 != attr.scale())
  {
  cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).doubleValue());
  }
  else
  {
  // 设置列类型
  setCellVo(value, attr, cell);
  }
  }
  }
  catch (Exception e)
  {
  log.error(“导出Excel失败{}”, e);
  }
  return cell;
  }
/**
- 编码文件名
  */
  public String encodingFilename(String filename)
  {
  filename = UUID.randomUUID().toString() + “_” + filename + “.xlsx”;
  return filename;
  }
/**
- 获取下载路径
- @param filename 文件名称
  */
  public String getAbsoluteFile(String filename)
  {
  String downloadPath = “D:\” + filename;
  File desc = new File(downloadPath);
  if (!desc.getParentFile().exists())
  {
  desc.getParentFile().mkdirs();
  }
  return downloadPath;
  }
/**
- 获取bean中的属性值
- @param vo 实体对象
- @param field 字段
- @param excel 注解
- @return 最终的属性值
- @throws Exception
  */
  private Object getTargetValue(T vo, Field field, Excel excel) throws Exception
  {
  Object o = field.get(vo);
  if (StringUtils.isNotEmpty(excel.targetAttr()))
  {
  String target = excel.targetAttr();
  if (target.contains(“.”))
  {
  String[] targets = target.split(“[.]”);
  for (String name : targets)
  {
  o = getValue(o, name);
  }
  }
  else
  {
  o = getValue(o, target);
  }
  }
  return o;
  }
/**
- 以类的属性的get方法方法形式获取值
- @param o
- @param name
- @return value
- @throws Exception
  */
  private Object getValue(Object o, String name) throws Exception
  {
  if (StringUtils.isNotNull(o) && StringUtils.isNotEmpty(name))
  {
  Class<?> clazz = o.getClass();
  Field field = clazz.getDeclaredField(name);
  field.setAccessible(true);
  o = field.get(o);
  }
  return o;
  }
/**
- 得到所有定义字段
  */
  private void createExcelField()
  {
  this.fields = getFields();
  this.fields = this.fields.stream().sorted(Comparator.comparing(objects -> ((Excel) objects[1]).sort())).collect(Collectors.toList());
  this.maxHeight = getRowHeight();
  }
/**
- 获取字段注解信息
  */
  public List<Object[]> getFields()
  {
  List<Object[]> fields = new ArrayList<Object[]>();
  List tempFields = new ArrayList<>();
  tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
  tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
  for (Field field : tempFields)
  {
  if (!ArrayUtils.contains(this.excludeFields, field.getName()))
  {
  // 单注解
  if (field.isAnnotationPresent(Excel.class))
  {
  Excel attr = field.getAnnotation(Excel.class);
  if (attr != null && (attr.type() == Type.ALL || attr.type() == type))
  {
  field.setAccessible(true);
  fields.add(new Object[] { field, attr });
  }
  if (Collection.class.isAssignableFrom(field.getType()))
  {
  subMethod = getSubMethod(field.getName(), clazz);
  ParameterizedType pt = (ParameterizedType) field.getGenericType();
  Class<?> subClass = (Class<?>) pt.getActualTypeArguments()[0];
  this.subFields = FieldUtils.getFieldsListWithAnnotation(subClass, Excel.class);
  }
  }
  }
  }
  return fields;
  }
/**
- 根据注解获取最大行高
  */
  public short getRowHeight()
  {
  double maxHeight = 0;
  for (Object[] os : this.fields)
  {
  Excel excel = (Excel) os[1];
  maxHeight = Math.max(maxHeight, excel.height());
  }
  return (short) (maxHeight * 20);
  }
/**
- 创建一个工作簿
  */
  public void createWorkbook()
  {
  this.wb = new SXSSFWorkbook(500);
  this.sheet = wb.createSheet();
  wb.setSheetName(0, sheetName);
  this.styles = createStyles(wb);
  }
/**
- 创建工作表
- @param sheetNo sheet数量
- @param index 序号
  */
  public void createSheet(int sheetNo, int index)
  {
  // 设置工作表的名称.
  if (sheetNo > 1 && index > 0)
  {
  this.sheet = wb.createSheet();
  this.createTitle();
  wb.setSheetName(index, sheetName + index);
  }
  }
/**
- 格式化不同类型的日期对象
- @param dateFormat 日期格式
- @param val 被格式化的日期对象
- @return 格式化后的日期字符
  */
  public String parseDateToStr(String dateFormat, Object val)
  {
  if (val == null)
  {
  return “”;
  }
  String str;
  if (val instanceof Date)
  {
  str = DateToStr(dateFormat, (Date) val);
  }
  else if (val instanceof LocalDateTime)
  {
  str = DateToStr(dateFormat, toDate((LocalDateTime) val));
  }
  else if (val instanceof LocalDate)
  {
  str = DateToStr(dateFormat, toDate((LocalDate) val));
  }
  else
  {
  str = val.toString();
  }
  return str;
  }
/**
- 是否有对象的子列表
  */
  public boolean isSubList()
  {
  return StringUtils.isNotNull(subFields) && subFields.size() > 0;
  }
/**
- 是否有对象的子列表，集合不为空
  */
  public boolean isSubListValue(T vo)
  {
  return StringUtils.isNotNull(subFields) && subFields.size() > 0 && StringUtils.isNotNull(getListCellValue(vo)) && getListCellValue(vo).size() > 0;
  }
/**
- 获取集合的值
  */
  public Collection<?> getListCellValue(Object obj) { Object value; try { value = subMethod.invoke(obj, new Object[] {}); } catch (Exception e) { return new ArrayList(); } return (Collection<?>) value;
  }
/**
- 获取对象的子列表方法
- @param name 名称
- @param pojoClass 类对象
- @return 子列表方法
  /
  public Method getSubMethod(String name, Class<?> pojoClass)
  {
  StringBuffer getMethodName = new StringBuffer(“get”);
  getMethodName.append(name.substring(0, 1).toUpperCase());
  getMethodName.append(name.substring(1));
  Method method = null;
  try
  {
  method = pojoClass.getMethod(getMethodName.toString(), new Class[] {});
  }
  catch (Exception e)
  {
  log.error(“获取对象异常{}”, e.getMessage());
  }
  return method;
  }
  /*
- 转换为字符串
- 如果给定的值为null，或者转换失败，返回默认值null
- 转换失败不会报错
- @param value 被转换的值
- @return 结果
  */
  public String toStr(Object value)
  {
  return toStr(value, null);
  }
/**
- 转换为字符串
- 如果给定的值为null，或者转换失败，返回默认值
- 转换失败不会报错
- @param value 被转换的值
- @param defaultValue 转换错误时的默认值
- @return 结果
  */
  public String toStr(Object value, String defaultValue)
  {
  if (null == value)
  {
  return defaultValue;
  }
  if (value instanceof String)
  {
  return (String) value;
  }
  return value.toString();
  }
/**
- 转换为double
- 如果给定的值为空，或者转换失败，返回默认值null
- 转换失败不会报错
- @param value 被转换的值
- @return 结果
  */
  public Double toDouble(Object value)
  {
  return toDouble(value, null);
  }
/**
- 转换为double
- 如果给定的值为空，或者转换失败，返回默认值
- 转换失败不会报错
- @param value 被转换的值
- @param defaultValue 转换错误时的默认值
- @return 结果
  /
  public Double toDouble(Object value, Double defaultValue)
  {
  if (value == null)
  {
  return defaultValue;
  }
  if (value instanceof Double)
  {
  return (Double) value;
  }
  if (value instanceof Number)
  {
  return ((Number) value).doubleValue();
  }
  final String valueStr = toStr(value, null);
  if (StringUtils.isEmpty(valueStr))
  {
  return defaultValue;
  }
  try
  {
  // 支持科学计数法
  return new BigDecimal(valueStr.trim()).doubleValue();
  }
  catch (Exception e)
  {
  return defaultValue;
  }
  }
  /*
- 转换为int
- 如果给定的值为null，或者转换失败，返回默认值null
- 转换失败不会报错
- @param value 被转换的值
- @return 结果
  */
  public Integer toInt(Object value)
  {
  return toInt(value, null);
  }
/**
- 转换为int
- 如果给定的值为空，或者转换失败，返回默认值
- 转换失败不会报错
- @param value 被转换的值
- @param defaultValue 转换错误时的默认值
- @return 结果
  */
  public Integer toInt(Object value, Integer defaultValue)
  {
  if (value == null)
  {
  return defaultValue;
  }
  if (value instanceof Integer)
  {
  return (Integer) value;
  }
  if (value instanceof Number)
  {
  return ((Number) value).intValue();
  }
  final String valueStr = toStr(value, null);
  if (StringUtils.isEmpty(valueStr))
  {
  return defaultValue;
  }
  try
  {
  return Integer.parseInt(valueStr.trim());
  }
  catch (Exception e)
  {
  return defaultValue;
  }
  }
public String DateToStr(String format, Date date)
{
return new SimpleDateFormat(format).format(date);
}

/**
- 增加 LocalDateTime ==> Date
  */
  public Date toDate(LocalDateTime temporalAccessor)
  {
  ZonedDateTime zdt = temporalAccessor.atZone(ZoneId.systemDefault());
  return Date.from(zdt.toInstant());
  }
/**
- 增加 LocalDate ==> Date
  */
  public Date toDate(LocalDate temporalAccessor)
  {
  LocalDateTime localDateTime = LocalDateTime.of(temporalAccessor, LocalTime.of(0, 0, 0));
  ZonedDateTime zdt = localDateTime.atZone(ZoneId.systemDefault());
  return Date.from(zdt.toInstant());
  }
  }