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

﹏ヽ暗。殇╰゛Y 2022-04-01 06:41 183阅读 0赞

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

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

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

*  上传组件

*  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;
        }

*  拓展（常用）

<table style="width:500px;">  Workbook  
 <thead> 
  <tr> 
   <th colspan="2">Excel表格sheet的顶层对象</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="width:197px;"><a href="http://poi.apache.org/apidocs/4.0/org/apache/poi/ss/usermodel/Workbook.html#getActiveSheetIndex--" rel="nofollow">getActiveSheetIndex</a>()</td> 
   <td style="width:302px;">获取当前工作表中活动的工作表</td> 
  </tr> 
  <tr> 
   <td style="width:197px;"><a href="http://poi.apache.org/apidocs/4.0/org/apache/poi/ss/usermodel/Workbook.html#getSheetAt-int-" rel="nofollow">getSheetAt</a>(int&nbsp;index)</td> 
   <td style="width:302px;">获取给定索引处的工作表对象。</td> 
  </tr> 
  <tr> 
   <td style="width:197px;"><a href="http://poi.apache.org/apidocs/4.0/org/apache/poi/ss/usermodel/Workbook.html#getSheetIndex-org.apache.poi.ss.usermodel.Sheet-" rel="nofollow">getSheetIndex</a>(<a href="http://poi.apache.org/apidocs/4.0/org/apache/poi/ss/usermodel/Sheet.html" rel="nofollow">Sheet</a>&nbsp;sheet)</td> 
   <td style="width:302px;">返回给定工作表的索引</td> 
  </tr> 
  <tr> 
   <td style="width:197px;"><a href="http://poi.apache.org/apidocs/4.0/org/apache/poi/ss/usermodel/Workbook.html#getSheetName-int-" rel="nofollow">getSheetName</a>(int&nbsp;sheet)</td> 
   <td style="width:302px;">获取工作表名称</td> 
  </tr> 
 </tbody> 
</table>

<table style="width:500px;">  Sheet  
 <thead> 
  <tr> 
   <th colspan="2">Workbook下的sheet工作表，包含单元格</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="width:250px;"><a href="http://poi.apache.org/apidocs/4.0/org/apache/poi/ss/usermodel/Sheet.html#getFirstRowNum--" rel="nofollow">getFirstRowNum</a>()</td> 
   <td style="width:249px;">获取工作表的第一行</td> 
  </tr> 
  <tr> 
   <td style="width:250px;"><a href="http://poi.apache.org/apidocs/4.0/org/apache/poi/ss/usermodel/Sheet.html#getLastRowNum--" rel="nofollow">getLastRowNum</a>()</td> 
   <td style="width:249px;">获取工作表有内容的最后一行</td> 
  </tr> 
  <tr> 
   <td style="width:250px;"><a href="http://poi.apache.org/apidocs/4.0/org/apache/poi/ss/usermodel/Sheet.html#getPhysicalNumberOfRows--" rel="nofollow">getPhysicalNumberOfRows</a>()</td> 
   <td style="width:249px;">返回已定义工作行的行数</td> 
  </tr> 
  <tr> 
   <td style="width:250px;"><a href="http://poi.apache.org/apidocs/4.0/org/apache/poi/ss/usermodel/Sheet.html#getRow-int-" rel="nofollow">getRow</a>(int&nbsp;rownum)</td> 
   <td style="width:249px;">返回索引起的逻辑行</td> 
  </tr> 
  <tr> 
   <td style="width:250px;"><a href="http://poi.apache.org/apidocs/4.0/org/apache/poi/ss/usermodel/Sheet.html#isColumnHidden-int-" rel="nofollow">isColumnHidden</a>(int&nbsp;columnIndex)</td> 
   <td style="width:249px;">判断索引处列是否为隐藏列</td> 
  </tr> 
 </tbody> 
</table>