简单的Excel导入导出工具

小灰灰 2022-01-20 01:23 362阅读 0赞

支持多Sheet导出导入

1.创建模型

@Data
    @ExcelSheet(sheet = "判断题", title = "判断题")
    public class QuestionJudgeEo {
    
        @ExcelField("题目")
        private String stem;
    
        @ExcelField("答案")
        private String answer;
    
        @ExcelField("参考分值")
        private String score;
    
    }
    复制代码

2.导出

@Test
        public void export() {
    
            //  创建对象集合
            List<QuestionJudgeEo> judges = new ArrayList<>();
            for (int i = 0; i < 10; i++) {
                QuestionJudgeEo questionJudgeEo = new QuestionJudgeEo();
                questionJudgeEo.setStem("stem-" + i);
                if (i % 2 == 1) {
                    questionJudgeEo.setAnswer("正确");
                } else {
                    questionJudgeEo.setAnswer("错误");
                }
                questionJudgeEo.setScore(i + "");
                judges.add(questionJudgeEo);
            }
    
            //  创建对象集合
            ArrayList<QuestionChoiceEo> choiceEos = new ArrayList<>();
            for (int i = 0; i < 5; i++) {
                QuestionChoiceEo questionChoiceEo = new QuestionChoiceEo();
                questionChoiceEo.setStem("stem-" + i);
                questionChoiceEo.setOptionA("option-" + "A");
                questionChoiceEo.setOptionB("option-" + "B");
                questionChoiceEo.setOptionC("option-" + "C");
                questionChoiceEo.setOptionD("option-" + "D");
                questionChoiceEo.setAnswer("A");
                questionChoiceEo.setScore(i + "");
                choiceEos.add(questionChoiceEo);
            }
    
            //获取工作簿
            Workbook workbook = excelExportService.createWorkbook();
            //写入Sheet1  参数: 工作簿对象, 对象集合, 对象类型
            excelExportService.getWorkbook(workbook, judges, QuestionJudgeEo.class);
            //写入Sheet2  参数: 工作簿对象, 对象集合, 对象类型
            excelExportService.getWorkbook(workbook, choiceEos, QuestionChoiceEo.class);
    
            try {
                FileOutputStream fileOutputStream = new FileOutputStream("D:/123.xlsx");
                workbook.write(fileOutputStream);
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    复制代码

2.导入

@Test
        public void importt() {
            try {
    
                ExcelImportServiceImpl excelImportService = new ExcelImportServiceImpl();
                Workbook workbook = excelImportService.createWorkbook("D:/123.xlsx");   //指定文件
    
                List<QuestionChoiceEo> itemList = excelImportService.getItemList(workbook, QuestionChoiceEo.class);     //解析Sheet1, excel生成对象集合
    
                List<QuestionJudgeEo> itemList1 = excelImportService.getItemList(workbook, QuestionJudgeEo.class);      //解析Sheet2, excel生成对象集合
    
                System.out.println();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    
    复制代码

项目地址: [github.com/QinhuaZhao/…][github.com_QinhuaZhao], 测试类含有使用方法

[github.com_QinhuaZhao]: https://link.juejin.im?target=https%3A%2F%2Fgithub.com%2FQinhuaZhao%2Fexcel