POI删除Excel中数据有效性

        读到这篇文章，请停下您Ctrl+c,Ctrl+v的脚步。因为这并不是真正的删除数据有效性，而是通过一种方式实现多次为某列设置数据有效性。我是真的百度没查到，Google没查到，官网也没找到关于使用POI删除数据有效性的方法。这里用到的思路是准备一个并没有设置过数据有效性的原始模板，每次修改数据有效性都复制这个模板，然后在新的模板上操作。

        本文将致力于解决以下几个问题：

1.多次为某列设置数据有效性真的没有效果吗？

2.如何实现“删除”某列的数据有效性，即能多次为某列设置数据有效性

3.设置数据有效性时选项超过255个ASCLL码，即报String literals in formulas can't be bigger than 255 characters ASCII错误怎么解决

一、多次为某列设置数据有效性真的没有效果吗？

        首先我在D盘放一个excel文件，待会用来操作

其中的内容是这样的，注意：部门这一列是并没有设置数据有效性，而我们一会要使用POI为其设置数据有效性

 我用的POI依赖如下所示

		<!--操作excel-->
		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>4.0.1</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>4.0.1</version>
		</dependency>

                <!--文件操作工具-->
		<!-- https://mvnrepository.com/artifact/commons-io/commons-io -->
		<dependency>
			<groupId>commons-io</groupId>
			<artifactId>commons-io</artifactId>
			<version>2.6</version>
		</dependency>

代码如下所示，每一行代码的作用都用注释写得清清楚楚。

package cn.waterch.partyconstruction.util;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.io.File;
import java.io.FileOutputStream;

public class Test {
    public static void main(String[] args) {
        try {
            //文件路径
            String path = "D://test/origin-template.xls";
            File originTemplateFile = new File(path);
            //读取新的模板excel文件,考虑到兼容性，使用Excel 2007之前的格式
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(FileUtils.openInputStream(originTemplateFile));
            //获取系统中的所有党支部名称
            //List<String> branchList = branchService.getBranchNameTotal();
            //用来作为数据有效性下拉列表的选项
            String[] departments = new String[] {"部门1","部门2"};
            //读取工作表sheet
            HSSFSheet sheet = hssfWorkbook.getSheetAt(0);
            //第一行需要格式化的单元格(单元格下标是从0开始的，且前6行用来写提示信息了，因此从第7行开始操作，其下标为6)
            int firstRowNum = 6;
            //最后一行需要格式化的单元格，默认格式化约200行，根据需要设置
            int lastRowNum = 200;
            //第6列，也就是每一行党支部的那一列都需要进行格式化
            int colume = 6;

            //需要进行格式化的范围
            CellRangeAddressList regions = new CellRangeAddressList(firstRowNum, lastRowNum, colume, colume);

            //设置下拉列表值
            DVConstraint constraint = DVConstraint.createExplicitListConstraint(departments);
            //结合范围设置数据有效性
            HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
            //设置提示信息
            dataValidation.createPromptBox("温馨提示","此项禁止自行填写，请从下拉列表中选择");
            //为该sheet添加数据有效性
            sheet.addValidationData(dataValidation);
            //删除原模板文件。因为模板文件中的数据已经被读入hssfWorkbook中了，现在删除也没关系
            if(originTemplateFile.exists()) {
                originTemplateFile.delete();
            }
            //打开文件输出流
            FileOutputStream stream = FileUtils.openOutputStream(originTemplateFile);
            //写入流信息
            hssfWorkbook.write(stream);
            //关闭输出流
            stream.close();
        } catch (Exception e) {
            System.out.println("设置数据有效性失败：" + e.getMessage());
        }
    }

}

执行此程序后打开被修改的excel文件发现数据有效性确实设置上去了，而且一直设置到索引为200的单元格。

下面我们将此句代码

   String[] departments = new String[] {"部门1","部门2"};

改为

  String[] departments = new String[] {"测试1","测试2"};

模仿多次设置数据有效性。

再次执行此程序后发现下拉选项并没有变为“测试1”，“测试2”，还是第一次设置的数据。

可是真的没变吗？真的没办法多次设置数据有效性吗？

现在我们打开Excel，手动取消数据有效性。

依次选择数据->数据验证->将序列改为任何值->确定。

        此时我们发现刚刚取消了数据有效性的单元格非但数据有效性还存在，而且显示的还是我们刚刚用代码设置的没有生效的下拉选项。如果你点击其他单元格就会发现，其他单元格中的下拉选项还是“部门1”，“部门2”。

        这是为什么？这现象也太奇怪了，这是什么神仙操作？别着急，向下看

        其实我们第二次用代码设置的数据有效性并不是没有生效，而是被第一次的数据有效性覆盖了。是的，你没听错，新的被旧的覆盖了，所以显示的现象就是多次为同一列设置数据有效性不生效。

        然后我就进行了一系列的尝试，查博客，扒官网，寻找删除数据有效性的方法，最后是真的真的没找到。好，那我就把已经设置了数据有效性的约200行单元格删掉。

 HSSFRow.removeCell(HSSFCell);

        其中HSSFRow表示循环获取的每一行单元格，HSSFCell表示每一行的部门列的单元格。结论是根本没效果。 

        然后我又发现了官网竟然还提供了另一种删除行数据的方法shiftRows。其实这个方法并不是删除，而是将整行进行移动，第一个参数表示从哪行开始，第二个参数表示从哪行结束，第三个参数表示从开始行到结束行整体向下移动多少行（负数表示向上移动）。结论同样是没效果。

 sheet.shiftRows(6,200,-200);

这样的话就只能进入我们的第二个栏目了。

二、如何实现“删除”某列的数据有效性，即能多次为某列设置数据有效性

首先将原始模板设置的数据有效性手动取消，即准备一个没有设置过数据有效性的干净的模板。

 只需将第一个单元格的数据有效性去掉，鼠标放在此单元格右下角的小方块，待鼠标改变形状按住向下拉就可以了。

注意：一定一定一定要把以上代码设置的提示也去掉，否则代码将不生效。

思路：准备一个并没有设置过数据有效性的原始模板，每次修改数据有效性都复制这个模板，然后在新的模板上操作。

注意下面代码中出现变化的几个地方

package cn.waterch.partyconstruction.util;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.io.File;
import java.io.FileOutputStream;

public class Test {
    public static void main(String[] args) {
        try {
            //文件路径
            String path = "D://test/origin-template.xls";
            File originTemplateFile = new File(path);
            //通过原始模板父路径和新的文件名得到新模板File
            File templateFile = new File(originTemplateFile.getParent(), "party-template.xls");
            //将原始模板文件数据复制到新的模板文件
            FileUtils.copyFile(originTemplateFile,templateFile);
            //读取新的模板excel文件,考虑到兼容性，使用Excel 2007之前的格式
            //注意：这里读取的是复制得到的新模板
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(FileUtils.openInputStream(templateFile));
            //获取系统中的所有党支部名称
            //List<String> branchList = branchService.getBranchNameTotal();
            //用来作为数据有效性下拉列表的选项
            String[] departments = new String[] {"部门1","部门2"};
            //读取工作表sheet
            HSSFSheet sheet = hssfWorkbook.getSheetAt(0);
            //第一行需要格式化的单元格(单元格下标是从0开始的，且前6行用来写提示信息了，因此从第7行开始操作，其下标为6)
            int firstRowNum = 6;
            //最后一行需要格式化的单元格，默认格式化约200行，根据需要设置
            int lastRowNum = 200;
            //第6列，也就是每一行党支部的那一列都需要进行格式化
            int colume = 6;
            
            //需要进行格式化的范围
            CellRangeAddressList regions = new CellRangeAddressList(firstRowNum, lastRowNum, colume, colume);

            //设置下拉列表值
            DVConstraint constraint = DVConstraint.createExplicitListConstraint(departments);
            //结合范围设置数据有效性
            HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
            //设置提示信息
            dataValidation.createPromptBox("温馨提示","此项禁止自行填写，请从下拉列表中选择");
            //为该sheet添加数据有效性
            sheet.addValidationData(dataValidation);
            //删除新模板文件。因为模板文件中的数据已经被读入hssfWorkbook中了，现在删除也没关系，
            if(templateFile.exists()) {
                templateFile.delete();
            }
            //打开文件输出流
            FileOutputStream stream = FileUtils.openOutputStream(templateFile);
            //写入流信息
            hssfWorkbook.write(stream);
            //关闭输出流
            stream.close();
        } catch (Exception e) {
            System.out.println("设置数据有效性失败：" + e.getMessage());
        }
    }

}

程序执行完毕后，打开文件夹发现我们的新模板已经存在了。

 

打开party-template.xls可以看到数据有效性设置成功

接下来，将改变数据有效性下拉选项值，再次运行程序

    String[] departments = new String[] {"测试1","测试2"};

        打开party-template.xls,铛裆当档~重复设置数据有效性成功。你可以将party-template.xls当作模板返回来完成你的需求，也可以继续在party-template.xls文件上进行操作，返回操作后的文件来完成你的需求。

        多说一句，如果你数据有效性下拉列表的数组中数据特别多，就会报

String literals in formulas can't be bigger than 255 characters ASCII错误，下面我们来解决这个问题。

三、设置数据有效性时选项超过255个ASCLL码，即报String literals in formulas can't be bigger than 255 characters ASCII错误怎么解决

其实不仅是在代码中设置的数据有效性数据太多会报这个错误，在来源处能容纳的数据量也是有限的，太多会打不上去。

如果你对Excel足够了解，就会知道下面这样设置数据有效性下拉列表是不会有长度限制的，因为它是利用公式读入，而不是直接写入选项。

直接输入来源公式或者点击向上的箭头选择需要作为下拉列表选项的数据

最后点击确定就设置好了。

思路：新建一个隐藏的sheet，将下拉列表的选项写入隐藏sheet中，再利用公式将隐藏sheet中的数据设置为下拉列表选项

代码如下所示，数据量绝对超过255个ASCLL码了

package cn.waterch.partyconstruction.util;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.io.File;
import java.io.FileOutputStream;

public class Test {
    public static void main(String[] args) {

        //文件路径
        String path = "D://test/origin-template.xls";
        File originTemplateFile = new File(path);
        //通过原始模板父路径和新的文件名得到新模板File
        File templateFile = new File(originTemplateFile.getParent(), "party-template.xls");
        //如果新的模板文件已存在则删除（上次操作导致）
        if (templateFile.exists()) {
            templateFile.delete();
        }

        try {
            //将原始模板文件数据复制到新的模板文件
            FileUtils.copyFile(originTemplateFile, templateFile);
            //读取新的模板excel文件,考虑到兼容性，使用Excel 2007之前的格式
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(FileUtils.openInputStream(templateFile));
            //获取系统中的所有党支部名称
//                List<String> branchList = branchService.getBranchNameTotal();
            String[] departments = new String[]{"河南益高教育咨询有限公司", "河南逸云装饰工程有限公司",
                    "开封市心意诚物业管理有限公司", "河南逸云建筑劳务有限公司", "河南佳禾兴商贸有限公司",
                    "河南省文睿建筑工程有限公司", "开封市鑫荣建筑设备租赁有限公司", "开封市汇福特种工程有限公司",
                    "开封市博威建筑劳务有限公司", "河南万方对外经济技术合作有限公司", "河南省乐享文化传媒有限公司",
                    "开封嘉禾汽车销售有限公司", "河南成长格教育咨询有限公司", "河南点对点文化传播有限公司",
                    "河南昱太绿色建筑科技有限公司", "开封市大鹏房地产开发有限公司", "开封新世纪建筑节能工程有限公司",
                    "河南超之前建筑材料有限公司", "河南钰树汽车贸易有限公司", "开封市优责汽车保养服务有限公司",
                    "开封旭明网络科技有限公司", "河南赛意软件技术有限公司"};
            //创建一个隐藏的sheet保存党支部下拉列表，直接设置数据有效性会报
            //String literals in formulas can't be bigger than 255 characters ASCII错误
            HSSFSheet hiddenSheet = hssfWorkbook.createSheet("hidden");
            //将此sheet隐藏
            hssfWorkbook.setSheetHidden(hssfWorkbook.getSheetIndex(hiddenSheet), true);
            //为隐藏的sheet写入数据，用作下拉列表项内容
            for (int i = 0; i < departments.length; i++) {
                HSSFRow row = hiddenSheet.createRow(i);
                HSSFCell cell = row.createCell(0);
                cell.setCellValue(departments[i]);
            }

            //读取工作表sheet
            HSSFSheet sheet = hssfWorkbook.getSheetAt(0);
            //第一行需要格式化的单元格
            int firstRowNum = 6;
            //最后一行需要格式化的单元格，默认格式化约200行
            int lastRowNum = 200;
            //第6列，也就是每一行党支部的那一列都需要进行格式化
            int colume = 6;

            //需要进行格式化的范围
            CellRangeAddressList regions = new CellRangeAddressList(firstRowNum, lastRowNum, colume, colume);
            //拼接数据有效性公式
            String strFormula = hiddenSheet.getSheetName() + "!$A$1:$A$" + departments.length;
            //设置下拉列表值
            DVConstraint constraint =
                    DVConstraint.createFormulaListConstraint(strFormula);
            HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
            //设置提示信息
            dataValidation.createPromptBox("温馨提示", "此项禁止自行填写，请从下拉列表中选择");
            sheet.addValidationData(dataValidation);
            //读取修改后的excel输出流
            FileOutputStream stream = FileUtils.openOutputStream(templateFile);
            //写入文件
            hssfWorkbook.write(stream);
            //关闭输出流
            stream.close();
        } catch (Exception e) {
            System.out.println("更新excel党支部列失败:" + e.getMessage());
        }

    }


}

打开party-template.xls,可以看到数据有效性设置成功了。

 右键点击左下角的sheet0,选择取消隐藏，可以看到隐藏的sheet

我们写入隐藏sheet中的数据也成功了。

 你以为终于大功告成了？哼哼，还没有。

此时你点击保存会发现一个警告，大概的意思就是sheet0中引用了sheet hidden中的数据验证规则，早期版本不支持这个功能。其实我们兼容到Excel 2007已经可以了，像Excel 2003已经很难见到了。

 

 要解决这个问题，思路也是有的

思路：将数据验证下拉列表中的数据写入shee0中没有被使用的某一列，然后通过此列编写数据验证的公式，最后将此列隐藏。

这个方法我试过了，结果是sheet0中的所有信息都没了。