如何使用Workbook实现简单的excel导入功能？

今天药忘吃喽~ 2024-03-16 15:18 51阅读 0赞

### Workbook导入excel ###

1.需要用到的依赖

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>5.2.3</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>5.2.3</version>
            </dependency>

1.  工具类

mport org.apache.poi.ss.usermodel.*;
    import org.springframework.web.multipart.MultipartFile;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    public class ExcelUtil {
        
    //XXX为excel列对应的实体类
        public static List<XXX> readExcel(MultipartFile file) throws IOException {
        
            List<XXX> list = new ArrayList<>();
            InputStream inputStream = file.getInputStream(); // 获取上传文件的输入流
            Workbook workbook = WorkbookFactory.create(inputStream); // 使用工厂方法创建 Workbook 对象
            Sheet sheet = workbook.getSheetAt(0); // 获取工作簿中的第一个表格
            DataFormatter dataFormatter = new DataFormatter();
            // 获取表格中的每一行
            // 获取当前行
            boolean isOk = true;
            for (Row currentRow : sheet) {
        
    //            跳过第一行
                if(isOk) {
        
                    isOk = false;
                    continue;
                }
                XXX sx = new XXX();
                //获取列赋值给对象，这里就举例，具体的按自己需求定
                sx.setCycle(dataFormatter.formatCellValue(currentRow.getCell(0)));
                sx.setCode(dataFormatter.formatCellValue(currentRow.getCell(1)));
     
                list.add(sx);
            return list;
        }
    }

3.**controller层**

@PostMapping("/uploadExcel")
        @ResponseBody
        public ResponseEntity<String> uploadExcel(@RequestParam("file") MultipartFile file) {
        
            try {
        
                LOGGER.info("接收到上传 excel: {}, 文件大小: {}", file.getOriginalFilename(), file.getSize());
                List<XXX> list = ExcelUtil.readExcel(file);
                LOGGER.info("读取到 excel 行数: {}", list.size());
                for (XXX sx: list) {
        
                    XXXService.save(sx);
                }
                return new ResponseEntity<>("excel 内容入库", 200);
            } catch (IOException e){
        
                e.printStackTrace();
                return new ResponseEntity<>("excel 内容读取失败", 2001);
            }
        }

**简单页面调用**

<!DOCTYPE html>
    <html lang="en">
    <head>
        <meta charset="UTF-8">
        <title>excel导入</title>
    </head>
    <body>
      <h1>引入 excel 数据源</h1>
      //action指向接口相对路径
      <form method="post" enctype="multipart/form-data" action="/uploadExcel">
        <input type="file" name="file"/>
        <br />
        <br />
        <input type="submit" value="上传"/>
      </form>
    </body>
    </html>