2017-06-21 19:00:00 ajing4030 阅读数 837
  • 基于SSM的POI导入导出Excel实战

    本课程将给大家分享如何基于SSM实现POI导入导出Excel,并讲解目前企业级JavaWeb应用mvc三层模式的开发流程,可让初学者或者职场萌新掌握如何基于SSM整合第三方框架并采用mvc三层开发模式实现自己的业务模块!

    1272 人正在学习 去看看 钟林森

2007格式:

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

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

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

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

  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.     private int preCol = 0;     //上一列列索引  
  33.     private int titleRow = 0;   //标题行,一般情况下为0  
  34.     private int rowsize = 0;    //列数  
  35.       
  36.     //excel记录行操作方法,以行索引和行元素列表为参数,对一行元素进行操作,元素为String类型  
  37. //  public abstract void optRows(int curRow, List<String> rowlist) throws SQLException ;  
  38.       
  39.     //excel记录行操作方法,以sheet索引,行索引和行元素列表为参数,对sheet的一行元素进行操作,元素为String类型  编写自己的业务
  40.     public abstract void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException;  
  41.       
  42.     //只遍历一个sheet,其中sheetId为要遍历的sheet索引,从1开始,1-3  
  43.     public void processOneSheet(String filename,int sheetId) throws Exception {  
  44.         OPCPackage pkg = OPCPackage.open(filename);  
  45.         XSSFReader r = new XSSFReader(pkg);  
  46.         SharedStringsTable sst = r.getSharedStringsTable();  
  47.           
  48.         XMLReader parser = fetchSheetParser(sst);  
  49.   
  50.         // rId2 found by processing the Workbook  
  51.         // 根据 rId# 或 rSheet# 查找sheet  
  52.         InputStream sheet2 = r.getSheet("rId"+sheetId);  
  53.         sheetIndex++;  
  54.         InputSource sheetSource = new InputSource(sheet2);  
  55.         parser.parse(sheetSource);  
  56.         sheet2.close();  
  57.     }  
  58.   
  59.     /** 
  60.      * 遍历 excel 文件 
  61.      */  
  62.     public void process(String filename) throws Exception {  
  63.         OPCPackage pkg = OPCPackage.open(filename);  
  64.         XSSFReader r = new XSSFReader(pkg);  
  65.         SharedStringsTable sst = r.getSharedStringsTable();  
  66.   
  67.         XMLReader parser = fetchSheetParser(sst);  
  68.   
  69.         Iterator<InputStream> sheets = r.getSheetsData();  
  70.         while (sheets.hasNext()) {  
  71.             curRow = 0;  
  72.             sheetIndex++;  
  73.             InputStream sheet = sheets.next();  
  74.             InputSource sheetSource = new InputSource(sheet);  
  75.             parser.parse(sheetSource);  
  76.             sheet.close();  
  77.         }  
  78.     }  
  79.   
  80.     public XMLReader fetchSheetParser(SharedStringsTable sst)  
  81.             throws SAXException {  
  82.         XMLReader parser = XMLReaderFactory  
  83.                 .createXMLReader("org.apache.xerces.parsers.SAXParser");  
  84.         this.sst = sst;  
  85.         parser.setContentHandler(this);  
  86.         return parser;  
  87.     }  
  88.   
  89.     public void startElement(String uri, String localName, String name,  
  90.             Attributes attributes) throws SAXException {  
  91.         // c => 单元格  
  92.         if (name.equals("c")) {  
  93.             // 如果下一个元素是 SST 的索引,则将nextIsString标记为true  
  94.             String cellType = attributes.getValue("t");  
  95.             String rowStr = attributes.getValue("r");  
  96.             curCol = this.getRowIndex(rowStr);  
  97.             if (cellType != null && cellType.equals("s")) {  
  98.                 nextIsString = true;  
  99.             } else {  
  100.                 nextIsString = false;  
  101.             }  
  102.         }  
  103.         // 置空  
  104.         lastContents = "";  
  105.     }  
  106.   
  107.     public void endElement(String uri, String localName, String name)  
  108.             throws SAXException {  
  109.         // 根据SST的索引值的到单元格的真正要存储的字符串  
  110.         // 这时characters()方法可能会被调用多次  
  111.         if (nextIsString) {  
  112.             try {  
  113.                 int idx = Integer.parseInt(lastContents);  
  114.                 lastContents = new XSSFRichTextString(sst.getEntryAt(idx))  
  115.                         .toString();  
  116.             } catch (Exception e) {  
  117.   
  118.             }  
  119.         }  
  120.   
  121.         // v => 单元格的值,如果单元格是字符串则v标签的值为该字符串在SST中的索引  
  122.         // 将单元格内容加入rowlist中,在这之前先去掉字符串前后的空白符  
  123.         if (name.equals("v")) {  
  124.             String value = lastContents.trim();  
  125.             value = value.equals("")?" ":value;  
  126.             int cols = curCol-preCol;  
  127.             if (cols>1){  
  128.                 for (int i = 0;i < cols-1;i++){  
  129.                     rowlist.add(preCol,"");  
  130.                 }  
  131.             }  
  132.             preCol = curCol;  
  133.             rowlist.add(curCol-1, value);  
  134.         }else {  
  135.             //如果标签名称为 row ,这说明已到行尾,调用 optRows() 方法  
  136.             if (name.equals("row")) {  
  137.                 int tmpCols = rowlist.size();  
  138.                 if(curRow>this.titleRow && tmpCols<this.rowsize){  
  139.                     for (int i = 0;i < this.rowsize-tmpCols;i++){  
  140.                         rowlist.add(rowlist.size(), "");  
  141.                     }  
  142.                 }  
  143.                 try {  
  144.                     optRows(sheetIndex,curRow,rowlist);  
  145.                 } catch (SQLException e) {  
  146.                     e.printStackTrace();  
  147.                 }  
  148.                 if(curRow==this.titleRow){  
  149.                     this.rowsize = rowlist.size();  
  150.                 }  
  151.                 rowlist.clear();  
  152.                 curRow++;  
  153.                 curCol = 0;  
  154.                 preCol = 0;  
  155.             }  
  156.         }  
  157.     }  
  158.   
  159.     public void characters(char[] ch, int start, int length)  
  160.             throws SAXException {  
  161.         //得到单元格内容的值  
  162.         lastContents += new String(ch, start, length);  
  163.     }  
  164.       
  165.     //得到列索引,每一列c元素的r属性构成为字母加数字的形式,字母组合为列索引,数字组合为行索引,  
  166.     //如AB45,表示为第(A-A+1)*26+(B-A+1)*26列,45行  
  167.     public int getRowIndex(String rowStr){  
  168.         rowStr = rowStr.replaceAll("[^A-Z]""");  
  169.         byte[] rowAbc = rowStr.getBytes();  
  170.         int len = rowAbc.length;  
  171.         float num = 0;  
  172.         for (int i=0;i<len;i++){  
  173.             num += (rowAbc[i]-'A'+1)*Math.pow(26,len-i-1 );  
  174.         }  
  175.         return (int) num;  
  176.     }  
  177.   
  178.     public int getTitleRow() {  
  179.         return titleRow;  
  180.     }  
  181.   
  182.     public void setTitleRow(int titleRow) {  
  183.         this.titleRow = titleRow;  
  184.     }  
  185. }  



20003格式:

  1. package com.gaosheng.util.xls;  
  2. import java.io.FileInputStream;  
  3. import java.io.FileNotFoundException;  
  4. import java.io.IOException;  
  5. import java.io.PrintStream;  
  6. import java.sql.SQLException;  
  7. import java.util.ArrayList;  
  8. import java.util.List;  
  9.   
  10. import org.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener;  
  11. import org.apache.poi.hssf.eventusermodel.HSSFEventFactory;  
  12. import org.apache.poi.hssf.eventusermodel.HSSFListener;  
  13. import org.apache.poi.hssf.eventusermodel.HSSFRequest;  
  14. import org.apache.poi.hssf.eventusermodel.MissingRecordAwareHSSFListener;  
  15. import org.apache.poi.hssf.eventusermodel.EventWorkbookBuilder.SheetRecordCollectingListener;  
  16. import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord;  
  17. import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord;  
  18. import org.apache.poi.hssf.model.HSSFFormulaParser;  
  19. import org.apache.poi.hssf.record.BOFRecord;  
  20. import org.apache.poi.hssf.record.BlankRecord;  
  21. import org.apache.poi.hssf.record.BoolErrRecord;  
  22. import org.apache.poi.hssf.record.BoundSheetRecord;  
  23. import org.apache.poi.hssf.record.FormulaRecord;  
  24. import org.apache.poi.hssf.record.LabelRecord;  
  25. import org.apache.poi.hssf.record.LabelSSTRecord;  
  26. import org.apache.poi.hssf.record.NoteRecord;  
  27. import org.apache.poi.hssf.record.NumberRecord;  
  28. import org.apache.poi.hssf.record.RKRecord;  
  29. import org.apache.poi.hssf.record.Record;  
  30. import org.apache.poi.hssf.record.SSTRecord;  
  31. import org.apache.poi.hssf.record.StringRecord;  
  32. import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
  33. import org.apache.poi.poifs.filesystem.POIFSFileSystem;  
  34.   
  35. public abstract class HxlsAbstract implements HSSFListener {  
  36.     private int minColumns;  
  37.     private POIFSFileSystem fs;  
  38.     private PrintStream output;  
  39.   
  40.     private int lastRowNumber;  
  41.     private int lastColumnNumber;  
  42.   
  43.     /** Should we output the formula, or the value it has? */  
  44.     private boolean outputFormulaValues = true;  
  45.   
  46.     /** For parsing Formulas */  
  47.     private SheetRecordCollectingListener workbookBuildingListener;  
  48.     private HSSFWorkbook stubWorkbook;  
  49.   
  50.     // Records we pick up as we process  
  51.     private SSTRecord sstRecord;  
  52.     private FormatTrackingHSSFListener formatListener;  
  53.   
  54.     /** So we known which sheet we're on */  
  55.     private int sheetIndex = -1;  
  56.     private BoundSheetRecord[] orderedBSRs;  
  57.     @SuppressWarnings("unchecked")  
  58.     private ArrayList boundSheetRecords = new ArrayList();  
  59.   
  60.     // For handling formulas with string results  
  61.     private int nextRow;  
  62.     private int nextColumn;  
  63.     private boolean outputNextStringRecord;  
  64.   
  65.     private int curRow;  
  66.     private List<String> rowlist;  
  67.     @SuppressWarnings"unused")  
  68.     private String sheetName;  
  69.   
  70.     public HxlsAbstract(POIFSFileSystem fs)  
  71.             throws SQLException {  
  72.         this.fs = fs;  
  73.         this.output = System.out;  
  74.         this.minColumns = -1;  
  75.         this.curRow = 0;  
  76.         this.rowlist = new ArrayList<String>();  
  77.     }  
  78.   
  79.     public HxlsAbstract(String filename) throws IOException,  
  80.             FileNotFoundException, SQLException {  
  81.         this(new POIFSFileSystem(new FileInputStream(filename)));  
  82.     }  
  83.       
  84.     //excel记录行操作方法,以行索引和行元素列表为参数,对一行元素进行操作,元素为String类型  
  85. //  public abstract void optRows(int curRow, List<String> rowlist) throws SQLException ;  
  86.       
  87.     //excel记录行操作方法,以sheet索引,行索引和行元素列表为参数,对sheet的一行元素进行操作,元素为String类型  
  88.     public abstract void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException;  
  89.       
  90.     /** 
  91.      * 遍历 excel 文件 
  92.      */  
  93.     public void process() throws IOException {  
  94.         MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(  
  95.                 this);  
  96.         formatListener = new FormatTrackingHSSFListener(listener);  
  97.   
  98.         HSSFEventFactory factory = new HSSFEventFactory();  
  99.         HSSFRequest request = new HSSFRequest();  
  100.   
  101.         if (outputFormulaValues) {  
  102.             request.addListenerForAllRecords(formatListener);  
  103.         } else {  
  104.             workbookBuildingListener = new SheetRecordCollectingListener(  
  105.                     formatListener);  
  106.             request.addListenerForAllRecords(workbookBuildingListener);  
  107.         }  
  108.   
  109.         factory.processWorkbookEvents(request, fs);  
  110.     }  
  111.       
  112.     /** 
  113.      * HSSFListener 监听方法,处理 Record 
  114.      */  
  115.     @SuppressWarnings("unchecked")  
  116.     public void processRecord(Record record) {  
  117.         int thisRow = -1;  
  118.         int thisColumn = -1;  
  119.         String thisStr = null;  
  120.         String value = null;  
  121.           
  122.         switch (record.getSid()) {  
  123.         case BoundSheetRecord.sid:  
  124.             boundSheetRecords.add(record);  
  125.             break;  
  126.         case BOFRecord.sid:  
  127.             BOFRecord br = (BOFRecord) record;  
  128.             if (br.getType() == BOFRecord.TYPE_WORKSHEET) {  
  129.                 // Create sub workbook if required  
  130.                 if (workbookBuildingListener != null && stubWorkbook == null) {  
  131.                     stubWorkbook = workbookBuildingListener  
  132.                             .getStubHSSFWorkbook();  
  133.                 }  
  134.   
  135.                 // Works by ordering the BSRs by the location of  
  136.                 // their BOFRecords, and then knowing that we  
  137.                 // process BOFRecords in byte offset order  
  138.                 sheetIndex++;  
  139.                 if (orderedBSRs == null) {  
  140.                     orderedBSRs = BoundSheetRecord  
  141.                             .orderByBofPosition(boundSheetRecords);  
  142.                 }  
  143.                 sheetName = orderedBSRs[sheetIndex].getSheetname();  
  144.             }  
  145.             break;  
  146.   
  147.         case SSTRecord.sid:  
  148.             sstRecord = (SSTRecord) record;  
  149.             break;  
  150.   
  151.         case BlankRecord.sid:  
  152.             BlankRecord brec = (BlankRecord) record;  
  153.   
  154.             thisRow = brec.getRow();  
  155.             thisColumn = brec.getColumn();  
  156.             thisStr = "";  
  157.             break;  
  158.         case BoolErrRecord.sid:  
  159.             BoolErrRecord berec = (BoolErrRecord) record;  
  160.   
  161.             thisRow = berec.getRow();  
  162.             thisColumn = berec.getColumn();  
  163.             thisStr = "";  
  164.             break;  
  165.   
  166.         case FormulaRecord.sid:  
  167.             FormulaRecord frec = (FormulaRecord) record;  
  168.   
  169.             thisRow = frec.getRow();  
  170.             thisColumn = frec.getColumn();  
  171.   
  172.             if (outputFormulaValues) {  
  173.                 if (Double.isNaN(frec.getValue())) {  
  174.                     // Formula result is a string  
  175.                     // This is stored in the next record  
  176.                     outputNextStringRecord = true;  
  177.                     nextRow = frec.getRow();  
  178.                     nextColumn = frec.getColumn();  
  179.                 } else {  
  180.                     thisStr = formatListener.formatNumberDateCell(frec);  
  181.                 }  
  182.             } else {  
  183.                 thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook,  
  184.                         frec.getParsedExpression()) + '"';  
  185.             }  
  186.             break;  
  187.         case StringRecord.sid:  
  188.             if (outputNextStringRecord) {  
  189.                 // String for formula  
  190.                 StringRecord srec = (StringRecord) record;  
  191.                 thisStr = srec.getString();  
  192.                 thisRow = nextRow;  
  193.                 thisColumn = nextColumn;  
  194.                 outputNextStringRecord = false;  
  195.             }  
  196.             break;  
  197.   
  198.         case LabelRecord.sid:  
  199.             LabelRecord lrec = (LabelRecord) record;  
  200.   
  201.             curRow = thisRow = lrec.getRow();  
  202.             thisColumn = lrec.getColumn();  
  203.             value = lrec.getValue().trim();  
  204.             value = value.equals("")?" ":value;  
  205.             this.rowlist.add(thisColumn, value);  
  206.             break;  
  207.         case LabelSSTRecord.sid:  
  208.             LabelSSTRecord lsrec = (LabelSSTRecord) record;  
  209.   
  210.             curRow = thisRow = lsrec.getRow();  
  211.             thisColumn = lsrec.getColumn();  
  212.             if (sstRecord == null) {  
  213.                 rowlist.add(thisColumn, " ");  
  214.             } else {  
  215.                 value =  sstRecord  
  216.                 .getString(lsrec.getSSTIndex()).toString().trim();  
  217.                 value = value.equals("")?" ":value;  
  218.                 rowlist.add(thisColumn,value);  
  219.             }  
  220.             break;  
  221.         case NoteRecord.sid:  
  222.             NoteRecord nrec = (NoteRecord) record;  
  223.   
  224.             thisRow = nrec.getRow();  
  225.             thisColumn = nrec.getColumn();  
  226.             // TODO: Find object to match nrec.getShapeId()  
  227.             thisStr = '"' + "(TODO)" + '"';  
  228.             break;  
  229.         case NumberRecord.sid:  
  230.             NumberRecord numrec = (NumberRecord) record;  
  231.   
  232.             curRow = thisRow = numrec.getRow();  
  233.             thisColumn = numrec.getColumn();  
  234.             value = formatListener.formatNumberDateCell(numrec).trim();  
  235.             value = value.equals("")?" ":value;  
  236.             // Format  
  237.             rowlist.add(thisColumn, value);  
  238.             break;  
  239.         case RKRecord.sid:  
  240.             RKRecord rkrec = (RKRecord) record;  
  241.   
  242.             thisRow = rkrec.getRow();  
  243.             thisColumn = rkrec.getColumn();  
  244.             thisStr = '"' + "(TODO)" + '"';  
  245.             break;  
  246.         default:  
  247.             break;  
  248.         }  
  249.   
  250.         // 遇到新行的操作  
  251.         if (thisRow != -1 && thisRow != lastRowNumber) {  
  252.             lastColumnNumber = -1;  
  253.         }  
  254.   
  255.         // 空值的操作  
  256.         if (record instanceof MissingCellDummyRecord) {  
  257.             MissingCellDummyRecord mc = (MissingCellDummyRecord) record;  
  258.             curRow = thisRow = mc.getRow();  
  259.             thisColumn = mc.getColumn();  
  260.             rowlist.add(thisColumn," ");  
  261.         }  
  262.   
  263.         // 如果遇到能打印的东西,在这里打印  
  264.         if (thisStr != null) {  
  265.             if (thisColumn > 0) {  
  266.                 output.print(',');  
  267.             }  
  268.             output.print(thisStr);  
  269.         }  
  270.   
  271.         // 更新行和列的值  
  272.         if (thisRow > -1)  
  273.             lastRowNumber = thisRow;  
  274.         if (thisColumn > -1)  
  275.             lastColumnNumber = thisColumn;  
  276.   
  277.         // 行结束时的操作  
  278.         if (record instanceof LastCellOfRowDummyRecord) {  
  279.             if (minColumns > 0) {  
  280.                 // 列值重新置空  
  281.                 if (lastColumnNumber == -1) {  
  282.                     lastColumnNumber = 0;  
  283.                 }  
  284.             }  
  285.             // 行结束时, 调用 optRows() 方法  
  286.             lastColumnNumber = -1;  
  287.             try {  
  288.                 optRows(sheetIndex,curRow, rowlist);  
  289.             } catch (SQLException e) {  
  290.                 e.printStackTrace();  
  291.             }  
  292.             rowlist.clear();  
  293.         }  
  294.     }  
  295. }  


###########大数据导出导出时采用SXSSFWorkbook处理大数据###################
/**创建空模板 利用SXSSF技术,降低内存使用率**/

Workbook wb =  new SXSSFWorkbook(1000);
Sheet sheet = wb.createSheet();

spring在上传excel文件时,有时候会有缓存,即上传的excel解析后会有重复,所有contrllor层时,一定要使用注解@scoper='prototype"

2018-05-10 14:47:47 whandgdh 阅读数 10206
  • 基于SSM的POI导入导出Excel实战

    本课程将给大家分享如何基于SSM实现POI导入导出Excel,并讲解目前企业级JavaWeb应用mvc三层模式的开发流程,可让初学者或者职场萌新掌握如何基于SSM整合第三方框架并采用mvc三层开发模式实现自己的业务模块!

    1272 人正在学习 去看看 钟林森

今天在做excel文件上传时,发现数据量超过10万条后,系统会出现内存溢出。
跟踪代码发现程序卡在如下代码这里:

Workbook workbook=WorkbookFactory.create(new File(path));

通过poi的api发现此方法属于能对excel读写,但是内存消耗大,速度较慢。
在poi的官方文档中找到了读取大量数据的方法,但只能读数据,不能进行写操作。
代码整理如下


import java.io.InputStream;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.junit.Test;
import org.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

/**
 * 数据量比较大(8万条以上)的excel文件解析,将excel文件解析为 行列坐标-值的形式存入map中,此方式速度快,内存耗损小 但只能读取excle文件
 * 提供处理单个sheet方法 processOneSheet(String  filename) 以及处理多个sheet方法 processAllSheets(String  filename)
 * 只需传入文件路径+文件名即可  调用处理方法结束后,只需 接收LargeExcelFileReadUtil.getRowContents()返回值即可获得解析后的数据
 *
 */
public class LargeExcelFileReadUtil  {

    private  LinkedHashMap<String, String>rowContents=new LinkedHashMap<String, String>(); 
    private  SheetHandler sheetHandler;

public LinkedHashMap<String, String> getRowContents() {
        return rowContents;
    }
    public void setRowContents(LinkedHashMap<String, String> rowContents) {
        this.rowContents = rowContents;
    }

    public SheetHandler getSheetHandler() {
        return sheetHandler;
    }
    public void setSheetHandler(SheetHandler sheetHandler) {
        this.sheetHandler = sheetHandler;
    }
    //处理一个sheet
    public void processOneSheet(String filename) throws Exception {
        InputStream sheet2=null;
        OPCPackage pkg =null;
        try {
                pkg = OPCPackage.open(filename);
                XSSFReader r = new XSSFReader(pkg);
                SharedStringsTable sst = r.getSharedStringsTable();
                XMLReader parser = fetchSheetParser(sst);
                sheet2 = r.getSheet("rId1");
                InputSource sheetSource = new InputSource(sheet2);
                parser.parse(sheetSource);
                setRowContents(sheetHandler.getRowContents());
                }catch (Exception e) {
                    e.printStackTrace();
                    throw e;
                    }finally{
                        if(pkg!=null){
                            pkg.close();
                                     }
                        if(sheet2!=null){
                            sheet2.close();
                                        }
                }
    }
//处理多个sheet
    public void processAllSheets(String filename) throws Exception {
        OPCPackage pkg =null;
        InputStream sheet=null;
        try{
                pkg=OPCPackage.open(filename);
                XSSFReader r = new XSSFReader( pkg );
                SharedStringsTable sst = r.getSharedStringsTable();
                XMLReader parser = fetchSheetParser(sst);
                Iterator<InputStream> sheets = r.getSheetsData();
                while(sheets.hasNext()) {
                    System.out.println("Processing new sheet:\n");
                    sheet = sheets.next();
                    InputSource sheetSource = new InputSource(sheet);
                    parser.parse(sheetSource);
                                        }
            }catch (Exception e) {
                    e.printStackTrace();
                    throw e;
                   }finally{
                       if(pkg!=null){
                           pkg.close();
                                 }
                       if(sheet!=null){
                           sheet.close();
                                    }
                            }
    }

    public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
        XMLReader parser =
            XMLReaderFactory.createXMLReader(
                    "com.sun.org.apache.xerces.internal.parsers.SAXParser"
            );
        setSheetHandler(new SheetHandler(sst));
        ContentHandler handler = (ContentHandler) sheetHandler;
        parser.setContentHandler(handler);
        return parser;
    }

    /** 
     * See org.xml.sax.helpers.DefaultHandler javadocs 
     */
    //测试
    @Test
    public  void test ()throws Exception {
       Long time=System.currentTimeMillis();
        LargeExcelFileReadUtil example = new LargeExcelFileReadUtil();

        example.processOneSheet("C:/Users/Desktop/2018041310024112.xlsx");
        Long endtime=System.currentTimeMillis();
        LinkedHashMap<String, String>  map=example.getRowContents();
        Iterator<Entry<String, String>> it= map.entrySet().iterator();
        int count=0;
        String prePos="";
        while (it.hasNext()){
            Map.Entry<String, String> entry=(Map.Entry<String, String>)it.next();
            String pos=entry.getKey();
            if(!pos.substring(1).equals(prePos)){
                prePos=pos.substring(1);
                count++;
            }
            System.out.println(pos+";"+entry.getValue());
        }
        System.out.println("解析数据"+count+"条;耗时"+(endtime-time)/1000+"秒");
    }
}


/**
SheetHandler  类中处理从excle获取的数据,官方文档中 SheetHandler以内部类形式,为保证更新代码减少内部类class文件忘记打包,改为一般java类
*/
import java.util.LinkedHashMap;

import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;

public class SheetHandler  extends DefaultHandler{

    private SharedStringsTable sst;
    private String lastContents;
    private boolean nextIsString;
    private String  cellPosition;
    private  LinkedHashMap<String, String>rowContents=new LinkedHashMap<String, String>(); 

    public LinkedHashMap<String, String> getRowContents() {
        return rowContents;
    }

    public void setRowContents(LinkedHashMap<String, String> rowContents) {
        this.rowContents = rowContents;
    }

    public SheetHandler(SharedStringsTable sst) {
        this.sst = sst;
    }

    public void startElement(String uri, String localName, String name,
            Attributes attributes) throws SAXException {
        if(name.equals("c")) {
         //   System.out.print(attributes.getValue("r") + " - ");
            cellPosition=attributes.getValue("r");
            String cellType = attributes.getValue("t");
            if(cellType != null && cellType.equals("s")) {
                nextIsString = true;
            } else {
                nextIsString = false;
            }
        }
        // 清楚缓存内容
        lastContents = "";
    }

    public void endElement(String uri, String localName, String name)
            throws SAXException {
        if(nextIsString) {
            int idx = Integer.parseInt(lastContents);
            lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
            nextIsString = false;
        }

        if(name.equals("v")) {
//            System.out.println("lastContents:"+cellPosition+";"+lastContents);
            //数据读取结束后,将单元格坐标,内容存入map中
            if(!(cellPosition.length()==2)||(cellPosition.length()==2&&!"1".equals(cellPosition.substring(1)))){//不保存第一行数据
                rowContents.put(cellPosition, lastContents);
            }
        }
    }

    public void characters(char[] ch, int start, int length)
            throws SAXException {
        lastContents += new String(ch, start, length);
    }
}

亲测可用,20多万条数据,4秒解析完,执行结果
这里写图片描述
poi jar包如下:
这里写图片描述

2019-06-18 10:15:26 qq_41546940 阅读数 114
  • 基于SSM的POI导入导出Excel实战

    本课程将给大家分享如何基于SSM实现POI导入导出Excel,并讲解目前企业级JavaWeb应用mvc三层模式的开发流程,可让初学者或者职场萌新掌握如何基于SSM整合第三方框架并采用mvc三层开发模式实现自己的业务模块!

    1272 人正在学习 去看看 钟林森

  Apache POI 是用Java编写的免费开源的跨平台的 Java API,Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。POI为“Poor Obfuscation Implementation”的首字母缩写,意为“简洁版的模糊实现”。简单来说POI就是用来批量操作数据的。

如何实现(SXSSFWorkbook)

POI中的SXSSFWorkbook(XSSF)类,XSSF类采用当数据加工时不是类似前面版本的对象,它可以控制excel数据占用的内存,他通过控制在内存中的行数来实现资源管理,即当创建对象超过了设定的行数,它会自动刷新内存,将数据写入文件,这样导致打印时,占用的CPU,和内存很少。

注意:XSSF类最多支持百万量级别的数据

下面是一个从数据库当中导出大量数据到Excel中的例子:

public void exportBigDataExcel(ValueDataDto valueDataDto, String path)
			throws IOException {
	// 最重要的就是使用SXSSFWorkbook,表示流的方式进行操作
	// 在内存中保持100行,超过100行将被刷新到磁盘
	SXSSFWorkbook wb = new SXSSFWorkbook(100);
	Sheet sh = wb.createSheet(); // 建立新的sheet对象
	Row row = sh.createRow(0);   // 创建第一行对象
	// -----------定义表头-----------
	Cell cel0 = row.createCell(0);
	cel0.setCellValue("1");
	Cell cel2 = row.createCell(1);
	cel2.setCellValue("2");
	Cell cel3 = row.createCell(2);
	cel3.setCellValue("3");
	Cell cel4 = row.createCell(3);
	// ---------------------------
	List<valuedatabean> list = new ArrayList<valuedatabean>();
	// 数据库中存储的数据行
	int page_size = 10000;
	// 求数据库中待导出数据的行数
	int list_count = this.daoUtils.queryListCount(this.valueDataDao
			.queryExportSQL(valueDataDto).get("count_sql"));
	// 根据行数求数据提取次数
	int export_times = list_count % page_size > 0 ? list_count / page_size
			+ 1 : list_count / page_size;
	// 按次数将数据写入文件
	for (int j = 0; j < export_times; j++) {
		list = this.valueDataDao.queryPageList(this.valueDataDao
				.queryExportSQL(valueDataDto).get("list_sql"), j + 1,
				page_size);
		int len = list.size() < page_size ? list.size() : page_size;
<span style="white-space:pre">	</span>	for (int i = 0; i < len; i++) {
			Row row_value = sh.createRow(j * page_size + i + 1);
			Cell cel0_value = row_value.createCell(0);
			cel0_value.setCellValue(list.get(i).getaa());
			Cell cel2_value = row_value.createCell(1);
			cel2_value.setCellValue(list.get(i).getaa());
			Cell cel3_value = row_value.createCell(2);
			cel3_value.setCellValue(list.get(i).getaa_person());
		}
		list.clear(); // 每次存储len行,用完了将内容清空,以便内存可重复利用
	}
	FileOutputStream fileOut = new FileOutputStream(path);
	wb.write(fileOut);
	fileOut.close();
	wb.dispose();
}

这样就可以从数据库中导出大量数据到Excel当中了

2012-09-06 11:58:43 kjkhi 阅读数 181
  • 基于SSM的POI导入导出Excel实战

    本课程将给大家分享如何基于SSM实现POI导入导出Excel,并讲解目前企业级JavaWeb应用mvc三层模式的开发流程,可让初学者或者职场萌新掌握如何基于SSM整合第三方框架并采用mvc三层开发模式实现自己的业务模块!

    1272 人正在学习 去看看 钟林森

POI读取Excel文件有两种方式,一种是使用usermodel方式读取,这种方式的优势是统一接口开发,读取.xls文件的HSSFWorkbook与读取.xlsx文件的XSSFWorkbook两个类都实现了Workbook接口。另外一种是eventusermodel方式读取,这种方式比前面一种方式读取复杂很多,并且对与2003版本和2007版本的Excel处理没有统一接口,需要了解Excel的内部文件组织原理,但是效率却比第一种方式快得多,并却能轻松读取大量数据的Excel文件,而不会把内存溢出。本文也是主要介绍使用eventusermodel方式读取2007版本的Excel文件。

eventusermodel其实是使用了XML的文件格式读取Excel的,因为Excel内部组织也是通过XML实现了(可以把后缀名改为.zip)。

xl\worksheets\sheet1.xml  - 第一个sheet的内容

 

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"><dimension ref="A1:D1"/>
<sheetViews><sheetView workbookViewId="0"><selection sqref="A1:XFD1"/></sheetView></sheetViews>
<sheetFormatPr defaultRowHeight="13.5" x14ac:dyDescent="0.15"/>
	<sheetData>
		<row r="1" spans="1:4" x14ac:dyDescent="0.15">
			<c r="A1" t="s"><v>0</v></c>
			<c r="B1" t="s"><v>1</v></c>
			<c r="C1" t="s"><v>2</v></c>
			<c r="D1" t="s"><v>15</v></c>
		</row>
	</sheetData>
<phoneticPr fontId="1" type="noConversion"/><pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/><pageSetup paperSize="0" orientation="portrait" horizontalDpi="0" verticalDpi="0" copies="0"/></worksheet>

<c />标签表示单元格,t=“s”,说明当前的单元格类型是字符串,那此时<v>0</v>,0则是在sharedStrings.xml的一个索引值。是<si />标签序号。 

 

xl\sharedStrings.xml - Excel文件中字符串的值,如其内容片段

 

<si>
	<t>col1</t><phoneticPr fontId="1" type="noConversion"/>
</si>
<si>
	<t>col2</t><phoneticPr fontId="1" type="noConversion"/>
</si>

 POI的eventusermodel也是通过这样的原理读取Excel文件的。

首先读取Excel文件,取得XSSFReader实例:

 

XSSFReader reader =  new XSSFReader(OPCPackage.open(file));
XMLReader xmlReader = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");

// sharedStrings.xml实体
SharedStringsTable table = reader.getSharedStringsTable();

xmlReader.setContentHandler(new ContentHandler()//实现该接口的一个实例);

InputStream sheet = reader.getSheet("rId"+sheetId);

InputSourcesheetSource  =  new InputSource(sheet )

xmlReader.parse(sheetSource);
 
package net.bingosoft.import4excel.common;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;

public class TestContentHandler extends DefaultHandler{

	private SharedStringsTable table;
	
	private boolean isString;
	
	private String value;
	
	private FileWriter writer;
	
	public TestContentHandler(SharedStringsTable table){
		this.table = table;
	}

	/* (non-Javadoc)
	 * @see org.xml.sax.helpers.DefaultHandler#characters(char[], int, int)
	 */
	@Override
	public void characters(char[] ch, int start, int length)
			throws SAXException {
		value = new String(ch,start,length);
	}

	/* (non-Javadoc)
	 * @see org.xml.sax.helpers.DefaultHandler#endDocument()
	 */
	@Override
	public void endDocument() throws SAXException {
		try {
			writer.flush();
			writer.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/* (non-Javadoc)
	 * @see org.xml.sax.helpers.DefaultHandler#endElement(java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	public void endElement(String uri, String localName, String qName)
			throws SAXException {
		try {
			if(qName.equals("v")){
				if(isString) value = table.getEntryAt(Integer.valueOf(value.trim())).getT();
				writer.write(value+",");
			}
			if(qName.equals("row")){
				writer.write("\r\n");
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/* (non-Javadoc)
	 * @see org.xml.sax.helpers.DefaultHandler#startDocument()
	 */
	@Override
	public void startDocument() throws SAXException {
		try {
			File file = new File("D:/test.txt");
			if(file.exists()) file.delete();
			writer = new FileWriter(file);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/* (non-Javadoc)
	 * @see org.xml.sax.helpers.DefaultHandler#startElement(java.lang.String, java.lang.String, java.lang.String, org.xml.sax.Attributes)
	 */
	@Override
	public void startElement(String uri, String localName, String qName,
			Attributes attributes) throws SAXException {
		if(qName.equals("c")){
			String type = attributes.getValue("t");
			if(StringUtils.isNotBlank(type) && type.equals("s")){
				isString = true;
			}else{
				isString = false;
			}
		}
		value = "";
	}
	
	
}

 

2018-05-22 00:33:37 sinat_30314715 阅读数 10234
  • 基于SSM的POI导入导出Excel实战

    本课程将给大家分享如何基于SSM实现POI导入导出Excel,并讲解目前企业级JavaWeb应用mvc三层模式的开发流程,可让初学者或者职场萌新掌握如何基于SSM整合第三方框架并采用mvc三层开发模式实现自己的业务模块!

    1272 人正在学习 去看看 钟林森

java代码使用poi的API解决在读取大数据量的Excel数据时候内存溢出的问题:首先我需要声明下面的工具类是在老袁博客(https://laoyuan.me/posts/java-read-big-excel-with-poi.html)基础上做了稍微的改造,我将老袁的的工具类需要2个参数改成只需要一个参数就可以完成调用,当然你可以根据你自己的情况使用。
下面是一个工具类,复制到自己的项目中直接调用即可:

1、工具类

package com.xxx.xxx.xxx;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;
/**
 * 解析大数据量Excel工具类
 * @author RobinTime
 *
 */
@Component
public class ExcelParser {
    private static final Logger logger = LoggerFactory.getLogger(ExcelParser.class);
    /**
     * 表格默认处理器
     */
    private ISheetContentHandler contentHandler = new DefaultSheetHandler();
    /**
     * 读取数据
     */
    private List<String[]> datas = new ArrayList<String[]>();

    /**
     * 转换表格,默认为转换第一个表格
     * @param stream
     * @return
     * @throws InvalidFormatException
     * @throws IOException
     * @throws ParseException
     */
    public ExcelParser parse(InputStream stream)
            throws InvalidFormatException, IOException, ParseException {
        return parse(stream, 1);
    }


    /**
     * 
     * @param stream
     * @param sheetId:为要遍历的sheet索引,从1开始
     * @return
     * @throws InvalidFormatException
     * @throws IOException
     * @throws ParseException
     */
    public synchronized ExcelParser parse(InputStream stream, int sheetId)
            throws InvalidFormatException, IOException, ParseException {
        // 每次转换前都清空数据
        datas.clear();
        // 打开表格文件输入流
        OPCPackage pkg = OPCPackage.open(stream);
        try {
            // 创建表阅读器
            XSSFReader reader;
            try {
                reader = new XSSFReader(pkg);
            } catch (OpenXML4JException e) {
                logger.error("读取表格出错");
                throw new ParseException(e.fillInStackTrace());
            }

            // 转换指定单元表
            InputStream shellStream = reader.getSheet("rId" + sheetId);
            try {
                InputSource sheetSource = new InputSource(shellStream);
                StylesTable styles = reader.getStylesTable();
                ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);
                getContentHandler().init(datas);// 设置读取出的数据
                // 获取转换器
                XMLReader parser = getSheetParser(styles, strings);
                parser.parse(sheetSource);
            } catch (SAXException e) {
                logger.error("读取表格出错");
                throw new ParseException(e.fillInStackTrace());
            } finally {
                shellStream.close();
            }
        } finally {
            pkg.close();

        }
        return this;

    }

    /**
     * 获取表格读取数据,获取数据前,需要先转换数据<br>
     * 此方法不会获取第一行数据
     * 
     * @return 表格读取数据
     */
    public List<String[]> getDatas() {
        return getDatas(true);

    }

    /**
     * 获取表格读取数据,获取数据前,需要先转换数据
     * 
     * @param dropFirstRow
     *            删除第一行表头记录
     * @return 表格读取数据
     */
    public List<String[]> getDatas(boolean dropFirstRow) {
        if (dropFirstRow && datas.size() > 0) {
            datas.remove(0);// 删除表头
        }
        return datas;

    }

    /**
     * 获取读取表格的转换器
     * 
     * @return 读取表格的转换器
     * @throws SAXException
     *             SAX错误
     */
    protected XMLReader getSheetParser(StylesTable styles, ReadOnlySharedStringsTable strings) throws SAXException {
        XMLReader parser = XMLReaderFactory.createXMLReader();
        parser.setContentHandler(new XSSFSheetXMLHandler(styles, strings, getContentHandler(), false));
        return parser;
    }

    public ISheetContentHandler getContentHandler() {
        return contentHandler;
    }

    public void setContentHandler(ISheetContentHandler contentHandler) {
        this.contentHandler = contentHandler;
    }

    /**
     * 表格转换错误
     */
    public class ParseException extends Exception {
        private static final long serialVersionUID = -2451526411018517607L;

        public ParseException(Throwable t) {
            super("表格转换错误", t);
        }

    }

    public interface ISheetContentHandler extends SheetContentsHandler {

        /**
         * 设置转换后的数据集,用于存放转换结果
         * 
         * @param datas
         *            转换结果
         */
        void init(List<String[]> datas);
    }

    /**
     * 默认表格解析handder
     */
    class DefaultSheetHandler implements ISheetContentHandler {
        /**
         * 读取数据
         */
        private List<String[]> datas;
        private int columsLength;
        // 读取行信息
        private String[] readRow;
        private ArrayList<String> fristRow = new ArrayList<String>();

        @Override
        public void init(List<String[]> datas) {
            this.datas = datas;
//          this.columsLength = columsLength;
        }

        @Override
        public void startRow(int rowNum) {
            if (rowNum != 0) {
                readRow = new String[columsLength];
            }
        }

        @Override
        public void endRow(int rowNum) {
        //将Excel第一行表头的列数当做数组的长度,要保证后续的行的列数不能超过这个长度,这是个约定。
            if (rowNum == 0) {
                columsLength = fristRow.size();
                readRow = fristRow.toArray(new String[fristRow.size()]);
            }else {
                readRow = fristRow.toArray(new String[columsLength]);
            }
            datas.add(readRow.clone());
            readRow = null;
            fristRow.clear();
        }

        @Override
        public void cell(String cellReference, String formattedValue, XSSFComment comment) {
            int index = getCellIndex(cellReference);//转换A1,B1,C1等表格位置为真实索引位置
            try {
                fristRow.set(index, formattedValue);
            } catch (IndexOutOfBoundsException e) {
                int size = fristRow.size();
                for (int i = index - size+1;i>0;i--){
                    fristRow.add(null);
                }
                fristRow.set(index,formattedValue);
            }
        }

        @Override
        public void headerFooter(String text, boolean isHeader, String tagName) {
        }

        /**
         * 转换表格引用为列编号
         * 
         * @param cellReference
         *            列引用
         * @return 表格列位置,从0开始算
         */
        public int getCellIndex(String cellReference) {
            String ref = cellReference.replaceAll("\\d+", "");
            int num = 0;
            int result = 0;
            for (int i = 0; i < ref.length(); i++) {
                char ch = cellReference.charAt(ref.length() - i - 1);
                num = (int) (ch - 'A' + 1);
                num *= Math.pow(26, i);
                result += num;
            }
            return result - 1;
        }
    }
}

2、调用

File tempFile = new File(this.getClass().getClassLoader().getResource("").getPath() + "tempFile\\" + (new Date()).getTime() + ".xlsx");
//传入一个路径产生流再将流传入工具类,返回解析对象,Excel的所有数据就被解析到List<String[]> 里面,遍历list任由你处置。
FileInputStream inputStream = new FileInputStream(tempFile);
ExcelParser parse = excelParser.parse(inputStream);
List<String[]> datas = parse.getDatas();

poi 导出数据

阅读数 34

POI 读取excel数据

阅读数 289

没有更多推荐了,返回首页