1500字范文 > java实现excel导入导出对象图片读取上传七牛云

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

时间：2019-09-25 22:07:38

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

因为公司业务需求，要完成针对表格的导入导出，excel这里使用MultipartFile类接收

，下面是部分关键代码，希望有所帮助

//获取excel文件的输入流，必须是.xlsx后缀，如果是xlsx后缀，要用HSSFWorkBookXSSFWorkbook xssfWorkbook = new XSSFWorkbook(multipartFile.getInputStream());//获取表格XSSFSheet sheetAt = xssfWorkbook.getSheetAt(0);// 创建sheetSheet 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);

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

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

/*** 获取Excel图片 \ 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 excelHSSFWorkbook embeddedWorkbook = new HSSFWorkbook(packagePart.getInputStream());printsImg(packagePart.getInputStream(),"xlx");break;}// Excel Workbook - OpenXML file format offic excelcase "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet": {XSSFWorkbook embeddedWorkbook = new XSSFWorkbook(packagePart.getInputStream());printsImg(packagePart.getInputStream(),"xlsx");break;}// Word Document - binary (OLE2CDF) file format offic wordcase "application/msword": {HWPFDocument document = new HWPFDocument(packagePart.getInputStream());printsImg(packagePart.getInputStream(),"doc");break;}// Word Document - OpenXML file format case "application/vnd.openxmlformats-officedocument.wordprocessingml.document": {XWPFDocument document = new XWPFDocument(packagePart.getInputStream());printsImg(packagePart.getInputStream(),"docx");break;}// PowerPoint Document - binary file format pptcase "application/vnd.ms-powerpoint": {HSLFSlideShow slideShow = new HSLFSlideShow(packagePart.getInputStream());printsImg(packagePart.getInputStream(),"ppt");break;}// PowerPoint Document - OpenXML file formatcase "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" };@Resourceprivate 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;}}