Excel导入和导出

一、添加需要用到的依赖

<!-- 实体类工具 -->
<dependency>
	<groupId>org.projectlombok</groupId>
	<artifactId>lombok</artifactId>
	<optional>true</optional>
</dependency>

<!-- excel工具 -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>4.1.2</version>
</dependency>

<!-- Alibaba Fastjson -->
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>fastjson</artifactId>
	<version>1.2.74</version>
</dependency>

二、创建注解类

import java.lang.annotation.*;

/**
 * excel注解
 *
 * @author 胡晓波
 * @version V3.1.0
 * @copyright 微奇点网络工作室
 * @date 2022-11-03 17:00:03
 */
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE, ElementType.FIELD})
public @interface Excel {

    /**
     * 导入导出数据的名称
     * @return
     */
    String value();

    /**
     * 导出时每行数据是否有序号列（实体类上才有效）
     * @return
     */
    boolean xh() default false;

    /**
     * 导出时列顺序
     * @return
     */
    int sort() default 0;

    /**
     * 导出时需要格式化的数据
     * @return
     */
    String format() default "";

    /**
     * 导出是否忽视
     * @return
     */
    boolean hidden() default false;
}

三、异常类

/**
 * excel异常类
 *
 * @author 胡晓波
 * @version V3.1.0
 * @copyright 微奇点网络工作室
 * @date 2022-11-03 14:58:32
 */
public class ExcelException extends Exception {

    public ExcelException() {
        super();
    }

    public ExcelException(String message) {
        super(message);
    }
}

四、需要用到的时间工具类和json工具类

import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Date;

/**
 * 时间类型工具
 *
 * @author 胡晓波
 * @version V3.1.0
 * @copyright 微奇点网络工作室
 * @date 2022-11-04 15:28:06
 */
public class DateUtil {

    /**
     * Date类型转字符串
     * @param date
     * @param pattern
     * @return
     * @throws Exception
     */
    public static String dateFormat (Date date, String pattern) throws Exception {
        if (StringUtil.isEmpty(pattern)) {
            throw new Exception("转换格式为空");
        }
        if (date == null) {
            date = new Date();
        }
        SimpleDateFormat formatter = new SimpleDateFormat(pattern);
        String dateString = formatter.format(date);
        return dateString;
    }

    /**
     * LocalDateTime类型进行格式化输出（返回类型：String）
     * @param date
     * @return
     */
    public static String dateFormat(LocalDateTime date) {
        DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String localTime = df.format(date);
        return localTime;
    }

    /**
     * LocalDate类型进行格式化输出（返回类型：String）
     * @param date
     * @return
     */
    public static String dateFormat(LocalDate date) {
        DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        String localTime = df.format(date);
        return localTime;
    }
}

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;

import java.util.List;

/**
 * json转换工具
 *
 * @author 胡晓波
 * @version V3.1.0
 * @copyright 微奇点网络工作室
 * @date 2022-11-04 15:26:34
 */
public class JsonUtil {


    /**
     * 功能描述：把java对象转换成JSON数据
     * @param object java对象
     * @return JSON数据
     */
    public static String getObjectToString(Object object) {
        return JSON.toJSONString(object, SerializerFeature.WriteMapNullValue);
    }

    /**
     * 功能描述：把JSON数据转换成指定的java对象列表
     * @param dto dto对象
     * @param clazz 指定的java对象
     * @return List<T>
     */
    public static <T> List<T> getJsonToList(Object dto, Class<T> clazz) {
        return JSON.parseArray(getObjectToString(dto), clazz);
    }


}

五、导入导出excel工具类具体代码

import com.wx.annotation.Excel;
import com.wx.exception.ExcelException;
import lombok.Builder;
import lombok.Data;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;

/**
 * excel导入导出工具
 *
 * @author 胡晓波
 * @version V3.1.0
 * @copyright 微奇点网络工作室
 * @date 2022-11-04 09:43:27
 */
public class DoExcel {

    /**
     * excel数据导入
     * @param is excel 导入数据
     * @param oc 实体类类型
     * @param sheetIndex 工作簿下标
     * @param titleRowIndex 标题行下标
     * @param <T>
     * @return 实体类集合
     * @throws Exception
     */
    public static <T> List<T> importExcel (InputStream is, Class<T> oc, Integer sheetIndex, Integer titleRowIndex) throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook(is);
        XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
        // 标题行
        XSSFRow row = sheet.getRow(titleRowIndex ++);
        List<String> titleText = new ArrayList<>();
        for (Cell cell : row) {
            titleText.add(cell.getStringCellValue());
        }
        Field[] fields = oc.getDeclaredFields();
        Map<String, ExpectExcelVo> titleMap = new HashMap<>(15);
        for (Field field : fields) {
            Excel _excel = field.getAnnotation(Excel.class);
            if (_excel == null) {
                continue;
            }
            titleMap.put(_excel.value(), ExpectExcelVo.builder()
                    .name(field.getName()).type(field.getType().getName()).build());
        }
        if (titleMap.size() == 0) {
            throw new ExcelException("类中没有@Excel注解");
        }
        List<Map<String, Object>> list = new ArrayList<>();
        for (int i = titleRowIndex; i <= sheet.getLastRowNum(); i++) {
            row = sheet.getRow(i);
            Map<String, Object> map = new HashMap<>(15);
            for (int j = 0; j < titleText.size(); j++) {
                String s = titleText.get(j);
                ExpectExcelVo expectExcelVo = titleMap.get(s);
                if (expectExcelVo == null) {
                    continue;
                }
                XSSFCell cell = row.getCell(j);
                map.put(expectExcelVo.getName(), cell.getStringCellValue());
            }
            list.add(map);
        }
        return JsonUtil.getJsonToList(list, oc);
    }

    /**
     * 导出数据为excel
     * @param list
     * @param oc
     * @return excel流
     * @throws Exception
     */
    public static XSSFWorkbook expectExcel (List<Object> list, Class<?> oc) throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("import");
        Integer rowIndex = 0;
        Integer cellIndex = 0;

        Integer header = header(oc, sheet, rowIndex);
        if (header == 1) {
            rowIndex ++;
        }else if (header == 2) {
            rowIndex ++;
            cellIndex ++;
        }
        ArrayList<String> fieldNames = title(oc, sheet, rowIndex, cellIndex);
        rowIndex ++;
        content(list, fieldNames, sheet, rowIndex, cellIndex);
        return workbook;
    }

    /**
     * 生成表的头部
     * @param oc
     * @param sheet
     * @param rowIndex
     */
    private static Integer header (Class<?> oc, XSSFSheet sheet, Integer rowIndex) {
        Excel header = oc.getAnnotation(Excel.class);
        if (header != null) {
            XSSFRow row = sheet.createRow(rowIndex++);
            XSSFCell cell = row.createCell(0);
            cell.setCellValue(header.value());
            if (header.xh()) {
                return 2;
            }
            return 1;
        }
        return 0;
    }

    /**
     * 生成标题
     * @param oc
     * @param sheet
     * @param rowIndex
     * @param cellIndex
     * @return
     * @throws Exception
     */
    private static ArrayList<String> title (Class<?> oc, XSSFSheet sheet, Integer rowIndex, Integer cellIndex) throws Exception {
        ArrayList<String> fieldNames = new ArrayList<>();
        Field[] fields = oc.getDeclaredFields();
        if (fields.length > 0) {
            for (Field field : fields) {
                field.setAccessible(true);
                Excel title = field.getAnnotation(Excel.class);
                if (title == null || title.hidden()) {
                    continue;
                }
                fieldNames.add(field.getName() + "~" + title.sort() + "~" + title.value());
            }
            fieldNames.sort((o1, o2) -> {
                int $1 = Integer.parseInt(o1.split("~")[1]);
                int $2 = Integer.parseInt(o2.split("~")[1]);
                return $1 - $2;
            });
        }
        if (fieldNames.size() == 0) {
            throw new ExcelException("实体类中属性没有@Excel注解");
        }else {
            XSSFRow row = sheet.createRow(rowIndex++);
            XSSFCell xh = row.createCell(0);
            xh.setCellValue("序号");
            for (int i = 0; i < fieldNames.size(); i++) {
                String[] split = fieldNames.get(i).split("~");
                XSSFCell cell = row.createCell(i+cellIndex);
                cell.setCellValue(split[2]);
                fieldNames.set(i, split[0]);
            }
        }
        return fieldNames;
    }

    /**
     * 生成数据内容
     * @param list
     * @param fieldNames
     * @param sheet
     * @param rowIndex
     * @param cellIndex
     * @throws Exception
     */
    private static void content (List<Object> list, ArrayList<String> fieldNames, XSSFSheet sheet, Integer rowIndex, Integer cellIndex) throws Exception {
        for (int i = 0; i < list.size(); i++) {
            Object o = list.get(i);
            Class<?> ec = o.getClass();
            XSSFRow row = sheet.createRow(rowIndex++);
            XSSFCell xh = row.createCell(0);
            xh.setCellValue((i + 1) + "");
            int colIndex = cellIndex;
            for (String fieldName : fieldNames) {
                Field field = ec.getDeclaredField(fieldName);
                field.setAccessible(true);
                Excel column = field.getAnnotation(Excel.class);
                XSSFCell cell = row.createCell(colIndex++);
                String typeName = field.getType().getName();
                String[] split = typeName.split("\\.");
                switch (split[split.length - 1]) {
                    case "Date":
                        cell.setCellValue(DateUtil.dateFormat((Date) field.get(o), column.format()));
                        break;
                    case "LocalDateTime":
                        cell.setCellValue(DateUtil.dateFormat((LocalDateTime) field.get(o)));
                        break;
                    case "LocalDate":
                        cell.setCellValue(DateUtil.dateFormat((LocalDate) field.get(o)));
                        break;
                    default: cell.setCellValue(field.get(o).toString());
                }

            }
        }
    }
}

@Data
@Builder
class ExpectExcelVo {
    private String type;
    private String name;
}

六、测试类及测试实体

import com.wx.annotation.Excel;
import com.wx.utils.DoExcel;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 测试类
 *
 * @author 胡晓波
 * @version V3.1.0
 * @copyright 微奇点网络工作室
 * @date 2022-11-04 15:30:34
 */
public class TestMain {
    public static void main(String[] args) throws Exception {
        List<Object> list = new ArrayList<Object>(){{
            add(ExcelTestVo.builder()
                    .no(9527).name("唐白虎").sex('男').birthday(new Date())
                    .grad("一年级").school("加州大学").createTime(new Date()).build());
            add(ExcelTestVo.builder()
                    .no(9528).name("秋雅").sex('女').birthday(new Date())
                    .grad("一年级").school("加州大学").createTime(new Date()).build());
        }};
        XSSFWorkbook workbook = DoExcel.expectExcel(list, ExcelTestVo.class);
        // 创建文件 或者直接用相应输出流输出结果（需要配置请求头）
        File file = new File("D://out/tmp.xlsx");
        file.createNewFile();
        FileOutputStream fos = new FileOutputStream(file);
        workbook.write(fos);
        workbook.close();
        fos.close();

        FileInputStream fis = new FileInputStream(file);
        List<ExcelTestVo> result = DoExcel.importExcel(fis, ExcelTestVo.class, 0, 1);
        System.out.println(result);
    }
}

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Excel(value = "excel导入导出测试实体", xh = true)
class ExcelTestVo {

    @Excel(value = "编号", sort = 1)
    private Integer no;

    @Excel(value = "姓名", sort = 2)
    private String name;

    @Excel(value = "性别", sort = 3)
    private Character sex;

    @Excel(value = "生日", sort = 4, hidden = true, format = "yyyy-MM-dd")
    private Date birthday;

    @Excel(value = "年级", sort = 5)
    private String grad;

    @Excel(value = "学校", sort = 6)
    private String school;

    @Excel(value = "创建时间", sort = 7, format = "yyyy-MM-dd HH:mm:ss")
    private Date createTime;

}

七、测试结果

导入结果

 导出结果