SpringBoot 简洁明了将导入Excel到数据库

以下有两种构建工程看看你选择哪个

maven依赖

<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>

Gradle依赖

compile group: 'org.apache.poi', name: 'poi', version: '3.17'
compile group: 'org.apache.poi', name: 'poi-ooxml', version: '3.17'

控制层代码

    @ApiOperation(value = "导入", notes = "导入", produces = "text/plain")
    @PostMapping("/v2/payable/importOrder")
    @PreAuthorize("hasRole('importOrder-payable-v2')")
    public JSONResult importOrder(MultipartFile file) throws Exception {
        orderService.importOrder(Long.parseLong(SecurityContextHolder.getSubject().getId()),file);
        return JSONResult.ok();
    }

 

服务层代码

    /**
     * 导入
     * @param file 文件
     */
    void importOrder(Long userId,MultipartFile file) throws Exception;

实现层代码（这里是我多表添加操作数据）

@Override
    @Transactional
    public void importOrder(Long userId, MultipartFile file) throws Exception {
        //调用封装好的工具
        ExcelImportUtil importUtil = new ExcelImportUtil(file);
        //调用导入的方法，获取sheet表的内容
        List<Map<String, String>> maps = importUtil.readExcelContent();
        //获取自定义表头标题数据
        Map<String, Object> someTitle = importUtil.readExcelSomeTitle();
        //导入订单表
        PayableOrder payableOrder = PayableOrder.builder()
                .entryTime(someTitle.get("date").toString())
                .remark(someTitle.get("remark").toString())
                .createTime(DateTimeUtils.dateTimeToString(LocalDateTime.now()))
                .createBy(String.valueOf(userId))
                .status(1)
                .type(1)
                .build();
        //执行添加到数据库
        payableOrderDao.save(payableOrder);
         //获取订单号
        Long id = payableOrder.getId();
        //导入订单详情表
        List<PayableOrderDetail> orderDetails = maps.stream().filter(Objects::nonNull).map(map -> {
            Customer customer = customerDao.findByEnCode(map.get("供应商编码"));
            if (Objects.isNull(customer)) throw new HandleException("供应商不存在");
            return PayableOrderDetail.builder()
                    .qPayableId(id)
                    .supplierCode(map.get("供应商编码"))
                    .cCustomerId(customer.getId())
                    .supplierName(map.get("供应商名称"))
                    .registerAddr(map.get("注册地址"))
                    .bankAccount(map.get("银行账号"))
                    .openBank(map.get("开户行"))
                    .telephone(map.get("联系方式"))
                    .contact(map.get("主联系人"))
                    .remark(map.get("备注"))
                    .payAmount(BigDecimal.valueOf(Double.parseDouble(map.get("期初应付金额"))))
                    .createTime(DateTimeUtils.dateTimeToString(LocalDateTime.now()))
                    .createBy(String.valueOf(userId))
                    .status(1).build();
        }).collect(Collectors.toList());
         //批量添加到订单详情
        orderDetailDao.saveAll(orderDetails);
    }

导入工具类

package com.softwarebr.ashe.util;

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.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author ZHY
 * @date 2020/6/17 15:56
 */
public class ExcelImportUtil {
    private Workbook wb;
    private Sheet sheet;
    private Row row;

    /**
     * 读取Excel
     *
     * @author ZHY
     */
    public ExcelImportUtil(MultipartFile file) throws Exception {
        String filename = file.getOriginalFilename();
        String ext = filename.substring(filename.lastIndexOf("."));
        InputStream is = file.getInputStream();
        if (".xls".equals(ext)) {
            wb = new HSSFWorkbook(is);
        } else if (".xlsx".equals(ext)) {
            wb = new XSSFWorkbook(is);
        } else {
            wb = null;
        }
    }

    /**
     * 读取Excel表格表头的内容输出
     *
     */
    public List<Map<String, Object>> readExcelTitleOut() {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        if (wb != null) {
            sheet = wb.getSheetAt(0);
            row = sheet.getRow(0);
            // 标题总列数
            int colNum = row.getPhysicalNumberOfCells();

            System.out.println("colNum:" + colNum);

            Map<String, Object> map = new LinkedHashMap<String, Object>();

            for (int i = 0; i < colNum; i++) {
                String stringCellValue = row.getCell(i).getStringCellValue();
                map.put(stringCellValue, null);
            }
            list.add(map);
            return list;
        }
        return list;
    }

    /**
     * 读取Excel表格表头
     *
     */
    public String[] readExcelTitle() {
        String[] title = {};
        if (wb != null) {
            sheet = wb.getSheetAt(0);
            row = sheet.getRow(4);
            // 标题总列数
            int colNum = row.getPhysicalNumberOfCells();

            System.out.println("colNum:" + colNum);

            title = new String[colNum];

            for (int i = 0; i < colNum; i++) {
                title[i] = row.getCell(i).getStringCellValue().replaceAll("\\s+", "");
            }
        }
        return title;
    }

    /**
     * 读取Excel表格的某一个数值
     * @return
     */
    public  Map<String, Object> readExcelSomeTitle(){
        Map<String, Object> map = new LinkedHashMap<>();
        if (wb != null) {
            sheet = wb.getSheetAt(0);
            String title = parseExcel(sheet.getRow(2).getCell(1));
            String remark = parseExcel(sheet.getRow(3).getCell(1));
            map.put("date",title);
            map.put("remark",remark);
        }
        return map;
    }

    /**
     * 读取Excel数据内容
     *
     */
    public List<Map<String, String>> readExcelContent() {
        List<Map<String, String>> list = new ArrayList<>();
        if (wb != null) {
            //获取sheet表
            sheet = wb.getSheetAt(0);
            // 得到总行数
            int rowNum = sheet.getLastRowNum();
            //获取表头的标题
            String[] readExcelTitle = readExcelTitle();
            // 正文内容应该从第二行开始,第一行为表头的标题
            for (int i = 5; i <= rowNum; i++) {
                row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                Map<String, String> map = new LinkedHashMap<>();
                for (int j = 0; j < readExcelTitle.length; j++) {
                    //获取每一列的数据值
                    String str = parseExcel(row.getCell(j));
                    //判断对应行的列值是否为空
                    if (StringUtils.isNotBlank(str)) {
                        //表头的标题为键值，列值为值
                        map.put(readExcelTitle[j], str);
                    }
                }
                //判段添加的对象是否为空
                if (!map.isEmpty()){
                    list.add(map);
                }
            }
        }
        return list;
    }

    /**
     *
     * 根据Cell类型设置数据
     *
     */
    private String parseExcel(Cell cell) {
        String result = "";
        if (cell != null) {
            SimpleDateFormat sdf = null;
            switch (cell.getCellTypeEnum()) {
                case NUMERIC:// 数字类型
                    if (DateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                        if (cell.getCellStyle().getDataFormat() == 20) {
                            sdf = new SimpleDateFormat("HH:mm");
                        } else {// 日期
                            sdf = new SimpleDateFormat("yyyy-MM-dd");
                        }
                        String dateFormat = sdf.format(cell.getDateCellValue());
                        result = dateFormat;
                    } else if (cell.getCellStyle().getDataFormat() == 58) {
                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = DateUtil.getJavaDate(value);
                        result = sdf.format(date);
                    } else {
                        double value = cell.getNumericCellValue();
                        DecimalFormat format = new DecimalFormat("#.###########");
                        String strVal = format.format(value);
                        result = strVal;
                    }
                    break;
                case STRING:// String类型
                    result = cell.getRichStringCellValue().toString();
                    break;
                default:
                    break;
            }
        }
        return result;
    }
}

 

操作的excel文件，如图：

 

以上就是操作导入excel文件数据到数据库，我操作的是MySQL数据库，其他的数据没有试过，你们可以试试。

不懂的可以问我，联系方式：18679691390@163.com