基于POI的导入导出工具类使用
背景
自己封装了一个excel工具类，方便以后使用和部分可能有相同需求的朋友使用，现在做一个简单的记录
快速引入
第一步导包，maven包仅包含poi依赖，导入时注意poi版本冲突
<dependency>
<groupId>top.rdongyue</groupId>
<artifactId>ExcelUtils</artifactId>
<version>1.0.6</version>
</dependency>
里面封装了基本的excel表格导入转成对象，多个excel导入合并成一个对象，导出复杂的excel表格等等功能后续还会继续增加功能。
基本excel导入成对象
现在要将上表导入并转换为对象
//定义对象
@Data
//sheetNumber表示读第几个工作薄从0开始默认0
//startRow表示从第几行开始解析默认从1开始
@StandardExcel(sheetNumber = 1,startRow=1)
public class Test1 {
//excelColumn表示的该字段对应excel中的第几列
//formart对应的是excel里面date的格式
@StandardExcelAttr(excelColumn = 0)
private String string;
@StandardExcelAttr(excelColumn = 1 ,formart = "yyyy-MM-dd hh:mm:ss")
private Date string2;
@StandardExcelAttr(excelColumn = 2)
private Double string3;
@StandardExcelAttr(excelColumn = 3)
private Integer string4;
}
//测试类
public static void main(){
StandardExcelParse<Test1> standard = ExcelUtilFactory.getStandard();
List<Test1> test1s1 = standard.standardExcelToList(new File("你自己的的excel文档"), Test1.class);
for (Test1 test1 : test1s1) {
System.out.println( test1.toString());
}
}
结果：
合并excel导入成为对象
需求:现有一个对象需要从两张或多张表中读取数据
test1表：
test2表：
//对象
@StandardExcel(sheetNumber = 1)
public class Test1 {
@StandardExcelAttr(excelColumn = 0)
private String string;
@StandardExcelAttr(excelColumn = 1 )
private String string2;
@StandardExcelAttr(excelColumn = 2)
private String string3;
@StandardExcelAttr(excelColumn = 3)
private String string4;
@StandardExcelAttr(excelColumn = 4)
private String string5;
@StandardExcelAttr(excelColumn = 5)
private String string6;
@StandardExcelAttr(excelColumn = 6)
private String string7;
@StandardExcelAttr(excelColumn = 7)
private String string8;
}
//解析
public static void main(String[] agrs){
File file = new File("文件1");
File file1 = new File("文件2");
FileInputStream fileInputStream = new FileInputStream(file);
FileInputStream fileInputStream1 = new FileInputStream(file1);
List<InputStream> fileList = new ArrayList<>();
fileList.add(fileInputStream);
fileList.add(fileInputStream1);
List<List<String>> lists = new MergeExcelUtil().mergeExcel(fileList);
StandardExcelParse<Test1> standard = ExcelUtilFactory.getStandard();
List<Test1> testModels1 = standard.standardExcelToList(lists, Test1.class);
for (Test1 testModel : testModels1) {
System.out.println(testModel.toString());
}
}
结果：
导出复杂excel表格
需求：现要导出一个表格
//定义对象
@IrregularExcel
public class TestExportirregularModel {
@IrregularFieldAnno(row =0,cloumn = 0,colspan = 9)
private String title;
@IrregularFieldNameAnno(row = 2,cloumn = 0,title = "编号",rowspan = 1)
@IrregularFieldAnno(row = 2,cloumn = 1,colspan = 2,rowspan = 1)
private String sn;
@IrregularFieldNameAnno(row = 2,cloumn =4,title = "名称")
@IrregularFieldAnno(row = 2,cloumn = 5,colspan = 4)
private String name;
@IrregularFieldNameAnno(row = 3,cloumn =4,title = "地址")
@IrregularFieldAnno(row = 3,cloumn = 5,colspan = 4)
private String address;
@IrregularFieldAnno(row = 4,cloumn = 0,colspan = 9)
private String listInfo;
@IrregularItem(startRow=5)
private List<TestModel> testModelList;
}
public class TestModel {
@StandardExcelAttr(excelColumn = 0, title = "列表标题1")
String number1;
@StandardExcelAttr(excelColumn = 1, title = "列表标题2")
String number2;
@StandardExcelAttr(excelColumn = 2, title = "列表标题3")
String number3;
@StandardExcelAttr(excelColumn = 3, title = "列表标题4")
String number4;
@StandardExcelAttr(excelColumn = 4, title = "列表标题5")
String number5;
@StandardExcelAttr(excelColumn = 5, title = "列表标题6")
String number6;
@StandardExcelAttr(excelColumn = 6, title = "列表标题7")
String number7;
@StandardExcelAttr(excelColumn = 7, title = "列表标题8")
String number8;
@StandardExcelAttr(excelColumn = 8, title = "列表标题9")
String number9;
@StandardExcelAttr(excelColumn = 9, title = "列表标题10")
String number10;
}
public static void main(String[] args){
TestExportirregularModel testExportirregularModel = new TestExportirregularModel();
testExportirregularModel.setTitle("这是由model生成的标题");
testExportirregularModel.setSn("测试编号af70fad5-7d2c-484a-8747-ab5474cb1bc4");
testExportirregularModel.setName("这是由model生成的名称");
testExportirregularModel.setAddress("这是由model生成的地址");
testExportirregularModel.setListInfo("这是model生成的数据(下面是列表)");
List<TestModel> testModels = new ArrayList<>();
for (int i = 0; i < 25; i++) {
TestModel testModel = new TestModel();
testModel.setNumber1(i+"行");
testModel.setNumber2(i+"行");
testModel.setNumber3(i+"行");
testModel.setNumber4(i+"行");
testModel.setNumber5(i+"行");
testModel.setNumber6(i+"行");
testModel.setNumber7(i+"行");
testModel.setNumber8(i+"行");
testModel.setNumber9(i+"行");
testModel.setNumber10(i+"行");
testModels.add(testModel);
}
testExportirregularModel.setTestModelList(testModels);
try {
//传入的对象只要包含了注解就行
ExportIrregularExcelUtil.export(testExportirregularModel,new FileOutputStream("d://导出异形表.xlsx"));
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}
结果：
最后
"如果您发现任何错误或有任何建议，请在评论中告诉我。我会尽力改进工具。"
"感谢您使用我的工具！如果您有任何问题或建议，请随时与我联系。"
"我也会持续改进的我工具。如果您有任何想法，请随时与我分享。"