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