该文章展示了如何在基于SpringBoot的Web项目中，利用ApachePOI库将图片插入到Excel工作簿中。代码示例详细解释了设置Maven依赖、创建Excel工作表、调整单元格大小以及添加和定位图片的方法。最后，文章提供了一个实际的导出数据方法，包括创建标题和填充数据，以及处理图片的逻辑。
基于SpringBoot Web项目
maven 依赖：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
demo
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
public class Test {
public static void main(String[] args) {
HSSFWorkbook book=new HSSFWorkbook();
HSSFSheet sheet=book.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
BufferedImage bufferedImage = null;
try {
String[] imgs = {"D:\\photos\\792b6604637e40de2f0c800db6648122.jpeg",
"D:\\photos\\d701e5d7f2f32d27ff3cb8e919e56b61.jpeg",
"D:\\photos\\e1885b439f188ef9ed1ab209af35b3ee.jpeg" };
// 计算边距
int mar = 10 + 10 + (imgs.length - 1) * 10;
// 大致平均值,每个图片宽度(1023为每个单元格总比，）
int ave = (1023 - mar) / imgs.length;
//设置图片位于表格的单元格下标
int hang=0;
int lie=0;
HSSFRow row = sheet.createRow(hang);
//设置单元个宽高，单元格宽高限制了图片的宽高
row.setHeight((short)(100*10));
sheet.setColumnWidth(lie,4800*imgs.length);
for (int i = 0; i < imgs.length; i++) {
File file = new File(imgs[i]);
bufferedImage = ImageIO.read(file);
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
ImageIO.write(bufferedImage, "jpg", byteArrayOutputStream);
HSSFClientAnchor hSSFClientAnchor=null;
//dx1:图片左上角x坐标, dy1:图片左上角y坐标范, dx2:图片右下角x坐标, dy2:图片右下角y坐标。坐标x轴总比1023，坐标y轴总比255,图片排列样式根据左上角与右下角坐标比例排列
hSSFClientAnchor = new HSSFClientAnchor(10 * (i + 1) + ave * i, 10,(10 + ave) * (i + 1), 245, (short) lie, hang, (short) lie, hang);
patriarch.createPicture(hSSFClientAnchor,book.addPicture(byteArrayOutputStream.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
}
FileOutputStream outputStream = new FileOutputStream("C:\\Users\\LENOVO\\Desktop\\photo\\template\\test.xls");
book.write(outputStream);
outputStream.close();
} catch (Exception io) {
io.printStackTrace();
}
}
}
项目实战：
先抽取方法
/**
*
* @param workbook
* @param patriarch
* @param fileMetadataList 文件元数据列表
* @param hang 第几行
* @throws IOException
*/
private void generatePicture(HSSFWorkbook workbook,HSSFPatriarch patriarch,List<SzzcFileMetadata> fileMetadataList,int hang) throws IOException{
//设置图片位于表格的单元格下标
int lie=2;
BufferedImage bufferedImage = null;
if (CollectionUtils.isEmpty(fileMetadataList)){
return;
}
for (int i = 0; i < fileMetadataList.size(); i++) {
// 计算边距
int mar = 10 + 10 + (fileMetadataList.size() - 1) * 10;
// 大致平均值,每个图片宽度(1023为每个单元格总比，）
int ave = (1023 - mar) / fileMetadataList.size();
SzzcFileMetadata szzcFileMetadata = fileMetadataList.get(i);
String path = szzcFileMetadata.getBaseDir()+ szzcFileMetadata.getFileUrl();
File file = new File(path);
try {
bufferedImage = ImageIO.read(file);
if(ObjectUtils.isEmpty(bufferedImage)){
log.warn("读取文件异常：bufferedImage为空：{}, fileUrl->{}",path);
continue;
}
} catch (IOException e) {
log.error("读取文件异常：异常信息是：{}, fileUrl->{}",e.getMessage(),path);
continue;
}
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
try {
ImageIO.write(bufferedImage, "jpg", byteArrayOutputStream);
} catch (IOException e) {
log.error("===ImageIO.write===$$$$$$$$$$$$$$$$$$$===文件路径： {},异常信息：{}",path,e.getMessage());
continue;
}
/* dx1:图片左边界距离单元格左边框像素值,
* dy1:图片上边界距离单元格上边框像素值,
* dx2:图片右边界距离单元格右边框像素值（负数）,
* dy2:图片下边界距离单元格下边框像素值（负数）,
* col1:列下标（0开始），
* row1:行下标（0开始），
* col2:列下标（1开始），
* row2:行下标（1开始）。*/
HSSFClientAnchor anchor = new HSSFClientAnchor(10 * (i + 1) + ave * i, 10,(10 + ave) * (i + 1), 245, (short) lie, hang, (short) lie, hang);
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
patriarch.createPicture(anchor, workbook.addPicture(byteArrayOutputStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
byteArrayOutputStream.close();
}
}
具体调用：
/**
* 导出数据
* @param response
* @param data 从数据库查询，封装的数据列表
* @throws IOException
*/
public void exportData(HttpServletResponse response, List<SzzcFeedBackExportVO> data) throws IOException{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("sheet1");
//画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFRow row = sheet.createRow(0);
row.setHeight((short) 650);
HSSFCellStyle headStyle = workbook.createCellStyle();
headStyle.setAlignment(HorizontalAlignment.CENTER);
headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
headStyle.setBorderBottom(BorderStyle.THIN);//下边框
headStyle.setBorderLeft(BorderStyle.THIN);//左边框
headStyle.setBorderTop(BorderStyle.THIN);//上边框
headStyle.setBorderRight(BorderStyle.THIN);//右边框
//声明列对象
HSSFCell cell;
//创建标题
List<String> titleList = Arrays.asList("标识号", "反馈内容","图片","反馈人","反馈时间");
for (int i = 0; i < titleList.size(); i++) {
sheet.setColumnWidth(i, 10000);
sheet.setDefaultRowHeight((short) 255);
cell = row.createCell(i);
cell.setCellValue(titleList.get(i));
HSSFFont font = workbook.createFont();
//设置excel数据字体颜色
font.setColor(Font.COLOR_NORMAL);
//设置excel数据字体大小
font.setFontHeightInPoints((short) 15);
headStyle.setFont(font);
cell.setCellStyle(headStyle);
}
HSSFCellStyle dataStyle = workbook.createCellStyle();
dataStyle.setAlignment(HorizontalAlignment.CENTER);
dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
dataStyle.setWrapText(true);//自动换行
HSSFFont fontContext = workbook.createFont();
fontContext.setColor(Font.COLOR_NORMAL);
fontContext.setFontHeightInPoints((short) 15);
dataStyle.setFont(fontContext);
for (int j = 0; j < data.size(); j++) {
SzzcFeedBackExportVO szzcFeedBackExportVO = data.get(j);
int hang = j+1;
//填充数据
row = sheet.createRow(hang);
row.setHeight((short) 2000);
cell = row.createCell(0);
cell.setCellValue(szzcFeedBackExportVO.getId());
cell.setCellStyle(dataStyle);
cell = row.createCell(1);
cell.setCellValue(szzcFeedBackExportVO.getContext());
cell.setCellStyle(dataStyle);
cell = row.createCell(3);
cell.setCellValue(szzcFeedBackExportVO.getCreateUserName());
cell.setCellStyle(dataStyle);
cell = row.createCell(4);
cell.setCellValue(szzcFeedBackExportVO.getCreateTime());
cell.setCellStyle(dataStyle);
//处理图片
this.generatePicture(workbook,patriarch,szzcFeedBackExportVO.getFileMetadataList(),hang);
}
//第四步，将.xsl文件导出到respones的输出流，下载到浏览器
//(1)设置响应类型
// response.setContentType("application/octet-stream;charset=UTF-8");
// response.addHeader("Content-Disposition", "attachment;filename=反馈记录表.xls");
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("反馈记录" + ".csv", "UTF-8"));
//(2)获取输出流
OutputStream out = response.getOutputStream();
workbook.write(out);
workbook.close();
//关闭资源
out.flush();
}
导出效果：
参考文章：
https://blog.csdn.net/fan___/article/details/127669050
扫一扫
1108
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
