java 读取 excel 图片_java--poi读取excel图片和内容（支持03版本）

packagecom.liuf.util;importjava.io.BufferedInputStream;importjava.io.File;importjava.io.FileInputStream;importjava.io.FileNotFoundException;importjava.io.FileOutputStream;importjava.io.IOException;importjava.text.DecimalFormat;importjava.text.SimpleDateFormat;importjava.util.ArrayList;importjava.util.Arrays;importjava.util.Date;importjava.util.HashMap;importjava.util.List;importjava.util.Map;importorg.apache.poi.POIXMLDocumentPart;importorg.apache.poi.hssf.usermodel.HSSFCell;importorg.apache.poi.hssf.usermodel.HSSFClientAnchor;importorg.apache.poi.hssf.usermodel.HSSFDateUtil;importorg.apache.poi.hssf.usermodel.HSSFPicture;importorg.apache.poi.hssf.usermodel.HSSFPictureData;importorg.apache.poi.hssf.usermodel.HSSFRow;importorg.apache.poi.hssf.usermodel.HSSFShape;importorg.apache.poi.hssf.usermodel.HSSFSheet;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.poifs.filesystem.POIFSFileSystem;importorg.apache.poi.xssf.usermodel.XSSFClientAnchor;importorg.apache.poi.xssf.usermodel.XSSFDrawing;importorg.apache.poi.xssf.usermodel.XSSFPicture;importorg.apache.poi.xssf.usermodel.XSSFPictureData;importorg.apache.poi.xssf.usermodel.XSSFShape;importorg.apache.poi.xssf.usermodel.XSSFSheet;importorg.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;/*** 从excel中取出图片的功能代码

*@authorAdministrator

* 2019-11-26*/

public classExcelImageUtil {public static void main(String[] args) throwsException {

File file= new File("D:"+ File.separator +"123.xls");

getData(file,0);

}/*** 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行

*@paramfile 读取数据的源Excel

*@paramignoreRows 读取数据忽略的行数，比喻行头不需要读入 忽略的行数为1

*@return读出的Excel中数据的内容

*@throwsFileNotFoundException

*@throwsIOException*/

public static String[][] getData(File file, intignoreRows)throwsFileNotFoundException, IOException {

List result = new ArrayList();int rowSize = 0;

BufferedInputStream in= new BufferedInputStream(newFileInputStream(

file));//打开HSSFWorkbook

POIFSFileSystem fs = newPOIFSFileSystem(in);

HSSFWorkbook wb= newHSSFWorkbook(fs);

//1.读出的Excel中的图片

Map maplist=null;

HSSFSheet sheet=null;

sheet= wb.getSheetAt(0);//支持word03的方法获取图片

if (file.getPath().endsWith(".xls")) {

maplist=getPictures((HSSFSheet) sheet);

}try{

printImg(maplist);

}catch(IOException e) {//TODO Auto-generated catch block

e.printStackTrace();

}

//2.读出的Excel中数据的内容

HSSFCell cell= null;for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {

HSSFSheet st=wb.getSheetAt(sheetIndex);//第一行为标题，不取

for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {

HSSFRow row=st.getRow(rowIndex);if (row == null) {continue;

}int tempRowSize = row.getLastCellNum() + 1;if (tempRowSize >rowSize) {

rowSize=tempRowSize;

}

String[] values= newString[rowSize];

Arrays.fill(values,"");boolean hasValue = false;for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {

String value= "";

cell=row.getCell(columnIndex);if (cell != null) {//注意：一定要设成这个，否则可能会出现乱码//cell.setEncoding(HSSFCell.ENCODING_UTF_16);

switch(cell.getCellType()) {caseHSSFCell.CELL_TYPE_STRING:

value=cell.getStringCellValue();break;caseHSSFCell.CELL_TYPE_NUMERIC:if(HSSFDateUtil.isCellDateFormatted(cell)) {

Date date=cell.getDateCellValue();if (date != null) {

value= new SimpleDateFormat("yyyy-MM-dd").format(date);

}else{

value= "";

}

}else{

value= new DecimalFormat("0").format(cell.getNumericCellValue());

}break;caseHSSFCell.CELL_TYPE_FORMULA://导入时如果为公式生成的数据则无值

if (!cell.getStringCellValue().equals("")) {

value=cell.getStringCellValue();

}else{

value= cell.getNumericCellValue() + "";

}break;caseHSSFCell.CELL_TYPE_BLANK:break;caseHSSFCell.CELL_TYPE_ERROR:

value= "";break;caseHSSFCell.CELL_TYPE_BOOLEAN:

value= (cell.getBooleanCellValue() == true ? "Y" : "N");break;default:

value= "";

}

}if (columnIndex == 0 && value.trim().equals("")) {break;

}

values[columnIndex]=rightTrim(value);

hasValue= true;

}if(hasValue) {

result.add(values);

}

}

}

in.close();

String[][] returnArray= newString[result.size()][rowSize];for (int i = 0; i < returnArray.length; i++) {

returnArray[i]=(String[]) result.get(i);

}returnreturnArray;

}/*** 去掉字符串右边的空格

*@paramstr 要处理的字符串

*@return处理后的字符串*/

public staticString rightTrim(String str) {if (str == null) {return "";

}int length =str.length();for (int i = length - 1; i >= 0; i--) {if (str.charAt(i) != 0x20) {break;

}

length--;

}return str.substring(0, length);

}/*** 获取图片和位置 (xls)测试过

*@paramsheet

*@return*@throwsIOException*/

public static Map getPictures(HSSFSheet sheet) throwsIOException {

Map map = new HashMap();

List list =sheet.getDrawingPatriarch().getChildren();for(HSSFShape shape : list) {if (shape instanceofHSSFPicture) {

HSSFPicture picture=(HSSFPicture) shape;

HSSFClientAnchor cAnchor=(HSSFClientAnchor) picture.getAnchor();

HSSFPictureData pdata=picture.getPictureData();

String key= cAnchor.getRow1() + "-" + cAnchor.getCol1(); //行号-列号

map.put(key, pdata);

}

}returnmap;

}//图片写出

public static void printImg(Map maplist) throwsIOException {//for (Map map : sheetList) {

Object key[] =maplist.keySet().toArray();for (int i = 0; i < maplist.size(); i++) {//获取图片流

HSSFPictureData pic =maplist.get(key[i]);//获取图片索引

String picName =key[i].toString();byte[] data =pic.getData();//图片保存路径

FileOutputStream out = new FileOutputStream("D:\\img\\pic" + picName + ".jpg");

out.write(data);

out.close();

}//}

}

}