1、最近在一个项目中,需要将省市数据导入到表,省市数据存于excel文件中,总共有4万多条数据;
2、开始准备用数据库工具oracle developer导入,但是数据量过大,导致数据库工具卡死;
3、所以想着先将excel数据转换成sql语句然后再insert到表;
4、excel为xls格式,故用poi来解析;
5、如何将excel大量数据导入到oracle数据库表,如果有其他好的方法,望各位大神赐教!
代码如下:
poi-3.9.jar
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
* 将excel数据读出并生产sql语句
* @author zhouy
*
*/
public class ReadAsSqlTxt {
private static final String EXCEL_PATH = "C:\\Users\\123\\Desktop\\B1012-地区码数字字典_20170614.xls";
private static final String TXT_PATH = "C:\\Users\\123\\Desktop\\1234.txt";
private static final String CHARSET = "utf-8";
public static void main(String[] args) {
ReadAsSqlTxt readAsSqlTxt = new ReadAsSqlTxt();
try {
readAsSqlTxt.doBusiness(EXCEL_PATH, TXT_PATH);
} catch (Exception e) {
e.printStackTrace();
System.out.println("======处理异常!");
}
}
/**
* 内部类需要外部类来调用,而不能再main方法中直接调用
* @param excelPath
* @param txtPath
* @throws Exception
*/
private void doBusiness(String excelPath, String txtPath) throws Exception{
ReadExcelToTxt readExcelToTxt = new ReadExcelToTxt();
readExcelToTxt.todo(excelPath, txtPath);
}
class ReadExcelToTxt{
private void todo(String excelPath, String txtPath) throws Exception{
long beginTime = System.currentTimeMillis();
// 目标文件流
FileOutputStream fos = new FileOutputStream(new File(txtPath));
// 源文件流
FileInputStream fis = new FileInputStream(excelPath);
// 生成一个excel文件对象
HSSFWorkbook workBook = new HSSFWorkbook(fis);
// 获取第一个sheet
HSSFSheet sheet = workBook.getSheetAt(0);
// 解析sheet
// getLastRowNum没有加上第一行,得加上1
int rowLength = sheet.getLastRowNum()+1;
System.out.println("======待解析行数:"+rowLength); // 46950
for(int i=1; i<rowLength; i++){
// 去掉了第一行,第一行为表头
HSSFRow row = sheet.getRow(i);
HSSFCell areaShorNameCell = row.getCell(0); // 中文简称
HSSFCell areaCodeCell = row.getCell(1); // 地区码
HSSFCell areaNameCell = row.getCell(2); // 区域名称
HSSFCell parentCodeCell = row.getCell(3); // 上级区域码
String sql = "insert into wechat_pingan_area_info(area_name,area_code,parent_code,area_short_name) "
+ "values('"+getValue(areaNameCell)+"',"
+ "'"+getValue(areaCodeCell)+"',"
+ "'"+getValue(parentCodeCell)+"',"
+ "'"+getValue(areaShorNameCell)+"');";
fos.write(sql.getBytes(CHARSET));
fos.write("\r\n".getBytes(CHARSET));
}
fos.close();
long endTime = System.currentTimeMillis();
System.out.println("======处理成功!供耗时:"+(endTime-beginTime)/1000+"秒");
}
/**
* 获取相应类型值
* @param cell
* @return
*/
@SuppressWarnings("static-access")
private String getValue(HSSFCell cell){
if(cell.getCellType()==cell.CELL_TYPE_BOOLEAN){
return String.valueOf(cell.getBooleanCellValue());
}else if(cell.getCellType()==cell.CELL_TYPE_NUMERIC){
return String.valueOf((int)cell.getNumericCellValue());
}else if(cell.getCellType()==cell.CELL_TYPE_STRING){
return String.valueOf(cell.getStringCellValue());
}else if(cell.getCellType()==cell.CELL_TYPE_BLANK){
return "";
}else{
return "";
}
}
}
}