一、EXCEL的底层实现
能力有限，了解的比较浅，有不足之处望指正，首先看下图：
一、 excel2007是使用xml格式来存储的，把一个excel文件后缀改为.zip,打开之后就直接可以看到一个excel文件对应的xml格式的文件了。
1.1、docProps目录
1.1.1、 core.xml文件
<?xml version="1.0" encoding="utf-8"?>
<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/"
xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<!-- 创建人 -->
<dc:creator>Administrator</dc:creator>
<!-- 最后修改人 -->
<cp:lastModifiedBy>Administrator</cp:lastModifiedBy>
<!-- 创建时间 -->
<dcterms:created xsi:type="dcterms:W3CDTF">2008-09-11T17:22:52Z</dcterms:created>
<!-- 修改时间 -->
<dcterms:modified xsi:type="dcterms:W3CDTF">2017-03-31T15:29:39Z</dcterms:modified>
</cp:coreProperties>
1.1.2、 app.xml文件
<?xml version="1.0" encoding="utf-8"?>
<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties"
xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes">
<!-- 文档类型 -->
<Application>Microsoft Excel</Application>
<!-- 是否加密 -->
<DocSecurity>0</DocSecurity>
<ScaleCrop>false</ScaleCrop>
<HeadingPairs>
<vt:vector size="2" baseType="variant">
<vt:variant>
<vt:lpstr>工作表</vt:lpstr>
</vt:variant>
<vt:variant>
<vt:i4>3</vt:i4>
</vt:variant>
</vt:vector>
</HeadingPairs>
<TitlesOfParts>
<vt:vector size="3" baseType="lpstr">
<vt:lpstr>Sheet1</vt:lpstr>
<vt:lpstr>Sheet2</vt:lpstr>
<vt:lpstr>Sheet3</vt:lpstr>
</vt:vector>
</TitlesOfParts>
<Company/>
<LinksUpToDate>false</LinksUpToDate>
<!-- 是否共享 -->
<SharedDoc>false</SharedDoc>
<HyperlinksChanged>false</HyperlinksChanged>
<!-- 版本 -->
<AppVersion>12.0000</AppVersion>
</Properties>
1.2、 xl目录，该目录保存的就是excel中的详细内容了
1.2.1、workbook.xml文件
文件包含一对<sheets>标签，其中的每个<sheet>元素都代表Excel 2007文件中的一个sheet，工作表的名称就是其name属性的值，这里有三个sheet
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<fileVersion appName="xl" lastEdited="4" lowestEdited="4" rupBuild="4506"/>
<workbookPr defaultThemeVersion="124226"/>
<bookViews>
<workbookView xWindow="0" yWindow="105" windowWidth="12765" windowHeight="5715"/>
</bookViews>
<sheets>
<sheet name="Sheet1" sheetId="1" r:id="rId1"/>
<sheet name="Sheet2" sheetId="2" r:id="rId2"/>
<sheet name="Sheet3" sheetId="3" r:id="rId3"/>
</sheets>
<calcPr calcId="125725"/>
</workbook>
1.2.2、 _rels/workbook.xml.rels
定义每个sheetid对应的sheet内容文件sheet1.xml，共享的单元格内容文件sharedstring.xml,样式文件style.xml是当前单元格的样式字体，颜色等样式的xml配置。
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet3.xml"/>
<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet2.xml"/>
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>
<Relationship Id="rId6" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/>
<Relationship Id="rId5" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>
<Relationship Id="rId4" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Target="theme/theme1.xml"/>
</Relationships>
1.2.3、 worksheets文件夹,保存的就是excel中的sheet
1.2.4、sheet.xml文件
<?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">
<!--
容积，即该sheet中以供占用了多大的地方，哪个单元格到哪个单元格，矩形的左上角单元格和右下角单元格
此属性可以判断列数，如下就是：(M-A+1)
-->
<dimension ref="A1:M8"/>
<!-- sheet视图的配置信息，如打开时默认选中的单元格是哪个 -->
<sheetViews>
<sheetView tabSelected="1" workbookViewId="0">
<selection activeCell="G12" sqref="G12"/>
</sheetView>
</sheetViews>
<!-- sheet的格式参数 -->
<sheetFormatPr defaultRowHeight="14.25"/>
<!-- 行中每列的配置参数 -->
<cols>
<col min="1" max="1" width="7.875" customWidth="1"/>
<col min="2" max="2" width="6.75" customWidth="1"/>
<col min="3" max="3" width="6" customWidth="1"/>
<col min="4" max="4" width="4.5" customWidth="1"/>
<col min="5" max="5" width="8.5" customWidth="1"/>
<col min="6" max="6" width="9.375" customWidth="1"/>
<col min="7" max="7" width="13.5" customWidth="1"/>
<col min="8" max="8" width="7" customWidth="1"/>
<col min="9" max="9" width="7.625" customWidth="1"/>
<col min="10" max="10" width="4.625" customWidth="1"/>
<col min="11" max="11" width="9.5" bestFit="1" customWidth="1"/>
</cols>
<!-- 数据区域，这个里面就是我们的单元格的相关数据信息了，是以行（Row）的方式记录的 -->
<sheetData>
<!-- excel中的行 -->
<row r="1" spans="1:13">
<!--
excel中的列
r:单元格坐标
s:单元格的style,对应style.xml中cellXfs元素下的某个元素xf元素，
此处就是对应索引为12的元素，从0开始计算，即为第13个xf元素。
t:元素类型，s表示是字符串类型，对应到sharedStrings.xml中的sst元素
-->
<c r="A1" s="12" t="s">
<!--
如果是t="s" 则存储的是sharedStrings.xml中sst元素下的某个si元素，此处对应索引为0的元素，即第一个si元素
否则，这里存储的就是单元格的值
-->
<v>0</v>
</c><c r="B1" s="12" t="s">
<v>10</v>
</c><c r="C1" s="12" t="s">
<v>1</v>
</c><c r="D1" s="12" t="s">
<v>2</v>
</c><c r="E1" s="12" t="s">
<v>3</v>
</c><c r="F1" s="12" t="s">
<v>4</v>
</c><c r="G1" s="12" t="s">
<v>5</v>
</c><c r="H1" s="12" t="s">
<v>6</v>
</c><c r="I1" s="12" t="s">
<v>7</v>
</c><c r="J1" s="12" t="s">
<v>8</v>
</c><c r="K1" s="12" t="s">
<v>9</v>
</c>
</row>
<row r="2" spans="1:13">
<c r="A2" t="s">
<v>11</v>
</c><c r="B2" s="10" t="s">
<v>12</v>
</c><c r="C2" s="1">
<v>2</v>
</c><c r="D2" s="2" t="s">
<v>13</v>
</c><c r="E2" s="3" t="s">
<v>14</v>
</c><c r="F2" s="4">
<v>89</v>
</c><c r="G2" s="5">
<v>10</v>
</c><c r="H2" s="6">
<v>12</v>
</c><c r="I2" s="7">
<v>0.2</v>
</c><c r="J2" s="8">
<v>0.5</v>
</c><c r="K2" s="9">
<v>4564645</v>
</c><c r="L2" s="10"/>
<c r="M2" s="11"/>
</row>
<row r="3" spans="1:13">
<!--
此处为什么没有A3？
因为excel中常规列如果没有值，此处就不记录，因此在读取excel是应该特别注意，否则可能导致列错乱
-->
<c r="B3" s="10"/>
<c r="C3" s="1"/>
<c r="D3" s="2"/>
<c r="E3" s="3"/>
<c r="F3" s="4"/>
<c r="G3" s="5"/>
<c r="H3" s="6"/>
<c r="I3" s="7"/>
<c r="J3" s="8"/>
<c r="K3" s="9"/>
</row> <!-- 该行每列都是常规列，因此没有值的就不记录在这里 -->
<row r="4" spans="1:13">
<c r="A4" s="12" t="s">
<v>15</v>
</c><c r="E4" t="s">
<v>16</v>
</c>
</row>
<!-- 5,6,7 行都是合并再一起的单元格 -->
<row r="5" spans="1:13">
<!-- 合并单元格的值记录在起始单元格里，其余单元格置空 -->
<c r="A5" s="14" t="s">
<v>17</v>
</c><c r="B5" s="13"/>
<c r="C5" s="13"/>
<c r="D5" s="13"/>
<c r="E5" s="13"/>
<c r="F5" s="13"/>
<c r="G5" s="13"/>
<c r="H5" s="13"/>
<c r="I5" s="13"/>
<c r="J5" s="13"/>
<c r="K5" s="13"/>
</row>
<row r="6" spans="1:13">
<c r="A6" s="13"/>
<c r="B6" s="13"/>
<c r="C6" s="13"/>
<c r="D6" s="13"/>
<c r="E6" s="13"/>
<c r="F6" s="13"/>
<c r="G6" s="13"/>
<c r="H6" s="13"/>
<c r="I6" s="13"/>
<c r="J6" s="13"/>
<c r="K6" s="13"/>
</row>
<row r="7" spans="1:13">
<c r="A7" s="13"/>
<c r="B7" s="13"/>
<c r="C7" s="13"/>
<c r="D7" s="13"/>
<c r="E7" s="13"/>
<c r="F7" s="13"/>
<c r="G7" s="13"/>
<c r="H7" s="13"/>
<c r="I7" s="13"/>
<c r="J7" s="13"/>
<c r="K7" s="13"/>
</row>
<row r="8" spans="1:13">
<c r="A8" s="13"/>
<c r="B8" s="13"/>
<c r="C8" s="13"/>
<c r="D8" s="13"/>
<c r="E8" s="13"/>
<c r="F8" s="13"/>
<c r="G8" s="13"/>
<c r="H8" s="13"/>
<c r="I8" s="13"/>
<c r="J8" s="13"/>
<c r="K8" s="13"/>
</row>
</sheetData>
<!-- 这个是记录哪个到哪个是合并单元格的信息的 -->
<mergeCells count="1">
<!-- A5到K8被合并了 -->
<mergeCell ref="A5:K8"/>
</mergeCells>
<!-- -->
<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="9" orientation="portrait" r:id="rId1"/>
<!-- 图形信息，由图可以看出，这里引用了draw的文档了-->
<drawing/>
</worksheet>
1.2.5、sharedStrings.xml文件
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="18" uniqueCount="18">
<!-- si存储的是字符串的值 -->
<!-- 此处存储的是sheet.xml中c(列)中属性带有t="s"的列的值，
带有t="s"字符串列的定位是通过c元素下的v元素中的值来定位的
此处对应的就是v的值为0的单元格的实际值
-->
<si>
<t>常规</t>
<phoneticPr fontId="1" type="noConversion"/>
</si>
<!-- sheet.xml中c元素中属性t="s"的子元素<v>1</v>的单元格的实际值 -->
<si>
<t>数值</t>
<phoneticPr fontId="1" type="noConversion"/>
</si>
<si>
<t>货币</t>
<phoneticPr fontId="1" type="noConversion"/>
</si>
<si>
<t>会计专用</t>
<phoneticPr fontId="1" type="noConversion"/>
</si>
<si>
<t>短日期</t>
<phoneticPr fontId="1" type="noConversion"/>
</si>
<si>
<t>长日期</t>
<phoneticPr fontId="1" type="noConversion"/>
</si>
<si>
<t>时间</t>
<phoneticPr fontId="1" type="noConversion"/>
</si>
<si>
<t>百分比</t>
<phoneticPr fontId="1" type="noConversion"/>
</si>
<si>
<t>分数</t>
<phoneticPr fontId="1" type="noConversion"/>
</si>
<si>
<t>科学计数</t>
<phoneticPr fontId="1" type="noConversion"/>
</si>
<si>
<t>文本</t>
<phoneticPr fontId="1" type="noConversion"/>
</si>
<si>
<t>common</t>
<phoneticPr fontId="1" type="noConversion"/>
</si>
<si>
<t>String</t>
<phoneticPr fontId="1" type="noConversion"/>
</si>
<si>
<t>$</t>
<phoneticPr fontId="1" type="noConversion"/>
</si>
<si>
<t>D1</t>
<phoneticPr fontId="1" type="noConversion"/>
</si>
<si>
<t>常规A</t>
<phoneticPr fontId="1" type="noConversion"/>
</si>
<si>
<r>
<rPr>
<sz val="11"/>
<color theme="1"/>
<rFont val="宋体"/>
<family val="3"/>
<charset val="134"/>
</rPr><t>常规</t>
</r><r>
<rPr>
<sz val="11"/>
<color theme="1"/>
<rFont val="Tahoma"/>
<family val="2"/>
<charset val="134"/>
</rPr><t>D</t>
</r>
<phoneticPr fontId="1" type="noConversion"/>
</si>
<si>
<t>说明: 为了简单，行数就设置的少点
第一行为标题行(本身是常规行)，分别标注了每列的属性。
第三行和第二行一样的列格式
第四行为常规行</t>
<phoneticPr fontId="1" type="noConversion"/>
</si>
</sst>
1.2.6、style.xml文件
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<!-- 数字格式化显示的定义 -->
<numFmts count="5">
<!-- 此处的numFmtId和cellXfs中的numFmtId对应 -->
<!-- 会计专用 -->
<!-- 该元素表示cellXfs中xf元素的numFmtId="44"对应的格式化样式 -->
<numFmt numFmtId="44" formatCode="_ "¥"* #,##0.00_ ;_ "¥"* \-#,##0.00_ ;_ "¥"* "-"??_ ;_ @_ "/>
<!-- 数值格式 -->
<numFmt numFmtId="176" formatCode="0.00_);[Red]\(0.00\)"/>
<!-- 货币 -->
<numFmt numFmtId="177" formatCode=""¥"#,##0.00_);[Red]\("¥"#,##0.00\)"/>
<!-- 长日期 -->
<numFmt numFmtId="178" formatCode="[$-F800]dddd\,\ mmmm\ dd\,\ yyyy"/>
<!-- 时间 -->
<numFmt numFmtId="179" formatCode="[$-F400]h:mm:ss\ AM/PM"/>
</numFmts>
<!-- 字体样式 -->
<fonts count="3">
<font>
<sz val="11"/>
<color theme="1"/>
<name val="Tahoma"/>
<family val="2"/>
<charset val="134"/>
</font>
<font>
<sz val="9"/>
<name val="Tahoma"/>
<family val="2"/>
<charset val="134"/>
</font>
<font>
<sz val="11"/>
<color theme="1"/>
<name val="宋体"/>
<family val="3"/>
<charset val="134"/>
</font>
</fonts>
<!-- 填充样式，包括背景色 -->
<fills count="2">
<fill>
<patternFill patternType="none"/>
</fill>
<fill>
<patternFill patternType="gray125"/>
</fill>
</fills>
<!-- 边框 -->
<borders count="1">
<border>
<left/>
<right/>
<top/>
<bottom/>
<diagonal/>
</border>
</borders>
<!-- 提供给cellXfs来调用 -->
<cellStyleXfs count="1">
<!-- 此处的numFmtId和cellXfs中的numFmtId对应 -->
<xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>
</cellStyleXfs>
<!-- 真正的样式定义 -->
<cellXfs count="15">
<!-- 此处的numFmtId和cellStyleXfs、numFmts等中的numFmtId对应 -->
<!-- sheet.xml中的c(列)元素属性中s的属性值都对应这里一个xf元素，s的属性值存储的就是xf的索引-->
<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
<!-- 该元素表示sheet.xml中c元素中s="1"对应的样式 -->
<xf numFmtId="176" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
<!-- 该元素表示sheet.xml中c元素中s="2"对应的样式 -->
<xf numFmtId="177" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
<xf numFmtId="44" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
<xf numFmtId="14" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
<xf numFmtId="178" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
<xf numFmtId="179" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
<xf numFmtId="10" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
<xf numFmtId="12" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
<xf numFmtId="11" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
<xf numFmtId="49" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
<xf numFmtId="13" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
<xf numFmtId="0" fontId="2" fillId="0" borderId="0" xfId="0" applyFont="1"/>
<xf numFmtId="0" fontId="2" fillId="0" borderId="0" xfId="0" applyFont="1" applyAlignment="1">
<alignment horizontal="left" vertical="top"/>
</xf>
<xf numFmtId="0" fontId="2" fillId="0" borderId="0" xfId="0" applyFont="1" applyAlignment="1">
<alignment horizontal="left" vertical="top" wrapText="1"/>
</xf>
</cellXfs>
<cellStyles count="1">
<cellStyle name="常规" xfId="0" builtinId="0"/>
</cellStyles>
<dxfs count="0"/>
<tableStyles count="0" defaultTableStyle="TableStyleMedium9" defaultPivotStyle="PivotStyleLight16"/>
</styleSheet>
二、使用java读取EXCEL
1. 依赖的jar包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.xerces</groupId>
<artifactId>com.springsource.org.apache.xerces</artifactId>
<version>2.8.1</version>
</dependency>
<dependency>
<groupId>commons-lang</groupId>
<artifactId>commons-lang</artifactId>
<version>2.6</version>
</dependency>
2. IExcelRowReader.java
package com.wms.xml;import java.util.List;public interface IExcelRowReader {
void getRows(int sheetIndex, int curRow, List<String> rowlist);
}
3. ExcelReaderUtil.java
package com.wms.xml;import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.xml.sax.SAXException;import java.io.IOException;public class ExcelReaderUtil {
public static void read2007Excel(IExcelRowReader rowReader, String path) throws
OpenXML4JException, SAXException, IOException {
Excel2007Reader excel2007Reader = new Excel2007Reader();
excel2007Reader.setRowReader(rowReader);
excel2007Reader.load(path);
}}
4. Excel2007Reader.java
package com.wms.xml;import org.apache.commons.lang.StringUtils;
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.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
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;
import org.xml.sax.helpers.XMLReaderFactory;import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;public class Excel2007Reader extends DefaultHandler { //列元素
private static final String C_ELEMENT = "c";
//列中属性r
private static final String R_ATTR = "r";
//列中的v元素
private static final String V_ELEMENT = "v";
//列中的t元素
private static final String T_ELEMENT = "t";
//列中属性值
private static final String S_ATTR_VALUE = "s";
//列中属性值
private static final String T_ATTR_VALUE = "t"; //sheet r:Id前缀
private static final String RID_PREFIX = "rId"; //行元素
private static final String ROW_ELEMENT = "row"; //时间格式化字符串
private static final String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss"; //saxParser
private static final String CLASS_SAXPARSER = "org.apache.xerces.parsers.SAXParser"; //填充字符串
private static final String CELL_FILL_STR = "@";
//列的最大位数
private static final int MAX_CELL_BIT = 3; //excel 2007 的共享字符串表,对应sharedString.xml
private SharedStringsTable sharedStringsTable; private final DataFormatter dataFormatter = new DataFormatter(); //当前行
private int curRow; //当前列
private int curCell; //上一次的内容
private String lastContent; //是否是String类型的
private boolean nextIsString; //单元数据类型
private CellDataType cellDataType; //当前列坐标， 如A1，B5
private String curCoordinate; //前一个列的坐标
private String preCoordinate; //行的最大列坐标
private String maxCellCoordinate; //单元格的格式表，对应style.xml
private StylesTable stylesTable; //单元格存储格式的索引，对应style.xml中的numFmts元素的子元素索引
private int numFmtIndex;
//单元格存储的格式化字符串，nmtFmt的formateCode属性的值
private String numFmtString; //sheet的索引
private int sheetIndex = -1; //存储每行的列元素
List<String> rowCellList = new ArrayList<String>(); //单元格的数据格式
enum CellDataType {
BOOL("b"),
ERROR("e"),
FORMULA("str"),
INLINESTR("inlineStr"),
SSTINDEX("s"),
NUMBER(""),
DATE("m/d/yy"),
NULL(""); private String name; CellDataType(String name) {
this.name = name;
} public String getName() {
return name;
} public void setName(String name) {
this.name = name;
}
} private IExcelRowReader rowReader; public void setRowReader(IExcelRowReader rowReader) {
this.rowReader = rowReader;
} /**
* 读取excel中的制定索引sheet
*
* @param path
* @param sheetIndex
* @throws OpenXML4JException
* @throws IOException
* @throws SAXException
*/
public void load(String path, int sheetIndex) throws OpenXML4JException, IOException,
SAXException {
OPCPackage opcPackage = OPCPackage.open(path);
XSSFReader xssfReader = new XSSFReader(opcPackage);
//获取styleTable
stylesTable = xssfReader.getStylesTable();
//获取共享字符串表
SharedStringsTable sharedStringsTable = xssfReader.getSharedStringsTable();
this.sharedStringsTable = sharedStringsTable;
//获取解析器
XMLReader xmlReader = fetchSheetReader(sharedStringsTable);
// 根据 rId# 或 rSheet# 查找sheet
InputStream sheetInputStream = xssfReader.getSheet(RID_PREFIX + sheetIndex);
InputSource sheetInputSource = new InputSource(sheetInputStream);
xmlReader.parse(sheetInputSource);
//关闭流
closeStream(sheetInputStream);
} /**
* 遍历所有的sheet
*
* @param path
* @throws IOException
* @throws OpenXML4JException
* @throws SAXException
*/
public void load(String path) throws IOException, OpenXML4JException, SAXException {
OPCPackage opcPackage = OPCPackage.open(path);
XSSFReader xssfReader = new XSSFReader(opcPackage);
stylesTable = xssfReader.getStylesTable();
SharedStringsTable sharedStringsTable = xssfReader.getSharedStringsTable();
this.sharedStringsTable = sharedStringsTable;
XMLReader xmlReader = fetchSheetReader(sharedStringsTable);
Iterator<InputStream> sheetsInputStream = xssfReader.getSheetsData();
while (sheetsInputStream.hasNext()) {
curRow = 0;
sheetIndex++;
InputStream sheetInputStream = sheetsInputStream.next();
InputSource sheetInputSource = new InputSource(sheetInputStream);
xmlReader.parse(sheetInputSource);
closeStream(sheetInputStream);
} } /**
* 获取sheet的解析器
*
* @param sharedStringsTable
* @return
* @throws SAXException
*/
public XMLReader fetchSheetReader(SharedStringsTable sharedStringsTable) throws SAXException {
XMLReader xmlReader = XMLReaderFactory.createXMLReader(CLASS_SAXPARSER);
xmlReader.setContentHandler(this);
return xmlReader;
} @Override
public void startElement(String uri, String localName, String qName, Attributes attributes)
throws SAXException {
//c 表示列
if (C_ELEMENT.equals(qName)) { //获取当前列坐标
String tempCurCoordinate = attributes.getValue(R_ATTR);
//前一列为null，则将其设置为"@",A为第一列，ascii码为65，前一列即为@，ascii码64
if (preCoordinate == null) {
preCoordinate = CELL_FILL_STR;
} else {
//存在，则前一列要设置为上一列的坐标
preCoordinate = curCoordinate;
}
//重置当前列
curCoordinate = tempCurCoordinate; //设置单元格类型
setCellType(attributes);
//t 属性值
String type = attributes.getValue(T_ATTR_VALUE);
//s 表示该列为字符串
if (S_ATTR_VALUE.equals(type)) {
nextIsString = true;
}
} lastContent = "";
} /**
* 设置单元格的类型
*
* @param attribute
*/
private void setCellType(Attributes attribute) {
//默认是数字类型
cellDataType = CellDataType.NUMBER;
//重置numFmtIndex,numFmtString的值
numFmtIndex = 0;
numFmtString = ""; //单元格的格式类型
String cellType = attribute.getValue(T_ATTR_VALUE);
//获取单元格的xf索引，对应style.xml中cellXfs的子元素xf
String xfIndexStr = attribute.getValue(S_ATTR_VALUE); if (CellDataType.BOOL.getName().equals(cellType)) {
cellDataType = CellDataType.BOOL;
} else if (CellDataType.ERROR.getName().equals(cellType)) {
cellDataType = CellDataType.ERROR;
} else if (CellDataType.INLINESTR.getName().equals(cellType)) {
cellDataType = CellDataType.INLINESTR;
} else if (CellDataType.FORMULA.getName().equals(cellType)) {
cellDataType = CellDataType.FORMULA;
} else if (CellDataType.SSTINDEX.getName().equals(cellType)) {
cellDataType = CellDataType.SSTINDEX;
} if (xfIndexStr != null) {
int xfIndex = Integer.parseInt(xfIndexStr);
XSSFCellStyle xssfCellStyle = stylesTable.getStyleAt(xfIndex);
numFmtIndex = xssfCellStyle.getDataFormat();
numFmtString = xssfCellStyle.getDataFormatString(); if (CellDataType.DATE.getName().equals(numFmtString)) {
cellDataType = CellDataType.DATE;
numFmtString = DATE_FORMAT;
} if (numFmtString == null) {
cellDataType = CellDataType.NULL;
numFmtString = BuiltinFormats.getBuiltinFormat(numFmtIndex);
}
} } @Override
public void endElement(String uri, String localName, String qName) throws SAXException {
String value = StringUtils.trim(lastContent);
if (T_ELEMENT.equals(qName)) {
rowCellList.add(curCell++, value);
} else if (C_ELEMENT.equals(qName)) {
value = getDataValue(value);
//补全单元格之间的空格
fillBlackCell(curCoordinate, preCoordinate, false);
rowCellList.add(curCell++, value);
} else {
//如果是row标签，说明已经到了一行的结尾
if (ROW_ELEMENT.equals(qName)) {
//最大列坐标以第一行的为准
if (curRow == 0) {
maxCellCoordinate = curCoordinate;
} //补全一行尾部可能缺失的单元格
if (maxCellCoordinate != null) {
fillBlackCell(maxCellCoordinate, curCoordinate, true);
} rowReader.getRows(sheetIndex, curRow, rowCellList); //一行结束
//清空rowCellList,
rowCellList.clear();
//行数增加
curRow++;
//当前列置0
curCell = 0;
//置空当前列坐标和前一列坐标
curCoordinate = null;
preCoordinate = null;
}
}
} /**
* 填充空白单元格
*
* @param curCoordinate
* @param preCoordinate
*/
private void fillBlackCell(String curCoordinate, String preCoordinate, boolean isEnd) {
if (!curCoordinate.equals(preCoordinate)) {
int len = calNullCellCount(curCoordinate, preCoordinate, isEnd);
for (int i = 0; i < len; i++) {
rowCellList.add(curCell++, "");
}
}
} /**
* 计算当前单元格和前一个单元格之间的空白单元格数量
* 如果是尾部则不减1
*
* @param curCoordinate
* @param preCoordinate
* @return
*/
private int calNullCellCount(String curCoordinate, String preCoordinate, boolean isEnd) {
// excel2007最大行数是1048576，最大列数是16384，最后一列列名是XFD
String curCellCoordinate = curCoordinate.replaceAll("\\d+", "");
String preCellCoordinate = preCoordinate.replaceAll("\\d+", ""); curCellCoordinate = fillChar(curCellCoordinate, MAX_CELL_BIT, CELL_FILL_STR);
preCellCoordinate = fillChar(preCellCoordinate, MAX_CELL_BIT, CELL_FILL_STR); char[] cur = curCellCoordinate.toCharArray();
char[] pre = preCellCoordinate.toCharArray(); int len = (cur[0] - pre[0]) * 26 * 26 + (cur[1] - pre[1]) * 26 + (cur[2] - pre[2]);
if (!isEnd) {
len = len - 1;
}
return len;
} /**
* 将不足指定位数的字符串补全，高位补上指定字符串
*
* @param cellCoordinate
* @param maxLen
* @param fillChar
* @return
*/
private String fillChar(String cellCoordinate, int maxLen, String fillChar) {
int coordinateLen = cellCoordinate.length();
if (coordinateLen < maxLen) {
for (int i = 0; i < (maxLen - coordinateLen); i++) {
cellCoordinate = fillChar + cellCoordinate;
}
}
return cellCoordinate;
} private String getDataValue(String lastContent) {
String value = "";
XSSFRichTextString xssfRichTextString = null;
switch (cellDataType) {
case BOOL:
char first = lastContent.charAt(0);
value = first == '0' ? "FALSE" : "TRUE";
break;
case ERROR:
value = "\"ERROR:" + lastContent + '"';
break;
case FORMULA:
value = '"' + lastContent + '"';
break;
case INLINESTR:
xssfRichTextString = new XSSFRichTextString(lastContent);
value = xssfRichTextString.getString();
xssfRichTextString = null;
break;
case SSTINDEX:
try {
int index = Integer.parseInt(lastContent);
xssfRichTextString = new XSSFRichTextString(sharedStringsTable.getEntryAt
(index));
value = xssfRichTextString.getString();
xssfRichTextString = null;
} catch (NumberFormatException e) {
value = lastContent;
}
break;
case NUMBER:
if (numFmtString != null) {
value = dataFormatter.formatRawCellContents(Double.parseDouble(lastContent),
numFmtIndex, numFmtString);
} else {
value = lastContent;
}
value = value.replace("_", "");
break;
case DATE:
value = dataFormatter.formatRawCellContents(Double.parseDouble(lastContent),
numFmtIndex, numFmtString);
break;
default:
value = "";
break;
} return value;
} @Override
public void characters(char[] ch, int start, int length) throws SAXException {
// 得到单元格内容的值
lastContent += new String(ch, start, length);
} /**
* 关闭流
*
* @param inputStream
*/
public void closeStream(InputStream inputStream) {
if (inputStream != null) {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
} public static void main(String[] args) {
// System.out.println("m/d/yy" == CellDataType.DATE.getName());
// System.out.println('"' + "ss" + '"');
// char[] letter = "@@A".toCharArray();
// char[] letter_1 = "@@B".toCharArray();
// int res = (letter[0] - letter_1[0]) * 26 * 26 + (letter[1] - letter_1[1]) * 26 +
// (letter[2] - letter_1[2]);
// System.out.println(res);
// System.out.println(Integer.valueOf('@'));
IExcelRowReader rowReader = new ExcelRowReader();
try {
// ExcelReaderUtil.readExcel(rowReader,
// "E://2016-07-04-011940a.xls");
System.out.println("**********************************************");
ExcelReaderUtil.read2007Excel(rowReader, "F:\\studygit\\test.xlsx");
} catch (Exception e) {
e.printStackTrace();
}
}}
