Excel文件导入&导出工具类

已于 2023-07-02 01:48:19 修改
阅读量1.6k 收藏 2
点赞数
分类专栏： JAVA 实用功能 文章标签： excel java POI
于 2023-07-01 20:22:36 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/java_t_t/article/details/131494321
版权

之前写过一篇导出Excel文件的通用类的文章，当时只实现了导出数据到Excel的功能，这次在上次的基础上，增加从Excel读取数据到List的功能，依旧使用POI读取Excel文件。

代码实现

1.在pom.xml中添加POI相关依赖

        <!-- POI -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.2.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.2</version>
        </dependency>

2.Excel字段注解类

package com.example.study.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelField {
    /**
     * 排序，该字段值越小，排序越前
     */
    int ordinal() default 0;

    /**
     * 列宽，取值范围0-255，默认16，当列宽<0或>255时，使用默认列宽
     */
    int columnWidth() default 16;

    /**
     * 字段名
     */
    String columnName();

    /**
     * 时间类型的格式化，目前支持Date, LocalDate, LocalTime, LocalDateTime四种时间类型
     */
    String format() default "yyyy-MM-dd HH:mm:ss";
}

3.Excel导入&导出工具类

package com.example.study.util;

import com.example.study.annotation.ExcelField;
import lombok.Builder;
import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.Assert;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAccessor;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;

/**
 * 导出Excel
 */
@Slf4j
public class ExcelUtil {
    /**
     * 读取excel文件内容到List中
     *
     * @param clazz 数据实体类的class
     * @param excel excel文件
     * @param <T>   数据实体类
     * @return 包含指定工作表内容的数据的list
     */
    public static <T> List<T> read(Class<? extends T> clazz, String excel) {
        return read(clazz, excel, null, 0);
    }

    /**
     * 读取excel文件内容到List中
     *
     * @param clazz 数据实体类的class
     * @param excel excel文件
     * @param <T>   数据实体类
     * @return 包含指定工作表内容的数据的list
     */
    public static <T> List<T> read(Class<? extends T> clazz, File excel) {
        return read(clazz, excel, null, 0);
    }

    /**
     * 读取excel文件内容到List中
     *
     * @param clazz       数据实体类的class
     * @param inputStream excel文件
     * @param <T>         数据实体类
     * @return 包含指定工作表内容的数据的list
     */
    public static <T> List<T> read(Class<? extends T> clazz, InputStream inputStream) {
        return read(clazz, inputStream, null, 0);
    }

    /**
     * 读取excel文件内容到List中
     *
     * @param clazz     数据实体类的class
     * @param excel     excel文件
     * @param sheetName 工作表名（默认取第一个工作表）
     * @param <T>       数据实体类
     * @return 包含指定工作表内容的数据的list
     */
    public static <T> List<T> read(Class<? extends T> clazz, String excel, String sheetName, Integer titleRowIndex) {
        Assert.hasText(excel, "excel文件不能为空");
        return read(clazz, new File(excel), sheetName, titleRowIndex);
    }

    /**
     * 读取excel文件内容到List中
     *
     * @param clazz     数据实体类的class
     * @param excel     excel文件
     * @param sheetName 工作表名（默认取第一个工作表）
     * @param <T>       数据实体类
     * @return 包含指定工作表内容的数据的list
     */
    public static <T> List<T> read(Class<? extends T> clazz, File excel, String sheetName, Integer titleRowIndex) {
        Assert.notNull(excel, "excel文件不能为空");
        try {
            InputStream inputStream = new FileInputStream(excel);
            return read(clazz, inputStream, sheetName, titleRowIndex);
        } catch (FileNotFoundException exception) {
            log.error("excel file not exists");
            throw new IllegalArgumentException("excel文件不存在");
        }
    }

    /**
     * 读取excel文件内容到List中
     *
     * @param clazz         数据实体类的class
     * @param inputStream   excel文件
     * @param sheetName     工作表名（默认取第1个工作表）
     * @param titleRowIndex 标题所在行索引（起始地址为0，默认取第1行）
     * @param <T>           数据实体类
     * @return 包含指定工作表内容的数据的list
     */
    public static <T> List<T> read(Class<? extends T> clazz, InputStream inputStream, String sheetName, Integer titleRowIndex) {
        Assert.notNull(clazz, "实体类不能为空");
        Assert.notNull(inputStream, "excel文件不能为空");
        if (titleRowIndex == null || titleRowIndex < 0) {
            titleRowIndex = 0;
        }
        try (XSSFWorkbook workbook = new XSSFWorkbook(inputStream)) {
            XSSFSheet sheet;
            if (StringUtils.hasText(sheetName)) {
                sheet = workbook.getSheet(sheetName);
            } else {
                sheet = workbook.getSheetAt(0);
            }
            Assert.notNull(sheet, "指定或默认的sheet不存在");
            Map<Integer, Field> columnIndexFieldMap = readTitle(clazz, sheet, titleRowIndex);
            Assert.notEmpty(columnIndexFieldMap, "在excel中未找到有效数据");
            return readData(clazz, sheet, titleRowIndex, columnIndexFieldMap);
        } catch (IOException | InstantiationException | IllegalAccessException exception) {
            log.error("throw Exception when read excel:{}", exception.getMessage());
            throw new IllegalArgumentException(exception.getMessage());
        }
    }

    /**
     * 读取excel数据
     *
     * @param clazz               数据实体类的class
     * @param sheet               工作表对象
     * @param titleRowIndex       标题所在行索引
     * @param columnIndexFieldMap 列索引与数据实体类字段Field的映射map
     * @param <T>                 数据实体类
     * @return excel数据
     * @throws IllegalAccessException 没有设置属性的权限
     * @throws InstantiationException 实例化对象失败
     */
    private static <T> List<T> readData(Class<? extends T> clazz, XSSFSheet sheet, Integer titleRowIndex, Map<Integer, Field> columnIndexFieldMap) throws IllegalAccessException, InstantiationException {
        List<T> list = new ArrayList<>();
        for (int rowIndex = titleRowIndex + 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            XSSFRow row = sheet.getRow(rowIndex);
            T data = clazz.newInstance();
            for (Map.Entry<Integer, Field> entry : columnIndexFieldMap.entrySet()) {
                Integer colIndex = entry.getKey();
                Field field = entry.getValue();
                Object value = getCell(row, colIndex, field.getType());
                field.setAccessible(true);
                field.set(data, value);
            }
            list.add(data);
        }
        return list;
    }

    /**
     * 读取excel标题，获取列索引与数据实体类字段Field的映射map
     *
     * @param clazz         数据实体类的class
     * @param sheet         工作表对象
     * @param titleRowIndex 标题所在行索引
     * @return 列索引与数据实体类字段Field的映射map
     */
    private static Map<Integer, Field> readTitle(Class<?> clazz, XSSFSheet sheet, Integer titleRowIndex) {
        List<FieldProperties> properties = getFieldProperties(clazz);
        Map<String, Field> columnNameFieldMap = new HashMap<>();
        for (FieldProperties property : properties) {
            String columnName = property.getExcelField().columnName();
            columnNameFieldMap.put(columnName, property.getField());
        }
        Map<Integer, Field> columnIndexFieldMap = new HashMap<>();
        XSSFRow row = sheet.getRow(titleRowIndex);
        Assert.notNull(row, "指定或默认的标题行不存在");
        for (int columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++) {
            String titleName = getCell(row, columnIndex, String.class);
            Field field = columnNameFieldMap.get(titleName);
            if (field != null) {
                columnIndexFieldMap.put(columnIndex, field);
            }
        }
        return columnIndexFieldMap;
    }

    /**
     * 获取单元格内容，单元格内容转为对应字段的类型
     *
     * @param row      行
     * @param colIndex 列索引
     * @param clazz    对应字段类型的class
     * @param <T>      对应字段类型
     * @return 已转为对应字段类型的单元格内容
     */
    private static <T> T getCell(XSSFRow row, int colIndex, Class<? extends T> clazz) {
        XSSFCell cell = row.getCell(colIndex);
        if (cell == null) {
            cell = row.createCell(colIndex);
        }
        String name = clazz.getName();
        Object cellValue;
        switch (name) {
            case "byte":
            case "java.lang.Byte":
                cellValue = (byte) cell.getNumericCellValue();
                break;
            case "short":
            case "java.lang.Short":
                cellValue = (short) cell.getNumericCellValue();
                break;
            case "int":
            case "java.lang.Integer":
                cellValue = (int) cell.getNumericCellValue();
                break;
            case "long":
            case "java.lang.Long":
                cellValue = (long) cell.getNumericCellValue();
                break;
            case "float":
            case "java.lang.Float":
                cellValue = (float) cell.getNumericCellValue();
                break;
            case "double":
            case "java.lang.Double":
                cellValue = cell.getNumericCellValue();
                break;
            case "char":
            case "java.lang.Character":
                cellValue = (char) cell.getNumericCellValue();
                break;
            case "boolean":
            case "java.lang.Boolean":
                cellValue = cell.getBooleanCellValue();
                break;
            case "java.util.Date":
                cellValue = cell.getDateCellValue();
                break;
            case "java.time.LocalDate":
                cellValue = cell.getLocalDateTimeCellValue() == null ? null : cell.getLocalDateTimeCellValue().toLocalDate();
                break;
            case "java.time.LocalTime":
                cellValue = cell.getLocalDateTimeCellValue() == null ? null : cell.getLocalDateTimeCellValue().toLocalTime();
                break;
            case "java.time.LocalDateTime":
                cellValue = cell.getLocalDateTimeCellValue();
                break;
            case "java.lang.String":
                cellValue = cell.toString().trim();
                break;
            case "java.math.BigInteger":
                String strValue = cell.toString().trim();
                int dotIndex = strValue.indexOf('.');
                if (dotIndex >= 0) {
                    strValue = strValue.substring(0, dotIndex);
                }
                cellValue = StringUtils.hasText(strValue) ? new BigInteger(strValue) : null;
                break;
            case "java.math.BigDecimal":
                cellValue = StringUtils.hasText(cell.toString().trim()) ? new BigDecimal(cell.toString().trim()) : null;
                break;
            default:
                cellValue = cell.toString().trim();
        }
        return (T) cellValue;
    }

    /**
     * 导出excel文件
     *
     * @param collection 数据集合
     * @param <T>        数据实体类
     * @return workbook对象
     */
    public static <T> XSSFWorkbook write(Collection<T> collection) {
        return write(collection, null, null);
    }

    /**
     * 导出excel文件
     *
     * @param collection 数据集合
     * @param clazz      数据实体类的class
     * @param <T>        数据实体类
     * @return workbook对象
     */
    public static <T> XSSFWorkbook write(Collection<T> collection, Class<?> clazz) {
        return write(collection, clazz, null);
    }

    /**
     * 导出excel文件
     *
     * @param collection 数据集合
     * @param sheetName  工作表表名
     * @param <T>        数据实体类
     * @return workbook对象
     */
    public static <T> XSSFWorkbook write(Collection<T> collection, String sheetName) {
        return write(collection, null, sheetName);
    }

    /**
     * 导出excel文件
     *
     * @param collection 数据集合
     * @param clazz      数据实体类的class
     * @param sheetName  工作表表名
     * @param <T>        数据实体类
     * @return workbook对象
     */
    public static <T> XSSFWorkbook write(Collection<T> collection, Class<?> clazz, String sheetName) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet(StringUtils.hasText(sheetName) ? sheetName : "Sheet0");
        XSSFCellStyle titleStyle = createCellStyle(workbook);
        List<FieldProperties> properties = null;
        if (clazz != null) {
            properties = getFieldProperties(clazz);
            writeTitle(sheet, properties, titleStyle);
        }
        if (collection == null || collection.isEmpty()) {
            return workbook;
        }
        if (properties == null) {
            Optional<T> any = collection.stream().filter(o -> o != null).findAny();
            if (!any.isPresent()) {
                return workbook;
            }
            properties = getFieldProperties(any.get().getClass());
            writeTitle(sheet, properties, titleStyle);
        }
        try {
            writeData(sheet, properties, collection);
        } catch (IllegalAccessException exception) {
            log.error("throw IllegalAccessException when writeData:{}", exception.getMessage());
        }
        return workbook;
    }

    /**
     * 返回表头的单元格格式
     *
     * @param workbook workbook对象
     * @return 绿色背景的单元格格式
     */
    private static XSSFCellStyle createCellStyle(XSSFWorkbook workbook) {
        XSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return titleStyle;
    }

    /**
     * 写入数据
     *
     * @param sheet      工作表
     * @param properties 表头
     * @param collection 数据
     * @param <T>        数据实体类
     * @throws IllegalAccessException 访问数据字段值时无访问权限
     */
    private static <T> void writeData(XSSFSheet sheet, List<FieldProperties> properties, Collection<T> collection) throws IllegalAccessException {
        int rowIndex = 1;
        XSSFRow row;
        for (T element : collection) {
            if (element == null) {
                continue;
            }
            row = sheet.createRow(rowIndex++);
            for (int index = 0; index < properties.size(); index++) {
                FieldProperties property = properties.get(index);
                Object value = property.getField().get(element);
                if (value == null) {
                    continue;
                }
                String type = property.getField().getType().getSimpleName();
                switch (type) {
                    case "Date":
                        SimpleDateFormat format = new SimpleDateFormat(property.getExcelField().format());
                        row.createCell(index).setCellValue(format.format(value));
                        break;
                    case "LocalDate":
                    case "LocalTime":
                    case "LocalDateTime":
                        DateTimeFormatter formatter = DateTimeFormatter.ofPattern(property.getExcelField().format());
                        row.createCell(index).setCellValue(formatter.format((TemporalAccessor) value));
                        break;
                    default:
                        row.createCell(index).setCellValue(value.toString());
                }
            }
        }
    }

    /**
     * 写入表头
     *
     * @param sheet      工作表
     * @param properties 表头
     * @param titleStyle 表头格式
     */
    private static void writeTitle(XSSFSheet sheet, List<FieldProperties> properties, XSSFCellStyle titleStyle) {
        XSSFRow row = sheet.createRow(0);
        for (int index = 0; index < properties.size(); index++) {
            ExcelField excelField = properties.get(index).getExcelField();
            String columnName = excelField.columnName();
            XSSFCell cell = row.createCell(index);
            cell.setCellValue(columnName);
            cell.setCellStyle(titleStyle);
            // 设置列宽
            int columnWidth = excelField.columnWidth();
            columnWidth = columnWidth < 0 || columnWidth > 255 ? 16 : columnWidth;
            sheet.setColumnWidth(index, columnWidth * 256);
        }
    }

    /**
     * 获取表头字段
     *
     * @param clazz 数据实体类的class
     * @return 排好序的表头字段
     */
    private static List<FieldProperties> getFieldProperties(Class<?> clazz) {
        Field[] declaredFields = clazz.getDeclaredFields();
        List<FieldProperties> properties = new ArrayList<>();
        for (Field declaredField : declaredFields) {
            ExcelField excelField = declaredField.getAnnotation(ExcelField.class);
            if (excelField == null) {
                continue;
            }
            declaredField.setAccessible(true);
            FieldProperties property = FieldProperties.builder().excelField(excelField).field(declaredField).build();
            properties.add(property);
        }
        properties.sort((o1, o2) -> o1.getExcelField().ordinal() - o2.getExcelField().ordinal());
        return properties;
    }

    @Getter
    @Setter
    @Builder
    private static class FieldProperties {
        private ExcelField excelField;
        private Field field;
    }
}

功能验证

实体类:

package com.example.study.entity;


import com.example.study.annotation.ExcelField;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.fasterxml.jackson.databind.annotation.JsonDeserialize;
import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import com.fasterxml.jackson.datatype.jsr310.deser.LocalDateDeserializer;
import com.fasterxml.jackson.datatype.jsr310.deser.LocalDateTimeDeserializer;
import com.fasterxml.jackson.datatype.jsr310.deser.LocalTimeDeserializer;
import com.fasterxml.jackson.datatype.jsr310.ser.LocalDateSerializer;
import com.fasterxml.jackson.datatype.jsr310.ser.LocalDateTimeSerializer;
import com.fasterxml.jackson.datatype.jsr310.ser.LocalTimeSerializer;
import lombok.Getter;
import lombok.Setter;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.Date;

@Getter
@Setter
public class StudentEntity {
    @ExcelField(columnName = "Bolid")
    private Boolean bBolid;

    @ExcelField(columnName = "bolid")
    private boolean bolid;

    @ExcelField(columnName = "Bid")
    private Byte bBid;

    @ExcelField(columnName = "bid")
    private byte bid;

    @ExcelField(columnName = "Sid")
    private Short bSid;

    @ExcelField(columnName = "sid")
    private short sid;

    @ExcelField(columnName = "Iid")
    private Integer bIid;

    @ExcelField(columnName = "iid")
    private int iid;

    @ExcelField(columnName = "Lid")
    private Long bLid;

    @ExcelField(columnName = "lid")
    private long lid;

    @ExcelField(columnName = "Fid")
    private Float bFid;

    @ExcelField(columnName = "fid")
    private float fid;

    @ExcelField(columnName = "Did")
    private Double bDid;

    @ExcelField(columnName = "did")
    private double did;

    @ExcelField(columnName = "Cid")
    private Character bCid;

    @ExcelField(columnName = "cid")
    private char cid;

    @ExcelField(columnName = "Bigid")
    private BigInteger Bigid;

    @ExcelField(columnName = "Decid")
    private BigDecimal Decid;

    @ExcelField(columnName = "id")
    private Integer id;

    @ExcelField(columnName = "生日")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date birthday;

    @ExcelField(columnName = "LD生日")
    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
    @JsonSerialize(using = LocalDateSerializer.class)
    @JsonDeserialize(using = LocalDateDeserializer.class)
    private LocalDate LdBirthday;

    @ExcelField(columnName = "LT生日")
    @JsonFormat(pattern = "HH:mm:ss", timezone = "GMT+8")
    @JsonSerialize(using = LocalTimeSerializer.class)
    @JsonDeserialize(using = LocalTimeDeserializer.class)
    private LocalTime LtBirthday;

    @ExcelField(columnName = "LDT生日")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    @JsonSerialize(using = LocalDateTimeSerializer.class)
    @JsonDeserialize(using = LocalDateTimeDeserializer.class)
    private LocalDateTime LdtBirthday;

    @ExcelField(columnName = "名字")
    private String name;

    @ExcelField(columnName = "性别")
    private String sex;
}

测试代码：

package com.example.study.test;

import com.example.study.entity.StudentEntity;
import com.example.study.util.ExcelUtil;
import com.fasterxml.jackson.databind.ObjectMapper;

import java.io.File;
import java.io.FileInputStream;
import java.util.List;

public class ExcelUtilTest {
    public static void main(String[] args) throws Exception {
        ObjectMapper mapper = new ObjectMapper();
        List<StudentEntity> read = ExcelUtil.read(StudentEntity.class, "F:\\tmp\\students.xlsx");
        System.out.println(mapper.writeValueAsString(read));
        read = ExcelUtil.read(StudentEntity.class, new File("F:\\tmp\\students.xlsx"));
        System.out.println(mapper.writeValueAsString(read));
        read = ExcelUtil.read(StudentEntity.class, new FileInputStream("F:\\tmp\\students.xlsx"));
        System.out.println(mapper.writeValueAsString(read));
        read = ExcelUtil.read(StudentEntity.class, "F:\\tmp\\students.xlsx", "sheet2", 1);
        System.out.println(mapper.writeValueAsString(read));
        read = ExcelUtil.read(StudentEntity.class, new File("F:\\tmp\\students.xlsx"), "sheet2", 1);
        System.out.println(mapper.writeValueAsString(read));
        read = ExcelUtil.read(StudentEntity.class, new FileInputStream("F:\\tmp\\students.xlsx"), "sheet2", 1);
        System.out.println(mapper.writeValueAsString(read));
    }
}

excel表内容如下：

sheet1（第一个工作表，左边和上边都没有空行，可使用默认参数）:

名字id生日性别BolidbolidBidbidSidsidIidiidLidlidFidfidDiddidCidcidBigidDecidLD生日LT生日LDT生日
张三11995/1/1男TRUEFALSE111111111116597111995/1/11995/1/11995/1/1
李四21995/1/2男FALSETRUE222222222226698221995/1/21995/1/21995/1/2
王冰冰31995/1/3女TRUEFALSE333333333336799331995/1/31995/1/31995/1/3

sheet2（第二个工作表，左边有四列空单元格，上边有一行空行，必须手动指定参数）:

名字id生日性别
张三11995/1/1男
李四21995/1/2男
王冰冰31995/1/3女

执行结果如下：

19:46:44.624 [main] DEBUG org.apache.poi.openxml4j.opc.PackageRelationshipCollection - Parsing relationship: /xl/_rels/workbook.xml.rels
19:46:44.624 [main] DEBUG org.apache.poi.openxml4j.opc.PackageRelationshipCollection - Parsing relationship: /_rels/.rels
[{"bolid":false,"bid":1,"sid":0,"iid":1,"lid":1,"fid":1.0,"did":1.0,"cid":"a","id":1,"birthday":"1995-01-01 00:00:00","LdBirthday":"1995-01-01","LtBirthday":"00:00:00","LdtBirthday":"1995-01-01 00:00:00","name":"张三","sex":"男","biid":1,"bfid":1.0,"bdid":1.0,"bbolid":true,"bcid":"A","blid":1,"decid":1.0,"bigid":1,"bbid":1,"ltBirthday":{"hour":0,"minute":0,"second":0,"nano":0},"ldtBirthday":{"month":"JANUARY","year":1995,"dayOfMonth":1,"hour":0,"minute":0,"monthValue":1,"nano":0,"second":0,"dayOfWeek":"SUNDAY","dayOfYear":1,"chronology":{"id":"ISO","calendarType":"iso8601"}},"ldBirthday":{"year":1995,"month":"JANUARY","dayOfMonth":1,"monthValue":1,"era":"CE","dayOfWeek":"SUNDAY","dayOfYear":1,"leapYear":false,"chronology":{"id":"ISO","calendarType":"iso8601"}},"bsid":1},{"bolid":true,"bid":2,"sid":0,"iid":2,"lid":2,"fid":2.0,"did":2.0,"cid":"b","id":2,"birthday":"1995-01-02 00:00:00","LdBirthday":"1995-01-02","LtBirthday":"00:00:00","LdtBirthday":"1995-01-02 00:00:00","name":"李四","sex":"男","biid":2,"bfid":2.0,"bdid":2.0,"bbolid":false,"bcid":"B","blid":2,"decid":2.0,"bigid":2,"bbid":2,"ltBirthday":{"hour":0,"minute":0,"second":0,"nano":0},"ldtBirthday":{"month":"JANUARY","year":1995,"dayOfMonth":2,"hour":0,"minute":0,"monthValue":1,"nano":0,"second":0,"dayOfWeek":"MONDAY","dayOfYear":2,"chronology":{"id":"ISO","calendarType":"iso8601"}},"ldBirthday":{"year":1995,"month":"JANUARY","dayOfMonth":2,"monthValue":1,"era":"CE","dayOfWeek":"MONDAY","dayOfYear":2,"leapYear":false,"chronology":{"id":"ISO","calendarType":"iso8601"}},"bsid":2},{"bolid":false,"bid":3,"sid":0,"iid":3,"lid":3,"fid":3.0,"did":3.0,"cid":"c","id":3,"birthday":"1995-01-03 00:00:00","LdBirthday":"1995-01-03","LtBirthday":"00:00:00","LdtBirthday":"1995-01-03 00:00:00","name":"王冰冰","sex":"女","biid":3,"bfid":3.0,"bdid":3.0,"bbolid":true,"bcid":"C","blid":3,"decid":3.0,"bigid":3,"bbid":3,"ltBirthday":{"hour":0,"minute":0,"second":0,"nano":0},"ldtBirthday":{"month":"JANUARY","year":1995,"dayOfMonth":3,"hour":0,"minute":0,"monthValue":1,"nano":0,"second":0,"dayOfWeek":"TUESDAY","dayOfYear":3,"chronology":{"id":"ISO","calendarType":"iso8601"}},"ldBirthday":{"year":1995,"month":"JANUARY","dayOfMonth":3,"monthValue":1,"era":"CE","dayOfWeek":"TUESDAY","dayOfYear":3,"leapYear":false,"chronology":{"id":"ISO","calendarType":"iso8601"}},"bsid":3}]
19:46:45.686 [main] DEBUG org.apache.poi.openxml4j.opc.PackageRelationshipCollection - Parsing relationship: /xl/_rels/workbook.xml.rels
19:46:45.686 [main] DEBUG org.apache.poi.openxml4j.opc.PackageRelationshipCollection - Parsing relationship: /_rels/.rels
[{"bolid":false,"bid":1,"sid":0,"iid":1,"lid":1,"fid":1.0,"did":1.0,"cid":"a","id":1,"birthday":"1995-01-01 00:00:00","LdBirthday":"1995-01-01","LtBirthday":"00:00:00","LdtBirthday":"1995-01-01 00:00:00","name":"张三","sex":"男","biid":1,"bfid":1.0,"bdid":1.0,"bbolid":true,"bcid":"A","blid":1,"decid":1.0,"bigid":1,"bbid":1,"ltBirthday":{"hour":0,"minute":0,"second":0,"nano":0},"ldtBirthday":{"month":"JANUARY","year":1995,"dayOfMonth":1,"hour":0,"minute":0,"monthValue":1,"nano":0,"second":0,"dayOfWeek":"SUNDAY","dayOfYear":1,"chronology":{"id":"ISO","calendarType":"iso8601"}},"ldBirthday":{"year":1995,"month":"JANUARY","dayOfMonth":1,"monthValue":1,"era":"CE","dayOfWeek":"SUNDAY","dayOfYear":1,"leapYear":false,"chronology":{"id":"ISO","calendarType":"iso8601"}},"bsid":1},{"bolid":true,"bid":2,"sid":0,"iid":2,"lid":2,"fid":2.0,"did":2.0,"cid":"b","id":2,"birthday":"1995-01-02 00:00:00","LdBirthday":"1995-01-02","LtBirthday":"00:00:00","LdtBirthday":"1995-01-02 00:00:00","name":"李四","sex":"男","biid":2,"bfid":2.0,"bdid":2.0,"bbolid":false,"bcid":"B","blid":2,"decid":2.0,"bigid":2,"bbid":2,"ltBirthday":{"hour":0,"minute":0,"second":0,"nano":0},"ldtBirthday":{"month":"JANUARY","year":1995,"dayOfMonth":2,"hour":0,"minute":0,"monthValue":1,"nano":0,"second":0,"dayOfWeek":"MONDAY","dayOfYear":2,"chronology":{"id":"ISO","calendarType":"iso8601"}},"ldBirthday":{"year":1995,"month":"JANUARY","dayOfMonth":2,"monthValue":1,"era":"CE","dayOfWeek":"MONDAY","dayOfYear":2,"leapYear":false,"chronology":{"id":"ISO","calendarType":"iso8601"}},"bsid":2},{"bolid":false,"bid":3,"sid":0,"iid":3,"lid":3,"fid":3.0,"did":3.0,"cid":"c","id":3,"birthday":"1995-01-03 00:00:00","LdBirthday":"1995-01-03","LtBirthday":"00:00:00","LdtBirthday":"1995-01-03 00:00:00","name":"王冰冰","sex":"女","biid":3,"bfid":3.0,"bdid":3.0,"bbolid":true,"bcid":"C","blid":3,"decid":3.0,"bigid":3,"bbid":3,"ltBirthday":{"hour":0,"minute":0,"second":0,"nano":0},"ldtBirthday":{"month":"JANUARY","year":1995,"dayOfMonth":3,"hour":0,"minute":0,"monthValue":1,"nano":0,"second":0,"dayOfWeek":"TUESDAY","dayOfYear":3,"chronology":{"id":"ISO","calendarType":"iso8601"}},"ldBirthday":{"year":1995,"month":"JANUARY","dayOfMonth":3,"monthValue":1,"era":"CE","dayOfWeek":"TUESDAY","dayOfYear":3,"leapYear":false,"chronology":{"id":"ISO","calendarType":"iso8601"}},"bsid":3}]
19:46:45.765 [main] DEBUG org.apache.poi.openxml4j.opc.PackageRelationshipCollection - Parsing relationship: /xl/_rels/workbook.xml.rels
19:46:45.765 [main] DEBUG org.apache.poi.openxml4j.opc.PackageRelationshipCollection - Parsing relationship: /_rels/.rels
[{"bolid":false,"bid":1,"sid":0,"iid":1,"lid":1,"fid":1.0,"did":1.0,"cid":"a","id":1,"birthday":"1995-01-01 00:00:00","LdBirthday":"1995-01-01","LtBirthday":"00:00:00","LdtBirthday":"1995-01-01 00:00:00","name":"张三","sex":"男","biid":1,"bfid":1.0,"bdid":1.0,"bbolid":true,"bcid":"A","blid":1,"decid":1.0,"bigid":1,"bbid":1,"ltBirthday":{"hour":0,"minute":0,"second":0,"nano":0},"ldtBirthday":{"month":"JANUARY","year":1995,"dayOfMonth":1,"hour":0,"minute":0,"monthValue":1,"nano":0,"second":0,"dayOfWeek":"SUNDAY","dayOfYear":1,"chronology":{"id":"ISO","calendarType":"iso8601"}},"ldBirthday":{"year":1995,"month":"JANUARY","dayOfMonth":1,"monthValue":1,"era":"CE","dayOfWeek":"SUNDAY","dayOfYear":1,"leapYear":false,"chronology":{"id":"ISO","calendarType":"iso8601"}},"bsid":1},{"bolid":true,"bid":2,"sid":0,"iid":2,"lid":2,"fid":2.0,"did":2.0,"cid":"b","id":2,"birthday":"1995-01-02 00:00:00","LdBirthday":"1995-01-02","LtBirthday":"00:00:00","LdtBirthday":"1995-01-02 00:00:00","name":"李四","sex":"男","biid":2,"bfid":2.0,"bdid":2.0,"bbolid":false,"bcid":"B","blid":2,"decid":2.0,"bigid":2,"bbid":2,"ltBirthday":{"hour":0,"minute":0,"second":0,"nano":0},"ldtBirthday":{"month":"JANUARY","year":1995,"dayOfMonth":2,"hour":0,"minute":0,"monthValue":1,"nano":0,"second":0,"dayOfWeek":"MONDAY","dayOfYear":2,"chronology":{"id":"ISO","calendarType":"iso8601"}},"ldBirthday":{"year":1995,"month":"JANUARY","dayOfMonth":2,"monthValue":1,"era":"CE","dayOfWeek":"MONDAY","dayOfYear":2,"leapYear":false,"chronology":{"id":"ISO","calendarType":"iso8601"}},"bsid":2},{"bolid":false,"bid":3,"sid":0,"iid":3,"lid":3,"fid":3.0,"did":3.0,"cid":"c","id":3,"birthday":"1995-01-03 00:00:00","LdBirthday":"1995-01-03","LtBirthday":"00:00:00","LdtBirthday":"1995-01-03 00:00:00","name":"王冰冰","sex":"女","biid":3,"bfid":3.0,"bdid":3.0,"bbolid":true,"bcid":"C","blid":3,"decid":3.0,"bigid":3,"bbid":3,"ltBirthday":{"hour":0,"minute":0,"second":0,"nano":0},"ldtBirthday":{"month":"JANUARY","year":1995,"dayOfMonth":3,"hour":0,"minute":0,"monthValue":1,"nano":0,"second":0,"dayOfWeek":"TUESDAY","dayOfYear":3,"chronology":{"id":"ISO","calendarType":"iso8601"}},"ldBirthday":{"year":1995,"month":"JANUARY","dayOfMonth":3,"monthValue":1,"era":"CE","dayOfWeek":"TUESDAY","dayOfYear":3,"leapYear":false,"chronology":{"id":"ISO","calendarType":"iso8601"}},"bsid":3}]
19:46:45.858 [main] DEBUG org.apache.poi.openxml4j.opc.PackageRelationshipCollection - Parsing relationship: /xl/_rels/workbook.xml.rels
19:46:45.905 [main] DEBUG org.apache.poi.openxml4j.opc.PackageRelationshipCollection - Parsing relationship: /_rels/.rels
[{"bolid":false,"bid":0,"sid":0,"iid":0,"lid":0,"fid":0.0,"did":0.0,"cid":"\u0000","id":1,"birthday":"1995-01-01 00:00:00","LdBirthday":null,"LtBirthday":null,"LdtBirthday":null,"name":"张三","sex":"男","biid":null,"bfid":null,"bdid":null,"bbolid":null,"bcid":null,"blid":null,"decid":null,"bigid":null,"bbid":null,"ltBirthday":null,"ldtBirthday":null,"ldBirthday":null,"bsid":null},{"bolid":false,"bid":0,"sid":0,"iid":0,"lid":0,"fid":0.0,"did":0.0,"cid":"\u0000","id":2,"birthday":"1995-01-02 00:00:00","LdBirthday":null,"LtBirthday":null,"LdtBirthday":null,"name":"李四","sex":"男","biid":null,"bfid":null,"bdid":null,"bbolid":null,"bcid":null,"blid":null,"decid":null,"bigid":null,"bbid":null,"ltBirthday":null,"ldtBirthday":null,"ldBirthday":null,"bsid":null},{"bolid":false,"bid":0,"sid":0,"iid":0,"lid":0,"fid":0.0,"did":0.0,"cid":"\u0000","id":3,"birthday":"1995-01-03 00:00:00","LdBirthday":null,"LtBirthday":null,"LdtBirthday":null,"name":"王冰冰","sex":"女","biid":null,"bfid":null,"bdid":null,"bbolid":null,"bcid":null,"blid":null,"decid":null,"bigid":null,"bbid":null,"ltBirthday":null,"ldtBirthday":null,"ldBirthday":null,"bsid":null}]
19:46:45.983 [main] DEBUG org.apache.poi.openxml4j.opc.PackageRelationshipCollection - Parsing relationship: /xl/_rels/workbook.xml.rels
19:46:45.983 [main] DEBUG org.apache.poi.openxml4j.opc.PackageRelationshipCollection - Parsing relationship: /_rels/.rels
[{"bolid":false,"bid":0,"sid":0,"iid":0,"lid":0,"fid":0.0,"did":0.0,"cid":"\u0000","id":1,"birthday":"1995-01-01 00:00:00","LdBirthday":null,"LtBirthday":null,"LdtBirthday":null,"name":"张三","sex":"男","biid":null,"bfid":null,"bdid":null,"bbolid":null,"bcid":null,"blid":null,"decid":null,"bigid":null,"bbid":null,"ltBirthday":null,"ldtBirthday":null,"ldBirthday":null,"bsid":null},{"bolid":false,"bid":0,"sid":0,"iid":0,"lid":0,"fid":0.0,"did":0.0,"cid":"\u0000","id":2,"birthday":"1995-01-02 00:00:00","LdBirthday":null,"LtBirthday":null,"LdtBirthday":null,"name":"李四","sex":"男","biid":null,"bfid":null,"bdid":null,"bbolid":null,"bcid":null,"blid":null,"decid":null,"bigid":null,"bbid":null,"ltBirthday":null,"ldtBirthday":null,"ldBirthday":null,"bsid":null},{"bolid":false,"bid":0,"sid":0,"iid":0,"lid":0,"fid":0.0,"did":0.0,"cid":"\u0000","id":3,"birthday":"1995-01-03 00:00:00","LdBirthday":null,"LtBirthday":null,"LdtBirthday":null,"name":"王冰冰","sex":"女","biid":null,"bfid":null,"bdid":null,"bbolid":null,"bcid":null,"blid":null,"decid":null,"bigid":null,"bbid":null,"ltBirthday":null,"ldtBirthday":null,"ldBirthday":null,"bsid":null}]
19:46:46.030 [main] DEBUG org.apache.poi.openxml4j.opc.PackageRelationshipCollection - Parsing relationship: /xl/_rels/workbook.xml.rels
19:46:46.030 [main] DEBUG org.apache.poi.openxml4j.opc.PackageRelationshipCollection - Parsing relationship: /_rels/.rels
[{"bolid":false,"bid":0,"sid":0,"iid":0,"lid":0,"fid":0.0,"did":0.0,"cid":"\u0000","id":1,"birthday":"1995-01-01 00:00:00","LdBirthday":null,"LtBirthday":null,"LdtBirthday":null,"name":"张三","sex":"男","biid":null,"bfid":null,"bdid":null,"bbolid":null,"bcid":null,"blid":null,"decid":null,"bigid":null,"bbid":null,"ltBirthday":null,"ldtBirthday":null,"ldBirthday":null,"bsid":null},{"bolid":false,"bid":0,"sid":0,"iid":0,"lid":0,"fid":0.0,"did":0.0,"cid":"\u0000","id":2,"birthday":"1995-01-02 00:00:00","LdBirthday":null,"LtBirthday":null,"LdtBirthday":null,"name":"李四","sex":"男","biid":null,"bfid":null,"bdid":null,"bbolid":null,"bcid":null,"blid":null,"decid":null,"bigid":null,"bbid":null,"ltBirthday":null,"ldtBirthday":null,"ldBirthday":null,"bsid":null},{"bolid":false,"bid":0,"sid":0,"iid":0,"lid":0,"fid":0.0,"did":0.0,"cid":"\u0000","id":3,"birthday":"1995-01-03 00:00:00","LdBirthday":null,"LtBirthday":null,"LdtBirthday":null,"name":"王冰冰","sex":"女","biid":null,"bfid":null,"bdid":null,"bbolid":null,"bcid":null,"blid":null,"decid":null,"bigid":null,"bbid":null,"ltBirthday":null,"ldtBirthday":null,"ldBirthday":null,"bsid":null}]
java_t_t
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
【java超方便的导入导出工具类】SpringBoot操作Excel导入和导出
qq_53438100的博客
04-29 995
超方便的SpringBoot+Excel导入导出，值得你研究。
Excel 导入导出、样式设计、Excel各种工具类整合，FileUtil、ExcelUtil
qq_43358385的博客
01-11 1189
样式设计可参考：https://apidoc.gitee.com/loolly/hutool/cn/hutool/poi/excel/ExcelWriter.html。下述工具类会不断完善，欢迎提出意见，由于为实现复杂需求，所以代码从excel拿数据拿的比较细致，未作代码优化。
Excel快速导入工具类
最新发布
嗯哼？是可乐啊~
05-20 1443
本本介绍了ExcelImportUtils工具类通过ExcelImportField注解进行Excel数据解析、数据填充到Bean中，简化了数据解析过程。通过ExcelImportFieldFormat接口，灵活转换数据格式。
Excel常用导入导出工具类
ls18802694089的博客
12-26 611
【代码】Excel常用导入导出工具类。
工具类
欢迎所有人，批评指正！
11-15 288
package com.casic.business.utils; import java.io.IOException; import java.net.URI; import java.util.ArrayList; import java.util.List; import java.util.Map; import org.apache.http.NameValuePair; impo...
Excel导出导入工具类
普通人一枚
12-23 1567
excle导入导出
POI-EXCEL导入导出工具
Mr_Hellon的博客
10-20 1166
POI-Excel工具类
Excel导入导出工具类
weixin_2630663675的博客
07-21 73
【代码】Excel导入导出工具类。
Excel导入工具类
m0_49341808的博客
10-13 52
【代码】Excel导入工具类。
Java导入导出功能（浏览器\本地）
weixin_47737825的博客
06-07 732
一、 springBoot支持需要用到的依赖包 <!--导入导出--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.13</version> </dependency&gt
工具类——Java 浏览器导入、导出Excel（Java import、export）
Today_He的博客
08-30 4803
目录结构Ⅰ、导入一、前端1.input file2.button二、后端1.Controller2.Service3.解析类ExcelUtils4.MapperⅡ、导出一、前端1.方式一2.方式二二、后端1.Controller2.Service3.Mapper Ⅰ、导入 给到两种导入，大差不差的 一、前端 1.input file 注意1：enctype=“multipart/form-data”，method=“post” 注意2：name=“planImport”，file的name要与java读取流
使用Java导入、导出excel详解（附有封装好的工具类）
是江迪呀 的博客
03-20 9155
提供一个使用Apache POI实现的excel导入导出，并附有现成工具类，可以直接使用呦~
Excel导入导出工具类（多sheet、多表头、单元格下拉选择、根据列名匹配转为List）
weixin_43830468的博客
01-21 2200
Excel工具类导入导出（多sheet、多表头、单元格下拉选择、根据列名匹配转为List）
poi导入导出工具类（超级实用，记得收藏）
qq_34091529的博客
05-25 1300
poi导入导出工具类
Excel导出工具类-EasyExcelUtils
ShaoXia1216的博客
01-31 1147
Excel导出工具类-EasyExcelUtils。
Excel工具类
Dzooooone_的博客
03-23 206
Excel工具类
Java导入、导出excel保姆级教程（附封装好的工具类）
Java知音
08-01 8776
前言我们在日常开发中，一定遇到过要将数据导出为Excel的需求，那么怎么做呢？在做之前，我们需要思考下Excel的组成。Excel是由四个元素组成的分别是：WorkBook(工作簿)、Sheet(工作表)、Row(行)、Cell(单元格)，其中包含关系是从左至右，一个WorkBook可以包含多个Sheet，一个Sheet又是由多个Row组成，一个Row是由多个Cell组成。知道这些后那么我们就使用...
EasyExcel导出工具类(支持模板导出多Sheet导出)
qq_35792867的博客
01-12 3146
EasyExcel导出工具类(支持模板导出多Sheet导出)http
导出Excel的工具类
Jackie的博客
07-13 230
/************************系统基础的公共方法（为了图方便，不单独起一个类）*****************************////同一年的只算月份。* 获取上个月的年份和月份(实际是倒退了两个月，因为本月调用的数据都是上个月的数据)* 获取当前的月份(实际是上月，因为本月调用的数据都是上个月的数据)// font.setFontName("宋体");/**取得下个月的年份和月份(实际上这才是本月)**//**加载相应的EXCEL模板**/
java编写导入导出excel文件工具类
04-21
很高兴能够回答这个问题。可以使用Apache POI库来实现Java编写导入导出Excel文件的工具类。该库可以对Excel文件进行读写操作，支持Excel 97-2003和Excel 2007及以上格式的文件。以下是一个简单的示例代码，可以参考： ``` import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.FileOutputStream; import java.util.List; public class ExcelUtils { /** * 导出Excel文件 * * @param header Excel表头 * @param data Excel数据 * @param path 文件路径 * @throws Exception */ public static void exportExcel(List<String> header, List<List<Object>> data, String path) throws Exception { Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet("Sheet1"); // 写入表头 Row headerRow = sheet.createRow(0); for (int i = 0; i < header.size(); i++) { headerRow.createCell(i).setCellValue(header.get(i)); } // 写入数据 for (int i = 0; i < data.size(); i++) { Row row = sheet.createRow(i + 1); List<Object> rowData = data.get(i); for (int j = 0; j < rowData.size(); j++) { Object value = rowData.get(j); if (value instanceof Number) { row.createCell(j).setCellValue(((Number) value).doubleValue()); } else { row.createCell(j).setCellValue(value.toString()); } } } // 保存文件 try (FileOutputStream outputStream = new FileOutputStream(path)) { workbook.write(outputStream); } } } ``` 以上代码使用了XSSFWorkbook对象来创建一个新的Excel文件。将表头和数据写入工作表中，并将保存到指定路径的文件中。请注意，此示例代码仅用于说明如何在Java中编写导入导出Excel文件的工具类，实际应用场景可能需要根据具体要求进行更改和优化。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值