==通過此代碼可以實現(xiàn)，對富文本內(nèi)容進行解析罐孝，對Excel中包含多張圖片導(dǎo)出呐馆，可以對圖片進行自定義壓縮，導(dǎo)出多樣性==
Excel帶圖片導(dǎo)出
業(yè)務(wù)場景:前端用戶提問發(fā)帖莲兢，在運營平臺管理系統(tǒng)中對問題進行回復(fù)汹来，只有回答過的帖子，才在前端進行展示改艇。
后臺要求導(dǎo)出用戶前端提問帖收班。前端用戶發(fā)帖數(shù)據(jù)為富文本內(nèi)容，這里使用jsoup進行HTML代碼解析谒兄。
之前沒有圖片的Excel導(dǎo)出一直使用easypoi進行處理摔桦，easypoi也可以對本地圖片或圖片byte[]進行導(dǎo)出，但是不支持多張圖片承疲，壓縮圖片不能控制邻耕。這里使用自己處理的方式。
這個工具類包含網(wǎng)絡(luò)圖片及本地圖片導(dǎo)出燕鸽，創(chuàng)建workbook等等兄世。
public class ExcelUtilsNew {
private static final String FILE_ROOT_PATH = "/mnt/ymt_bonjour_file";
private static final String IMAGES_TEMP = "/temp/";
/**
* 導(dǎo)出Excel
*
* @param sheetName sheet名稱
* @param title 標(biāo)題
* @param values 內(nèi)容
* @param wb HSSFWorkbook對象
* @return
*/
public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook wb) {
// 第一步，創(chuàng)建一個HSSFWorkbook啊研，對應(yīng)一個Excel文件
if (wb == null) {
wb = new HSSFWorkbook();
}
// 第二步御滩，在workbook中添加一個sheet,對應(yīng)Excel文件中的sheet
HSSFSheet sheet = wb.createSheet(sheetName);
// 第三步鸥拧，在sheet中添加表頭第0行,注意老版本poi對Excel的行數(shù)列數(shù)有限制
HSSFRow row = sheet.createRow(0);
// 第四步，創(chuàng)建單元格艾恼，并設(shè)置值表頭 設(shè)置表頭居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
//聲明列對象
HSSFCell cell = null;
//創(chuàng)建標(biāo)題
for (int i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(style);
}
//創(chuàng)建內(nèi)容
for (int i = 0; i < values.length; i++) {
row = sheet.createRow(i + 1);
for (int j = 0; j < values[i].length; j++) {
//將內(nèi)容按順序賦給對應(yīng)的列對象
row.createCell(j).setCellValue(values[i][j]);
}
}
return wb;
}
// 主要使用這個創(chuàng)建XSSFWorkbook 對象住涉，list為Excel的數(shù)據(jù)麸锉，titles為列名與list中的key的對應(yīng)關(guān)系钠绍。
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;
// 遍歷設(shè)置表頭
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));
}
// 每一行的內(nè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;
}
/**
* 批量插入網(wǎng)絡(luò)圖片到excel
*
* @param wb 待處理的ExcelWorkbook
* @param rowIndex 行
* @param colstartIndex 列
* @param scale 壓縮比例
* @param quality 壓縮質(zhì)量
* @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();
//設(shè)置請求方式為"GET"
conn.setRequestMethod("GET");
//超時響應(yīng)時間為50秒
conn.setConnectTimeout(50 * 1000);
//通過輸入流獲取圖片數(shù)據(jù)
InputStream inStream = conn.getInputStream();
//得到圖片的二進制數(shù)據(jù)，以二進制封裝得到數(shù)據(jù)花沉，具有通用性 (壓縮)
byte[] byteData = readInputStream(inStream, scale, quality);
XSSFClientAnchor anchor =
new XSSFClientAnchor(0, 0, 0, 0,
(short) colstartIndex, rowIndex, (short) colstartIndex + 1,
rowIndex + 1);
//設(shè)置圖片隨單元移動調(diào)整大小
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;
}
//通過輸入流獲取圖片數(shù)據(jù)
InputStream inStream = new FileInputStream(file);
//得到圖片的二進制數(shù)據(jù)柳爽，以二進制封裝得到數(shù)據(jù)，具有通用性 (壓縮)
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);//設(shè)置圖片隨單元移動調(diào)整大小
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);
//創(chuàng)建一個Buffer字符串
byte[] buffer = new byte[1024];
//每次讀取的字符串長度碱屁，如果為-1磷脯，代表全部讀取完畢
int len = 0;
//使用一個輸入流從buffer里把數(shù)據(jù)讀取出來
while ((len = is.read(buffer)) != -1) {
//用輸出流往buffer里寫入數(shù)據(jù)，中間參數(shù)代表從哪個位置開始讀娩脾，len代表讀取的長度
outStream.write(buffer, 0, len);
}
//關(guān)閉輸入流
inStream.close();
is.close();
//把outStream里的數(shù)據(jù)寫入內(nèi)存
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("已經(jīng)被刪除的文件:" + f);
// 刪除文件
f.delete();
} else if (file.isDirectory()) {
// 如果是文件夾,就遞歸調(diào)用文件夾的方法
deleteFile(f);
}
}
// 刪除文件夾自己,如果它低下是空的,就會被刪除
System.out.println("已經(jīng)被刪除的文件夾:" + file);
file.delete();
}
// 如果是文件,就直接刪除自己
System.out.println("已經(jīng)被刪除的文件:" + file);
file.delete();
}
/**
* 自適應(yīng)寬度(中文支持)
*
* @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;
//當(dāng)前行未被使用過
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 對可能出現(xiàn)的img標(biāo)簽進行匹配（并不是每個用戶提問都是帶圖片），匹配到在進行解析富文本內(nèi)容柿赊。
final val baseOutDTO = exclusiveConsultantBbsList(params);
if (ObjectUtil.isNotNull(baseOutDTO.getData())) {
page = baseOutDTO.getData().getList();
final WordTree wordTree = new WordTree();
for (BbsPageOutDTO pageOutDTO : page) {
//將HTML標(biāo)簽解析俩功，將<p>標(biāo)簽和<img>標(biāo)簽分離開，單獨插入表格中
Document document = Jsoup.parseBodyFragment(pageOutDTO.getBbsContent());
//<p>標(biāo)簽里面的文字
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中實際導(dǎo)出代碼
public void exportExcel(HttpServletResponse response) throws Exception {
// Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), ExclusiveOutDTO.class, page);
// Excel的表頭與導(dǎo)出實體類的字段做映射
HashMap<String, String> stringHashMap = new HashMap<>(12);
stringHashMap.put("bbsAuthorId", "用戶aid");
stringHashMap.put("releaseTime", "提問發(fā)帖時間");
stringHashMap.put("bbsSubject", "帖子主題");
stringHashMap.put("bbsContentText", "提問內(nèi)容");
stringHashMap.put("bbsContentImg", "提問圖片");
stringHashMap.put("commentTime", "回復(fù)時間");
stringHashMap.put("commentContent", "回復(fù)內(nèi)容");
stringHashMap.put("answerUser", "回復(fù)人");
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();
// 調(diào)用response對象下載Excel
}
使用easypoi的示例碰声，適用于單張圖片
easypoi的官方示例
實體類的注解解釋
我的項目中使用easypoi的實體類示例
@Data
public class ExclusiveOutDTO implements Serializable {
private static final long serialVersionUID = -1782708760205108787L;
/**
* 作者ID
*/
@Excel(name = "用戶aid", width = 30)
private String bbsAuthorId;
/**
* 發(fā)布時間
*/
@Excel(name = "提問發(fā)帖時間", width = 30)
private String releaseTime;
/**
* 帖子主題
*/
@Excel(name = "帖子主題", width = 30)
private String bbsSubject;
/**
* 提問內(nèi)容
*/
// @Excel(name = "提問全量內(nèi)容", width = 30)
private String bbsContent;
/**
* 提問內(nèi)容
*/
@Excel(name = "提問內(nèi)容", width = 30)
private String bbsContentText;
/**
* 提問內(nèi)容
*/
// @Excel(name = "提問圖片", width = 30)
private List<String> bbsContentImg;
/**
* 回復(fù)時間
*/
@Excel(name = "回復(fù)時間", width = 30)
private String commentTime;
/**
* 回復(fù)內(nèi)容
*/
@Excel(name = "回復(fù)內(nèi)容", width = 30)
private String commentContent;
/**
* 回復(fù)人
*/
@Excel(name = "回復(fù)人", 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);
// 如果文件路徑所對應(yīng)的文件存在诡蜓，并且是一個文件，則直接刪除
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導(dǎo)出
*
* @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;
}
}
希望可以幫助到有需求的小伙伴胰挑，共同進步Ｂ！！瞻颂！
