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

有的时候需要将excel中所包含的图片在导入的时候取出来存到服务器中，

详细实现代码如下：

package com.liuf.util;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFPictureData;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFShape;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFPictureData;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
/**
 * 从excel中取出图片的功能代码
 * @author Administrator
 * 2019-11-26
 */
public class ExcelImageUtil {
    
    
    public static void main(String[] args) throws Exception {
        File file = new File("D:"+ File.separator +"123.xls");
        getData(file,0);
    }
       /**
     * 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
     * @param file 读取数据的源Excel
     * @param ignoreRows 读取数据忽略的行数，比喻行头不需要读入 忽略的行数为1
     * @return 读出的Excel中数据的内容
     * @throws FileNotFoundException
     * @throws IOException
     */
    public static String[][] getData(File file, int ignoreRows)
           throws FileNotFoundException, IOException {
       List<String[]> result = new ArrayList<String[]>();
       int rowSize = 0;
       BufferedInputStream in = new BufferedInputStream(new FileInputStream(
              file));
       // 打开HSSFWorkbook
       POIFSFileSystem fs = new POIFSFileSystem(in);
       HSSFWorkbook wb = new HSSFWorkbook(fs);

　　　　//1.读出的Excel中的图片
       Map<String, HSSFPictureData>  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 = new String[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()) {
                     case HSSFCell.CELL_TYPE_STRING:
                         value = cell.getStringCellValue();
                         break;
                     case HSSFCell.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;
                     case HSSFCell.CELL_TYPE_FORMULA:
                         // 导入时如果为公式生成的数据则无值
                         if (!cell.getStringCellValue().equals("")) {
                            value = cell.getStringCellValue();
                         } else {
                            value = cell.getNumericCellValue() + "";
                         }
                         break;
                     case HSSFCell.CELL_TYPE_BLANK:
                         break;
                     case HSSFCell.CELL_TYPE_ERROR:
                         value = "";
                         break;
                     case HSSFCell.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 = new String[result.size()][rowSize];
       for (int i = 0; i < returnArray.length; i++) {
           returnArray[i] = (String[]) result.get(i);
       }
       return returnArray;
    }


    /**
     * 去掉字符串右边的空格
     * @param str 要处理的字符串
     * @return 处理后的字符串
     */
     public static String 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)测试过
       * @param sheet
       * @return
       * @throws IOException
       */
      public static Map<String, HSSFPictureData> getPictures(HSSFSheet sheet) throws IOException {
            Map<String, HSSFPictureData> map = new HashMap<String, HSSFPictureData>();
            List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
            for (HSSFShape shape : list) {
                if (shape instanceof HSSFPicture) {
                    HSSFPicture picture = (HSSFPicture) shape;
                    HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
                    HSSFPictureData pdata = picture.getPictureData();
                    String key = cAnchor.getRow1() + "-" + cAnchor.getCol1(); // 行号-列号
                    map.put(key, pdata);
                }
            }
            return map;
        }
    //图片写出
      public static void printImg(Map<String, HSSFPictureData> maplist) throws IOException {  
         
            //for (Map<String, PictureData> 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();  
                }  
           // }  
              
        }
}

 点击下载

更完整的兼容xlsx格式的详细实现代码如下：

 

package com.util;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
 * 重写平台导入功能
 * @author Administrator
 * 2019-03-19
 */
public class ExcelsUtil {
    /**
     * 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
     * @param file 读取数据的源Excel
     * @param ignoreRows 读取数据忽略的行数，比喻行头不需要读入 忽略的行数为1
     * @return 读出的Excel中数据的内容
     * @throws FileNotFoundException
     * @throws IOException
     * @throws InvalidFormatException 
     */
    public static String[][] getData(File file, int ignoreRows)
           throws Exception {
       List<String[]> result = new ArrayList<String[]>();
       int rowSize = 0;
       BufferedInputStream in = new BufferedInputStream(new FileInputStream(
              file));
       String[][] returnArray = null;
       if (POIFSFileSystem.hasPOIFSHeader(in)) {
           // 打开HSSFWorkbook
           POIFSFileSystem fs = new POIFSFileSystem(in);
           HSSFWorkbook wb = new HSSFWorkbook(fs);
           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 = new String[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()) {
                         case HSSFCell.CELL_TYPE_STRING:
                             value = cell.getStringCellValue();
                             break;
                         case HSSFCell.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;
                         case HSSFCell.CELL_TYPE_FORMULA:
                             Cell numCell = row.getCell(0);
                             if(numCell != null){
                                 numCell.setCellType(Cell.CELL_TYPE_STRING);
                             }
                             value = numCell.getStringCellValue();
                             /*// 导入时如果为公式生成的数据则无值
                             if (!cell.getStringCellValue().equals("")) {
                                value = cell.getStringCellValue();
                             } else {
                                value = cell.getNumericCellValue() + "";
                             }*/
                             break;
                         case HSSFCell.CELL_TYPE_BLANK:
                             break;
                         case HSSFCell.CELL_TYPE_ERROR:
                             value = "";
                             break;
                         case HSSFCell.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();
           returnArray = new String[result.size()][rowSize];
           for (int i = 0; i < returnArray.length; i++) {
               returnArray[i] = (String[]) result.get(i);
           }
       }else{
           XSSFWorkbook wb = new XSSFWorkbook(OPCPackage.open(in));
           XSSFCell cell = null;
           for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
               XSSFSheet st = wb.getSheetAt(sheetIndex);
               // 第一行为标题，不取
               for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
                  XSSFRow row = st.getRow(rowIndex);
                  if (row == null) {
                      continue;
                  }
                  int tempRowSize = row.getLastCellNum() + 1;
                  if (tempRowSize > rowSize) {
                      rowSize = tempRowSize;
                  }
                  String[] values = new String[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()) {
                         case HSSFCell.CELL_TYPE_STRING:
                             value = cell.getStringCellValue();
                             break;
                         case HSSFCell.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;
                         case HSSFCell.CELL_TYPE_FORMULA:
                             Cell numCell = row.getCell(0);
                             if(numCell != null){
                                 numCell.setCellType(Cell.CELL_TYPE_STRING);
                             }
                             value = numCell.getStringCellValue();
                             /*// 导入时如果为公式生成的数据则无值
                             if (!cell.getStringCellValue().equals("")) {
                                value = cell.getStringCellValue();
                             } else {
                                value = cell.getNumericCellValue() + "";
                             }*/
                             break;
                         case HSSFCell.CELL_TYPE_BLANK:
                             break;
                         case HSSFCell.CELL_TYPE_ERROR:
                             value = "";
                             break;
                         case HSSFCell.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();
           returnArray = new String[result.size()][rowSize];
           for (int i = 0; i < returnArray.length; i++) {
               returnArray[i] = (String[]) result.get(i);
           }
       }
       
       return returnArray;
    }


    /**
     * 去掉字符串右边的空格
     * @param str 要处理的字符串
     * @return 处理后的字符串
     */
     public static String 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);
    }
}