Change Value in Cell and Read Excel

Hi,


I have excel with lot of sales figures and counts. On top I have cell with drop down list which contains all US 50 states. So when state is changed, data will change.

Now when someone uploads this excel in my application, i want to read excel for all 50 states one by one and i want to know how can i change state value using ASPOSE ?

Appreciate your help.

Hi,


Thanks for your posting and using Aspose.Cells.

Such operations are usually possible with Aspose.Cells. Please provide us your sample excel file which you can create manually using Microsoft Excel to look into this issue further. We will investigate it and provide you a sample code to achieve your requirements. Thanks for your cooperation in this regard and have a good day.

Thank you Shakeel.


Here is the sample file that we use. For security reasons i dont have data in it but ideas is when i change State, values will change from Row 4. $ and count.

Hi,


Thanks for considering Aspose.Cells.

To make the data change according to State, you should use formulas with reference to H3 cell for those cells and calculate formulas when State changed.

Shakeel,


I already have formulas in excel to change value when state is changed. But how do i change state using aspose cell? User will upload file and then i will have to change states one by one pragmatically. User might submit excel with any state selected, but i would like to start with very first state in list and go down to the list. How do i do that with aspose cell?

Hi,


Thanks for your posting and considering Aspose.Cells.

You can change the value of cell H3 from the dropdown list using Aspose.Cells but the values in your cells like C4 and D4 will not be changed because there is no formula inside those cells which gets affected with the value of cell H3.

So you need to first make your source excel file dynamic in such a way that when you change the value of cell H3, the value of cell C4 and D4 also gets changed. Without making it dynamic, you cannot achieve your requirement using Aspose.Cells.

Once, you will make your source excel file dynamic, then you will load it with Aspose.Cells API and change the value of cell H3 and calculate the formula and then retrieve the new values of cell C4 and D4. I have also attached the screenshot explaining these points for your reference.

Hi,


Thanks for using Aspose.Cells.

Please see my above post.

https://forum.aspose.com/t/26108

To explain it further, I have written the following sample code and also shown its console output. I have also attached the sample excel file used in this code and the output excel file generated by it for your reference.

If you look into the code, I am changing the value of cell A1 from dropdown list and whenever its value changes, it also changes the value of cell D2. This is how we can change the dropdown list value of the cell and then calculate the new values of the affected cells.

C#
//Create a workbook object
Workbook wb = new Workbook(“sample.xlsx”);
//Access first worksheet
Worksheet ws = wb.Worksheets[0];

//Access cell A1
Cell a1 = ws.Cells[“A1”];

//Get the dropdown list validation
Validation val = a1.GetValidation();
object[] values = val.Value1 as object[];


for (int ddIndex=0; ddIndex<4; ddIndex++)
{
//Change the value of cell A1 and calculate the value cell D2
string ddValue = (string)values[ddIndex];

//Put dropdown list value in cell A1
a1.PutValue(ddValue);

//Now calculate formula
wb.CalculateFormula();

//Print the value of cell D2 on console and debug
Console.WriteLine(ws.Cells[“D2”].StringValue);
Debug.WriteLine(ws.Cells[“D2”].StringValue);
}

//Save the workbook
wb.Save(“output.xlsx”);


Console Output
AR1 Welcome
AR2 Welcome
AR3 Welcome
AR4 Welcome

i have license version of 3.0. Do i have to update it latest version for this?

Hi,

Thanks for your posting and using Aspose.Cells.

If your license is years old, then please use the latest version and buy a new license. It seems version 3.0 is very old version almost more than 3 years old. So it is necessary for you to upgrade to latest version. Anyway, you can try the given code shared by me with your older version but if you get some error, then you will have to upgrade to latest version to get rid of this problem. Have a good day.

Hi,


I tried doing it and getting error at

oExcel.CalculateFormula();

Here is my code and attached error screenshot.

foreach (DataRow stateRow in stateData.Rows)
{
//Change the value of cell H# and calculate the values
string ddValue = (string)stateRow[0];

//Put dropdown list value in cell H3
H3.PutValue(ddValue);

//Now calculate formula
oExcel.CalculateFormula();

}

Hi,


Thanks for your posting and using Aspose.Cells.

Please use the latest version: Aspose.Cells for .NET (Latest Version) now to solve this issue. We are afraid, we cannot fix the bugs in older version. We always fix bug in most recent version. Thanks for your understanding and have a good day.