【转】 大数据量的excel文件读取——excel2007(含代码及示例)

转载 2011年02月08日 13:59:00

大数据量的excel文件读取——excel2007(含代码及示例)

转自:http://www.javaeye.com/topic/624969

 

excel2007文件格式与之前版本不同,之前版本采用的是微软自己的存储格式。07版内容的存储采用XML格式,所以,理所当然的,对大数据量的 xlsx文件的读取采用的也是XML的处理方式SAX。

    同之前的版本一样,大数据量文件的读取采用的是事件模型eventusermodel。usermodel模式需要将文件一次性全部读到内存中,07版的既然采用的存储模式是xml,解析用的DOM方式也是如此,这种模式操作简单,容易上手,但是对于大量数据占用的内存也是相当可观,在Eclipse中经常出现内存溢出。

    下面就是采用eventusermodel对07excel文件读取。

    同上篇,我将当前行的单元格数据存储到List中,抽象出 optRows 方法,该方法会在每行末尾时调用,方法参数为当前行索引curRow(int型)及存有行内单元格数据的List。继承类只需实现该行级方法即可。

 

    经测试,对12万条数据,7M大小的文件也能正常运行。无需设置vm的内存空间。

 

    excel读取采用的API为POI3.6,使用前先下载此包,若运行中出现其他依赖包不存在,请下载相应依赖包。

 

抽象类:XxlsAbstract ,作用:遍历excel文件,提供行级操作方法 optRows

Java代码 复制代码
  1. package com.gaosheng.util.xls;   
  2.   
  3. import java.io.InputStream;   
  4. import java.sql.SQLException;   
  5. import java.util.ArrayList;   
  6. import java.util.Iterator;   
  7. import java.util.List;   
  8.   
  9. import org.apache.poi.xssf.eventusermodel.XSSFReader;   
  10. import org.apache.poi.xssf.model.SharedStringsTable;   
  11. import org.apache.poi.xssf.usermodel.XSSFRichTextString;   
  12. import org.apache.poi.openxml4j.opc.OPCPackage;   
  13. import org.xml.sax.Attributes;   
  14. import org.xml.sax.InputSource;   
  15. import org.xml.sax.SAXException;   
  16. import org.xml.sax.XMLReader;   
  17. import org.xml.sax.helpers.DefaultHandler;   
  18. import org.xml.sax.helpers.XMLReaderFactory;   
  19.   
  20. /**  
  21.  * XSSF and SAX (Event API)  
  22.  */  
  23. public abstract class XxlsAbstract extends DefaultHandler {   
  24.     private SharedStringsTable sst;   
  25.     private String lastContents;   
  26.     private boolean nextIsString;   
  27.   
  28.     private int sheetIndex = -1;   
  29.     private List<String> rowlist = new ArrayList<String>();   
  30.     private int curRow = 0;   
  31.     private int curCol = 0;   
  32.   
  33.     //excel记录行操作方法,以行索引和行元素列表为参数,对一行元素进行操作,元素为String类型   
  34. //  public abstract void optRows(int curRow, List<String> rowlist) throws SQLException ;   
  35.        
  36.     //excel记录行操作方法,以sheet索引,行索引和行元素列表为参数,对sheet的一行元素进行操作,元素为String类型   
  37.     public abstract void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException;   
  38.        
  39.     //只遍历一个sheet,其中sheetId为要遍历的sheet索引,从1开始,1-3   
  40.     public void processOneSheet(String filename,int sheetId) throws Exception {   
  41.         OPCPackage pkg = OPCPackage.open(filename);   
  42.         XSSFReader r = new XSSFReader(pkg);   
  43.         SharedStringsTable sst = r.getSharedStringsTable();   
  44.            
  45.         XMLReader parser = fetchSheetParser(sst);   
  46.   
  47.         // rId2 found by processing the Workbook   
  48.         // 根据 rId# 或 rSheet# 查找sheet   
  49.         InputStream sheet2 = r.getSheet("rId"+sheetId);   
  50.         sheetIndex++;   
  51.         InputSource sheetSource = new InputSource(sheet2);   
  52.         parser.parse(sheetSource);   
  53.         sheet2.close();   
  54.     }   
  55.   
  56.     /**  
  57.      * 遍历 excel 文件  
  58.      */  
  59.     public void process(String filename) throws Exception {   
  60.         OPCPackage pkg = OPCPackage.open(filename);   
  61.         XSSFReader r = new XSSFReader(pkg);   
  62.         SharedStringsTable sst = r.getSharedStringsTable();   
  63.   
  64.         XMLReader parser = fetchSheetParser(sst);   
  65.   
  66.         Iterator<InputStream> sheets = r.getSheetsData();   
  67.         while (sheets.hasNext()) {   
  68.             curRow = 0;   
  69.             sheetIndex++;   
  70.             InputStream sheet = sheets.next();   
  71.             InputSource sheetSource = new InputSource(sheet);   
  72.             parser.parse(sheetSource);   
  73.             sheet.close();   
  74.         }   
  75.     }   
  76.   
  77.     public XMLReader fetchSheetParser(SharedStringsTable sst)   
  78.             throws SAXException {   
  79.         XMLReader parser = XMLReaderFactory   
  80.                 .createXMLReader("org.apache.xerces.parsers.SAXParser");   
  81.         this.sst = sst;   
  82.         parser.setContentHandler(this);   
  83.         return parser;   
  84.     }   
  85.   
  86.     public void startElement(String uri, String localName, String name,   
  87.             Attributes attributes) throws SAXException {   
  88.         // c => 单元格   
  89.         if (name.equals("c")) {   
  90.             // 如果下一个元素是 SST 的索引,则将nextIsString标记为true   
  91.             String cellType = attributes.getValue("t");   
  92.             if (cellType != null && cellType.equals("s")) {   
  93.                 nextIsString = true;   
  94.             } else {   
  95.                 nextIsString = false;   
  96.             }   
  97.         }   
  98.         // 置空   
  99.         lastContents = "";   
  100.     }   
  101.   
  102.     public void endElement(String uri, String localName, String name)   
  103.             throws SAXException {   
  104.         // 根据SST的索引值的到单元格的真正要存储的字符串   
  105.         // 这时characters()方法可能会被调用多次   
  106.         if (nextIsString) {   
  107.             try {   
  108.                 int idx = Integer.parseInt(lastContents);   
  109.                 lastContents = new XSSFRichTextString(sst.getEntryAt(idx))   
  110.                         .toString();   
  111.             } catch (Exception e) {   
  112.   
  113.             }   
  114.         }   
  115.   
  116.         // v => 单元格的值,如果单元格是字符串则v标签的值为该字符串在SST中的索引   
  117.         // 将单元格内容加入rowlist中,在这之前先去掉字符串前后的空白符   
  118.         if (name.equals("v")) {   
  119.             String value = lastContents.trim();   
  120.             value = value.equals("")?" ":value;   
  121.             rowlist.add(curCol, value);   
  122.             curCol++;   
  123.         }else {   
  124.             //如果标签名称为 row ,这说明已到行尾,调用 optRows() 方法   
  125.             if (name.equals("row")) {   
  126.                 try {   
  127.                     optRows(sheetIndex,curRow,rowlist);   
  128.                 } catch (SQLException e) {   
  129.                     e.printStackTrace();   
  130.                 }   
  131.                 rowlist.clear();   
  132.                 curRow++;   
  133.                 curCol = 0;   
  134.             }   
  135.         }   
  136.     }   
  137.   
  138.     public void characters(char[] ch, int start, int length)   
  139.             throws SAXException {   
  140.         //得到单元格内容的值   
  141.         lastContents += new String(ch, start, length);   
  142.     }   
  143. }  

 

继承类:XxlsBig,作用:将数据转出到数据库临时表

Java代码 复制代码
  1. package com.gaosheng.util.examples.xls;   
  2.   
  3. import java.io.FileInputStream;   
  4. import java.io.IOException;   
  5. import java.sql.Connection;   
  6. import java.sql.DriverManager;   
  7. import java.sql.PreparedStatement;   
  8. import java.sql.SQLException;   
  9. import java.sql.Statement;   
  10. import java.util.List;   
  11. import java.util.Properties;   
  12.   
  13. import com.gaosheng.util.xls.XxlsAbstract;   
  14.   
  15. public class XxlsBig extends XxlsAbstract {   
  16.     public static void main(String[] args) throws Exception {   
  17.         XxlsBig howto = new XxlsBig("temp_table");   
  18.         howto.processOneSheet("F:/new.xlsx",1);   
  19.         howto.process("F:/new.xlsx");   
  20.         howto.close();   
  21.     }   
  22.        
  23.     public XxlsBig(String tableName) throws SQLException{   
  24.         this.conn = getNew_Conn();   
  25.         this.statement = conn.createStatement();   
  26.         this.tableName = tableName;   
  27.     }   
  28.   
  29.     private Connection conn = null;   
  30.     private Statement statement = null;   
  31.     private PreparedStatement newStatement = null;   
  32.   
  33.     private String tableName = "temp_table";   
  34.     private boolean create = true;   
  35.        
  36.     public void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException {   
  37.         if (sheetIndex == 0 && curRow == 0) {   
  38.             StringBuffer preSql = new StringBuffer("insert into " + tableName   
  39.                     + " values(");   
  40.             StringBuffer table = new StringBuffer("create table " + tableName   
  41.                     + "(");   
  42.             int c = rowlist.size();   
  43.             for (int i = 0; i < c; i++) {   
  44.                 preSql.append("?,");   
  45.                 table.append(rowlist.get(i));   
  46.                 table.append("  varchar2(100) ,");   
  47.             }   
  48.   
  49.             table.deleteCharAt(table.length() - 1);   
  50.             preSql.deleteCharAt(preSql.length() - 1);   
  51.             table.append(")");   
  52.             preSql.append(")");   
  53.             if (create) {   
  54.                 statement = conn.createStatement();   
  55.                 try{   
  56.                     statement.execute("drop table "+tableName);   
  57.                 }catch(Exception e){   
  58.                        
  59.                 }finally{   
  60.                     System.out.println("表 "+tableName+" 删除成功");   
  61.                 }   
  62.                 if (!statement.execute(table.toString())) {   
  63.                     System.out.println("创建表 "+tableName+" 成功");   
  64.                     // return;   
  65.                 } else {   
  66.                     System.out.println("创建表 "+tableName+" 失败");   
  67.                     return;   
  68.                 }   
  69.             }   
  70.             conn.setAutoCommit(false);   
  71.             newStatement = conn.prepareStatement(preSql.toString());   
  72.   
  73.         } else if(curRow>0) {   
  74.             // 一般行   
  75.             int col = rowlist.size();   
  76.             for (int i = 0; i < col; i++) {   
  77.                 newStatement.setString(i + 1, rowlist.get(i).toString());   
  78.             }   
  79.             newStatement.addBatch();   
  80.             if (curRow % 1000 == 0) {   
  81.                 newStatement.executeBatch();   
  82.                 conn.commit();   
  83.             }   
  84.         }   
  85.     }   
  86.        
  87.     private static Connection getNew_Conn() {   
  88.         Connection conn = null;   
  89.         Properties props = new Properties();   
  90.         FileInputStream fis = null;   
  91.   
  92.         try {   
  93.             fis = new FileInputStream("D:/database.properties");   
  94.             props.load(fis);   
  95.             DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());   
  96.             // String jdbcURLString =   
  97.             // "jdbc:oracle:thin:@192.168.0.28:1521:orcl";   
  98.             StringBuffer jdbcURLString = new StringBuffer();   
  99.             jdbcURLString.append("jdbc:oracle:thin:@");   
  100.             jdbcURLString.append(props.getProperty("host"));   
  101.             jdbcURLString.append(":");   
  102.             jdbcURLString.append(props.getProperty("port"));   
  103.             jdbcURLString.append(":");   
  104.             jdbcURLString.append(props.getProperty("database"));   
  105.             conn = DriverManager.getConnection(jdbcURLString.toString(), props   
  106.                     .getProperty("user"), props.getProperty("password"));   
  107.         } catch (Exception e) {   
  108.             e.printStackTrace();   
  109.         } finally {   
  110.             try {   
  111.                 fis.close();   
  112.             } catch (IOException e) {   
  113.                 e.printStackTrace();   
  114.             }   
  115.         }   
  116.         return conn;   
  117.     }   
  118.        
  119.     public int close() {   
  120.         try {   
  121.             newStatement.executeBatch();   
  122.             conn.commit();   
  123.             System.out.println("数据写入完毕");   
  124.             this.newStatement.close();   
  125.             this.statement.close();   
  126.             this.conn.close();   
  127.             return 1;   
  128.         } catch (SQLException e) {   
  129.             return 0;   
  130.         }   
  131.     }   
  132. }  

 继承类:XxlsPrint,作用:将数据输出到控制台

Java代码 复制代码
  1. package com.gaosheng.util.examples.xls;   
  2.   
  3. import java.sql.SQLException;   
  4. import java.util.List;   
  5.   
  6. import com.gaosheng.util.xls.XxlsAbstract;   
  7.   
  8. public class XxlsPrint extends XxlsAbstract {   
  9.   
  10.     @Override  
  11.     public void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException {   
  12.         for (int i = 0; i < rowlist.size(); i++) {   
  13.             System.out.print("'" + rowlist.get(i) + "',");   
  14.         }   
  15.         System.out.println();   
  16.     }   
  17.   
  18.     public static void main(String[] args) throws Exception {   
  19.         XxlsPrint howto = new XxlsPrint();   
  20.         howto.processOneSheet("F:/new.xlsx",1);   
  21. //      howto.processAllSheets("F:/new.xlsx");   
  22.     }   
  23. }  

 源代码在附件中,还包含了说明文件、数据库配置文件、以及整合xls文件和xlsx文件读取的类:Xls2Do。

相关文章推荐

Delphi7高级应用开发随书源码

  • 2003年04月30日 00:00
  • 676KB
  • 下载

Java读取操作大数据excel

工作需要,读取大数据量的excel。用Apache poi的普通模式读取,会抛内存溢出。查询文档得知有另外一种模式--用户模式。该模式不会一下子整个文件load进来放在内存里,而是一行一行的读取,这样...

JAVA读大数据量Excel

JAVA读大数据量Excel,对Excel各种类型的单元格数据进行分类处理,最后封装到一个类中。 只需要传入Excel文件即可进行逐行读取内容...

sax解析excel导入数据到mysql

当Excel数据量比较小的时候可以直接通过poi导入Excel。 但是当数据量过大时,生成WorkBook过程中会直接造成超内存,这时候可以通过sax解析Excel的xml格式。 sax是如何解析Ex...

POI读写海量Excel

目前处理Excel的开源javaAPI主要有两种,一是Jxl(Java Excel API),Jxl只支持Excel2003以下的版本。另外一种是Apache的Jakarta POI,相比于Jxl,P...

POI使用SAX处理大量数据的xlsx格式的Excel文件

POI使用SAX处理大量数据的xlsx格式的Excel文件 博客分类: Java       POI在处理Excel方面确实比较方便,但是当Excel数据量比较大的时候...

Delphi7高级应用开发随书源码

  • 2003年04月30日 00:00
  • 676KB
  • 下载

POI事件驱动模式读写Excel和格式设置及2007EXCEL解析SAXParser类找不到

POI事件驱动模式读写Excel 目前处理Excel的开源javaAPI主要有两种,一是Jxl(Java Excel API),Jxl只支持Excel2003以下的版本。另外一种是Apache的Jak...

利用POI框架的SAX方式处理大数据2007版Excel(xlsx)

http://m.blog.csdn.net/article/details?id=50747122 1.开发环境:jdk7,poi3.13 如图: ...

POI使用SAX处理大量数据的xlsx格式的Excel文件

POI在处理Excel方面确实比较方便,但是当Excel数据量比较大的时候,使用POI处理就会导致java.lang.OutOfMemoryError: Java heap space的错误,当有大量...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:【转】 大数据量的excel文件读取——excel2007(含代码及示例)
举报原因:
原因补充:

(最多只允许输入30个字)