We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Export to Worksheet - Make a column mandatory

Hi,


I am trying to download an excel sheet with two columns like Project Name and Project Phase.
I want the downloaded excel to restrict the Project Name not allowing blanks i.e Project Name is mandatory. Please let me know how can I achieve the functionality.

Thanks,
Varsha

We are using 8.5.1.0 version for Aspose.Cells

Hi,


I think you may try to use data validation (custom) to accomplish your task. See the following sample code (with comments) for your reference, please refer to it and you may add/update your code accordingly for your requirements.
e.g
Sample code:

Workbook workBook = new Workbook();
Worksheet workSheet = workBook.Worksheets[0];
Cells cells = workSheet.Cells;
Cell cell = cells[“A1”];
cell.PutValue(“Project Name”);

//Add data validation to the worksheet
ValidationCollection 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 value cannot be left blank”;
validation.IgnoreBlank = false;
//Add formula to check blank in the cell
validation.Formula1 = “=COUNTA(A2)>0”;
validation.ShowError = true;
//Set your desired area/range of cells where you will input data for the A column
//I set the range: A2:A11
CellArea area = new CellArea();
area.StartRow = 1;
area.EndRow = 10;
area.StartColumn = 0;
area.EndColumn = 0;
validation.AreaList.Add(area);
// Save the excel file.
workBook.Save(@“e:\test2\out1Custom_Protection1.xlsx”);

Also, see the document for your further reference on Data Validations:
http://www.aspose.com/docs/display/cellsnet/Data+Filtering+and+Validation#DataFilteringandValidation-validation

Thank you.

Thanks for the reply Amjad Sahi.


When I give all conditions provided by you, I could see the custom validation in the DataValidations of excel.But I am unable to get the alert .To be more clear, I wanted the validation to be thrown when I try to save the file with ProjectPhase value alone i.e ProjectPhase present and ProjectName missing. Here is my code:

Dim cell As Cell = Sheet.Cells(“C10”)
Dim area As CellArea
area.StartRow = 9
area.EndRow = 150
area.StartColumn = 2
area.EndColumn = 2

Dim Validations As ValidationCollection = Sheet.Validations
Dim validation As Validation = Validations(Validations.Add(area))
validation.Type = Aspose.Cells.ValidationType.Custom
validation.Operator = OperatorType.None
validation.AlertStyle = ValidationAlertType.Stop
validation.ErrorTitle = “Error”
validation.ErrorMessage = “This value cannot be left blank”
validation.IgnoreBlank = False
validation.Formula1 = “=COUNTA(C10)>0”
validation.ShowError = True
validation.AreaList.Add(area)

Please let me know if I am missing anything.
Hi,

Varsha2015:
....To be more clear, I wanted the validation to be thrown when I try to save the file with ProjectPhase value alone i.e ProjectPhase present and ProjectName missing.

How could you do this in MS Excel manually? I suspect this is not possible in MS Excel to get alert regarding data validation upon file's save. For your information, Aspose.Cells follows MS Excel standards and specifications. If something is not done in MS Excel manually, it cannot be done via Aspose.Cells APIs. If you are able to accomplish the task in MS Excel manually, kindly provide such an Excel file, we will check how to do it via Aspose.Cells APIs.

Thank you.

Hi Amjad,


If the Name is empty,when I move to next cell Phase then the validation should show up. But I see it is not happening. Can you check it.

I mean to ask, is it possible to enter the phase the value only if I have a Name. Without entering the name, I cannot go to phase. Can we atleast do something like this?


Hi,


Well, this is MS Excel’s behavior. You need to double click in the cell (where the custom data validation is applied) and then press Enter (without inserting any value) to instantiate the data validation to be evaluated for the cell, so you will get alert.

Thank you.

I knew to double click. Did you test the code you gave me? its not giving any alert. Can you please check

Hi,


Please try the code segment pasted in one of my previous reply here:
https://forum.aspose.com/t/32495
Please note, I did try the code segment using our latest version/fix: Aspose.Cells for .NET v8.8.0 which works fine.

We do not evaluate issues or fix issues in older versions. So, kindly first try my code segment (line by line), you may easily convert to VB.NET (if you want) with your older version, open the output file into MS Excel and test the validation if you get the alert (e.g when double clicking on A2 cell and press Enter (without inserting any value)).

PS. I have also attached the output file for your reference after using my code segment.

Thank you.