[详细步骤]Java将Excel文件导入到数据库表中、并使用postman测试-Toy模板网

这篇具有很好参考价值的文章主要介绍了[详细步骤]Java将Excel文件导入到数据库表中、并使用postman测试。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

1、Maven依赖添加

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>

2、需要使用的类

controller：ExcelController

entity：ExcelEntity（设计此实体类，需要与excel文件一一对应）

mapper：ExcelMapper

service：ExcelServiceImpl、ExcelService

util：ReadExcelUtil

3、代码

package com.dbm.exceltodatabase.controller;

import com.dbm.exceltodatabase.entity.ExcelEntity;
import com.dbm.exceltodatabase.service.ExcelService;
import com.dbm.exceltodatabase.util.ReadExcelUtil;
import com.jgdf.core.filter.http.DisableEncryptResponse;
import com.jgdf.core.web.R;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Zhangyj
 * @create 2023/4/6
 */
@RestController
@RequestMapping("/excel")
@Api(tags = "Excel导入数据库接口")
public class ExcelController {
    @Autowired
    private ExcelService excelService;

    @PostMapping("/excelExport")
    @ApiOperation("Excel表导入数据库")
    @DisableEncryptResponse
    public R<Void> test(@RequestParam("file") MultipartFile file) {
        long startTime = System.currentTimeMillis();
        Map<String, Object> res = new HashMap<>();
        List<ExcelEntity> excelInfo = ReadExcelUtil.getExcelInfo(file);
        for (ExcelEntity patientInfo : excelInfo) {
            excelService.save(patientInfo);
        }
        long endTime = System.currentTimeMillis();
        String time = String.valueOf((endTime - startTime) / 1000);
        res.put("time", "导入数据用时:" + time + "秒");
//        renderResult(response, res);
        System.out.println(res);
        return R.ok();
    }

    @PostMapping("/select")
    @ApiOperation("查询数据库表详情")
    @DisableEncryptResponse
    public R<List<ExcelEntity>> select() {
        List<ExcelEntity> list = excelService.select();
        return R.ok(list);
    }

}

package com.dbm.exceltodatabase.entity;

import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModelProperty;

/**
 * @author Zhangyj
 * @create 2023/4/6
 */
@TableName(value = "excel")
public class ExcelEntity {
    @ApiModelProperty("序号")
    @TableField("f_id")
    @ExcelProperty("序号")
    private String id;

    @ApiModelProperty("区域")
    @TableField("f_area")
    @ExcelProperty("区域")
    private String area;

    @ApiModelProperty("站址名称")
    @TableField("f_site_name")
    @ExcelProperty("站址名称")
    private String siteName;

    @ApiModelProperty("塔型")
    @TableField("f_tower_type")
    @ExcelProperty("塔型")
    private String towerType;

    @ApiModelProperty("站址经度")
    @TableField("f_site_longitude")
    @ExcelProperty("站址经度")
    private String siteLongitude;

    @ApiModelProperty("站址维度")
    @TableField("f_site_dimension")
    @ExcelProperty("站址维度")
    private String siteDimension;

    @ApiModelProperty("方位角度")
    @TableField("f_position_angle")
    @ExcelProperty("方位角度")
    private String positionAngle;

    @ApiModelProperty("详细位置")
    @TableField("f_detail_location")
    @ExcelProperty("详细位置")
    private String detailLocation;

    @ApiModelProperty("云台编号")
    @TableField("f_yuntai_number")
    @ExcelProperty("云台编号")
    private String yuntaiNumber;

    @ApiModelProperty("设备编号")
    @TableField("f_equipment_number")
    @ExcelProperty("设备编号")
    private String equipmentNumber;

    @ApiModelProperty("运营商")
    @TableField("f_operator")
    @ExcelProperty("运营商")
    private String operator;

    @ApiModelProperty("ip地址")
    @TableField("f_ip_address")
    @ExcelProperty("ip地址")
    private String ipAddress;

    public ExcelEntity(String id, String area, String siteName, String towerType, String siteLongitude, String siteDimension, String positionAngle, String detailLocation, String yuntaiNumber, String equipmentNumber, String operator, String ipAddress) {
        this.id = id;
        this.area = area;
        this.siteName = siteName;
        this.towerType = towerType;
        this.siteLongitude = siteLongitude;
        this.siteDimension = siteDimension;
        this.positionAngle = positionAngle;
        this.detailLocation = detailLocation;
        this.yuntaiNumber = yuntaiNumber;
        this.equipmentNumber = equipmentNumber;
        this.operator = operator;
        this.ipAddress = ipAddress;
    }

    public ExcelEntity() {

    }

    public String getId() {
        return id;
    }

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

    public String getArea() {
        return area;
    }

    public void setArea(String area) {
        this.area = area;
    }

    public String getSiteName() {
        return siteName;
    }

    public void setSiteName(String siteName) {
        this.siteName = siteName;
    }

    public String getTowerType() {
        return towerType;
    }

    public void setTowerType(String towerType) {
        this.towerType = towerType;
    }

    public String getSiteLongitude() {
        return siteLongitude;
    }

    public void setSiteLongitude(String siteLongitude) {
        this.siteLongitude = siteLongitude;
    }

    public String getSiteDimension() {
        return siteDimension;
    }

    public void setSiteDimension(String siteDimension) {
        this.siteDimension = siteDimension;
    }

    public String getPositionAngle() {
        return positionAngle;
    }

    public void setPositionAngle(String positionAngle) {
        this.positionAngle = positionAngle;
    }

    public String getDetailLocation() {
        return detailLocation;
    }

    public void setDetailLocation(String detailLocation) {
        this.detailLocation = detailLocation;
    }

    public String getYuntaiNumber() {
        return yuntaiNumber;
    }

    public void setYuntaiNumber(String yuntaiNumber) {
        this.yuntaiNumber = yuntaiNumber;
    }

    public String getEquipmentNumber() {
        return equipmentNumber;
    }

    public void setEquipmentNumber(String equipmentNumber) {
        this.equipmentNumber = equipmentNumber;
    }

    public String getOperator() {
        return operator;
    }

    public void setOperator(String operator) {
        this.operator = operator;
    }

    public String getIpAddress() {
        return ipAddress;
    }

    public void setIpAddress(String ipAddress) {
        this.ipAddress = ipAddress;
    }

    @Override
    public String toString() {
        return "ExcelEntity{" +
                "id='" + id + '\'' +
                ", area='" + area + '\'' +
                ", siteName='" + siteName + '\'' +
                ", towerType='" + towerType + '\'' +
                ", siteLongitude='" + siteLongitude + '\'' +
                ", siteDimension='" + siteDimension + '\'' +
                ", positionAngle='" + positionAngle + '\'' +
                ", detailLocation='" + detailLocation + '\'' +
                ", yuntaiNumber='" + yuntaiNumber + '\'' +
                ", equipmentNumber='" + equipmentNumber + '\'' +
                ", operator='" + operator + '\'' +
                ", ipAddress='" + ipAddress + '\'' +
                '}';
    }
}

package com.dbm.exceltodatabase.mapper;

import com.dbm.exceltodatabase.entity.ExcelEntity;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author Zhangyj
 * @create 2023/4/6
 */
@Mapper
@Repository
public interface ExcelMapper {
    //Excel表导入数据库
    @Insert("insert into excel(f_id,f_area,f_site_name,f_tower_type,f_site_longitude," +
            "f_site_dimension,f_position_angle,f_detail_location,f_yuntai_number,f_equipment_number," +
            "f_operator,f_ip_address)" +
            " values(#{id},#{area},#{siteName},#{towerType},#{siteLongitude}," +
            "#{siteDimension},#{positionAngle},#{detailLocation},#{yuntaiNumber},#{equipmentNumber}," +
            "#{operator},#{ipAddress})")
    int save(ExcelEntity excelEntity);

    //查询数据库表详情
    @Select("select f_id as id,f_area as area,f_site_name as siteName,f_tower_type as towerType,f_site_longitude as siteLongitude," +
            "f_site_dimension as siteDimension,f_position_angle as positionAngle,f_detail_location as detailLocation," +
            "f_yuntai_number as yuntaiNumber,f_equipment_number as equipmentNumber," +
            "f_operator as operator,f_ip_address as ipAddress " +
            "from excel")
    List<ExcelEntity> select();
}

package com.dbm.exceltodatabase.service.impl;

import com.dbm.exceltodatabase.entity.ExcelEntity;
import com.dbm.exceltodatabase.mapper.ExcelMapper;
import com.dbm.exceltodatabase.service.ExcelService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @author Zhangyj
 * @create 2023/4/6
 */
@Slf4j
@Service
public class ExcelServiceImpl implements ExcelService {
    @Autowired
    private ExcelMapper excelMapper;

    //Excel表导入数据库
    @Override
    public void save(ExcelEntity excelEntity) {
        excelMapper.save(excelEntity);
    }

    //查询数据库表详情
    @Override
    public List<ExcelEntity> select() {
        List<ExcelEntity> list = excelMapper.select();
        return list;
    }
}

package com.dbm.exceltodatabase.service;

import com.dbm.exceltodatabase.entity.ExcelEntity;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * @author Zhangyj
 * @create 2023/4/6
 */
@Transactional
public interface ExcelService {
    //Excel表导入数据库
    void save(ExcelEntity excelEntity);

    //查询数据库表详情
    List<ExcelEntity> select();
}

package com.dbm.exceltodatabase.util;

import com.dbm.exceltodatabase.entity.ExcelEntity;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Zhangyj
 * @create 2023/4/6
 */
public class ReadExcelUtil {
    //总行数
    private static int totalRows = 0;
    //总条数
    private static int totalCells = 0;
    //错误信息接收器
    private static String errorMsg;

    /**
     * 读EXCEL文件，获取信息集合
     *
     * @return
     */
    public static List<ExcelEntity> getExcelInfo(MultipartFile mFile) {
        String fileName = mFile.getOriginalFilename();//获取文件名
        try {
            if (!validateExcel(fileName)) {// 验证文件名是否合格
                return null;
            }
            boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
            if (isExcel2007(fileName)) {
                isExcel2003 = false;
            }
            //file.getinputstream()的作用是获取文件的输入流，可以用来读取文件的内容。
            List<ExcelEntity> userList = createExcel(mFile.getInputStream(), isExcel2003);
            return userList;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 根据excel里面的内容读取客户信息
     *
     * @param is          输入流
     * @param isExcel2003 excel是2003还是2007版本
     * @return
     * @throws IOException
     */
    public static List<ExcelEntity> createExcel(InputStream is, boolean isExcel2003) {
        try {
            Workbook wb = null;
            if (isExcel2003) {// 当excel是2003时,创建excel2003
                wb = new HSSFWorkbook(is);
            } else {// 当excel是2007时,创建excel2007
                wb = new XSSFWorkbook(is);
            }
            List<ExcelEntity> userList = readExcelValue(wb);// 读取Excel里面客户的信息
            return userList;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 读取Excel里面客户的信息
     *
     * @param wb
     * @return
     */
    private static List<ExcelEntity> readExcelValue(Workbook wb) {
        //默认会跳过第一行标题
        // 得到第一个shell
        Sheet sheet = wb.getSheetAt(0);
        // 得到Excel的行数
        totalRows = sheet.getPhysicalNumberOfRows();
        // 得到Excel的列数(前提是有行数)
        if (totalRows > 1 && sheet.getRow(0) != null) {
            totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        List<ExcelEntity> userList = new ArrayList<ExcelEntity>();
        // 循环Excel行数
        for (int r = 2; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            ExcelEntity user = new ExcelEntity();
            // 循环Excel的列
            for (int c = 0; c < totalCells + 1; c++) {
                Cell cell = row.getCell(c);
                if (null != cell) {
                    if (c == 0) {           //第一列
                        //如果是纯数字,将单元格类型转为String
                        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                            cell.setCellType(CellType.STRING);
                        }
                        user.setId(cell.getStringCellValue());//将单元格数据赋值给user
                    } else if (c == 1) {
                        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                            cell.setCellType(CellType.STRING);
                        }
                        user.setArea(cell.getStringCellValue());
                    } else if (c == 2) {
                        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                            cell.setCellType(CellType.STRING);
                        }
                        String stringCellValue = cell.getStringCellValue();
                        user.setSiteName(stringCellValue);
                    } else if (c == 3) {
                        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                            cell.setCellType(CellType.STRING);
                        }
                        user.setTowerType(String.valueOf(cell.getStringCellValue()));
                    } else if (c == 4) {
                        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                            cell.setCellType(CellType.STRING);
                        }
                        user.setSiteLongitude(String.valueOf(cell.getStringCellValue()));
                    } else if (c == 5) {
                        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                            cell.setCellType(CellType.STRING);
                        }
                        user.setSiteDimension(String.valueOf(cell.getStringCellValue()));
                    } else if (c == 6) {
                        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                            cell.setCellType(CellType.STRING);
                        }
                        user.setPositionAngle(String.valueOf(cell.getStringCellValue()));
                    } else if (c == 7) {
                        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                            cell.setCellType(CellType.STRING);
                        }
                        user.setDetailLocation(String.valueOf(cell.getStringCellValue()));
                    } else if (c == 8) {
                        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                            cell.setCellType(CellType.STRING);
                        }
                        user.setYuntaiNumber(String.valueOf(cell.getStringCellValue()));
                    } else if (c == 9) {
                        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                            cell.setCellType(CellType.STRING);
                        }
                        user.setEquipmentNumber(String.valueOf(cell.getStringCellValue()));
                    } else if (c == 10) {
                        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                            cell.setCellType(CellType.STRING);
                        }
                        user.setOperator(String.valueOf(cell.getStringCellValue()));
                    } else if (c == 11) {
                        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                            cell.setCellType(CellType.STRING);
                        }
                        user.setIpAddress(String.valueOf(cell.getStringCellValue()));
                    }
                }
            }

            // 添加到list
            userList.add(user);
        }
        return userList;
    }

    /**
     * 验证EXCEL文件
     *
     * @param filePath
     * @return
     */
    public static boolean validateExcel(String filePath) {
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
            errorMsg = "文件名不是excel格式";
            return false;
        }
        return true;
    }

    // @描述：是否是2003的excel，返回true是2003
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    //@描述：是否是2007的excel，返回true是2007
    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

}

4、postman测试

postman怎么测试导入excel文件,java,数据库,excel

file（excel）的文件路径需真实存在，下图进行设置路径、我的excel放在了D:\excel

如果不行设置或路径不存在、会出现以下提示

This file isn't in your working directory. Teammates you share this request with won't be able to use this file. To make collaboration easier you can setup your working directory in Settings.

postman怎么测试导入excel文件,java,数据库,excel