/** * 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); }
/** 导入 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); }