专门做正品的网站/sem招聘

由于基于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;}}