java excle 函数 java excelutil

转载

feiry 2024-08-26 16:50:54

文章标签 java excle 函数 java List apache 文章分类 Java 后端开发

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());
 }
 }

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。