【转】 大数据量的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。

大数据量的excel文件读取——excel2003及之前版本(含代码及示例)

http://www.iteye.com/topic/624967
  • zhuhong_0308
  • zhuhong_0308
  • 2013年11月20日 11:46
  • 668

excel大数据量处理小结(excel07)

前端时间因为项目中excel(2007)上传下载的所要支持的数据量剧增,所以研究学习了下大数据量excel的处理方式,并应用到product上去,这里简单recap下.主要有三点。 一 excel2...
  • huang798807481
  • huang798807481
  • 2015年05月20日 10:45
  • 1643

pb 数据窗口导出到excel2007

oleobject loo_excel,loo_sheet  string ls_txt,ls_xls,ls_xls_new loo_excel = create oleobject if loo_e...
  • xjq778
  • xjq778
  • 2015年06月27日 14:12
  • 692

excel2007如何转换成pdf格式

工作中常常会遇到需要将Excel转换成PDF的时候,但是大多数人都不知道如何转换,网上虽然有很多这类软件但不是每个转换效果都好,因此小编根据自己的转换经验跟大家分享一下我的转换方法。 首先通过百度进...
  • wowpdf
  • wowpdf
  • 2016年05月03日 18:03
  • 270

解析xlsx文件---Java读取Excel2007

关于Java读取Excel2007的文章在Google、百度上搜索一下,没有太好的例子,实现的也不算太好。查看了一下Poi,最新的 POI 3.5 beta 4 支持读写 Excel2007和PP...
  • tianyazaiheruan
  • tianyazaiheruan
  • 2013年10月10日 09:04
  • 10145

vc操作excel历程(vs2008+excel2007)

概要 由于要用到许多表格和图形,所以选择了excel2007,为了更好的操作excel2007,所以选择了vc2008. 本来想用excel12.h,相关方法实现的。后来在发现生成excel1...
  • zeng133
  • zeng133
  • 2012年04月25日 15:51
  • 2684

EPPLUS 操作Excel2007 入门实例

目前正在进行excel com访问方式改造,涉及到要操作excel并且不需要在服务器上安装office。经过长时间在网上一通搜索,找到两种评价不错的第三方程序: 1、操作Excel2003 首选...
  • gbyzzj
  • gbyzzj
  • 2013年05月18日 18:52
  • 930

/*------------------------------------------------- //目前真正最全的OLE操作Excel的完整代码 //版本:2007.01.15.01 //C+

/*------------------------------------------------- //目前真正最全的OLE操作Excel的完整代码 //版本:2007.01.15.01 /...
  • slinace
  • slinace
  • 2017年10月15日 19:49
  • 106

C#导出图片到EXCEL

这几天在做个项目,将图片导出到EXCEL中,网上找了半天发现有个类,但自己一直不知道如何引用INTER类,结果郁闷了半天,最后自己在VS命令提示符里面自己生成了个EXCEL.DLL文件,然后从新引用,...
  • tq84020519
  • tq84020519
  • 2009年07月11日 22:50
  • 8554

Excel在统计分析中的应用—第十章—方差分析-单因素方差分析工具的运用

接上篇,单因素方差分析,可直接使用Excel数据分析工具包中的“单因素方差分析工具”,简单易用。...
  • hpdlzu80100
  • hpdlzu80100
  • 2017年12月16日 22:18
  • 160
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:【转】 大数据量的excel文件读取——excel2007(含代码及示例)
举报原因:
原因补充:

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