java 操作 excel 2010

原文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{}的性能消耗相当大。还有做批量处理的时候一定要控制操作次数。

时间： 2024-10-30 04:35:12

java 操作 excel 2010的相关文章

java操作Excel处理数字类型的精度损失问题验证

java操作Excel处理数字类型的精度损失问题验证: 场景: CELL_TYPE_NUMERIC-->CELL_TYPE_STRING--->CELL_TYPE_NUMERIC POI版本:poi-3.10.1poi-3.9 Code: package poi; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.i

Java 操作 Excel

原文:Java 操作 Excel 这次的任务是实现数据的导入/导出功能,就是用Java操作Excel,上网找了一下,就Java来说用Apache的POI库的比较多,功能也相对比较强大.其他Excel开发库: http://www.oschina.net/project/tag/258/excel-tools Apache POI 下载地址: http://poi.apache.org/download.html  废话不多说,直接给几个例子就了解怎么用了. Excel 2003 (xls后缀)

java操作excel，用jxl将多个excel文件合并成一个多sheet的excel文件，颜色会出错

问题描述 java操作excel,用jxl将多个excel文件合并成一个多sheet的excel文件,颜色会出错 程序代码如下: public class Main { public static void main(String[] args) { try { InputStream fis1 = new FileInputStream("D:/fei/testfiles/2G囤卡量.xls"); InputStream fis2 = new FileInputStream(&quo

java操作excel，用jxl将多个excel文件合并成一个多sheet的excel文件

问题描述 java操作excel,用jxl将多个excel文件合并成一个多sheet的excel文件 java操作excel,用jxl将多个excel文件合并成一个多sheet的excel文件有3个list 解决方案 当个excel文件是只有一个sheet的吗,如果是的话,可以逐个遍历Excel文件,获取这个文件的sheet后,直接添加到一个空白excel文档中就可以了. 了解下jxl的api,这个需求实现应该不复杂的.

java操作excel使用jxl如何控制数据行数

问题描述 java操作excel使用jxl如何控制数据行数 java操作excel使用jxl如何控制数据行数,当数据量达到1000条,就新建一个excel,不是新建excel中的sheet表哦!如何实现?文件名称会怎么变化呢?求助! 解决方案 http://blog.csdn.net/yhawaii/article/details/6927927 解决方案二: java 使用jxl 操作Excel在java中使用jxl操作excelJAVA中使用JXL操作EXCEL

jxl的使用总结（java操作excel）

jxl.jar是通过java操作excel表格的工具类库: jxl.jar包:链接:http://pan.baidu.com/s/1o8qFJHw 密码:5jyq  1:通过模拟实现创建一个表格,然后模拟添加到表格中数据,实际开发过程中都是通过从数据库导入到表格中的数据 1 package com.bie; 2 3 import java.io.File; 4 import java.io.IOException; 5 6 import jxl.Workbook; 7 import jxl.wr

Java操作Excel的2种方法--Poi,jExcelApi

老师让查一些java操作excel的方法,在网上查了半天,看的也不太懂,就把有价值的总结一下. 发现java操作Excel的第三方jar包比较多,有两套比较有影响的API可供使用,一个是POI,一个是jExcelAPI. 一.jExcelAPI jExcelAPI是一个韩国程序员的作品,虽然没有POI那样血统高贵,但是在笔者的使用过程中,感觉简单方便,对中文支持非常好,功能也比较强大.它的下载地址是:http://www.andykhan.com/jexcelapi/   它的特征有如下描述:

java操作excel的方法_java

本文实例讲述了java操作excel的方法.分享给大家供大家参考.具体如下: WritableWorkbook workbook = Workbook.createWorkbook(new File("d:\\output.xls")); WritableSheet sheet = workbook.createSheet("项目简报", 0); //样式 WritableFont sonti18font = new WritableFont(WritableFon

利用java操作Excel文件的方法_java

很久以来都想研究一下利用java操作Excel的方法,今天没事,就稍微了解了一下,特总结一下.利用java操作Excel,有个开源的东东-jxl.jar,可以到http://sourceforge.net/projects/jexcelapi/files/下载. 一.读取Excel文件内容 复制代码 代码如下: /** *//**读取Excel文件的内容     * @param file  待读取的文件     * @return     */    public static String