Excel转换为sql语句导入Oracle数据库
2009年01月16日 星期五 20:33
package excel;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import jxl.Sheet;
import jxl.Workbook;
public class ExcelToInsert {excel连接sql数据库教程
public static void main(String[] args) {
        String table_name = "OPERATION_FILTER"; //在此输入表名
  int ID = 0;
  int OPERATION_ID = 0;
  int USER_ID = 0;
  int STATUS = 0;
  String USER_CN = "";
  String USER_NAME = "";
  String SQL = ""; 
  final String EXL_NAME = "OPERATION_FILTER";
  final String BASE_PATH = "D:/data/"; // BasePath
  final String IN_EXL_PATH = BASE_PATH + EXL_NAME + ".xls"; // excelPath
  StringBuffer sb = null;
  FileWriter fw = null;
  int rows = 0;
  int columns = 0;
  try {
    fw = new FileWriter(BASE_PATH + EXL_NAME + ".sql");
    final String start = "REM INSERTING into " + table_name;
    fw.write(start + "\r\n"); // newline
    // read excel file
    try {
    Workbook rwb = Workbook(new File(IN_EXL_PATH));
    Sheet rs[] = Sheets();
    for (int i = 0; i < rs.length; i++) {
      rows = rs[i].getRows();
      columns = rs[i].getColumns();
      System.out.println(columns+","+rows);
      String[] ldapattr = new String[columns];
      for (int j = 0; j < rows; j++) {
      if (j == 0) {
        for (int g = 0; g < columns; g++) {
        ldapattr[g] = rs[i].getCell(g, j).getContents();
        }
        continue;
      }
      sb = new StringBuffer();
      sb = sb.append("\r\n");       
      ID = Integer.parseInt(rs[i].getCell(0, j).getContents().trim());
      OPERATION_ID = Integer.parseInt(rs[i].getCell(1, j).getContents().trim());
      USER_ID = Integer.parseInt(rs[i].getCell(2, j).getContents().trim());
      USER_CN = rs[i].getCell(3, j).getContents().trim();
      USER_NAME = rs[i].getCell(4, j).getContents().trim();
      STATUS = Integer.parseInt(rs[i].getCell(5, j).getContents().trim());
      System.out.println("VALUE:"+ID+","+OPERATION_ID+","+USER_ID
                    +","+USER_CN+","+USER_NAME+","+STATUS);     
      SQL = "INSERT INTO TABLE " + table_name +"(ID,OPERATION_ID,USER_ID,USER_CN," +
        "USER_NAME,STATUS) VALUES "+ "("+ID+","+OPERATION_ID+","+USER_ID+"," +
        "\""+USER_CN+"\""+","+"\""+USER_NAME+"\""+","+STATUS+")";
        sb = sb.append(SQL + "\r\n"); 
        fw.String());
      }
    }
    } catch (final Exception e) {
    ID = 1;
    }
    fw.close();
  } catch (final IOException ex) {
    ex.printStackTrace();
  }
    }
}
Excel 表A、B、C、D、E、F列名分别是ID、OPERATION_ID \USER_ID、USER_CN、USER_NAME、STATUS。一共8行数据
最后会生成一个和Excel表相同的sql文件名，里面的内容如下：
REM INSERTING into OPERATION_FILTER
INSERT INTO TABLE OPERATION_FILTER(ID,OPERATION_ID,USER_ID,USER_CN,USER_NAME,STATUS) VALUES (1,1,1,"admin","超级管理员",1);
INSERT INTO TABLE OPERATION_FILTER(ID,OPERATION_ID,USER_ID,USER_CN,USER_NAME,STATUS) VALUES (2,2,1,"admin","超级管理员",1);
INSERT INTO TABLE OPERATION_FILTER(ID,OPERATION_ID,USER_ID,USER_CN,USER_NAME,STATUS) VALUES (3,3,1,"admin","超级管理员",1);
INSERT INTO TABLE OPERATION_FILTER(ID,OPERATION_ID,USER_ID,USER_CN,USER_NAME,STATUS) VALUES (4,4,1,"admin","超级管理员",1);
INSERT INTO TABLE OPERATION_FILTER(ID,OPERATION_ID,USER_ID,USER_CN,USER_NAME,STATUS) VALUES (5,5,1,"admin","超级管理员",1);
INSERT INTO TABLE OPERATION_FILTER(ID,OPERATION_ID,USER_ID,USER_CN,USER_NAME,STATUS) VALUES (6,6,1,"admin","超级管理员",1);
INSERT INTO TABLE OPERATION_FILTER(ID,OPERATION_ID,USER_ID,USER_CN,USER_NAME,STATUS) VALUES (7,7,1,"admin","超级管理员",1);
INSERT INTO TABLE OPERATION_FILTER(ID,OPERATION_ID,USER_ID,USER_CN,USER_NAME,STATUS) VALUES (8,8,1,"admin","超级管理员",1);

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。