使用JDBC读取本地的Excel文件并批量更新数据

package com.jqgj.test;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.util.ArrayList;import java.util.List;import org.apache.commons.io.FilenameUtils;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellValue;import org.apache.poi.ss.usermodel.FormulaEvaluator;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;public class ImportUserNameTest {/** * Excel 2003 */private final static String XLS = "xls";/** * Excel 2007 */private final static String XLSX = "xlsx";/** * 分隔符 */private final static String SEPARATOR = "|";/** * 由Excel文件的Sheet导出至List *  * @param file * @param sheetNum * @return */public static List<Terminal> exportListFromExcel(File file, int sheetNum)throws IOException {return exportListFromExcel(new FileInputStream(file),FilenameUtils.getExtension(file.getName()), sheetNum);}/** * 由Excel流的Sheet导出至List *  * @param is * @param extensionName * @param sheetNum * @return * @throws IOException */public static List<Terminal> exportListFromExcel(InputStream is,String extensionName, int sheetNum) throws IOException {Workbook workbook = null;if (extensionName.toLowerCase().equals(XLS)) {workbook = new HSSFWorkbook(is);} else if (extensionName.toLowerCase().equals(XLSX)) {workbook = new XSSFWorkbook(is);}return exportListFromExcel(workbook, sheetNum);}/** * 由指定的Sheet导出至List *  * @param workbook * @param sheetNum * @return * @throws IOException */private static List<Terminal> exportListFromExcel(Workbook workbook,int sheetNum) {Sheet sheet = workbook.getSheetAt(sheetNum);// 解析公式结果FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();List<Terminal> list = new ArrayList<Terminal>();int minRowIx = sheet.getFirstRowNum();int maxRowIx = sheet.getLastRowNum();for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++) {Terminal terminal = new Terminal();Row row = sheet.getRow(rowIx);short minColIx = row.getFirstCellNum();short maxColIx = row.getLastCellNum();for (short colIx = minColIx; colIx <= maxColIx; colIx++) {Cell cell = row.getCell(new Integer(colIx));CellValue cellValue = evaluator.evaluate(cell);if (cellValue == null) {continue;}switch(colIx){case 0://如果手机号码是数字格式则转换（放开注释），如果是文本则不转换//DecimalFormat df = new DecimalFormat("#");//String sjhm = df.format(cellValue.getNumberValue());String sjhm = cellValue.getStringValue();terminal.setDwhm(sjhm);break;case 1:String name=cellValue.getStringValue();terminal.setZdmc(name);break;default:break;}}list.add(terminal);}return list;}/** * @param args */public static void main(String[] args) {String path = "f:\\telName.xlsx";try {List<Terminal> listS= exportListFromExcel(new File(path),0);/*for(int i=0;i<listS.size();i++){Terminal t = listS.get(i);System.out.println(t.getZdmc()+":"+t.getDwhm());}*/String result = exeBatchParparedSQL(listS);System.out.println("更新结果："+result);} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}/** * 批量更新 */public static String exeBatchParparedSQL(List<Terminal> listT){String result = "失败";String driver = "oracle.jdbc.OracleDriver";// 驱动字符串String url = "jdbc:oracle:thin:@IP:端口:数据库名字";// 链接字符串String user = "XXX";// 用户名String password = "XXX";// 密码Connection con = null;PreparedStatement pstm = null;try {Class.forName(driver);con = DriverManager.getConnection(url, user, password);String sql = "update T_userInfo set zdmc=? where dwhm=?";pstm = con.prepareStatement(sql);for(int i=0;i<listT.size();i++){Terminal t = listT.get(i);pstm.setString(1,t.getZdmc());pstm.setString(2,t.getDwhm());pstm.addBatch();  }int a[] = pstm.executeBatch();System.out.println("条数："+a.length);if (a.length>0) {con.commit();result = "成功";}} catch (Exception e) {try {con.rollback();} catch (Exception ec) {LogUtil.log.info("更新员工姓名回滚出错" + e.getMessage());}e.printStackTrace();} finally {PoolManager.closeConnection(con, pstm, null);}return result;}}