java操作任何树桩excel



package test;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.record.formula.IntPtg;
import org.apache.velocity.runtime.directive.Parse;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class DoExcelJdbc {

 public static void main(String[] args) {
  new DoExcelJdbc().treeExcelInsert();
  // System.out.println(Integer.parseInt("000"));
 }

 public void readAndWriteExcle() {

  Connection conn = null;
  PreparedStatement ps = null;
  ResultSet rs = null;
  try {

   Class.forName("oracle.jdbc.driver.OracleDriver");
   conn = DriverManager.getConnection(
     "jdbc:oracle:thin:@10.7.83.12:1521:orascap", "scap",
     "marconi");
   System.out.println(conn);// where
          // u.userid='980C6CFD48A86057E040007F01005AFF'

   Workbook wb = Workbook.getWorkbook(new File("E://参加活动号码.xls"));

   // 打开一个文件的副本，并且指定数据写回到原文件
   WritableWorkbook wirtebook = Workbook.createWorkbook(new File(
     "E://normal.xls"), wb);

   // 获得第一个工作表对象
   Sheet sheet = wb.getSheet(0);
   WritableSheet writesheet = wirtebook.getSheet(0);

   int row = sheet.getRows();
   System.out.println(row);
   for (int i = 0; i < row; i++) {
    if (conn.isClosed())
     conn = DriverManager.getConnection(
       "jdbc:oracle:thin:@10.7.83.12:1521:orascap",
       "scap", "marconi");
    // 得到第一列第一行的单元格
    Cell cell = sheet.getCell(3, i);
    String str = cell.getContents();

    String sqlStr = "select u.usertype,u.loginname from org_user  u ";
    ps = conn.prepareStatement(sqlStr);
    // ps.setString(1, "");
    rs = ps.executeQuery();
    if (rs.next()) {
     System.out
       .println(rs.getString(1) + "--" + rs.getString(2));
     writesheet.addCell(new Label(4, i, rs.getString(1)));
     writesheet.addCell(new Label(5, i, rs.getString(2)));
    } else {
     System.out.println("无记录");
     writesheet.addCell(new Label(5, i, "无记录"));
    }

    if (i % 19 == 0) {
     rs.close();
     ps.close();
     conn.close();
    }

   }
   wirtebook.write();
   wirtebook.close();
   wb.close();

  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   try {
    if (rs != null) {
     rs.close();
    }
    if (conn != null) {
     conn.close();
    }
    if (ps != null) {
     ps.close();
    }
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }

 public void treeExcelInsert() {
  Connection conn = null;
  PreparedStatement ps = null;
  ResultSet rs = null;
  int num=0;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   conn = DriverManager.getConnection(
     "jdbc:oracle:thin:@10.7.153.39:1521:orascap", "scap",
     "marconi");
   String co1Id = "00", co2Id = "0000", co3Id = "000000", co4Id = "00000000", co5Id = "0000000000",co6Id="000000000000";
   String p1Id = "0",  p2Id = "0000", p3Id = "000000", p4Id = "00000000", p5Id = "0000000000",p6Id="000000000000";
   String co1Name = "", co2Name = "", co3Name = "", co4Name = "", co5Name = "",co6Name="";
   String co1Paht = "/", co2Path = "", co3Path = "", co4Path = "", co5Path = "",co6Path="";
   String isNode = "0";

   //Workbook wb = Workbook.getWorkbook(new File("E://normal.xls"));
   Workbook wb = Workbook.getWorkbook(new File("E://normal.xls"));
   // 打开一个文件的副本，并且指定数据写回到原文件
   WritableWorkbook wirtebook = Workbook.createWorkbook(new File(
     "E://normal1.xls"), wb);
   // 获得第一个工作表对象
   Sheet sheet = wb.getSheet(0);
   WritableSheet writesheet = wirtebook.getSheet(0);

   //第一列
   int row = sheet.getRows();
   for (int i = 0; i < row; i++) {

    Cell cell = sheet.getCell(0, i);
    String str = cell.getContents();
    if (!("").equals(str) ) { // 第一列的ID
     if(i!=0){
     int cloIdTest = Integer.parseInt(co1Id) + 1;
     if (cloIdTest < 10) co1Id = "0" + cloIdTest;
     }
    
     co1Name = str;
     co2Id="0000";
     
     String sqlStr = "insert into svr_pub_code_epg_normal(id, parentid, name, isnode, path)values(?, ?, ?, ?, ?)";
     ps = conn.prepareStatement(sqlStr);
     ps.setString(1, co1Id);
     ps.setString(2, p1Id);
     ps.setString(3, co1Name);
     ps.setString(4, isNode);
     ps.setString(5, co1Paht);
     ps.execute();
     num++;
     System.out.println("找到了并正在插入第" + i + "行，第" + 0 + "列的一条记录\t"
       + co1Id + "\t" + p1Id + "\t" + co1Name + "\t"
        + isNode + "\t" + co1Paht);
     
    
    
   }
    
    //第二列
    cell = sheet.getCell(1, i);
    str = cell.getContents();
    String nextStr = sheet.getCell(2, i).getContents();
    if (!("").equals(str)) { // 第一列的ID
     
     int cloIdTest = Integer.parseInt(co2Id.substring(2,4)) + 1;
     if (cloIdTest < 10) co2Id =co1Id+ "0" + cloIdTest;
     else co2Id = co1Id + cloIdTest;
     p2Id = co1Id;
     co3Id="000000";
     co2Name = str;
     isNode = nextStr.equals("") ? "1" : "0";
     co2Path = co1Paht + "/" + co1Name;
     
     String sqlStr = "insert into svr_pub_code_epg_normal(id, parentid, name, isnode, path)values(?, ?, ?, ?, ?)";
     ps = conn.prepareStatement(sqlStr);
     ps.setString(1, co2Id);
     ps.setString(2, p2Id);
     ps.setString(3, co2Name);
     ps.setString(4, isNode);
     ps.setString(5, co2Path);
     ps.execute();
     num++;
     System.out.println("找到了并正在插入第" + i + "行，第" + 1 + "列的一条记录\t"
       + co2Id + "\t" + p2Id + "\t" + co2Name + "\t"
       + isNode + "\t" + co2Path);
     
    }
    
    //第三列
   
    str = nextStr;
    nextStr = sheet.getCell(3, i).getContents();
    if (!("").equals(str)) { // 第一列的ID

     int cloIdTest = Integer.parseInt(co3Id.substring(4, 6)) + 1;
     if (cloIdTest < 10) co3Id =co2Id+ "0" + cloIdTest;
     else co3Id = co2Id + cloIdTest;
     
     p3Id = co2Id;
     co3Name = str;
     isNode = nextStr.equals("") ? "1" : "0";
     co3Path = co2Path + "/" + co2Name;
     co4Id="00000000";
     
     String sqlStr = "insert into svr_pub_code_epg_normal(id, parentid, name, isnode, path)values(?, ?, ?, ?, ?)";
     ps = conn.prepareStatement(sqlStr);
     ps.setString(1, co3Id);
     ps.setString(2, p3Id);
     ps.setString(3, co3Name);
     ps.setString(4, isNode);
     ps.setString(5, co3Path);
     ps.execute();
     num++;
     System.out.println("找到了并正在第" + i + "行，第" + 2 + "列的一条记录\t"
       + co3Id + "\t" + p3Id + "\t" + co3Name + "\t"
       + isNode + "\t" + co3Path);
     
    }
    
   
    if(num>=240){
     try {
      if (rs != null) {
       rs.close();
      }
      if (conn != null) {
       conn.close();
      }
      if (ps != null) {
       ps.close();
      }
     } catch (SQLException e) {
      e.printStackTrace();
     }
      conn = DriverManager.getConnection(
        "jdbc:oracle:thin:@10.7.153.39:1521:orascap",
        "scap", "marconi");
      num=0;
     
    }
    //第四列
    str = nextStr;
    nextStr = sheet.getCell(4, i).getContents();
    if (!("").equals(str)) { // 第一列的ID
     int cloIdTest = Integer.parseInt(co4Id.substring(6, 8)) + 1;
     if (cloIdTest < 10) co4Id =co3Id+ "0" + cloIdTest;
     else co4Id = co3Id + cloIdTest;
     p4Id = co3Id;
     co4Name = str;
     isNode = nextStr.equals("") ? "1" : "0";
     co4Path = co3Path + "/" + co3Name;
     co5Id="0000000000";
     
     String sqlStr = "insert into svr_pub_code_epg_normal(id, parentid, name, isnode, path)values(?, ?, ?, ?, ?)";
     ps = conn.prepareStatement(sqlStr);
     ps.setString(1, co4Id);
     ps.setString(2, p4Id);
     ps.setString(3, co4Name);
     ps.setString(4, isNode);
     ps.setString(5, co4Path);
     ps.execute();
     num++;
     System.out.println("找到了正在插入" + i + "行，第" + 3 + "列的一条记录\t"
       + co4Id + "\t" + p4Id + "\t" + co4Name + "\t"
       + isNode + "\t" + co4Path);
     
    }

    //第五列
    str = nextStr;
    nextStr = sheet.getCell(5, i).getContents();
    if (!("").equals(str)) { // 第一列的ID
     int cloIdTest = Integer.parseInt(co5Id.substring(8, 10)) + 1;
     if (cloIdTest < 10) co5Id =co4Id+ "0" + cloIdTest;
     else co5Id = co4Id + cloIdTest;
     p5Id = co4Id;
     co5Name = str;
     isNode = nextStr.equals("") ? "1" : "0";
     co6Id="000000000000";
     co5Path = co4Path + "/" + co4Name;
     
     String sqlStr = "insert into svr_pub_code_epg_normal(id, parentid, name, isnode, path)values(?, ?, ?, ?, ?)";
     ps = conn.prepareStatement(sqlStr);
     ps.setString(1, co5Id);
     ps.setString(2, p5Id);
     ps.setString(3, co5Name);
     ps.setString(4, isNode);
     ps.setString(5, co5Path);
     ps.execute();
     num++;
     System.out.println("找到了第" + i + "行，第" + 3 + "列的一条记录\t"
       + co5Id + "\t" + p5Id + "\t" + co5Name + "\t"
       + isNode + "\t" + co5Path);
     
    }
    
    //第六列
    str = nextStr;
   // nextStr = sheet.getCell(5, i).getContents();
    if (!("").equals(str)) { // 第一列的ID
     int cloIdTest = Integer.parseInt(co6Id.substring(10, 12)) + 1;
     if (cloIdTest < 10) co6Id =co5Id+ "0" + cloIdTest;
     else co6Id = co5Id + cloIdTest;
     p6Id = co5Id;
     co6Name = str;
     isNode ="1";
     co6Path = co5Path + "/" + co5Name;
     
     String sqlStr = "insert into svr_pub_code_epg_normal(id, parentid, name, isnode, path)values(?, ?, ?, ?, ?)";
     ps = conn.prepareStatement(sqlStr);
     ps.setString(1, co6Id);
     ps.setString(2, p6Id);
     ps.setString(3, co2Name);
     ps.setString(4, isNode);
     ps.setString(5, co6Path);
     ps.execute();
     num++;
     System.out.println("找到了第" + i + "行，第" + 3 + "列的一条记录\t"
       + co6Id + "\t" + p6Id + "\t" + co6Name + "\t"
       + isNode + "\t" + co6Path);
     
    }
    
    
    
   }
   wirtebook.write();
   wirtebook.close();
   wb.close();

  } catch (Exception e) {
   e.printStackTrace();
  }
  finally {
   try {
    if (rs != null) {
     rs.close();
    }
    if (conn != null) {
     conn.close();
    }
    if (ps != null) {
     ps.close();
    }
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }
}