POI——将Excel的一个sheet复制到新的Excel中
一、场景说明
某个xlsx格式的excel，sheet页很多，每个sheet页中都有图片、图形、绘图、图表等各种东西，现在需要将各个sheet页分离出来，每个sheet页单独保存为一个Excel
二、探索（可跳过）
1、使用POI提供的高级API，将一个Excel的sheet复制到另一个Excel，大致步骤如下
① 打开Excel
② 遍历sheet页
③ 遍历每个sheet页的每一个可见单元格，复制文本、样式、边框
● 这一步没有过多的问题，最多慢一些
④ 遍历每个sheet页的shapes，获取图形、图片信息，如果存在图形组，还需要递归遍历
● 到这里会出现问题（使用POI的高级API）
> 图片复制的过程中，不仅需要复制其原始文件，而且还要复制图片在sheet页中的坐标，若图片在单元格内，则位置的处理相对简单；
> 若不在单元格内，而是在单元格“上面”（即图层），则图片定位的获取则相对麻烦；
> 若图片位于一个图形组内，则图片的定位此时不依赖于sheet窗口，而是所在图形组内的相对位置；目前POI的高级API，无法直接获取图形组内的图片的相对位置（截止到POI 5.2.5）
> 若图片不是PNG等常见格式，而是SVG或者OBJ，POI也同样不提供直接获取的方法
> 到了图形的层面，POI的高级API甚至无法复制
● 综上，夭折
2、使用POI提供的低级API，将一个Excel的sheet复制到另一个Excel
● 大致步骤是相同的
● 不同点在于，我们可以直接通过复制XML的形式，轻松愉快的复制上面提到的图形
● 低级API，即 extends org.apache.xmlbeans.XmlObject 的各种东西，比如 CTWorkbook、CTWorksheet、CTRow等，他们都可以直接获取到xlsx文件的各种XML格式文件的内容
● 我们可以通过创建一个新的workbook和worksheet，copy旧的workbook、worksheet的xml，新建drawing图层，复制旧的worksheet关联的drawing.xml，此时便解决了图形的问题（前提需要处理好各种引用关系）
● 比如，workbook.xml中，涉及到sheet的引用
<sheets>
<sheet name="Sheet1" sheetId="1" r:id="rId1" />
</sheets>
● workbook.xml.rels中，有各种引用位置
<?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/styles"
Target="styles.xml" />
<Relationship Id="rId2"
Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme"
Target="theme/theme1.xml" />
<Relationship Id="rId1"
Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"
Target="worksheets/sheet1.xml" />
</Relationships>
● worksheet.xml中，有关于drawing（图层）的引用
<drawing r:id="rId1" />
● worksheet.xml.rels中，有关于sheet页引用的组件的位置
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1"
Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing"
Target="../drawings/drawing1.xml" />
</Relationships>
● drawing.xml描述了各种图片的引用、图形的信息和位置
● 在 drawing.xml.rels 中，描述了drawing.xml实际引用的图片的地址
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId8"
Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/customXml"
Target="../ink/ink2.xml" />
<Relationship Id="rId3"
Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image"
Target="../media/image3.png" />
.....
</Relationships>
● 当新建一个sheet时，会在workbook.xml和workbook.xml.refs中创建sheet相关的引用信息；当在sheet中创建drawing时，会在sheet.xml和sheet.xml.refs中创建drawing的相关引用信息；图片、图形等同理
● 因此，要完整复制一个sheet的内容到新的workbook，使用复制XML的形式，则需要在workbook.xml层级，过滤出需要复制的sheet，同时使用新创建的sheet的引用ID，覆盖掉旧的sheet的引用ID；sheet引用的东西、drawing引用的东西，凡是新建的，都要重新处理引用，否则即使复制了XML，也无法完美的在新的workbook中展示
● 涉及到图片的时候，由于图片原始文件都在media文件夹中，且是所有sheet页共用的，因此复制sheet的时候，需要通过引用关系，过滤出需要复制的图片；这里还涉及到图片格式的问题，POI可能不支持某些图片格式，比如在Excel中插入一个图标，如下所示。在Excel展示的时候是矢量图（SVG）格式，在media中其实是保存了2份图片，一个是png，一个是svg，如下下图所示
● 当尝试建立新的workbook和SVG的关系的时候，会遇见麻烦，POI并不支持SVG类型，于是，需要扩展POI，使其支持SVG
/**
* Adds a picture to the workbook.
*
* @param pictureData The bytes of the picture
* @param format The format of the picture.
*
* @return the index to this picture (1 based).
* @see #PICTURE_TYPE_EMF
* @see #PICTURE_TYPE_WMF
* @see #PICTURE_TYPE_PICT
* @see #PICTURE_TYPE_JPEG
* @see #PICTURE_TYPE_PNG
* @see #PICTURE_TYPE_DIB
*/
int addPicture(byte[] pictureData, int format);
扩展：
public final class CXSSFRelation extends POIXMLRelation {
public static final CXSSFRelation IMAGE_SVG = new CXSSFRelation(
"image/svg",
PackageRelationshipTypes.IMAGE_PART,
"/xl/media/image#.svg",
CXSSFPictureData::new, CXSSFPictureData::new
);
private CXSSFRelation(String type, String rel, String defaultName,
NoArgConstructor noArgConstructor,
PackagePartConstructor packagePartConstructor) {
super(type, rel, defaultName, noArgConstructor, packagePartConstructor, null);
}
}
● 当继续探索，又会发现另一个问题，当图片是OBJ时候…，当sheet页中包含更多的内容，比如图表等…除非穷尽Excel的sheet页中所有可添加的内容，并为此写代码处理
3、删除多余sheet页
● 使用POI删除多余sheet的方式的确可以分离各个sheet到新的Excel，但是，被删除的sheet页的图片等信息不会删除，导致Excel大小几乎不会变；
● 比如，一个Excel有3个sheet页面，每个sheet页 10M，假设整个Excel 30M，使用POI删除sheet2和sheet3，并保存为新的Excel，则新的Excel还是30M
● 如果可以接受Excel大小不变，删除多余sheet的方式是非常快速方便的
三、复制实践
1、效果：拆分Excel中的所有sheet，每个sheet单独保存为一个Excel文件，每个Excel不包含多余内容（其它sheet的内容）
2、步骤
● 读取 src workbook
● 获取需要复制的 sheet 的 CTSheet
● 获取 srcSheet 和 sheetIndex
● 获取整个src workbook 的 PackagePart
● 找到 src workbook 的 PackagePart
● 从 src workbook 的 PackagePart 找到 待复制的 worksheet 的 PackagePart
● 创建新的 workbook（destWb）
● 将 待复制的worksheet 关联的内容，复制过来
● 创建新的 worksheet（destSheet）
● 将 srcSheet 的 relation 复制给 destSheet
● 复制 src sheet.xml 的内容
● 复制 sheet.xml 关联的 sharedStrings.xml 内容
● 复制 printArea, 返回sheet页面的起止colnum
● 从 sheet.xml 提取样式引用ID，为复制 styles.xml 的内容做准备
● 复制 style.xml 整体的样式结构 和 命名空间
● 复制 style.xml 中 font、fill、border、numFmts和单元格样式，并修改相应的引用ID
● 保存 destWb
四、部分代码
public static XSSFWorkbook handle(XSSFWorkbook srcWb, String waitCopySheetName) {
try {
// 获取需要复制的 sheet 的 CTSheet
CTSheet srcCtSheet = srcWb.getCTWorkbook().getSheets().getSheetList().stream().filter(r -> waitCopySheetName.equals(r.getName())).findFirst().orElseThrow(() -> new RuntimeException("未找到[" + waitCopySheetName + "]"));
// 获取 srcSheet 和 sheetIndex
XSSFSheet srcSheet = srcWb.getSheet(waitCopySheetName);
int sheetIndex = srcWb.getSheetIndex(srcSheet);
// 获取整个workbook的PackagePart
List<PackagePart> srcWbAllPackPartList = srcWb.getPackagePart().getPackage().getParts();
// 找到 workbook 的 PackagePart
PackagePart srcWorkbookPackagePart = ExcelPackagePartUtil.findPackagePartByPartName(srcWbAllPackPartList, Common.WORKBOOK_PART_NAME);
// 从 workbook 的 PackagePart 找到 待复制的 worksheet 的 PackagePart
PackageRelationship waitCopySheetPackageRelationship = srcWorkbookPackagePart.getRelationship(srcCtSheet.getId());
if(Objects.isNull(waitCopySheetPackageRelationship)) {
throw new RuntimeException("未在workbook中找到[" + waitCopySheetName + "]的 RelationShip");
}
PackagePart waitCopySheetPackagePart = srcWorkbookPackagePart.getRelatedPart(waitCopySheetPackageRelationship);
if(Objects.isNull(waitCopySheetPackagePart)) {
throw new RuntimeException("未在workbook中找到[" + waitCopySheetName + "]的 PackagePart");
}
// 创建新的workbook
XSSFWorkbook destWb = new XSSFWorkbook();
// 将 waitCopySheetPackagePart 关联的内容，复制过来
ExcelPackagePartUtil.recursiveRegistPart(destWb, waitCopySheetPackagePart);
// 创建新的 worksheet
XSSFSheet destSheet = destWb.createSheet(waitCopySheetName);
PackagePart destSheetPackagePart = destSheet.getPackagePart();
// 将 srcSheet 的 relation 复制给 destSheet
Map<PackagePart, PackageRelationship> relationAndPackagePart = ExcelPackagePartUtil.findRelationAndPackagePart(waitCopySheetPackagePart);
relationAndPackagePart.forEach(((packagePart, packageRelationship) -> {
destSheetPackagePart.addRelationship(
packagePart.getPartName(),
packageRelationship.getTargetMode(),
packageRelationship.getRelationshipType(),
packageRelationship.getId());
}));
// 复制 src sheet.xml 的内容
destSheet.getCTWorksheet().set(srcSheet.getCTWorksheet());
// 复制 sheet.xml 关联的 sharedStrings.xml 内容
ExcelCTUtil.copySharedStrings(srcWb, destWb, destSheet);
// 复制 printArea, 返回sheet页面的起止colnum
int[] sheetColIndexRange = ExcelCommonUtil.copyPrintArea(srcWb, destWb, srcSheet, sheetIndex);
// 从 sheet.xml 提取样式引用ID，为复制 styles.xml 的内容做准备
List<Long> srcSheetCellStyleRefIdList = ExcelCTUtil.extractStyleRef(srcSheet);
// 复制 style.xml 整体的样式结构 和 命名空间
destWb.getStylesSource().getCTStylesheet().set(srcWb.getStylesSource().getCTStylesheet());
// 复制 style.xml 中 font、fill、border、numFmts和单元格样式，并修改相应的引用ID
ExcelCTUtil.copyAndSetStyle(srcWb, destWb, destSheet, srcSheetCellStyleRefIdList);
// 返回
return destWb;
} catch (IOException e) {
throw new RuntimeException("发生IO错误", e);
} catch (InvalidFormatException e) {
throw new RuntimeException("findRelationAndPackagePart发生错误", e);
} catch (Exception e) {
throw new RuntimeException("发生未知错误", e);
}
}
五、全部代码
https://github.com/kanseiu/Office-Converter.git
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
