Excel万能读取Util类初级版 ExcelUtil excel读取 Excel万能

今天在构思一个能够读取任何Excel的java类，而不用做任何改变！只需要传入Model即可，今天写了一下，明天继续！

调用非常方便,简简单单两行代码就能读取你要的Excel数据，兼容性好03,07都兼容！我会写调用步骤，下面！

今天完成了，现在发布出来造福程序猿们！主要代码如下，需要源码的请到这里下载，想拿来直接用的，我也挺辛苦的就3分，下载的时候！

调用演示

Excel格式如下

下面是主程序

package com.lkx.excel;import java.io.FileInputStream;import java.io.InputStream;import java.lang.reflect.Method;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.HashMap;import java.util.Iterator;import java.util.List;import java.util.Map;import java.util.Set;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.DateUtil;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;/** *  * ClassName: ExcelUtil Function: Excel万能读取公共类兼容03/07版Excel Reason: date: * 2016年11月23日 上午11:06:28 *  * @author likaixuan * @version v1.0 * @since JDK 1.7 */public class ExcelUtil {public static void main(String[] args) {}public static Map<String, String> getMap(String keyValue) {Map<String, String> map = new HashMap<String, String>();if (keyValue != null) {String[] str = keyValue.split(",");for (int i = 0; i < str.length; i++) {String[] str2 = str[i].split(":");map.put(str2[0], str2[1]);}}return map;}/** * readXls:(根据传进来的map集合读取Excel) 传进来两个参数，第一个Map * <String,String>类型，第二个要反射的类的具体路径) * 其中Map第一个为Excel列的名称，第二个为反射类的属性要求首字母大写，以便反射 *  * @author likaixuan * @param filePath * @param map * @param classPath * @return * @throws Exception * @since JDK 1.7 */public static List<Object> readXls(String filePath, Map map,String classPath) throws Exception {Set keySet = map.keySet();// 返回键的集合/** 反射用 **/Class<?> demo = null;Object obj = null;/** 反射用 **/List<Object> list = new ArrayList<Object>();demo = Class.forName(classPath);String fileType = filePath.substring(filePath.lastIndexOf(".") + 1,filePath.length());InputStream is = new FileInputStream(filePath);Workbook wb = null;if (fileType.equals("xls")) {wb = new HSSFWorkbook(is);} else if (fileType.equals("xlsx")) {wb = new XSSFWorkbook(is);} else {System.out.println("[ExcelUtil Error Message]:您输入的excel格式不正确");}lableBreak: for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {// 获取每个Sheet表int rowNum_x = -1;// 记录第x行为表头Map<String, Integer> cellmap = new HashMap<String, Integer>();// 存放每一个field字段对应所在的列的序号Sheet hssfSheet = wb.getSheetAt(sheetNum);// 循环行Rowfor (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {Row hssfRow = hssfSheet.getRow(rowNum);if (hssfRow == null) {continue;}boolean flag = false;for (int i = 0; i < hssfRow.getLastCellNum(); i++) {if (hssfRow.getCell(i) != null&& !("").equals(hssfRow.getCell(i).toString().trim())) {flag = true;}}if (!flag)continue;if (rowNum_x == -1) {// 循环列Cellfor (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {Cell hssfCell = hssfRow.getCell(cellNum);if (hssfCell == null) {continue;}String tempCellValue = "";tempCellValue = hssfSheet.getRow(rowNum).getCell(cellNum).getStringCellValue().trim();Iterator it = keySet.iterator();while (it.hasNext()) {Object key = it.next();if (!("").equals(tempCellValue)&& key.equals(tempCellValue)) {rowNum_x = rowNum;cellmap.put(map.get(key).toString(), cellNum);}}if (rowNum_x == -1) {System.out.println("[ExcelUtil Error Message]:没有找到对应的字段或者对应字段行上面含有不为空白的行字段");break lableBreak;}}} else {obj = demo.newInstance();Iterator it = keySet.iterator();while (it.hasNext()) {Object key = it.next();Integer cellNum_x = cellmap.get(map.get(key).toString());if (cellNum_x == null|| hssfRow.getCell(cellNum_x) == null) {continue;}String attr = map.get(key).toString();// 得到属性Class<?> attrType = demo.getField(attr).getType();Cell cell = hssfRow.getCell(cellNum_x);getValue(cell, obj, attr,attrType);}list.add(obj);}}}// wb.close();return list;}/** * setter:(反射的set方法给属性赋值) *  * @author likaixuan * @param obj *            具体的类 * @param att *            类的属性@注意首字母记得大写 * @param value *            赋予属性的值 * @param type *            属性是哪种类型 比如:String double boolean等类型 * @since JDK 1.7 */public static void setter(Object obj, String att, Object value,Class<?> type) {try {Method method = obj.getClass().getMethod("set" + toUpperCaseFirstOne(att), type);method.invoke(obj, value);} catch (Exception e) {e.printStackTrace();}}/** * getValue:(得到Excel列的值) *  * @author likaixuan * @param hssfCell * @return * @since JDK 1.7 */public static void getValue(Cell cell, Object obj, String attr,Class attrType) {Object val = null;if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {val = cell.getBooleanCellValue();} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {if (DateUtil.isCellDateFormatted(cell)) {SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");try {if(attrType==String.class){val = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));}else{val = dateConvertFormat(sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue())));}} catch (ParseException e) {System.out.println("日期格式转换错误");}} else {if(attrType==String.class){val = Double.toString(cell.getNumericCellValue());}else if(attrType==Float.class){val = (float)cell.getNumericCellValue();}else if(attrType==int.class){val = (int)cell.getNumericCellValue();}else{val = cell.getNumericCellValue();}}} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {val = cell.getStringCellValue();}setter(obj, attr, val, attrType);}/** *  首字母转小写 * @param s * @return */public static String toLowerCaseFirstOne(String s) {if (Character.isLowerCase(s.charAt(0)))return s;elsereturn (new StringBuilder()).append(Character.toLowerCase(s.charAt(0))).append(s.substring(1)).toString();}/** *  首字母转大写 * @param s * @return */public static String toUpperCaseFirstOne(String s) {if (Character.isUpperCase(s.charAt(0)))return s;elsereturn (new StringBuilder()).append(Character.toUpperCase(s.charAt(0))).append(s.substring(1)).toString();}/** * String类型日期转为Date类型 * @param dateStr * @return * @throws ParseException  * @throws Exception */public static Date dateConvertFormat(String dateStr) throws ParseException{Date date = new Date();SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");date = format.parse(dateStr);return date ;}}