Reply to topic  [ 2 posts ] 
Read formula value 
Author Message
Reply with quote
Post 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?


Tue Jun 27, 2006 7:15 pm

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


Wed Jun 28, 2006 11:48 am
Profile WWW
Display posts from previous:  Sort by  
Reply to topic   [ 2 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:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group.
Designed by STSoftware for PTF.