接收MultipartFile直接转化为Workbook(Excel文件对象)及其应用场景

接收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())); //错误写法

下面写个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都是保证错误:

接收MultipartFile直接转化为Workbook(Excel文件对象)及其应用场景

 情况1:

接收MultipartFile直接转化为Workbook(Excel文件对象)及其应用场景

 

 

想看更多精彩内容,可以关注我的CSDN

我的CSDN

 

接收MultipartFile直接转化为Workbook(Excel文件对象)及其应用场景

上一篇:MYSQL拒绝访问:not allowed to connect解决方法


下一篇:MySQL 自定义函数CREATE FUNCTION实例