apache POI: dataValidation (or style) for entire column, except for the header row?

Question

For the CellRange we can pass -1 for both the start/end row parameters to apply styles and dataValidators to the entire column.

But how to skip the header?

The ideal solution would be a CellRangeAddressList created with "A1:A$", but it only have int constructors.

i tried assuming that -1 is a special value that means something special, but CellRangeAddressList(1, -1, ...) fails with a "start row > finish row" error. Then I also tried assuming -1 meant last cell, but going from last to 1 CellRangeAddressList(-1, 1, ...) resulted in no cell selected.

Lastly I tried to remove the first row from the CellRangeAddressList(-1, -1, ...) but it is not possible to manipulate the ranges after creation as far as I could tell from the docs.

Axel Richter · Accepted Answer · 2019-01-18T13:36:00.833

Creating a CellRangeAddress for whole column except first row means a CellRangeAddress starts on row 2 and goes up to maximum rows count. This depends on SpreadsheetVersion. In EXCEL2007 the maximum rows count is 2^20 = 1048576. In EXCEL97 the maximum rows count is 2^16 = 65536.

Using SpreadsheetVersion we can get that different maximum rows count dependent on SpreadsheetVersion.

Example:

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.SpreadsheetVersion;

class CreateCellRangeAddressList {

 public static void main(String[] args) throws Exception {

  //Workbook workbook = new XSSFWorkbook(); 
  Workbook workbook = new HSSFWorkbook(); 

// ...

  int lastRow = workbook.getSpreadsheetVersion().getLastRowIndex();

  CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(
   1, // row 2
   lastRow, 
   2, // column C
   2);

System.out.println(cellRangeAddressList.getCellRangeAddress(0)); 
//C2:C1048576 or C2:C65536 dependent on SpreadsheetVersion

// ...

 }
}

Because the question was about data validation for whole column except first row let's have a example for this.

import java.io.FileOutputStream;

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

class CreateExcelDataValidationListsWholeColumn {

 public static void main(String[] args) throws Exception {

  //Workbook workbook = new HSSFWorkbook();
  Workbook workbook = new XSSFWorkbook();

  Sheet sheet = workbook.createSheet("Sheet1");

  sheet.createRow(0).createCell(1).setCellValue("col2Head");

  //data validation in column B, except first row:
  DataValidationHelper dvHelper = sheet.getDataValidationHelper();
  DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(new String[]{"X", "Y"}) ;
  int lastRow = workbook.getSpreadsheetVersion().getLastRowIndex();
  CellRangeAddressList addressList = new CellRangeAddressList(1, lastRow, 1, 1); //B2:B1048576      
  DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
  sheet.addValidationData(validation); // data validation for B2:B1048576 

  FileOutputStream out = null;
  if (workbook instanceof HSSFWorkbook) {
   out = new FileOutputStream("CreateExcelDataValidationListsWholeColumn.xls");
  } else if (workbook instanceof XSSFWorkbook) {
   out = new FileOutputStream("CreateExcelDataValidationListsWholeColumn.xlsx");
  }
  workbook.write(out);
  workbook.close();
  out.close();

 }
}

This results in sheet XML as follows:

<worksheet>
 <dimension ref="B1"/>
 <sheetViews>
  <sheetView workbookViewId="0" tabSelected="true"/>
 </sheetViews>
 <sheetFormatPr defaultRowHeight="15.0"/>
 <sheetData>
  <row r="1"><c r="B1" t="s"><v>0</v></c></row>
 </sheetData>
 <dataValidations count="1">
  <dataValidation type="list" sqref="B2:B1048576" allowBlank="true" errorStyle="stop">
    <formula1>"X,Y"</formula1>
  </dataValidation>
 </dataValidations>
 <pageMargins bottom="0.75" footer="0.3" header="0.3" left="0.7" right="0.7" top="0.75"/>
</worksheet>

And using HSSFWorkbook the resulting CreateExcelDataValidationListsWholeColumn.xls is 4 KByte in size.

But this will result in a spreadsheet with thousands of lines. while range(-1,-1...) results in all the visible rows carrying the dataValidation and adding as user add new ones, without starting with 65k rows. — Gabriel, Jan 18 '19 at 03:30
@Gabriel: "will result in a spreadsheet with thousands of lines": No, not necessarily. Why do you think so? See my added example for data validation in `B2:B1048576 `. — Axel Richter, Jan 18 '19 at 05:13

apache POI: dataValidation (or style) for entire column, except for the header row?

1 Answers1