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("点击下载附件"); 最后在附上七牛云工具类 @Slf4jpublic 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表的过程，当中的难点解决方式，也希望有大佬指点一下，谢谢。