Java导出图片到excel
1、例如你有这样一个集合,具体结合你的业务场景
@Data
public class Student {/*** 姓名*/private String xh;/*** 学号*/private String xm;/*** 照片*/private byte[] zp;
}
2、相关代码
@RequestMapping(value = "/quereImgByPkid/{pkid}",method = RequestMethod.GET)public void test(@PathVariable("pkid") String pkid, HttpServletResponse response){try {// 业务代码List<Student> students = xscnsService.queryImgByPkidAndType(pkid);// 设置响应头response.setContentType("application/octet-stream");response.setHeader("Content-Disposition", "attachment; filename=students.xlsx");try (OutputStream outputStream = response.getOutputStream();Workbook workbook = new XSSFWorkbook()) {Sheet sheet = workbook.createSheet("Student Data");// 创建表头Row header = sheet.createRow(0);header.createCell(0).setCellValue("学号");header.createCell(1).setCellValue("姓名");header.createCell(2).setCellValue("照片");int rowIndex = 1;for (Student student : students) {Row row = sheet.createRow(rowIndex++);// 设置行高(行高设置为100点)row.setHeightInPoints(100);row.createCell(0).setCellValue(student.getXh());row.createCell(1).setCellValue(student.getXm());// 插入照片int pictureIdx = workbook.addPicture(student.getZp(), Workbook.PICTURE_TYPE_JPEG);CreationHelper helper = workbook.getCreationHelper();Drawing<?> drawing = sheet.createDrawingPatriarch();ClientAnchor anchor = helper.createClientAnchor();anchor.setCol1(2); // 照片列anchor.setRow1(rowIndex - 1); // 当前行// 将图片大小调整为单元格大小anchor.setCol2(3); // 结束列anchor.setRow2(rowIndex); // 结束行Picture pict = drawing.createPicture(anchor, pictureIdx);// 不调用resize,让图片完全适应单元格}// 将数据写入到响应输出流中workbook.write(outputStream);outputStream.flush();}} catch (Exception e) {e.printStackTrace();}}
3、访问接口得以下载,如下
4、拓展(实战代码)
@GetMapping("/exportFjPf")public void exportFjPf(@RequestParam String uuid, HttpServletResponse response) {wsjcService.exportFjPf(uuid,response);}
@Overridepublic void exportFjPf(String uuid, HttpServletResponse response) {//1、获取缓存的数据Object obj = NHRedisUtils.getRedisResult(uuid);if (obj == null) {throw new NHWarmingException("导出Excel失败,请稍后再试");}Map<String, Object> map = (Map<String, Object>) obj;List<Map<String, Object>> list = (List<Map<String, Object>>) map.get("list");// 文件名字,防止乱码String fileName = "房间评分信息.xlsx";// 针对不同的浏览器进行文件名编码String encodedFileName = null;try {encodedFileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");} catch (UnsupportedEncodingException e) {throw new RuntimeException(e);}response.setContentType("application/octet-stream");// 对于不同的浏览器设置不同的 Content-Disposition 头String headerValue = String.format("attachment; filename=\"%s\"; filename*=UTF-8''%s", encodedFileName, encodedFileName);response.setHeader("Content-Disposition", headerValue);try (OutputStream outputStream = response.getOutputStream(); Workbook workbook = new XSSFWorkbook()) {Sheet sheet = workbook.createSheet("房间评分信息");// 设置统一的列宽(比如统一设置为20个字符宽度)int columnWidth = 20 * 256;// 创建一个单元格样式,设置居中对齐CellStyle cellStyle = workbook.createCellStyle();// 水平居中cellStyle.setAlignment(HorizontalAlignment.CENTER); // 垂直居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 创建一个标题单元格样式,设置居中对齐和加粗字体CellStyle headerStyle = workbook.createCellStyle();headerStyle.setAlignment(HorizontalAlignment.CENTER);headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 设置字体加粗Font headerFont = workbook.createFont();headerFont.setBold(true);headerStyle.setFont(headerFont);// 创建表头字段String[] headers = {"序号", "校区", "楼栋","楼层","房间","学院","专业","班级","所属年级","所属辅导员","评分","评分等级","评分人","评分时间","评语","上传图片"};// 对应的字段String[] column = {"ROW_ID", "XQMC", "LDMC","LCMC","FJ","BMMC","ZYMC","BJMC","SSNJ","FDY","PF","PFDJMC","PFR","PFSJ","PY"};// 创建表头Row headerRow = sheet.createRow(0);for (int i = 0; i < headers.length; i++) {headerRow.setHeightInPoints(30);Cell cell = headerRow.createCell(i);cell.setCellValue(headers[i]);cell.setCellStyle(headerStyle);// 设置每一列的宽度sheet.setColumnWidth(i, columnWidth);}int rowIndex = 1;for (Map<String,Object> item : list) {Row row = sheet.createRow(rowIndex++);for (int i = 0; i < column.length; i++) {// 设置行高 100点row.setHeightInPoints(100);Cell cell = row.createCell(i);cell.setCellValue(getStrByObject(item.get(column[i])));cell.setCellStyle(cellStyle);}List<byte []> pictureList = getPicture(item.get("SCTP"));for (int i = 0; i < pictureList.size(); i++) {sheet.setColumnWidth(column.length + i,columnWidth);// 插入照片int pictureIdx = workbook.addPicture(pictureList.get(i), Workbook.PICTURE_TYPE_JPEG);CreationHelper helper = workbook.getCreationHelper();Drawing<?> drawing = sheet.createDrawingPatriarch();ClientAnchor anchor = helper.createClientAnchor();anchor.setCol1(column.length + i); // 照片列anchor.setRow1(rowIndex - 1); // 当前行// 将图片大小调整为单元格大小anchor.setCol2(column.length + i + 1); // 结束列anchor.setRow2(rowIndex); // 结束行Picture pict = drawing.createPicture(anchor, pictureIdx);//不调用resize,让图片完全适应单元格}}// 将数据写入到响应输出流中workbook.write(outputStream);outputStream.flush();} catch (Exception e) {logger.error(e.getMessage(), e);throw new RuntimeException(e);}}private List<byte[]> getPicture(Object sctp) {List<byte[]> bytes = new ArrayList<>();String strByObject = getStrByObject(sctp);if(strByObject == null){return bytes;}// 开始远程调用Object fjDetail = dynamicServiceFeignClient.getDetailed(strByObject);// 调用日志logger.warn("附件主表req:{},rep:{}", strByObject, fjDetail);List<Map<String, Object>> maps = new ArrayList<>();if (fjDetail != null) {Map map = (Map) fjDetail;Object data = map.get("data");maps = (List<Map<String, Object>>) data;}List<String> uuids = maps.stream().map(a -> (String) a.get("uuid")).collect(Collectors.toList());for (int i = 0; i < uuids.size(); i++) {// 开始调用详细附件明细Response fileMessage = docrepoServiceFeignClient.getFileInfo(uuids.get(i), "admin", "admin");// 打印日志logger.warn("根据uuid拿到资源req:{},resp:{}", uuids.get(i), fileMessage);if (fileMessage == null || fileMessage.status() != 200) {continue;}// 拿到文件流try (ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream()){InputStream imageStream = fileMessage.body().asInputStream();byte[] buffer = new byte[1024];int bytesRead;while ((bytesRead = imageStream.read(buffer)) != -1) {byteArrayOutputStream.write(buffer, 0, bytesRead);}byte[] imageBytes = byteArrayOutputStream.toByteArray();bytes.add(imageBytes);} catch (IOException e) {throw new RuntimeException(e);}}return bytes;}private String getStrByObject(Object str){if(str != null){return str.toString();}return null;}
结果: