1

In my current project I need to create an excel file with a list validation on an entire column. Googling turned up with the following two results:

http://www.aspose.com/docs/display/cellsnet/Working+with+Validations+in+Columns
This refers to aspose.cells.griddesktop which actually has the worksheet.Columns[n].Validations property. Aspose.Cells doesn't.

http://www.aspose.com/docs/display/cellsjava/Data+Filtering+and+Validation All the examples use a CellArea which requires a start- and end row.

Anything I missed?

Boris Callens
  • 90,659
  • 85
  • 207
  • 305

2 Answers2

3

There are two types of excel formats. One is older XLS format and other is newer XLSX format. The number of rows inside the column in XLS format is 65536 and in XLSX format is 1048576. So you can use the above two values to cover your entire column in the CellArea.

You can also use CellArea.CreateCellArea() static method to create cell area object easily

For XLS format, the following CellArea code covers entire column A

CellArea ca = CellArea.CreateCellArea("A1", "A65536");

For XLSX format, the following CellArea code covers entire column A

CellArea ca = CellArea.CreateCellArea("A1", "A1048576");

Note: I am working as Developer Evangelist at Aspose

shakeel
  • 1,717
  • 10
  • 14
  • Can you comment on why the method and API is different for griddesktop? Is this a technical constraint? – Boris Callens Sep 09 '15 at 07:48
  • Yes, it is technical constraint. Also GridDesktop APIs are constantly improving and we are trying to make it similar to Aspose.Cells. It might take few months to make them consistent with Aspose.Cells. – shakeel Sep 09 '15 at 09:57
  • 1
    @shakeel It would be nice if the library exposed this value (65536 or 1048576) via a property. Let's say: `workbook.MaxSheetRowsNumber`. Just an idea... ;) – user2173353 Jun 20 '18 at 05:36
  • @user2173353, I think, it is reasonable so I logged a ticket for it. It is CELLSNET-46185. Once, we will have some news for you, we will update you asap. – shakeel Jun 20 '18 at 08:25
  • @BorisCallens, there is another way to cover entire column, it is discussed in my second reply. You may like to check it. Thank you. – shakeel Jun 20 '18 at 08:26
  • We have added WorkbookSettings.MaxRow and WorkbookSettings.MaxColumn properties in next version i.e. 18.7 - July 2018 release. Thanks. – shakeel Jun 21 '18 at 15:06
  • Hot FIx can be downloaded from these links. ---- https://www.dropbox.com/s/uhjk63d52fii4vj/Aspose.Cells18.6.1%20For%20.Net4.0.Zip?dl=0 ---- https://www.dropbox.com/s/bb37pu35myn5pu5/Aspose.Cells18.6.1%20For%20.Net2_AuthenticodeSigned.Zip?dl=0 ---- https://www.dropbox.com/s/rke1muvzhrow2xi/Aspose.Cells18.6.1%20For%20.NetStandard20.Zip?dl=0 --- Thanks. – shakeel Jun 22 '18 at 09:05
3

Here is another way to cover entire column.

// Cover entire column A
CellArea ca = CellArea.CreateCellArea("A", "A");

This will work with both XLS and XLSX format.

Please see the following sample code, execute it at your end and also read its comments. You will get two output Excel files. One in XLS format and other in XLSX format.

Now enter 200 (or any value greater than 100) in these cells and you will get validation error.

  • A65536
  • A1048576

C#

// Create workbook
Workbook workbook = new Workbook();

// Accessing the Validations collection of the worksheet
ValidationCollection validations = workbook.Worksheets[0].Validations;

// Cover entire column A
CellArea ca = CellArea.CreateCellArea("A", "A");

// Creating a Validation object
Validation validation = validations[validations.Add(ca)];

// Setting the validation type to whole number
validation.Type = ValidationType.WholeNumber;

// Setting the operator for validation to Between
validation.Operator = OperatorType.Between;

// Setting the minimum value for the validation
validation.Formula1 = "10";

// Setting the maximum value for the validation
validation.Formula2 = "100";

// Save in XLS format
workbook.Save("output.xls", SaveFormat.Excel97To2003);

// Remove the area of validation and add it again
validation.RemoveArea(ca);
validation.AddArea(ca);

// Save in XLSX format
workbook.Save("output.xlsx");

Note: I am working as Developer Advocate at Aspose

shakeel
  • 1,717
  • 10
  • 14