poi 操作 单元格

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;

/**
 * @author longrong.lang
 * @version 1.0
 * @description
 * @date 2020/9/15 9:32
 */
public class MergeCellDemo {
    public static void main(String[] args) throws IOException {
        //新建工作簿
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();

        //新建工作表
        XSSFSheet sheet = xssfWorkbook.createSheet("工作表1");
        //指定合并开始行、合并结束行 合并开始列、合并结束列
        CellRangeAddress rangeAddress = new CellRangeAddress(0, 0, 0, 1);
        //添加要合并地址到表格
        sheet.addMergedRegion(rangeAddress);
        //创建行，指定起始行号，从0开始
        XSSFRow row = sheet.createRow(0);
        //创建单元格，指定起始列号，从0开始
        XSSFCell cell = row.createCell(0);
        //设置单元格内容
        cell.setCellValue("我是合并后的单元格");
        //创建样式对象
        CellStyle style = xssfWorkbook.createCellStyle();
        //设置样式对齐方式：水平\垂直居中
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //设定填充单色
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设定背景颜色
        style.setFillForegroundColor(IndexedColors.PINK.getIndex());
        //为指定单元格设定样式
        cell.setCellStyle(style);
        FileOutputStream fileOutputStream = new FileOutputStream("d:\\MergeCellDemo.xlsx");
        xssfWorkbook.write(fileOutputStream);
        fileOutputStream.close();
//FileOutputStream fos = new FileOutputStream(new File("C:\\信息.xlsx"));
           // wb.write(fos);
            //fos.close();
           // wb.close();
    }
}

项目上最近有一个需求，需要导出的excel拥有多个数据有效性校验的功能，开始是参考了网上的使用POI为Excel添加数据有效性验证,核心代码大致如下

但是使用过程中，发现一旦有效性List超过255个字符整个Excel就会全部失效的情况，找了很久，参考了网上另外一篇poi导出excel时下拉列表值超过255问题解决方案，
核心代码如下

         XSSFSheet sheet = wb.createSheet(sheetName);
         //获取所有sheet页个数
         int sheetTotal = wb.getNumberOfSheets();
         String hiddenSheetName = "hiddenSheet" + sheetTotal;
         XSSFSheet hiddenSheet = wb.createSheet(hiddenSheetName);
         Row row;
         //写入下拉数据到新的sheet页中
         for (int i = 0; i < selectList.length; i++) {
             row = hiddenSheet.createRow(i);
             Cell cell = row.createCell(0);
             cell.setCellValue(selectList[i]);
         }
         //获取新sheet页内容
         String strFormula = hiddenSheetName + "!$A$1:$A$65535";   //hiddenSheetName + ! 定位到用来加载列的新的sheet页，后面则是A列的1-65535为有效性List条件
         XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST,strFormula);
         // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
         CellRangeAddressList regions = new CellRangeAddressList(0,65535, columnIndex, columnIndex);
         // 数据有效性对象
         DataValidationHelper help = new XSSFDataValidationHelper((XSSFSheet) sheet);
         DataValidation validation = help.createValidation(constraint, regions);
         sheet.addValidationData(validation);
         //将新建的sheet页隐藏掉
         wb.setSheetHidden(sheetTotal, true);

成功解决问题！！！

	XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
			.createExplicitListConstraint(explicitListValues);
	CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
	XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
	validation.setSuppressDropDownArrow(true);
	validation.setShowErrorBox(true);
	sheet.addValidationData(validation);