导入表格的工具类

package com.odianyun.manage.util;

import com.alibaba.fastjson.JSON;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.util.*;

public class ExcelUtils {

    /**
     * Excel 导入
     *
     * @param file 文件
     * @param keys 数据顺序
     */
    public static List<Map<String, Object>> importExcel(MultipartFile file, String[] keys) throws Exception {
        Workbook wb = null;
        String fileName = file.getOriginalFilename();
        if (fileName.endsWith("xls")) {
            POIFSFileSystem pois = new POIFSFileSystem(file.getInputStream());
            wb = new HSSFWorkbook(pois);
        } else if (fileName.endsWith("xlsx")) {
            wb = new XSSFWorkbook(file.getInputStream());
        } else {
            //不是excel文件
            throw new PackageException(PackageEnum.EXCEL_FILE_EXCEPTION);
        }

        //获取第一页数据
        Sheet sheet = wb.getSheetAt(0);
        //第一页行数
        int rowCount = sheet.getPhysicalNumberOfRows();
        //检测模板和传入header是否数量一致
        if (sheet.getRow(0).getPhysicalNumberOfCells() != keys.length) {
            throw new RuntimeException("导入的Excel和模板的列不匹配");
        }
        //用于返回的结果
        List<Map<String, Object>> result = new ArrayList<>();
        for (int i = 0; i < rowCount - 1; i++) {
            //数据行,跳过第一行表头
            Row row = sheet.getRow(i + 1);
            Map<String, Object> tmp = new HashMap<>();
            for (int j = 0; j < keys.length; j++) {
                Cell cell = row.getCell(j);
                if (cell != null) {
                    // 把类型转行String
                    cell.setCellType(CellType.STRING);
                    tmp.put(keys[j], cell.getStringCellValue());
                } else {
                    tmp.put(keys[j], "");
                }

            }
            result.add(tmp);
        }
        return result;
    }


    /**
     * 获得Excel文件的第一个Sheet
     * @param file
     * @return
     */
    public static Sheet getFirstSheet(MultipartFile file) throws Exception {
        try(Workbook workBook = WorkbookFactory.create(file.getInputStream())) {
            return workBook.getSheetAt(0);
        } catch (Exception e) {
            throw new Exception("上传文件异常",e); // 文件上传出错
        }
    }

    public static class ExcelHeaderMetaData {
        private List<String> dataList;

        private String placeholder;

        private Short color;

        public ExcelHeaderMetaData(List<String> dataList, String placeholder, short color) {
            this.dataList = dataList;
            this.placeholder = placeholder;
            this.color = color;
        }

        public List<String> getDataList() {
            return dataList;
        }

        public void setDataList(List<String> dataList) {
            this.dataList = dataList;
        }

        public String getPlaceholder() {
            return placeholder;
        }

        public void setPlaceholder(String placeholder) {
            this.placeholder = placeholder;
        }

        public Short getColor() {
            return color;
        }

        public void setColor(Short color) {
            this.color = color;
        }
    }

    /**
     * 转换模板为表格标题
     * @param template  模板
     * @return
     */
    public static List<String> transformTemplateToTitle(String template) {
        List<LinkedHashMap> maps = JSON.parseArray(template, LinkedHashMap.class);
        List<String> result = new ArrayList<>();
        for (Map map : maps) {
            for (Object o : map.entrySet()) {
                Map.Entry entry = (Map.Entry) o;
                result.add(String.valueOf(entry.getKey()));
            }
        }
        return result;
    }


    /**
     * 创建只带表头的模板
     * @param template                  模板
     * @param excelHeaderMetaDataList   表头特殊单元格对象
     * @return
     */
    public static Workbook createWorkBook(String template, List<ExcelHeaderMetaData> excelHeaderMetaDataList) {
        Workbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet(ImportCreateMpEnum.SHEET_NAME_MP.getCode());
        Row row = sheet.createRow(0);
        // 1 构建占位符 - metadata键值对
        Map<String, ExcelHeaderMetaData> placeholder2MetaData = new HashMap<>();
        if (CollectionUtils.isNotEmpty(excelHeaderMetaDataList)) {
            for (ExcelHeaderMetaData headerMetaData : excelHeaderMetaDataList) {
                if (StringUtils.isNotBlank(headerMetaData.getPlaceholder())) {
                    placeholder2MetaData.put(headerMetaData.getPlaceholder(), headerMetaData);
                }
            }
        }
        // 2 创建表头
        int cellIndex = 0;
        List<String> titles = ExcelUtils.transformTemplateToTitle(template);
        for (String title : titles) {
            ExcelHeaderMetaData metaData = placeholder2MetaData.get(title);
            if (metaData == null) {
                Cell cell = row.createCell(cellIndex);
                cell.setCellValue(title);
                CellStyle cellStyle = workbook.createCellStyle();
                Font font = workbook.createFont();
                font.setBold(true);
                cellStyle.setFont(font);
                cell.setCellStyle(cellStyle);
                if (title.indexOf("*") == 0) {// 首位为*号设置单元格字体为红色
                    font.setColor(Font.COLOR_RED);
                }
                cellIndex++;
            } else {
                if (CollectionUtils.isEmpty(metaData.getDataList())) {
                    continue;
                }
                for (String data : metaData.getDataList()) {
                    Cell cell = row.createCell(cellIndex);
                    cell.setCellValue(data);
                    CellStyle cellStyle = workbook.createCellStyle();
                    cellStyle.setFillForegroundColor(metaData.getColor() == null ? IndexedColors.WHITE.getIndex() : metaData.getColor());
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    cellStyle.setFillBackgroundColor(metaData.getColor() == null ? IndexedColors.WHITE.getIndex() : metaData.getColor());
                    Font font = workbook.createFont();
                    font.setBold(true);
                    cellStyle.setFont(font);
                    if (data.indexOf("*") == 0) {// 首位为*号设置单元格字体为红色
                        font.setColor(Font.COLOR_RED);
                    }
                    cell.setCellStyle(cellStyle);
                    cellIndex++;
                }
            }
        }
        return workbook;
    }
}