importdatafromexcel.java

采用JAVA开发

package com.gctech.sms.voice.api;

import java.util.*;
import java.sql.*;
import java.io.*;
import jxl.*;
import jxl.read.biff.BiffException;

import com.gctech.sms.voice.*;
import com.gctech.sms.voice.dao.*;
import com.gctech.sms.voice.common.*;

public class ImportDataFromExcel
{
  public ImportDataFromExcel()
      throws Exception
  {

  }
  public void ImportDataSrtruFromExcel(String filePath)
      throws Exception
  {


    DBHelper.isDebug = true;
    Connection conn = null;
    File xlsFile = new File(filePath);
    Workbook ww;
    Sheet s;
    List bigList = new ArrayList();
    List smallList = new ArrayList();
    try
    {
      ww = Workbook.getWorkbook(xlsFile);
      //得到大了类表
      s = ww.getSheet(0);
      int count = s.getRows();

      for(int i = 0;i < count;i++)
      {
        Cell[] cells = s.getRow(i);
        if(cells[1].getContents().equals("0"))
        {
          //一个大类
          BigCatalogValueObject vo = new BigCatalogValueObject();
          vo.setId(new Integer(cells[0].getContents()));
          vo.setName(cells[2].getContents());
          bigList.add(vo);
        }
        else
        {
          //一个小类
          SmallCatalogValueObject vo = new SmallCatalogValueObject();
          vo.setId(new Integer(cells[1].getContents()));
          vo.setBigId(new Integer(cells[0].getContents()));
          vo.setName(cells[2].getContents());
          smallList.add(vo);
        }
      }
      conn = DBHelper.getConn();
      conn.setAutoCommit(false);
      BigCatalogDAO dao = new BigCatalogDAO();
      BigCatalogValueObject vo = null;
      for(int i = 0;i < bigList.size();i++)
      {
        vo = (BigCatalogValueObject)bigList.get(i);
        dao.insert(conn,vo);
                      System.out.println(vo.getId()+":"+vo.getName());
      }

      SmallCatalogDAO dao1 = new SmallCatalogDAO();
      SmallCatalogValueObject vo1 = null;
      for(int i = 0;i < smallList.size();i++)
      {
        vo1 = (SmallCatalogValueObject)smallList.get(i);
        dao1.insert(conn,vo1);
              System.out.println(vo1.getBigId()+":"+vo1.getId()+":"+vo1.getName());
      }
    }
    catch(Exception ex)
    {
      ex.printStackTrace();
      try
      {
        conn.rollback();
      }
      catch(Exception exe)
      {

      }
    }
    finally
    {
      try
      {

        DBHelper.cleanup(conn);
      }
      catch(Exception ex)
      {
        ex.printStackTrace();
      }

    }


  }

  public void ImportDataFromUSERExcel(String filePath)
      throws Exception
  {
    try
    {
      DBHelper.isDebug = true;
      Connection conn = null;
      File xlsFile = new File(filePath);
      Workbook ww;
      Sheet s;
      ww = Workbook.getWorkbook(xlsFile);

     s = ww.getSheet(0);
     int count = s.getRows();
     VoiceValueObject voice = null;
     VoiceDAO vd = new VoiceDAO();
     Random random = new Random();
     for(int i = 1;i < count;i++)
     {
       Cell[] cells = s.getRow(i);

       conn = DBHelper.getConn();
       voice = new VoiceValueObject();
       Integer bigCatalogId = new Integer(0);
       Integer smallCatalogId = new Integer(0);
       String fileName = cells[1].getContents();
       String filepath = ".\\uservox\\" + fileName + ".vox";

       String desc = cells[2].getContents();
       Integer type = Consts.VOICE_TYPE_USER;
       voice.setType(type);
       voice.setId(new Integer(DBHelper.nextVoiceSeqId(conn)));
       voice.setCatalogId(smallCatalogId);
       voice.setCount(new Integer(random.nextInt(10000)));
       voice.setCreateDate(new java.util.Date());
       voice.setDescription(desc);
       voice.setName(filepath);
       voice.setPrice(new Integer(100));
       voice.setType(type);
       vd.insert(conn,voice);

       conn.commit();
       conn.close();
       System.out.println("import "+i+":"+voice.getCatalogId()+":"+voice.getCount()+":"+voice.getName()+":"+voice.getDescription());



     }


    }
    catch(Exception ex)
    {
      ex.printStackTrace();
    }

  }

  public void ImportDataFromJFDExcel(String filePath)
      throws Exception
  {

    DBHelper.isDebug = true;
    Connection conn = null;
    File xlsFile = new File(filePath);
    Workbook ww;
    Sheet s;

    try
    {
      //得到所有文件的详细信息
      ww = Workbook.getWorkbook(xlsFile);
      s = ww.getSheet(1);
      int size = s.getRows();
      VoiceDAO vd = new VoiceDAO();
      VoiceValueObject voice = new VoiceValueObject();
      Random random = new Random();
      System.out.println("size is " + size);
      for(int i = 1;i < size;i++)
      {
        Cell[] cells = s.getRow(i);
        if(cells == null)return;
        conn = DBHelper.getConn();

        Integer bigCatalogId = new Integer(cells[0].getContents().trim());
        Integer smallCatalogId = new Integer(cells[1].getContents().trim());
        String fileName = cells[2].getContents();
        String filepath = ".\\VOX\\" + bigCatalogId + "\\" + smallCatalogId +
            "\\" + fileName + ".vox";

        String desc = cells[3].getContents();
        String strType = cells[4].getContents().trim();
        Integer type = null;
        if(strType.equals("吉芙德"))type = Consts.VOICE_TYPE_JFD;
        else if (strType.equals("朴润"))type = Consts.VOICE_TYPE_PR;
        else type = Consts.VOICE_TYPE_CMHD;
        voice.setType(type);
        voice.setId(new Integer(DBHelper.nextVoiceSeqId(conn)));
        voice.setCatalogId(smallCatalogId);
        voice.setCount(new Integer(random.nextInt(50000)));
        voice.setCreateDate(new java.util.Date());
        voice.setDescription(desc);
        voice.setName(filepath);
        voice.setPrice(new Integer(100));
        voice.setType(type);
        vd.insert(conn,voice);
        conn.commit();
        conn.close();
        System.out.println("import "+i+":"+voice.getCatalogId()+":"+voice.getCount()+":"+voice.getName()+":"+voice.getDescription());


      }

    }
    catch(Exception ex)
    {
      ex.printStackTrace();
    }

    finally
    {

    }

  }

  public static void main(String[] args)
      throws Exception
  {
    ImportDataFromExcel df = new ImportDataFromExcel();
    String arg = args[0];
    String filePath = args[1];
    if(arg.equals("jfd"))df.ImportDataFromJFDExcel(filePath);
    else if(arg.equals("user")) df.ImportDataFromUSERExcel(filePath);
    else  if(arg.equals("stru")) df.ImportDataSrtruFromExcel(filePath);


  }

}