由于基于Oracle数据库上做开发，因此常常会需要把大量的Excel数据导入到Oracle数据库中，其实如果从事SqlServer数据库的开发，那么思路也是一样的，本文主要介绍如何导入Excel数据进入Oracle数据库的内容。
方法一：
1.准备数据:在excel中构造出需要的数据
2.将excel中的数据另存为文本文件(有制表符分隔的)
3.将新保存到文本文件中的数据导入到pl*sql中
在pl*sql中选择tools--text importer,在出现的窗口中选择Data from Textfile,然后再选择Open data file,
在弹出的文件选择框中选中保存有数据的文本文件，此时将会看到data from textfile中显示将要导入的数据
4.在configuration中进行如下配置
注:如果不将Name in header勾选上会导致字段名也当做记录被导入到数据库中，从而导致数据错误
5.点击data to oracle，选择将要导入数据的表，并在fields中将文本中的字段与表中的字段进行关联
6.点击import按钮进行导入
7.查看导入的数据
OK,至此数据导入成功。
方法二：
String fileName = "F:\\xx.xls"; //"F:\\document/test/aa.xls";
public static void createBusinessFinish(String fileName) {
try {
//申办
EntitySet<laam_ex_sb> sbSet = getExcelSBdata(fileName);
List<laam_ex_sb> sblist = null;
if (sbSet != null) {
sblist = sbSet.getResult();
for (int i = 0; i < sblist.size(); i++) {
laam_ex_sb sb = sblist.get(i);
sb.setID(Global.getInstance().GetUUID());
String sbStr = Global.getInstance().getSerializService().Serialize(sb);
EntityBean bean = (EntityBean) Global.getInstance().getSerializService().DeSerialize(sbStr, EntityBean.class);
bean.setbeanname("laam_ex_sb");
bean.insert();
}
}
}
public static EntitySet<laam_ex_sb> getExcelSBdata (String fileName)
{
try
{
//直接从本地文件创建Workbook
FileInputStream instream = new FileInputStream(File.get(fileName));
HSSFWorkbook hssfworkbook = new HSSFWorkbook(instream);
HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);//第一个工作表
//Map<String, Object> map = new HashMap<String, Object>();
EntitySet<laam_ex_sb> beanSet = new EntitySet<laam_ex_sb>();
String busino = null;
String sblsh = null;
String sbwd = null;
//遍历该行所有的行,j表示行数， getPhysicalNumberOfRows行的总数
ArrayList<laam_ex_sb> list = new ArrayList<laam_ex_sb>();//创建List 集合
laam_ex_sb entity = null;
Row row = null;
int rows = hssfsheet.getPhysicalNumberOfRows(); //总行数
for (int j = 1; j < rows; j++)
{
entity = new laam_ex_sb();
row = hssfsheet.getRow(j); //获取行数据对象(0是表头)
if ( row == null )
{
System.out.println("提示:\n" + (j + 1) + " 行没有数据。\n");
break;
}
/** */
/**将EXCEL中的第 j 行，第一列的值插入到实例中*/
//获取每一行的列
int k = 0;
//事项名称
if ( row.getCell(k) == null )
{
entity.setSxmc("");
}
else
{
entity.setSxmc(row.getCell(k).getStringCellValue().trim());
if ( j == 1 )
{
sxmc = row.getCell(k).getStringCellValue().trim();
}
}
k++;
//申请人类型
if ( row.getCell(k) == null )
{
entity.setSqrlx("1");
}
else
{
try {
entity.setSqrlx(Math.round(row.getCell(k).getNumericCellValue()) + "");
} catch (Exception e) {
entity.setSqrlx(row.getCell(k).getStringCellValue().trim());
}
}
k++;
//申请人名称
if ( row.getCell(k) == null )
{
entity.setSqrmc("");
}
else
{
try {
entity.setSqrmc(row.getCell(k).getStringCellValue().trim());
} catch (Exception e) {
entity.setSqrmc(Math.round(row.getCell(k).getNumericCellValue()) + "");
}
}
k++;
//申请人证件号码
if ( row.getCell(k) == null )
{
entity.setSqrzjhm("");
}
else
{
String card = null;
try
{
card = row.getCell(k).getStringCellValue().trim();
}
catch (Exception e)
{
card = Math.round(row.getCell(k).getNumericCellValue()) + "";
}
finally
{
String c = new NSgetProcessData().getRandomID();
if ( card == null || "".equals(card) || card.length() < 15 )
{
card = c;//得到一个随机的身份证号码
}
entity.setSqrzjhm(card);
}
}
k++;
//联系人姓名
if ( row.getCell(k) == null )
{
entity.setLxrxm("");
}
else
{
try {
entity.setLxrxm(row.getCell(k).getStringCellValue().trim());
} catch (Exception e) {
entity.setLxrxm(Math.round(row.getCell(k).getNumericCellValue()) + "");
}
}
k++;
//联系人手机
if ( row.getCell(k) == null )
{
entity.setLxrsj(new NSgetProcessData().getPhone());
}
else
{
String phone = null;
try {
phone = Math.round(row.getCell(k).getNumericCellValue()) + "";
} catch (Exception e) {
phone = row.getCell(k).getStringCellValue().trim();
}finally{
if ( phone == null || "".equals(phone)|| phone.length()<8|| phone.length()>11)
{
phone = new NSgetProcessData().getPhone();//得到一个随机的手机号码
}
entity.setLxrsj(phone);
}
}
k++;
//申办项目名称
if ( row.getCell(k) == null )
{
entity.setSbxmmc("");
}
else
{
try {
entity.setSbxmmc(row.getCell(k).getStringCellValue().trim());
} catch (Exception e) {
entity.setSbxmmc(Math.round(row.getCell(k).getNumericCellValue()) + "");
}
}
k++;
//申办材料清单
if ( row.getCell(k) == null )
{
entity.setSbclqd("无需提交材料");
}
else
{
try {
entity.setSbclqd(row.getCell(k).getStringCellValue().trim());
} catch (Exception e) {
entity.setSbclqd(Math.round(row.getCell(k).getNumericCellValue()) + "");
}
}
k++;
//提交方式
if ( row.getCell(k) == null )
{
entity.setTjfs("1");
}
else
{
try {
entity.setTjfs(Math.round(row.getCell(k).getNumericCellValue()) + "");
} catch (Exception e) {
entity.setTjfs(row.getCell(k).getStringCellValue().trim());
}
}
k++;
//申办时间
if ( row.getCell(k) == null )
{
entity.setSbsj("2014-07-05 09:30:24");
}
else
{
String year = row.getCell(k).getDateCellValue().getYear() + 1900 + "";
int mon = row.getCell(k).getDateCellValue().getMonth() + 1;
String month = mon < 10 ? "0" + mon : mon + "";
int d = row.getCell(k).getDateCellValue().getDate();
String day = d < 10 ? "0" + d : d + "";
Random rd = new Random();
String hour = "";
if (sxmc.contains("社会投资项目备案") || sxmc.contains("接收高校应届毕业生")) { //当为即办件的时候
String[] number = { "09", "10"};
hour = number[rd.nextInt(number.length)];
}else
{
String[] number = { "09", "10", "11", "12", "13", "14", "15", "16", "17" };
hour = number[rd.nextInt(number.length)];
}
int m = rd.nextInt(60);
String minite = m < 10 ? "0" + m : m + "";
int s = rd.nextInt(60);
String secend = s < 10 ? "0" + s : s + "";
String timer =year + "-" + month + "-" + day + " " + hour + ":" + minite + ":" + secend;
entity.setSbsj(timer);
}
if ( j == 1 )
{
EntityBean result = Global.getInstance().getDataSource().beanExecuteFirstRow("select busino,businame,"+ "(select dirname from laambusinessdir where id=laambusiness.dirid)||'网上窗口' deptname from laambusiness where businame='"
+ sxmc + "'");
busino = result.getString("busino");
sbwd = result.getString("deptname");
//busiService bService = new busiService();
}
String prefix = busino.substring(0, 19);
sblsh = GlobalSNService.getSN("lgbsShenbanLiushuiHao", "{date:yyMMdd}{sn:xxxx}", prefix==null?"A":prefix, null, 1, null, new Date());
entity.setSblsh(sblsh); //申办流水号
entity.setSxbm(busino); //事项编码
entity.setSbjtwd(sbwd); //申办具体网点
entity.setXzqhdm("440305"); //业务发生所在地行政区划代码
entity.setSbhzh(sblsh); //申办回执号
list.add(entity);
}
int z = hssfsheet.getPhysicalNumberOfRows();
int nextLine = list.size() + 1;
if ( list.size() < z - 1 )
{
System.out.println("导入提示:\n" + "成功导入" + list.size() + "条数据，请根据提示检查第:" + nextLine + "行数据");
}
else
{
System.out.println("导入提示:\n" + "成功导入" + list.size() + "条数据");
}
beanSet.setResult(list);
return beanSet;
}
catch (Exception e)
{
Global.getInstance().LogError(e);
return null;
}
}
浙公网安备 33010602011771号