Issues in Setting formula for cell data validation - after export errors

Aspose.Cells Product Family
1

Hi


I been trying to create cascading dropdown list in excel(Aspose cells ver. 4.7.1.0).

string rangeName = this.MakeRangeName(“ReorderCDE_”);
lookupMatrixWorksheet.Build(whichColumn, reorderList, rangeName);

IValidationCollection validations = dataWorksheet.GetValidations();
int index = validations.Add();
IValidation validation = validations.GetValidation(index);

validation.Type = ValidationType.List;
validation.Operator = OperatorType.None;
validation.ShowInput = false;
validation.IgnoreBlank = true;
validation.InCellDropDown = true;
validation.AlertStyle = ValidationAlertType.Warning;
validation.ErrorMessage =Localization.GetString(“SDE_INVALID_REORDER_NUMBER”, base.cultureInfo.Name);

validation.Formula1 = string.Format("={0}",
@“IF(SUBSTITUTE(CELL(”“contents”", INDIRECT(ADDRESS(ROW(),COLUMN()-1))),""->"","""")="""", INDIRECT("“BLANK”"), INDIRECT(SUBSTITUTE(CELL("“contents”", INDIRECT(ADDRESS(ROW(),COLUMN()-1))),""->"","""")))");

And above code is exporting excel, and we support 2003 and 2007 version from our project. And i found that 2003 & 2007 showing different behaviour;
2003 Behaviour:
I can open Excel 2003 file.
Validation fomula is set, But not working
Once i have selected the cells of that row and trying to set again - formula shows up there and i can set no issues. Then it will start working.
I have not tried this file to open from MS office 2003.
I dont know what happened here.
2007 Behaviour:
I can not open excel 2007 file.
Shows up some error, dont know what happened.

Could you guys pls look into this issue, also files attached.? Pls help ASAP



2

Attaching file that is creating issues initially in 2003 or 2007 excel.

3

Hi,

Thanks for your posting and using Aspose.Cells.

Please manually create your sample Excel file with desired Data Validation using Microsoft Excel and attach it here for our reference.

We will investigate it and provide you a sample code how to achieve the same thing using Aspose.Cells.

Thanks for your cooperation.

4

Please find the manually created XLS file

5

Any workaround for this issue.?

6

Hi,

Thanks for your posting and using Aspose.Cells.

I have looked into your Excel file but I am unable to grasp it completely. Could you please guide us with step by step instructions to let us know how you created this Data Validation List with formula and how your formula is working.

Once, we understand the inner working of your file and the steps to create it using Microsoft Excel, we will be in a better position to reproduce the same thing with Aspose.Cells code.

Thanks for your cooperation.

7
Requirement:
The task was to support cascade style filtering in excel.
For example, In Excel there are three columns country, state, city. User can select the Country and based on the selection in country column the state column should be populated and based on the selection in state column, city should be populated.

Current Problem:

1. Cascade drop-down can be created manually and it is supported in Excel 2003 and later versions.
2. Aspose.DLL 4.7.1.0 is not supporting this filter to be created programmatically for Excel 2003 and 2007 versions, which is the officially supported versions of excel by aspose.
4. Need a workaround for this issue with the 4.7.1.0 version of Aspose DLL.




8

Hi,

Thanks for your posting and using Aspose.Cells.

Please see the following sample code. It creates a new worksheet and creates your desired list data validation and applies it to cell B1:B6 and saves the output in XLS and XLSX formats.

The list data validation works fine as expected. Please see the screenshot showing them working attached with this post for your reference.

I have also attached the output Excel files. I have run the sample code with the latest version: Aspose.Cells
for .NET v8.4.0.1.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\CascadeListing±+Copy.xls”;


//Open source workbook

Workbook workbook = new Workbook(filePath);


//Add new worksheet

Worksheet sh = workbook.Worksheets.Add(“Test”);


//Add values from A1:A6

sh.Cells[“A1”].PutValue(“xeroxmodel1”);

sh.Cells[“A2”].PutValue(“xeroxmodel2”);

sh.Cells[“A3”].PutValue(“xeroxmodel1”);

sh.Cells[“A4”].PutValue(“xeroxmodel2”);

sh.Cells[“A5”].PutValue(“xeroxmodel1”);

sh.Cells[“A6”].PutValue(“xeroxmodel2”);


//Apply List Data Validation on B1:B6

CellArea area= CellArea.CreateCellArea(“B1”, “B6”);


int idx = sh.Validations.Add(area);


Validation v = sh.Validations[idx];

v.Formula1 = “=IF(SUBSTITUTE(CELL("contents",INDIRECT(ADDRESS(ROW(),COLUMN()-1)))," → ","")="",INDIRECT("BLANK"),INDIRECT(SUBSTITUTE(CELL("contents",INDIRECT(ADDRESS(ROW(),COLUMN()-1)))," → ","")))”;

v.Type = ValidationType.List;


//Autofit columns

sh.AutoFitColumns();


//Save in XLS and XLSX formats

workbook.Save(“output.xls”);

workbook.Save(“output.xlsx”);

9
It has the same issue in 2003 Excel when i exported. It wont work initially when i open the excel. But its working perfectly fine- after i set the validation again and save. Attached the file here.

(Aspose.cells 4.7.1.0)
10

Same issues exists for 2007 also. Can you pls check this issue, i have attached the 2007 excel file hereby.



(Aspose.cells 4.7.1.0)
11

Hi,

Thanks for your posting and using Aspose.Cells.

Please use the latest version: Aspose.Cells
for .NET v8.4.0.1 because we only fix issues if they are found in recent versions. Please use the formula with slash as escape characters. It is working fine as I have tested it and shown you in the above post.

If your issue still occurs with the latest version, then let us know your sample code and provide us any source excel file used in your code.

Also, you can load your source excel file yourself and observe the values or formulas using debug/watch windows etc.

12

Could you please check with 4.7.1.0…?

Since it seems like not supportingin that version, Only Aspose can help me. Could you please help me .?
13

Hi,

Thanks for using Aspose.Cells.

Do you want me to try the code I have given in my above post with the older version?

14

Yes, Since i have no choice to upgrade aspose.cells to latest version, i need help to achieve my requirement using 4.7.1.0 version.

Could you please try with 4.7.1.0 version.
15

Hi,

Thanks for using Aspose.Cells.

I have tested this issue with the following sample code using the older version (4.7.1.0) and found that the older version contains a bug.

I have attached the output xls and xlsx files generated by it. In xls output, when you click data validation and click ok, it starts working, the same problem you faced earlier. And xlsx output is corrupt and does not open in Microsoft Excel.

So you have no choice but to use the latest version which works fine without any issue.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\CascadeListing±+Copy.xls”;


//Open source workbook

Workbook workbook = new Workbook();

workbook.Open(filePath);


//Add new worksheet

Worksheet sh = workbook.Worksheets.Add(“Test”);


//Add values from A1:A6

sh.Cells[“A1”].PutValue(“xeroxmodel1”);

sh.Cells[“A2”].PutValue(“xeroxmodel2”);

sh.Cells[“A3”].PutValue(“xeroxmodel1”);

sh.Cells[“A4”].PutValue(“xeroxmodel2”);

sh.Cells[“A5”].PutValue(“xeroxmodel1”);

sh.Cells[“A6”].PutValue(“xeroxmodel2”);


//Apply List Data Validation on B1:B6

CellArea area = new CellArea();

area.StartRow = 0;

area.EndRow = 5;

area.StartColumn = 1;

area.EndColumn = 1;


int idx = sh.Validations.Add();

Validation v = sh.Validations[idx];

v.AreaList.Add(area);

v.Formula1 = “=IF(SUBSTITUTE(CELL(“contents”,INDIRECT(ADDRESS(ROW(),COLUMN()-1))),”->","")="",INDIRECT(“BLANK”),INDIRECT(SUBSTITUTE(CELL(“contents”,INDIRECT(ADDRESS(ROW(),COLUMN()-1))),"->","")))";

v.Type = ValidationType.List;


//Autofit columns

sh.AutoFitColumns();


//Save in XLS and XLSX formats

workbook.Save(“output.xls”);

workbook.Save(“output.xlsx”);

16

Thank You Very Much Shakeel Faiz.

17

Thank You Very Much,