Hi Arnard,
Thank you very much for using Syncfusion products.
Problem 1:
I have used the following code as per your requirement to display the values 1-01, 1-02.. 1-100 in the 2nd column.
for (int i = 1; i < 100; i++)
{
if(i<10)
sheet.Range[ i,2].Value = "1-0" + i.ToString();
else
sheet.Range[ i,2].Value = "1-" + i.ToString();
}
Here we can use two options.
1.sheet.Range[ i,2].Value is store the values as in Data Time format.
2.sheet.Range[ i,2].text is store the values as string in the given format.
Please use the above options at your side and let us know if these helps you.
Problem 2:
When we are calculating the formulas and the calculated values are not yet stored in the input file. So you are getting the 0 as the calculated value. Our XlsIO we only trigger the MSExcel to calculate the formulas available in the sheet, this can accessed only if it stored in the file. To avoid this we have added a runtime calculation mechanism EnableSheetCalculations() method to calculate the formula value at run time.
And for your requirement, we have the property called CalcualtedValue which returns the calculated string value for the given formula.
Use these property at your side and convert the value to the desired data format.
Code Snippet:
//Enable the Sheet Calculations to the worksheet.
sheet.EnableSheetCalculations();
//Getting the calculated Value
String tmp4 = sheet.Range["O6"].CalculatedValue;
double tmp3 = Convert.ToDouble(tmp4);
//or use the following code which do the same
double double_value = Convert.ToDouble(sheet.Range["O6"].CalculatedValue);
We have created the sample at our side to illustrate the above problems at our side and attached here for your reference, Please try the sample at your side and let us know if these helps you.
Please let us know if you require any further clarifications.
Thanks,
Sridhar.S
FormulaStringValue_4275ce8b.zip