导入带图片的excel,可查看文章：Java 通过POI快速导入带图片的excel并且图片不会丢失
数据导出
导出包含了带图片的与不带图片的导出方式，大致如下：
- 无图片的导出 ；
这种导出可以选择你喜欢用的，POI或者EasyExcel等，这里选用的EasyExcel。
引入依赖：
<!-- 阿里 easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.4</version>
<exclusions>
<exclusion>
<artifactId>poi-ooxml</artifactId>
<groupId>org.apache.poi</groupId>
</exclusion>
</exclusions>
</dependency>
接口数据查询及表格映射导出：
@PostMapping("exportBridgesData")
public void exportBridgesData(HttpServletResponse response,
HttpServletRequest request,
@RequestBody UrbanListQueryDto dto){
//导出数据获取，注意数据与抬头的映射
List<RoadsAndBridgesExportVo> vos = urbanManageListService.exportRoadsAndBridgesData(dto, Constants.MAINTAIN);
if(CollectionUtils.isEmpty(vos)){
throw new ServiceException("暂无数据可导出！");
}
ExcelUtil.writeExcel(response,request, vos, "列表.xlsx", "列表", RoadsAndBridgesExportVo.class);
}
数据映射截图：
这里使用的导出调用的方法：
/**
* 导出Excel(一个sheet)
*
* @param response HttpServletResponse
* @param list 数据list
* @param fileName 导出的文件名
* @param sheetName 导入文件的sheet名
* @param clazz 实体类
*/
public static <T> void writeExcel(HttpServletResponse response, HttpServletRequest request,
List<T> list, String fileName, String sheetName, Class<T> clazz) {
OutputStream outputStream = handleRequestResponse(response, request, fileName);
EasyExcel.write(outputStream, clazz).sheet(sheetName).doWrite(list);
}
- 带图片的导出 ；
package com.yfjgpt.disposalCompany.utils;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.*;
import org.springblade.core.tool.utils.DateUtil;
import org.springblade.core.tool.utils.Func;
import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.OutputStream;
import java.net.URL;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @Description:
* @author: guo-xz
* @Project:
* @Date: 2022年08月11日 09:16
*/
public class ExcelUtilImpl{
//这里可以根据自己情况修改，本地修改后图片放在后面了，下面也有参数说明
/**
* 导出
*
* @param list 数据列表
* @param excelHeaders 表头
* @param keys 导出字段属性
* @param fileName 文件名称
* @param response 相应对象
* @return
*/
public static void excelImg(List<Map> list, String[] excelHeaders, String[] keys, String fileName, HttpServletResponse response){
excelImg(list, excelHeaders, keys, null, fileName, response);
}
/**
* 导出
*
* @param list 数据列表
* @param excelHeaders 表头
* @param keys 导出字段属性
* @param imgs 导出图片字段属性，用逗号隔开
* @param fileName 文件名称
* @param response 相应对象
* @return
*/
public static void excelImg(List<Map> list, String[] excelHeaders, String[] keys, String imgs, String fileName, HttpServletResponse response){
// 获取数据列表
// 创建一个工作簿，对应文件
XSSFWorkbook workBook = new XSSFWorkbook();
// 创建一个sheet工作表
XSSFSheet sheet = workBook.createSheet(fileName + DateUtil.time());
// 设置表头单元格样式
XSSFCellStyle headstyle = workBook.createCellStyle();
// 设置居中
headstyle.setAlignment(HorizontalAlignment.CENTER);
headstyle.setVerticalAlignment(VerticalAlignment.CENTER);
XSSFFont headFont = workBook.createFont();
headFont.setFontHeight(14);
headFont.setBold(true);
headstyle.setFont(headFont);
// 创建一般单元格样式
XSSFCellStyle cellstyle = workBook.createCellStyle();
cellstyle.setAlignment(HorizontalAlignment.CENTER);
cellstyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellstyle.setWrapText(true);
XSSFFont cellFont = workBook.createFont();
cellFont.setFontHeight(11);
cellstyle.setFont(cellFont);
XSSFRow headRow = sheet.createRow(0);
for (int i = 0; i < excelHeaders.length; i++) {
XSSFCell cell = headRow.createCell(i);
cell.setCellValue(excelHeaders[i]);
cell.setCellStyle(headstyle);
sheet.setColumnWidth(i, (20 * 256));
}
// 创建内容
XSSFRow row = null;
for (int rowIndex = 0; rowIndex < list.size(); rowIndex++) {
row = sheet.createRow(rowIndex + 1);
row.setHeight((short) (40 * 20));
// 单元格
XSSFCell cell = null;
String s = JSONObject.toJSONString(list.get(rowIndex));
// 转成map
Map<String,Object> map = JSONObject.parseObject(s, HashMap.class);
for (int j = 0; j < keys.length; j++) {
cell = row.createCell(j);
cell.setCellStyle(cellstyle);
if(Func.isNotEmpty(imgs) && imgs.contains((String)map.get(keys[j]))){
cell.setCellValue("");
try{
URL photoFile = new URL( (String) map.get(keys[j]));
// 先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
//将图片读入BufferedImage对象
BufferedImage bufferImg = ImageIO.read(photoFile);
// 将图片写入流中
ImageIO.write(bufferImg, "jpg", byteArrayOut);
// 利用HSSFPatriarch将图片写入EXCEL
XSSFDrawing patriarch = sheet.createDrawingPatriarch();
// 图片一导出到单元格I3-5中 列开始：8 行开始：2 列结束：9 行结束：5
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, j, rowIndex + 1, j, rowIndex + 1);
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
// 插入图片内容
Picture picture = patriarch.createPicture(anchor, workBook.addPicture(byteArrayOut
.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
picture.resize(1.05,1.10);
//将图片插入工作表
}catch (Exception e) {
cell.setCellValue("暂无图片");
}
}else{
cell.setCellValue((String) map.get(keys[j]));
}
}
}
try {
//最终已流的形式返回
OutputStream out = null;
out = response.getOutputStream();
response.setHeader("content-type", "application/octet-stream");
response.setContentType("application/octet-stream");
response.addHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName + DateUtil.time() + ".xlsx", "UTF-8"));
workBook.write(out);
out.flush();
out.close();
}catch (Exception e) {
e.printStackTrace();
}
}
}
这是我修改后封装的公用方法，第一个为数据列表，第二个是T，也就是导出的数据类，这里是通过前面的T的@ExcelRow来获取判断字段是否是图片字段，是就进行图片的列设置
本地封装好的放下面了：
/**
* 导出
*
* @param list 数据列表
* @param clazz 映射体
* @param smallTitle 需要指定的小标题，位于数据列名行之上
* @param fileName 文件名称
* @param response 相应对象
* @return
*/
public void excelImg(List<T> list, @NotNull final Class<?> clazz, String smallTitle, String fileName, HttpServletResponse response){
//获取文件属性
ExcelFile excelFile = clazz.getAnnotation(ExcelFile.class);
//获取是否导出图片列
List<Integer> pictureCloum = new ArrayList<>();
//获取字段及表头
List<String> formatterText = new ArrayList<>();
Map<Integer, Object> title = getTitle(clazz, formatterText, pictureCloum);
// 获取数据列表
// 创建一个工作簿，对应文件
XSSFWorkbook workBook = new XSSFWorkbook();
// 创建一个sheet工作表
XSSFSheet sheet = workBook.createSheet(fileName);
// 设置表头单元格样式
XSSFCellStyle headstyle = workBook.createCellStyle();
// 设置居中
setCenter(workBook, headstyle);
// 创建一般单元格样式
XSSFCellStyle cellstyle = workBook.createCellStyle();
createCellstyle(workBook, cellstyle);
int rowNum = 0;
//创建标题
if (null != excelFile) {
rowNum = createHead(excelFile, sheet, rowNum, workBook, formatterText, cellstyle);
}
if(StringUtils.isNotEmpty(smallTitle)){
rowNum = createSmallHead(smallTitle, sheet, rowNum, workBook, formatterText, cellstyle);
}
//创建列标题
rowNum = createCloumTitle(sheet, title, headstyle, rowNum);
// 创建内容
for (int rowIndex = 0; rowIndex < list.size(); rowIndex++) {
rowNum = rowNum + 1;
XSSFRow row = sheet.createRow(rowNum);
row.setHeight((short) (40 * 20));
// 单元格
XSSFCell cell = null;
String s = JSONObject.toJSONString(list.get(rowIndex));
// 转成map
Map<String,Object> map = JSONObject.parseObject(s, HashMap.class);
for (int j = 0; j < formatterText.size(); j++) {
cell = row.createCell(j);
cell.setCellStyle(cellstyle);
Object obj = map.get(formatterText.get(j));
String value = null;
if(ObjectUtils.isNotEmpty(obj)){
value = String.valueOf(obj);
}
if(!CollectionUtils.isEmpty(pictureCloum) && pictureCloum.contains(j)){
try{
if(StringUtils.isNotEmpty(value)){
putPicture(value, workBook, sheet, rowNum, j);
}
}catch (Exception e) {
cell.setCellValue("暂无图片");
}
}else{
if(StringUtils.isNotEmpty(value)){
cell.setCellValue(value);
}
}
}
}
try {
//最终已流的形式返回
OutputStream out = null;
out = response.getOutputStream();
response.setHeader("content-type", "application/octet-stream");
response.setContentType("application/octet-stream");
response.addHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName + DateUtil.format(new Date(), "yyyy-MM-dd") + ".xlsx", "UTF-8"));
workBook.write(out);
out.flush();
out.close();
}catch (Exception e) {
e.printStackTrace();
}
}
private void createCellstyle(XSSFWorkbook workBook, XSSFCellStyle cellstyle){
// 创建一般单元格样式
cellstyle.setAlignment(HorizontalAlignment.CENTER);
cellstyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellstyle.setWrapText(true);
XSSFFont cellFont = workBook.createFont();
cellFont.setFontHeight(11);
cellstyle.setFont(cellFont);
}
/**
* @description: 设置属性值居中
* @param: [workBook, headstyle]
* @return: void
* @date: 2022/9/6 14:39
* @version: 1.0
**/
private void setCenter(XSSFWorkbook workBook, XSSFCellStyle headstyle){
headstyle.setAlignment(HorizontalAlignment.CENTER);
headstyle.setVerticalAlignment(VerticalAlignment.CENTER);
XSSFFont headFont = workBook.createFont();
headFont.setFontHeight(14);
headFont.setBold(true);
headstyle.setFont(headFont);
}
/**
* @description: 创建大标题
* @param: [excelFile]
* @return: void
* @date: 2022/9/6 14:54
* @version: 1.0
**/
private Integer createHead(ExcelFile excelFile, XSSFSheet sheet, int rowNum, XSSFWorkbook wb, List<String> formatterText, XSSFCellStyle cellstyle){
int endCloum = formatterText.size() - 1;
String bigTitle = excelFile.bigTitle();
String description = excelFile.description();
int x = excelFile.x();
int y = excelFile.y();
if(StringUtils.isNotEmpty(bigTitle)){
XSSFRow row = sheet.createRow(rowNum);
row.setHeight((short) (40 * 20));
//设置标题头
//----------------标题样式--------------------------------
XSSFCellStyle titleStyle = createTitleStyle(wb);
XSSFCell cell = row.createCell(0);
cell.setCellValue(bigTitle);
cell.setCellStyle(titleStyle);
sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, endCloum));
rowNum = rowNum + 1;
}
if(StringUtils.isNotEmpty(description)){
XSSFRow row = sheet.createRow(rowNum);
row.setHeight((short) (40 * 20));
if(x != 0){
XSSFCell cell = row.createCell(x);
cell.setCellValue(description);
cell.setCellStyle(cellstyle);
}else {
XSSFCell cell = row.createCell(formatterText.size());
cell.setCellValue(description);
cell.setCellStyle(cellstyle);
}
if(y != 0){
sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, x, y));
}else{
sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, x, endCloum));
}
rowNum = rowNum + 1;
}
return rowNum;
}
/**
* @description: 创建小标题
* @param: [excelFile, row, sheet, rowNum, wb, formatterText, cellstyle]
* @return: void
* @date: 2022/9/6 15:48
* @version: 1.0
**/
private Integer createSmallHead(String smallTitle, XSSFSheet sheet, int rowNum, XSSFWorkbook wb, List<String> formatterText, XSSFCellStyle cellstyle){
XSSFRow row = sheet.createRow(rowNum);
row.setHeight((short) (40 * 20));
XSSFCell cell = row.createCell(0);
cell.setCellValue(smallTitle);
cell.setCellStyle(cellstyle);
sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, formatterText.size() -1));
//创建一行给列标题用
rowNum = rowNum + 1;
return rowNum;
}
private Integer createCloumTitle(XSSFSheet sheet, Map<Integer, Object> title, XSSFCellStyle headstyle, int rowNum) {
XSSFRow headRow = sheet.createRow(rowNum);
for (int i = 0; i < title.size(); i++) {
XSSFCell cell = headRow.createCell(i);
cell.setCellValue(String.valueOf(title.get(i)));
cell.setCellStyle(headstyle);
sheet.setColumnWidth(i, (20 * 256));
}
return rowNum;
}
/**
* @description: 创建标题样式
* @param: [wb]
* @return: org.apache.poi.xssf.usermodel.XSSFCellStyle
* @date: 2022/9/6 15:47
* @version: 1.0
**/
private XSSFCellStyle createTitleStyle(XSSFWorkbook wb){
//标题样式
XSSFCellStyle titleStyle = wb.createCellStyle();
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
Font ztFont = wb.createFont();
ztFont.setItalic(false); // 设置字体为斜体字
//ztFont.setColor(Font.COLOR_NORMAL); // 将字体设置为“红色”
ztFont.setFontHeightInPoints((short) 16); // 将字体大小设置为18px
ztFont.setFontName("宋体"); // 将“宋体”字体应用到当前单元格上
ztFont.setBold(true); //加粗
//ztFont.setUnderline(Font.U_DOUBLE);// 添加（Font.U_SINGLE单条下划线/Font.U_DOUBLE双条下划线）
//ztFont.setStrikeout(true);// 是否添加删除线
titleStyle.setFont(ztFont);
return titleStyle;
}
/**
* @description: 获取标题行及需要的特殊字段
* @param: [clazz, formatterText, pictureCloum]
* @return: java.util.Map<java.lang.Integer,java.lang.Object>
* @date: 2022/9/6 11:11
* @version: 1.0
**/
private Map<Integer, Object> getTitle(Class<?> clazz, List<String> formatterText, List<Integer> pictureCloum){
Field[] fields = getAllField(clazz);
Map<Integer, Object> title = new HashMap<>();
for (Field field : fields) {
ExcelRow excelRow = field.getAnnotation(ExcelRow.class);
if (excelRow != null) {
title.put(excelRow.columnNo(), excelRow.cloumName());
formatterText.add(field.getName());
if(excelRow.isPicture()){
pictureCloum.add(excelRow.columnNo());
}
}
}
return title;
}
private Field[] getAllField(Class<?> clazz) {
//获取对象及其父类所有属性
Field[] fields = null;
//newObject是传入对象
for (; clazz != Object.class; clazz = clazz.getSuperclass()) {//向上循环 遍历父类
Field[] field = clazz.getDeclaredFields();
//hutool工具包，合并两个数组
fields = ArrayUtil.addAll(fields, field);
}
return fields;
}
/**
* @description: 图片设置
* @param: [pictureUrl, workBook, sheet, rowIndex, j]
* @return: void
* @date: 2022/9/6 11:17
* @version: 1.0
**/
private void putPicture(String pictureUrl, XSSFWorkbook workBook, XSSFSheet sheet, int rowIndex, int j) throws Exception{
URL photoFile = new URL(pictureUrl);
// 先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
//将图片读入BufferedImage对象
BufferedImage bufferImg = ImageIO.read(photoFile);
// 将图片写入流中，后缀可获取url后的后缀
ImageIO.write(bufferImg, "jpeg", byteArrayOut);
// 利用HSSFPatriarch将图片写入EXCEL
XSSFDrawing patriarch = sheet.createDrawingPatriarch();
// 图片一导出到单元格I3-5中 列开始：8 行开始：2 列结束：9 行结束：5
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, j, rowIndex, j, rowIndex);
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
// 插入图片内容
Picture picture = patriarch.createPicture(anchor, workBook.addPicture(byteArrayOut
.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
picture.resize(1.05,1.10);
//将图片插入工作表
}
需要查看模板下载及resource下的模板下载，可查看文章：模板下载
需要excel图片信息等数据导入，可查看文章：Java带图片的excel数据导入
死鬼~看完了来个三连哦！O.O`
反手就是一个赞赞赞——————————奥里给
193
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
