数据导出到Excel表格
https://www.cnblogs.com/zhangzhiyong-/p/13376527.html
package com.jxmcloud.business.shop.util;
import com.jxmcloud.business.shop.util.pay.PayUtil;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.BiConsumer;
/**
* 解析excel中图片和文本数据工具类
*
* @author hujun
**/
@Component
public class ExcelUtilWithPic {
private final Logger logger = LoggerFactory.getLogger(PayUtil.class);
@Autowired
private SaveFileUtil fileRemoteClient;
/**
* 解析excel中图片和文本数据
* Map<String, String>中key格式 1-1 获取这个excel坐标的图片地址
* Sheet对象,用来获取普通文字数据
* @Param [file, biConsumer]
* @author hujun
**/
public String handleDataFromExcel(MultipartFile file, BiConsumer<Map<String, List<String>>, Sheet> biConsumer) {
Workbook workbook = null;
try {
String fileName = file.getOriginalFilename();
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
return "上传文件格式不正确";
}
boolean isExcel2003 = true;
if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
isExcel2003 = false;
}
InputStream is = file.getInputStream();
if (isExcel2003) {
workbook = new HSSFWorkbook(is);
} else {
workbook = new XSSFWorkbook(is);
}
// Map<String, String> maplist = null;
Map<String, List<String>> maplist =null;
Sheet sheet = workbook.getSheetAt(0);
// 解析图片,放入map
if (isExcel2003) {
// maplist = getPicturesXls((HSSFSheet) sheet);
maplist = getPicturesXlsMulit((HSSFSheet) sheet);
} else if (fileName.endsWith(".xlsx")) {
// maplist = getPicturesXlsx((XSSFSheet) sheet);
maplist = getPicturesXlsxMulit((XSSFSheet) sheet);
}
//解析文字数据
biConsumer.accept(maplist, sheet);
workbook.close();
} catch (IOException e) {
logger.error("解析带图片excel失败", e);
return "解析带图片excel失败";
}
return "操作成功!";
}
/**
* 获取图片和位置 (xls)
*
* @param sheet
* @return
* @throws IOException
*/
public Map<String, String> getPicturesXls(HSSFSheet sheet) throws IOException {
Map<String, String> map = new HashMap<String, String>();
List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
for (HSSFShape shape : list) {
if (shape instanceof HSSFPicture) {
HSSFPicture picture = (HSSFPicture) shape;
HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
// 行号-列号
String key = cAnchor.getRow1() + "-" + cAnchor.getCol1();
map.put(key, byteToMultipartFile(picture.getPictureData().getData()));
}
}
return map;
}
/**
* 获取图片和位置 (xls)
* 一个单元格中有多个图片
* @param sheet
* @return
* @throws IOException
*/
public Map<String, List<String>> getPicturesXlsMulit(HSSFSheet sheet) throws IOException {
Map<String, List<String>> map = new HashMap<String, List<String>>();
List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
for (HSSFShape shape : list) {
if (shape instanceof HSSFPicture) {
HSSFPicture picture = (HSSFPicture) shape;
HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
// 行号-列号
String key = cAnchor.getRow1() + "-" + cAnchor.getCol1();
String img=byteToMultipartFile(picture.getPictureData().getData());
List<String> list1;
List<String> list2=map.get(key);
if(list2==null){
list1=new ArrayList<>();
list1.add(img);
map.put(key, list1);
}else{
list2.add(img);
map.put(key, list2);
}
}
}
return map;
}
/**
* 获取图片和位置 (xlsx)
*
* @param sheet
* @return
* @throws IOException
*/
public Map<String, String> getPicturesXlsx(XSSFSheet sheet) throws IOException {
Map<String, String> map = new HashMap<String, String>();
List<POIXMLDocumentPart> list = sheet.getRelations();
for (POIXMLDocumentPart part : list) {
if (part instanceof XSSFDrawing) {
XSSFDrawing drawing = (XSSFDrawing) part;
List<XSSFShape> shapes = drawing.getShapes();
for (XSSFShape shape : shapes) {
XSSFPicture picture = (XSSFPicture) shape;
XSSFClientAnchor anchor = picture.getPreferredSize();
CTMarker marker = anchor.getFrom();
String key = marker.getRow() + "-" + marker.getCol();
map.put(key, byteToMultipartFile(picture.getPictureData().getData()));
}
}
}
return map;
}
/**
* 获取图片和位置 (xlsx)
* 获取一个单元格中的多个图片
* @param sheet
* @return
* @throws IOException
*/
public Map<String, List<String>> getPicturesXlsxMulit(XSSFSheet sheet) throws IOException {
Map<String, List<String>> map = new HashMap<String, List<String>>();
List<POIXMLDocumentPart> list = sheet.getRelations();
for (POIXMLDocumentPart part : list) {
if (part instanceof XSSFDrawing) {
XSSFDrawing drawing = (XSSFDrawing) part;
List<XSSFShape> shapes = drawing.getShapes();
for (XSSFShape shape : shapes) {
XSSFPicture picture = (XSSFPicture) shape;
XSSFClientAnchor anchor = picture.getPreferredSize();
CTMarker marker = anchor.getFrom();
String key = marker.getRow() + "-" + marker.getCol();
String img=byteToMultipartFile(picture.getPictureData().getData());
List<String> list1;
List<String> list2=map.get(key);
if(list2==null){
list1=new ArrayList<>();
list1.add(img);
map.put(key, list1);
}else{
list2.add(img);
map.put(key, list2);
}
}
}
}
return map;
}
/**
* 通过byte[]获取fastdfs中地址
* @Param [bytes]
* @author hujun
**/
public String byteToMultipartFile(byte[] bytes) {
MultipartFile multipartFile = new MockMultipartFile("file", "test.jpg", "text/plain", bytes);
return fileRemoteClient.saveFileOSS(multipartFile);
}
}