• POI操作Excel2007实例二之“SXSSFWorkbook”处理海量数据

    转自：http://blog.csdn.net/little_stars/article/details/8266262

    前文讲述了 POI 读取的基本操作，但后期 经过试验，当写入数据量超过5万条以上时，

    很容易报错“内存溢出”，就算你调整JVM的xmx为 “2048MB”，也无效果。

    后来查资料得知 SXSSFWorkbook 是专门用来处理大量数据写入 Excel2007的问题的。


    实例如下，具体步骤神马的，请看前文。

    读取仍然是“XSSFWorkbook”，写入则为“SXSSFWorkbook ”。

    经试验 写入处理速度在 7000行左右，根据 Excel文件大小 会上下浮动。



    Config.java

    [java] view plaincopyprint?
      1. package com.excel.poi.gz10000;  
      2. import java.io.BufferedInputStream;  
      3. import java.io.File;  
      4. import java.io.FileInputStream;  
      5. import java.io.FileOutputStream;  
      6. import java.io.IOException;  
      7. import java.io.UnsupportedEncodingException;  
      8. import java.sql.Connection;  
      9. import java.sql.DriverManager;  
      10. import java.sql.ResultSet;  
      11. import java.sql.SQLException;  
      12. import java.sql.Statement;  
      13. import java.text.DecimalFormat;  
      14. import java.text.NumberFormat;   
      15. import java.text.SimpleDateFormat;  
      16. import java.util.ArrayList;  
      17. import java.util.Date;  
      18.   
      19. import javax.servlet.ServletException;  
      20. import javax.servlet.http.HttpServlet;  
      21. import javax.servlet.http.HttpServletRequest;  
      22. import javax.servlet.http.HttpServletResponse;  
      23.    
      24. import org.apache.log4j.Logger;  
      25. import org.apache.log4j.PropertyConfigurator;  
      26. import org.apache.poi.ss.usermodel.Cell;  
      27. import org.apache.poi.ss.usermodel.Row;  
      28. import org.apache.poi.ss.usermodel.Sheet;  
      29. import org.apache.poi.xssf.streaming.SXSSFWorkbook;  
      30. import org.apache.poi.xssf.usermodel.XSSFCell;         
      31. import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
      32.   
      33. /** 
      34.  * <b> 数据匹配</b> <br> 
      35.  * <ul> 
      36.  * <li> 作者：C_Dream </li> 
      37.  * <li> 当前版本：4.0 </li> 
      38.  * <li> 修改时间： 2012-12-6 17:50 </li> 
      39.  * <li> 修改内容：<ol> 
      40.  * <li> 更换Excel2007写入方式，大幅提升性能。</li> 
      41.  * <li> 引入 log4j，局部提升性能。</li> 
      42.  * <li> 部分代码优化，CMD输出提示可选。</li> 
      43.  * </ol></li> 
      44.  * <li> 创建时间： 2012-11-08 19:22 </li> 
      45.  * </ul> 
      46.  */  
      47. public class Config extends HttpServlet{  
      48.     private static final long serialVersionUID = 1L;       
      49.     //Config c = new Config();//此处决不能 new 本类，否则报错：java.lang.StackOverflowError  
      50.     String class_path = this.getClass().getResource("").getPath();   
      51.     public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException  
      52.     {  
      53.         this.doGet(request,response); //将 表单  post 方法传过来的参数，转给 get方法 去处理  
      54.     }  
      55.     public void doGet(HttpServletRequest request,HttpServletResponse response)throws ServletException, IOException   
      56.     {  
      57.          request.setCharacterEncoding("UTF-8"); //转码  
      58.          String forms=(String)request.getParameter("forms");  
      59.   
      60.          if(forms.equals("if_3g")){  
      61.                 try {  
      62.                     this.read_Excel(request,response);  
      63.                 } catch (SQLException e) {  
      64.                     e.printStackTrace();  
      65.                 } catch (ClassNotFoundException e) {  
      66.                     e.printStackTrace();  
      67.                 }    
      68.          }  
      69.                    
      70.          response.sendRedirect("index.jsp?done=true");  
      71.     }   
      72.       
      73.       
      74.     @SuppressWarnings("rawtypes")  
      75.     public void read_Excel(HttpServletRequest request,  
      76.             HttpServletResponse response) throws UnsupportedEncodingException, SQLException, ClassNotFoundException  {  
      77.           
      78.         PropertyConfigurator.configure(class_path+"log4j.properties");//获取 log4j 配置文件  
      79.         Logger logger = Logger.getLogger(Config.class ); //获取log4j的实例  
      80.         String startTmie = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());  
      81.         logger.debug(" **********【准备处理，正在加载文件】**********");//7  
      82.         logger.debug(" **********每行数据间请不要有空行，以免程序误判**********");//7  
      83.         logger.debug(" **********由于处理Excel的插件本身性能局限，请控制文件大小，以免影响加载效率。**********");//7  
      84.         logger.debug(" **********大小在  7 MB  以内，大约  21 万  行数据。**********");//7  
      85.         logger.debug(" **********本程序每分钟约处理 7000条数据，但根据文件大小会有影响。**********");//7  
      86.           
      87.         request.setCharacterEncoding("UTF-8");  //转码  
      88.         String xls_read_Address=(String)request.getParameter("xls_read_Address");//读取     
      89.         String xls_write_Address=(String)request.getParameter("xls_write_Address");//写入  
      90.         String  count_rows=(String)request.getParameter("count_rows");//自动编号      
      91.         String  tips_cmd=(String)request.getParameter("tips_cmd");//CMD窗口的提示方式  
      92.           
      93.         try {   
      94.             DataConvert dc = new DataConvert();//数据转换工具  
      95.             DecimalFormat df = (DecimalFormat) NumberFormat.getPercentInstance();   
      96.               
      97.             ArrayList<ArrayList> ls = new  ArrayList<ArrayList>();  
      98.       
      99.             File excel_file = new File(xls_read_Address);//读取的文件路径    
      100.             FileInputStream input = new FileInputStream(excel_file);  //读取的文件路径   
      101.             XSSFWorkbook wb = new XSSFWorkbook(new BufferedInputStream(input));                    
      102.             int sheet_numbers = wb.getNumberOfSheets();//获取表的总数  
      103.             logger.debug(" **********共有工作表总数**********："+sheet_numbers);//7    
      104.             String[] sheetnames=new String[sheet_numbers];  
      105.               
      106.             Connection con=null;  
      107.             Statement  stmt=null;  
      108.             ResultSet  rs=null;  
      109.             String s_3g=null;             
      110.             Class.forName("oracle.jdbc.driver.OracleDriver");  
      111.             con = DriverManager.getConnection("jdbc:oracle:thin:@12.12.12.123:1521:gz10000","gz12345","12345");  
      112.             stmt = con.createStatement();     
      113.               
      114.             for(int i=0;i<sheet_numbers;i++){//遍历所有表               
      115.                 ArrayList<String[]> ls_a = new  ArrayList<String[]>(); //用来存储某个表 读取出来的数据    
      116.                 Sheet sheet = wb.getSheetAt(i);  //获取 某个表                
      117.                 sheetnames[i] = sheet.getSheetName();//获取表名，存入数组  
      118.                 logger.debug(" ---正在读取和匹配工作表 《"+sheetnames[i]+"》 的数据--- ");//7   
      119.                   
      120.                 int rows_num = sheet.getLastRowNum();//获取行数  
      121.                 logger.debug(" ---表 《"+sheetnames[i]+"》  共有数据---： "+rows_num+" 行");//7   
      122.                 for( int rows=0;rows<rows_num;rows++){   
      123.                     Row row = sheet.getRow(rows);//取得某一行   对象                                     
      124.                       
      125.                     if(row!=null&&!(row.equals(""))){  
      126.                     int columns_num = row.getLastCellNum();//获取列数  
      127.                       
      128.                     String[] s =new String[5];//初始化数组长度  
      129.                     for( int columns=0;columns<columns_num;columns++){  
      130.                         Cell  cell = row.getCell(columns);                    
      131.                         if(cell!=null){   
      132.                         switch ( cell.getCellType()) {    
      133.                          case XSSFCell.CELL_TYPE_STRING: // 字符串     
      134.                              s[columns] = cell.getStringCellValue();    
      135.                              if(s[columns]==null){  
      136.                                  s[columns]=" ";  
      137.                              }  
      138.                              break;   
      139.                          case XSSFCell.CELL_TYPE_NUMERIC: // 数字                            
      140.                                 double strCell = cell.getNumericCellValue();  
      141.                              if(String.valueOf(strCell)==null){  
      142.                                  s[columns]=" ";  
      143.                              }    
      144.                                       
      145.                                 df.applyPattern("0");  
      146.                                 s[columns] = df.format(strCell);  
      147.                                 if(Double.parseDouble(s[columns])!=strCell){  
      148.                                  df.applyPattern(Double.toString(strCell));  
      149.                                  s[columns] = df.format(strCell);  
      150.                                 }  
      151.                                
      152.                              break;  
      153.                          case XSSFCell.CELL_TYPE_BLANK: // 空值     
      154.                              s[columns]=" ";    
      155.                              break;     
      156.                          default:    
      157.                              logger.debug(" ---单元格格式不支持---");      
      158.                              break;     
      159.                          }    
      160.                        }  
      161.                     }  
      162.                     if(count_rows.equals("是")&&rows>0){  
      163.                         s[0]=dc.intToString(rows);//自动编号  
      164.                     }  
      165.                       
      166.                     /* ******** 访问数据库 ，并判断是否3G ******** */  
      167.                     String sql="select busiattr1 from ap_t_si_cus_spec_info where cus_phone='"+s[1]+"' and rownum=1";                         
      168.                     rs = stmt.executeQuery(sql);  
      169.                       
      170.                     if(rs.next()){  
      171.                         if(rs.getString("busiattr1")!=null){  
      172.                             s_3g = rs.getString("busiattr1").toString().toUpperCase();  
      173.                         }  
      174.                         else{  
      175.                             s_3g=" ";  
      176.                         }  
      177.                     }  
      178.                     else{  
      179.                         s_3g=" ";  
      180.                     }   
      181.                     /* ******** 访问结束  ******** */  
      182.                       
      183.                     if(s_3g.contains("3G")){  
      184.                         s[4]="是";//写入 “是否3G”这一列 的值，比如 “是”  
      185.                     }  
      186.                     if(s[4]==null){  
      187.                         s[4]=" ";  
      188.                     }  
      189.                       
      190.                     /* CMD窗口提示方式 */  
      191.                     if(!(tips_cmd.equals("none"))&&tips_cmd!=null&&!(tips_cmd.equals(""))) {  
      192.                         if(tips_cmd.equals("all")){  
      193.                             logger.debug(" 匹配中："+s[0]+" "+s[1]+" "+s[2]+" "+s[3]+" "+ s[4]);   
      194.                         }else                 
      195.                         if(rows%DataConvert.stringToInt(tips_cmd)==0){  
      196.                             logger.debug(" 匹配中："+s[0]+" "+s[1]+" "+s[2]+" "+s[3]+" "+ s[4]);   
      197.                         }  
      198.                     }  
      199.                       
      200.                     ls_a.add(s);//添加每行数据到 ls_a  
      201.                     }  
      202.                 }  
      203.             ls.add(ls_a);       //添加 每个表 到 ls              
      204.             input.close();     
      205.             write_Excel( xls_write_Address, ls, sheetnames ,tips_cmd)  ;                  
      206.             }              
      207.         } catch (IOException ex) {    
      208.             ex.printStackTrace();    
      209.         }  
      210.         String endTmie = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());  
      211.         logger.debug(" ***************【处理完成，程序结束】***************");//7     
      212.         logger.debug(" 开始时间："+startTmie);  
      213.         logger.debug(" 结束时间："+endTmie);   
      214.         logger.debug(" 新文件输出路径为："+xls_write_Address);  
      215.     }  
      216.   
      217.     @SuppressWarnings({ "rawtypes", "unchecked" })  
      218.     public void write_Excel( String xls_write_Address,ArrayList<ArrayList> ls,String[] sheetnames,String tips_cmd ) throws IOException  {  
      219.            
      220.         PropertyConfigurator.configure(class_path+"log4j.properties");//获取 log4j 配置文件  
      221.         Logger logger = Logger.getLogger(Config.class ); //获取log4j的实例         
      222.         FileOutputStream output = new FileOutputStream(new File(xls_write_Address));  //读取的文件路径   
      223.         SXSSFWorkbook wb = new SXSSFWorkbook(10000);//内存中保留 10000 条数据，以免内存溢出，其余写入 硬盘          
      224.           
      225.           
      226.         for(int sn=0;sn<ls.size();sn++){  
      227.             Sheet sheet = wb.createSheet(String.valueOf(sn));  
      228.   
      229.             wb.setSheetName(sn, sheetnames[sn]);     
      230.             ArrayList<String[]> ls2 = ls.get(sn);   
      231.             for(int i=0;i<ls2.size();i++){  
      232.                 Row row = sheet.createRow(i);  
      233.                 String[] s = ls2.get(i);                  
      234.                 for(int cols=0;cols<s.length;cols++){  
      235.                     Cell cell = row.createCell(cols);                     
      236.                     cell.setCellType(XSSFCell.CELL_TYPE_STRING);//文本格式                    
      237.                     sheet.setColumnWidth(cols, s[cols].length()*384); //设置单元格宽度  
      238.                     cell.setCellValue(s[cols]);//写入内容  
      239.                 }  
      240.                 /* CMD窗口提示方式 */  
      241.                 if(!(tips_cmd.equals("none"))&&tips_cmd!=null&&!(tips_cmd.equals(""))) {  
      242.                     if(tips_cmd.equals("all")){  
      243.                         logger.debug(" 写入中："+s[0]+" "+s[1]+" "+s[2]+" "+s[3]+" "+ s[4]);   
      244.                     }else                 
      245.                     if(i%DataConvert.stringToInt(tips_cmd)==0){  
      246.                         logger.debug(" 写入中："+s[0]+" "+s[1]+" "+s[2]+" "+s[3]+" "+ s[4]);   
      247.                     }  
      248.                 }  
      249.             }              
      250.          }    
      251.         wb.write(output);  
      252.         output.close();           
      253.     }  
      254.        
      255. } 
  • 相关阅读:
    C#——Unity事件监听器
    关于RAS加解密的工具类
    关于Map的源码分析之HashMap
    关于Map的源码分析
    关于Set的源码分析
    关于集合List的源码分析
    关于String源码分析
    hdu 1285 确定比赛名次
    hdu 2553 N皇后问题
    hdu 1312 Red and Black
  • 原文地址：https://www.cnblogs.com/liuzy2014/p/5010426.html
Copyright © 2020-2023  润新知