通过此代码可以实现,对富文本内容进行解析,对Excel中包含多张图片导出,可以对图片进行自定义压缩,导出多样性
Excel带图片导出
业务场景:前端用户提问发帖,在运营平台管理系统中对问题进行回复,只有回答过的帖子,才在前端进行展示。
后台要求导出用户前端提问帖。前端用户发帖数据为富文本内容,这里使用jsoup进行HTML代码解析。
之前没有图片的Excel导出一直使用easypoi进行处理,easypoi也可以对本地图片或图片byte[]进行导出,但是不支持多张图片,压缩图片不能控制。这里使用自己处理的方式。
这个工具类包含网络图片及本地图片导出,创建workbook等等。
public class ExcelUtilsNew {
private static final String FILE_ROOT_PATH = "/mnt/ymt_bonjour_file";
private static final String IMAGES_TEMP = "/temp/";
/**
* 导出Excel
*
* @param sheetName sheet名称
* @param title 标题
* @param values 内容
* @param wb HSSFWorkbook对象
* @return
*/
public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook wb) {
// 第一步,创建一个HSSFWorkbook,对应一个Excel文件
if (wb == null) {
wb = new HSSFWorkbook();
}
// 第二步,在workbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet(sheetName);
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
HSSFRow row = sheet.createRow(0);
// 第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
//声明列对象
HSSFCell cell = null;
//创建标题
for (int i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(style);
}
//创建内容
for (int i = 0; i < values.length; i++) {
row = sheet.createRow(i + 1);
for (int j = 0; j < values[i].length; j++) {
//将内容按顺序赋给对应的列对象
row.createCell(j).setCellValue(values[i][j]);
}
}
return wb;
}
// 主要使用这个创建XSSFWorkbook 对象,list为Excel的数据,titles为列名与list中的key的对应关系。
public static XSSFWorkbook createWB(List<Map<String, Object>> list, Map<String, String> titles) {
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet();
CellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
int rowNum = 0;
XSSFRow row = sheet.createRow(rowNum++);
Cell cell = null;
// 遍历设置表头
Collection<String> values = titles.values();
List<String> list1;
if (values instanceof List) {
list1 = (List<String>) values;
} else {
list1 = new ArrayList<String>(values);
}
for (int i = 0; i < list1.size(); i++) {
sheet.setDefaultColumnStyle(i, style);
cell = row.createCell(i);
cell.setCellValue(list1.get(i));
}
// 每一行的内容
for (Map<String, Object> map : list) {
int colNum = 0;
row = sheet.createRow(rowNum++);
List<String> list2 = new ArrayList<String>(titles.keySet());
for (String s : list2) {
cell = row.createCell(colNum++);
Object o = map.get(s);
cell.setCellValue(o == null ? "" : o.toString());
}
}
return wb;
}
/**
* 批量插入网络图片到excel
*
* @param wb 待处理的ExcelWorkbook
* @param rowIndex 行
* @param colstartIndex 列
* @param scale 压缩比例
* @param quality 压缩质量
* @param imageUrls 待处理图片URL
* @throws Exception
*/
public static void inserNetImages(XSSFWorkbook wb, int rowIndex, int colstartIndex, float scale, float quality, List<String> imageUrls) throws Exception {
HttpURLConnection conn = null;
XSSFSheet sheet = wb.getSheetAt(0);
XSSFDrawing patriarch = sheet.createDrawingPatriarch();
for (String imageUrl : imageUrls) {
URL url = new URL(imageUrl);
//打开链接
conn = (HttpURLConnection) url.openConnection();
//设置请求方式为"GET"
conn.setRequestMethod("GET");
//超时响应时间为50秒
conn.setConnectTimeout(50 * 1000);
//通过输入流获取图片数据
InputStream inStream = conn.getInputStream();
//得到图片的二进制数据,以二进制封装得到数据,具有通用性 (压缩)
byte[] byteData = readInputStream(inStream, scale, quality);
XSSFClientAnchor anchor =
new XSSFClientAnchor(0, 0, 0, 0,
(short) colstartIndex, rowIndex, (short) colstartIndex + 1,
rowIndex + 1);
//设置图片随单元移动调整大小
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
patriarch.createPicture(anchor, wb.addPicture(byteData, XSSFWorkbook.PICTURE_TYPE_JPEG));
}
}
/**
* 批量插入本地图片到excel
*
* @param wb
* @param rowIndex
* @param colstartIndex
* @param scale
* @param quality
* @param imagePathList
*/
public static void insertLocalImages(XSSFWorkbook wb, int rowIndex, int colstartIndex, float scale, float quality, List<String> imagePathList) throws Exception {
XSSFSheet sheet = wb.getSheetAt(0);
XSSFDrawing patriarch = sheet.createDrawingPatriarch();
for (String filePath : imagePathList) {
File file = new File(FILE_ROOT_PATH + filePath);
if (!file.exists()) {
break;
}
//通过输入流获取图片数据
InputStream inStream = new FileInputStream(file);
//得到图片的二进制数据,以二进制封装得到数据,具有通用性 (压缩)
byte[] byteData = readInputStream(inStream, scale, quality);
System.out.println("图片大小:" + byteData.length / 1024 / 1024);
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) colstartIndex, rowIndex, (short) colstartIndex + 1, rowIndex + 1);
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);//设置图片随单元移动调整大小
patriarch.createPicture(anchor, wb.addPicture(byteData, XSSFWorkbook.PICTURE_TYPE_JPEG));
colstartIndex++;
}
}
private static byte[] readInputStream(InputStream inStream, float scale, float quality) throws Exception {
ByteArrayOutputStream outStream = new ByteArrayOutputStream();
File temp_dir = new File(FILE_ROOT_PATH + IMAGES_TEMP);
if (!temp_dir.exists()) {
temp_dir.mkdirs();
}
String tempFileName = System.currentTimeMillis() + ".jpg";
File image_temp = new File(temp_dir, tempFileName);
if (!image_temp.exists()) {
image_temp.createNewFile();
}
//压缩图片到临时文件
Thumbnails.of(inStream)
.scale(scale)
.outputQuality(quality)
.toFile(image_temp);
FileInputStream is = new FileInputStream(image_temp);
//创建一个Buffer字符串
byte[] buffer = new byte[1024];
//每次读取的字符串长度,如果为-1,代表全部读取完毕
int len = 0;
//使用一个输入流从buffer里把数据读取出来
while ((len = is.read(buffer)) != -1) {
//用输出流往buffer里写入数据,中间参数代表从哪个位置开始读,len代表读取的长度
outStream.write(buffer, 0, len);
}
//关闭输入流
inStream.close();
is.close();
//把outStream里的数据写入内存
return outStream.toByteArray();
}
public static void deleteTempFiles() {
File file = new File(FILE_ROOT_PATH + IMAGES_TEMP);
deleteFile(file);
}
/**
* 删除临时文件
*/
private static void deleteFile(File file) {
// 判断传递进来的是文件还是文件夹,如果是文件,直接删除,如果是文件夹,则判断文件夹里面有没有东西
if (file.isDirectory()) {
// 如果是目录,就删除目录下所有的文件和文件夹
File[] files = file.listFiles();
// 遍历目录下的文件和文件夹
for (File f : files) {
// 如果是文件,就删除
if (f.isFile()) {
System.out.println("已经被删除的文件:" + f);
// 删除文件
f.delete();
} else if (file.isDirectory()) {
// 如果是文件夹,就递归调用文件夹的方法
deleteFile(f);
}
}
// 删除文件夹自己,如果它低下是空的,就会被删除
System.out.println("已经被删除的文件夹:" + file);
file.delete();
}
// 如果是文件,就直接删除自己
System.out.println("已经被删除的文件:" + file);
file.delete();
}
/**
* 自适应宽度(中文支持)
*
* @param sheet
* @param size
*/
private void setSizeColumn(XSSFSheet sheet, int size) {
for (int columnNum = 0; columnNum < size; columnNum++) {
int columnWidth = sheet.getColumnWidth(columnNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
XSSFRow currentRow;
//当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(columnNum) != null) {
XSSFCell currentCell = currentRow.getCell(columnNum);
if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
sheet.setColumnWidth(columnNum, columnWidth * 256);
}
}
}
其中使用jsoup解析HTML代码,及使用hutool的DFA单词树(以下简称单词树)WordTree 对可能出现的img标签进行匹配(并不是每个用户提问都是带图片),匹配到在进行解析富文本内容。
final val baseOutDTO = exclusiveConsultantBbsList(params);
if (ObjectUtil.isNotNull(baseOutDTO.getData())) {
page = baseOutDTO.getData().getList();
final WordTree wordTree = new WordTree();
for (BbsPageOutDTO pageOutDTO : page) {
//将HTML标签解析,将<p>标签和<img>标签分离开,单独插入表格中
Document document = Jsoup.parseBodyFragment(pageOutDTO.getBbsContent());
//<p>标签里面的文字
String textP = document.selectFirst("p").text();
pageOutDTO.setBbsContentText(textP);
wordTree.addWords("img");
//img的图片
if (wordTree.isMatch(pageOutDTO.getBbsContent())) {
List<String> imgSrcs = new ArrayList<String>();
Elements elements = document.getElementsByTag("img");
for (Element element : elements) {
String imgSrc = element.attr("src");
imgSrcs.add(imgSrc);
}
pageOutDTO.setBbsContentImg(imgSrcs);
}
final ExclusiveOutDTO exclusiveOutDTO = new ExclusiveOutDTO();
BeanUtil.copyProperties(pageOutDTO, exclusiveOutDTO);
mapList.add(BeanUtil.beanToMap(exclusiveOutDTO));
}
return R.ok();
} else {
return R.error();
}
在controller中实际导出代码
public void exportExcel(HttpServletResponse response) throws Exception {
// Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), ExclusiveOutDTO.class, page);
// Excel的表头与导出实体类的字段做映射
HashMap<String, String> stringHashMap = new HashMap<>(12);
stringHashMap.put("bbsAuthorId", "用户aid");
stringHashMap.put("releaseTime", "提问发帖时间");
stringHashMap.put("bbsSubject", "帖子主题");
stringHashMap.put("bbsContentText", "提问内容");
stringHashMap.put("bbsContentImg", "提问图片");
stringHashMap.put("commentTime", "回复时间");
stringHashMap.put("commentContent", "回复内容");
stringHashMap.put("answerUser", "回复人");
XSSFWorkbook workbook = ExcelUtilsNew.createWB(mapList, stringHashMap);
for (int i = 0; i < page.size(); i++) {
if (CollUtil.isNotEmpty(page.get(i).getBbsContentImg())) {
ExcelUtilsNew.inserNetImages(workbook, i + 1, 1, 0.6f, 0.5f, page.get(i).getBbsContentImg());
}
}
// 清空压缩的临时文件
ExcelUtilsNew.deleteTempFiles();
// 调用response对象下载Excel
}
使用easypoi的示例,适用于单张图片
这里是easypoi的官方文档地址easypoi的官方文档
我的项目中使用easypoi的实体类示例
@Data
public class ExclusiveOutDTO implements Serializable {
private static final long serialVersionUID = -1782708760205108787L;
/**
* 作者ID
*/
@Excel(name = "用户aid", width = 30)
private String bbsAuthorId;
/**
* 发布时间
*/
@Excel(name = "提问发帖时间", width = 30)
private String releaseTime;
/**
* 帖子主题
*/
@Excel(name = "帖子主题", width = 30)
private String bbsSubject;
/**
* 提问内容
*/
// @Excel(name = "提问全量内容", width = 30)
private String bbsContent;
/**
* 提问内容
*/
@Excel(name = "提问内容", width = 30)
private String bbsContentText;
/**
* 提问内容
*/
// @Excel(name = "提问图片", width = 30)
private List<String> bbsContentImg;
/**
* 回复时间
*/
@Excel(name = "回复时间", width = 30)
private String commentTime;
/**
* 回复内容
*/
@Excel(name = "回复内容", width = 30)
private String commentContent;
/**
* 回复人
*/
@Excel(name = "回复人", width = 30)
private String answerUser;
}
附带Maven的地址
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>net.coobird</groupId>
<artifactId>thumbnailator</artifactId>
<version>0.4.8</version>
</dependency>
<dependency>
<groupId>org.jsoup</groupId>
<artifactId>jsoup</artifactId>
<version>1.11.3</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.1</version>
</dependency>
本项目中对easypoi的工具类
public class FileUtil {
public static void uploadFile(byte[] file, String filePath, String fileName) throws Exception {
File targetFile = new File(filePath);
if (!targetFile.exists()) {
targetFile.mkdirs();
}
FileOutputStream out = new FileOutputStream(filePath + fileName);
out.write(file);
out.flush();
out.close();
}
public static boolean deleteFile(String fileName) {
File file = new File(fileName);
// 如果文件路径所对应的文件存在,并且是一个文件,则直接删除
if (file.exists() && file.isFile()) {
if (file.delete()) {
return true;
} else {
return false;
}
} else {
return false;
}
}
public static String renameToUUID(String fileName) {
return UUID.randomUUID() + "." + fileName.substring(fileName.lastIndexOf(".") + 1);
}
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response){
ExportParams exportParams = new ExportParams(title, sheetName);
exportParams.setCreateHeadRows(isCreateHeader);
defaultExport(list, pojoClass, fileName, response, exportParams);
}
/**
* 项目中默认使用这个作为Excel导出
*
* @param list
* @param pojoClass
* @param fileName
* @param response
*/
public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response) {
defaultExport(list, pojoClass, fileName, response, new ExportParams());
}
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response){
defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
}
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
defaultExport(list, fileName, response);
}
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
if (workbook != null) {
downLoadExcel(fileName, response, workbook);
}
}
public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
workbook.write(response.getOutputStream());
} catch (IOException e) {
throw new RuntimeException(e.getMessage());
}
}
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
if (workbook != null);
downLoadExcel(fileName, response, workbook);
}
public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass){
if (StringUtils.isBlank(filePath)){
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
}catch (NoSuchElementException e){
throw new RuntimeException("模板不能为空");
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage());
}
return list;
}
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
if (file == null){
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
}catch (NoSuchElementException e){
throw new RuntimeException("excel文件不能为空");
} catch (Exception e) {
throw new RuntimeException(e.getMessage());
}
return list;
}
}
希望可以帮助到有需求的小伙伴,共同进步!!!