Apache POI 4.0.1版本读取本地Excel文件并写入数据库(兼容 xls 和 xlsx)(五)

读取Excel文件并写入数据库,一般这种业务，经常会碰到。

数据模板：

maven依赖

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.0.1</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.1</version>
        </dependency>

ExcelBean

package com.springbootemaildemo.excel.c;

import io.swagger.annotations.ApiModelProperty;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;

public class ExcelBean {
    @ApiModelProperty(value = "列头（标题）名")
    private String headTextName;
    @ApiModelProperty(value = "对应字段名")
    private String propertyName;
    @ApiModelProperty(value = "合并单元格数")
    private Integer cols;
    @ApiModelProperty(value = "excel列设置风格")
    private XSSFCellStyle cellStyle;

    public ExcelBean() {
    }

    public ExcelBean(String headTextName, String propertyName, Integer cols, XSSFCellStyle cellStyle) {
        this.headTextName = headTextName;
        this.propertyName = propertyName;
        this.cols = cols;
        this.cellStyle = cellStyle;
    }

    public ExcelBean(String headTextName, String propertyName) {
        this.headTextName = headTextName;
        this.propertyName = propertyName;
    }

    public ExcelBean(String headTextName, String propertyName, Integer cols) {
        super();
        this.headTextName = headTextName;
        this.propertyName = propertyName;
        this.cols = cols;
    }

    public String getHeadTextName() {
        return headTextName;
    }

    public void setHeadTextName(String headTextName) {
        this.headTextName = headTextName;
    }

    public String getPropertyName() {
        return propertyName;
    }

    public void setPropertyName(String propertyName) {
        this.propertyName = propertyName;
    }

    public Integer getCols() {
        return cols;
    }

    public void setCols(Integer cols) {
        this.cols = cols;
    }

    public XSSFCellStyle getCellStyle() {
        return cellStyle;
    }

    public void setCellStyle(XSSFCellStyle cellStyle) {
        this.cellStyle = cellStyle;
    }
}

Student

package com.springbootemaildemo.excel.c;

import java.util.Date;

public class Student {
    private Integer id;

    private String username;

    private Date createdate;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username == null ? null : username.trim();
    }

    public Date getCreatedate() {
        return createdate;
    }

    public void setCreatedate(Date createdate) {
        this.createdate = createdate;
    }

}

ExcelUtils

package com.springbootemaildemo.excel.c;

import org.apache.http.client.utils.DateUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
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 org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class ExcelUtils {
    private static final Logger logger = LoggerFactory.getLogger(StudentService.class);

    /**
     * Excel文件类型
     */
    private final static String excel2003L = ".xls"; // 2003- 版本的excel
    private final static String excel2007U = ".xlsx"; // 2007+ 版本的excel

    /**
     * Excel导入
     *
     * @param fileName
     * @return
     */
    public static List<List<Object>> getListByExcel(String fileName) {
        InputStream in = null;
        List<List<Object>> list = null;
        try {
            in = new FileInputStream(fileName);
            // 创建Excel工作薄
            Workbook work = getWorkbook(in, fileName);
            if (null == work) {
                throw new Exception("创建Excel工作薄为空！");
            }
            Sheet sheet = null;
            Row row = null;
            Cell cell = null;
            list = new ArrayList<List<Object>>();
            // 遍历Excel中所有的sheet
            for (int i = 0; i < work.getNumberOfSheets(); i++) {
                sheet = work.getSheetAt(i);
                if (sheet == null) {
                    continue;
                }
                // 遍历当前sheet中的所有行
                // 包含头部，所以要小于等于最后一列数,这里也可以在初始值加上头部行数，以便跳过头部
                for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                    // 读取一行
                    row = sheet.getRow(j);
                    // 去掉空行和表头
                    if (row == null || row.getFirstCellNum() == j) {
                        continue;
                    }
                    // 遍历所有的列
                    List<Object> li = new ArrayList<Object>();
                    for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                        cell = row.getCell(y);
                        li.add(getCellValue(cell));
                    }
                    list.add(li);
                }
            }
        } catch (FileNotFoundException e) {
            logger.info("找不到文件....");
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 描述：根据文件后缀，自适应上传文件的版本
     *
     * @param inStr
     * @param fileName
     * @return
     */
    public static Workbook getWorkbook(InputStream inStr, String fileName) {
        Workbook wb = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        try {
            if (excel2003L.equals(fileType)) {
                wb = new HSSFWorkbook(inStr); // 2003-
            } else if (excel2007U.equals(fileType)) {
                wb = new XSSFWorkbook(inStr); // 2007+
            } else {
                logger.info("解析的文件格式有误！");
                throw new Exception("解析的文件格式有误！");
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return wb;
    }

    /**
     * 描述：对表格中数值进行格式化
     *
     * @param cell
     * @return
     */
    public static Object getCellValue(Cell cell) {
        Object value = null;
        DecimalFormat df = new DecimalFormat("0"); // 格式化字符类型的数字
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd hh:mm:ss"); // 日期格式化
        DecimalFormat df2 = new DecimalFormat("0"); // 格式化数字
        if (cell.getCellType() == CellType.STRING) {
            value = cell.getRichStringCellValue().getString();
        } else if (cell.getCellType() == CellType.NUMERIC) {
            if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                value = df.format(cell.getNumericCellValue());
            } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
                value = sdf.format(cell.getDateCellValue());
            } else {
                value = df2.format(cell.getNumericCellValue());
            }
        } else if (cell.getCellType() == CellType.BOOLEAN) {
            value = cell.getBooleanCellValue();
        } else if (cell.getCellType() == CellType.BLANK) {
            value = "";
        }
        return value;
    }
}

StudentService

package com.springbootemaildemo.excel.c;

import com.alibaba.fastjson.JSON;
import com.google.common.collect.Lists;
import org.apache.commons.collections4.CollectionUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Service
public class StudentService {
    private static final Logger logger = LoggerFactory.getLogger(StudentService.class);
    private static final String fileName = "E:\files\write-02.xlsx";
    @Autowired
    static StudentMapper studentMapper;public static void main(String[] args) {
        insert();
    }

    public static void insert() {
        try {
            List<List<Object>> lists = ExcelUtils.getListByExcel(fileName);
            logger.info("EXCEL DATA .............:{}", JSON.toJSONString(lists));
            List<Student> users = Lists.newArrayList();
            if (CollectionUtils.isNotEmpty(lists)) {
                for (int i = 0; i < lists.size(); i++) {
                    Student user = new Student();
                    List<Object> ob = lists.get(i);//List<Object>
                    user.setId(Integer.parseInt(ob.get(0).toString()));//"1"
                    user.setUsername(ob.get(1).toString());
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
                    Date time = sdf.parse(ob.get(2).toString());
                    user.setCreatedate(time);
                    users.add(user);
                }
            }
            logger.info("users DATA.............：{}", JSON.toJSONString(users));
            logger.info("insert into datadb begining.............");
//            studentMapper.insertBatch(users);
            logger.info("insert into datadb succeed.............");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

StudentMapper

package com.springbootemaildemo.excel.c;

import com.springbootemaildemo.excel.c.Student;
import com.springbootemaildemo.excel.c.StudentCriteria;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.session.RowBounds;

@Mapper
public interface StudentMapper {
    /**
     * 批量新增
     *
     * @param list
     */
    void insertBatch(@Param("list") List<Student> list);
}

StudentMappe.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.springbootemaildemo.excel.c.StudentMapper">
  <resultMap id="BaseResultMap" type="com.springbootemaildemo.excel.c.Student">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="username" jdbcType="VARCHAR" property="username" />
    <result column="createdate" jdbcType="TIMESTAMP" property="createdate" />
  </resultMap>
 
  <sql id="Base_Column_List">
    id, username, createdate
  </sql>

  <insert id="insertBatch" parameterType="java.util.List">
  insert into student (id, username, createdate
      )
    values
    <foreach collection="list"  item="item" index="index" separator=",">
      (#{item.id,jdbcType=INTEGER}, #{item.username,jdbcType=VARCHAR}, #{item.createdate,jdbcType=TIMESTAMP}
      )
    </foreach>
  </insert>
</mapper>

结果：