java springmvc + mybatis 批量导出excel数据到mysql

最新推荐文章于 2023-04-10 14:27:29 发布

BlingBlingjuan

最新推荐文章于 2023-04-10 14:27:29 发布

阅读量880

点赞数 2

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/BlingBlingjuan/article/details/78981013

本文详细介绍了如何使用Java的SpringMVC和MyBatis框架，配合处理Excel数据，实现从Excel文件批量导入到MySQL数据库的过程。控制器层（Controller）作为关键交互点，协调业务逻辑和数据操作，确保数据准确无误地迁移。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

controller

@RequestMapping(value = "/import/index", method = RequestMethod.POST)
@ResponseBody
public ResponseResult upload(HttpServletRequest request,@RequestParam(value="file", required=true) MultipartFile file) {
    ResponseResult<Object> responseResult = new ResponseResult<>();
    //判断文件是否为空
    if(file == null) {
        responseResult.setSuccess(false);
        responseResult.setErrorMessage("文件为空");
        return responseResult;
    }
    //获取文件名
    String name = file.getOriginalFilename();
    //进一步判断文件是否为空（即判断其大小是否为0或其名称是否为null）
    long size = file.getSize();
    if(name == null || ("").equals(name) && size == 0) {
        responseResult.setSuccess(false);
        responseResult.setErrorMessage("null");
        return responseResult;
    }
    Map<String, Integer> dataMap = new HashMap<>();
    try {
        //批量导入。参数：文件名，文件。
       dataMap = goodsService.batchImport(name, file);
    }catch (Exception e){
        e.printStackTrace();
        responseResult.setSuccess(false);
        responseResult.setErrorMessage("批量导入XX异常！");
    }
    responseResult.setSuccess(true);
    responseResult.setData(dataMap);
    responseResult.setErrorMessage("批量导入EXCEL成功！");
    return responseResult;
}

util

/**
 * 读EXCEL文件，获取客户信息集合
 * @param
 * @return
 */
public Map<String, Integer> getExcelInfo(String name, MultipartFile file) {
    Map<String, Integer> dataMap = new HashMap<>();
    try {
        /** 验证文件是否合法 */
        if (!validateExcel(name)) {
            return null;
        }
        /** 判断文件的类型，是2003还是2007 */
        boolean isExcel2003 = true;
        if (WDWUtil.isExcel2007(name)) {
            isExcel2003 = false;
        }
        /** 调用本类提供的根据流读取的方法 */

        //根据excel里面的内容读取客户信息
        dataMap = createExcel(file.getInputStream(), isExcel2003);

    } catch (Exception ex) {
        ex.printStackTrace();
    }
    /** 返回最后读取的结果*/
    return dataMap;
}
/**
 * 读取
 * @param is 输入流
 * @param isExcel2003 excel是2003还是2007版本
 * @return
 * @throws IOException
 */
public Map<String, Integer> createExcel(InputStream is, boolean isExcel2003) {
    {
        Map<String, Integer> dataMap = new HashMap<>();
        try {
            /** 根据版本选择创建Workbook的方式 */
            Workbook wb = null;
            if (isExcel2003) {
                wb = new HSSFWorkbook(is);
            } else {
                wb = new XSSFWorkbook(is);
            }
            //读取
            dataMap = readExcelValue(wb);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return dataMap;
    }
}

/**
 * 获取excel数据
 * @param wb
 * @return
 */
private Map<String, Integer> readExcelValue (Workbook wb) {
    Map<String, Integer> dataMap = new HashMap<>();

    //得到第一个shell
    Sheet sheet = wb.getSheetAt(0);
    //得到Excel的行数
    this.totalRows = sheet.getPhysicalNumberOfRows();
    //得到Excel的列数(前提是有行数)
    if (totalRows >= 1 && sheet.getRow(0) != null) {
        this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
    }
    Map<Integer , Goods> goodsMap = new HashMap<>();
    Map<Integer, GoodsExport> exportMap = new HashMap<>();
    //循环Excel行数,从第二行开始。标题不入库
    List<Integer> notNullCell = new ArrayList<>();
    notNullCell.add(0);
    notNullCell.add(1);
    notNullCell.add(2);
    notNullCell.add(5);
    notNullCell.add(7);
    notNullCell.add(8);
    notNullCell.add(10);
    notNullCell.add(11);
    notNullCell.add(12);
    notNullCell.add(15);
    for (int r = 1; r < totalRows; r++) {
        Row row = sheet.getRow(r);
        if(isRowEmpty(row))continue;
        //if (row == null) continue;

        Goods goods = new Goods();
        GoodsExport goodsExport = new GoodsExport();
        int errCell = 0;

        //循环Excel的列
        for (int c = 0; c < this.totalCells; c++) {
            Cell cell = row.getCell(c);
            if(cell== null && notNullCell.contains(c)){
                errCell ++;
            }
            if (null != cell) {
                if (c == 0) {
                    //获取分类
                    goodsExport.setCategoryName(getCellValue(cell));
                    if(cell.getCellType()!= Cell.CELL_TYPE_STRING){
                        errCell ++;
                        continue;
                    }
                    continue;
                } 
            }
        }
        if(errCell > 0 ){
            exportMap.put(row.getRowNum(), goodsExport);
        }else {
            goodsMap.put(row.getRowNum(), goods);
        }
    }

    //从03格式excel中获取图片
        if(wb instanceof HSSFWorkbook) {
        List<HSSFPictureData> pictures = (List<HSSFPictureData>) wb.getAllPictures();
        HSSFSheet hsheet = (HSSFSheet) sheet;
        if (pictures.size() != 0) {
            for (HSSFShape shape : hsheet.getDrawingPatriarch().getChildren()) {
                HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
                int row = anchor.getRow1();
                if (shape instanceof HSSFPicture) {
                    HSSFPicture pic = (HSSFPicture) shape;
                    int pictureIndex = pic.getPictureIndex() - 1;
                    HSSFPictureData pictureData = pictures.get(pictureIndex);
                    byte[] picData = pictureData.getData();      
                    if(goods!=null){
                        uploadImgVo.setType(1);
                        List<GoodsImg> goodsImgList = goods.getGoodsImgList();
                        if(goodsImgList == null){
                            goodsImgList = new ArrayList<>();
                        }   
                    }
                    GoodsExport goodsExport = exportMap.get(row);
                    if(goodsExport != null){
                        List<String> imgs = goodsExport.getImgs();
                        if(imgs ==null){
                            imgs = new ArrayList<>();
                        }
                        imgs.add(picStr);
                        goodsExport.setImgs(imgs);
                        exportMap.put(row, goodsExport);
                    }
                }
            }
        }
    }else if(wb instanceof  XSSFWorkbook ){
            //07格式excel获取图片
            XSSFSheet hsheet =  (XSSFSheet) sheet ;
            for (POIXMLDocumentPart dr : hsheet.getRelations()) {
                if (dr instanceof XSSFDrawing) {
                    XSSFDrawing drawing = (XSSFDrawing) dr;
                    for (XSSFShape shape : shapes) {
                        XSSFPicture pic = (XSSFPicture) shape;
                        XSSFPictureData pictureData = pic.getPictureData();
                        byte[] picData =pictureData.getData();
                        XSSFClientAnchor anchor = pic.getPreferredSize();
                        CTMarker ctMarker = anchor.getFrom();
                        int row = ctMarker.getRow();
                        Goods goods = goodsMap.get(row);
                        if(goods!=null){
                            UploadImgVo uploadImgVo = new UploadImgVo();
                            uploadImgVo.setType(1);
                            if(goodsImgList == null){
                                goodsImgList = new ArrayList<>();
                            }
                        }
                        if(goodsExport != null){
                            List<String> imgs = goodsExport.getImgs();
                            if(imgs ==null){
                                imgs = new ArrayList<>();
                            }
                            exportMap.put(row, goodsExport);
                        }
                    }
                }
            }
        }
    return dataMap;
}