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 block
e.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;
}
}
使用JDBC读取本地的Excel文件并批量更新数据
最新推荐文章于 2024-07-26 10:05:08 发布