java学习记录(6)- poi 图片解析-定位与读取
提要:
- 引言
- 代码
- 总结
1 引言:把excel中的内容转换到数据库中,是一个常见的工作。 而如果excel中有图片应该如何进行图片的操作,以及确定图片到底是属于哪一行的内容。在图片的解析中发现,图片的解析和文字的解析是分开的。能够直接读取图片左上角所处的单元格的位置,但是图片在excel中可以一定,并不特定的属于某一个单元格。
2 代码
//从03格式excel中获取图片
public static Map<String, PictureData> getSheetPictrues03(int sheetNum,
HSSFSheet sheet, HSSFWorkbook workbook) {
Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
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);
String picIndex = String.valueOf(sheetNum) + "_"
+ String.valueOf(anchor.getRow1()) + "_"
+ String.valueOf(anchor.getCol1());
sheetIndexPicMap.put(picIndex, picData);
}
}
return sheetIndexPicMap;
} else {
return null;
}
}
//07格式excel获取图片。
public static Map<String, PictureData> getSheetPictrues07(int sheetNum,
XSSFSheet sheet, XSSFWorkbook workbook) {
Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
for (POIXMLDocumentPart dr : sheet.getRelations()) {
if (dr instanceof XSSFDrawing) {
XSSFDrawing drawing = (XSSFDrawing) dr;
List<XSSFShape> shapes = drawing.getShapes();
for (XSSFShape shape : shapes) {
XSSFPicture pic = (XSSFPicture) shape;
XSSFClientAnchor anchor = pic.getPreferredSize();
CTMarker ctMarker = anchor.getFrom();
String picIndex = String.valueOf(sheetNum) + "_"
+ ctMarker.getRow() + "_" + ctMarker.getCol();
// pic.getPictureData().getData();
sheetIndexPicMap.put(picIndex, pic.getPictureData());
}
}
}
return sheetIndexPicMap;
}
//图片及位置获取
public static void getAllDate(String excelPath) throws InvalidFormatException, IOException {
// 创建文件
File file = new File(excelPath);
// 创建流
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<Map<String, PictureData>>();
// 循环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 {
sheetIndexPicMap = getSheetPictrues07(i, (XSSFSheet) sheet, (XSSFWorkbook) wb);
}
// 将当前sheet图片map存入list
sheetList.add(sheetIndexPicMap);
}
// Map map = getData(excelPath);
printImg(sheetList);
}
//将图片保存到指定位置
public static void printImg(List<Map<String, PictureData>> sheetList) throws IOException {
for (Map<String, PictureData> map : sheetList) {
Object key[] = map.keySet().toArray();
for (int i = 0; i < map.size(); i++) {
// 获取图片流
PictureData pic = map.get(key[i]);
// 获取图片索引
String picName = key[i].toString();
// 获取图片格式
String ext = pic.suggestFileExtension();
byte[] data = pic.getData();
FileOutputStream out = new FileOutputStream("D:\\pic\\img" + picName + "." + ext);
out.write(data);
out.close();
}
}
}
3 总结
数据库中的数据以一行为单位进行数据存储,同样excel总也是一样是一个基本数据单元。
数据库处理图片1 保存图片的地址 2 转成base64,行固定。
excel处理图片能够定位图片的左上角单元格,但可以移动。