1 maven
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.4</version>
</dependency>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc14</artifactId>
<version>10.2.0.4.0</version>
</dependency>
2 code
package com.aaa.test; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.math.BigDecimal; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.List; import org.apache.commons.lang3.StringUtils; 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.ss.usermodel.WorkbookFactory; public class ReadExcel { /** * 读取Excel测试,兼容 Excel 2003/2007/2010 */ public static List excelToList(InputStream inputStream) { List<String[]> list = null; SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd"); try { Workbook workbook = WorkbookFactory.create(inputStream); // 这种方式 // Excel // 2003/2007/2010 // 都是可以处理的 int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量 // 遍历每个Sheet,暂时不用,只取第一个 // for (int s = 0; s < sheetCount; s++) { Sheet sheet = workbook.getSheetAt(0); int rowCount = sheet.getPhysicalNumberOfRows(); // 获取总行数 list = new ArrayList<String[]>(); int cellCount = 0; if (rowCount > 0) { cellCount = sheet.getRow(0).getPhysicalNumberOfCells(); // 获取总列数 } // 遍历每一行 for (int r = 0; r < rowCount; r++) { Row row = sheet.getRow(r); if(row == null) continue; // int cellCount = row.getLastCellNum(); // 获取总列数 String[] lineItem = new String[cellCount]; // 遍历每一列 for (int c = 0; c < cellCount; c++) { Cell cell = row.getCell(c); if (cell == null) { continue; } int cellType = cell.getCellType(); String cellValue = null; if(Cell.CELL_TYPE_STRING == cellType){ // 文本 cellValue = cell.getStringCellValue(); } else if(Cell.CELL_TYPE_NUMERIC == cellType){ if (DateUtil.isCellDateFormatted(cell)) { cellValue = fmt.format(cell.getDateCellValue()); // 日期型 } else { cellValue = String.valueOf(cell.getNumericCellValue()); // 数字 if (cellValue.endsWith(".0")) { cellValue = cellValue.substring(0, cellValue.length() - 2); } //防止科学计数法 BigDecimal bd = new BigDecimal(cellValue); cellValue = bd.toPlainString(); if (cellValue.endsWith(".0")) { cellValue = cellValue.substring(0, cellValue.length() - 2); } } } else if(Cell.CELL_TYPE_BOOLEAN == cellType){ cellValue = String.valueOf(cell.getBooleanCellValue()); } else if(Cell.CELL_TYPE_BLANK == cellType){ cellValue = cell.getStringCellValue(); } else { cellValue = "错误"; } // System.out.print(cellValue + " "); lineItem[c] = cellValue; } // System.out.println(); boolean addFlag = false; for (int i = 0; i < lineItem.length; i++) { if(StringUtils.isNotBlank(lineItem[i])) { addFlag = true; } } if(addFlag) list.add(lineItem); // } } } catch (Exception e) { e.printStackTrace(); } return list; } public static List excelToList(String path) { List<String[]> list = null; FileInputStream in = null; try { File excelFile = new File(path); in = new FileInputStream(excelFile); list = excelToList(in); } catch (Exception e) { e.printStackTrace(); } finally { if (in != null) { try { in.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } return list; } }
package com.zte.test; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.math.BigDecimal; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.List; import org.apache.commons.lang3.StringUtils; 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.ss.usermodel.WorkbookFactory; public class ReadExcel { /** * 读取Excel测试,兼容 Excel 2003/2007/2010 */ public static List excelToList(InputStream inputStream) { List<String[]> list = null; SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd"); try { Workbook workbook = WorkbookFactory.create(inputStream); // 这种方式 // Excel // 2003/2007/2010 // 都是可以处理的 int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量 // 遍历每个Sheet,暂时不用,只取第一个 // for (int s = 0; s < sheetCount; s++) { Sheet sheet = workbook.getSheetAt(0); int rowCount = sheet.getPhysicalNumberOfRows(); // 获取总行数 list = new ArrayList<String[]>(); int cellCount = 0; if (rowCount > 0) { cellCount = sheet.getRow(0).getPhysicalNumberOfCells(); // 获取总列数 } // 遍历每一行 for (int r = 0; r < rowCount; r++) { Row row = sheet.getRow(r); if(row == null) continue; // int cellCount = row.getLastCellNum(); // 获取总列数 String[] lineItem = new String[cellCount]; // 遍历每一列 for (int c = 0; c < cellCount; c++) { Cell cell = row.getCell(c); if (cell == null) { continue; } int cellType = cell.getCellType(); String cellValue = null; if(Cell.CELL_TYPE_STRING == cellType){ // 文本 cellValue = cell.getStringCellValue(); } else if(Cell.CELL_TYPE_NUMERIC == cellType){ if (DateUtil.isCellDateFormatted(cell)) { cellValue = fmt.format(cell.getDateCellValue()); // 日期型 } else { cellValue = String.valueOf(cell.getNumericCellValue()); // 数字 if (cellValue.endsWith(".0")) { cellValue = cellValue.substring(0, cellValue.length() - 2); } //防止科学计数法 BigDecimal bd = new BigDecimal(cellValue); cellValue = bd.toPlainString(); if (cellValue.endsWith(".0")) { cellValue = cellValue.substring(0, cellValue.length() - 2); } } } else if(Cell.CELL_TYPE_BOOLEAN == cellType){ cellValue = String.valueOf(cell.getBooleanCellValue()); } else if(Cell.CELL_TYPE_BLANK == cellType){ cellValue = cell.getStringCellValue(); } else { cellValue = "错误"; } // System.out.print(cellValue + " "); lineItem[c] = cellValue; } // System.out.println(); boolean addFlag = false; for (int i = 0; i < lineItem.length; i++) { if(StringUtils.isNotBlank(lineItem[i])) { addFlag = true; } } if(addFlag) list.add(lineItem); // } } } catch (Exception e) { e.printStackTrace(); } return list; } public static List excelToList(String path) { List<String[]> list = null; FileInputStream in = null; try { File excelFile = new File(path); in = new FileInputStream(excelFile); list = excelToList(in); } catch (Exception e) { e.printStackTrace(); } finally { if (in != null) { try { in.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } return list; } }
package com.aaa.test;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.List;
public class ExcelCityTest {
private static final String DRIVER = "oracle.jdbc.OracleDriver";
private static final String URL = "jdbc:oracle:thin:@ip:host:db";
private static final String USER = "user";
private static final String PWD = "pass";
public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, SQLException {
File file = new File("C:\\Users\\anyone\\Desktop\\city.xls");
FileInputStream inputStream = new FileInputStream(file);
List<String[]> dataList = ReadExcel.excelToList(inputStream);
if (inputStream != null) {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (dataList == null || dataList.size() <= 1) {
return;
}
Class.forName(DRIVER); // 加载驱动程序
Connection connection = DriverManager.getConnection(URL, USER, PWD);// 连接数据库
connection.setAutoCommit(false);//关闭自动提交
Statement statement = connection.createStatement();
for (int i = 1; i < dataList.size(); i++) {
String id = UUIDTool.getUUID();
StringBuffer sb = new StringBuffer("insert into FBP_REGION VALUES('");
sb.append(id);
sb.append("','1',sysdate,'1',sysdate,1,'");
sb.append(dataList.get(i)[4].replace("'","''") + "',");
//地区编码暂未提供为null
sb.append("null,'");
sb.append(dataList.get(i)[2].replace("'","''") +"','");
sb.append(dataList.get(i)[5].replace("'","''") +"','");
sb.append(dataList.get(i)[5].replace("'","''") +"','"+dataList.get(i)[6]+"')");//单引号转义
statement.addBatch(sb.toString());
}
long startTime = System.currentTimeMillis(); //获取开始时间
try {
statement.executeBatch();
statement.clearBatch();
connection.commit();
} catch (Exception e) {
statement.clearBatch();
connection.rollback();
e.printStackTrace();
}finally {
connection.setAutoCommit(true);//在把自动提交打开
statement.close();
connection.close();
long endTime = System.currentTimeMillis(); //获取结束时间
System.out.println("程序运行时间:" + (endTime - startTime) + "ms"); //输出程序运行时间
}
}
}