Java动态导出excel列

宁漂打工仔
已于 2023-10-08 10:26:35 修改
阅读量3.3k
点赞数
分类专栏： # POI Java 文章标签： 1024程序员节
于 2022-10-24 22:27:02 首次发布
本文链接：https://blog.csdn.net/springhash/article/details/127503586
版权
Java 同时被 2 个专栏收录
69 篇文章 0 订阅
订阅专栏
POI
1 篇文章 0 订阅
订阅专栏
该代码实现了一个Excel助手类，能够读取Excel文件，并根据指定的产品属性信息动态追加列。主要功能包括：创建标题行、处理内容行、导出Excel、解析Excel并转换为JSON数组等。涉及到的处理包括单元格样式设置、数据类型判断、日期格式转换等。
摘要由CSDN通过智能技术生成
实现效果：在模板基础列上，动态追加列
在这里插入图片描述


import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.RandomUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.util.*;
import java.util.stream.Collectors;


/**
 * ClassName ExcelHelper
 *
 * @author Wang
 */
public class ExcelHelper {

    private static final int START_CELL_NUMBER = 9;

    private static final int PRODUCT_UID_CELL_INDEX = 0;

    /**
     * 添加自定义列
     *
     * @param response
     * @param fis
     * @param productAttributeInfos
     */
    public static void appendExcelColumn(HttpServletResponse response, InputStream fis, List<ProductAttributeInfo> productAttributeInfos) throws IOException {
        XSSFWorkbook workBook = createTitle(fis, productAttributeInfos);
        if (workBook == null) {
            return;
        }
        writeResponse(response, workBook);
    }

    private static void writeResponse(HttpServletResponse response, XSSFWorkbook workBook) throws IOException {
        response.reset();
        response.setHeader("Content-disposition", "attachment; filename=" + Constants.DEVICE_WRAP);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        OutputStream outputStream = response.getOutputStream();
        workBook.write(outputStream);
    }

    /**
     * 创建标题行
     *
     * @param fis
     * @param productAttributeInfos
     * @return
     * @throws IOException
     */
    private static XSSFWorkbook createTitle(InputStream fis, List<ProductAttributeInfo> productAttributeInfos) throws IOException {
        if (CollectionUtil.isEmpty(productAttributeInfos)) {
            return null;
        }
        XSSFWorkbook workBook;
        workBook = new XSSFWorkbook(fis);
        Sheet sheetAt = workBook.getSheetAt(0);
        Row firstRow = sheetAt.getRow(0);
        int cellNum = START_CELL_NUMBER;
        // 自定义样式
        CellStyle cellStyle = getXssfCellStyle(workBook);
        for (ProductAttributeInfo productAttributeInfo : productAttributeInfos) {
            // 上报字段忽略
            int isReportAttribute = productAttributeInfo.getIsReportAttribute();
            if (isReportAttribute == Constants.REPORT) {
                continue;
            }
            Cell cell = firstRow.createCell(cellNum);
            cell.setCellValue(productAttributeInfo.getColumnName());
            cell.setCellStyle(cellStyle);
            cellNum++;
        }
        return workBook;
    }

    private static XSSFCellStyle getXssfCellStyle(XSSFWorkbook workBook) {
        XSSFCellStyle cellStyle = workBook.createCellStyle();
        // 填充色
        IndexedColorMap colorMap = workBook.getStylesSource().getIndexedColors();
        cellStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(166, 166, 166, 255), colorMap));
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        // 水平垂直居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        Font font = workBook.createFont();
        font.setFontHeightInPoints((short) 11);
        font.setFontName("宋体");
        font.setColor(Font.SS_SUPER);

        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 取出产品uid
     *
     * @param file
     * @return
     */
    public static String parseProductUid(MultipartFile file) throws IOException {
        Workbook workBook = null;
        String stringCellValue = null;
        String originalFilename = file.getOriginalFilename();
        assert originalFilename != null;
        if (isExcel2003(originalFilename) || isExcel2007(originalFilename)) {
            workBook = WorkbookFactory.create(file.getInputStream());
        } else {
            throw new ApiException(ErrorCodeMessage.IMPORT_EXCEL_ERROR);
        }
        Sheet sheetAt = workBook.getSheetAt(0);
        Row secondRow = sheetAt.getRow(1);
        stringCellValue = secondRow.getCell(PRODUCT_UID_CELL_INDEX).getStringCellValue();
        return stringCellValue;
    }

    /**
     * 【1】将列索引与产品属性 做映射
     * 【2】解析excel，将私有属性列数据转成jsonArray
     *
     * @param file        file
     * @param productInfo productInfo
     * @return List<Device>
     */
    public static List<Device> parseExcel(MultipartFile file, ProductInfo productInfo) throws Exception {
        List<ProductAttributeInfo> productAttributeInfos = productInfo.getProductAttributeInfos();
        Map<String, List<ProductAttributeInfo>> columnMap = productAttributeInfos.stream().collect(Collectors.groupingBy(ProductAttributeInfo::getColumnName));
        Set<String> columnList = columnMap.keySet();
        Workbook workBook = null;
        String originalFilename = file.getOriginalFilename();
        List<Device> list = new ArrayList<>();
        assert originalFilename != null;
        if (isExcel2003(originalFilename) || isExcel2007(originalFilename)) {
            workBook = WorkbookFactory.create(file.getInputStream());
        } else {
            throw new ApiException(ErrorCodeMessage.IMPORT_EXCEL_ERROR);
        }
        Sheet sheetAt = workBook.getSheetAt(0);
        Map<Integer, String> mapping = new LinkedHashMap<>();
        for (int rowNum = 0; rowNum <= sheetAt.getLastRowNum(); rowNum++) {
            // 【1】将列索引与产品属性 做映射
            if (rowNum == 0) {
                mapping = handleFirstRowMapping(sheetAt.getRow(rowNum), columnList);
                continue;
            }
            // 【2】解析excel，将私有属性列数据转成jsonArray
            Device device = handleContentRow(sheetAt.getRow(rowNum), mapping, columnMap, productInfo.getName());
            if (StringUtil.isEmpty(device.getSecret())) {
                device.setSecret(RandomUtil.randomString(Constants.DEVICE_SECRET_LENGTH));
            }
            list.add(device);
        }
        return list;
    }

    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    /**
     * 解析excel，将私有属性列数据转成jsonArray
     *
     * @param row
     * @param mapping   {index - columnName}
     * @param columnMap
     * @param name
     * @return
     */
    private static Device handleContentRow(Row row, Map<Integer, String> mapping, Map<String, List<ProductAttributeInfo>> columnMap, String name) throws Exception {
        Device device = new Device();
        device.init();
        // 处理公共属性
        device.setProductUid(getCellValue(row.getCell(0)));
        device.setProductName(name);
        device.setName(getCellValue(row.getCell(2)));
        device.setDeviceCode(getCellValue(row.getCell(3)));
        device.setDevicePhoto(getCellValue(row.getCell(4)));
        device.setRemark(getCellValue(row.getCell(5)));
        device.setStatus(
                Objects.nonNull(getCellValue(row.getCell(6)))
                        ? Integer.parseInt(getCellValue(row.getCell(6)))
                        : null
        );
        String lastOnLineTime = getCellValue(row.getCell(7));
        device.setLastOnLineTime(
                StringUtil.isNotEmpty(lastOnLineTime)
                        ? SDFFactory.DATETIME_DASH.parse(lastOnLineTime)
                        : null
        );
        String lastOffLineTime = getCellValue(row.getCell(8));
        device.setLastOffLineTime(
                StringUtil.isNotEmpty(lastOffLineTime)
                        ? SDFFactory.DATETIME_DASH.parse(lastOffLineTime)
                        : null
        );

        // 处理私有属性
        JSONArray jsonArray = new JSONArray();
        for (int cellNum = START_CELL_NUMBER; cellNum < row.getLastCellNum(); cellNum++) {
            //获取每一列
            Cell cell = row.getCell(cellNum);
            String cellVal = getCellValue(cell);
            if (Objects.nonNull(mapping.get(cellNum))) {
                JSONObject jsonObject = new JSONObject();
                String columnName = mapping.get(cellNum);
                jsonObject.put("columnName", columnName);
                jsonObject.put("columnValue", cellVal);
                ProductAttributeInfo productAttributeInfo = columnMap.get(columnName).get(0);
                jsonObject.put("displayName", productAttributeInfo.getDisplayName());
                jsonArray.add(jsonObject);
            }
        }
        device.setPrivateAttribute(jsonArray);
        return device;
    }

    private static String getCellValue(Cell cell) {
        String cellValue = "";
        if (null != cell) {
            //以下是判断数据类型
            switch (cell.getCellType()) {
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        cellValue = SDFFactory.DATETIME_DASH.format(date);
                    } else {
                        DecimalFormat df = new DecimalFormat("####.######");
                        cellValue = df.format(cell.getNumericCellValue());
                    }
                    break;
                case STRING:
                    cellValue = cell.getStringCellValue();
                    break;
                case BOOLEAN:
                    cellValue = cell.getBooleanCellValue() + "";
                    break;
                case BLANK:
                    cellValue = "";
                    break;
                case ERROR:
                    cellValue = null;
                    break;
                default:
                    break;
            }
        }
        return cellValue;
    }

    /**
     * 将列索引 与 列名 做映射
     *
     * @param row
     * @param columnList
     * @return
     */
    private static Map<Integer, String> handleFirstRowMapping(Row row, Set<String> columnList) {
        Map<Integer, String> mapping = new LinkedHashMap<>();
        for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
            //获取每一列
            Cell cell = row.getCell(cellNum);
            if (Objects.nonNull(cell) && columnList.contains(cell.getStringCellValue())) {
                mapping.put(cellNum, cell.getStringCellValue());
            }
        }
        return mapping;
    }

    /**
     * 导出excel
     *  TODO  产品列 与 导出数据的私有属性，需要保持一致
     *
     * @param response              响应流
     * @param inputStream           导出文件模板
     * @param productAttributeInfos 产品列
     * @param devices               导出数据
     */
    public static void exportExcel(HttpServletResponse response, InputStream inputStream, List<ProductAttributeInfo> productAttributeInfos, List<Device> devices) throws IOException {
        XSSFWorkbook workBook = createTitle(inputStream, productAttributeInfos);
        if (workBook == null) {
            return;
        }
        createContent(workBook, devices);
        writeResponse(response, workBook);
    }

    /**
     * 创建导出数据内容体
     *
     * @param workBook
     * @param devices
     */
    private static void createContent(XSSFWorkbook workBook, List<Device> devices) {
        Sheet sheetAt = workBook.getSheetAt(0);
        RedisService redisService = SpringUtils.getBean(RedisService.class);
        for (int i = 0; i < devices.size(); i++) {
            Row row = sheetAt.createRow(i + 1);
            Device device = devices.get(i);
            // 创建公共列
            createCommonCell(row, device);
            // 创建私有属性列
            JSONArray privateAttribute = device.getPrivateAttribute();
            if (CollectionUtil.isEmpty(privateAttribute)) {
                continue;
            }
            int cellNum = START_CELL_NUMBER;
            for (Object o : privateAttribute) {
                if (o instanceof LinkedHashMap) {
                    LinkedHashMap jsonObject = (LinkedHashMap) o;
                    Cell cell = row.createCell(cellNum);
                    if (CollectionUtil.isNotEmpty(jsonObject) && jsonObject.containsKey(AttributeCons.COLUMN_VALUE)) {
                        Object isReport = jsonObject.get(AttributeCons.IS_REPORT_ATTRIBUTE);
                        if (Constants.REPORT.equals(isReport)) {
                            continue;
                        }
                        Object columnValue = jsonObject.get(AttributeCons.COLUMN_VALUE);
                        if (Objects.nonNull(columnValue)) {
                            cell.setCellValue(columnValue.toString());
                        }
                    }
                    cellNum++;
                }
            }
        }
    }

    private static void mergeCacheToPrivateAttribute(JSONObject deviceStatus, LinkedHashMap jsonObject) {
        if (Objects.isNull(deviceStatus)) {
            return;
        }
        if (deviceStatus.containsKey(jsonObject.get(AttributeCons.COLUMN_NAME))) {
            jsonObject.put(AttributeCons.COLUMN_VALUE, deviceStatus.get(jsonObject.get(AttributeCons.COLUMN_NAME)));
        }
    }


    /**
     * 创建公共列
     *
     * @param row
     * @param device
     */
    private static void createCommonCell(Row row, Device device) {
        row.createCell(0).setCellValue(device.getProductUid());
        row.createCell(1).setCellValue(device.getProductName());
        row.createCell(2).setCellValue(device.getName());
        row.createCell(3).setCellValue(device.getDeviceCode());
        row.createCell(4).setCellValue(device.getDevicePhoto());
        row.createCell(5).setCellValue(device.getRemark());
        row.createCell(6).setCellValue(device.getStatus());
        row.createCell(7).setCellValue(
                Objects.nonNull(device.getLastOnLineTime())
                        ? SDFFactory.DATETIME_DASH.format(device.getLastOnLineTime())
                        : null
        );
        row.createCell(8).setCellValue(
                Objects.nonNull(device.getLastOnLineTime())
                        ? SDFFactory.DATETIME_DASH.format(device.getLastOffLineTime())
                        : null
        );
    }
}