POI读取excel

##背景：
由于公司业务需要将本地excel数据读取到数据库中。
##前提：
必须定义好excel模板，如下所示：
###模板一：
|行号| A | B | C |
| -----|:----? ----?
| 1 | 姓名 | 年龄 | 学历 |
| 2 | 王武 | 15 | 大二 |
| 3 | 李思 | 25 |研究生|
###模板二
| 行号 | A | B | C |
| -----|:----? ----?
| 1 | | | |
| 2 | 姓名 | 年龄 | 学历 |
| 3 | 王武 | 15 | 大二 |
| 4 | 李思 | 25 | 研究生 |
以上两个模板的行号对应的就是excel中的行号，注意列名所在行
###需要实现的功能：
因为自定义模板的存在，所以每张模板中列名所在第几行也是知道的。实现了输入第几张表格和第几行为列名所在行就可以读取。
###javacode:

package com.nd.core.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.nd.app.controller.ExcelWriter;

/**
 * 操作Excel表格的功能类
 */
public final class ExcelReader extends Reader {
	private static final Log LOG = LogFactory.getLog(ExcelReader.class);
	private static final String XLSX = "xlsx";
	private static final String XLS = "xls";

	// 文件的后缀名
	private String suffix;
	// 列名所在行
	private String rowNameNumber;
	// 标题
	private String titleNumber;
	private Workbook workbook;
	private InputStream inputStream;

	public ExcelReader(InputStream in, String rowNameNumber, String suffix, String titleNumber) throws Exception {
		this.rowNameNumber = rowNameNumber;
		this.inputStream = in;
		this.suffix = suffix;
		this.titleNumber = titleNumber;
		initWorkbook();
	}

	/**
	 * 初始化workbook
	 * 
	 * @param is
	 * @param suffix
	 * @throws IOException
	 */
	private void initWorkbook() throws IOException {
		// excel版本不同，处理方法不同
		if (XLS.equals(suffix)) {
			workbook = new HSSFWorkbook(inputStream);
		} else if (XLSX.equals(suffix)) {
			workbook = new XSSFWorkbook(inputStream);
		}
	}

	/**
	 * 读取具体文件
	 * 
	 * @throws IOException
	 */
	@Override
	public Map<String, List<Map<String, Object>>> readFile() throws Exception {
		return readAllSheet();
	}

	/**
	 * 读取Excel列名
	 * 
	 * @param is
	 * @param suffix
	 * @return
	 * @throws Exception
	 */
	public List<String[]> readColumnName() throws Exception {
		// 读取全部表的列名
		return readAllSheetColName();
	}

	/**
	 * 读取Excel的标题
	 * 
	 * @return
	 * @throws Exception
	 */
	public Map<String, String> readTitle() throws Exception {
		// 读取全部表的标题
		return readAllSheetTitle();
	}

	/**
	 * 读取表单
	 * 
	 * @param workbook
	 * @return
	 */
	private Map<String, List<Map<String, Object>>> readAllSheet() throws Exception {
		Map<String, List<Map<String, Object>>> sheetMap = new LinkedHashMap<String, List<Map<String, Object>>>();
		for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
			org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(numSheet);
			int colNum = 0; // 列数
			String[] strColName = null; // 列名
			List<Map<String, Object>> tempList = new ArrayList<Map<String, Object>>();
			boolean initTitle = true;
			for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
				Row row = sheet.getRow(rowNum);
				if (row == null) { // 如果此列为空就跳过
					continue;
				}

				if (initTitle) {
					colNum = row.getPhysicalNumberOfCells();
					strColName = new String[colNum];
				}
				initTitle = false;
				Map<String, Object> map = new LinkedHashMap<String, Object>();
				if (rowNum == Integer.valueOf(rowNameNumber)) {
					for (int i = 0; i < colNum; i++) {
						strColName[i] = getCellValue(row.getCell(i));
					}
				}
				if (rowNum > Integer.valueOf(rowNameNumber)) // 内容
				{
					for (int i = 0; i < colNum; i++) {
						map.put(strColName[i], getCellValue(row.getCell(i)));
					}
					tempList.add(map);
				}

			}
			sheetMap.put(workbook.getSheetName(numSheet), tempList);
		}
		return sheetMap;
	}
	

	/**
	 * 读取标题列表
	 * 
	 * @param workbook
	 * @return
	 */
	private Map<String, String> readAllSheetTitle() {
		Map<String, String> map = new HashMap<String, String>();
		for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
			org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(numSheet);
			for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
				Row row = sheet.getRow(rowNum);
				if (rowNum == Integer.valueOf(titleNumber)) {
					map.put(workbook.getSheetName(numSheet), getCellValue(row.getCell((short) 0)).trim());
					break;
				}
			}
		}
		return map;
	}

	/**
	 * 获取列表数组
	 * 
	 * @param workbook
	 * @return
	 */
	private List<String[]> readAllSheetColName() throws Exception {
		List<String[]> titleList = new ArrayList<String[]>();
		for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
			org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(numSheet);
			String[] strColName = null; // 列名数组
			int colNum = 0; // 列数
			for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
				Row row = sheet.getRow(rowNum);
				if (rowNum == Integer.valueOf(rowNameNumber)) {
					colNum = row.getPhysicalNumberOfCells();
					strColName = new String[colNum];
					for (int i = 0; i < colNum; i++) {
						strColName[i] = getCellValue(row.getCell((short) i));
					}

					titleList.add(strColName);
				}
			}
		}
		return titleList;
	}

	private String getCellValue(Cell cell) {
		try {
			if (cell != null) {
				switch (cell.getCellType()) {
				case Cell.CELL_TYPE_NUMERIC:
					DataFormatter formatter = new DataFormatter();
					return formatter.formatCellValue(cell);
				case Cell.CELL_TYPE_STRING:
					return cell.getRichStringCellValue().getString();
				case Cell.CELL_TYPE_FORMULA:
					if (HSSFDateUtil.isCellDateFormatted(cell)) {
						Date date = cell.getDateCellValue();
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
						return sdf.format(date);
					} else {
						DataFormatter f = new DataFormatter();
						return f.formatCellValue(cell);
					}
				case Cell.CELL_TYPE_BOOLEAN:
					return String.valueOf(cell.getBooleanCellValue());
				default:
					return "";
				}
			}
		} catch (Exception e) {
			LOG.error("获取cell的值失败", e);
		}

		return "";
	}

	public static void main(String[] args) throws Exception {
	
		String path1 = "/Users/JJC/Downloads/111111111111.xlsx";
		InputStream is = new FileInputStream(new File(path1));
		ExcelReader er = new ExcelReader(is, "1", "xlsx", "0");
		List<String[]> list = er.readColumnName();
		Map<String, String> titleMap = er.readTitle();
		Map<String, List<Map<String, Object>>> contentMap = er.readFile();
		ExcelWriter.gatherExcel(titleMap, contentMap);
		/**
		// for (String[] s : list) {
		// for (String str : s) {
		// System.out.println(str);
		// }
		//
		// System.out.println("===========next");
		//
		// }

//		for (String title : titleList) {
//			System.out.println(title);
//		}

		for (String key : map.keySet()) {
			System.out.println("sheet name = " + key);
			List<Map<String, Object>> ll = map.get(key);
			for (Map<String, Object> mm : ll) {
				for (String kk : mm.keySet()) {
					System.out.println(kk + "--> " + mm.get(kk));
				}
				System.out.println("====><<<>>>");
			}
		}
	}
	*/
		
		
	}
}
}

####需要用到的包：
这里我选择了POI3.8版本的，具体的可以去官网上下载。
我公司的MAVEN地址如下：

<!-- 文件读取 -->
		<dependency>
  			<groupId>com.poi</groupId>
  			<artifactId>poi-examples3.8</artifactId>
 			<version>0.0.1</version>
		</dependency>
		<dependency>
  			<groupId>com.poi</groupId>
			 <artifactId>poi-excelant3.8</artifactId>
			 <version>0.0.1</version>
		</dependency>
		<dependency>
 			 <groupId>com.poi</groupId>
			  <artifactId>poi-ooxml-schemas3.8</artifactId>
			  <version>0.0.1</version>
		</dependency>
		<dependency>
			  <groupId>com.poi</groupId>
			  <artifactId>poi-ooxml3.8</artifactId>
			  <version>0.0.1</version>
		</dependency>
		<dependency>
			  <groupId>com.poi</groupId>
			  <artifactId>poi-scratchpad3.8</artifactId>
			  <version>0.0.1</version>
		</dependency> 
		<dependency>
			  <groupId>com.poi</groupId>
			  <artifactId>poi3.8</artifactId>
			  <version>0.0.1</version>
		</dependency>
		<dependency>
		  <groupId>com.poi</groupId>
		  <artifactId>stax-api1.0.1</artifactId>
		  <version>0.0.1</version>
		</dependency>
		<dependency>
		  <groupId>com.poi</groupId>
		  <artifactId>xmlbeans2.3.0</artifactId>
		  <version>0.0.1</version>
		</dependency>
		<dependency>
			<groupId>com.dom4j</groupId>
			<artifactId>dom4j</artifactId>
			<version>1.6.1</version>
		</dependency>

###注意点：
####excel分为2003-2007版本和2007-2010版本的，版本不同所用到的操作类也不一样。2003-2007用的是HSSFWorkbook，2007-2010用的是 XSSFWorkbook。

欢迎志同道合的朋友加入java讨论群，讨论下技术，顺便交个朋友！群号:426090267