利用L将Oracle中的数据导入L中.doc

利用EXEL将Oracle中的数据导入EXEL中一、 配置数据源安装Oracle数据库客户端程序，配置Oracle数据源，如下例：E:oracleora92networkadmintnsnames.ora 该文件的内容为：LANYAN = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ecatic-shihu)(PORT = 1521) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = lanyan) (SID = shihu) ) )二、 EXEL连接数据源并获得数据如图所示：1、新建EXEL，数据自其它来源2、选择数据源3、提示输入密码4、选择需要从Oracle中导出的表，并选择表中的列5、选择列6、涮选数据7、选择列作为排序关键字8、完成查询向导9、导入数据，选择新建工作表10、正在导入数据到EXEL中11、导入完成三、 将EXEL中的数据导入到Oracle中1、有一个比较麻烦但保证成功的方法A、在本地创建一个Access数据库，将Excel数据先导入到Access。可直接导入点击鼠标右键，选择导入，文件类型选择要导入的Excel文件，也可通过创建宏用TransferSpreadsheet函数实现。B、在本地创建ODBC，链接oracle数据库。将oracle中需要导入数据的表以链接表的方式在本地Access中创建（千万不要使用导入表）。C、在Access数据库中创建插入查询将导入好的Excel数据导入到oracle中。 此方法实验过但是没有成功。2、自己写了个JAVA类来完成将EXEL中数据导入到Oracle中，下面为详细方法。此方法能够成功执行，需要仔细。package test.control.action;import java.io.File;import java.io.FileInputStream;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.SQLException;import java.sql.Timestamp;import jxl.Cell;import jxl.Sheet;import jxl.Workbook;import common.util.DateUtil;import common.util.Tools;public class Test /* * 指定EXEL数据文件路径 */ private static final File file = new File(d:product_info.xls); /* * 更新SQL语句 */ private static final String updateStr = UPDATE EBSP.PRODUCT_INFO SET + ITEM_ID = ? + ,ITEM_FULL_NAME = ? + ,ITEM_SHORT_NAME = ? + ,ITEM_BRAND = ? + ,ITEM_SPECS = ? + ,CATEGORY_ID = ? + ,CATEGORY_NAME = ? + ,SUPPLIER_ITEM_NUMBER = ? + ,MANUFACTURER_NAME = ? + ,MFR_PART_NUMBER = ? + ,MAIN_PERFORMANCE = ? + ,MAIN_STRUTURE = ? + ,MAIN_PARAMETER = ? + ,PRIMARY_IMAGE_ID = ? + ,PRIMARY_IMAGE_NAME = ? + ,IS_INCLUDE_IMG = ? + ,IS_INCLUDE_DOC = ? + ,PROVIDER_ID = ? + ,PROVIDER_NAME = ? + ,KEY_WORD = ? + ,AUDIT_STATUS = ? + ,VIEW_STATUS = ? + ,USED_STATUS = ? + ,VOUCH_STATUS = ? + ,CREATE_USER_ID = ? + ,CREATE_USER_NAME = ? + ,CREATE_DATE = ? + ,LAST_UPDATE_USER_ID = ? + ,LAST_UPDATE_USER_NAME = ? + ,LAST_UPDATE_DATE = ? + ,LANGUAGE_CODE = ? + ,APP_ID = ? + WHERE ITEM_ID = ? ; /* * 插入SQL语句 */ private static final String insertStr = INSERT INTO EBSP.PRODUCT_INFO ( + ITEM_ID + ,ITEM_FULL_NAME + ,ITEM_SHORT_NAME + ,ITEM_BRAND + ,ITEM_SPECS + ,CATEGORY_ID + ,CATEGORY_NAME + ,SUPPLIER_ITEM_NUMBER + ,MANUFACTURER_NAME + ,MFR_PART_NUMBER + ,MAIN_PERFORMANCE + ,MAIN_STRUTURE + ,MAIN_PARAMETER + ,PRIMARY_IMAGE_ID + ,PRIMARY_IMAGE_NAME + ,IS_INCLUDE_IMG + ,IS_INCLUDE_DOC + ,PROVIDER_ID + ,PROVIDER_NAME + ,KEY_WORD + ,AUDIT_STATUS + ,VIEW_STATUS + ,USED_STATUS + ,VOUCH_STATUS + ,CREATE_USER_ID + ,CREATE_USER_NAME + ,CREATE_DATE + ,LAST_UPDATE_USER_ID + ,LAST_UPDATE_USER_NAME + ,LAST_UPDATE_DATE + ,LANGUAGE_CODE + ,APP_ID + ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?); /* * 主函数 * param arg * throws Exception */ public static void main(String arg) throws Exception boolean result = new Test().updateDate();/boolean result = new Test().insertDate();System.out.println(完成！ + result); /* * 连接数据库 * return */ public static Connection getConnecton() String driverUrl = jdbc:oracle:thin:6:1521:shihu;String userName = ebsp;String password = ebsp;Connection conn = null;try Class.forName(oracle.jdbc.driver.OracleDriver); conn = DriverManager.getConnection(driverUrl, userName, password); catch (Exception e) e.printStackTrace();return conn; /* * 更新方法 * 1、连接数据库 2、读取EXEL文件并将该文件中的字段对应SQL语句字段 * return * throws Exception */ public boolean updateDate()throws Exceptionboolean result = false;Connection conn = new Test().getConnecton();PreparedStatement pstmt = null;InputStream inputStream = new FileInputStream(file);pstmt = conn.prepareStatement(updateStr);try Workbook workbook = Workbook.getWorkbook(inputStream); Sheet sheet = workbook.getSheet(0); System.out.println(Update Start:); for (int k = 1; k sheet.getRows(); k +) System.out.println(k); Cell cells = sheet.getRow(k); System.out.print(*); conn.setAutoCommit(false); int i = 1; pstmt.setInt(i+, Tools.parseInt(cells0.getContents(); pstmt.setString(i+, cells1.getContents(); pstmt.setString(i+, cells2.getContents(); pstmt.setString(i+, cells3.getContents(); pstmt.setString(i+, cells4.getContents(); pstmt.setInt(i+, Tools.parseInt(cells6.getContents(); pstmt.setString(i+, cells7.getContents(); pstmt.setString(i+, cells8.getContents(); pstmt.setString(i+, cells9.getContents(); pstmt.setString(i+, cells10.getContents(); pstmt.setString(i+, cells11.getContents(); pstmt.setString(i+, cells12.getContents(); pstmt.setString(i+, cells13.getContents(); pstmt.setInt(i+, Tools.parseInt(cells14.getContents(); pstmt.setString(i+, cells15.getContents(); pstmt.setInt(i+, Tools.parseInt(cells16.getContents(); pstmt.setInt(i+, Tools.parseInt(cells17.getContents(); pstmt.setInt(i+, Tools.parseInt(cells18.getContents(); pstmt.setString(i+, cells19.getContents(); pstmt.setString(i+, cells20.getContents(); pstmt.setInt(i+, Tools.parseInt(cells21.getContents(); pstmt.setInt(i+, Tools.parseInt(cells22.getContents(); pstmt.setInt(i+, Tools.parseInt(cells23.getContents(); pstmt.setString(i+, cells24.getContents(); pstmt.setInt(i+, Tools.parseInt(cells25.getContents(); pstmt.setString(i+, cells26.getContents(); pstmt.setTimestamp(i+,(cells27.getContents() = null) ? null: new Timestamp(DateUtil.parseDate(cells27.getContents().getTime(); pstmt.setInt(i+, Tools.parseInt(cells28.getContents(); pstmt.setString(i+, cells29.getContents(); pstmt.setTimestamp(i+,(cells30.getContents() = null) ? null: new Timestamp(DateUtil.parseDate(cells30.getContents().getTime(); pstmt.setString(i+, cells31.getContents(); pstmt.setString(i+, cells32.getContents(); pstmt.setInt(i+, Tools.parseInt(cells0.getContents(); pstmt.executeUpdate(); mit(); conn.setAutoCommit(true); result = true; catch (SQLException e) e.printStackTrace(); finally if (conn != null)conn.close(); System.out.println(Update End!);return result; /* * 插入方法 * 1、连接数据库 2、读取EXEL文件并将该文件中的字段对应SQL语句字段 3、将EXEL文件中的数据插入到数据库中 * return * throws Exception */ public boolean insertDate()throws Exceptionboolean result = false;Connection conn = new Test().getConnecton();PreparedStatement pstmt = null;InputStream inputStream = new FileInputStream(file);pstmt = conn.prepareStatement(insertStr);try Workbook workbook = Workbook.getWorkbook(inputStream); Sheet sheet = workbook.getSheet(0); System.out.println(Insert Start:); for (int k = 1; k sheet.getRows(); k+) Cell cells = sheet.getRow(k); System.out.println(+ + k); conn.setAutoCommit(false); int i = 1; pstmt.setInt(i+, Tools.parseInt(cells0.getContents(); pstmt.setString(i+, cells1.getContents(); pstmt.setString(i+, cells2.getContents(); pstmt.setString(i+, cells3.getContents(); pstmt.setString(i+, cells4.getContents(); pstmt.setInt(i+, Tools.parseInt(cells6.getContents(); pstmt.setString(i+, cells7.getContents(); pstmt.setString(i+, cells8.getContents(); pstmt.setString(i+, cells9.getContents(); pstmt.setString(i+, cells10.getContents(); pstmt.setString(i+, cells11.getContents(); pstmt.setString(i+, cells12.getContents(); pstmt.setString(i+, cells13.getContents(); pstmt.setInt(i+, Tools.parseInt(cells14.getContents(); pstmt.setString(i+, cells15.getContents(); pstmt.setInt(i+, Tools.parseInt(cells16.getContents(); pstmt.setInt(i+, Tools.parseInt(cells17.getContents(); pstmt.setInt(i+, Tools.parseInt(cells18.getContents(); pstmt.setString(i+, cells19.getContents(); pstmt.setString(i+, cells20.getContents(); pstmt.setInt(i+, Tools.parseInt(cells21.getContents(); pstmt.setInt(i+, Tools.parseInt(cells22.getContents(); pstmt.setIn