Set column as read only using data validation in MS Excel using C#.NET

Aspose.Cells Product Family
1

hi

I want to set a column in excel 2007 (xlsm) as read only

i have the following code which works on excel 2003.

Cells cells = sheetRef.Cells;

CellArea cellArea = new CellArea();

cellArea.StartColumn = startColumn;

cellArea.EndColumn = endColumn;

cellArea.StartRow = startRow;

cellArea.EndRow = endRow;

Validation validation = sheetRef.Validations[sheetRef.Validations.Add()];

validation.AlertStyle = ValidationAlertType.Stop;

validation.Type = ValidationType.Custom;

validation.ErrorTitle = ..

validation.ErrorMessage = ...

validation.IgnoreBlank = false;

validation.ShowInput = true;

validation.ShowError = true;

validation.AreaList.Add(cellArea);

i have a column which represent int values. when I change the value to different number

I dont get an error, but if I enter string value I get it.

do I need it to define something else?

Thanks

2

Hi,

Following code snippet will help you to validate cell value.Please refer to the direct link for more information about data validation. If still you face any problem feel free to contact us.

Code snippet:

Workbook workBook = new Workbook();
FileStream fileStream = null;
Worksheet workSheet = workBook.Worksheets[0];
Validations validations = workSheet.Validations;
//Creating a Validation object
Validation validation = validations[validations.Add()];
//Setting the custom validation type
validation.Type = Aspose.Cells.ValidationType.Custom;
validation.AlertStyle = ValidationAlertType.Stop;
validation.ErrorMessage = "This cell cannot be changed";
validation.Formula1 = "=A1";
validation.ShowError = true;
CellArea area = new CellArea();
area.StartRow =0;
area.EndRow = 0;
area.StartColumn = 0;
area.EndColumn = 0;
validation.AreaList.Add(area);
workBook.Save(@"d:\\test\\Custom_Protection.xls", FileFormatType.Excel2003);

Online documentations:

Data validation

3

Thanks

what I was missing is setting Formula1.

but can you explain what that it mean?

Does it mean all cells in the CellArea must be equel to the cell A1 (because that's not the case I get) ?

4

Hi,

If you set formula as "=A1", the formula will be different in each cell. The formula will be changed according to each cell.

If you want the formula to be same in each cell, use formula as "=$A$1". Where reference with "$" means, it's an absolute reference and reference without "$", means it is relative reference.

Note:
In case of relative reference, the change will automatically be reflected in Validation, ConditionalFormatting, SharedFormula.

Thanks,