换个姿势认识 Excel

在一次意外中，鼠标在一个excel文件上，点击了右键

发现这货竟然是一个压缩文件，解压后发现包含以下文件（仅限参考）

里面大多数都是xml格式（有点惊喜）。下图为excel文件使用excel打开时的视图，以下称“界面视图”

我们再回到解压的出来的文件中，看一下以下几个文件

/xl/workbook.xml

/xl/_rels/workbook.xml.rels

/xl/sharedStrings.xml


	
		name
		
	
	
		age
		
	
	
		张三
		
	
	
		李四
		
	
	
		王五

/xl/worksheets/sheet1.xml

从上面文件中提取部分片段：

从上面先下几个假设:

1. 从sheet的名字（Sheet1）可以找到存储了表格数据的文件(/xl/worksheets/sheet1.xml)

2. /xl/worksheets/sheet1.xml 文件中的row 是行,c 是 column(列), v是值， c中的 t="s"描述的是字符串类型，则v代表的是序号

3. 字符串类型的值，都存在于 /xl/sharedStrings.xml 文件中。

如果以上假设成立，那么我们现在对excel的读取就变成了在有限的几个xml文件中，读取我们需要的数据。

步骤如下：

1. 读取 /xl/workbook.xml 文件，得到sheet名与rid的关系

2. 读取 /xl/_rels/workbook.xml.rels 文件，得到rid与sheet.xml的文件路径的关系

3. 读取/xl/sharedStrings.xml文件，得到字符串与序号的关系

4. 读取/xl/worksheets/sheet*.xml文件，得到行数据，当读到一个新的行，则创建新的数据容器（示例中为列表，读者可尝试使用类反射相关内容，实现把行数据读到实体中）并放入待返回的数据集中，然后读取行中的列，填充到数据容器对应的位置，如果列的类型为字符串，即有属性t="s"，则把序号进行转换得到真实值，然后放到数据容器中。

代码如下：

package org.yafox.excel;

import java.io.File;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;

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


public class Reader {

    
    private List readSharedStrings(ZipFile zipFile, SAXParser saxParser) throws Exception {
        ZipEntry entry = zipFile.getEntry("xl/sharedStrings.xml");
        ShareStringsHandler dh = new ShareStringsHandler();
        saxParser.parse(zipFile.getInputStream(entry), dh);
        return dh.getSharedStrings();
    }

    
    private Map readRelationship(ZipFile zipFile, SAXParser saxParser) throws Exception {
        ZipEntry entry = zipFile.getEntry("xl/_rels/workbook.xml.rels");
        RelationshipHandler dh = new RelationshipHandler();
        saxParser.parse(zipFile.getInputStream(entry), dh);
        return dh.getRelationship();
    }

    
    private Map readNameRidMapping(ZipFile zipFile, SAXParser saxParser) throws Exception {
        ZipEntry entry = zipFile.getEntry("xl/workbook.xml");
        SheetHandler dh = new SheetHandler();
        saxParser.parse(zipFile.getInputStream(entry), dh);
        return dh.getNameRidMapping();
    }

    public List> readData(File excelFile, String sheetName) throws Exception {
        ZipFile zipFile = null;
        try {
            zipFile = new ZipFile(excelFile);
            SAXParserFactory saxFactory = SAXParserFactory.newInstance();
            SAXParser saxParser = saxFactory.newSAXParser();

            Map nameRidMapping = readNameRidMapping(zipFile, saxParser);
            Map relationship = readRelationship(zipFile, saxParser);
            List sharedStrings = readSharedStrings(zipFile, saxParser);

            String sheetId = nameRidMapping.get(sheetName);
            String path = relationship.get(sheetId);
            ZipEntry entry = zipFile.getEntry("xl/" + path);
            RowHandler dh = new RowHandler(sharedStrings);
            saxParser.parse(zipFile.getInputStream(entry), dh);
            return dh.getDatas();
        } finally {
            zipFile.close();
        }
    }

}

package org.yafox.excel;

import java.util.HashMap;
import java.util.Map;

import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;


public class RelationshipHandler extends DefaultHandler {
    
    
    private Map relationship = new HashMap();
    
    @Override
    public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {
        if (qName.equalsIgnoreCase("Relationship")) {
            String id = attributes.getValue("Id");
            String target = attributes.getValue("Target");
            relationship.put(id, target);
        }
    }

    public Map getRelationship() {
        return relationship;
    }
    
    
}

package org.yafox.excel;

import java.util.ArrayList;
import java.util.List;

import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;


public class ShareStringsHandler extends DefaultHandler {
    
    
    private List sharedStrings = new ArrayList();
    
    private boolean capture = false;
    
    @Override
    public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {
        if (qName.equalsIgnoreCase("t")) {
            this.capture = true;
        }
    }
    
    @Override
    public void endElement(String uri, String localName, String qName) throws SAXException {
        if (qName.equalsIgnoreCase("t")) {
            this.capture = false;
        }
    }
    
    @Override
    public void characters(char[] ch, int start, int length) throws SAXException {
        // 如果处于捕获状态，则取t里的值 
        if (this.capture) {
            String str = new String(ch, start, length);
            this.sharedStrings.add(str.trim());
        }
    }

    public List getSharedStrings() {
        return sharedStrings;
    }

    
    
}

package org.yafox.excel;

import java.util.HashMap;
import java.util.Map;

import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;


public class SheetHandler extends DefaultHandler {

    private Map nameRidMapping = new HashMap();

    @Override
    public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {
        if ("sheet".equals(qName)) {
            String name = attributes.getValue("name");
            String rid = attributes.getValue("r:id");
            nameRidMapping.put(name, rid);
        }
    }
    
    public Map getNameRidMapping() {
        return nameRidMapping;
    }

}

package org.yafox.excel;

import java.util.ArrayList;
import java.util.List;

import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;


public class RowHandler extends DefaultHandler {

    private List sharedStrings = new ArrayList();

    private List> datas = new ArrayList>();

    private List rowData = null;
    
    private boolean needTranslate = false;

    private List paths = new ArrayList();

    public RowHandler(List sharedStrings) {
        super();
        this.sharedStrings = sharedStrings;
    }

    @Override
    public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {
        this.paths.add(qName);
        if (paths.size() == 3 && paths.get(0).equals("worksheet") && paths.get(1).equals("sheetData") && paths.get(2).equals("row")) {
            rowData = new ArrayList();
            this.datas.add(rowData);
        } else if (paths.size() == 4 && paths.get(0).equals("worksheet") && paths.get(1).equals("sheetData") && paths.get(2).equals("row") && paths.get(3).equals("c")) {
            this.needTranslate = "s".equals(attributes.getValue("t"));
        }
    }

    @Override
    public void endElement(String uri, String localName, String qName) throws SAXException {
        this.paths.remove(paths.size() - 1);
    }
    
    @Override
    public void characters(char[] ch, int start, int length) throws SAXException {
        if (capturable()) {
            String value = new String(ch, start, length).trim();
            if (this.needTranslate) {
                int idx = Integer.parseInt(value);
                value = this.sharedStrings.get(idx);
            }
            
            rowData.add(value);
        }
    }
    
    private boolean capturable() {
        if (paths.size() != 5) {
            return false;
        }
        
        return  paths.get(0).equals("worksheet") && paths.get(1).equals("sheetData") && paths.get(2).equals("row") && paths.get(3).equals("c") && paths.get(4).equals("v");
    }

    public List> getDatas() {
        return datas;
    }

}

以上文件格式是 .xlsx文件的结构，至于.xls的，应该不是上面的存储格式。

最近，留给大家思考一个问题，如何做一个高效的excel导出工具或类库呢？

换个姿势认识 Excel

.Net相关栏目本月热门文章