poi做的Excel导入数据库表工具

package com.sinosoft.common; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.sql.SQLException; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.Date; import java.util.Iterator; import java.util.Vector; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFDateUtil; 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.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; 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 程志平 * */ public class ReadExcelToDBTableTool { public void readExcel2007(DBModel dbConn, String filePath, String tableName) throws Exception { try { // InputStream inp = new FileInputStream(filePath); // Workbook wb = WorkbookFactory.create(inp); XSSFWorkbook wb = new XSSFWorkbook(filePath); int sheetSize = wb.getNumberOfSheets(); for (int i = 0; i < sheetSize; i++) { XSSFSheet sheet = wb.getSheetAt(i); // Sheet sheet = wb.getSheetAt(i); for (Iterator rit = sheet.rowIterator(); rit.hasNext();) { // 迭代行 XSSFRow row = (XSSFRow) rit.next(); // 迭代单元格 Vector datas = new Vector(); for (Iterator cit = row.cellIterator(); cit.hasNext();) { // 定义集合datas用于存Excel中一个行的数据 XSSFCell cell = (XSSFCell) cit.next(); String value = getValue2007(cell); // 注意行和列是基于0索引的 System.out.print(cell.getRowIndex() + ":" + cell.getColumnIndex() + " "); datas.add(value); // 开始操作单元格 } // 向表中插入数据 DBFactory.insertData(dbConn, tableName, datas); } } } catch (FileNotFoundException e) { e.printStackTrace(); throw e; } catch (InvalidFormatException e) { e.printStackTrace(); throw e; } catch (IOException e) { e.printStackTrace(); throw e; } } /** * 将Excel数据导入到表中 * * @param filePath * Excel路径 * @param tableName * 表名 */ public void readExcel(DBModel dbConn, String filePath, String tableName) throws Exception { try { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream( filePath)); // 创建工作簿 HSSFWorkbook workBook = new HSSFWorkbook(fs); System.out.println("工作表个数 :" + workBook.getNumberOfSheets()); for (int i = 0; i < workBook.getNumberOfSheets(); i++) { // 创建工作表 HSSFSheet sheet = workBook.getSheetAt(i); int rows = sheet.getPhysicalNumberOfRows(); // 获得行数 if (rows > 0) { sheet.getMargin(HSSFSheet.TopMargin); for (int r = 0; r < rows; r++) { // 行循环 HSSFRow row = sheet.getRow(r); if (row != null && r != 0) {// 不取第一行 int cells = row.getLastCellNum();// 获得列数 // 定义集合datas用于存Excel中一个行的数据 Vector datas = new Vector(); for (short c = 0; c < cells; c++) { // 列循环 HSSFCell cell = row.getCell(c); if (cell != null) { String value = getValue(cell); System.out.println("第" + r + "行 " + "第" + c + "列：" + value); datas.add(value); } } // 向表中插入数据 DBFactory.insertData(dbConn, tableName, datas); } } } else { break; } } } catch (Exception ex) { ex.printStackTrace(); System.out.println("readExcel 方法异常：" + ex); throw ex; } } public String getValue2007(XSSFCell cell) { String value = ""; switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: System.out.println(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { System.out.println(cell.getDateCellValue()); java.util.Date date = cell.getDateCellValue(); SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd"); value = format.format(date); } else { System.out.println(cell.getNumericCellValue()); value = String.valueOf(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: System.out.println(cell.getBooleanCellValue()); value = " " + cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_FORMULA: System.out.println(cell.getCellFormula()); value = cell.getCellFormula(); break; default: System.out.println(); } return value; } /** * 获取Excel中某个单元格的值 * * @param cell * @return * @throws ParseException */ public String getValue(HSSFCell cell) throws ParseException { String value = ""; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: // 数值型 if (HSSFDateUtil.isCellDateFormatted(cell)) { // 如果是date类型则 ，获取该cell的date值 value = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()) .toString(); java.util.Date date1 = new Date(value); SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd"); value = format.format(date1); } else {// 纯数字 value = String.valueOf(cell.getNumericCellValue()); } break; /* 此行表示单元格的内容为string类型 */ case HSSFCell.CELL_TYPE_STRING: // 字符串型 value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_FORMULA:// 公式型 // 读公式计算值 value = String.valueOf(cell.getNumericCellValue()); if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串 value = cell.getStringCellValue().toString(); } cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_BOOLEAN:// 布尔 value = " " + cell.getBooleanCellValue(); break; /* 此行表示该单元格值为空 */ case HSSFCell.CELL_TYPE_BLANK: // 空值 value = ""; break; case HSSFCell.CELL_TYPE_ERROR: // 故障 value = ""; break; default: value = cell.getStringCellValue().toString(); } return value; } @SuppressWarnings("deprecation") public static void main(String[] args) throws SQLException { // ReadExcelToDBTableTool tool = new ReadExcelToDBTableTool(); // tool.readExcel("E:\\javaDemo\\person.xls","person"); // tool.getColumns("person"); // 2009/9/12 String dateValue = "Sat Jun 18 00:00:00 CST 2011"; java.util.Date date1 = new Date(dateValue); // java.util.Date date = format.parse(value) ; System.out.println(new SimpleDateFormat("yyyy-MM-dd").format(date1)); } }