How to use POI to import and export Excel tables in Java-javaTutorial-php.cn

1. Java uses POI to import and export Excel tables demo

1.Introduce dependencies

 org.apache.poi poi-ooxml 4.1.2

Copy after login

2.Import demo

2.1 controller layer

/** * Excel导入 */ @PostMapping("/import") public Result userImport2(@RequestParam("file") MultipartFile file) throws Exception{ Result result=userService.userImportExcel(file); return result; }

Copy after login

2.2 service implementation class layer

public Result userImportExcel(MultipartFile file){ try { InputStream inputStream = file.getInputStream(); XSSFWorkbook sheets = new XSSFWorkbook(inputStream); //获取表单sheet 第一个 XSSFSheet sheetAt = sheets.getSheetAt(0); //获取第一行 int firstRowNum = sheetAt.getFirstRowNum(); //最后一行 int lastRowNum = sheetAt.getLastRowNum(); //存入数据集合 List users=new ArrayList<>(); //遍历数据 for(int i=firstRowNum+1;i
        
         Copy after login

3. Export demo

3.1 controller layer

/** * 导出 * @param response * @return * @throws Exception */ @GetMapping("/export") public Result userExport2(HttpServletResponse response) throws Exception{ Result result=userService.userExportExcel(response); return result; }

Copy after login

3.2 service implementation class

public Result userExportExcel(HttpServletResponse response) { try { //创建excel XSSFWorkbook sheets = new XSSFWorkbook(); //创建行 XSSFSheet sheet = sheets.createSheet("用户信息"); //格式设置 XSSFCellStyle cellStyle = sheets.createCellStyle(); //横向居中 cellStyle.setAlignment(HorizontalAlignment.CENTER); //创建单元格第一列 XSSFRow row = sheet.createRow(0); //表头 this.titleExcel(row,cellStyle); //查询全部的用户数据 mybatis-plus List list = list(); //遍历设置值 for(int i=0;i
        
         Copy after login

2. Hutool tool class encapsulation method export and import Excel

1.Introduce dependencies

Encapsulate poi into the tool class In the method

  cn.hutool hutool-all 5.7.20   org.apache.poi poi-ooxml 4.1.2

Copy after login

2. Import demo

/** * Excel导入 */ @PostMapping("/import") public Result userImport(@RequestParam("file") MultipartFile file) throws Exception{ System.out.println(file.toString()); //InputStream inputStream = multipartFile.getInputStream(); InputStream inputStream = file.getInputStream(); ExcelReader reader = ExcelUtil.getReader(inputStream); //读取表的内容 List> list = reader.read(1); List users = new ArrayList<>(); for(List