Dropdown list issue

Aspose.Cells Product Family
1

We are using ASPOSE.CELLS DLL (Version: 17.8.0.0) for generating EXCEL Document in our application.
Over there we are facing issues in the below scenario,
Whenever we are adding any number of sheets in a workbook and are using dropdown list with
data source provide from workbook sheet itself.
Example:
Source sheet is sheet1

  1. sheet2 dropdown list bind to from sheet1 data values range is A1:A3
  2. sheet3 dropdown list bind to from sheet1 data values range is A4:A7
  3. sheet4 dropdown list bind to from sheet1 data values range is A8:A15
    and so on.

Whenever save workbook into a MemoryStream object it changes a formula value (please refer below screen shorts). Below is the C# code for the same.
workbook.Save(ms, saveFormat);
Before:
before1.PNG (11.1 KB)

After:
after1.PNG (15.5 KB)

We are only getting values of dropdown list displayed in the last sheet.
Can you please provide us help for the above scenario to fix the issue.

2

@gajananjoshi,

Thanks for the details and screenshots.

Please try our latest version/fix: Aspose.Cells for .NET v18.2.6:

If you still find the issue with v18.2.6, kindly do create a sample console application (runnable), zip the project (excluding Aspose.Cells assembly) and post us here to reproduce the issue, we will check it soon. Also attach your template files (input, output, etc.), this will help us really to evaluate your issue precisely to consequently figure it out soon.

3

Thanks for the quick response

As suggested by you we have changed the reference of our projects to the new aspose dll (18.6.2.0).
However even after doing that we are still facing the same issue.
We have attached the code which is currently referencing that aspose dll.
dropdownerror.pdf (121.6 KB)

4

@gajananjoshi,

Thanks for the sample code.

I debug your code (after adding it into a separate console application) by adding break points and quick watches at certain locations to check different objects/variables, etc., in the code segments. The ranges are fine tuned I think. As you are inserting “one”, “two” and “three” values repeatedly into “hidesheet” A column cells. When first iteration is done, “one”, “two” and “three” values are inserted into A1,
A2 and A3 cells respectively —> it gives the List data validation formula as “hidesheet!A1:A3” which is correct. Mind you you have used the code segment:
i.e.,
int starCell = sheet1.Cells.MaxRow + 1;
string scn = sheet1.Cells[starCell, 0].Name;
…
When next iteration is done, as you are using MaxRow (it always gives last row index which has data/formatting) in your code/logic, so next list validation formula would be rightly as “hidesheet!A4:A6” (after again inserting “one”, “two” and “three” values starting from A4,
to A6 cells respectively.). Similarly in the last iteration (for s3 sheet), list validation formula would be rightly as “hidesheet!A7:A9” (after again inserting “one”, “two” and “three” values starting from A7 to A9 cells respectively).

What’s wrong with it? Could you elaborate and provide more details and screenshots, etc. to evaluate your issue on our end. Also, you are adding and inserting each sheet in memory streams but in the same workbook.

Moreover, your code or logic is not right when using it in licensed mode (when setting a valid license at the start of the code). Since when using the API in licensed mode, there would not be any evaluation watermark sheet (created), so could you update your code segment and logic so we could run it with a valid license.

5

Hi Amjad_Sahi,

Thank you for the quick response.

From the code that we have attached previously, we ran it from the both dll

  1. Aspose ddl – 17.8.0.0 (old)
  2. Aspose ddl – 18.2.6.0 (New)

Using old dll we are able to replicate the issue (whose snapshot we have already attached in our first comment ) but with new dll we are getting license watermark ” Evaluation Only. Created with Aspose.Cells for .NET.Copyright 2003 - 2017 Aspose Pty Ltd.”.
We request you please run the code which we have provided, using both dll references from your side.
Also provide us a dll which will not have the watermark issue.

6

@gajananjoshi,

I found the issue you have described in your first post with older version, i.e., Aspose.Cells for .NET v17.8. When I used the latest version (without setting a valid license) if works differently. Please find attached the output file produced by using your code segment with v18.3. You may check the output if it has an issue, looks so. Moreover, details is already provided in my previous reply when using your code with a valid license. I guess your code or logic you shared in your PDF is not right when using it in the licensed mode. Since in licensed mode, there would not be any extra evaluation watermark sheet (created/added), so, I am getting “Index was out of range. Must be non-negative and less than the size of the collection” on the line of code:

Worksheet objws = objwb.Worksheets[rowId];

so could you update your code segment and logic so we could run it with a valid license to evaluate your issue further with latest version.
file1.zip (8.0 KB)

PS. Evaluation watermark will always embed as you are not setting a valid license in your code.

7

Hi @Amjad_Sahi,

Thanks for dll issue solution it’s working fine.

Thanks,
Gajanan Joshi.

8

@gajananjoshi,

Good to know that newer version sorts out your issue. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.