EasyXLS
http://forum.easyxls.com/

Read formula value
http://forum.easyxls.com/viewtopic.php?f=5&t=23		 Page 1 of 1
Author:  Freja [ Tue Jun 27, 2006 7:15 pm ]
Post subject:  Read formula value
I have a complicated spreadsheet.
Several sheets and lots of formulas
It calculates for an - product.

I have to enter some values and read the result.

Can I use your product for this?
Author:  exari [ Wed Jun 28, 2006 11:48 am ]
Post subject:  Read formula value
Calculating cell formulas should be a simple task if the cells that contain formulas have the proper data type set. We implemented the formula processing in the same way Microsoft Excel does; this means that for specific functions or operations you should have proper operands (e.g. for a multiplication you should use only numeric operands).
The problem was that you loaded an excel file and after that you tried to change the cell values, but the cells data type remained the same as in the excel file (probably ?String? data type). The following code sample shows you how to handle this kind of situation:
--------------------
ExcelDocument xls = new ExcelDocument();
Console.WriteLine("DebugEasyXLSOne");
Console.WriteLine(String.Format("License: {0}, LicenceKey: {1} ",sLicense,sLicenseKey));
String sheet = "C:\\Samples\\Tutorial9.xls";
if (xls.easy_LoadTemplateFile(sheet,sLicense,sLicenseKey))
{
ExcelTable xlsFirstTable = ((ExcelWorksheet)xls.easy_getSheet("Sheet1")).easy_getExcelTable();
xlsFirstTable.easy_getCell("B1").setValue("1");
//for performance resons you have to set the data type of a cell.
//If no data type is set then the default data type of the cell is String and that cell is excluded from formulas that require numeric operands
xlsFirstTable.easy_getCell("B1").setDataType(EasyXLS.Constants.DataType.NUMERIC);
xlsFirstTable.easy_getCell("B2").setValue("1");
xlsFirstTable.easy_getCell("B2").setDataType(EasyXLS.Constants.DataType.NUMERIC);
// add a formula
<b>xlsFirstTable.easy_getCell("B3").setValue("=B1 + LEN(B2*100)");</b>
//you should set the cell data type to automatic in order to calculate the cell formula
xlsFirstTable.easy_getCell("B3").setDataType(EasyXLS.Constants.DataType.AUTOMATIC);
//calculate all formulas from the ExcelWorksheet object
<b> ((ExcelWorksheet)xls.easy_getSheet("Sheet1")).easy_computeFormulas(xls, false);
String result = xlsFirstTable.easy_getCell("B3").getFormulaResultValue();</b>
Console.WriteLine(String.Format("Result is {0}",result));
}
else
{
Console.WriteLine(String.Format("Error reading file {0}",sheet));
}
--------------------
You can also create an Excel file from scratch using EasyXLS, as in the following example:
--------------------
ExcelDocument xls = new ExcelDocument();
Console.WriteLine("DebugEasyXLSOne");
Console.WriteLine(String.Format("License: {0}, LicenceKey: {1} ",sLicense,sLicenseKey));

//create an worksheet
ExcelWorksheet xlsWorksheet = new ExcelWorksheet("Sheet Name");
//get the worksheet data table
ExcelTable xlsFirstTable = xlsWorksheet.easy_getExcelTable();
//set values
xlsFirstTable.easy_getCell("B1").setValue("1");
xlsFirstTable.easy_getCell("B1").setDataType(EasyXLS.Constants.DataType.NUMERIC);
xlsFirstTable.easy_getCell("B2").setValue("1");
xlsFirstTable.easy_getCell("B2").setDataType(EasyXLS.Constants.DataType.NUMERIC);
<b>xlsFirstTable.easy_getCell("B3").setValue("=B1 + LEN(B2*100)");</b>
// calculate formulas
<b>xlsWorksheet.easy_computeFormulas(xls, false);
String result = xlsFirstTable.easy_getCell("B3").getFormulaResultValue();</b>
Console.WriteLine(String.Format("Result is {0}",result));
//add the worksheet to the ExcelDocument
xls.easy_addWorksheet(xlsWorksheet);
//save the ExcelDocument to HDD
String sheet = "C:\\Samples\\newFile.xls";
xls.easy_WriteExcelFile(sheet, sLicense,sLicenseKey);

--------------------
Notice that in the second sample you don't have to set the formula data type because the default data type of a cell is AUTOMATIC.
Also, the formulas are calculated automatically when the ExcelDocument is written to disk so you don't really need to call xlsWorksheet.easy_computeFormulas method unless you really need the formula's result.
Page 1 of 1 All times are UTC - 4 hours
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/