java实现excel导入导出，对象图片读取，上传七牛云

java实现excel导入导出以及解决方案

因为公司业务需求，要完成针对表格的导入导出，excel这里使用MultipartFile类接收
，下面是部分关键代码，希望有所帮助

 			//获取excel文件的输入流，必须是.xlsx后缀，如果是xlsx后缀，要用HSSFWorkBook
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(multipartFile.getInputStream());
            //获取表格
            XSSFSheet sheetAt = xssfWorkbook.getSheetAt(0);
            // 创建sheet
            Sheet sheet = null;
            //获取excel sheet总数
//            int sheetNumbers = xssfWorkbook.getNumberOfSheets();
//            // sheet list
//            List<Map<String, PictureData>> sheetList = new ArrayList<Map<String, PictureData>>();
            sheet = xssfWorkbook.getSheetAt(0);
            // map等待存储excel图片
            Map<String, PictureData> sheetIndexPicMap;

            // 判断获取图片和对象
            XSSFWorkbook sheetPictrues07 = getSheetPictrues07(0, (XSSFSheet) sheet, xssfWorkbook);
            XSSFSheet sheetAt2 = sheetPictrues07.getSheetAt(0);
            sheet.shiftRows(1, 1, -1);

下面的代码是只获取图片的处理

    /**
     * 获取Excel2007图片 \ word \execl \PowerPoint
     *
     * @param sheetNum 当前sheet编号
     * @param sheet    当前sheet对象
     * @param workbook 工作簿对象
     * @return Map key:图片单元格索引（0_1_1）String，value:图片流PictureData
     */
    public XSSFWorkbook getSheetPictrues07(int sheetNum, XSSFSheet sheet, XSSFWorkbook workbook) throws IOException, OpenXML4JException, XmlException {
        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();
                    //获取表格簿
                    XSSFSheet sheetAt = workbook.getSheetAt(0);
                    //获取行
                    XSSFRow row = sheetAt.getRow(ctMarker.getRow());
                    //创建列
                    XSSFCell cell = row.createCell(ctMarker.getCol());
                    //填入其对应上传七牛云的图片编号
                    cell.setCellValue(printsImg(pic.getPictureData()));
                }
            }

如果你还要获取嵌入对象的话，需要判断其对象文件的隐性xml格式类型

  /**
     * 获取Excel2007图片 \ word \execl \PowerPoint
     *
     * @param sheetNum 当前sheet编号
     * @param sheet    当前sheet对象
     * @param workbook 工作簿对象
     * @return Map key:图片单元格索引（0_1_1）String，value:图片流PictureData
     */
    public XSSFWorkbook getSheetPictrues07(int sheetNum, XSSFSheet sheet, XSSFWorkbook workbook) throws IOException, OpenXML4JException, XmlException {
        for (POIXMLDocumentPart dr : sheet.getRelations()) {
            PackagePart packagePart = dr.getPackagePart();
            String contentType = packagePart.getContentType();
            //获取表格簿
            XSSFSheet sheetAt = workbook.getSheetAt(0);
            switch (contentType) {
                case "application/vnd.ms-excel": {//offic 2003 excel
                    HSSFWorkbook embeddedWorkbook = new HSSFWorkbook(packagePart.getInputStream());
                    printsImg(packagePart.getInputStream(),"xlx");
                    break;
                }
                // Excel Workbook - OpenXML file format offic 2007 excel
                case "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet": {
                    XSSFWorkbook embeddedWorkbook = new XSSFWorkbook(packagePart.getInputStream());
                    printsImg(packagePart.getInputStream(),"xlsx");
                    break;
                }
                // Word Document - binary (OLE2CDF) file format offic 2003 word
                case "application/msword": {
                    HWPFDocument document = new HWPFDocument(packagePart.getInputStream());
                    printsImg(packagePart.getInputStream(),"doc");
                    break;
                }
                // Word Document - OpenXML file format 2007
                case "application/vnd.openxmlformats-officedocument.wordprocessingml.document": {
                    XWPFDocument document = new XWPFDocument(packagePart.getInputStream());
                    printsImg(packagePart.getInputStream(),"docx");
                    break;
                }
                // PowerPoint Document - binary file format 2003 ppt
                case "application/vnd.ms-powerpoint": {
                    HSLFSlideShow slideShow = new HSLFSlideShow(packagePart.getInputStream());
                    printsImg(packagePart.getInputStream(),"ppt");
                    break;
                }
                // PowerPoint Document - OpenXML file format
                case "application/vnd.openxmlformats-officedocument.presentationml.presentation": {
                    OPCPackage docPackage = OPCPackage.open(packagePart.getInputStream());
                    XSLFSlideShow slideShow = new XSLFSlideShow(docPackage);
                    printsImg(packagePart.getInputStream(),"pptx");
                    break;
                }
                //PowerPoint Document - OpenXML file format  照片
                case "application/vnd.openxmlformats-officedocument.drawing+xml": {
                    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();
                            //获取行
                            XSSFRow row = sheetAt.getRow(ctMarker.getRow());
                            //创建列
                            XSSFCell cell = row.createCell(ctMarker.getCol());
                            //填入其对应上传七牛云的图片编号
                            cell.setCellValue(printsImg(pic.getPictureData()));
                        }
                    }

                    break;
                }
//                 Any other type of embedded object.
                default:
                    System.out.println("Unknown Embedded Document: " + contentType);
//                    InputStream inputStream = packagePart.getInputStream();
//                    (Worksheet)sheet;
                    OPCPackage aPackage = packagePart.getPackage();
                    workbook.getSheetIndex(packagePart.getPartName().getName());
                    break;
            }

接下来是导出，但接下来说明一下，两个难点（个人认为）

• 导入时

在表格里面的图片，我可以获取到他的所处位置，然后上传至七牛云，然后将其图片地址插入对应位置的单元格中，是可以的。
但是如果，你的是文件的话，判断文件类型之后，我只能通过packagePart.getInputStream()去获取文件的流，读取到文件，可是我无法获取到文件的位置（所处单元格位置），就没办法和该行的数据对应，我就不能知道他是哪一行数据的携带附件

• 导出时

如果是将文件插入对应的单元格，如果是图片的话，是可以的
在java插入excel是有盲区的，poi反正我尝试了很久，往对应的单元格当中插入对象文件，还是不行，
我无法通过XSSFSheet对象 或者XSSword对象去插入它，图片的到是有一个如下图

				ByteArrayOutputStream byteArrayOut = excelData(list.get(i).getPhoto());
                //图片格式距离单元格left，top，right，bottom的像素距离
                XSSFClientAnchor anchor1 = new XSSFClientAnchor(0, 0, 0, 0, 7,i +1, 8, index);
                drawingPatriarch.createPicture(anchor1, wb.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));

所以最后决定换成插入的附件文件采用超链接的方式，代码如下

 //文件生成超链接方式
                    XSSFCreationHelper creationHelper = wb.getCreationHelper();
                    XSSFHyperlink hyperlink = (XSSFHyperlink) creationHelper.createHyperlink(HyperlinkType.URL);
                    hyperlink.setAddress("域名"+list.get(i).getSurveyManual());
                    row.createCell(2).setHyperlink(hyperlink);
                    row.createCell(2).setCellValue("点击下载附件");

最后在附上七牛云工具类

@Slf4j
public class QiniuCloudUtil {
    /**
     * 设置需要操作的账号的AK和SK
     */
    private static final String ACCESS_KEY = "xxxxxxxxxxxxxxxxxxxxxxxxxx";
    private static final String SECRET_KEY = "xxxxxxxxxxxxxxxxxxxxxxxxxx";
    // 要上传的空间名
    private static final String BUCKETNAME = "xxxxxxxxxx";
    /**
     * 外链域名 读取时使用
     */
    private static final String DOMAIN = "xxxxxxxxxxxxxxxxxxx";

    /**
     * 密钥
     */
    private static final Auth AUTH = Auth.create(ACCESS_KEY, SECRET_KEY);

    /**
     * 文件允许的后缀扩展名
     */
    public static String[] IMAGE_FILE_ETD = new String[] { "png", "bmp", "jpg", "jpeg","pdf" };

    @Resource
    private RestTemplate restTemplate;


    /**
     * 上传
     *
     * @param file
     * @return
     * @throws IOException
     */
    public static String upload(InputStream file, String ext) throws IOException {
        // 创建上传对象，Zone*代表地区
        Region region = Region.region2();
        Configuration configuration = new Configuration(region);
        UploadManager uploadManager = new UploadManager(configuration);
        try {
            // 调用put方法上传
            String token = AUTH.uploadToken(BUCKETNAME);
            if (StringUtils.isEmpty(token)) {
                System.out.println("未获取到token，请重试！");
                return null;
            }

            String imageName ="fileupload/" + UUID.randomUUID().toString().replaceAll("-","")+"."+ext;
            System.out.println("File name = " + imageName);
            Response res = uploadManager.put(file, imageName, token,null,null);
            // 打印返回的信息
            if (res.isOK()) {
                Map map = JSON.parseObject(res.bodyString(), Map.class);
                return map.get("key").toString();
            }
        } catch (QiniuException e) {
            Response r = e.response;
            // 请求失败时打印的异常的信息
            e.printStackTrace();
            log.error("error " + r.toString());
            try {
                // 响应的文本信息
                log.error(r.bodyString());
            } catch (QiniuException e1) {
                log.error("error " + e1.error());
            }
        }
        return null;
    }

    /**
     * 上传
     *
     * @param file
     * @return
     * @throws IOException
     */
    public static String uploadFile(byte[] file, String ext) throws IOException {
        // 创建上传对象，Zone*代表地区
        Region region = Region.region2();
        Configuration configuration = new Configuration(region);
        UploadManager uploadManager = new UploadManager(configuration);
        try {
            // 调用put方法上传
            String token = AUTH.uploadToken(BUCKETNAME);
            if (StringUtils.isEmpty(token)) {
                System.out.println("未获取到token，请重试！");
                return null;
            }
            ByteArrayOutputStream output = new ByteArrayOutputStream();
            byte[] buffer = new byte[4096];
            int n = 0;
//            while (-1 != (n = file.read(buffer))) {
//                output.write(buffer, 0, n);
//            }

            String fileName ="excelFile/" + UUID.randomUUID().toString().replaceAll("-","")+"."+ext;
            System.out.println("File name = " + fileName);
            Response res = uploadManager.put(file, fileName, token);
            // 打印返回的信息
            if (res.isOK()) {
                Map map = JSON.parseObject(res.bodyString(), Map.class);
                return map.get("key").toString();
            }
        } catch (QiniuException e) {
            Response r = e.response;
            // 请求失败时打印的异常的信息
            e.printStackTrace();
            log.error("error " + r.toString());
            try {
                // 响应的文本信息
                log.error(r.bodyString());
            } catch (QiniuException e1) {
                log.error("error " + e1.error());
            }
        }finally {
//            output.close();
        }
        return null;
    }

    public static String uploadFileStream(InputStream inputStream, String ext) throws IOException {
        // 创建上传对象，Zone*代表地区
        Region region = Region.region2();
        Configuration configuration = new Configuration(region);
        UploadManager uploadManager = new UploadManager(configuration);
        try {
            // 调用put方法上传
            String token = AUTH.uploadToken(BUCKETNAME);
            if (StringUtils.isEmpty(token)) {
                System.out.println("未获取到token，请重试！");
                return null;
            }
            String fileName ="excelFile/" + UUID.randomUUID().toString().replaceAll("-","")+"."+ext;
            System.out.println("File name = " + fileName);
            Response res = uploadManager.put(inputStream, fileName, token,null,null);
            // 打印返回的信息
            if (res.isOK()) {
                Map map = JSON.parseObject(res.bodyString(), Map.class);
                return map.get("key").toString();
            }
        } catch (QiniuException e) {
            Response r = e.response;
            // 请求失败时打印的异常的信息
            e.printStackTrace();
            log.error("error " + r.toString());
            try {
                // 响应的文本信息
                log.error(r.bodyString());
            } catch (QiniuException e1) {
                log.error("error " + e1.error());
            }
        }finally {
            inputStream.close();
        }
        return null;
    }


    /**
     * 下载数据
     * @param fileUrl
     * @return
     * @throws IOException
     */
    public  byte[] download(String fileUrl) throws IOException {
        ResponseEntity<byte[]> res = restTemplate.exchange(fileUrl, HttpMethod.GET, null, byte[].class);
        byte[] body = res.getBody();
        return body;
    }


    /**
     * 验证文件格式
     * @param fileName
     * @return
     */
    public static boolean isFileAllowed(String fileName) {
        for (String ext : IMAGE_FILE_ETD) {
            if (ext.equals(fileName)) {
                return true;
            }
        }
        return false;
    }

}

以上就是java导出导入excel表的过程，当中的难点解决方式，也希望有大佬指点一下，谢谢。