Reply to topic  [ 8 posts ] 
Calculate formulas 
Author Message
Reply with quote
Post 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?


Thu Jun 01, 2006 9:59 pm

Joined: Sat Apr 22, 2006 10:20 am
Posts: 5
Reply with quote
Post 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");


Fri Jun 02, 2006 11:23 am
Profile WWW
Reply with quote
Post 
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?


Wed Aug 22, 2007 2:15 am

Joined: Fri Aug 24, 2007 5:58 am
Posts: 5
Reply with quote
Post 
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.


Fri Aug 24, 2007 8:32 am
Profile
Reply with quote
Post 
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!


Wed Aug 29, 2007 2:47 am

Joined: Fri Aug 24, 2007 5:58 am
Posts: 5
Reply with quote
Post 
Please use "."(dot) instead of "," (comma) for floats. This should solve the problem.

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


Wed Aug 29, 2007 5:34 am
Profile
Reply with quote
Post 
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]


Wed Aug 29, 2007 7:24 am

Joined: Fri Aug 24, 2007 5:58 am
Posts: 5
Reply with quote
Post 
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.


Fri Aug 31, 2007 5:41 am
Profile
Display posts from previous:  Sort by  
Reply to topic   [ 8 posts ] 

Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group.
Designed by STSoftware for PTF.