java 操作 excel 2010

简介: 原文http://blog.csdn.net/dallas16/article/details/6954264    前段时间,需要进行excel2010数据的解析。先是找组件,jxl不支持,然后时poi。

原文http://blog.csdn.net/dallas16/article/details/6954264

   前段时间,需要进行excel2010数据的解析。先是找组件,jxl不支持,然后时poi。poi倒是可以解析excel10(XSSF),但是数据量 一大就会内存溢出(但是用的还是一个小破笔记本1000+行就会内存溢出)。这下傻眼了,没办法,只好到网上找资料了,发现没什么能用,最后在 apache 官网上找到一个示例 XLSX2CSV(地址:http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/eventusermodel/XLSX2CSV.java),对它进行了改写。

        excel 从07开始其数据存储结构就发生了改变,现在是用多个xml进行存储的,可以直接用解压软件进行打开,就可以看到里面的组成(可以参考:http://blog.csdn.net/lupengcheng2025/article/details/5339998)。

        好了,现在开始解析。




import java.io.IOException;
import java.io.InputStream;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;

import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;



/**
 * 读取excel xlsx(07、10)
 * @author dallas16
 *
 */
public class ExcelAnalysisXLSX extends DefaultHandler {
    /**
     * excel样式
     */
     private StylesTable stylesTable;
     /**
      * 好像
      */
     private ReadOnlySharedStringsTable sharedStringsTable;
     /**
      * 单元格中的数据可能的数据类型
      *
      */
     enum xssfDataType {
         BOOL,
         ERROR,
         FORMULA,
         INLINESTR,
         SSTINDEX,
         NUMBER,
     }
     /**
      * 存储某个cell的类型
      */
     private xssfDataType nextDataType = xssfDataType.NUMBER;
    /**
     * 某个cell的值
     */
    private String value = "";
    
    private short formatIndex;
    private String formatString;
    private final DataFormatter formatter = new DataFormatter();
    
    /**
     * 存储某一行的数据
     */
    private List<String> rowlist = new ArrayList<String>();
    /**
     * excel的sheet名称
     */
    private String sheetName;
    /**
     * excel 的路径
     */
    private String path;
    /**
     * 准备存放读取结果
     */
    private List<List<String>> datas = new ArrayList<List<String>>();
    /**
     * 当前的数据时第几列
     */
    private int  thisColumn;
    private int lastColumnNumber;
    
    /**
     * 匹配开头和结尾是否是数字
     */
    private static Pattern p1 = Pattern.compile("^\\d.*\\d$");



    /**
     * 对单元格的数据进行处理,重写了DefaultHandler中characters方法(其实DefaultHandler中的所有方法都没有方法体),
     *     这个方法在读取过程中会被自动调用
     * @param ch
     * @param start
     * @param length
     */
    @Override
    public void characters(char[] ch, int start, int length)
            throws SAXException {
        value = value+new String(ch,start,length);
    }

    /**
     * 在读取元素结束时的 处理,主要是判断是不是一个单元格结束,是不是一行结束,是的话进行相应的处理
     *                             是单元格则将数据(value)添加到rowlist的相应位置
     *                             是一行结束的话则将 rowlist添加到sheetVo中
     * @param uri
     * @param localName
     * @param qName
     */
    @Override
    public void endElement(String uri, String localName, String qName)
            throws SAXException {
         if ("v".equals(qName)) {
            endDeal();
         } else if ("row".equals(qName)) {
             if (lastColumnNumber == -1) {
                 lastColumnNumber = 0;
             }
             this.dealData();
             lastColumnNumber = -1;
         }

    }

    /**
     * 在读取元素开始时的 处理,
     * @param uri
     * @param localName
     * @param qName
     */
    @Override
    public void startElement(String uri, String localName, String qName,
            Attributes attributes) throws SAXException {
        if ("inlineStr".equals(qName) || "v".equals(qName)) {
            value = "";
        }
        else if ("c".equals(qName)) {
            String r = attributes.getValue("r");
            int firstDigit = -1;
            int length = r.length();
            for (int c = 0; c < length; ++c) {
                if (Character.isDigit(r.charAt(c))) {
                    firstDigit = c;
                    break;
                }
            }
            thisColumn = nameToColumn(r.substring(0, firstDigit));
            dealDataType(attributes);
        }

    }

    /**
     * 对指定的sheet进行处理
     * @param styles
     * @param strings
     * @param sheetInputStream
     * @throws IOException
     * @throws ParserConfigurationException
     * @throws SAXException
     */
    public void processSheet(
            StylesTable styles,
            ReadOnlySharedStringsTable strings,
            InputStream sheetInputStream)
            throws IOException, ParserConfigurationException, SAXException {

        InputSource sheetSource = new InputSource(sheetInputStream);
        SAXParserFactory saxFactory = SAXParserFactory.newInstance();
        SAXParser saxParser = saxFactory.newSAXParser();
        XMLReader sheetParser = saxParser.getXMLReader();
        this.stylesTable = styles ;
        this.sharedStringsTable = strings;
        sheetParser.setContentHandler(this);
        sheetParser.parse(sheetSource);
    }

    /**
     *
     * @param excelUtilBean.getPath() 需要读取的excel的路径
     * @param excelUtilBean.getSheetName() 如果值不为空则按名称进行解析
     * @param excelUtilBean.getSheetNumber() 如果值为空则按指定的顺序进行解析
     * @throws IOException
     * @throws OpenXML4JException
     * @throws ParserConfigurationException
     * @throws SAXException
     */
    @SuppressWarnings("unused")
    public void process()
            throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {
        OPCPackage pkg = OPCPackage.open(path);
        ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);
        XSSFReader xssfReader = new XSSFReader(pkg);
        StylesTable styles = xssfReader.getStylesTable();
        XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        int index = 0;
        boolean flag = false;
        while (iter.hasNext()) { // 做判断,看是否是自己需要解析的那一个sheet
            InputStream stream = iter.next();
             String sheetName = iter.getSheetName();
             if(iter.getSheetName().equals(sheetName)){
                 processSheet(styles, strings, stream);
                 flag = true;
                 break;
             }   
              stream.close();
              ++index;
        }
        if(!flag ){
            String errorInfo = "名为  ‘"+sheetName+"’ 的sheet不存在!";
            throw new RuntimeException(errorInfo);
        }
        pkg = null;
        strings = null;
        xssfReader = null;
        styles = null;
    }
    
    /**
     * 结束元素时的处理,根据情况将数据添加到 rowlist中
     */
    public void endDeal(){
         String thisStr = null;
         thisStr = dealData(value,thisStr); //对单元格的数据进行类型处理
         if (lastColumnNumber == -1) {
             lastColumnNumber = 0;
         }
         for (int i = lastColumnNumber+1; i < thisColumn; ++i){
             rowlist.add("  ");
         }
             rowlist.add(thisStr==null?"":thisStr);
         if (thisColumn > -1){
             lastColumnNumber = thisColumn;
         }
    }
    
    /**
     * 将数据添加到sheetVo中去,也是根据条件进行不同的处理
     */
    public void dealData(){
        if(rowlist != null && rowlist.size() != 0 ){
            datas.add(rowlist);
            rowlist = null;
            
        }
        if(rowlist == null){
            rowlist = new ArrayList<String>();
        }
    }
    
    /**
     * 好像是计算当前是第几列
     * @param name
     * @return
     */
    private int nameToColumn(String name) {
        int column = -1;
        int length = name.length();
        for (int i = 0; i < length ; ++i) {
            int c = name.charAt(i);
            column = (column + 1) * 26 + c - 'A';
        }
        return column;
    }
    
    /**
     * 对解析出来的数据进行类型处理
     * @param value       单元格的值(这时候是一串数字)
     * @param thisStr  一个空字符串
     * @return
     */
    @SuppressWarnings("deprecation")
    public String dealData(String value,String thisStr){
         switch (nextDataType) {//这几个的顺序不能随便交换,交换了很可能会导致数据错误
         case BOOL:
             char first = value.charAt(0);
             thisStr = first == '0' ? "FALSE" : "TRUE";
             break;
         case ERROR:
             thisStr = "\"ERROR:" + value.toString() + '"';
             break;
         case FORMULA:
             thisStr = '"' + value.toString() + '"';
             break;
         case INLINESTR:
             XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
             
             thisStr =  rtsi.toString() ;
             rtsi = null;
             break;
         case SSTINDEX:
             String sstIndex = value.toString();
             try {
                 int idx = Integer.parseInt(sstIndex);
                 XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx));
                 thisStr = rtss.toString();
                 rtss = null;
             }
             catch (NumberFormatException ex) {
                 thisStr  = value.toString();
             }
             break;
         case NUMBER:
             String n = value.toString();
             if (this.formatString != null)
                 thisStr = formatter.formatRawCellContents(Double.parseDouble(n), this.formatIndex, this.formatString);
             else
                 thisStr = n;
             break;
         default:
             thisStr = " ";
             
             break;
     }

     try {    
             Date date = new Date(thisStr);

     } catch (Exception e) {
        
     }

        return thisStr;
    }
    

    /**
     * 处理数据类型
     * @param attributes
     */
    public void dealDataType(Attributes attributes){
        this.nextDataType = xssfDataType.NUMBER;
        this.formatIndex = -1;
        this.formatString = null;
        String cellType = attributes.getValue("t");
        String cellStyleStr = attributes.getValue("s");

        if ("b".equals(cellType))
            nextDataType = xssfDataType.BOOL;
        else if ("e".equals(cellType))
            nextDataType = xssfDataType.ERROR;
        else if ("inlineStr".equals(cellType))
            nextDataType = xssfDataType.INLINESTR;
        else if ("s".equals(cellType))
            nextDataType = xssfDataType.SSTINDEX;
        else if ("str".equals(cellType))
            nextDataType = xssfDataType.FORMULA;
        else if (cellStyleStr != null) {
            int styleIndex = Integer.parseInt(cellStyleStr);
            XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
            this.formatIndex = style.getDataFormat();
            this.formatString = style.getDataFormatString();
            if (this.formatString == null)
                this.formatString = BuiltinFormats.getBuiltinFormat(this.formatIndex);
        }
    }
    /**
     * 将数据添加到rowlist中
     * @param str    单元格的数据
     * @param num    列号
     */
    public void addRowlist(String str , int num){

        if(rowlist.size() >= num){
            rowlist.add(num, str==null?"":str);
        } else {
            int size = rowlist.size();
            int newNum = num+1;
            for(int i = size; i < newNum; i++){
                rowlist.add("");
            }
            rowlist.add(num, str==null?"":str);
        }
    }

    public String getSheetName() {
        return sheetName;
    }

    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    public String getPath() {
        return path;
    }

    public void setPath(String path) {
        this.path = path;
    }

    public List<List<String>> getDatas() {
        return datas;
    }

    public void setDatas(List<List<String>> datas) {
        this.datas = datas;
    }

    public static void main(String[] args) throws Exception {
        
        ExcelAnalysisXLSX excel = new ExcelAnalysisXLSX();
        excel.setPath("F:/b.xlsx");
        excel.setSheetName("Sheet1");
        excel.process();
        List<List<String>> datas = excel.getDatas();
        for(List<String> data : datas){
            System.out.println(data);
        }
    }

}


其中需要的jar包:poi相关jar、xmlBean-2.3.0.jar、dom4j.jar。

这样就可以解析了,我测试过的最大的是13000条数据(我系统中的解析10w条数据时没有问题的,但是做了批量处理),更大的数据就需要做批量处 理了,因为数据就算能解析出来,但是list中已经装不下这么多了(当然这和电脑配置有关系的)。然后还需要考虑解析的性能问题,其中的日期类型处理那里 的try{}catch{}的性能消耗相当大。还有做批量处理的时候一定要控制操作次数。

目录
相关文章
|
6天前
|
分布式计算 DataWorks Java
DataWorks操作报错合集之在使用MaxCompute的Java SDK创建函数时,出现找不到文件资源的情况,是BUG吗
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
29 0
|
6天前
|
Java
java导出复杂excel
java导出复杂excel
|
6天前
|
Java Android开发
java利用xml-rpc协议操作wordpress博客
java利用xml-rpc协议操作wordpress博客
12 1
|
6天前
|
Java 测试技术 Python
《手把手教你》系列技巧篇(三十六)-java+ selenium自动化测试-单选和多选按钮操作-番外篇(详解教程)
【4月更文挑战第28天】本文简要介绍了自动化测试的实战应用,通过一个在线问卷调查(&lt;https://www.sojump.com/m/2792226.aspx/&gt;)为例,展示了如何遍历并点击问卷中的选项。测试思路包括找到单选和多选按钮的共性以定位元素,然后使用for循环进行点击操作。代码设计方面,提供了Java+Selenium的示例代码,通过WebDriver实现自动答题。运行代码后,可以看到控制台输出和浏览器的相应动作。文章最后做了简单的小结,强调了本次实践是对之前单选多选操作的巩固。
25 0
|
1天前
|
JavaScript 前端开发 Java
《手把手教你》系列技巧篇(四十九)-java+ selenium自动化测试-隐藏元素定位与操作(详解教程)
【5月更文挑战第13天】本文主要讨论了在Selenium自动化测试中如何处理前端隐藏元素的问题。隐藏元素通常是通过`type="hidden"`或`style="display: none;"`属性实现的,它们在页面上不可见,但仍然存在于HTML代码中。Selenium可以定位到这些隐藏元素,但无法直接进行点击、输入等操作,会报错“ElementNotInteractableException”。
18 3
|
2天前
|
JavaScript 前端开发 测试技术
《手把手教你》系列技巧篇(四十八)-java+ selenium自动化测试-判断元素是否可操作(详解教程)
【5月更文挑战第12天】本文介绍了WebDriver中用于判断元素状态的三个方法:`isEnabled()`、`isSelected()`和`isDisplayed()`。`isSelected()`检查元素是否被选中,通常用于勾选框。`isDisplayed()`则用来判断元素是否在页面上可见。`isEnabled()`方法确定元素是否可操作,例如是否能点击或输入内容。
11 1
|
4天前
|
Java Apache 索引
POI操作大全(动态合并单元格,为单元格生成一个自定义的数据显示格式,自定义公式计算结果生成,读取excel,word文件在生成图片,word指定位置生成图片)
POI操作大全(动态合并单元格,为单元格生成一个自定义的数据显示格式,自定义公式计算结果生成,读取excel,word文件在生成图片,word指定位置生成图片)
|
6天前
|
监控 Java
Java一分钟之-NIO:非阻塞IO操作
【5月更文挑战第14天】Java的NIO(New IO)解决了传统BIO在高并发下的低效问题,通过非阻塞方式提高性能。NIO涉及复杂的选择器和缓冲区管理,易出现线程、内存和中断处理的误区。要避免这些问题,可以使用如Netty的NIO库,谨慎设计并发策略,并建立标准异常处理。示例展示了简单NIO服务器,接收连接并发送欢迎消息。理解NIO工作原理和最佳实践,有助于构建高效网络应用。
8 2
|
6天前
|
安全 Java 数据安全/隐私保护
Java一分钟之-Java反射机制:动态操作类与对象
【5月更文挑战第12天】本文介绍了Java反射机制的基本用法,包括获取Class对象、创建对象、访问字段和调用方法。同时,讨论了常见的问题和易错点,如忽略访问权限检查、未捕获异常以及性能损耗,并提供了相应的避免策略。理解反射的工作原理和合理使用有助于提升代码灵活性,但需注意其带来的安全风险和性能影响。
24 4
|
6天前
|
Java
java的excel列行合并模版
java的excel列行合并模版