06，SpringMVC项目导入wps表格文件_wps spring 接入从头到尾-CSDN博客

本文链接：https://blog.csdn.net/bingliang514csdn/article/details/127708485

（1），jar包依赖

        <!--POI 相关jar start -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>${poi.version}</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>${poi.version}</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>${poi.version}</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-examples</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <!--POI 相关jar end -->

        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.3.2</version>
        </dependency>

（2），multipartResolver配置bean

	<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
		<property name="defaultEncoding" value="utf-8"></property>
		<!--上传文件上限单位字节，10M-->
		<property name="maxUploadSize" value="10485760"></property>
		<property name="maxInMemorySize" value="40960"></property>
	</bean>

（3），选择上传wps表格模板文件的jsp页面

注意此行不是代码：http://localhost:8085/bl_mave_wf_war_exploded/testExamp/studentListJsp.jsp 这是跳转本页的链接

<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form" %>
<%@ page language="java" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jstl/core_rt" prefix="c"%>

<%
    String path = request.getContextPath();
    String contextPath = request.getContextPath();
    String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
    <base href="<%=basePath%>">
    <title></title>
</head>
<body>
    <form action="<%=contextPath %>/studentConter/importExcelFile" method="post" enctype="multipart/form-data">
        请选择要导入的excel模板文件：<input type="file" value="请选择" name="upFileName" />
        <input type="submit" value="导入" />
    </form>
</body>
<script type="text/javascript">
</script>
</html>

（4），后端java代码解析表格文件内容

import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.commons.CommonsMultipartFile;
import java.io.InputStream;
/**
* @date: 2022/10/30 07:12
* @desc: 测试控制类
*/
@Controller
@RequestMapping(value = "/studentConter")
public class StudentController {
@RequestMapping(value = "/importExcelFile", method = RequestMethod.POST, produces = "text/html;charset=UTF-8" )
public String importExcelFile(@RequestParam(value = "upFileName", required = true) CommonsMultipartFile commonsMultipartFile ) {
    // 读取文件内容
    String upFileName = commonsMultipartFile.getOriginalFilename();
    System.out.println("导入的文件名称："+ upFileName);
    try {
        readExcelFile(upFileName, commonsMultipartFile.getInputStream());
    } catch (Exception e1) {
        e1.printStackTrace();
    }
    return "";
}

// 读取文件内容
private void readExcelFile(String fileName, InputStream excelInStream) throws Exception {
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(excelInStream);
    int sheetSize = xssfWorkbook.getNumberOfSheets();
    for (int sheetIdx = 0; sheetIdx < sheetSize; sheetIdx++) {
        XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(sheetIdx);
        if (xssfSheet == null || xssfSheet.getLastRowNum() == 0)
            continue;
        // 循环sheet选项中的行
        for(int rowIdx = 0; rowIdx < xssfSheet.getLastRowNum(); rowIdx++) {
            System.out.println("开始输出表格行中的数据：");
            forHssfRow(xssfSheet.getRow(rowIdx));
        }
    }
}
// 在表格行中循环列格
private void forHssfRow(XSSFRow xssfRow) {
    int minColIdx = xssfRow.getFirstCellNum();
    int maxColIdx = xssfRow.getLastCellNum();
    for (int colIdx = minColIdx; colIdx < maxColIdx; colIdx++) {
        XSSFCell xssfCell = xssfRow.getCell(colIdx);
        if(xssfCell == null)
            continue;
        System.out.println("单元内容："+ getStringVal(xssfCell));
    }
}
private String getStringVal(XSSFCell xssfCell) {
    switch (xssfCell.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN :
            return xssfCell.getBooleanCellValue() ?"true":"false";
        case Cell.CELL_TYPE_FORMULA :
            return xssfCell.getCellFormula();
        case Cell.CELL_TYPE_NUMERIC :
            xssfCell.setCellType(Cell.CELL_TYPE_STRING);
            return xssfCell.getStringCellValue();
        case Cell.CELL_TYPE_STRING :
            return xssfCell.getStringCellValue();
        default :
            return "";
    }
}

}