编译环境IDEA2021.3 + SpringBoot
配置依赖
pom.xml
<!--Poi依赖,操作Excel-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10-FINAL</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.10-FINAL</version>
</dependency>
<!--MultipartFile需要导入的依赖-->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.3</version>
</dependency>
Controller层
@RestController
@CrossOrigin//解决跨域
public class ExcelController {
@RequestMapping(value = "/processExcel",method = RequestMethod.POST)
//RequestParam填接收前端传的键,前端传的值为Excel文件
public R processExcel_c(@RequestParam(" ") MultipartFile excelFile) throws Exception{
if (excelFile.isEmpty()){
R r =new R();
r.setCode(500);
r.setMsg("Excel文件为空");
return r;
}
String fileName = excelFile.getOriginalFilename();//获取文件名
InputStream in = excelFile.getInputStream();//获取文件输入流
return ExcelService.processExcel_s(in,fileName);
}
}
Service层
public interface ExcelService{
R processExcel_s(InputStream excelFile, String fileName);
}
Service层->Impl文件
Impl接口实现
@Service
public class ExcelServiceImpl implements ExcelService {
/**
* 操作Excel
* @param excelFile
* @param fileName
* @return R
*/
public R processExcel_s(InputStream excelFile,String fileName){
R r =new R();
try{
Workbook workbook = this.getWorkbook(excelFile,fileName);
Sheet sheet = workbook.getSheetAt(0);
//getSheetAt(i)获取第i也的数据
sheet.getLastRowNum()//获取表有多少行
//行、列均为0序
//表格第一行为标题字段,行是0序,所以getLastRowNum返回的值即是真实数据的行数(没有空行的前提下)
//获取有数据的总行数(真实行数)
sheet.getPhysicalNumberOfRows();
//获取有数据的总列数(真实列数)
row.getPhysicalNumberOfCells();
//即假设最后一行为36行,但中间有2行空行,那么
//sheet.getPhysicalNumberOfRows()的返回值是36-2=34
sheet.getRow(0).getCell(0).toString()
/*
getCell(i):
public abstract org.apache.poi.ss.usermodel.Cell getCell(
int i
)
getCell返回类型为抽象类,记得使用toString转字符串
*/
}catch (Exception e){
e.printStackTrace();
}
return r;
}
/**
* 截掉字符串后两位
* @param string
* @return
*/
public String removeLast2Bit(String string){
return string.substring(0,string.length()-2);
}
/**
* 判断文件格式.xls/.xlsx
* @param in
* @param fileName
* @return
* @throws Exception
*/
//方法源于网络
public Workbook getWorkbook(InputStream in, String fileName) throws Exception {
Workbook workbook = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if(".xls".equals(fileType)){
workbook = new HSSFWorkbook(in);
}else if(".xlsx".equals(fileType)){
workbook = new XSSFWorkbook(in);
}else {
throw new Exception("请上传.xls/.xlsx格式文件!");
}
return workbook;
}
}
注:在Excel中,纯数字整数数据<=11位为数值型,>11位为字符串型;不大于11位的数值型转字符串后会有小数点后1位,例如“12345.0”,字符串再转int或long型之前应截掉字符串后两位。大于11位的字符串传入后端再toString后保持不变,即没有小数点后1位,例如“12345”.
总结
- 从Controller层传入的文件名主要是Impl实现类中getWorkbook方法判断文件后缀是否是xlsx/xls,以及是哪一种Excel并创建workbook实例
- 如果可以确定前端传入的是".xls"或者".xlsx"文件,则可以直接在实现方法中用HSSFWookbook或XSSFWorkbook创建实例