需求
将一个Excel中多个Sheet页转换为多个单Sheet的文件
思路
- clone Excel中的多个Sheet页
- 将clone的新Sheet页保存到Workbook中
- 将workbook写入新文件
方案研究
-
根据官网介绍,目前Apache POI是支持基于UserModel下的Sheet Clone。
POI 4.0以上提供了cloneSheet的函数,但该函数只适用于再同一个Workbook中进行sheet的拷贝
-
Spire.XLS for Java
简单、方便、专业(只能使用免费的)
目标文件(多sheet)
- sheet1
2. sheet2
- sheet3
Code验证
Apache POI
- POM.xml添加依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.3</version>
</dependency>
<!-- 2007版本 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version>
</dependency>
- code
- 定义策略文件
package com.er.excelresolve.resolve;
import org.apache.poi.poifs.filesystem.FileMagic;
import org.apache.poi.ss.usermodel.Sheet;
/**
* @author admin
* @version 1.0
* @description resolve strategy
* @date 2023/9/2
*/
public interface ResolveStrategy {
/**
* is support
* @param fileMagic file type
* @return boolean result
*/
boolean isSupport(FileMagic fileMagic);
/**
* copy sheet
* @param srcSheet source
* @param targetSheet target
*/
void copySheet(Sheet srcSheet, Sheet targetSheet);
}
- 针对不同2003版和2007版本Excel的不同,定义各自的策略实现类
package com.er.excelresolve.resolve;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.FileMagic;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellUtil;
import java.util.List;
/**
* 2003 版本 excel处理
* @author admin
* @version 1.0
* @description
* @date 2023/9/3
*/
public class HSSFResolveStrategy implements ResolveStrategy {
@Override
public boolean isSupport(FileMagic fileMagic) {
return fileMagic == FileMagic.OLE2;
}
@Override
public void copySheet(Sheet srcSheet, Sheet targetSheet) {
this.copyRow((HSSFSheet) srcSheet, (HSSFSheet) targetSheet);
this.copyImage((HSSFSheet) srcSheet, (HSSFSheet) targetSheet);
}
private void copyImage(HSSFSheet srcSheet, HSSFSheet targetSheet) {
HSSFPatriarch drawingPatriarch = srcSheet.getDrawingPatriarch();
if (drawingPatriarch == null) {
return;
}
HSSFPatriarch patriarch = targetSheet.createDrawingPatriarch();
List<HSSFShape> children = drawingPatriarch.getChildren();
children.parallelStream().forEach(shape -> {
if (shape instanceof HSSFPicture) {
HSSFPicture picture = (HSSFPicture) shape;
HSSFPictureData pictureData = picture.getPictureData();
targetSheet.getWorkbook().addPicture(pictureData.getData(), pictureData.getFormat());
patriarch.createPicture(picture.getClientAnchor(), picture.getPictureIndex());
} else if (shape instanceof HSSFSimpleShape) {
// TODO: 目前 HSSFChart 只能从sheet中获取,不能添加,等待后续....
HSSFChart[] sheetCharts = HSSFChart.getSheetCharts(targetSheet);
}
});
}
private void copyRow(HSSFSheet srcSheet, HSSFSheet targetSheet) {
int firstRowNum = srcSheet.getFirstRowNum();
int lastRowNum = srcSheet.getLastRowNum();
if (lastRowNum < firstRowNum) {
return;
}
CellCopyPolicy copyPolicy = new CellCopyPolicy.Builder()
.condenseRows(true)
.cellFormula(true).build();
for (int i = firstRowNum; i <= lastRowNum; i++) {
Row row = srcSheet.getRow(i);
if (row == null) {
continue;
}
// TODO: HSSF提供的 copyRowFrom 存在一定的问题,谨慎使用
HSSFRow destRow = targetSheet.createRow(i);
for (Cell c : row) {
HSSFCell cell = destRow.createCell(c.getColumnIndex(), CellType.FORMULA);
cell.setHyperlink(c.getHyperlink());
cell.setCellType(c.getCellType());
CellUtil.copyCell(c, cell, copyPolicy, null);
}
}
}
}
package com.er.excelresolve.resolve;
import org.apache.poi.poifs.filesystem.FileMagic;
import org.apache.poi.ss.usermodel.CellCopyPolicy;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.*;
import java.util.ArrayList;
import java.util.List;
/**
* 2007 以上版本 excel处理
* @author admin
* @version 1.0
* @description
* @date 2023/9/3
*/
public class XSSFResolveStrategy implements ResolveStrategy {
@Override
public boolean isSupport(FileMagic fileMagic) {
return fileMagic == FileMagic.OOXML;
}
@Override
public void copySheet(Sheet srcSheet, Sheet targetSheet) {
this.copyRow((XSSFSheet) srcSheet, (XSSFSheet) targetSheet);
this.copyImage((XSSFSheet) srcSheet, (XSSFSheet) targetSheet);
}
private void copyImage(XSSFSheet srcSheet, XSSFSheet targetSheet) {
XSSFDrawing drawingPatriarch = srcSheet.getDrawingPatriarch();
if (null == drawingPatriarch) {
return;
}
// copy chart
List<XSSFChart> charts = drawingPatriarch.getCharts();
XSSFDrawing xssfDrawing = targetSheet.createDrawingPatriarch();
if (!charts.isEmpty()) {
charts.parallelStream().forEach(xssfDrawing::importChart);
}
// copy image
List<XSSFShape> shapes = drawingPatriarch.getShapes();
XSSFWorkbook workbook = targetSheet.getWorkbook();
for (int i = 0; i < shapes.size(); i++) {
XSSFShape shape = shapes.get(i);
if (!(shape instanceof XSSFPicture)) {
return;
}
XSSFPicture picture = (XSSFPicture) shape;
XSSFPictureData pictureData = picture.getPictureData();
workbook.addPicture(pictureData.getData(), pictureData.getPictureType());
xssfDrawing.createPicture(picture.getClientAnchor(), i);
}
}
private void copyRow(XSSFSheet srcSheet, XSSFSheet targetSheet) {
int firstRowNum = srcSheet.getFirstRowNum();
int lastRowNum = srcSheet.getLastRowNum();
if (lastRowNum <= firstRowNum) {
return;
}
List<Row> rows = new ArrayList<>(lastRowNum);
for (int i = firstRowNum; i <= lastRowNum; i++) {
XSSFRow row = srcSheet.getRow(i);
if (null != row) {
rows.add(row);
}
}
CellCopyPolicy copyPolicy = new CellCopyPolicy.Builder().build();
targetSheet.copyRows(rows, firstRowNum, copyPolicy);
}
}
- 定义策略上下文
package com.er.excelresolve.resolve;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.poifs.filesystem.FileMagic;
import org.apache.poi.ss.usermodel.Sheet;
import java.util.ArrayList;
import java.util.List;
/**
* 策略 context
* @author admin
* @version 1.0
* @description
* @date 2023/9/2
*/
@Slf4j
public class ResolveContext {
/**
* excel type
*/
private final FileMagic fileMagic;
/**
* resolve strategy
*/
private static final List<ResolveStrategy> RESOLVE_STRATEGY = new ArrayList<>();
static {
RESOLVE_STRATEGY.add(new HSSFResolveStrategy());
RESOLVE_STRATEGY.add(new XSSFResolveStrategy());
}
public ResolveContext(FileMagic fileMagic) {
this.fileMagic = fileMagic;
}
public ResolveContext(FileMagic fileMagic, List<ResolveStrategy> strategyList) {
this.fileMagic = fileMagic;
RESOLVE_STRATEGY.addAll(strategyList);
}
/**
* sheet 内容拷贝
* @param srcSheet 源sheet
* @param targetSheet 目标sheet
* @return 拷贝结果
*/
public boolean copySheet(Sheet srcSheet, Sheet targetSheet) {
for (ResolveStrategy strategy : RESOLVE_STRATEGY) {
if (strategy.isSupport(this.fileMagic)) {
strategy.copySheet(srcSheet, targetSheet);
return true;
}
}
log.error("there is no copy policy corresponding to this file type. file type: {}", this.fileMagic.toString());
return false;
}
}
- 测试类
@Test
public void cloneSheetXSSF() {
ClassPathResource resource = new ClassPathResource("/template/template.xlsx");
Assert.isTrue(resource.exists());
try (InputStream inputStream = resource.getInputStream()) {
XSSFWorkbookFactory factory = new XSSFWorkbookFactory();
FileMagic fileMagic = FileMagic.valueOf(inputStream);
boolean isSupport = factory.accepts(fileMagic);
if (!isSupport) {
log.error("不支持该格式");
return;
}
XSSFWorkbook workbook = factory.create(inputStream);
int sheetNum = workbook.getNumberOfSheets();
for (int i = 0; i < sheetNum; i++) {
String sheetName = workbook.getSheetName(i);
XSSFSheet srcSheet = workbook.getSheetAt(i);
XSSFWorkbook target = new XSSFWorkbook();
XSSFSheet targetSheet = target.createSheet(sheetName);
ResolveContext resolveContext = new ResolveContext(fileMagic);
if (!resolveContext.copySheet(srcSheet, targetSheet)) {
log.error("sheetName: {} copy sheet failed.", sheetName);
continue;
}
FileOutputStream outputStream = new FileOutputStream(sheetName + ".xlsx");
target.write(outputStream);
target.close();
}
} catch (IOException e) {
throw new RuntimeException(e);
}
}
@Test
public void cloneSheetHSSF() {
ClassPathResource resource = new ClassPathResource("/template/template.xls");
Assert.isTrue(resource.exists());
try (InputStream inputStream = resource.getInputStream()) {
HSSFWorkbookFactory factory = new HSSFWorkbookFactory();
FileMagic fileMagic = FileMagic.valueOf(inputStream);
boolean isSupport = factory.accepts(fileMagic);
if (!isSupport) {
log.error("不支持该格式");
return;
}
HSSFWorkbook workbook = (HSSFWorkbook) factory.create(inputStream);
int sheetNum = workbook.getNumberOfSheets();
ResolveContext resolveContext = new ResolveContext(fileMagic);
for (int i = 0; i < sheetNum; i++) {
String sheetName = workbook.getSheetName(i);
HSSFSheet srcSheet = workbook.getSheetAt(i);
HSSFWorkbook target = new HSSFWorkbook();
HSSFSheet targetSheet = target.createSheet(sheetName);
resolveContext.copySheet(srcSheet, targetSheet);
FileOutputStream outputStream = new FileOutputStream(sheetName + ".xlsx");
target.write(outputStream);
target.close();
}
} catch (IOException e) {
throw new RuntimeException(e);
}
}
- 拆分后结果
-
XSSF测试结果【较好】
-
HSSF测试结果【差】
- 优缺点
- 优点:
- 免费
- XSSF效果较好
- 缺点:
- 处理复杂
- HSSF 效果不是很理想
- 目前POI官方的某些方法(拷贝图标、拷贝行)需谨慎使用
Spire.XLS for Java
-
POM.xml添加依赖
<!-- 免费版 --> <!--<dependency>--> <!-- <groupId>e-iceblue</groupId>--> <!-- <artifactId>spire.xls.free</artifactId>--> <!-- <version>5.1.0</version>--> <!--</dependency>--> <dependency> <groupId>e-iceblue</groupId> <artifactId>spire.xls</artifactId> <version>13.6.5</version> </dependency>
-
code
@Test public void splitSheetBySpire() throws IOException { ClassPathResource resource = new ClassPathResource("/template/template.xls"); Assert.isTrue(resource.exists()); Workbook workbook = new Workbook(); workbook.loadFromStream(resource.getInputStream()); WorksheetsCollection worksheets = workbook.getWorksheets(); int count = worksheets.getCount(); for (int i = 0; i < count; i++) { Worksheet srcSheet = worksheets.get(i); Workbook outputWork = new Workbook(); outputWork.getWorksheets().clear(); outputWork.getWorksheets().addCopy(srcSheet); outputWork.saveToFile(srcSheet.getName() + ".xlsx"); } }
-
拆分后结果【好】
-
优缺点
- 优点:
- 超简单,结果很OK
- 专业,操作很方便
- 缺点:
- 收费,只能使用免费的
- 免费的Jar,拆分xls文件无问题
总结
本文对使用POI和Spire.xls for Java两种方式进行多Sheet页进行拆分研究,
结果显而易见,根据实际需要合理性选择使用。