zouzhiy-excel是一款Excel导入导出的轻量级工具。省略了繁琐的配置，几行代码快速实现Excel导入导出。
仓库地址： https://github.com/zouzhiy/zouzhiy-excel
国内镜像地址： https://gitee.com/zouzhiy/zouzhiy-excel
- 默认大于配置，不需要显式的标注注解。反向解析，不需要的字段或者需要自定义配置的字段才需要注解标注
- 支持模板导出。可以预先设置好标题，表头，数据行格式。写入的数据自动继承模板的格式
- 支持一对多导入导出。一个数据对象占据不固定的多行多列。
- 支持拆分写入不同列。如：用户信息作为一个对象，可通过自定义CellHandler,实现多列写入，一列显示姓名，一列显示通信方式等。
- 支持自定义单元格格式，基本囊括了poi提供的style属性
- 支持自定义标题，表头，表尾写入
- 提供回调函数，可在导入导出过程中提供一些回调操作，比如:修改根据配置生成的样式、修改数据等
<dependency>
<groupId>io.github.zouzhiy</groupId>
<artifactId>zouzhiy-excel</artifactId>
<version>1.1.1</version>
</dependency><dependency>
<groupId>io.github.zouzhiy</groupId>
<artifactId>zouzhiy-excel-boot-starter</artifactId>
<version>1.1.1</version>
</dependency>@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class DemoVo {
private String name;
private String title;
public static List<DemoVo> getList() {
Random random = new Random(System.currentTimeMillis());
int size = random.nextInt(5000);
List<DemoVo> demoVoList = new ArrayList<>(size);
for (int i = 0; i < size; i++) {
DemoVo demoVo = DemoVo.builder()
.name("name-" + random.nextInt(900))
.title("title-" + random.nextInt(111))
.build();
demoVoList.add(demoVo);
}
return demoVoList;
}
}
public class ExcelDemo {
private final ZouzhiyExcelFactory zouzhiyExcelFactory = new DefaultZouzhiyExcelFactory(new Configuration());
@Test
void exportNoTemplate() {
//noinspection ConstantConditions
String rootPath = this.getClass().getResource("/").getPath();
// 无标题，无表头
zouzhiyExcelFactory.write(new File(rootPath + File.separator + System.currentTimeMillis() + ".xlsx"))
.sheet()
.dataRowStartIndex(0)
.write(DemoVo.getList(), DemoVo.class);
// 无标题，有表头
zouzhiyExcelFactory.write(new File(rootPath + File.separator + System.currentTimeMillis() + ".xlsx"))
.sheet()
.dataRowStartIndex(1)
.write(DemoVo.getList(), DemoVo.class);
// 有标题，无表头
zouzhiyExcelFactory.write(new File(rootPath + File.separator + System.currentTimeMillis() + ".xlsx"))
.sheet()
.title("有标题无表头")
.titleRowStartIndex(0)
.headRowStartIndex(-1)
.dataRowStartIndex(1)
.write(DemoVo.getList(), DemoVo.class);
zouzhiyExcelFactory.write(new File(rootPath + File.separator + System.currentTimeMillis() + ".xlsx"))
.sheet()
.title("有标题有表头")
.dataRowStartIndex(2)
.write(DemoVo.getList(), DemoVo.class);
}
@Test
void exportWithTemplate() {
String exportTemplateFilePath = "template/export.xlsx";
//noinspection ConstantConditions
String rootPath = this.getClass().getResource("/").getPath();
// 不覆盖标题，不覆盖标题表头
zouzhiyExcelFactory.write(new File(rootPath + File.separator + System.currentTimeMillis() + ".xlsx"))
.template(this.getClass().getClassLoader().getResourceAsStream(exportTemplateFilePath))
.sheet()
.titleRowStartIndex(-1)
.headRowStartIndex(-1)
.dataRowStartIndex(2)
.write(DemoVo.getList(), DemoVo.class);
// 覆盖标题，不覆盖标题表头
zouzhiyExcelFactory.write(new File(rootPath + File.separator + System.currentTimeMillis() + ".xlsx"))
.template(this.getClass().getClassLoader().getResourceAsStream(exportTemplateFilePath))
.sheet()
.title("覆盖标题")
.titleRowStartIndex(0)
.headRowStartIndex(-1)
.dataRowStartIndex(2)
.write(DemoVo.getList(), DemoVo.class);
// 不覆盖标题，覆盖表头
zouzhiyExcelFactory.write(new File(rootPath + File.separator + System.currentTimeMillis() + ".xlsx"))
.template(this.getClass().getClassLoader().getResourceAsStream(exportTemplateFilePath))
.sheet()
.titleRowStartIndex(-1)
.headRowStartIndex(1)
.dataRowStartIndex(2)
.write(DemoVo.getList(), DemoVo.class);
// 覆盖标题，覆盖标题表头
zouzhiyExcelFactory.write(new File(rootPath + File.separator + System.currentTimeMillis() + ".xlsx"))
.template(this.getClass().getClassLoader().getResourceAsStream(exportTemplateFilePath))
.sheet()
.title("覆盖标题，覆盖表头")
.dataRowStartIndex(2)
.write(DemoVo.getList(), DemoVo.class);
}
@Test
void importExcel() {
String inputFilePath = "template/import.xlsx";
InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream(inputFilePath);
List<DemoVo> demoVoList = zouzhiyExcelFactory.read(inputStream).sheet(0).dataRowStartIndex(2).read(DemoVo.class);
for (DemoVo demoVo : demoVoList) {
System.out.println(demoVo);
}
}
}@RestController
@RequestMapping("test")
public class TestController {
@Resource
private ZouzhiyExcelFactory zouzhiyExcelFactory;
@PostMapping("import")
public List<DemoVo> importFile(MultipartFile multipartFile) {
return zouzhiyExcelFactory
.read(multipartFile.getInputStream())
.sheet()
.dataRowStartIndex(2)
.read(DemoVo.class);
}
@GetMapping("export/no-template1")
public void exportDataByNoTemplate(HttpServletResponse response) {
List<DemoVo> demoVoList = Collections.emptyList();
response.addHeader("Content-Disposition", "attachment; filename*=utf-8''" + "export.xlsx");
zouzhiyExcelFactory
.write(response.getOutputStream())
.sheet()
.title("export")
.titleRowStartIndex(0)
.dataRowStartIndex(2)
.write(demoVoList, DemoVo.class);
}
@GetMapping("export/no-template2")
public void exportDataByTemplate4(HttpServletResponse response) {
List<DemoVo> demoVoList = this.getList();
response.addHeader("Content-Disposition", "attachment; filename*=utf-8''" + "export.xlsx");
zouzhiyExcelFactory
.write(response.getOutputStream())
.sheet()
.title("export")
.titleRowStartIndex(0)
.dataRowStartIndex(2)
.write(demoVoList, DemoVo.class);
}
@GetMapping("export/template1")
public void exportDataByTemplate1(HttpServletResponse response) {
List<DemoVo> demoVoList = this.getList();
String exportTemplateFilePath = "template/export/export1.xlsx";
InputStream exportTemplateInputStream = this.getClass().getClassLoader().getResourceAsStream(exportTemplateFilePath);
response.addHeader("Content-Disposition", "attachment; filename*=utf-8''" + "export.xlsx");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
zouzhiyExcelFactory
.write(response.getOutputStream())
.template(exportTemplateInputStream)
.sheet()
.titleRowStartIndex(-1)
.headRowStartIndex(-1)
.dataRowStartIndex(2)
.write(demoVoList, DemoVo.class);
}
@GetMapping("export/template2")
public void exportDataByTemplate2(HttpServletResponse response) {
List<DemoVo> demoVoList = this.getList();
String exportTemplateFilePath = "template/export/export2.xls";
InputStream exportTemplateInputStream = this.getClass().getClassLoader().getResourceAsStream(exportTemplateFilePath);
response.addHeader("Content-Disposition", "attachment; filename*=utf-8''" + "import.xls");
response.setContentType("application/vnd.ms-excel");
zouzhiyExcelFactory
.write(response.getOutputStream())
.template(exportTemplateInputStream)
.sheet()
.titleRowStartIndex(-1)
.headRowStartIndex(-1)
.dataRowStartIndex(2)
.write(demoVoList, DemoVo.class);
}
@GetMapping("export/template3")
public void exportDataByTemplate3(HttpServletResponse response) {
List<DemoVo> demoVoList = Collections.emptyList();
String exportTemplateFilePath = "template/export/export2.xls";
InputStream exportTemplateInputStream = this.getClass().getClassLoader().getResourceAsStream(exportTemplateFilePath);
response.addHeader("Content-Disposition", "attachment; filename*=utf-8''" + "import.xls");
response.setContentType("application/vnd.ms-excel");
zouzhiyExcelFactory
.write(response.getOutputStream())
.template(exportTemplateInputStream)
.sheet()
.titleRowStartIndex(-1)
.headRowStartIndex(-1)
.dataRowStartIndex(2)
.write(demoVoList, DemoVo.class);
}
private final Random random = new Random(System.currentTimeMillis());
private List<DemoVo> getList() {
int size = random.nextInt(5000);
List<DemoVo> demoVoList = new ArrayList<>(size);
for (int i = 0; i < size; i++) {
DemoVo demoVo = DemoVo.builder()
.name("name-" + random.nextInt(900))
.title("title-" + random.nextInt(111))
.build();
demoVoList.add(demoVo);
}
return demoVoList;
}
}@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class ItemVo {
private final static Random RANDOM = new Random(System.currentTimeMillis());
private String firstName;
private String lastName;
private Integer age;
public static ItemVo newInstance() {
return ItemVo
.builder()
.firstName(RANDOM.nextBoolean() ? null : "firstName-" + RANDOM.nextDouble())
.lastName(RANDOM.nextBoolean() ? null : "lastName-" + RANDOM.nextDouble())
.age(RANDOM.nextBoolean() ? null : RANDOM.nextInt(100))
.build();
}
}
public class ItemCellHandler extends AbstractCellHandler<ItemVo> {
@Override
protected ItemVo getCellValue(SheetContext sheetContext, ExcelFieldConfig excelFieldConfig, CellResult firstCellResult) {
String value = firstCellResult.getStringValue();
String[] values = value.split(",", -1);
return ItemVo.builder().firstName(values[0])
.lastName(values[1])
.age((values[2] == null || "null".equals(values[2]) || values[2].length() == 0) ? null : new BigDecimal(values[2]).intValue())
.build();
}
@Override
protected void setCellValue(RowContext rowContext, ExcelFieldConfig excelFieldConfig, Cell cell, ItemVo value) {
cell.setCellValue(String.format("%s,%s,%s", value.getFirstName(), value.getLastName(), value.getAge()));
}
@Override
public ExcelType getExcelType() {
return ExcelType.STRING;
}
@Override
protected ItemVo getBlankValue() {
return ItemVo.builder().build();
}
}@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class DemoVo {
private final static Random random = new Random(System.currentTimeMillis());
private String name;
private String title;
@ExcelField(cellHandler = ListStringStringSplitHandler.class)
private List<String> valueList;
}
public class ExcelDemo {
private final ZouzhiyExcelFactory zouzhiyExcelFactory = new DefaultZouzhiyExcelFactory(new Configuration());
@Test
void exportNoTemplate() {
//noinspection ConstantConditions
String rootPath = this.getClass().getResource("/").getPath();
// 无标题，无表头
zouzhiyExcelFactory.write(new File(rootPath + File.separator + System.currentTimeMillis() + ".xlsx"))
.sheet()
.dataRowStartIndex(0)
.write(DemoVo.getList(), DemoVo.class);
// 无标题，有表头
zouzhiyExcelFactory.write(new File(rootPath + File.separator + System.currentTimeMillis() + ".xlsx"))
.sheet()
.dataRowStartIndex(1)
.write(DemoVo.getList(), DemoVo.class);
// 有标题，无表头
zouzhiyExcelFactory.write(new File(rootPath + File.separator + System.currentTimeMillis() + ".xlsx"))
.sheet()
.title("有标题无表头")
.titleRowStartIndex(0)
.headRowStartIndex(-1)
.dataRowStartIndex(1)
.write(DemoVo.getList(), DemoVo.class);
zouzhiyExcelFactory.write(new File(rootPath + File.separator + System.currentTimeMillis() + ".xlsx"))
.sheet()
.title("有标题有表头")
.dataRowStartIndex(2)
.write(DemoVo.getList(), DemoVo.class);
}
@Test
void exportWithTemplate() {
String exportTemplateFilePath = "template/export.xlsx";
//noinspection ConstantConditions
String rootPath = this.getClass().getResource("/").getPath();
// 不覆盖标题，不覆盖标题表头
zouzhiyExcelFactory.write(new File(rootPath + File.separator + System.currentTimeMillis() + ".xlsx"))
.template(this.getClass().getClassLoader().getResourceAsStream(exportTemplateFilePath))
.sheet()
.titleRowStartIndex(-1)
.headRowStartIndex(-1)
.dataRowStartIndex(2)
.write(DemoVo.getList(), DemoVo.class);
// 覆盖标题，不覆盖标题表头
zouzhiyExcelFactory.write(new File(rootPath + File.separator + System.currentTimeMillis() + ".xlsx"))
.template(this.getClass().getClassLoader().getResourceAsStream(exportTemplateFilePath))
.sheet()
.title("覆盖标题")
.titleRowStartIndex(0)
.headRowStartIndex(-1)
.dataRowStartIndex(2)
.write(DemoVo.getList(), DemoVo.class);
// 不覆盖标题，覆盖表头
zouzhiyExcelFactory.write(new File(rootPath + File.separator + System.currentTimeMillis() + ".xlsx"))
.template(this.getClass().getClassLoader().getResourceAsStream(exportTemplateFilePath))
.sheet()
.titleRowStartIndex(-1)
.headRowStartIndex(1)
.dataRowStartIndex(2)
.write(DemoVo.getList(), DemoVo.class);
// 覆盖标题，覆盖标题表头
zouzhiyExcelFactory.write(new File(rootPath + File.separator + System.currentTimeMillis() + ".xlsx"))
.template(this.getClass().getClassLoader().getResourceAsStream(exportTemplateFilePath))
.sheet()
.title("覆盖标题，覆盖表头")
.dataRowStartIndex(2)
.write(DemoVo.getList(), DemoVo.class);
}
@Test
void importExcel() {
String inputFilePath = "template/import.xlsx";
InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream(inputFilePath);
List<DemoVo> demoVoList = zouzhiyExcelFactory.read(inputStream).sheet(0).dataRowStartIndex(2).read(DemoVo.class);
for (DemoVo demoVo : demoVoList) {
System.out.println(demoVo);
}
}
}@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class ItemVo {
private final static Random RANDOM = new Random(System.currentTimeMillis());
private String firstName;
private String lastName;
private Integer age;
public static ItemVo newInstance() {
return ItemVo
.builder()
.firstName(RANDOM.nextBoolean() ? null : "firstName-" + RANDOM.nextDouble())
.lastName(RANDOM.nextBoolean() ? null : "lastName-" + RANDOM.nextDouble())
.age(RANDOM.nextBoolean() ? null : RANDOM.nextInt(100))
.build();
}
}
public class ItemCellHandler implements CellHandler<ItemVo> {
@Override
public Class<ItemVo> getJavaType() {
return ItemVo.class;
}
@Override
public ExcelType getExcelType() {
return ExcelType.STRING;
}
@Override
public ItemVo read(SheetContext sheetContext, ExcelFieldConfig excelFieldConfig, CellResultSet cellResultSet) {
if (cellResultSet == null) {
return null;
}
if (cellResultSet.isNone()) {
return null;
}
List<CellResult> cellResultList = cellResultSet.getCellResultListList().get(0);
CellResult cellResultFirstName = cellResultList.size() > 0 ? cellResultList.get(0) : CellResult.none();
CellResult cellResultSecondName = cellResultList.size() > 1 ? cellResultList.get(1) : CellResult.none();
CellResult cellResultAge = cellResultList.size() > 2 ? cellResultList.get(2) : CellResult.none();
CellHandler<String> stringStringHandler = sheetContext.getConfiguration().getCellHandlerRegistry().getCellHandler(StringStringHandler.class);
String firstName = stringStringHandler.read(sheetContext, excelFieldConfig, CellResultSet.firstCellResult(cellResultFirstName));
String secondName = stringStringHandler.read(sheetContext, excelFieldConfig, CellResultSet.firstCellResult(cellResultSecondName));
CellHandler<Integer> integerCellHandler = sheetContext.getConfiguration().getCellHandlerRegistry().getCellHandler(IntegerNumberHandler.class);
Integer age = integerCellHandler.read(sheetContext, excelFieldConfig, CellResultSet.firstCellResult(cellResultAge));
return ItemVo.builder().firstName(firstName).lastName(secondName).age(age).build();
}
@Override
public void write(RowContext rowContext, Integer columnIndex, ExcelFieldConfig excelFieldConfig, ItemVo value) {
if (value == null) {
return;
}
Row row = rowContext.getRowList().get(0);
Cell cellFirstName = row.createCell(columnIndex);
Cell cellLastName = row.createCell(columnIndex + 1);
Cell cellAge = row.createCell(columnIndex + 2);
if (value.getFirstName() != null) {
cellFirstName.setCellValue(value.getFirstName());
}
if (value.getLastName() != null) {
cellLastName.setCellValue(value.getLastName());
}
if (value.getAge() != null) {
cellAge.setCellValue(value.getAge());
}
SheetContext sheetContext = rowContext.getSheetContext();
CellStyle cellStyle = sheetContext.getDataCellStyle(excelFieldConfig, this.getDefaultExcelFormat());
cellFirstName.setCellStyle(cellStyle);
cellLastName.setCellStyle(cellStyle);
cellAge.setCellStyle(cellStyle);
int rowspan = rowContext.getRowspan();
int rowIndex = row.getRowNum();
RegionUtils.addMergedRegionIfPresent(sheetContext, cellStyle, rowIndex, rowIndex + rowspan - 1, columnIndex, columnIndex);
RegionUtils.addMergedRegionIfPresent(sheetContext, cellStyle, rowIndex, rowIndex + rowspan - 1, columnIndex + 1, columnIndex + 1);
RegionUtils.addMergedRegionIfPresent(sheetContext, cellStyle, rowIndex, rowIndex + rowspan - 1, columnIndex + 2, columnIndex + 2);
}
}
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@ExcelClass(titleStyle = @ExcelStyle(borderLeft = BorderStyle.THIN, rotation = 45, wrapText = true, font = @ExcelFont(fontHeightInPoints = 32, bold = true, italic = true)))
public class DemoVo {
@ExcelField(dataStyle = @ExcelStyle(font = @ExcelFont(bold = true, italic = true)), headStyle = @ExcelStyle(font = @ExcelFont(italic = true)))
private String name;
@ExcelField(dataStyle = @ExcelStyle(font = @ExcelFont(bold = true, italic = true, color = Font.COLOR_RED)), headStyle = @ExcelStyle(font = @ExcelFont(italic = true)))
private String title;
public static List<DemoVo> getList() {
Random random = new Random(System.currentTimeMillis());
int size = random.nextInt(5000);
List<DemoVo> demoVoList = new ArrayList<>(size);
for (int i = 0; i < size; i++) {
DemoVo demoVo = DemoVo.builder()
.name("name-" + random.nextInt(900))
.title("title-" + random.nextInt(111))
.build();
demoVoList.add(demoVo);
}
return demoVoList;
}
}- 补充注释
- 补齐文档
- 完善单sheet导入导出功能。如宽度设置，根据表头匹配等
- 增加多sheet支持
非常欢迎你的加入！提一个 Issue 或者提交一个 Pull Request。
QQ：546963897
email：[email protected]
Apache 2.0 © zouzhiy