基于POI的导入导出工具类使用

  • 背景
  • 快速引入
  • 基本excel导入成对象
  • 合并excel导入成为对象
  • 导出复杂excel表格
  • 最后


背景

自己封装了一个excel工具类,方便以后使用和部分可能有相同需求的朋友使用,现在做一个简单的记录

快速引入

第一步导包,maven包仅包含poi依赖,导入时注意poi版本冲突
<dependency>
            <groupId>top.rdongyue</groupId>
            <artifactId>ExcelUtils</artifactId>
            <version>1.0.9.1</version>
        </dependency>
导出标准excel表格及自定义标题样式详见基于POI封装的操作Excel工具类使用二

基于POI封装的操作Excel工具类使用(二)

基本excel导入成对象

java 导出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());
    }
}

结果:

java 导出excel提示部分内容有问题_工具类_02

合并excel导入成为对象

需求:现有一个对象需要从两张或多张表中读取数据

test1表:

java 导出excel提示部分内容有问题_excel导入_03


test2表:

java 导出excel提示部分内容有问题_工具类_04

//对象
@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());
        }
}

结果:

java 导出excel提示部分内容有问题_excel导入_05

导出复杂excel表格

需求:现要导出一个表格

java 导出excel提示部分内容有问题_excel_06

//定义对象
@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();
        }
}

结果:

java 导出excel提示部分内容有问题_List_07