POI 生成带联动下拉框的excel表格

2022-11-28 16:03:36

参考:https://www.cnblogs.com/cjbbk/p/7527276.html

解决POI3.17 与其它版本的不同的坑:https://blog.csdn.net/Weirdo_zhu/article/details/79912606

3.17 非常详细:https://www.cnblogs.com/huajiezh/p/5467821.html

参考：http://www.360doc.com/content/17/1101/11/40984640_699967795.shtml

POI 冻结某一行:

在POI中有createFreezePane方法能直接固定单元格,具体参数如下:

sheet.createFreezePane(int cellNum,int rowNum,int firstCellNum,int firstRollNum );

四个参数分别代表:
cellNum:表示要冻结的列数；
rowNum:表示要冻结的行数；
firstCellNum:表示被固定列右边第一列的列号；
firstRollNum :表示被固定行下边第一列的行号;

注意: 后2个参数均从0开始计算列号和行号,且firstCellNum>=cellNum &&firstRollNum >=cellNum
如:

// 冻结第一行

sheet1.createFreezePane( 0, 1, 0, 1 );

// 冻结第一列

sheet2.createFreezePane( 1, 0, 1, 0 );

sheet.createFreezePane(,,,);//就是固定了首列,列号的显示为:A,BCDEF... 
sheet.createFreezePane(1,0,3,0);//固定了首列,列号的显示为:A,DEF... 
//注意:BC列不是被隐藏,而是默认显示列为A,DEF,若想要看BC列,只需移动滚轮即可.行号同理

--------------------- 

原文：https://blog.csdn.net/qq_24076135/article/details/77449898?utm_source=copy

依赖:

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->

        <dependency>

            <groupId>org.apache.poi</groupId>

            <artifactId>poi</artifactId>

            <version>3.17</version>

        </dependency>

        <dependency>

            <groupId>org.apache.poi</groupId>

            <artifactId>poi-ooxml</artifactId>

            <version>3.17</version>

        </dependency>

        <dependency>

            <groupId>org.apache.poi</groupId>

            <artifactId>poi-ooxml-schemas</artifactId>

            <version>3.17</version>

        </dependency>

注释,下拉选(生成execl基本使用)

package com.icil.esolution;

import static org.junit.Assert.*;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.InputStream;

import java.util.Date;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

import org.apache.poi.ss.util.CellRangeAddress;

import org.apache.poi.ss.util.CellRangeAddressList;

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

import org.junit.Test;

import org.springframework.boot.autoconfigure.data.cassandra.CassandraReactiveRepositoriesAutoConfiguration;

public class ReadExcelByPOITest {

    @Test

    public void CreateExceltest01() throws Exception {

                Workbook[] wbs = new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() };

                    Workbook workbook =  new HSSFWorkbook();

                    // 得到一个POI的工具类

                    CreationHelper createHelper = workbook.getCreationHelper();

                    // 在Excel工作簿中建一工作表，其名为缺省值, 也可以指定Sheet名称

                    Sheet sheet = workbook.createSheet();

                    // Sheet sheet = workbook.createSheet("SheetName");

                    // 用于格式化单元格的数据

                    DataFormat format = workbook.createDataFormat();

                    // 设置字体

                    Font font = workbook.createFont();

                    font.setFontHeightInPoints((short) ); // 字体高度

                    font.setColor(Font.COLOR_RED); // 字体颜色

                    font.setFontName("黑体"); // 字体

                 //   font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 宽度

                    font.setItalic(true); // 是否使用斜体

                    // font.setStrikeout(true); //是否使用划线

                    // 设置单元格类型

                    CellStyle cellStyle = workbook.createCellStyle();

                    cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框

                    cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框

                    cellStyle.setBorderTop(BorderStyle.THIN);// 上边框

                    cellStyle.setBorderRight(BorderStyle.THIN);// 右边框

                    cellStyle.setFont(font);

                 //   cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中

                    cellStyle.setWrapText(true);

                    CellStyle cellStyle2 = workbook.createCellStyle();

                    cellStyle2.setDataFormat(format.getFormat("＃, ## 0.0"));

                    CellStyle cellStyle3 = workbook.createCellStyle();

                    cellStyle3.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm:ss"));

                    // 添加单元格注释

                    // 创建Drawing对象,Drawing是所有注释的容器.

                    Drawing drawing = sheet.createDrawingPatriarch();

                    // ClientAnchor是附属在WorkSheet上的一个对象， 其固定在一个单元格的左上角和右下角.

                    ClientAnchor anchor = createHelper.createClientAnchor();

                    // 设置注释位子

                    anchor.setRow1();

                    anchor.setRow2();

                    anchor.setCol1();

                    anchor.setCol2();

                    // 定义注释的大小和位置,详见文档

                    Comment comment = drawing.createCellComment(anchor);

                    // 设置注释内容

                    RichTextString str = createHelper.createRichTextString("hello sea");

                    comment.setString(str);

                    // 设置注释作者. 当鼠标移动到单元格上是可以在状态栏中看到该内容.

                    comment.setAuthor("Sea");

                    //注释方法2

                    // 定义注释的大小和位置，详见文档

                    /**

                     * 前面四个参数:指定位置:／／从那一行开始，到那一行结束，从那一列开始，到那一列结束；

                     */

                    Comment comment1 = drawing.createCellComment(new HSSFClientAnchor(,,,, (short),  ,(short) , ));

                    // 设置注释内容

                    comment1.setString(str);

                    //下拉选  //(int firstRow, int lastRow, int firstCol, int lastCol)  ／／从那一行开始，到那一行结束，从那一列开始，到那一列结束；

                    CellRangeAddressList regions = new CellRangeAddressList(, ,, );

                    DVConstraint constraint =DVConstraint.createExplicitListConstraint(new String[] { "百度","阿里", "腾讯" });//限制只能选中这些值,否则报错

                    HSSFDataValidation dataValidate = new HSSFDataValidation(regions,constraint);

                    sheet.addValidationData(dataValidate);

                    // 定义几行

                    for (int rownum = ; rownum < ; rownum++) {

                        // 创建行

                        Row row = sheet.createRow(rownum);

                        // 创建单元格

                        Cell cell = row.createCell((short) );

                        cell.setCellValue(createHelper.createRichTextString("Hello！" + rownum));// 设置单元格内容

                        cell.setCellStyle(cellStyle);// 设置单元格样式

                        cell.setCellType(Cell.CELL_TYPE_STRING);// 指定单元格格式：数值、公式或字符串

                        /**

                         * 说明注释是唯一的,只会加载一个地方,在for 循环中.默认会加载到最后一个地方

                         */

                        cell.setCellComment(comment);// 添加注释  

                        // 格式化数据

                        Cell cell2 = row.createCell((short) ); //参数表示在那一列显示（起始值为０）

                        cell2.setCellValue(11111.25);

                        cell2.setCellStyle(cellStyle2);

                        Cell cell3 = row.createCell((short) );

                        cell3.setCellValue(new Date());

                        cell3.setCellStyle(cellStyle3);

                        sheet.autoSizeColumn((short) ); // 调整第一列宽度　／／数子是＋１　列

                        sheet.autoSizeColumn((short) ); // 调整第二列宽度

                        sheet.autoSizeColumn((short) ); // 调整第三列宽度

                        sheet.autoSizeColumn((short) ); // 调整第四列宽度

                    }

                    // 合并单元格／／四个参数：前两个表示行：从哪行开始到哪一行结束　　　后面两个参数：从哪一列开始到哪以咧结束

                    sheet.addMergedRegion(new CellRangeAddress(, // 第一行（0）

                            , // last row（0-based）

                            , // 第一列（基于0）

                             // 最后一列（基于0）

                    ));

                    // 保存

                    String filename = "/home/sea/Desktop/workbook01.xls";

                    if (workbook instanceof XSSFWorkbook) {

                        filename = filename + "x";

                    }

                    FileOutputStream out = new FileOutputStream(filename);

                    workbook.write(out);

                    out.close();

    }

}

自定义下拉选错误提示可参考:http://wuhaidong.iteye.com/blog/2039848

/** * excel添加下拉数据校验 * @param sheet 哪个 sheet 页添加校验 * @param dataSource 数据源数组 * @param col 第几列校验（0开始） * @return */ public static DataValidation createDataValidation(Sheet sheet, String[] dataSource, int col) { CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(, , col, col); DataValidationHelper helper = sheet.getDataValidationHelper(); DataValidationConstraint constraint = helper.createExplicitListConstraint(dataSource); DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList); //处理Excel兼容性问题 if (dataValidation instanceof XSSFDataValidation) { dataValidation.setSuppressDropDownArrow(true); dataValidation.setShowErrorBox(true); } else { dataValidation.setSuppressDropDownArrow(false); } dataValidation.setEmptyCellAllowed(true); dataValidation.setShowPromptBox(true); dataValidation.createPromptBox("提示", "只能选择下拉框里面的数据"); return dataValidation; }

作者：赛亚人之神

链接：https://www.jianshu.com/p/3fb7feca9685

來源：简书

简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。

未修改:联动

import java.io.File;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.List;

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

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

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

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

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

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

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

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

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

import org.apache.poi.hssf.util.HSSFColor;

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

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

import org.apache.poi.ss.util.CellRangeAddressList;

public class ExcelLinkage {

    // 样式

    private HSSFCellStyle cellStyle;

    // 初始化省份数据

    private List<String> province = new ArrayList<String>(Arrays.asList("湖南",

            "广东"));

    // 初始化数据（湖南的市区）

    private List<String> hnCity = new ArrayList<String>(Arrays.asList("长沙市",

            "邵阳市"));

    // 初始化数据（广东市区）

    private List<String> gdCity = new ArrayList<String>(Arrays.asList("深圳市",

            "广州市"));

    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) ); // 设置字体大小

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

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

        HSSFDataFormat format = workbook.createDataFormat();

        cellStyle.setDataFormat(format.getFormat("@"));

    }

    /**

     * 创建数据域（下拉联动的数据）

     *

     * @param workbook

     * @param hideSheetName

     *            数据域名称

     */

    private void creatHideSheet(HSSFWorkbook workbook, String hideSheetName) {

        // 创建数据域

        HSSFSheet sheet = workbook.createSheet(hideSheetName);

        // 用于记录行

        int rowRecord = ;

        // 获取行（从0下标开始）

        HSSFRow provinceRow = sheet.createRow(rowRecord);

        // 创建省份数据

        this.creatRow(provinceRow, province);

        // 根据省份插入对应的市信息

        rowRecord++;

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

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

            // 我这里是写死的 ， 实际中应该从数据库直接获取更好

            if (province.get(i).toString().equals("湖南")) {

                // 将省份名称放在插入市的第一列， 这个在后面的名称管理中需要用到

                list.add(, province.get(i).toString());

                list.addAll(hnCity);

            } else {

                list.add(, province.get(i).toString());

                list.addAll(gdCity);

            }

            //获取行

            HSSFRow Cityrow = sheet.createRow(rowRecord);

            // 创建省份数据

            this.creatRow(Cityrow, list);

            rowRecord++;

        }

    }

    /**

     * 创建一列数据

     *

     * @param currentRow

     * @param textList

     */

    public void creatRow(HSSFRow currentRow, List<String> text) {

        if (text != null) {

            int i = ;

            for (String cellValue : text) {

                // 注意列是从（1）下标开始

                HSSFCell userNameLableCell = currentRow.createCell(i++);

                userNameLableCell.setCellValue(cellValue);

            }

        }

    }

    /**

     * 名称管理

     *

     * @param workbook

     * @param hideSheetName

     *            数据域的sheet名

     */

    private void creatExcelNameList(HSSFWorkbook workbook, String hideSheetName) {

        Name name;

        name = workbook.createName();

        // 设置省名称

        name.setNameName("province");

        name.setRefersToFormula(hideSheetName + "!$A$1:$"

                + this.getcellColumnFlag(province.size())+ "$1");

        // 设置省下面的市

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

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

            if (province.get(i).toString().equals("湖南")) {

                name = workbook.createName();

                num.add(,province.get(i).toString());

                num.addAll(hnCity);

                name.setNameName(province.get(i).toString());

                name.setRefersToFormula(hideSheetName + "!$B$" + (i + ) + ":$"

                            + this.getcellColumnFlag(num.size()) + "$" + (i + ));

            } else {

                name = workbook.createName();

                num.add(,province.get(i).toString());

                num.addAll(gdCity);

                name.setNameName(province.get(i).toString());

                name.setRefersToFormula(hideSheetName + "!$B$" + (i + ) + ":$"

                            + this.getcellColumnFlag(num.size()) + "$" + (i + ));

            }

        }

    }

    // 根据数据值确定单元格位置（比如：28-AB）

    private String getcellColumnFlag(int num) {

        String columFiled = "";

        int chuNum = ;

        int yuNum = ;

        if (num >=  && num <= ) {

            columFiled = this.doHandle(num);

        } else {

            chuNum = num / ;

            yuNum = num % ;

            columFiled += this.doHandle(chuNum);

            columFiled += this.doHandle(yuNum);

        }

        return columFiled;

    }

    private String doHandle(final int num) {

        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" };

        return charArr[num - ].toString();

    }

    /**

     * 使用已定义的数据源方式设置一个数据验证

     *

     * @param formulaString

     * @param naturalRowIndex

     * @param naturalColumnIndex

     * @return

     */

    public DataValidation getDataValidationByFormula(String formulaString,

            int naturalRowIndex, int naturalColumnIndex) {

        // 加载下拉列表内容

        DVConstraint constraint = DVConstraint

                .createFormulaListConstraint(formulaString);

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

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

        int firstRow = naturalRowIndex;

        int lastRow = naturalRowIndex;

        int firstCol = naturalColumnIndex - ;

        int lastCol = naturalColumnIndex - ;

        CellRangeAddressList regions = new CellRangeAddressList(firstRow,

                lastRow, firstCol, lastCol);

        // 数据有效性对象

        DataValidation data_validation_list = new HSSFDataValidation(regions,

                constraint);

        return data_validation_list;

    }

    /**

     * 创建一列数据

     *

     * @param hssfSheet

     */

    public void creatAppRow(HSSFSheet hssfSheet, int naturalRowIndex) {

        // 获取行

        HSSFRow hssfRow = hssfSheet.createRow(naturalRowIndex);

        HSSFCell province = hssfRow.createCell();

        province.setCellValue("");

        province.setCellStyle(cellStyle);

        HSSFCell City = hssfRow.createCell();

        City.setCellValue("");

        City.setCellStyle(cellStyle);

        // 得到验证对象

        DataValidation data_validation_list1 = this.getDataValidationByFormula(

                "province", naturalRowIndex, );

        DataValidation data_validation_list2 = this

                .getDataValidationByFormula("INDIRECT($A"

                        + (naturalRowIndex + ) + ")", naturalRowIndex, );

        // 工作表添加验证数据

        hssfSheet.addValidationData(data_validation_list1);

        hssfSheet.addValidationData(data_validation_list2);

    }

    public void Export() {

        try {

            File file = new File("F:/excel.xls");

            FileOutputStream outputStream = new FileOutputStream(file);

            // 创建excel

            HSSFWorkbook workbook = new HSSFWorkbook();

            // 设置sheet 名称

            HSSFSheet excelSheet = workbook.createSheet("excel");

            // 设置样式

            this.setDataCellStyles(workbook, excelSheet);

            // 创建一个隐藏页和隐藏数据集

            this.creatHideSheet(workbook, "shutDataSource");

            // 设置名称数据集

            this.creatExcelNameList(workbook, "shutDataSource");

            // 创建一行数据

            for (int i = ; i < ; i++) {

                this.creatAppRow(excelSheet,i);

            }

            workbook.write(outputStream);

            outputStream.close();

        } catch (FileNotFoundException e) {

            e.printStackTrace();

        } catch (IOException e) {

            e.printStackTrace();

        }

    }

    public static void main(String[] args) {

        ExcelLinkage linkage = new ExcelLinkage();

        linkage.Export();

    }

}

码农公寓

相关文章