使用Java写入Excel下拉选择框选项过多不显示问题

最新推荐文章于 2023-11-09 11:06:18 发布

no_one_coder

最新推荐文章于 2023-11-09 11:06:18 发布

阅读量3.1k

点赞数 6

分类专栏： Java 文章标签： poi excel java

本文链接：https://blog.csdn.net/csdnalexwang0610/article/details/114792583

版权

Java 专栏收录该内容

3 篇文章 1 订阅

订阅专栏

1.问题描述

工作中遇到需要使用Java poi读写Excel文件的问题，因为需求中有要求在写文件时创建下拉选择框。按照传统的直接使用List集合保存下拉选择框的选项，再通过poi本身的方法将选择框的选项添加到下拉框中。

一开始编写demo测试时只有几个选项，可以正确的添加下拉框。后来把方法搬到项目中，发现创建的文件中的下拉框的选项数据不能正确的显示。通过对比两个文件，发现唯一的不同点就是下拉框选项的数量存在差异。通过一番查询，发现poi添加下拉框选项的数量被控制在20个以内（官方说法好像是256个字节？记不太清楚了，如果说错了，请大佬批评指正orz）。

2.选项数量(<=20)较少的方法

向Excel中写数据，简单样例

/**
     * 将lines的内容写入excel中
     *
     * @param outputPath excel存放路径
     * @param lines      需要写入execl的文件
     * @throws IOException
     */
    public synchronized void writeInExcel(String outputPath, Collection<String[]> lines) throws IOException {

        @SuppressWarnings("resource")
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        // 写入Excel表格开头
        Row firstRow = sheet.createRow(0);
        ArrayList<String> arrayList = new ArrayList<String>();
        String[] excelHeadList = {"a","b","c","d","e"};
        for (int i = 0; i < excelHeadList.length; i++) {
            arrayList.add(excelHeadList[i]);
        }
        String[] arr = new String[arrayList.size()];
        for (int i = 0; i < arrayList.size(); i++) {
            arr[i] = arrayList.get(i);
        }
        writeInRow(firstRow, arr);

        if (lines != null && lines.size() > 0) {
            // 写入Excel表格内容
            AtomicInteger i = new AtomicInteger(1);
            lines.forEach(line -> {
                Row row = sheet.createRow(i.get());
                writeInRow(row, line);
                i.set(i.incrementAndGet());
            });
            DataValidationHelper helper = sheet.getDataValidationHelper();
            // 创建审核结果下拉框
            createDropDownBox(sheet, helper, checkResultList, 1, i.get() - 1, arr.length - 6, arr.length - 6);
        }
        // 写入指定的文件
        workbook.write(new FileOutputStream(outputPath));
    }

给Excel添加下拉框的方法

/**
     * 给Excel的添加下拉框
     *
     * @param sheet
     * @param helper
     * @param list     下拉框需要的数据
     * @param firstRow 首行
     * @param lastRow  尾行
     * @param firstCol 首列
     * @param lastCol  尾列
     */
    private void createDropDownBox(Sheet sheet, DataValidationHelper helper, String[] list, int firstRow, int lastRow,
                                   int firstCol, int lastCol) {
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);

        DataValidationConstraint constraint = helper.createExplicitListConstraint(list);
        DataValidation validation = helper.createValidation(constraint, addressList);
        if (validation instanceof XSSFDataValidation) {
            validation.setSuppressDropDownArrow(true);
            validation.setShowErrorBox(true);
        } else {
            validation.setSuppressDropDownArrow(false);
        }
        sheet.addValidationData(validation);
    }

把数组的内容写到Excel的行

/**
     * 将数组中的内容写入excel的行
     *
     * @param row  行
     * @param line 需要写入的数据
     */
    public static void writeInRow(Row row, String[] line) {
        if (line != null) {
            for (int i = 0; i < line.length; i++) {
                Cell cell = row.createCell(i);
                cell.setCellValue(line[i]);
            }
        }
    }

通过以上三个方法就可以实现向Excel中写入数据，并给指定的列添加下拉框

全部代码

package com.learning;

import org.apache.poi.hssf.util.CellRangeAddressList;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * @author: alex
 * @date: 2020/8/7 下午5:55
 */

public class Test {
    /**
     * 将lines的内容写入excel中
     *
     * @param outputPath excel存放路径
     * @param lines      需要写入execl的文件
     * @throws IOException
     */
    public synchronized void writeInExcel(String outputPath, Collection<String[]> lines) throws IOException {

        @SuppressWarnings("resource")
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        // 写入Excel表格开头
        Row firstRow = sheet.createRow(0);
        ArrayList<String> arrayList = new ArrayList<>();
        // 创建单元格标题数组
        String[] excelHeadList = {"row1","row2","row3","row4","row5"};
        for (int i = 0; i < excelHeadList.length; i++) {
            arrayList.add(excelHeadList[i]);
        }
        String[] arr = new String[arrayList.size()];
        for (int i = 0; i < arrayList.size(); i++) {
            arr[i] = arrayList.get(i);
        }
        writeInRow(firstRow, arr);

        String[] selectList = {"option1","option2","option3","option4","option5"};

        if (lines != null && lines.size() > 0) {
            // 写入Excel表格内容
            AtomicInteger i = new AtomicInteger(1);
            lines.forEach(line -> {
                Row row = sheet.createRow(i.get());
                writeInRow(row, line);
                i.set(i.incrementAndGet());
            });
            DataValidationHelper helper = sheet.getDataValidationHelper();
            // 创建审核结果下拉框
            createDropDownBox(sheet, helper, selectList, 1, i.get() - 1, arr.length - 6, arr.length - 6);
        }
        // 写入指定的文件
        workbook.write(new FileOutputStream(outputPath));
    }

    /**
     * 给Excel的添加下拉框
     *
     * @param sheet
     * @param helper
     * @param list     下拉框需要的数据
     * @param firstRow 首行
     * @param lastRow  尾行
     * @param firstCol 首列
     * @param lastCol  尾列
     */
    private void createDropDownBox(Sheet sheet, DataValidationHelper helper, String[] list, int firstRow, int lastRow,
                                   int firstCol, int lastCol) {
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);

        DataValidationConstraint constraint = helper.createExplicitListConstraint(list);
        DataValidation validation = helper.createValidation(constraint, addressList);
        if (validation instanceof XSSFDataValidation) {
            validation.setSuppressDropDownArrow(true);
            validation.setShowErrorBox(true);
        } else {
            validation.setSuppressDropDownArrow(false);
        }
        sheet.addValidationData(validation);
    }

    /**
     * 将数组中的内容写入excel的行
     *
     * @param row  行
     * @param line 需要写入的数据
     */
    public static void writeInRow(Row row, String[] line) {
        if (line != null) {
            for (int i = 0; i < line.length; i++) {
                Cell cell = row.createCell(i);
                cell.setCellValue(line[i]);
            }
        }
    }

    public static void main(String[] args) {
        System.out.println("hello world!!");
        Test test = new Test();
        List<String[]> list = new ArrayList<>();
        String[] strings = {"aaa","bbb","ccc","ddd","eee"};
        list.add(strings);
        try {
            test.writeInExcel("result/test.xlsx",list);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

实现效果如下图所示：
实现下拉框

3.下拉框选项的数量超过了20个

上边的方法只适用于下拉框选项的数量在20个以内的情况，如果选项的数量超过了20个，那么需要使用隐藏sheet的方式来实现。即：使用一个隐藏的sheet表来存储下拉框的选项数量，再通过定位引用的方式来获取到选项的值。

只需要在上面的writeInExcel方法中增加下面的代码段嘛，即可实现在Excel中创建一个隐藏的sheet。

// 创建一个隐藏的sheet，存放下拉框选项
Sheet hiddenSheet = workbook.createSheet("hiddenSelect");
//  把下拉框列表数据放进隐藏sheet
Cell cell = null;
for (int i = 0; i < selectList.length; i++) {
   Row row = hiddenSheet.createRow(i);
   cell = row.createCell(0);
   cell.setCellValue(selectList[i]);
}
Name nameCell = workbook.createName();
nameCell.setNameName(hiddenSheet.getSheetName());
nameCell.setRefersToFormula(hiddenSheet.getSheetName() + "!$A$1:$A$" + selectList.length);
workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet), true);

创建了隐藏的sheet，并进行定位之后，我们还需要对createDropDownBox方法进行修改：

/**
     * 给Excel的添加下拉框
     *
     * @param sheet
     * @param helper
     * @param list     下拉框需要的数据
     * @param firstRow 首行
     * @param lastRow  尾行
     * @param firstCol 首列
     * @param lastCol  尾列
     */
private void createDropDownBox(Sheet sheet, DataValidationHelper helper, String[] list, int firstRow, int lastRow,
                               int firstCol, int lastCol) {
   CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
   DataValidationConstraint constraint = null;
   DataValidation validation = null;
   // 为了适配xls和xlsx不同版本的Excel（即2003和2007版本的）
   if (sheet instanceof XSSFSheet || sheet instanceof SXSSFSheet) {
      constraint = helper.createFormulaListConstraint(hiddenSheet.getSheetName());
      validation = helper.createValidation(constraint, addressList);
   } else {
      constraint = DVConstraint.createFormulaListConstraint(hiddenSheet.getSheetName());
      validation = new HSSFDataValidation(addressList, constraint);
   }
   if (validation instanceof XSSFDataValidation) {
      validation.setSuppressDropDownArrow(true);
      validation.setShowErrorBox(true);
   } else {
      validation.setSuppressDropDownArrow(false);
   }
   sheet.addValidationData(validation);
}

通过以上的方法就可以实现超过20个选项数量的下拉框的创建。
真正用到项目中，还发现了一个问题：在2003版的Exce（即后缀名为：xls）中是可以看见隐藏sheet表的名称的，但是在新版的Office中（即后缀名为：xlsx）中，是看不见隐藏sheet表的。如下图所示：
hideenSelect下拉框选项

我还是太菜了，不知道是什么原因。如果有大佬知道，请指教。

如果有错误欢迎大家指出，有疑问欢迎交流讨论！

no_one_coder

关注

6
点赞
踩
18

收藏

觉得还不错? 一键收藏
2
评论
使用Java写入Excel下拉选择框选项过多不显示问题

1.问题描述工作中遇到需要使用Java poi读写Excel文件的问题，因为需求中有要求在写文件时创建下拉选择框。按照传统的直接使用List集合保存下拉选择框的选项，再通过poi本身的方法将选择框的选项添加到下拉框中。一开始编写demo测试时只有几个选项，可以正确的添加下拉框。后来把方法搬到项目中，发现创建的文件中的下拉框的选项数据不能正确的显示。通过对比两个文件，发现唯一的不同点就是下拉框选项的数量存在差异。通过一番查询，发现poi添加下拉框选项的数量被控制在20个以内（官方说法好像是256个字节？记
复制链接

扫一扫