封装的Excel POI读取方法，包含文件及示范代码

Excel POI读取封装(文件+示范代码) package org.excel.service; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileWriter; import java.io.IOException; import java.lang.reflect.Field; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.text.DateFormat; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.*; import javax.jws.WebService; import org.apache.poi.hssf.usermodel.*; import org.excel.data.DataType; import org.excel.data.DealForeign; import org.excel.data.ExcelImport; import org.excel.data.impl.StInStatusImpl; import org.excel.data.impl.StuClassImportImpl; import org.excel.data.impl.StuEducationImpl; import org.excel.data.impl.StuImprotDataImpl; import org.excel.data.impl.StuInClassImportImpl; import org.excel.data.impl.StuWorkStatusImpl; import org.excel.manager.Util; import org.excel.model.ExcelMap; import org.excel.xml.ExcelXmlParse; import net.sourceforge.jtds.jdbcx.JtdsDataSource; @WebService public class ExcelServiceImpl implements IExcelService { String tableName = ""; public static Integer suc = 0; public static Integer fail = 0; StringBuilder insert = new StringBuilder( " insert into {tableName} {column} values {values}"); /** 读取Excel表格数据 */ public List> readExcel(String excelName, Integer sheetIndex, String startPoint, String endPoint) throws Exception { FileInputStream inputStream = null; inputStream = new FileInputStream(excelName); HSSFWorkbook workbook = new HSSFWorkbook(inputStream); HSSFSheet sheet = workbook.getSheetAt(sheetIndex); String[] sc = null;// 开始坐标 String[] ec = null;// 结束坐标 int startRow = 0;// 默认开始行数 int endRoe = sheet.getPhysicalNumberOfRows();// 默认结束行 int startLine = 0;// 默认开始列 int endLine = 0;// 结束列 if (startPoint != null && endPoint != null) { sc = startPoint.split(","); ec = endPoint.split(","); startRow = Integer.parseInt(sc[1]) - 1;// 设置用户指定的开始行 endRoe = Integer.parseInt(ec[1]);// 设置用户指定的结束行 startLine = f(sc[0]) - 1;// 设置用户指定的开始列 endLine = f(ec[0]);// 设置用户指定的结束列 } Map headMap = new HashMap(0);// 存放表头 List> dataList = new ArrayList>( 0);// 存放其他数据集合 for (int i = startRow; i < endRoe; i++) {// 循环行 // ;i的范围是xls坐标中的数字（A‘1’，C‘10’） HSSFRow row = sheet.getRow(i); if (row == null) continue; HashMap dataMap = null; if (row != null) { dataMap = new HashMap(0); if (startPoint == null && endPoint == null) endLine = row.getLastCellNum(); for (int j = startLine; j < endLine; j++) {// 循环列;j的范围xls坐标中的字母（‘A’1，‘C’10） HSSFCell cell = row.getCell(j);// 单元格 if (i == startRow && cell != null) {// 遍历表头 headMap.put(j, cell);// map的key是表头的列号，value是单元格里面的 } else { if (row.getCell(startLine) == null || row.getCell(startLine).toString().trim() .equals("")) { break; } else { if (cell == null || cell.toString().trim().equals("")) dataMap.put(headMap.get(j).toString(), ""); else { if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { java.util.Date date = null; if (HSSFDateUtil.isCellDateFormatted(cell)) { date = cell.getDateCellValue(); DateFormat formater = new SimpleDateFormat( "yyyy-MM-dd"); dataMap.put(headMap.get(j).toString(), formater.format(date) .toString()); continue; } Double d = new Double( cell.getNumericCellValue()); dataMap.put(headMap.get(j).toString(), String.valueOf(d.longValue())); continue; } dataMap.put(headMap.get(j).toString(), cell.toString()); } } } } dataList.add(dataMap); } } return dataList; } // 将从Excel表格读取到的数据与配置文件筛选成可以插入到数据库的数据 public List> parseExcelData( List> excelDataMap, Map> dbfield) { List> list = new ArrayList>(); HashMap datamap = null; DealForeign deal = null; // 存放带验证信息的属性 Map iscompare = new HashMap(); for (Map map : excelDataMap) {// 得到当前一行数据对应的map集合 if (null == map || map.size() == 0) continue; datamap = new HashMap(); datamap.clear(); for (String datakey : dbfield.keySet()) {// 循环该map的key集合 for (ExcelMap e : dbfield.get(datakey)) { if (map.containsKey(e.getName())) { if (e.isCompare() && !iscompare.containsKey(e.getName())) { // 将带有验证信息的字段放到该集合中 iscompare.put(e.getName(), e.getColumn()); // 将带有验证信息的字段加"$c"，方便后面验证调用 e.setColumn(e.getColumn() + "$c"); } String s=e.getName(); String excelvalue = map.get(e.getName()).trim(); Object obj=null; DataType stued=new StuEducationImpl(); if(e.isForeign()){ String[] data=(String[])stued.dealDataType(e,excelvalue); obj=convertDataType(data[1],data[0].toString().getClass()); datamap.put(e.getColumn(), obj); continue; } // 类型匹配 obj = convertDataType(excelvalue, e.getJavaType().getClass()); // 将数据的英文字段作为key，对映值转换为相应类型 datamap.put(e.getColumn(), obj); } } list.add(datamap); } } return list; } /** 将参数转换为数据库数据 */ public Object convertDataType(String src, @SuppressWarnings("rawtypes") Class classzz) { if (null == classzz) throw new RuntimeException("参数不对…………"); Object obj = null; if (classzz == Integer.class) { if (src.contains(".")) { src = src.substring(0, src.indexOf(".")); obj = Integer.valueOf(src); } } if (classzz == Double.class) obj = Double.valueOf(src); if (classzz == String.class || classzz == char.class) obj = String.valueOf(src); if (classzz == Float.class) obj = Float.valueOf(src); if (classzz == Boolean.class) obj = Boolean.valueOf(src); if (classzz == Long.class) obj = Long.valueOf(src); if (classzz == Date.class) { if (src.contains(":")) { src = src.subSequence(0, src.indexOf(":")).toString(); } obj = java.sql.Date.valueOf(src); } return obj; } // 计算坐标 private int ch2int(char ch) { if (ch >= 'a' && ch <= 'z') return ch - 'a' + 1; if (ch >= 'A' && ch <= 'Z') return ch - 'A' + 1; throw new IllegalArgumentException(); } // 计算坐标 private int f(String str) { char[] ch = str.toCharArray(); int ret = 0; for (int i = 0; i < ch.length; i++) { ret *= 26; ret += this.ch2int(ch[i]); } return ret; } static final Config config = new Config(); /** 关闭数据库连接 */ public void closeResource(ResultSet rs, Statement st, Connection c) { try { if (rs != null) rs.close(); if (st != null) st.close(); if (c != null) c.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } /** 关闭数据库连接 */ public void closeResource(ResultSet rs) { Statement st = null; Connection c = null; try { if (rs != null) { st = rs.getStatement(); } if (st != null) { c = st.getConnection(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } if (st != null) { st.close(); } if (c != null) { c.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } /** 获得数据库连接 */ public Connection getConnection() throws SQLException { try { // Class.forName("com.mysql.jdbc.Driver"); // // Class.forName(config.getDriver()); // String url = "jdbc:mysql://192.168.1.109:3306/em?user=root&password=root"; // // 建立连接 // return DriverManager.getConnection(url); Class.forName(config.getDriver()); return DriverManager.getConnection(config.getUrl(), config.getUser(), config.getPassword()); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; } /** 执行增、删、改 */ public int executeUpdate(String sql, Object... params) { Connection con = null; PreparedStatement ps = null; int ret = 0; try { con = getConnection(); ps = con.prepareStatement(sql); if (params != null && params.length > 0) { for (int i = 0; i < params.length; i++) { if (null == params[i]) continue; ps.setObject(i + 1, params[i]); } } ret = ps.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } System.out.println(sql); return ret; } /** 查询 */ public ResultSet executeQuery(String sql, Object... params) { Connection c = null; PreparedStatement pst = null; ResultSet rs = null; try { c = getConnection(); pst = c.prepareStatement(sql); if (params != null && params.length > 0) { for (int i = 0; i < params.length; i++) { if (null == params[i]) continue; pst.setObject(i + 1, params[i]); } } rs = pst.executeQuery(); } catch (SQLException e) { e.printStackTrace(); } System.out.println(sql); return rs; } public void importData(String excelName, int sheetIndex, String startPoint, String endPoint, Map> data, String filePath) { List> dataList = new ArrayList>(); try { // 开始解析Excel表格 List> excelmap = readExcel( excelName, sheetIndex, startPoint,endPoint); ExcelXmlParse parse = new ExcelXmlParse(); // 将Excel表格数据转换为可以插入数据库的数据 // Map> data= dataList = parseExcelData(excelmap,data); if ("".equals(filePath) || null == filePath) { write(filePath, "本次导入使用默认路径: " + filePath.toString() + " " + new Util().getNowTime() + "\r\n\r\n\r\n\r\n"); filePath.toString(); } else { write(filePath, "本次导入路径: " + filePath.toString() + " " + new Util().getNowTime() + "\r\n\r\n\r\n\r\n"); } for (Map map : dataList) { if (map.size() == 0) continue; // 学生插入 ExcelImport stuImport = new StuImprotDataImpl(); // 班级插入 ExcelImport stuClass = new StuClassImportImpl(); // 班级学生对映关系插入 ExcelImport stuInClass = new StuInClassImportImpl(); try { int s = stuImport.importData(map,filePath); if (s > 0) { int stu = stuClass.importData(map, filePath); if (stu != 0) { int in = stuInClass.importData(map,filePath); suc++; } else{ fail++; } } else{ fail++; } } catch (Exception e) { write(filePath, e.getMessage()); fail++; continue; } } write(filePath, "成功="+suc+",失败"+fail); fail= suc=0; } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } // 判断该条数据是否在数据中重复 @SuppressWarnings("unused") public boolean isNull(String sql, Object... params) { ResultSet rs = executeQuery(sql, params); try { if (rs.next()) return false; else return true; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return true; } // 判断当前列在数据库是否是唯一值 public boolean isUniqueValue(String checkColumn) { return checkColumn .substring(checkColumn.length() - 2, checkColumn.length()) .toString().equals("$c"); } /**根据excelName得到所有的工作部*/ public Map getSheetIndex(String excelName) throws IOException { FileInputStream inputStream = null; inputStream = new FileInputStream(excelName); HSSFWorkbook workbook = new HSSFWorkbook(inputStream); int index = workbook.getNumberOfSheets(); Map sheetMap = new HashMap(); for (int i = 0; i < index; i++) { HSSFSheet sheet = workbook.getSheetAt(i); sheetMap.put(sheet.getSheetName(), i); } return sheetMap; } /** * 日志记录 * * @param savaAdd * 保存日志路径，可以不选使用默认路径 * @param sql * 执行的sql语句， * @param success * 成功插入条数 * @param fail * 失败条数 * @param params * 插入数据具体值 * */ @SuppressWarnings("unused") public void logger(String filePath, String sql, Object... params) { String msg = ""; if ("".equals(sql) || null == sql || params.length == 0 || null == filePath) { msg = "错误插入，无数据…………"; write(filePath, msg); } String content = ""; for (int i = 0; i < params.length; i++) { if (null != params[i]) content += params[i].toString() + ","; } content = content.substring(0, content.lastIndexOf(",")); msg = "对映参数值: " + content; write(filePath, sql + "\r\n" + msg); } // 写入到文本中 public void write(String fileName, String content) { try { // 打开一个写文件器，构造函数中的第二个参数true表示以追加形式写文件 FileWriter writer = new FileWriter(fileName, true); writer.write(content + " \r\n"); writer.write("------------------------------------------------------------- \r\n"); writer.close(); } catch (IOException e) { e.printStackTrace(); } } public static void main(String[] args) throws IOException { new ExcelServiceImpl().importData("e:/testdata.xls", 0, "a,4", "o,32", new ExcelXmlParse().getExcelMap("excelMap.xml"), "e:/"+new Util().getLoggerName()+".txt"); // DealForeign d=new StInStatusImpl(); // System.out.println( new ExcelServiceImpl().dealWith(d, "新生入学")); } }