poi 3.17 导入excel文件

sumMStar

已于 2024-02-15 19:36:38 修改

阅读量479

点赞数 10

文章标签： excel

于 2024-02-15 19:32:46 首次发布

本文链接：https://blog.csdn.net/summstar/article/details/136122538

版权

参考

springboot+apache poi 3.17读取Excel文件数据_springboot 获取多对多对多的excel数据-CSDN博客

前端上传Excel，后台接收文件并处理

导入依赖

注：poi-ooxml要在其他两个poi后面引入，不然导包报错

<dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-collections4</artifactId>
            <version>4.1</version>
        </dependency>
 
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.5</version>
        </dependency>
 
        <dependency>
            <groupId>org.apache.xmlbeans</groupId>
            <artifactId>xmlbeans</artifactId>
            <version>2.6.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
 
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

工具类



import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.regex.Pattern;

import com.wedu.modules.fault.annotation.ExcelColumn;
import org.apache.commons.io.IOUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;


public class ExcelUtil {

    private static final DecimalFormat DECIMAL_FORMAT = new DecimalFormat("0");// 格式化 number为整

    private static final DecimalFormat DECIMAL_FORMAT_PERCENT = new DecimalFormat("##.00%");//格式化分比格式，后面不足2位的用0补齐

    private static final SimpleDateFormat FAST_DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    private static final DecimalFormat DECIMAL_FORMAT_NUMBER  = new DecimalFormat("0.00E000"); //格式化科学计数器

    private static final Pattern POINTS_PATTERN = Pattern.compile("0.0+_*[^/s]+"); //小数匹配

    /**
     * 读取excel
     * @param file    spring封装的文件
     * @return         二维数据列表
     * @throws IOException
     */
    public static List<List<Object>> readExcel(MultipartFile file) throws IOException {
        String extension = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1).toLowerCase();
        if(Objects.equals("xls", extension) || Objects.equals("xlsx", extension)) {
            return readExcel(file.getInputStream());
        } else {
            throw new IOException("不支持的文件类型");
        }
    }

    /**
     * 读取excel
     * @param file      spring封装的文件
     * @param cls       返回泛型类型
     * @return          返回对象列表
     * @throws IOException
     */
    public static <T> List<T> readExcel(MultipartFile file, Class<T> cls) throws IOException {
        String extension = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1).toLowerCase();
        if(Objects.equals("xls", extension) || Objects.equals("xlsx", extension)) {
            return readExcel(file.getInputStream(), cls);
        } else {
            throw new IOException("不支持的文件类型");
        }
    }

    /**
     *  读取excel
     * @param inputStream   excel文件流
     * @return                二维列表数据
     * @throws IOException
     */
    private static List<List<Object>> readExcel(InputStream inputStream) throws IOException {
        List<List<Object>> list = new LinkedList<>();
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(inputStream);
            int sheetsNumber = workbook.getNumberOfSheets();
            for (int n = 0; n < sheetsNumber; n++) {
                Sheet sheet = workbook.getSheetAt(n);
                Object value = null;
                Row row = null;
                Cell cell = null;
                for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getPhysicalNumberOfRows(); i++) { // 从第二行开始读取
                    row = sheet.getRow(i);
                    if (StringUtils.isEmpty(row)) {
                        continue;
                    }
                    List<Object> linked = new LinkedList<>();
                    for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                        cell = row.getCell(j);
                        if (StringUtils.isEmpty(cell)) {
                            continue;
                        }
                        value = getCellValue(cell);
                        linked.add(value);
                    }
                    list.add(linked);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(workbook);
            IOUtils.closeQuietly(inputStream);
        }
        return list;
    }

    /**
     * 获取excel数据 将之转换成bean
     * @param inputStream      excel文件输入流
     * @param cls               需要转换的实体类
     * @param <T>               可以返回任意类型的列表
     * @return
     */
    private static <T> List<T> readExcel(InputStream inputStream, Class<T> cls) {
        List<T> dataList = new LinkedList<T>();
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(inputStream);
            Map<String, List<Field>> classMap = new HashMap<String, List<Field>>();// 保存字段名及对
            Field[] fields = cls.getDeclaredFields();// 获取实体类所有字段
            for (Field field : fields) {
                ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                if (annotation != null) {
                    String value = annotation.value();
                    if (!classMap.containsKey(value)) {
                        classMap.put(value, new ArrayList<Field>());
                    }
                    field.setAccessible(true);
                    classMap.get(value).add(field);
                }
            }
            Map<Integer, List<Field>> reflectionMap = new HashMap<Integer, List<Field>>();
            int sheetsNumber = workbook.getNumberOfSheets(); // 获取excel工作单页数
            //获取每一个表单
            for (int n = 0; n < sheetsNumber; n++) {
                Sheet sheet = workbook.getSheetAt(n);

                // 一行中没有数据将会返回null
                if(sheet.getRow(0) == null){
                    return dataList;
                }
                int firstCellNum = sheet.getRow(0).getFirstCellNum();
                int lastCellNum = sheet.getRow(0).getLastCellNum();
                ;
                if(firstCellNum == lastCellNum ){       // 进入说明后面工作表单是空的，可以直接返回了
                    return dataList;
                }
                for (int j = firstCellNum; j < lastCellNum; j++) {  // 获取首行的所有列名
                    Object cellValue = getCellValue(sheet.getRow(0).getCell(j));
                    if (classMap.containsKey(cellValue)) {
                        reflectionMap.put(j, classMap.get(cellValue));
                    }
                }
                Row row = null;
                Cell cell = null;
                for (int i = sheet.getFirstRowNum() + 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                    row = sheet.getRow(i);
                    T t = cls.newInstance();
                    for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
                        cell = row.getCell(j);
                        if (reflectionMap.containsKey(j)) {
                            Object cellValue = getCellValue(cell);
                            List<Field> fieldList = reflectionMap.get(j);
                            for (Field field : fieldList) {
                                try {
                                    field.set(t, cellValue);
                                } catch (Exception e) {
                                    e.printStackTrace();
                                }
                            }
                        }
                    }
                    dataList.add(t);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(workbook);
            IOUtils.closeQuietly(inputStream);
        }
        return dataList;
    }

    /**
     * 获取excel 单元格数据
     * @param cell      单元格
     * @return           转换后的单元格数据
     */
    private static Object getCellValue(Cell cell) {
        Object value = null;
        if(cell == null){
            return value = "";
        }
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                value = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if(DateUtil.isCellDateFormatted(cell)){ //日期
                    value = FAST_DATE_FORMAT.format(DateUtil.getJavaDate(cell.getNumericCellValue()));//转成同统一日期格式
//                    value = cell.getDateCellValue();
                } else if("@".equals(cell.getCellStyle().getDataFormatString())
                        || "General".equals(cell.getCellStyle().getDataFormatString())
                        || "0_ ".equals(cell.getCellStyle().getDataFormatString())){
                    //文本  or 常规 or 整型数值
                    value = DECIMAL_FORMAT.format(cell.getNumericCellValue());
                } else if(POINTS_PATTERN.matcher(cell.getCellStyle().getDataFormatString()).matches()){ //正则匹配小数类型
                    value = cell.getNumericCellValue();  //直接显示
                } else if("0.00E+00".equals(cell.getCellStyle().getDataFormatString())){//科学计数
                    value = cell.getNumericCellValue();	//待完善
                    value = DECIMAL_FORMAT_NUMBER.format(value);
                } else if("0.00%".equals(cell.getCellStyle().getDataFormatString())){//百分比
                    value = cell.getNumericCellValue(); //待完善
                    value = DECIMAL_FORMAT_PERCENT.format(value);
                } else if("# ?/?".equals(cell.getCellStyle().getDataFormatString())){//分数
                    value = cell.getNumericCellValue(); //待完善
                } else { //货币
                    value = cell.getNumericCellValue();
                    value = DecimalFormat.getCurrencyInstance().format(value);
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_BLANK:// 空白区域
                value = "";
                break;
            default:
                value = cell.toString();
        }
        return value;
    }



}

编写注解类

import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
 
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelColumn{
 
    public String value() default "";
 
}

编写实体类

全是string类型，我是用来当临时存储数据的实体类，下面的才是包含多种数据类型的实体类

我只能想到这样处理类型不匹配问题

import com.wedu.modules.fault.annotation.ExcelColumn;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;



@Data
@AllArgsConstructor
@NoArgsConstructor
public class StudentTemp  {
//    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
//    @JsonDeserialize(using = CustomDateDeserializer.class)
    @ExcelColumn("时间")
    protected String createTime;

    @ExcelColumn("姓名")
    private String name;

    @ExcelColumn("学号")
    private String number;

    @ExcelColumn("班级")
    private String clazz;

    private String ttt1;

}

实体类

注：@TableName注解和@TableField注解是mybaties plus 的，可以不用写就能测试

@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("student")
public class Student  {
//    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
//    @JsonDeserialize(using = CustomDateDeserializer.class)

    @TableField("create_time")
    @ExcelColumn("时间")
    protected Date createTime;
    
    @TableField("name")
    @ExcelColumn("姓名")
    private String name;

    @TableId(value = "id", type = IdType.AUTO)
    @ExcelColumn("学号")
    private Integer number;

    @TableField("clazz")
    @ExcelColumn("班级")
    private String clazz;

    @TableField(exist = false)
    private String ttt1;

}

处理两个实体类转换问题，注解@ExcelColumn要一一对应

import com.wedu.modules.fault.annotation.ExcelColumn;

import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.Date;

public class EntityConverter {

    public static <T, S> T convertEntity(Class<T> targetClass, S sourceEntity) throws Exception {
        T targetEntity = targetClass.getDeclaredConstructor().newInstance();

        Field[] fields = sourceEntity.getClass().getDeclaredFields();
        for (Field field : fields) {
            field.setAccessible(true);
            Annotation annotation = field.getAnnotation(ExcelColumn.class);
            if (annotation != null) {
                ExcelColumn excelColumn = (ExcelColumn) annotation;
                String columnName = excelColumn.value();
                Field targetField = targetClass.getDeclaredField(field.getName());
                targetField.setAccessible(true);

                Object value = field.get(sourceEntity);
                if (value != null) {
                    if (targetField.getType() == Integer.class) {
                        targetField.set(targetEntity, Integer.parseInt((String) value));
                    } else if (targetField.getType() == Long.class) {
                        targetField.set(targetEntity, Long.parseLong((String) value));
                    } else if (targetField.getType() == Date.class) {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        Date dateValue = sdf.parse((String) value);
                        targetField.set(targetEntity, dateValue);
                    } else {
                        targetField.set(targetEntity, value);
                    }
                }
            }
        }

        return targetEntity;
    }
}

Controller类

    @PostMapping("/import")
    public Object importExcel(MultipartFile file) throws Exception {

        if (file.isEmpty()) {
            return "文件不能为空";
        }
        // 判断是否是excel文件
        List<StudentTemp> studentTempList = ExcelUtil.readExcel(file, StudentTemp.class);
        System.out.println(studentTempList.toString());

        List<Student> studentList = new ArrayList<>();
        for (StudentTemp studentTemp : studentTempList) {
            Student student = EntityConverter.convertEntity(Student.class, studentTemp);
            registerList.add(student);
        }
        System.out.println(studentList.toString());

        // 是则进行插入数据库操作

        studentservice.save(studentList) // 调用service自己写的方法插入数据库
        return "上传成功";
    }