EASYUI POI导出EXCEL二级联动下拉框

最新推荐文章于 2024-01-21 18:54:22 发布

W20111026

最新推荐文章于 2024-01-21 18:54:22 发布

阅读量1k

点赞数 2

文章标签： java easyui poi

本文链接：https://blog.csdn.net/w20111026/article/details/122099477

版权

该博客介绍了如何在Java项目中结合EasyUI和POI库来导出Excel文件，并且这些Excel包含二级联动的下拉框功能。通过调用特定的工具类ExcelTemplate，开发者可以设置导出的属性，实现复杂的Excel模板导出。文章还提到了参考的相关文档链接以供深入学习。

摘要由CSDN通过智能技术生成

1、项目应用，工具类ExcelTemplate

package com.utils;

import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Classname ExcelTemplate
 * @Description 导出模板下拉框二级联动
 * @Created by wcy
 * @date 2021/12/21 10:16
 */
public class ExcelTemplate {

    private static final int XLS_MAX_ROW = 65535; //0开始
    private static final String PARENT_NAME = "oneName";
    private static final String hiddenSheet = "hidden";

    /**
     * 创建excel二级联动模板
     */
    public static void createExcelComboBox(Workbook wb, Sheet mainSheet, List<String> parent, Map<String, List<String>> itemMap,int oneOrderNum, int twoOrderNum) throws IOException {

            //创建工作薄,存放下拉框数据
            Sheet hidden = wb.createSheet(hiddenSheet);
            wb.setSheetHidden(1, true); //将用于存储下拉框数据的sheet隐藏
            //初始化下拉框数据
            initComboBox(wb, hidden, parent, itemMap);
			//初始化级联关系
          initSheetNameMapping(mainSheet,oneOrderNum,twoOrderNum);
    }

    /**
     * 初始化二级联动下拉框数据
     */
    private static void initComboBox(Workbook workbook, Sheet sheet, List<String> parent, Map<String, List<String>> itemList) {
        writeOneComboBox(sheet, parent);
        writeTwoComboBox(workbook, sheet, parent, itemList);
        initParentNameMapping(workbook, sheet.getSheetName(), parent.size());
    }

    /**
     * 在隐藏sheet中写入二级下拉框数据
     */
    private static void writeTwoComboBox(Workbook workbook, Sheet sheet, List<String> parent, Map<String, List<String>> itemList) {
        for (int i = 0; i < parent.size(); i++) {
            int referColNum = i + 1;
            String parentName = parent.get(i);
            List<String> items = itemList.get(parentName);
            if (CollectionUtils.isNotEmpty(items)) {
                int rowCount = sheet.getLastRowNum();
                if(rowCount == 0 && sheet.getRow(0) == null )
                    sheet.createRow(0);
                for (int j = 0; j < items.size(); j++) {
                    if (j <= rowCount) { //前面创建过的行，直接获取行，创建列
                        sheet.getRow(j).createCell(referColNum).setCellValue(items.get(j)); //设置对应单元格的值
                    } else { //未创建过的行，直接创建行、创建列
                        sheet.setColumnWidth(j, 4000); //设置每列的列宽
                        //创建行、创建列
                        sheet.createRow(j).createCell(referColNum).setCellValue(items.get(j)); //设置对应单元格的值
                    }
                }
            }
            initShelfNameMapping(workbook, sheet.getSheetName(), parentName, referColNum, items.size());
        }
    }

    /**
     * 一级与二级下拉选择数据关联
     */
    private static void initShelfNameMapping(Workbook workbook, String sheetName, String parentName, int referColNum, int shelfQuantity) {
        Name name = workbook.createName();
        // 设置二级名称
        name.setNameName(parentName);
        String referColName = getColumnName(referColNum);
        String formula = sheetName + "!$" + referColName + "$1:$" + referColName + "$" + shelfQuantity;
        name.setRefersToFormula(formula);
    }

    /**
     * 在隐藏sheet中写入一级下拉框数据
     */
    private static void writeOneComboBox(Sheet sheet, List<String> parent) {
        for (int i = 0; i < parent.size(); i++) {
            Row row = sheet.createRow(i);
            Cell cell = row.createCell(0);
            cell.setCellValue(parent.get(i));
        }
    }

    /**
     * 初始化一级下拉选择“名称”
     */
    private static void initParentNameMapping(Workbook workbook, String sheetName, int parentQuantity) {
        Name name = workbook.createName();
        // 设置一级下拉名称
        name.setNameName(PARENT_NAME);
        name.setRefersToFormula(sheetName + "!$A$1:$A$" + parentQuantity);
    }

    /**
     * 根据数据值确定单元格位置（比如：0-A, 27-AB）
     *
     * @param index
     * @return
     */
    public static String getColumnName(int index) {
        StringBuilder s = new StringBuilder();
        while (index >= 26) {
            s.insert(0, (char) ('A' + index % 26));
            index = index / 26 - 1;
        }
        s.insert(0, (char) ('A' + index));
        return s.toString();
    }

    /**
     * 主sheet中下拉框初始化
     *
     * @param mainSheet
     */
    private static void initSheetNameMapping(Sheet mainSheet, int oneOrderNum, int twoOrderNum) {
        DataValidation warehouseValidation = getDataValidationByFormula(PARENT_NAME, oneOrderNum-1);
        DataValidation shelfValidation = getDataValidationByFormula("INDIRECT($"+doHandle(oneOrderNum)+"1)", twoOrderNum-1); //formula同"INDIRECT(INDIRECT(\"R\"&ROW()&\"C\"&(COLUMN()-1),FALSE))"
        // 主sheet添加验证数据
        mainSheet.addValidationData(warehouseVali