(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 "";
}
}
}