首页 > 文章列表 > Java如何将Excel数据导入数据库?

Java如何将Excel数据导入数据库?

java 数据库 excel
477 2023-04-27

Java如何将Excel数据导入数据库

1、根据业务需求设计数据库表

2、根据数据库表设计一个Excel模板

模板的每列属性必须与表字段一一对应

3、环境准备

我这里项目环境是基于SpringBoot单体式架构,持久层用的公司框架,内置了基于MyBatis-Plus的各种单表操作的方法。

导入依赖

        <!--使用POI读取文件-->

        <dependency>

            <groupId>org.apache.poi</groupId>

            <artifactId>poi</artifactId>

            <version>3.17</version>

        </dependency>

        <dependency>

            <groupId>org.apache.poi</groupId>

            <artifactId>poi-ooxml</artifactId>

            <version>3.17</version>

        </dependency>

4、通过插件生成表对应的实体类

/**

 * TbZbzs: 值班值守表

 * @author zs

 * @date 2021-12-17 08:46:31

 **/

@Data

@ApiModel(value="值班值守表,对应表tb_zbzs",description="适用于新增和修改页面传参")

public class TbZbzs extends ProBaseEntity<TbZbzs> {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value="id")

    private String id;		// id

    @ApiModelProperty(value="部门")

    private String bm;		// 部门

    @ApiModelProperty(value="值班上报")

    private String zbsb;		// 值班上报

    @ApiModelProperty(value="值班人员")

    private String zbry;		// 值班人员

    @ApiModelProperty(value="上报时间")

    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")

    private java.util.Date sbsj;		// 上报时间

    @ApiModelProperty(value="结束时间")

    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")

    private java.util.Date jssj;		// 结束时间

    @ApiModelProperty(value="联系方式")

    private String lxfs;		// 联系方式

    @ApiModelProperty(value="状态")

    private String zt;		// 状态

    /**

     * 逻辑删除

     */

    @ApiModelProperty(value="逻辑删除")

    private String delFlag;

    /**

     * 创建时间

     */

    @ApiModelProperty(value="创建时间")

    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")

    private Date createDate;

}

5、自定义编写工具类

这里提供的是一个基础模板,根据业务的需求可以增加转换条件

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;

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.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;

import java.io.InputStream;

import java.text.DecimalFormat;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.List;

/**

 * 新增值班排班表导入Excel表工具类

 * zyw

 */

public class ImportExcelUtil {

    private final static String excel2003L =".xls";    //2003- 版本的excel

    private final static String excel2007U =".xlsx";   //2007+ 版本的excel

    /**

     * 描述:获取IO流中的数据,组装成List<List<Object>>对象

     * @param in,fileName

     * @return

     * @throws Exception

     */

    public static List<List<Object>> getListByExcel(InputStream in, String fileName) throws Exception {

        List<List<Object>> list = null;

        //创建Excel工作薄

        Workbook work = ImportExcelUtil.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()+1; 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(ImportExcelUtil.getCellValue(cell));

                }

                list.add(li);

            }

        }

//        work.close();

        return list;

    }

    /**

     * 描述:根据文件后缀,自适应上传文件的版本

     * @param inStr,fileName

     * @return

     * @throws Exception

     */

    public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception{

        Workbook wb = null;

        String fileType = fileName.substring(fileName.lastIndexOf("."));

        if(excel2003L.equals(fileType)){

            wb = new HSSFWorkbook(inStr);  //2003-

        }else if(excel2007U.equals(fileType)){

            wb = new XSSFWorkbook(inStr);  //2007+

        }else{

            throw new Exception("解析的文件格式有误!");

        }

        return wb;

    }

    /**

     * 描述:对表格中数值进行格式化

     * @param cell

     * @return

     */

    public  static Object getCellValue(Cell cell){

        Object value = null;

        DecimalFormat df = new DecimalFormat("0");  //格式化number String字符

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

//        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化

//        DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字

        if (cell!=null){

            switch (cell.getCellType()) {

                case Cell.CELL_TYPE_STRING:

                    value = cell.getRichStringCellValue().getString();

                    break;

                case Cell.CELL_TYPE_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 = sdf.format(cell.getDateCellValue());

                    }

                    break;

                case Cell.CELL_TYPE_BOOLEAN:

                    value = cell.getBooleanCellValue();

                    break;

                case Cell.CELL_TYPE_BLANK:

                    value = "";

                    break;

                default:

                    break;

            }

        }

        return value;

    }

}

6、编写具体业务逻辑Service

主要思想:通过工具类将Excel文件解析成Object泛型的集合,再将集合循环遍历,在遍历中,将每行数据一次填入对象中,再每次循环中,将赋值后的对象存入一个list集合,最后统一将集合执行批量上传的方法,存入数据库。

public Map<String,Object> importTprkxx(MultipartFile file){

        Map<String,Object> resultMap = new HashMap<>();

        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        List<TbZbzs> tbZbzsList = new ArrayList<>();

        try {

            //获取数据

            List<List<Object>> olist = ImportExcelUtil.getListByExcel(file.getInputStream(), file.getOriginalFilename());

            resultMap.put("导入成功",200);

            //封装数据

            for (int i = 0; i < olist.size(); i++) {

                List<Object> list = olist.get(i);

                if (list.get(0) == "" || ("序号").equals(list.get(0))) {

                    continue;

                }

                TbZbzs tbZbzs = new TbZbzs();

                tbZbzs.setId(UUID.randomUUID().toString().replace("-", "").substring(0, 20));

                //根据下标获取每一行的每一条数据

                if (String.valueOf(list.get(0))==null) {

                    resultMap.put("state", "部门不能为空");

                    continue;

                }

                tbZbzs.setBm(String.valueOf(list.get(0)));

                if (String.valueOf(list.get(1))==null) {

                    resultMap.put("state", "值班上报不能为空");

                    continue;

                }

                tbZbzs.setZbsb(String.valueOf(list.get(1)));

                if (String.valueOf(list.get(2))==null) {

                    resultMap.put("state", "值班人员不能为空");

                    continue;

                }

                tbZbzs.setZbry(String.valueOf(list.get(2)));

                if (String.valueOf(list.get(3))==null) {

                    resultMap.put("state", "导入失败,上报时间不能为空");

                    continue;

                }

                String dateStr3 = String.valueOf(list.get(3));

                Date date3 = simpleDateFormat.parse(dateStr3);

                tbZbzs.setSbsj(date3);

                if (String.valueOf(list.get(4))==null) {

                    resultMap.put("state", "导入失败,结束时间不能为空");

                    continue;

                }

                String dateStr4 = String.valueOf(list.get(4));

                Date date4 = simpleDateFormat.parse(dateStr4);

                tbZbzs.setJssj(date4);

                if (String.valueOf(list.get(5))==null) {

                    resultMap.put("state", "联系方式不能为空");

                    continue;

                }

                tbZbzs.setLxfs(String.valueOf(list.get(5)));

                if (String.valueOf(list.get(6))==null) {

                    resultMap.put("state", "状态不能为空");

                    continue;

                }

                tbZbzs.setZt(String.valueOf(list.get(6)));

                if (String.valueOf(list.get(7))==null) {

                    resultMap.put("state", "逻辑删除不能为空");

                    continue;

                }

                tbZbzs.setDelFlag(String.valueOf(list.get(7)));

                if (String.valueOf(list.get(8))==null) {

                    resultMap.put("state", "导入失败,创建时间不能为空");

                    continue;

                }

                String dateStr8 = String.valueOf(list.get(8));

                Date date8 = simpleDateFormat.parse(dateStr8);

                tbZbzs.setCreateDate(date8);

                tbZbzsList.add(tbZbzs);

            }

            int i = tbZbzsDao.insertTbZbzsList(tbZbzsList);

            if (i != 0) {

                resultMap.put("state", "导入成功");

            }else {

                resultMap.put("state", "导入失败");

            }

        } catch (Exception e) {

            e.printStackTrace();

            resultMap.put("state", "导入失败");

        }

        return resultMap;

    }

7、在dao层对应的xml文件中,编写批量上传的方法

<insert id="insertTbZbzsList"  parameterType="java.util.List">

        insert into tb_zbzs (

        id,

        bm,

        zbsb,

        zbry,

        sbsj,

        jssj,

        lxfs,

        zt,

        del_flag,

        create_date

        ) VALUES

        <foreach collection="list" item="item" separator=",">

            (

            #{item.id},

            #{item.bm},

            #{item.zbsb},

            #{item.zbry},

            #{item.sbsj},

            #{item.jssj},

            #{item.lxfs},

            #{item.zt},

            #{item.delFlag},

            #{item.createDate}

            )

        </foreach>

        </insert>

8、Controller实现业务的控制

    /**

     * @方法名称: excelProTbZbzs

     * @实现功能: 导入值班值守表Excel TODO: 方法入参根据页面对象设置

     * @param    file

     * @return  java.lang.String

     * @create by zyw at 2022-03-17 16:49:31

     **/

    @ApiOperation(value="导入值班值守表Excel",notes="返回导入情况接口",response = TbZbzs.class)

    @PostMapping(value = "/excelProTbZbzs")

    public String excelProTbZbzs(@RequestParam("file")  MultipartFile file){

        try {

            return buildResultStr(service.importTprkxx(file).get("state").equals("导入成功") ? buildSuccessResultData() : buildErrorResultData(service.importTprkxx(file).get("state").toString()));

        }catch (RuntimeException e){

            logError(log, e);

            return buildResultStr(buildErrorResultData(e));

        }

    }

9、通过Swagger测试接口

10、在数据和控制台中查看导入效果