EasyXLS
http://forum.easyxls.com/

Calculate formulas
http://forum.easyxls.com/viewtopic.php?f=5&t=16		 Page 1 of 1
Author:  Andreas [ Thu Jun 01, 2006 9:59 pm ]
Post subject:  Calculate formulas
Will EasyXLS allow me to create a spreadsheet in memory, either from scratch or loaded from a file, change cell values (including formulas), and calculate the spreadsheet?
Author:  exari [ Fri Jun 02, 2006 11:23 am ]
Post subject:  Formula computation
Here is the C#.NET sample code to change cell values or formulas and to recalculate the spreadsheet:
-----------------------------
Creating an worksheet in the memory
ExcelDocument xls = new ExcelDocument();
ExcelWorksheet xlsWorksheet = new ExcelWorksheet("Sheet1");
xls.easy_addWorksheet(xlsWorksheet);
// Adding some data to the worksheet
ExcelTable xlsTable = xlsWorksheet.easy_getExcelTable();
xlsTable.easy_getCell(0,0).setValue("1");
xlsTable.easy_getCell(1,0).setValue("2");
xlsTable.easy_getCell(2,0).setValue("3");
<b>// Adding a formula
xlsTable.easy_getCell(3,0).setValue("=SUM(A1:A3)");
// Calling the method used to compute the formulas
String sError = xlsWorksheet.easy_computeFormulas(xls, true);</b>
if (sError.Length == 0)
Console.WriteLine("Formulas computed successfully");
else
Console.WriteLine("Error computing formulas! Error: " + sError);
<b>// Displaying the result of the formula
Console.WriteLine("The result of the formula entered at position A4 is: " + xlsTable.easy_getCell(3,0).getFormulaResultValue());</b>
// Writting the excel file.
xls.easy_WriteExcelFile("C:\\Samples\\FormulaResult.xls");
Author:  lelia [ Wed Aug 22, 2007 2:15 am ]
Post subject: 
I have the following problem:
This example works OK while I use integers as data (in cells A1:A3).
Then I tried to change them to floats
(
xlsTable.easy_getCell(0,0).setValue("1.5");
or
xlsTable.easy_getCell(0,0).setValue("1,5");
I tried both variants)

I got "#VALUE!" as a result of xlsTable.easy_getCell(3,0).getFormulaResultValue();. However sError was equal to string.Empty.


How can I calculate formulas on floats?
Author:  claudiu [ Fri Aug 24, 2007 8:32 am ]
Post subject: 
In case you changed the data type, you need to be sure that the datatype of the cells is NUMERIC

xlsTable.easy_getCell(0,0).setValue("1.5");
xlsTable.easy_getCell(0,0).setDataType(DataType.NUMERIC);

Please let us know if this solved your problem.
Author:  lelia [ Wed Aug 29, 2007 2:47 am ]
Post subject: 
Unfortunatelly it doesn't

Last time 1 tried the following code
Code:
       private static void TestExcel()
        {
            ExcelDocument xls = new ExcelDocument();
            ExcelWorksheet xlsWorksheet = new ExcelWorksheet("Sheet1");
            xls.easy_addWorksheet(xlsWorksheet);
            // Adding some data to the worksheet
            ExcelTable xlsTable = xlsWorksheet.easy_getExcelTable();

            xlsTable.easy_getCell(0, 0).setValue("1,5");
            xlsTable.easy_getCell(0, 0).setDataType(DataType.NUMERIC);
            xlsTable.easy_getCell(1, 0).setValue("2");
            xlsTable.easy_getCell(1, 0).setDataType(DataType.NUMERIC);
            xlsTable.easy_getCell(2, 0).setValue("3");
            xlsTable.easy_getCell(2, 0).setDataType(DataType.NUMERIC);


            // Adding a formula
            xlsTable.easy_getCell(3, 0).setValue("=SUM(A1:A3)");
            xlsTable.easy_getCell(4, 0).setValue("=A1");
            xlsTable.easy_getCell(5, 0).setValue("=A1+A2+A3");
            // Calling the method used to compute the formulas
            String sError = xlsWorksheet.easy_computeFormulas(xls, true);

            // Displaying the result of the formula
            string result1 = xlsTable.easy_getCell(3, 0).getFormulaResultValue();
            string result2 = xlsTable.easy_getCell(4, 0).getFormulaResultValue();
            string result3 = xlsTable.easy_getCell(5, 0).getFormulaResultValue();

            string message = string.Format("Error: {0}{1}A4: {2}, A5:{3}, A6:{4}", sError, Environment.NewLine, result1, result2, result3);
            Console.WriteLine(message);

            string pathTxt = @"C:\Temp\1.txt";
            File.AppendAllText(pathTxt, message);

            string pathXls = @"C:\Temp\1.xls";

            if(File.Exists(pathXls))
                File.Delete(pathXls);

            xls.easy_WriteExcelFile(pathXls);

            xls.Dispose();
        }


The result in 1.txt was the following
Quote:
Error:
A4: #NUM!, A5:NaN, A6:#VALUE!


But then I opened 1.xls everything was OK there
Quote:
1,5
2
3
6,5
1,5
6,5


Do you have any ideas why is it so in my case?

I hope you can help me to solve this problem.

PS:
if i change 1,5 to 1 in A1 cell everything is OK
The result I get in that case is
Quote:
Error:
A4: 6, A5:1, A6:6


PPS: I tried to test this functional also by opening a correct xls file.
The result was the same

Code:
       private static void TestExcelReverse()
        {
            string pathXls = @"C:\Temp\1.xls";
            ExcelDocument xls = new ExcelDocument();
            xls.easy_LoadTemplateFile(pathXls);
            ExcelWorksheet xlsWorksheet = (ExcelWorksheet) xls.easy_getSheetAt(0);
            // Adding some data to the worksheet
            ExcelTable xlsTable = xlsWorksheet.easy_getExcelTable();

            // Calling the method used to compute the formulas
            String sError = xlsWorksheet.easy_computeFormulas(xls, true);


            // Displaying the result of the formula
            string result1 = xlsTable.easy_getCell(3, 0).getFormulaResultValue();
            string result2 = xlsTable.easy_getCell(4, 0).getFormulaResultValue();
            string result3 = xlsTable.easy_getCell(5, 0).getFormulaResultValue();

            string message = string.Format("Error: {0}{1}A4: {2}, A5:{3}, A6:{4}", sError, Environment.NewLine, result1, result2, result3);
            Console.WriteLine(message);

            string pathTxt = @"C:\Temp\1.txt";
            File.AppendAllText(pathTxt, message);

            xls.Dispose();


Quote:
Error:
A4: #NUM!, A5:NaN, A6:#VALUE!
Author:  claudiu [ Wed Aug 29, 2007 5:34 am ]
Post subject: 
Please use "."(dot) instead of "," (comma) for floats. This should solve the problem.

xlsTable.easy_getCell(0,0).setValue("1.5");
Author:  lelia [ Wed Aug 29, 2007 7:24 am ]
Post subject: 
Claudiu wrote:
Please use "."(dot) instead of "," (comma) for floats. This should solve the problem.

xlsTable.easy_getCell(0,0).setValue("1.5");


On my computer default separator for floats is comma.
For example correct number in excel is 1,5.

But I tried "."
The result is

Quote:
Error:
A4: #NUM!, A5:NaN, A6:#VALUE!

Also threre is nothing!!! in cell A1 in result excel file. There is NO! exception during execution but in console window i can see the following
Quote:
Input string was not in a correct format.
....
at System.Double.Parse(String s)
.....
[/code]
Author:  claudiu [ Fri Aug 31, 2007 5:41 am ]
Post subject: 
For the moment being the only accepted format is the one with the dot as the default separator for floats.

To change the default settings for your computer go to Control Panel -> Regional Options -> Numbers.

Sorry for the inconvenience.
Page 1 of 1 All times are UTC - 4 hours
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/