|
View unanswered posts | View active topics
It is currently Wed Nov 27, 2024 4:37 pm
|
Page 1 of 1
|
[ 8 posts ] |
|
| Author |
Message |
|
Andreas
|
 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 |
|
|
|
exari
Joined: Sat Apr 22, 2006 10:20 am
Posts: 5
|
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 |
 
|
|
|
lelia
|
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 |
|
|
|
claudiu
Joined: Fri Aug 24, 2007 5:58 am
Posts: 5
|
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 |
|
|
|
lelia
|
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 But then I opened 1.xls everything was OK there 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 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(); | | | | |
|
| Wed Aug 29, 2007 2:47 am |
|
|
|
claudiu
Joined: Fri Aug 24, 2007 5:58 am
Posts: 5
|
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 |
|
|
|
lelia
|
On my computer default separator for floats is comma. For example correct number in excel is 1,5. But I tried "." The result is 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 [/code]
|
| Wed Aug 29, 2007 7:24 am |
|
|
|
claudiu
Joined: Fri Aug 24, 2007 5:58 am
Posts: 5
|
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 |
|
|
|
|
Page 1 of 1
|
[ 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
|
|
