Excle竖版表格模板导入

最新推荐文章于 2024-07-22 16:44:01 发布

多放香菜少加葱

最新推荐文章于 2024-07-22 16:44:01 发布

阅读量206

点赞数

文章标签： apache java spring

原文链接：https://blog.csdn.net/weixin_42906244/article/details/105944860

版权

一、Maven依赖

 <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>

二、代码块

1、控制层

package controller;

import model.ImportFileModel;
import model.Result;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import service.ImportFileService;

@RestController
public class ImportFileController {

    @Autowired
    private ImportFileService importFileService;

    @RequestMapping("/importFile")
    @ResponseBody
    public Result<ImportFileModel> importFile(@RequestParam(value = "file", required = false) MultipartFile file) {
        Result<ImportFileModel> importFileModelResult = importFileService.importFile(file);
        return importFileModelResult;
    }
}

2、服务层

package service.impl;

import model.ImportFileModel;
import model.Result;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import service.ImportFileService;

import java.io.InputStream;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;



@Service
public class ImportFileServiceImpl implements ImportFileService {

    @Override
    public Result<ImportFileModel> importFile(MultipartFile file) {
        try {
            //获取上传文件的对象
            Map<String, String> map = readExcel(file.getInputStream());
            //    将获取的数据重新整合输出
            Iterator iterator = map.keySet().iterator();
            ImportFileModel model = new ImportFileModel();
            while (iterator.hasNext()) {
                String key = iterator.next().toString();
                String value = (String) map.get(key);
                if ("姓名".equals(key)) {
                    model.setName(value);
                }
                if ("年龄".equals(key)) {
                    model.setAge(value);
                }
                if ("出生日期".equals(key)) {
                    model.setBirth(value);
                }
                if ("总销量".equals(key)) {
                    model.setSalesVolume(Double.valueOf(value));
                }
            }
            return new Result(model);
        } catch (Exception e) {
            return new Result<>(null, "", e.getMessage(), false);
        }

    }

    /*
     * @Description //TODO  读取文件中的数据信息
     * @Param [inputStream]
     * @return java.util.Map<java.lang.String,java.lang.String>
     **/
    public Map<String, String> readExcel(InputStream inputStream) throws Exception {

        Workbook hssfWorkbook = new XSSFWorkbook(inputStream);
        Map<String, String> hashMap = new HashMap<>();

        FormulaEvaluator evaluator = hssfWorkbook.getCreationHelper().createFormulaEvaluator();
        // 循环工作表Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // 循环行Row
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                Row hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                    Cell key = hssfRow.getCell(0);
                    Cell cell = hssfRow.getCell(1);
                    //解析文件中各种类型的数据信息
                    String stringCellValue = getStringCellValue(cell, evaluator);
                    //key值为去空格的值
                    hashMap.put(key.toString().trim(), stringCellValue);
                }
            }

        }
        return hashMap;
    }


    /**
     * @param cell Excel单元格
     * @return String 单元格数据内容
     */
    private String getStringCellValue(Cell cell, FormulaEvaluator evaluator) {
        //若表格为空，则直接返回空值
        if (cell == null) {
            return "";
        }
        String strCell = "";
        switch (cell.getCellType()) {
            //    表格中的数据为字符串型
            case Cell.CELL_TYPE_STRING:
                strCell = cell.getStringCellValue();
                break;
            //    表格中的数据为数值型
            case Cell.CELL_TYPE_NUMERIC:
                strCell = String.valueOf(cell.getNumericCellValue());
                break;
            //    表格中的数据为布尔型
            case Cell.CELL_TYPE_BOOLEAN:
                strCell = String.valueOf(cell.getBooleanCellValue());
                break;
            //    表格中的数据为空值
            case Cell.CELL_TYPE_BLANK:
                strCell = "";
                break;
            //    表格中的数据信息为公式类
            case Cell.CELL_TYPE_FORMULA:
                double numberValue = evaluator.evaluate(cell).getNumberValue();
                strCell = String.valueOf(numberValue);
                break;
            default:
                strCell = "";
                break;
        }
        if (strCell.equals("") || strCell == null) {
            return "";
        }
        return strCell;
    }
}