Excel多级联动处理

Excel多级联动处理

演示

94d6a257cf58cc938864f94c8f11f0a5be8f06eb

此处会附上源代码,会有简单的说明,可改的会有详细的说明,不需要修改的无需修改直接服用就好

设置数据验证全代码

import com.google.common.collect.Lists;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.util.CellRangeAddressList;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtils {
    private HSSFCellStyle cellStyle;
    private List<String> titles;

    public List<String> getTitles() {
        return titles;
    }
    public void setTitles(List<String> titles) {
        this.titles = titles;
    }
    private final String[] charArr = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J",
            "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V",
            "W", "X", "Y", "Z"};
    private  HSSFSheet excelSheet;
    /**
     * 设置样式
     *
     * @param workbook Excel
     * @param sheet    标签
     */
    public void setDataCellStyles(HSSFWorkbook workbook, HSSFSheet sheet) {

        cellStyle = workbook.createCellStyle();
        // 设置边框
       /* cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

        // 设置背景色

        cellStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);

        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        // 设置居中

        cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);*/
        // 设置字体
        HSSFFont font = workbook.createFont();
        font.setFontName("宋体");

        font.setFontHeightInPoints((short) 11); // 设置字体大小

        cellStyle.setFont(font);// 选择需要用到的字体格式

        // 设置单元格格式为文本格式(这里还可以设置成其他格式,可以自行百度)

        HSSFDataFormat format = workbook.createDataFormat();

        cellStyle.setDataFormat(format.getFormat("@"));
    }
    /**
     * 创建表格
     */
    public HSSFWorkbook createTitle(String sheetName){
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 设置sheet 名称
        excelSheet = workbook.createSheet(sheetName);
        HSSFRow row = excelSheet.createRow(0);
        this.creatRow(row, titles);
        return workbook;
    }
    /**
     * 程序入口
     */
    public void Export(List<String> parents, Map<String, List<String>>dateMap,HSSFWorkbook workbook,String sheet,String hideSheetName) {
        //初始化类型
            // 创建一个隐藏页和隐藏数据集
            this.creatHideSheet(workbook, hideSheetName, parents, dateMap);
            // 设置名称数据集
            this.creatExcelNameList(workbook, hideSheetName, parents, dateMap, sheet);
            // 设置样式
            this.setDataCellStyles(workbook, excelSheet);
    }

    /**
     * 响应表结构
     * @param response
     * @param workbook
     * @throws IOException
     */
    public <T> void commit(HttpServletResponse response, HSSFWorkbook workbook,Class<?extends T>aclass,String fileName) throws IOException {
        try {
            // 创建一行数据
            for (int i = 1; i < 2; i++) {
                this.creatAppRow(excelSheet, i,aclass);
            }
            response.setContentType("application/vnd.ms-excel; charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + URLEncoder.encode(fileName, "UTF-8"));
            OutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 使用已定义的数据源方式设置一个数据验证
     * 不需要修改强制这个格式
     *
     * @param formulaString
     * @param naturalRowIndex
     * @param naturalColumnIndex
     * @return
     */
    private DataValidation getDataValidationByFormula(String formulaString,

                                                     int naturalRowIndex, int naturalColumnIndex) {

        // 加载下拉列表内容

        DVConstraint constraint = DVConstraint

                .createFormulaListConstraint(formulaString);

        // 设置数据有效性加载在哪个单元格上。

        // 四个参数分别是:起始行、终止行、起始列、终止列

        int firstRow = naturalRowIndex;

        int lastRow = naturalRowIndex;

        int firstCol = naturalColumnIndex - 1;

        int lastCol = naturalColumnIndex - 1;

        CellRangeAddressList regions = new CellRangeAddressList(firstRow,

                lastRow, firstCol, lastCol);

        // 数据有效性对象

        DataValidation data_validation_list = new HSSFDataValidation(regions,

                constraint);

        return data_validation_list;

    }

    /**
     * 创建一列数据
     *
     * @param hssfSheet
     */
    private void creatAppRow(HSSFSheet hssfSheet, int naturalRowIndex,Class aclass) {
        // 获取行
        HSSFRow hssfRow = hssfSheet.createRow(naturalRowIndex);

        HSSFCell province = hssfRow.createCell(0);
        province.setCellValue("");
        province.setCellStyle(cellStyle);
        HSSFCell City = hssfRow.createCell(1);
        City.setCellValue("");
        City.setCellStyle(cellStyle);
        HSSFCell City1 = hssfRow.createCell(2);
        City1.setCellValue("");
        City1.setCellStyle(cellStyle);
        //提取数据对象参数并在指定的列开启下拉框
        Map<String, Boolean> aclassMap = new HashMap<String, Boolean>();
        for (Field field : aclass.getDeclaredFields()) {
            field.setAccessible(true);
            ConfigurationFileName annotation = field.getAnnotation(ConfigurationFileName.class);
            if (annotation != null && annotation.multiSelect()) {
                aclassMap.put(annotation.name(), annotation.multiSelect());
            }
        }
        // 得到验证对象
        DataValidation data_validation_list1 = this.getDataValidationByFormula(
                "province", naturalRowIndex, 1);
        hssfSheet.addValidationData(data_validation_list1);
        ArrayList<String> list = Lists.newArrayList(charArr);
        int num=0;
        for (int i = 1; i < titles.size(); i++) {
            if (aclassMap.get(titles.get(i)) != null) {
                if (aclassMap.get(titles.get(i)) != null) {
                    DataValidation data_validation_list2 = this
                            .getDataValidationByFormula("INDIRECT($"+list.get(num)
                                    + (naturalRowIndex) + ")", naturalRowIndex, i+1);
                    hssfSheet.addValidationData(data_validation_list2);
                    num++;
                }
            }
        }
    }
    /**
     * 名称管理
     * 此处不需要更改默认即可
     */
    private void creatExcelNameList(HSSFWorkbook workbook, String hideSheetName, List<String> KeyList, Map<String, List<String>> dataList, String nameManagement) {
        Name name;
        name = workbook.createName();
        name.setNameName(nameManagement);
        name.setRefersToFormula(hideSheetName + "!$A$1:$"
                + this.getcellColumnFlag(KeyList.size()) + "$1");
        for (int i = 0; i < KeyList.size(); i++) {
            List<String> num = new ArrayList<String>();
            if (dataList.get(KeyList.get(i)) != null) {
                name = workbook.createName();
                num.add(0, KeyList.get(i).toString());
                num.addAll(dataList.get(KeyList.get(i)));
                name.setNameName(KeyList.get(i).toString());
                name.setRefersToFormula(hideSheetName + "!$B$" + (i + 2) + ":$"
                        + this.getcellColumnFlag(num.size()) + "$" + (i + 2));
            }
        }
    }

    /**
     * 根据数据值确定单元格位置(比如:28-AB) 默认即可
     */
    private String getcellColumnFlag(int num) {
        String columFiled = "";
        int chuNum = 0;
        int yuNum = 0;
        if (num >= 1 && num <= 26) {
            columFiled = this.doHandle(num);
        } else {
            chuNum = num / 26;
            yuNum = num % 26;
            columFiled += this.doHandle(chuNum);
            columFiled += this.doHandle(yuNum);
        }

        return columFiled;

    }

    /**
     * 选择域 不需要更改默认即可
     */
    private String doHandle(final int num) {
        return charArr[num - 1].toString();

    }

    /**
     * 创建一列数据 默认即可
     *
     * @param currentRow Excel列
     * @param text       数据
     */
    private void creatRow(HSSFRow currentRow, List<String> text) {
        if (text != null) {
            int i = 0;
            for (String cellValue : text) {
                // 注意列是从(1)下标开始
                HSSFCell userNameLableCell = currentRow.createCell(i++);
                userNameLableCell.setCellValue(cellValue);
            }
        }
    }
    /**
     * 创建数据域名称 默认即可
     *
     * @param workbook      Excel
     * @param hideSheetName 数据域标签
     * @param dataList      数据名称
     */
    private void creatHideSheet(HSSFWorkbook workbook, String hideSheetName, List<String> KeyList, Map<String, List<String>> dataList) {
        // 创建数据域
        HSSFSheet sheet = workbook.createSheet(hideSheetName);
        // 用于记录行
        int rowRecord = 0;
        // 获取行(从0下标开始)
        HSSFRow provinceRow = sheet.createRow(rowRecord);
        this.creatRow(provinceRow, KeyList);
        rowRecord++;
        //数据分组
        for (int i = 0; i < KeyList.size(); i++) {
            List<String> list = new ArrayList<String>();
            if (dataList.get(KeyList.get(i)) != null) {
                list.add(0, KeyList.get(i).toString());
                //数据截取
                list.addAll(dataList.get(KeyList.get(i)));
            }
            //获取行
            HSSFRow Cityrow = sheet.createRow(rowRecord);
            this.creatRow(Cityrow, list);
            rowRecord++;
        }
    }

}

Pom

<dependency>
    <groupId>com.google.guava</groupId>
    <artifactId>guava</artifactId>
    <version>29.0-jre</version>
</dependency>
    <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.2</version>
<!--            <version>4.1.2</version>-->
        </dependency>

代码解读

image-20221115115849545
  • commit

给客户端响应Excel表格

  • Export

填充数据

  • setDataCellStyles

程序的开始创建Excel对象

教程尚未结束后期补充

THE END
喜欢就支持一下吧
点赞15 分享
评论 抢沙发
头像
欢迎您留下宝贵的见解!
提交
头像

昵称

取消
昵称表情代码图片

    暂无评论内容