接收MultipartFile直接转化为Workbook
情况1:
直接这样转化
//不管2003 .xls还是2007 .xlsx 都是
//用 new XSSFWorkbook(file.getInputStream())将MultipartFile文件转为Workbook文件
Workbook workbook = new XSSFWorkbook(file.getInputStream());
情况2:(错误)
刚开始以为需要2003的.xls要对应的HSSFWorkbook去接转化,2007的.xlsx要对应XSSFWorkbook.
这样做会报什么版本问题,如下:
org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
情况3:(错误)
网上说用这个 ,不过也是报了上面的错误。
Workbook workbook = new HSSFWorkbook(new POIFSFileSystem(file.getInputStream())); //错误写法
应用场景:其实就是上传Excel文件不在服务端生成文件,直接在运行内存中将MultipartFile转化为Workbook,然后读取数据。
下面写个demo来测试:
控制层代码:
package cn.mindgd.controller.background;
import cn.mindgd.service.ExcelService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
/**
* @author JiaXinMa
* @description Excel测试控制层
* @date 2021/7/22
*/
@Slf4j
@RestController
@RequestMapping("/excel")
public class ExcelController {
@Autowired
private ExcelService excelService;
@PostMapping("/uploadExcel")
public void uploadExcel(@RequestParam("file") MultipartFile file) {
log.info("/excel/uploadExcel");
excelService.uploadExcel01(file);
// excelService.uploadExcel02(file);
}
}
业务层代码:
package cn.mindgd.service;
import cn.mindgd.util.ExcelUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.util.List;
/**
* @author JiaXinMa
* @description Excel测试业务层
* @date 2021/7/22
*/
@Service
public class ExcelService {
/**
* @author: JiaXinMa
* @description: 上传excel文件同时写入数据库
* @date: 2021/7/22
*/
public void uploadExcel01(MultipartFile file) {
try {
//1.获取文件并读取数据
//判断是否为excel
String originalFilename = file.getOriginalFilename();
boolean isExcel = ExcelUtils.validateExcel(originalFilename);
if (isExcel) {
/* 不管2003 .xls还是2007 .xlsx 都是
* 用 new XSSFWorkbook(file.getInputStream())将MultipartFile文件转为Workbook文件
*/
Workbook workbook = new XSSFWorkbook(file.getInputStream());//情况1
//情况3
// Workbook workbook = new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));
List<List<String>> uploadExcelData = getUploadExcelData(workbook);
System.out.println("读取的文件数据为:" + uploadExcelData);
//2.赋值对象并插入数据,每一行就是条数据
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @author: JiaXinMa
* @description: 上传excel文件同时写入数据库
* @date: 2021/7/22
*/
public void uploadExcel02(MultipartFile file) {
try {
//1.获取文件并读取数据
//判断是否为excel
String originalFilename = file.getOriginalFilename();
boolean isExcel = ExcelUtils.validateExcel(originalFilename);
boolean isExcel2003 = ExcelUtils.isExcel2003(originalFilename);
boolean isExcel2007 = ExcelUtils.isExcel2007(originalFilename);
if (isExcel) {
Workbook workbook = null;
/* 不管2003 .xls还是2007 .xlsx 都是
* 用 new XSSFWorkbook(file.getInputStream())将MultipartFile文件转为Workbook文件
*/
if (isExcel2003) {//情况2
workbook = new HSSFWorkbook(file.getInputStream());
} else if (isExcel2007) {
workbook = new XSSFWorkbook(file.getInputStream());
}
List<List<String>> uploadExcelData = getUploadExcelData(workbook);
System.out.println("读取的文件数据为:" + uploadExcelData);
//2.赋值对象并插入数据,每一行就是条数据
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @author: JiaXinMa
* @description: 获取上传的Excel文件的数据
* @date: 2021/7/22
*/
public List<List<String>> getUploadExcelData(Workbook workbook) throws Exception {
ExcelUtils excelUtils = new ExcelUtils(workbook);
List<List<String>> list = excelUtils.read();
return list;
}
}
demo中的工具类可以看我这篇博客:如何使用POI下载、导出Excel模板以及设置样式?
测试后效果:
情况2和3都是报错误:
情况1:
想看更多精彩内容,可以关注我的博客园
我的博客园