the first step I created a code to export data into an excel file from a database in specific format.
blank templates contain dependent dropdowns, using “PutValue” i am updating data into dropdown, but unfortunately I am unable to get all values in dropdown.
dropdown binds the data from formula, example ( =UNIQUE(OFFSET(B1,MATCH(D10,A2:A784,0),0,COUNTIF(A2:A784,D10),1)) )
these formulas not working when I do write data using aspose dll.
Once I open the excel file and if I keep my cursor on the formula and if I press the tab everything will update properly, hope you understand my problem.
@kallaguntaashok,
You may share your runnable sample console application which is used to create this Excel file. To run this application without any error here, we need the sample data to be hardcoded or placed in a file such that the sample application compiles and runs fine without any missing reference or data to create this Excel file here. We will reproduce this issue here and provide assistance at the earliest.
thank you for your response, I am attaching the excel template that I am using to fill the data.
in this excel file, you can find 2 sheets, in 1st sheet I am updating data to the dropdown list’s, 2nd sheet is master data.
you can try filling the excel manually, then everything will do well, all the dropdown will fill in the necessary information [ it will show more than one item in dropdown].
in the same excel if I add data using “putvalue”, the dropdown won’t show more than one value, it shows only a single line item.
I added my file in dropbox, please download from the above link.
I tried to open your file into MS Excel 2013 and 2016 but MS Excel prompts an error message, so the file is corrupted a bit and is not displayed/shown in MS Excel manually. Please provide us a valid MS Excel file which could be opened fine into MS Excel 2013/2016, so we could evaluate your issue precisely.
PS. please zip your valid MS Excel file and attach it here using “Upload” button (when replying to this post).
now I have new issue, I am getting an error in “CreateCellArea” and I am using the “5.3.3.0” version of aspose cell, as I have a license to his version.
is there any way that I an use CreateCellArea method in 5.3.3.0 version.
CellArea area = CellArea.CreateCellArea(“C” + (i + 3), “C” + (i + 3));
int idx = sheet1.Validations.Add(area);
Validation v = sheet1.Validations[idx];
v.Formula1 = “=OFFSET(NMaster!H1,MATCH(B” + (i + 3) + “,NMaster!G2:G8,0),0,COUNTIF(NMaster!G2:G8,B” + (i + 3) + “),1)”;
v.Type = ValidationType.List;
sheet1.Cells[“C” + (i + 3)].PutValue(obj[i].BusinessUnit);
No, you cannot use it as you are using older version of the product. CreateCellArea is newer API and to use it, you got to upgrade to newer versions of the APIs. Also, we cannot include the newer API to older versions.
I guess for your needs, you may try to create the CellArea in the following way instead:
e.g Sample code: