分享一个Excel工具类

package com.niucipol.framework.component.poi;

 

import cn.hutool.core.date.LocalDateTimeUtil;

import com.alibaba.fastjson.JSON;

import com.alibaba.fastjson.JSONArray;

import com.alibaba.fastjson.JSONObject;

import com.niucipol.constant.CommonConstant;

import com.niucipol.util.FileUtils;

import org.apache.commons.lang3.StringUtils;

import org.apache.commons.lang3.time.DateFormatUtils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.*;

import org.apache.poi.xssf.streaming.SXSSFRow;

import org.apache.poi.xssf.streaming.SXSSFSheet;

import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.springframework.util.CollectionUtils;

import org.springframework.web.multipart.MultipartFile;

 

import javax.servlet.http.HttpServletResponse;

import java.io.IOException;

import java.text.DateFormat;

import java.text.DecimalFormat;

import java.time.LocalDate;

import java.time.LocalDateTime;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

import java.util.Map;

 

/**

 * excel读取工具类

 */

public final class ExcelUtil {

    private ExcelUtil() {

        throw new IllegalStateException();

    }

 

    /**

     * 2003版本excel

     */

    protected static final String XLS = "xls";

    /**

     * 2007版本excel

     */

    protected static final String XLSX = "xlsx";

    /**

     * 欧拉

     */

    protected static final String EULER = "E";

    /**

     * 单个sheet允许存放的数据行数

     */

    private static final int MAX_ROW = 1048576;

    /**

     * 单个sheet允许存放的数据列数

     */

    private static final int MAX_COLUMN = 16384;

    /**

     * 默认列宽

     */

    private static final int MIN_COLUMN_WIDTH = 17;

    /**

     * 默认列宽

     */

    private static final int MAX_COLUMN_WIDTH = 58;

 

    /**

     * 读取excel

     *

     * @param file        文件

     * @param headMap     字段 map.put("编号"，"id")

     * @param startRow    起始行 default 0

     * @param startColumn 起始列 default 0

     * @return 数据集合

     * @throws IOException 异常

     * @author wylin

     */

    public static JSONArray readExcel(final MultipartFile file, final Map<String, String> headMap, int startRow, int startColumn) throws IOException {

        JSONArray jsonArray = new JSONArray();

        try (Workbook workbook = getWorkbook(file)) {

            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

            List<String> head = new ArrayList<>();

            for (int sIdx = 0; sIdx < workbook.getNumberOfSheets(); sIdx++) {

                Sheet sheet = workbook.getSheetAt(sIdx);

                for (int rIdx = startRow; rIdx <= sheet.getLastRowNum(); rIdx++) {

                    // 一行记录

                    JSONObject jsonObject = new JSONObject();

                    Row row = sheet.getRow(rIdx);

                    for (int cIdx = startColumn; cIdx < row.getLastCellNum(); cIdx++) {

                        Cell cell = row.getCell(cIdx);

                        Object value = parseValue(cell, evaluator);

                        if (rIdx == 0) {

                            head.add(headMap.get(String.valueOf(value)));

                            continue;

                        }

                        jsonObject.put(head.get(cIdx - startColumn), value);

                    }

                    if (jsonObject.size() > 0) {

                        jsonArray.add(jsonObject);

                    }

                }

            }

        }

        return jsonArray;

    }

 

    /**

     * 导出excel

     *

     * @param excelName 文件名称

     * @param headMap   头部 map.put("id","编号")

     * @param jsonArray 数据

     * @param response  响应

     * @throws IOException 异常

     */

    public static void exportExcel(final String excelName, final Map<String, String> headMap, final JSONArray jsonArray, final HttpServletResponse response) throws IOException {

        if (headMap.size() > MAX_COLUMN) {

            throw new IllegalArgumentException("列宽超出excel最大宽度");

        }

        FileUtils.configResponse(response, excelName);

        // 只保留1000条数据在内存中

        SXSSFWorkbook workbookRef = null;

        try (SXSSFWorkbook workbook = new SXSSFWorkbook(1000)) {

            workbookRef = workbook;

            // （可选）是否压缩临时文件  workbook.setCompressTempFiles

            CellStyle headStyle = createHeadCellStyle(workbook);

            CellStyle cellStyle = createCellStyle(workbook);

            int[] colWidth = new int[headMap.size()];

            List<String> properties = new ArrayList<>(headMap.size());

            List<String> headers = new ArrayList<>(headMap.size());

            headMap.forEach((k, v) -> {

                properties.add(k);

                headers.add(v);

            });

            SXSSFSheet sheet = createSheet(workbook, headers, headStyle, colWidth);

            if (!CollectionUtils.isEmpty(jsonArray)) {

                // 创建完的工作簿已经包含了head

                int rowIndex = 1;

                for (Object obj : jsonArray) {

                    if (rowIndex > MAX_ROW) {

                        sheet = createSheet(workbook, headers, headStyle, colWidth);

                        rowIndex = 1;

                    }

                    JSONObject jsonObject = (JSONObject) JSON.toJSON(obj);

                    Row dataRow = sheet.createRow(rowIndex);

                    for (int i = 0; i < properties.size(); i++) {

                        Cell cell = dataRow.createCell(i);

                        Object o = jsonObject.get(properties.get(i));

                        String cellValue = handleValue(o);

                        cell.setCellValue(cellValue);

                        cell.setCellStyle(cellStyle);

                        // 如果不以表头宽度作为单元格宽度，则添加一下代码  handleColWidth(colWidth, i, cellValue.getBytes().length);

                        sheet.setColumnWidth(i, colWidth[i] * 256);

                    }

                    rowIndex++;

                }

            }

            workbook.write(response.getOutputStream());

        } finally {

            if (workbookRef != null) {

                workbookRef.dispose();

            }

        }

    }

 

    /**

     * 创建工作簿

     *

     * @param workbook  excel

     * @param headers   表头

     * @param headStyle 表头样式

     * @param colWidth  列宽

     * @return SXSSFSheet 工作簿

     */

    private static SXSSFSheet createSheet(SXSSFWorkbook workbook, List<String> headers, CellStyle headStyle, int[] colWidth) {

        // 创建工作簿

        SXSSFSheet sheet = workbook.createSheet();

        // 设置表头

        SXSSFRow headRow = sheet.createRow(0);

        for (int i = 0; i < headers.size(); i++) {

            Cell cell = headRow.createCell(i);

            cell.setCellValue(headers.get(i));

            cell.setCellStyle(headStyle);

            int bytes = headers.get(i).getBytes().length;

            handleColWidth(colWidth, i, bytes);

            sheet.setColumnWidth(i, colWidth[i] * 256);

        }

        return sheet;

    }

 

    /**

     * 设置标题样式

     *

     * @param workbook 表格

     * @return 样式

     * @author wylin

     */

    private static CellStyle createHeadCellStyle(final SXSSFWorkbook workbook) {

        CellStyle style = workbook.createCellStyle();

        setCellDefaultStyle(style);

        Font font = workbook.createFont();

        font.setFontHeightInPoints((short) 12);

        font.setBold(true);

        style.setFont(font);

        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);

        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        return style;

    }

 

    private static CellStyle createCellStyle(final SXSSFWorkbook workbook) {

        CellStyle style = workbook.createCellStyle();

        setCellDefaultStyle(style);

        Font font = workbook.createFont();

        font.setFontHeightInPoints((short) 12);

        style.setFont(font);

        return style;

    }

 

    /**

     * 设置样式

     *

     * @param style 样式

     */

    private static void setCellDefaultStyle(CellStyle style) {

        // 左右上下居中

        style.setAlignment(HorizontalAlignment.CENTER);

        style.setVerticalAlignment(VerticalAlignment.CENTER);

        // 边框

        style.setBorderTop(BorderStyle.THIN);

        style.setBorderLeft(BorderStyle.THIN);

        style.setBorderBottom(BorderStyle.THIN);

        style.setBorderRight(BorderStyle.THIN);

    }

 

    /**

     * 处理列宽

     *

     * @param colWidth 列宽数组

     */

    private static void handleColWidth(final int[] colWidth, final int index, final int width) {

        colWidth[index] = Math.max(colWidth[index], width);

        colWidth[index] = colWidth[index] > MAX_COLUMN_WIDTH ? MIN_COLUMN_WIDTH : colWidth[index];

        colWidth[index] = Math.max(colWidth[index], MIN_COLUMN_WIDTH);

    }

 

    /**

     * 处理字面值

     *

     * @param obj 字面值

     * @return 字符串

     */

    private static String handleValue(Object obj) {

        if (obj == null) {

            return "";

        }

        if (obj instanceof Float || obj instanceof Double) {

            return String.valueOf(obj);

        }

        if (obj instanceof LocalDate) {

            return LocalDateTimeUtil.format((LocalDate) obj, CommonConstant.DATE_TIME_FORMAT);

        }

        if (obj instanceof LocalDateTime) {

            return LocalDateTimeUtil.format((LocalDateTime) obj, CommonConstant.DATE_TIME_FORMAT);

        }

        if (obj instanceof Date) {

            return DateFormatUtils.format((Date) obj, CommonConstant.DATE_TIME_FORMAT);

        }

        return obj.toString();

    }

 

    private static Workbook getWorkbook(final MultipartFile file) throws IOException {

        String fileName = file.getOriginalFilename();

        if (StringUtils.isBlank(fileName)) {

            throw new NullPointerException("文件名称为空！");

        }

        if (fileName.endsWith(XLS)) {

            return new HSSFWorkbook(file.getInputStream());

        }

        if (fileName.endsWith(XLSX)) {

            return new XSSFWorkbook(file.getInputStream());

        }

        throw new IllegalArgumentException("文件格式不符合要求");

    }

 

    /**

     * 值处理

     *

     * @param evaluator FormulaEvaluator

     * @param cell      单元格

     * @return 单元格内容

     */

    private static Object parseValue(Cell cell, FormulaEvaluator evaluator) {

        switch (cell.getCellType()) {

            case _NONE:

            case STRING:

                return cell.getStringCellValue();

            case BLANK:

                return "";

            case ERROR:

                return cell.getErrorCellValue();

            case BOOLEAN:

                return cell.getBooleanCellValue();

            case FORMULA:

                String value = evaluator.evaluate(cell).formatAsString();

                if (value.contains(EULER)) {

                    return new DecimalFormat("#").format(value);

                }

                return value;

            // 数字(时间日期)

            case NUMERIC:

                return getValueOfNumericCell(cell);

            default:

                return cell.getRichStringCellValue();

        }

    }

 

    /**

     * 处理数字类型

     *

     * @param cell 单元格

     * @return 单元格内容

     */

    private static Object getValueOfNumericCell(Cell cell) {

        if (DateUtil.isCellDateFormatted(cell)) {

            return DateFormat.getDateTimeInstance()

                    .format(cell.getDateCellValue());

        }

        double value = cell.getNumericCellValue();

        if (String.valueOf(value).contains(EULER)) {

            return new DecimalFormat("#").format(value);

        } else {

            return value;

        }

    }

}