Excel formulas not working after writing data into excel file

Aspose.Cells Product Family
1

Hi,

I am using aspose dll to create an excel file.

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.

kindly suggest some solution.

2

check this video once, you will understand what I do after download the excel file using aspose cell.

3

@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.

4

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.

code:

string DestinationPath = @System.Configuration.ConfigurationManager.AppSettings[“UploadFiles”];
string FileName = “ResourceList” + System.DateTime.Now.ToString(“yyyyMMddHHMMssfff”) + “.xlsm”;
string Source = @System.Configuration.ConfigurationManager.AppSettings[“Resourcetemplate”];
System.IO.File.Copy(Source, DestinationPath + FileName);

            Aspose.Cells.License lic = new License();                
            Workbook workBook = new Workbook(DestinationPath + FileName);
            string workSheetName = workBook.Worksheets[0].Name;

            if (workSheetName.ToString() == "ResourceList")
            {
                Worksheet sheet1 = workBook.Worksheets[0];
                                   
                for (int i = 0; i < obj.Count; i++)
                {
                    sheet1.Cells["A" + (i + 3)].PutValue(obj[i].WBSNumber);
                    sheet1.Cells["B" + (i + 3)].PutValue(obj[i].Business);
                    sheet1.Cells["C" + (i + 3)].PutValue(obj[i].BusinessUnit);
                    sheet1.Cells["D" + (i + 3)].PutValue(obj[i].HighOrg);
                    sheet1.Cells["E" + (i + 3)].PutValue(obj[i].MidOrg);
                    sheet1.Cells["F" + (i + 3)].PutValue(obj[i].Team);
                    sheet1.Cells["G" + (i + 3)].PutValue(obj[i].RequiredSkill);
                    sheet1.Cells["H" + (i + 3)].PutValue(obj[i].FinYear);
                    sheet1.Cells["I" + (i + 3)].PutValue(obj[i].MAY);
                    sheet1.Cells["J" + (i + 3)].PutValue(obj[i].JUN);
                    sheet1.Cells["K" + (i + 3)].PutValue(obj[i].JUL);
                    sheet1.Cells["L" + (i + 3)].PutValue(obj[i].AUG);
                    sheet1.Cells["M" + (i + 3)].PutValue(obj[i].SEP);
                    sheet1.Cells["N" + (i + 3)].PutValue(obj[i].OCT);
                    sheet1.Cells["O" + (i + 3)].PutValue(obj[i].NOV);
                    sheet1.Cells["P" + (i + 3)].PutValue(obj[i].DEC);
                    sheet1.Cells["Q" + (i + 3)].PutValue(obj[i].JAN);
                    sheet1.Cells["R" + (i + 3)].PutValue(obj[i].FEB);
                    sheet1.Cells["S" + (i + 3)].PutValue(obj[i].MAR);
                    sheet1.Cells["T" + (i + 3)].PutValue(obj[i].APR);
                    sheet1.Cells["U" + (i + 3)].PutValue(obj[i].Comments, true);
                }
                                    
                workBook.Save(DestinationPath + FileName);                    
            }
5

@kallaguntaashok,

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).

6

i found some solution in your blog, now I am trying to use the dynamic dropdown to resolve my issues.

image.png (39.5 KB)

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);

above code is working with the latest version.

7

@kallaguntaashok,

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:

.........
//Create cell area C4:C5
CellArea area;
area.StartColumn = 2;
area.EndColumn = 2;
area.StartRow = 3;
area.EndRow = 4;
............