测试demo
/**
* @param args
* @throws IOException
* @throws InvalidFormatException
*/
public static void main(String[] args) throws InvalidFormatException, IOException, org.apache.poi.openxml4j.exceptions.InvalidFormatException {
// 创建文件
File file = new File("D:\\aaa.xlsx");
// 创建流
InputStream input = new FileInputStream(file);
// 获取文件后缀名
String fileExt = file.getName().substring(file.getName().lastIndexOf(".") + 1);
// 创建Workbook
Workbook wb = null;
// 创建sheet
Sheet sheet = null;
//根据后缀判断excel 2003 or 2007+
if (fileExt.equals("xls")) {
wb = (HSSFWorkbook) WorkbookFactory.create(input);
} else {
wb = new XSSFWorkbook(input);
}
//获取excel sheet总数
int sheetNumbers = wb.getNumberOfSheets();
// sheet list
List<Map<String, PictureData>> sheetList = new ArrayList<>();
// 循环sheet
for (int i = 0; i < sheetNumbers; i++) {
sheet = wb.getSheetAt(i);
// map等待存储excel图片
Map<String, PictureData> sheetIndexPicMap;
// 判断用07还是03的方法获取图片
if (fileExt.equals("xls")) {
sheetIndexPicMap = getSheetPictrues03(i, (HSSFSheet) sheet, (HSSFWorkbook) wb);
} else {
List<ExcelPicPositionEntity> sheetPictures = getSheetPictures(i, (XSSFSheet) sheet);
for (ExcelPicPositionEntity sheetPicture : sheetPictures) {
uploadImage1(sheetPicture);
}
}
// 将当前sheet图片map存入list
//.add(sheetIndexPicMap);
}
// printImg(sheetList);
}
/**
* 获取Excel2003图片
* @param sheetNum 当前sheet编号
* @param sheet 当前sheet对象
* @param workbook 工作簿对象
* @return Map key:图片单元格索引（0_1_1）String，value:图片流PictureData
* @throws IOException
*/
public static Map<String, PictureData> getSheetPictrues03(int sheetNum,
HSSFSheet sheet, HSSFWorkbook workbook) {
Map<String, PictureData> sheetIndexPicMap = new HashMap<>();
List<HSSFPictureData> pictures = workbook.getAllPictures();
if (pictures.size() != 0) {
for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
if (shape instanceof HSSFPicture) {
HSSFPicture pic = (HSSFPicture) shape;
int pictureIndex = pic.getPictureIndex() - 1;
HSSFPictureData picData = pictures.get(pictureIndex-1);
String picIndex = String.valueOf(sheetNum) + "_"
+ String.valueOf(anchor.getRow1()) + "_"
+ String.valueOf(anchor.getCol1());
sheetIndexPicMap.put(picIndex, picData);
}
}
return sheetIndexPicMap;
} else {
return null;
}
}
private static List<ExcelPicPositionEntity> getSheetPictures(int sheetNum, XSSFSheet sheet) {
final int CONSTANT_525 = 525; // todo 等比例缩放待明确
List<ExcelPicPositionEntity> positionEntities = new ArrayList<>();
for (POIXMLDocumentPart dr : sheet.getRelations()) {
if (dr instanceof XSSFDrawing) {
XSSFDrawing drawing = (XSSFDrawing) dr;
List<XSSFShape> shapes = drawing.getShapes();
for (XSSFShape shape : shapes) {
ExcelPicPositionEntity positionEntity = new ExcelPicPositionEntity();
XSSFPicture pic = (XSSFPicture) shape;
XSSFClientAnchor anchor = pic.getPreferredSize();
CTMarker ctMarker = anchor.getFrom();
positionEntity.setSheetNum(sheetNum);
positionEntity.setColumn(ctMarker.getCol());
positionEntity.setRow(ctMarker.getRow());
positionEntity.setPictureData(pic.getPictureData());
positionEntity.setFileName(pic.getShapeName());
// 图片的原始大小 宽高
try {
BufferedImage imageInfo = ImageIO.read(new ByteArrayInputStream(pic.getPictureData().getData()));
int height = imageInfo.getHeight();
int width = imageInfo.getWidth();
// 等比例缩放
if (width > CONSTANT_525) {
BigDecimal temp = new BigDecimal(String.valueOf(CONSTANT_525)).divide(new BigDecimal(String.valueOf(width)),20, RoundingMode.UP);
BigDecimal y = new BigDecimal(String.valueOf(height)).multiply(temp).setScale(0, RoundingMode.UP);
height = y.intValue();
width = CONSTANT_525;
}
positionEntity.setX(width);
positionEntity.setY(height);
} catch (IOException e) {
throw new RuntimeException(e);
}
positionEntities.add(positionEntity);
}
}
}
return positionEntities;
}
@Data
public static class ExcelPicPositionEntity{
int sheetNum;
int column;
int row;
XSSFPictureData pictureData;
String fileName;
int x;
int y;
}
private static void uploadImage1(ExcelPicPositionEntity sheetPicture) {
int column = sheetPicture.getColumn();
int row = sheetPicture.getRow();
String fileExtension = sheetPicture.pictureData.suggestFileExtension();
byte[] imageData = sheetPicture.pictureData.getData();
// 指定文件生成的目录和文件名
String directoryPath = "E:\\work\\icms_develop\\attachWeb\\upload\\files\\temp"; // 替换为实际的目录路径
String fileName = "column_"+column+"row_"+row+"."+fileExtension; // 替换为实际的文件名和扩展名
// 构建文件的完整路径
File outputFile = new File(directoryPath, fileName);
// 使用FileOutputStream将字节数据写入文件
try (FileOutputStream fos = new FileOutputStream(outputFile)) {
fos.write(imageData);
System.out.println("图片文件已生成: " + outputFile.getAbsolutePath());
} catch (IOException e) {
System.err.println("写入文件时发生错误: " + e.getMessage());
}
}
应用到项目中处理
//集合数据
List<TBCollectProCulEntity> tbCollectProCulEntityList = new ArrayList<>();
writeImage(tbCollectProCulEntityList, wb);
public void writeImage(List<TBCollectProCulEntity> tbCollectProCulEntityList, Workbook wb){
Sheet sheet = wb.getSheetAt(0);
List<ExcelPicPositionEntity> sheetPictures = getSheetPictures(0, (XSSFSheet) sheet);
//分组
Map<Integer,List<ExcelPicPositionEntity>> map = groupByRow(sheetPictures);
int i =1;
for (TBCollectProCulEntity proCulEntity : tbCollectProCulEntityList) {
List<ExcelPicPositionEntity> excelPicPositionEntities = map.get(Integer.valueOf(i));
if(!CollectionUtils.isEmpty(excelPicPositionEntities)){
String id = proCulEntity.getId();
for (int index = 0; index < excelPicPositionEntities.size(); index++) {
uploadImage1(excelPicPositionEntities.get(index),index, id);
}
}
i++;
}
}
private Map<Integer,List<ExcelPicPositionEntity>> groupByRow(List<ExcelPicPositionEntity> sheetPictures) {
/*通过列分组图片信息*/
Map<Integer,List<ExcelPicPositionEntity>> map = new HashMap<>();
if(CollectionUtils.isEmpty(sheetPictures)){
return map;
}
for (ExcelPicPositionEntity sheetPicture : sheetPictures) {
if(map.containsKey(Integer.valueOf(sheetPicture.getRow()))){
List<ExcelPicPositionEntity> excelPicPositionEntities = map.get(Integer.valueOf(sheetPicture.getRow()));
excelPicPositionEntities.add(sheetPicture);
map.put(Integer.valueOf(sheetPicture.getRow()), excelPicPositionEntities);
continue;
}
List<ExcelPicPositionEntity> excelPicPositionEntities = new ArrayList<>();
excelPicPositionEntities.add(sheetPicture);
map.put(Integer.valueOf(sheetPicture.getRow()), excelPicPositionEntities);
}
return map;
}
private static List<ExcelPicPositionEntity> getSheetPictures(int sheetNum, XSSFSheet sheet) {
final int CONSTANT_525 = 525; // todo 等比例缩放待明确
List<ExcelPicPositionEntity> positionEntities = new ArrayList<>();
for (POIXMLDocumentPart dr : sheet.getRelations()) {
if (dr instanceof XSSFDrawing) {
XSSFDrawing drawing = (XSSFDrawing) dr;
List<XSSFShape> shapes = drawing.getShapes();
for (XSSFShape shape : shapes) {
ExcelPicPositionEntity positionEntity = new ExcelPicPositionEntity();
XSSFPicture pic = (XSSFPicture) shape;
XSSFClientAnchor anchor = pic.getPreferredSize();
CTMarker ctMarker = anchor.getFrom();
positionEntity.setSheetNum(sheetNum);
positionEntity.setColumn(ctMarker.getCol());
positionEntity.setRow(ctMarker.getRow());
positionEntity.setPictureData(pic.getPictureData());
positionEntity.setFileName(pic.getSheet().getSheetName());
// 图片的原始大小 宽高
try {
BufferedImage imageInfo = ImageIO.read(new ByteArrayInputStream(pic.getPictureData().getData()));
int height = imageInfo.getHeight();
int width = imageInfo.getWidth();
// 等比例缩放
if (width > CONSTANT_525) {
BigDecimal temp = new BigDecimal(String.valueOf(CONSTANT_525)).divide(new BigDecimal(String.valueOf(width)),20, RoundingMode.UP);
BigDecimal y = new BigDecimal(String.valueOf(height)).multiply(temp).setScale(0, RoundingMode.UP);
height = y.intValue();
width = CONSTANT_525;
}
positionEntity.setX(width);
positionEntity.setY(height);
} catch (IOException e) {
throw new RuntimeException(e);
}
positionEntities.add(positionEntity);
}
}
}
return positionEntities;
}
扫一扫
1273
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
