EXCELPOI合并单元格解析
EXCEL POI 合并单元格解析
1、EXCEL2007的合并单元格的XML格式
<mergeCells count="2">
<mergeCell ref="A1:A2"/>
<mergeCell ref="B3:B4"/>
</mergeCells>
mergeCells ⾥⾯包含了所有合并单元格的情况，需要读取到mergeCell就可以知道 该Sheet的所有单元格的合并情况2 、SAX解析实现
import org.apachemons.lang3.StringUtils;
import org.apache.ptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.del.SharedStringsTable;
l.sax.*;
l.sax.helpers.DefaultHandler;
l.sax.helpers.XMLReaderFactory;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
/**
* 类名称：CellMergeParser
* 类描述：
*/
public class CellMergeParser {
private XSSFReader xssfReader;
List<String> merges = new ArrayList<>();
InputStream is;
/**
* 构造⽅法
*/
public CellMergeParser(String filename) throws Exception {
if (StringUtils.isEmpty(filename))
throw new Exception("⽂件名不能空");
is = new FileInputStream(new File(filename));
OPCPackage pkg = OPCPackage.open(is);
init(pkg);
}
public CellMergeParser(InputStream is) throws Exception {
if (null == is)
throw new Exception("流不能空");
OPCPackage pkg = OPCPackage.open(is);
init(pkg);
}
private void init(OPCPackage pkg) throws IOException, OpenXML4JException {
xssfReader = new XSSFReader(pkg);
}
/**
* 获取sheet
*
* @throws Exception
*/
public List<String> process(int sheetIndex) throws Exception {
SharedStringsTable sst = null;
try {
sst = SharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
Iterator<InputStream> it = SheetsData();
int index = 0;
while (it.hasNext()) {
index++;
if (index == sheetIndex) {
InputStream sheet = it.next();
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource);
sheet.close();
return merges;
}
}
throw new IllegalArgumentException("sheet index not exist");
} finally {
try {
is.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
* 加载sax 解析器
*
* @param sst
* @return
* @throws SAXException
*/
private XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
XMLReader parser = ateXMLReader("s.parsers.SAXParser");        ContentHandler handler = new PagingHandler(sst);
parser.setContentHandler(handler);
return parser;
}
/**
* l.sax.helpers.DefaultHandler javadocs
*/
private class PagingHandler extends DefaultHandler {
private SharedStringsTable sst;
private PagingHandler(SharedStringsTable sst) {
this.sst = sst;
}
/**
* 开始元素
*/
@Override
@Override
public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
//获取所有的 cell
if ("mergeCell".equalsIgnoreCase(name)) {
String value = Value("ref");
reference groupmerges.add(value);
}
}
/**
* 获取value
*/
@Override
public void endElement(String uri, String localName, String name) throws SAXException {
}
@Override
public void characters(char[] ch, int start, int length) throws SAXException {
}
}
/**
* 转换成 merge bean
*
* @return
*/
public List<MergeBean> convertBean() {
List<MergeBean> mergeBeans = new ArrayList<>();
merges.forEach(v -> {
MergeBean mergeBean = RowColUtil.calcMerge(v);
mergeBeans.add(mergeBean);
});
return mergeBeans;
}
}
通过对EXCEL的格式理解，使⽤XML的SAX解析⽅案 解析出所有的CELLMerge元素，然后转化为下标范围
import org.apache.poi.ss.util.CellReference;
import java.util.ArrayList;
import Matcher;
import Pattern;
/**
* A1:A2
*
* @author dean <tanping>
* @version 1.0.0
* @date 2020/01/08 17:10
* @since 1.0.0
*/
public class RowColUtil {
/**
* 正则匹配 mergeBean
* @param group
* @return
*/
public  static  MergeBean calcMerge(String group){
group = LowerCase();
String[] rw = group.split(":");
Pattern p = Patternpile("[a-z]+|\\d+");
Matcher m = p.matcher(rw[0]);
ArrayList<String> start = new ArrayList<>();
while (m.find()) {
start.up());
}
m = p.matcher(rw[1]);
ArrayList<String> end = new ArrayList<>();
while (m.find()) {
end.up());
}
int firstCol = (0));        int firstRow = Integer.(1));
int endCol = (0));        int endRow = Integer.(1));
MergeBean mergeBean = new MergeBean();
mergeBean.setFirstColumn(firstCol);
mergeBean.setFirstRow(firstRow);
mergeBean.setLastColumn(endCol);
mergeBean.setLastRow(endRow);
return mergeBean;
}
}
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
/**
* Merge group组
* [A1:A2, B3:B4]
*  row 从1 开始
* @author dean <tanping>
* @version 1.0.0
* @date 2020/01/08 17:06
* @since 1.0.0
*/
@Getter
@Setter
@ToString
public class MergeBean {
int firstRow;
int firstColumn;
int lastRow;
int lastColumn;
/**
* 是否包含
* @param row
* @param column
* @return
*/
public boolean isMergeGroup(int row,int column){
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {                return true;
}
}
return false;
}
/**
* ⽣成唯⼀值
* @return
*/
public String createdKey(){
return firstColumn+"-"+firstRow;
}
}
import lombok.Getter;
import lombok.Setter;
import org.apache.poi.ss.util.CellReference;
import javax.ws.rs.GET;
import java.util.HashMap;
import java.util.List;
import urrent.ConcurrentHashMap;
/**
* 分组缓存
*
* @author dean <tanping>
* @version 1.0.0
* @date 2020/01/08 17:22
* @since 1.0.0
*/
@Getter