POI 操作Excel导入篇1 -- 基本架构

本篇收录针对于POI导入Excel的操作sheet的一些常用步骤。

从网页导入文件到后台处理获得sheet

基本架构搭建（SpringBoot+HTML+MySQL）

• 上传组件

 <div> 
   <div> 
    <label for="excel">文件：</label> 　　
    <input type="text" name="userdefinedFile" id="userdefinedFile" placeholder="未选择任何文件" /> 　　
    <button type="button">选择文件</button> 
   </div> 
   <input type="file" name="excel" id="excel" /> 
  </div>

• ajax发送表单数据

var formdata = new FormData($("#uploadForm")[0]);
if (fileType !== 0) {
    $.ajax({
        type: 'post',
        url: ajaxUrl,
        data: formdata,
        processData: false,
        contentType: false,
        dataType: 'json',
        success: function(res) {

},
        error: function(XHR) {

}
    });
}

• 后台maven依赖

        //省略部分SpringBoot依赖
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.3.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-logging</artifactId>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.4</version>
        </dependency>

• 获取表单数据

@PostMapping("/upload")
@ResponseBody
public String upload(@RequestParam(value = "excel",required = false) MultipartFile excel) throws Exception {
        String fileName = excel.getOriginalFilename();
        return excelService.insert(fileName, excel);
}

• 获取文件sheet

public static Sheet checkExcelVersion(String fileName, MultipartFile file) throws Exception {
        //版本验证
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new RuntimeException("上传文件格式不正确");
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;
        is = file.getInputStream();
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        //返回第一个sheet
        Sheet sheet = wb.getSheetAt(0);
        if (sheet != null) {
            notNull = true;
        }
        return sheet;
    }

• 拓展（常用）