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);
} @Override
public 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;
}结果：
