java list导出excel

pom

  <!--根据excel模板生成excel start-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.16</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.16</version>
        </dependency>

注解类ExcelTitle

/**
 * excel标题头注解，当Java属性没有使用此注解，则默认使用Java属性作为标题。
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelTitle {
    String value();
}

时间处理

public interface ExcelValueFormatter {
    Object formatValue(Class<?> returnType, Object value);
}

DateValueFormatter类

import lombok.AllArgsConstructor;
import lombok.Data;

import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.Date;

/**
 * @Date 2023/3/1
 * @Time 17:40
 * @Description No Description
 */
@Data
@AllArgsConstructor
public class DateValueFormatter implements ExcelValueFormatter {

    private String dateFormat;

    @Override
    public Object formatValue(Class<?> returnType, Object value) {
        if (returnType.equals(Date.class)) {
            return DateTimeFormatter.ofPattern(dateFormat)
                    .format(toLocalDateTime((Date) value));
        } else {
            return value;
        }
    }

    private static LocalDateTime toLocalDateTime(Date date) {
        Instant instant = date.toInstant();
        ZoneId zoneId = ZoneId.systemDefault();
        return instant.atZone(zoneId).toLocalDateTime();
    }
}

ExcelWriterUtil工具类

package kf.buss.intercity.serve.util;

import com.demo.lemon.serve.biz.DateValueFormatter;
import com.demo.lemon.common.aspect.ExcelTitle;
import com.demo.lemon.common.aspect.ExcelValueFormatter;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author 
 * @Date 2023/3/1
 * @Time 17:30
 * @Description No Description
 */
@Slf4j
public class ExcelWriterUtil {

        private static ThreadLocal<ExcelValueFormatter> valueFormatter = ThreadLocal
                .withInitial(() -> new DateValueFormatter("yyyy-MM-dd"));

        public static void setExcelValueFormatter(ExcelValueFormatter excelValueFormatter) {
            valueFormatter.set(excelValueFormatter);
        }

        public static <E> void writeToExcel(List<E> list, Class<E> clazz, String fileName)
                throws InvocationTargetException, IllegalAccessException {
            @SuppressWarnings("MismatchedQueryAndUpdateOfCollection")
            List<Object[]> dataList = new ArrayList<>();
            Map<String, Method> fieldMethodMap = buildFieldMethodMap(clazz);
            Map<String, String> fieldTitleMap = buildFieldTitleMap(clazz, fieldMethodMap);
            List<Map.Entry<String, Method>> methodEntrySet = new ArrayList<>(fieldMethodMap.entrySet());
            int addMark = 0;
            int itemSize = fieldTitleMap.size();
      String[]   titleArr = new String[itemSize];
            for (E obj : list) {
                Object[] item = new Object[itemSize];
                for (int i = 0; i < methodEntrySet.size(); i++) {
                    Map.Entry<String, Method> methodEntry = methodEntrySet.get(i);
                    String field = methodEntry.getKey();
                    if (addMark < itemSize) {
                        titleArr[addMark] = fieldTitleMap.get(field);
                        addMark++;
                    }
                    Method method = methodEntry.getValue();
                    Object value = formatValue(method, obj, valueFormatter.get());
                    if (value != null) {
                        item[i] = value;
                    }
                }
                dataList.add(item);
            }
            writeObjectToExcel(dataList, titleArr, fileName);
        }

        private static Object formatValue(Method method, Object obj,
                                          ExcelValueFormatter excelValueFormatter)
                throws InvocationTargetException, IllegalAccessException {
            Object value = method.invoke(obj);
            if (value == null) {
                return null;
            }
            if(excelValueFormatter == null) {
                return value;
            }
            Class<?> returnType = method.getReturnType();
            return excelValueFormatter.formatValue(returnType, value);
        }

        private static <E> Map<String, Method> buildFieldMethodMap(Class<E> clazz) {
            List<Method> getMethods = Arrays.stream(clazz.getMethods())
                    .filter(
                            method -> method.getName().startsWith("get") && !method.getName().equals("getClass"))
                    .collect(
                            Collectors.toList());
            Map<String, Method> fieldMethodMap = new LinkedHashMap<>();
            for (Method getMethod : getMethods) {
                String m = getMethod.getName().replace("get", "");
                String field = m.substring(0, 1).toLowerCase() + m.substring(1);
                fieldMethodMap.put(field, getMethod);
            }
            return fieldMethodMap;
        }

        public static <E> Field[] getAllFields(Class<E> clazz){
            List<Field> fieldList = new ArrayList<>();
            while (clazz != null){
                fieldList.addAll(new ArrayList<>(Arrays.asList(clazz.getDeclaredFields())));
                clazz = (Class<E>) clazz.getSuperclass();
            }
            Field[] fields = new Field[fieldList.size()];
            fieldList.toArray(fields);
            return fields;
        }

        private static <E> Map<String, String> buildFieldTitleMap(Class<E> clazz,
                                                                  Map<String, Method> fieldMethodMap) {
            Map<String, String> fieldTitleMap = new LinkedHashMap<>();
            Field[] fields = getAllFields(clazz);
            Arrays.stream(fields).forEach(field -> {
                if (fieldMethodMap.containsKey(field.getName())) {
                    ExcelTitle excelTitle = field.getAnnotation(ExcelTitle.class);
                    String title = excelTitle == null ? field.getName() : excelTitle.value();
                    fieldTitleMap.put(field.getName(), title);
                }
            });
            return fieldTitleMap;
        }

        private static void writeObjectToExcel(List<Object[]> list, String[]
                excelTitle, String fileName) {
            //在内存中创建Excel文件
            Workbook workbook;
            if (fileName.endsWith("xls")) {
                workbook = new HSSFWorkbook();
            } else if (fileName.endsWith("xlsx")) {
                workbook = new XSSFWorkbook();
            } else {
                throw new IllegalArgumentException("fileName not legal");
            }
            Sheet sheet = workbook.createSheet();
            //标题行
            Row titleRow = sheet.createRow(0);
            for (int i = 0; i < excelTitle.length; i++) {
                titleRow.createCell(i).setCellValue(excelTitle[i]);
            }
            //创建数据行并写入值
            for (Object[] dataArr : list) {
                int lastRowNum = sheet.getLastRowNum();
                Row dataRow = sheet.createRow(lastRowNum + 1);
                for (int i = 0; i < dataArr.length; i++) {
                    Cell cell = dataRow.createCell(i);
                    Object cellValue = dataArr[i];
                    if(cellValue != null) {
                        setCellValue(cellValue, cell);
                    }
                }
            }
            //创建输出流对象
            FileOutputStream outputStream = null;
            try {
                outputStream = new FileOutputStream(new File(fileName));
            } catch (FileNotFoundException e) {
                log.error("file not found", e);
            }
            try {
                workbook.write(outputStream);
            } catch (IOException e) {
                log.error("write to file failed", e);
            } finally {
                if (outputStream != null) {
                    try {
                        outputStream.close();
                    } catch (IOException ignore) {
                    }
                }
            }
        }

        private static void setCellValue(Object cellValue, Cell cell) {
            if (cellValue instanceof Boolean) {
                cell.setCellValue((boolean) cellValue);
            } else if (cellValue instanceof String) {
                cell.setCellValue(cellValue.toString());
            } else if (cellValue instanceof Double || cellValue instanceof Integer
                    || cellValue instanceof Long) {
                cell.setCellValue(Double.valueOf(cellValue.toString()));
            } else if (cellValue instanceof Date) {
                cell.setCellValue((Date) cellValue);
            } else if (cellValue instanceof Calendar) {
                cell.setCellValue((Calendar) cellValue);
            } else if (cellValue instanceof RichTextString) {
                cell.setCellValue((RichTextString) cellValue);
            } else {
                cell.setCellValue(cellValue.toString());
            }
        }




    }

实体User

@Data
public class User{



    @ExcelTitle(value = "编号")
    private String id;


    @ExcelTitle(value = "名称")
    private String name;

    @ExcelTitle(value = "年纪")
    private String age;
    
}

使用：

 public static void main(String[] args) {
        try {
            List<User> list = new ArrayList<>();
            ExcelWriterUtil.writeToExcel(list,User.class,"C:\\Users\\Default\\1.xlsx");
        } catch (Exception e) {

            log.info(e.toString());
        }
    }