springboot 實作導入excel，解析excel資料并向資料庫插入insert

導入excel檔案，大緻如下：

controller層：此處file檔案寫死，自行修改

@RestController
@RequestMapping("/import")
public class ImportExcelController {

    @Autowired
    private ImportExcelService importExcelService;

    @RequestMapping(value = "/excel",method = RequestMethod.POST)
    public String importExcelDB(){
        //裝載流
        XSSFWorkbook workbook = null;
        File file = new File("D:\\副本系統所需資料港彙店.xlsx");

        try {
            workbook = new XSSFWorkbook(file.getPath());
        }catch (IOException e) {
            e.printStackTrace();
        }

        List<TestExcelEntity> list = new ArrayList<>();
        // 擷取一個工作表，下标從0開始
        XSSFSheet sheet = workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        // 通過循環，逐行取出表中每行資料
        for(int i=0;i<=lastRowNum;i++){//跳過第一行和第二行
            if(i==0 || i==1){
                continue;
            }

            // 擷取行
            XSSFRow row = sheet.getRow(i);

            TestExcelEntity excelEntity = new TestExcelEntity();

            excelEntity.setId(Double.valueOf(row.getCell(0).getNumericCellValue()).longValue()+"");
            excelEntity.setCardno(Double.valueOf(row.getCell(1).getNumericCellValue()).longValue()+"");
            excelEntity.setSaleTime(row.getCell(2).toString());
            excelEntity.setSaleSoure(row.getCell(3).toString());
            excelEntity.setSalePeople(row.getCell(4).toString());
            excelEntity.setRemark(row.getCell(5).toString());
            excelEntity.setCardCeateTime(row.getCell(6).toString());
            excelEntity.setEndTime(row.getCell(7).toString());
            excelEntity.setIsExperClass(row.getCell(8).toString());
            excelEntity.setClassName(row.getCell(9).toString());
            excelEntity.setClassTime(Double.valueOf(row.getCell(10).getNumericCellValue()).longValue()+"");
            excelEntity.setClassPrice(Double.valueOf(row.getCell(11).getNumericCellValue()).longValue()+"");
            excelEntity.setAdvisePrice(Double.valueOf(row.getCell(12).getNumericCellValue()).longValue()+"");
            excelEntity.setSalePrice(Double.valueOf(row.getCell(13).getNumericCellValue()).longValue()+"");
            excelEntity.setGiveClass(row.getCell(14).toString());
            excelEntity.setParentName(row.getCell(15).toString());
            excelEntity.setPhone(Double.valueOf(row.getCell(16).getNumericCellValue()).longValue()+"");
            excelEntity.setBabyName(row.getCell(17).toString());
            excelEntity.setBabyAge(Double.valueOf(row.getCell(18).getNumericCellValue()).longValue()+"");
            excelEntity.setBabySex(row.getCell(19).toString());
            excelEntity.setBabyBrith(row.getCell(20).toString());

            list.add(excelEntity);
        }

        for (TestExcelEntity excelEntity : list) {
            System.out.println(excelEntity);
        }
        importExcelService.importExcelDB(list);

        return new HashMap<String,String>().put("msg","OK!!");
    }
}
           

Service層：

@Service
public class ImportExcelServiceImpl implements ImportExcelService {
    @Autowired
    private ImportExcelMapper importExcelMapper;
    @Override
    public void importExcelDB(List<TestExcelEntity> list) {
        importExcelMapper.importExcelDB(list);
    }
}
           

DAO層：

@Mapper
public interface ImportExcelMapper {
    void importExcelDB(@Param("list") List<TestExcelEntity> list);
}

           

xml：

<insert id="importExcelDB" parameterType="java.util.List" useGeneratedKeys="false" >
      insert into test_entity (id,CARDNO,SALETIME,SALESOURE,SALEPEOPLE,REMARK,CARDCEATETIME,ENDTIME,ISEXPERCLASS,CLASSNAME,CLASSTIME,
        CLASSPRICE,ADVISEPRICE,SALEPRICE,GIVECLASS,PARENTNAME,PHONE,BABYNAME,BABYAGE,BABYSEX,BABYBRITH)
      <foreach collection="list" item="item" index="index" separator="UNION ALL" >
          ( select #{item.id},
          #{item.cardno},
          #{item.saleTime},
          #{item.saleSoure},
          #{item.salePeople},
          #{item.remark},
          #{item.cardCeateTime},
          #{item.endTime},
          #{item.isExperClass},
          #{item.className},
          #{item.classTime},
          #{item.classPrice},
          #{item.advisePrice},
          #{item.salePrice},
          #{item.giveClass},
          #{item.parentName},
          #{item.phone},
          #{item.babyName},
          #{item.babyAge},
          #{item.babySex},
          #{item.babyBrith} from dual)
      </foreach>
    </insert>
           

就可以直接導入到資料庫中了，此處資料庫是Oracle。

一點毛病也沒有~