导入excel表格数据

javaWeb导入excel表格数据，表格包含图片，保存到数据库中

导入excel表格数据

做个记录吧，方便查找
导入后缀是xls的表格的方法

    private List<FoodBean> readXls(String path,HttpServletRequest request) throws IOException, InvalidFormatException {
        HSSFWorkbook hssfWorkbook = null;
        try {
            InputStream is = new FileInputStream(path);
            hssfWorkbook = new HSSFWorkbook(is);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        BigDecimal bd2 = null;
        FoodBean foodBean = null;
        Sheet sheet = null;
        List<FoodBean> list = new ArrayList<FoodBean>();
        File file = new File(path);
        // 创建流
        InputStream input = new FileInputStream(file);
        HSSFWorkbook  wb = (HSSFWorkbook) WorkbookFactory.create(input);
        String name=null;
        PictureData sheetIndexPicMap=null;
        if (hssfWorkbook != null) {
            for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
                HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
                if (hssfSheet == null) {
                    continue;
                }
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                    sheet=wb.getSheetAt(numSheet);
                    // map等待存储excel图片
                        sheetIndexPicMap = getSheetPictrues03(rowNum, (HSSFSheet)sheet);
                    if (hssfRow != null) {
                        foodBean = new FoodBean();
                        //默认图片
                        if(sheetIndexPicMap==null){
                            name="/upload/xxx/more.png";
                        }else{
                            name= printImg(sheetIndexPicMap,request).get(0);
                        }
                        HSSFCell foodname = hssfRow.getCell(1);
                        HSSFCell price = hssfRow.getCell(2);
                        foodBean.setImgurl(name);
                        foodBean.setFoodname(getValue(foodname));
                        bd2 = new BigDecimal(price.toString());
                        foodBean.setPrice(bd2.toPlainString());
                        list.add(foodBean);
                    }
                }
            }
        }
        return list;
    }
    //xls表格图片信息
    public static PictureData getSheetPictrues03(int sheetNum,
                                                              HSSFSheet sheet) {

        Map<Integer, PictureData> result = new HashMap<Integer, PictureData>();
        if(sheet.getDrawingPatriarch()!=null){
        List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
        for (HSSFShape shape : list) {
            if (shape instanceof HSSFPicture) {
                HSSFPicture picture = (HSSFPicture) shape;
                HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
                HSSFPictureData pdata = picture.getPictureData();
                Integer key = cAnchor.getRow1(); // 行号
                result.put(key, pdata);
            }
        }
        }
   return result.get(sheetNum);
    }

导入xlsx后缀的表格方法

 private List<FoodBean> readXlsx(String path,HttpServletRequest request) throws IOException, InvalidFormatException {

        XSSFWorkbook xssfWorkbook = null;
        try {
            InputStream is = new FileInputStream(path);
            xssfWorkbook = new XSSFWorkbook(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
        BigDecimal bd2 = null;
        FoodBean foodBean = null;
        List<FoodBean> list = new ArrayList<FoodBean>();
        Sheet sheet = null;
        String name=null;
        PictureData sheetIndexPicMap=null;
           File file = new File(path);
        // 创建流
        InputStream input = new FileInputStream(file);
        XSSFWorkbook wb= (XSSFWorkbook)WorkbookFactory.create(input);
        if (xssfWorkbook != null) {
            // Read the Sheet
            for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
                XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
                if (xssfSheet == null) {
                    continue;
                }
                for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                    XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                    sheet=wb.getSheetAt(numSheet);
                    sheetIndexPicMap = getSheetPictrues07(rowNum, (XSSFSheet) sheet);
                    if (xssfRow != null) {
                        foodBean = new FoodBean();
                        if(sheetIndexPicMap==null){
                            name="/upload/xxx/more.png.png";
                        }else{
                            name= printImg(sheetIndexPicMap,request).get(0);
                        }
                        XSSFCell foodname = xssfRow.getCell(1);
                        XSSFCell price = xssfRow.getCell(2);
                        foodBean.setFoodname(getValue(foodname));
                        bd2 = new BigDecimal(price.toString());
                        foodBean.setPrice(bd2.toPlainString());
                        foodBean.setImgurl(name);
                        list.add(foodBean);
                    }
                }
            }
        }
        return list;
    }
        //xlsx版本表格的图片信息
    public static PictureData getSheetPictrues07(int sheetNum,
                                                              XSSFSheet sheet) {
        Map<Integer, PictureData> sheetIndexPicMap = new HashMap<Integer, PictureData>();
        for (POIXMLDocumentPart dr : sheet.getRelations()) {
            if (dr instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) dr;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    XSSFPicture pic = (XSSFPicture) shape;
                    XSSFClientAnchor anchor = pic.getPreferredSize();
                    CTMarker ctMarker = anchor.getFrom();
                    Integer picIndex =ctMarker.getRow() ;
                    sheetIndexPicMap.put(picIndex, pic.getPictureData());
                }
            }
        }
        return sheetIndexPicMap.get(sheetNum);
    }

图片上传

 //图片上传
    public static List<String> printImg(PictureData picData,HttpServletRequest request) throws IOException {
             List<String> name=new ArrayList<String>();
                // 获取图片流
                PictureData pic = picData;
                // 设置图片名字
                String picName = picData.getData().toString().substring(1);
                // 获取图片格式
                String ext = pic.suggestFileExtension();
                byte[] data = pic.getData();
                String path = request.getSession().getServletContext().getRealPath("upload") + "\\foodImg" + "\\" + picName+"."+ext;
                System.out.println("dddddd+"+path);
                System.out.println("picNamepicName+"+picName);
                name.add("\\upload"+"\\xxx" + "\\" + picName+"."+ext);
                FileOutputStream out = new FileOutputStream(path);
                out.write(data);
                out.close();
        return name;
    }