/**
   * Read an excel file and spit out what we find.
   *
   * @param args Expect one argument that is the file to read.
   * @throws IOException When there is an error processing the file.
   */
  public static void main(String[] args) throws IOException {

    try {
      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
      // con = DriverManager
      // .getConnection("jdbc:sqlserver://10.130.133.3:1433;DatabaseName=RTB;user=i264678;password=;SelectMethod=cursor ");
      con =
          DriverManager.getConnection(
              "jdbc:sqlserver://10.135.128.227:1433;DatabaseName=RTBWTC;user=i264678;password=;SelectMethod=cursor ");
      System.out.println("connected");

      // create a new file input stream with the input file specified
      // at the command line
      FileInputStream fin = new FileInputStream(args[0]);
      // create a new org.apache.poi.poifs.filesystem.Filesystem
      POIFSFileSystem poifs = new POIFSFileSystem(fin);
      // get the Workbook (excel part) stream in a InputStream
      InputStream din = poifs.createDocumentInputStream("Workbook");
      // construct out HSSFRequest object
      HSSFRequest req = new HSSFRequest();
      // lazy listen for ALL records with the listener shown above
      req.addListenerForAllRecords(new LoadServiceFromExcel());
      // create our event factory
      HSSFEventFactory factory = new HSSFEventFactory();
      // process our events based on the document input stream
      factory.processEvents(req, din);
      // once all the events are processed close our file input stream
      fin.close();
      // and our document input stream (don't want to leak these!)
      din.close();
      System.out.println("done.");
    } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } finally {
      try {
        con.close();
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    }
  }
  /**
   * Processes a file into essentially record events.
   *
   * @param req an Instance of HSSFRequest which has your registered listeners
   * @param dir a DirectoryNode containing your workbook
   */
  public void processWorkbookEvents(HSSFRequest req, DirectoryNode dir) throws IOException {
    // some old documents have "WORKBOOK" or "BOOK"
    final String name;
    Set<String> entryNames = dir.getEntryNames();
    if (entryNames.contains("Workbook")) {
      name = "Workbook";
    } else if (entryNames.contains("WORKBOOK")) {
      name = "WORKBOOK";
    } else if (entryNames.contains("BOOK")) {
      name = "BOOK";
    } else {
      name = "Workbook";
    }

    InputStream in = dir.createDocumentInputStream(name);

    processEvents(req, in);
  }
예제 #3
0
  /** 导入 excel 2003 biff格式 如果是xml格式的 可以使用SAX(未测试) */
  @Test
  public void testImportExcel2003() throws Exception {

    long beginTime = System.currentTimeMillis();

    String fileName = "D:\\Backup\\Book1.xls";

    List<ExcelData> dataList = Lists.newArrayList();

    // 输入流
    InputStream fis = new BufferedInputStream(new FileInputStream(fileName));
    // 创建 org.apache.poi.poifs.filesystem.Filesystem
    POIFSFileSystem poifs = new POIFSFileSystem(fis);
    // 从输入流 得到 Workbook(excel 部分)流
    InputStream din = poifs.createDocumentInputStream("Workbook");
    // 构造 HSSFRequest
    HSSFRequest req = new HSSFRequest();

    // 添加监听器
    req.addListenerForAllRecords(new Excel2003Listener(dataList));
    //  创建事件工厂
    HSSFEventFactory factory = new HSSFEventFactory();
    // 根据文档输入流处理事件
    factory.processEvents(req, din);
    // 关闭输入流
    fis.close();
    // 关闭文档流
    din.close();

    System.out.println(dataList.size());
    // 把最后剩下的不足batchSize大小
    if (dataList.size() > 0) {
      doBatchSave(dataList);
    }

    long endTime = System.currentTimeMillis();
    log.info("耗时(秒):" + (endTime - beginTime) / 1000);
  }