poi读取Excel文件和图片

首先得说一下，Excel文件是有03版和07版的区别的，也就是.xls和.xlsx，这两个文件需要分开读取。

其它的废话就不说了，直接贴代码：

package util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.HSSFCell;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtils {
	// 默认单元格格式化日期字符串
	public static final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	// 默认单元格内容为数字时格式
	private static DecimalFormat df = new DecimalFormat("0");
	// 格式化数字
	private static DecimalFormat nf = new DecimalFormat("0.00");

	/**
	 * 读取Excel中sheet1的内容
	 * @param file
	 * @return ArrayList<ArrayList<Object>>
	 */
	public static ArrayList<ArrayList<Object>> readExcel(File file) {
		if (file == null) {
			return null;
		}
		if (file.getName().endsWith("xlsx")) {
			// 处理ecxel2007
			return readExcel2007(file);
		} else if (file.getName().endsWith("xls")) {
			// 处理ecxel2003
			return readExcel2003(file);
		} else {
			return null;
		}
	}

	/**
	 * @param file
	 * @return
	 */
	private static ArrayList<ArrayList<Object>> readExcel2003(File file) {
		try {
			ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
			ArrayList<Object> oneRow = null;
			HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file));
			HSSFSheet sheet = workbook.getSheetAt(0);
			HSSFRow row;
			HSSFCell cell;
			Object value;
			for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
				row = sheet.getRow(i);
				oneRow = new ArrayList<Object>();
				if (row == null || checkRowNull2003(row)) {
					continue;
				}
				for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
					cell = row.getCell(j);
					if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
						if (j != row.getLastCellNum()) {
							oneRow.add("");
						}
						continue;
					}
					switch (cell.getCellType()) {
					case HSSFCell.CELL_TYPE_STRING:
						value = cell.getStringCellValue();
						break;
					case HSSFCell.CELL_TYPE_BOOLEAN:
						value = cell.getBooleanCellValue();
						break;
					case HSSFCell.CELL_TYPE_BLANK:
						value = "";
						break;
					case HSSFCell.CELL_TYPE_NUMERIC:
						if ("General".equals(cell.getCellStyle().getDataFormatString())) {
							double doubleVal = cell.getNumericCellValue();
							int intVal = (int) Math.round(doubleVal);  
							if (Double.parseDouble(intVal + ".0") == doubleVal) {
								value = df.format(intVal);
							} else {
								value = nf.format(doubleVal);
							}
						} else {
							value = ((Double)cell.getNumericCellValue()).toString();
						}
						break;
					default:
						value = cell.toString();
						break;
					}
					oneRow.add(value);
				}
				rowList.add(oneRow);
			}
			return rowList;
		} catch (IOException e) {
			return null;
		}
	}

	/**
	 * @param file
	 * @return
	 */
	private static ArrayList<ArrayList<Object>> readExcel2007(File file) {
		try {
			ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
			ArrayList<Object> oneRow = null;
			XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));
			XSSFSheet sheet = workbook.getSheetAt(0);
			XSSFRow row;
			XSSFCell cell;
			Object value;
			for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
				row = sheet.getRow(i);
				oneRow = new ArrayList<Object>();
				if (row == null || checkRowNull2007(row)) {
					continue;
				}
				for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
					cell = row.getCell(j);
					if (cell == null || cell.getCellType() == XSSFCell.CELL_TYPE_BLANK) {
						if (j != row.getLastCellNum()) {
							oneRow.add("");
						}
						continue;
					}
					switch (cell.getCellType()) {
					case XSSFCell.CELL_TYPE_STRING:
						value = cell.getStringCellValue();
						break;
					case XSSFCell.CELL_TYPE_BOOLEAN:
						value = cell.getBooleanCellValue();
						break;
					case XSSFCell.CELL_TYPE_BLANK:
						value = "";
						break;
					case XSSFCell.CELL_TYPE_NUMERIC:
						if ("General".equals(cell.getCellStyle().getDataFormatString())) {
							double doubleVal = cell.getNumericCellValue();
							int intVal = (int) Math.round(doubleVal);  
							if (Double.parseDouble(intVal + ".0") == doubleVal) {
								value = df.format(intVal);
							} else {
								value = nf.format(doubleVal);
							}
						} else {
							value = ((Double)cell.getNumericCellValue()).toString();
						}
						break;
					default:
						value = cell.toString();
						break;
					}
					oneRow.add(value);
				}
				rowList.add(oneRow);
			}
			return rowList;
		} catch (IOException e) {
			return null;
		}
	}

	/**
	 * 判断行为空(xls)
	 * @param row
	 * @return
	 */
	private static boolean checkRowNull2003(HSSFRow row) {
		for (int i = row.getFirstCellNum(); i < row.getPhysicalNumberOfCells(); i++) {
			HSSFCell cell = row.getCell(i);
			if (cell != null && cell.getCellType() != HSSFCell.CELL_TYPE_BLANK) {
				return false;
			}
		}
		return true;
	}

	/**
	 * 判断行为空(xlsx)
	 * @param row
	 * @return
	 */
	private static boolean checkRowNull2007(XSSFRow row) {
		for (int i = row.getFirstCellNum(); i < row.getPhysicalNumberOfCells(); i++) {
			XSSFCell cell = row.getCell(i);
			if (cell != null && cell.getCellType() != XSSFCell.CELL_TYPE_BLANK) {
				return false;
			}
		}
		return true;
	}
}

　　我遇到了需要读取保存Excel中图片的问题，之前在网上找到了一些代码参考，发现HSSFWorkbook类中有一个getAllPictures()方法可以返回一个Excel表中的所有图片的集合，通过index取得图片，但是这么做的话会有一个问题，就是得到的图片集合的key是独立的，没有办法与行号对应起来，也就是不知道图片对应的单元格。

　　下面是我用来获得图片的位置的方法，分享给大家：

	/**
	 * 获取图片和位置 (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 = picture.getClientAnchor();
				HSSFPictureData pdata = picture.getPictureData();
				String key = cAnchor.getRow1() + "-" + cAnchor.getCol1(); // 行号-列号
				map.put(key, pdata);
			}
		}
		return map;
	}

	/**
	 * 获取图片和位置 (xlsx)
	 * @param sheet
	 * @return
	 * @throws IOException
	 */
	public static Map<String, XSSFPictureData> getPictures (XSSFSheet sheet) throws IOException {
		Map<String, XSSFPictureData> map = new HashMap<String, XSSFPictureData>();
		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, picture.getPictureData());
				}
			}
		}
		return map;
	}