SmartXLS Software

• Dates value.
• Custom function/VBA function.

Dates value

The date cell is the value stored as numeric(double) value with date formatting in excel.

//a double value representing the number of days since 1/1/1900.
//Fractional days represent hours,minutes,and seconds.
double dd = workBook.getNumber(1, 1);//B2 is a date cell.
Console.WriteLine("days since 1900:" + dd);
String ds = workBook.getText(1,1);
//the formatted text as it is showed in Excel.
String dfs = workBook.getFormattedText(1,1);
Console.WriteLine("Formatted text:" + dfs);

//method 1 to set date value
//set cell value with formatted text(mm/dd/yy).
workBook.setEntry(3, 2, "08/08/2009");

//method 2 to set date value
//set the cell with number value.
workBook.setNumber(3,2,40033.0);
//formatting the value to date 'yyyy/mm/dd'
RangeStyle rs = workBook.getRangeStyle();
rs.CustomFormat = "yyyy/mm/dd";
workBook.setRangeStyle(rs, 3,2,3,2);

Custom function/VBA function

Custom functions are classes that extend the capabilities of the SmartXLS calculate engine. To implement an add-in function, create a class that extends SmartXLS.calc.AddinFunc.

MyDoSum.cs
namespace SmartXLS
{
public class MyDoSum : SmartXLS.calc.AddinFunc
{
const short valueTypeEmpty = 0;
const short valueTypeNumber = 1;
const short valueTypeText = 2;
const short valueTypeLogical = 3;
const short valueTypeError = 4;

const short valueErrInvalidValue = 3;

/// <summary>
/// This static initializer guarantees that the instance of this
/// function is created and added to the list of add-in functions.
/// </summary>
static MyDoSum()
{
new MyDoSum();
}

/// <summary>
/// Make this private so that only one instance is created
/// </summary>
public MyDoSum() : base("MyDoSum", 1, 30)
{
//we can allow 1-30 arguments
//any argument can be a reference (not just resolved to* a"value"
for (int i = 1; i < 30; i++)
base.ReferenceArgument = i;
}

/// <summary>
/// Override the SmartXLS.calc.AddinFunc.evaluate(...) to do the work
/// </summary>
/// <param name="fc">formula calculation context</param>
public override void evaluate(SmartXLS.calc.AddinFuncContext fc)
{
int argCount = fc.ArgumentCount;

//initialize the result
double sum = 0.0;
for (int i = 0; i < argCount; i++)
{
Argument val = fc.getArgument(i);
if (val.isCell())
{
Sheet sheet = val.Sheet;
int cellType = System.Math.Abs(sheet.getType(
val.Row1,
val.Col1));
if (cellType == valueTypeNumber)
{
sum += sheet.getNumber(val.Row1,
val.Col1);
}
else if (cellType == valueTypeError)
{
fc.setReturnValue(valueErrInvalidValue);
return;
}
}
else if (val.isRange())
{
Sheet sheet = val.Sheet;
int row2 = val.Row2;
int col2 = val.Col2;
for (int row1 = val.Row1; row1 <= row2; row1++)
{
for (int col1 = val.Col1; col1 <= col2; col1++)
{
int cellType = System.Math.Abs(sheet.getType(row1, col1));
if (cellType == valueTypeNumber)
{
sum += sheet.getNumber(row1, col1);
}
else if (cellType == valueTypeError)
{
fc.setReturnValue(valueErrInvalidValue);
return;
}
}
}
}
else if (val.isNumber())
{
sum += val.getNumber();
}
else
{
fc.setReturnValue(valueErrInvalidValue);
return;
}
}
fc.setReturnValue(sum);
}
}
}