java用poi解析excel2003和2007并封装成对象返回
网上看了很多资料 比价乱 而且 质量参差不齐 ，自己 通过实践和看资料学习
完整的 总结了这两个java里常用的工具类
整合成两个通用的方法
为了取值更加方便我用了 json来 组装数据
其实 最新的poi3.9已经可以用一个方法来 读取excel2003和2007了
但是我为了业务逻辑更加清楚才分开来 ，如果 你觉得还可以重构的更好 可以 发出你的改良后的代码哦
import java.io.BufferedOutputStream;import java.io.ByteArrayInputStream;import java.io.ByteArrayOutputStream;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.util.Date;import java.util.LinkedHashMap;import java.util.LinkedList;import java.util.List;import java.util.Map;import net.sf.json.JSONObject;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.poifs.filesystem.POIFSFileSystem;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;/** * Excel 操作工具类 其中涉及几个文件流操作和转换 * @author zqb * * 2013-8-2 */public class ExcelUtil {private static POIFSFileSystem fs;private static HSSFWorkbook wb;private static HSSFSheet sheet;private static HSSFRow row;private static FileInputStream input;private static String[] excleTitle;public static boolean isNum(String str) {return str.matches("^[-+]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?)$");}/** * 根据文件路径读取Excel数据内容 返回map * @param excelPath * @return */public static Map<Integer, JSONObject> readExcelContent(String excelPath) {Map<Integer, JSONObject> contentJson = new LinkedHashMap<Integer, JSONObject>();String excelStr = "";// excel 内容try {input = new FileInputStream(new File(excelPath));fs = new POIFSFileSystem(input);wb = new HSSFWorkbook(fs);sheet = wb.getSheetAt(0);int rowNum = sheet.getLastRowNum(); // 得到总行数row = sheet.getRow(0);// 得到标题的内容对象。int colNum = row.getPhysicalNumberOfCells();// 得到每行的列数。excleTitle = new String[colNum];for (int i = 0; i < colNum; i++) {excleTitle[i] = getStringCellValue(row.getCell((short) i));}// 正文内容应该从第二行开始,第一行为表头的标题for (int i = 1; i <= rowNum; i++) {row = sheet.getRow(i);int j = 0;while (j < colNum) {String v = "";if (j + 1 == colNum) {String vs = getStringCellValue(row.getCell((short) j)).trim();if (vs.indexOf(".") > -1) {if (isNum(vs)) { // 是否是数字if (vs.endsWith("0")) {v = vs.substring(0, vs.indexOf("."));}} else {v = vs.trim();}} else {v = vs.trim();}excelStr += v;} else {String vs = getStringCellValue(row.getCell((short) j)).trim()+ "&";if (vs.indexOf(".") > -1) {if (isNum(vs)) { // 是否是数字if (vs.endsWith("0")) { // 处理用poi读取excel整数后面加.0的格式化v = vs.substring(0, vs.indexOf("."));}} else {v = vs.trim();}} else {v = vs.trim();}excelStr += v;}j++;}String excelstrArray[] = excelStr.split("&", -1); // 每行数据Map<String, String> params = new LinkedHashMap<String, String>();for (int k = 0; k < excelstrArray.length; k++) {params.put(excleTitle[k], excelstrArray[k]);}JSONObject jsonObject = JSONObject.fromObject(params);contentJson.put(i, jsonObject);//content.put(i, excelStr);excelStr = "";}} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();} finally {try {if (input != null) {input.close();}} catch (IOException e) {e.printStackTrace();}}return contentJson;}/** * 根据文件流读取Excel数据内容 返回map 2003 * @param input * @return */public static Map<Integer, JSONObject> readExcelContent(InputStream input) {// 读取Excel数据内容Map<Integer, JSONObject> contentJson = new LinkedHashMap<Integer, JSONObject>();String excelStr = "";// excel 内容try {fs = new POIFSFileSystem(input);wb = new HSSFWorkbook(fs);sheet = wb.getSheetAt(0);int rowNum = sheet.getLastRowNum(); // 得到总行数row = sheet.getRow(0);// 得到标题的内容对象。int colNum = row.getPhysicalNumberOfCells();// 得到每行的列数。excleTitle = new String[colNum];for (int i = 0; i < colNum; i++) {excleTitle[i] = getStringCellValue(row.getCell((short) i));}// 正文内容应该从第二行开始,第一行为表头的标题for (int i = 1; i <= rowNum; i++) {row = sheet.getRow(i);int j = 0;while (j < colNum) {String v = "";if (j + 1 == colNum) {String vs = getStringCellValue(row.getCell((short) j)).trim();if (vs.indexOf(".") > -1) {if (isNum(vs)) { // 是否是数字if (vs.endsWith("0")) {v = vs.substring(0, vs.indexOf("."));}} else {v = vs.trim();}} else {v = vs.trim();}excelStr += v;} else {String vs = getStringCellValue(row.getCell((short) j)).trim()+ "&";if (vs.indexOf(".") > -1) {if (isNum(vs)) { // 是否是数字if (vs.endsWith("0")) { // 处理用poi读取excel整数后面加.0的格式化v = vs.substring(0, vs.indexOf("."));}} else {v = vs.trim();}} else {v = vs.trim();}excelStr += v;}j++;}String excelstrArray[] = excelStr.split("&", -1); // 每行数据Map<String, String> params = new LinkedHashMap<String, String>();for (int k = 0; k < excelstrArray.length; k++) {params.put(excleTitle[k], excelstrArray[k]);}JSONObject jsonObject = JSONObject.fromObject(params);contentJson.put(i, jsonObject);// content.put(i, excelStr);excelStr = "";}} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();} finally {try {if (input != null) {input.close();}} catch (IOException e) {e.printStackTrace();}}return contentJson;}/** * 读取Office 2007 excel * */public static Map<Integer, JSONObject> read2007Excels(InputStream input)throws IOException {Map<Integer, JSONObject> contentJson = new LinkedHashMap<Integer, JSONObject>();// 构造 XSSFWorkbook 对象，strPath 传入文件路径XSSFWorkbook xwb = new XSSFWorkbook(input);// 读取第一章表格内容XSSFSheet sheet = xwb.getSheetAt(0);Object value = null;XSSFRow row = null;XSSFCell cell = null;XSSFRow headerrow = sheet.getRow(0); // 表头 得到标题的内容对象int colNum = headerrow.getPhysicalNumberOfCells();// 得到每行的列数。excleTitle = new String[colNum];for (int i = 0; i < colNum; i++) {excleTitle[i] = getStringCellValue(headerrow.getCell((short) i));}// System.out.println(sheet.getPhysicalNumberOfRows());// 循环内容项 不循环标题 所以+1for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getPhysicalNumberOfRows(); i++) {row = sheet.getRow(i);if (row == null) {continue;}List<String> linked = new LinkedList<String>();for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {cell = row.getCell(j);if (null != cell) {value = getStringCellValue(cell);}linked.add(StringUtil.stringIsNull(value));}Map<String, String> params = new LinkedHashMap<String, String>();for (int j = 0; j < linked.size(); j++) {params.put(excleTitle[j], linked.get(j));}JSONObject jsonObject = JSONObject.fromObject(params);contentJson.put(i, jsonObject);}return contentJson;}/** * 根据(字节串(或叫字节数组)变成输入流的形式)读取Excel数据内容 返回map * @param input * @return */public static Map<Integer, JSONObject> readExcelContent(ByteArrayInputStream input) {// 读取Excel数据内容// Map<Integer, String> content = new HashMap<Integer, String>();Map<Integer, JSONObject> contentJson = new LinkedHashMap<Integer, JSONObject>();String excelStr = "";// excel 内容try {//ByteArrayInputStream is = new ByteArrayInputStream( new byte[1000]);fs = new POIFSFileSystem(input);wb = new HSSFWorkbook(fs);sheet = wb.getSheetAt(0);int rowNum = sheet.getLastRowNum(); // 得到总行数row = sheet.getRow(0);// 得到标题的内容对象。int colNum = row.getPhysicalNumberOfCells();// 得到每行的列数。excleTitle = new String[colNum];for (int i = 0; i < colNum; i++) {excleTitle[i] = getStringCellValue(row.getCell((short) i));}// 正文内容应该从第二行开始,第一行为表头的标题for (int i = 1; i <= rowNum; i++) {row = sheet.getRow(i);int j = 0;while (j < colNum) {String v = "";if (j + 1 == colNum) {String vs = getStringCellValue(row.getCell((short) j)).trim();if (vs.indexOf(".") > -1) {if (isNum(vs)) { // 是否是数字if (vs.endsWith("0")) {v = vs.substring(0, vs.indexOf("."));}} else {v = vs.trim();}} else {v = vs.trim();}excelStr += v;} else {String vs = getStringCellValue(row.getCell((short) j)).trim()+ "&";if (vs.indexOf(".") > -1) {if (isNum(vs)) { // 是否是数字if (vs.endsWith("0")) { // 处理用poi读取excel整数后面加.0的格式化v = vs.substring(0, vs.indexOf("."));}} else {v = vs.trim();}} else {v = vs.trim();}excelStr += v;}j++;}String excelstrArray[] = excelStr.split("&", -1); // 每行数据Map<String, String> params = new LinkedHashMap<String, String>();for (int k = 0; k < excelstrArray.length; k++) {params.put(excleTitle[k], excelstrArray[k]);}JSONObject jsonObject = JSONObject.fromObject(params);contentJson.put(i, jsonObject);// content.put(i, excelStr);excelStr = "";}} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();} finally {try {if (input != null) {input.close();}} catch (IOException e) {e.printStackTrace();}}return contentJson;}/** * 获取单元格数据内容为字符串类型的数据 * @param cell * @return */private static String getStringCellValue(HSSFCell cell) {String strCell = "";switch (cell.getCellType()) {case HSSFCell.CELL_TYPE_STRING:strCell = cell.getStringCellValue();break;case HSSFCell.CELL_TYPE_NUMERIC:strCell = String.valueOf(cell.getNumericCellValue());break;case HSSFCell.CELL_TYPE_BOOLEAN:strCell = String.valueOf(cell.getBooleanCellValue());break;case HSSFCell.CELL_TYPE_BLANK:strCell = "";break;default:strCell = "";break;}if (strCell.equals("") || strCell == null) {return "";}if (cell == null) {return "";}return strCell;}/** * 获取单元格数据内容为日期类型的数据 * @param cell * @return */private static String getDateCellValue(HSSFCell cell) {String result = "";try {int cellType = cell.getCellType();if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {Date date = cell.getDateCellValue();result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)+ "-" + date.getDate();} else if (cellType == HSSFCell.CELL_TYPE_STRING) {String date = getStringCellValue(cell);result = date.replaceAll("[年月]", "-").replace("日", "").trim();} else if (cellType == HSSFCell.CELL_TYPE_BLANK) {result = "";}} catch (Exception e) {System.out.println("日期格式不正确!");e.printStackTrace();}return result;}/** * 根据byte数组，生成文件 */public static void getFile(byte[] bfile, String filePath, String fileName) {BufferedOutputStream bos = null;FileOutputStream fos = null;File file = null;try {File dir = new File(filePath);if (!dir.exists() && dir.isDirectory()) {// 判断文件目录是否存在dir.mkdirs();}file = new File(filePath + "\" + fileName);fos = new FileOutputStream(file);bos = new BufferedOutputStream(fos);bos.write(bfile);} catch (Exception e) {e.printStackTrace();} finally {if (bos != null) {try {bos.close();} catch (IOException e1) {e1.printStackTrace();}}if (fos != null) {try {fos.close();} catch (IOException e1) {e1.printStackTrace();}}}}// 从byte[]转filepublic static File getFileFromBytes(byte[] b, String outputFile) {BufferedOutputStream stream = null;File file = null;try {file = new File(outputFile);if (!file.exists() && file.isDirectory()) {// 判断文件目录是否存在file.mkdirs();//mkdirs() 可以在不存在的目录中创建文件夹。诸如：a\\b,既可以创建多级目录。}FileOutputStream fstream = new FileOutputStream(file);stream = new BufferedOutputStream(fstream);stream.write(b);} catch (Exception e) {e.printStackTrace();} finally {if (stream != null) {try {stream.close();} catch (IOException e1) {e1.printStackTrace();}}}return file;}/** * 读取Office 2007 excel * */private static Map<Integer, JSONObject> read2007Excels(File file)throws IOException {Map<Integer, JSONObject> contentJson = new LinkedHashMap<Integer, JSONObject>();// 构造 XSSFWorkbook 对象，strPath 传入文件路径XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));// 读取第一章表格内容XSSFSheet sheet = xwb.getSheetAt(0);Object value = null;XSSFRow row = null;XSSFCell cell = null;XSSFRow headerrow = sheet.getRow(0); // 表头 得到标题的内容对象int colNum = headerrow.getPhysicalNumberOfCells();// 得到每行的列数。excleTitle = new String[colNum];for (int i = 0; i < colNum; i++) {excleTitle[i] = getStringCellValue(headerrow.getCell((short) i));}// System.out.println(sheet.getPhysicalNumberOfRows());// 循环内容项 不循环标题 所以+1for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getPhysicalNumberOfRows(); i++) {row = sheet.getRow(i);if (row == null) {continue;}List<String> linked = new LinkedList<String>();for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {cell = row.getCell(j);if (null != cell) {value = getStringCellValue(cell);}linked.add(StringUtil.stringIsNull(value));}Map<String, String> params = new LinkedHashMap<String, String>();for (int j = 0; j < linked.size(); j++) {params.put(excleTitle[j], linked.get(j));}JSONObject jsonObject = JSONObject.fromObject(params);contentJson.put(i, jsonObject);}return contentJson;}/** * 获取单元格数据内容为字符串类型的数据 * * @param cell * @return */private static String getStringCellValue(XSSFCell cell) {String strCell = "";switch (cell.getCellType()) {case HSSFCell.CELL_TYPE_STRING:strCell = cell.getStringCellValue();break;case HSSFCell.CELL_TYPE_NUMERIC:strCell = String.valueOf(cell.getNumericCellValue());break;case HSSFCell.CELL_TYPE_BOOLEAN:strCell = String.valueOf(cell.getBooleanCellValue());break;case HSSFCell.CELL_TYPE_BLANK:strCell = "";break;default:strCell = "";break;}if (strCell.equals("") || strCell == null) {return "";}if (cell == null) {return "";}return strCell;}/** * 获得指定文件的byte数组 */public static byte[] getBytes(String filePath) {byte[] buffer = null;try {File file = new File(filePath);FileInputStream fis = new FileInputStream(file);ByteArrayOutputStream bos = new ByteArrayOutputStream(1000);byte[] b = new byte[1000];int n;while ((n = fis.read(b)) != -1) {bos.write(b, 0, n);}fis.close();bos.close();buffer = bos.toByteArray();} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}return buffer;}Map<Integer, JSONObject> content = new LinkedHashMap<Integer, JSONObject>();if (version.equals("2003")) {content = ExcelUtil.readExcelContent(fileinput);} else if (version.equals("2007")) {content = ExcelUtil.read2007Excels(fileinput);}for (Map.Entry<Integer, JSONObject> entry : content.entrySet()) {System.out.println(entry.getValue().get("excel表头的名称")));//这个循环里可以把这些值 组装到一个 po里//这样取值更加方便//比如 Po p=new Po(); po.setEntityName(entry.getValue().get("excel表头的名称")));//....具体怎么扩展 更加适合你的项目 当然要你们自己思考了 呵呵...}