private unsafe static int TryExcelImpl4(int xlFunction, out object result, params object[] parameters)
{
int xlReturn;
// Set up the memory to hold the result from the call
XlOper resultOper = new XlOper();
resultOper.xlType = XlType.XlTypeEmpty;
XlOper *pResultOper = &resultOper; // No need to pin for local struct
// Special kind of ObjectArrayMarshaler for the parameters (rank 1)
using (XlObjectArrayMarshaler paramMarshaler = new XlObjectArrayMarshaler(1, true))
{
XlOper **ppOperParameters = (XlOper **)paramMarshaler.MarshalManagedToNative(parameters);
xlReturn = Excel4v(xlFunction, pResultOper, parameters.Length, ppOperParameters);
}
// pResultOper now holds the result of the evaluated function
// Get ObjectMarshaler for the return value
ICustomMarshaler m = XlObjectMarshaler.GetInstance("");
result = m.MarshalNativeToManaged((IntPtr)pResultOper);
// And free any memory allocated by Excel
Excel4v(xlFree, (XlOper *)IntPtr.Zero, 1, &pResultOper);
return(xlReturn);
}
public static string Address([ExcelReference] XlOper o)
{
ExcelThread xlt = ExcelThread.Current;
var ret = xlt.Call(Excel.Functions.GetCell, 1, o);
return(ret.AsString());
}
public unsafe static uint GetCurrentSheetId4()
{
XlOper SRef = new XlOper();
SRef.xlType = XlType.XlTypeSReference;
//SRef.srefValue.Count = 1;
//SRef.srefValue.Reference.RowFirst = 1;
//SRef.srefValue.Reference.RowLast = 1;
//SRef.srefValue.Reference.ColumnFirst = 1;
//SRef.srefValue.Reference.ColumnLast = 1;
XlOper resultOper = new XlOper();
XlOper *pResultOper = &resultOper;
XlOper * pSRef = &SRef;
XlOper **ppSRef = &(pSRef);
int xlReturn;
xlReturn = Excel4v(xlSheetNm, pResultOper, 1, ppSRef);
if (xlReturn == 0)
{
XlOper ResultRef = new XlOper();
xlReturn = Excel4v(xlSheetId, &ResultRef, 1, (XlOper **)&(pResultOper));
if (xlReturn == 0 && ResultRef.xlType == XlType.XlTypeReference)
{
return(ResultRef.refValue.SheetId);
}
}
return(0);
}
public static bool Contains(
[ExcelDescription("The one-dimensional array to search.")]
Array array,
[ExcelDescription("The value to locate in array.")]
[NoRef] XlOper value)
{
return(((IList)array).Contains(value.Value));
}
public static XlOper ToXlOper <T>(this T a)
{
if (a == null)
{
return(Excel.Error.NA);
}
if (XlOper.CanConvertFrom(a.GetType()))
{
return(XlOper.ConvertFrom(a));
}
return(Excel.Error.NA);
}
public unsafe static IntPtr GetCurrentSheetId4()
{
// In a macro type function, xlSheetNm seems to return the Active sheet instead of the Current sheet.
// So we first try to get the Current sheet from the caller.
IntPtr retval = GetCallerSheetId4();
if (retval != IntPtr.Zero)
{
return(retval);
}
// Else we try the old way.
XlOper SRef = new XlOper();
SRef.xlType = XlType.XlTypeSReference;
//SRef.srefValue.Count = 1;
//SRef.srefValue.Reference.RowFirst = 1;
//SRef.srefValue.Reference.RowLast = 1;
//SRef.srefValue.Reference.ColumnFirst = 1;
//SRef.srefValue.Reference.ColumnLast = 1;
XlOper resultOper = new XlOper();
XlOper *pResultOper = &resultOper;
XlOper * pSRef = &SRef;
XlOper **ppSRef = &(pSRef);
int xlReturn;
xlReturn = Excel4v(xlSheetNm, pResultOper, 1, ppSRef);
if (xlReturn == 0)
{
XlOper resultRef = new XlOper();
XlOper *pResultRef = &resultRef;
xlReturn = Excel4v(xlSheetId, pResultRef, 1, (XlOper **)&(pResultOper));
// Done with pResultOper - Free
Excel4v(xlFree, (XlOper *)IntPtr.Zero, 1, &pResultOper);
if (xlReturn == 0)
{
if (resultRef.xlType == XlType.XlTypeReference)
{
return(resultRef.refValue.SheetId);
}
// Done with ResultRef - Free it too
Excel4v(xlFree, (XlOper *)IntPtr.Zero, 1, &pResultRef);
}
}
return(retval);
}
public static XlOper[,] ToXlOper <T, T2>(this IEnumerable <KeyValuePair <T, T2> > a)
{
if (a == null)
{
return(null);
}
int n = a.Count();
if (n == 0)
{
return(null);
}
XlOper[,] ret = new XlOper[n, 2];
a.ForEach((x, i) => { ret[i, 0] = x.Key.ToXlOper(); ret[i, 1] = x.Value.ToXlOper(); });
return(ret);
}
public static int IndexOf(
[ExcelDescription("The one-dimensional array to search.")]
Array array,
[ExcelDescription("The value to locate in array.")]
[NoRef] XlOper value,
[ExcelDescription("The starting index of the search. Defaults to 0.")]
int startIndex = 0,
[ExcelDescription("The number of elements in the section to search. Defaults to all alements beginning with startIndex.")]
int count = int.MaxValue)
{
if (count == int.MaxValue)
{
count = array.Length - startIndex;
}
return(Array.IndexOf(array, value.Value, startIndex, count));
}
public static Array Expand(Array a, int rowOffset = 0, int colOffset = 1, string refTxt = null)
{
ExcelThread xlt = ExcelThread.Current;
XlOper o = refTxt == null || refTxt == "" ? xlt.GetCaller() : xlt.Call(Excel.Functions.Indirect, refTxt);
var des = xlt.Call(Excel.Functions.Offset, o, rowOffset, colOffset, a.GetLength(0), a.GetLength(1));
Excel.AsyncRangeUpdate(xlt.RefText(des, false), a);
return(a);
//var app = Excel.Application;
//app.GetType().InvokeMember("Run", BindingFlags.InvokeMethod, null, app, new object[] { "QDefineRangeName", "hh" });
//object workbook = app.GetType().InvokeMember("ActiveWorkbook", BindingFlags.GetProperty, null, app, null);
//object properties = app.GetType().InvokeMember("BuiltinDocumentProperties", BindingFlags.GetProperty, null, workbook.GetType(), null);
//object property = app.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, properties, new object[] { "Author" });
//object value = app.GetType().InvokeMember("Value", BindingFlags.GetProperty, null, property, null);
//xlt.CallPrivileged(Excel.Commands.DefineName, "hehe", "$A$2");
//xlt.CallUDF("QDefineRangeName", "hh");
}
public unsafe static IntPtr GetCallerSheetId4()
{
IntPtr retval = IntPtr.Zero;
XlOper resultOper = new XlOper();
XlOper* pResultOper = &resultOper;
int xlReturn;
xlReturn = Excel4v(xlfCaller, pResultOper, 0, (XlOper**)IntPtr.Zero);
if (xlReturn == 0)
{
if (resultOper.xlType == XlType.XlTypeReference)
{
retval = resultOper.refValue.SheetId;
Excel4v(xlFree, (XlOper*)IntPtr.Zero, 1, &pResultOper);
}
}
return retval;
}
public static int LastIndexOf(
[ExcelDescription("The one-dimensional array to search.")]
Array array,
[ExcelDescription("The value to locate in array.")]
[NoRef] XlOper value,
[ExcelDescription("The starting index of the backward search. Defaults to the last element.")]
int startIndex = int.MaxValue,
[ExcelDescription("The number of elements in the section to search. Defaults to all elements from 0 to startIndex.")]
int count = int.MaxValue)
{
if (startIndex == int.MaxValue)
{
startIndex = array.Length - 1;
}
if (count == int.MaxValue)
{
count = array.Length - startIndex;
}
return(Array.LastIndexOf(array, value.Value, startIndex, count));
}
static void ComplexFormulas(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat, new XlFormulaParser());
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
// Create worksheet columns and set their widths.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 200;
}
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 50;
}
for (int i = 0; i < 2; i++)
{
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 80;
column.Formatting = new XlCellFormatting();
column.Formatting.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
}
}
// Specify formatting settings for the header row.
XlCellFormatting headerRowFormatting = new XlCellFormatting();
headerRowFormatting.Font = XlFont.BodyFont();
headerRowFormatting.Font.Bold = true;
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0));
// Specify formatting settings for the total row.
XlCellFormatting totalRowFormatting = new XlCellFormatting();
totalRowFormatting.Font = XlFont.BodyFont();
totalRowFormatting.Font.Bold = true;
// Generate data for the document.
string[] header = new string[] { "Description", "QTY", "Price", "Amount" };
string[] product = new string[] { "Camembert Pierrot", "Gorgonzola Telino", "Mascarpone Fabioli", "Mozzarella di Giovanni" };
int[] qty = new int[] { 12, 15, 25, 10 };
double[] price = new double[] { 23.25, 15.50, 12.99, 8.95 };
// Create the header row.
using (IXlRow row = sheet.CreateRow()) {
for (int i = 0; i < 4; i++)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = header[i];
cell.ApplyFormatting(headerRowFormatting);
}
}
}
#region #Formula_String
// Create data rows using string formulas.
for (int i = 0; i < 4; i++)
{
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = product[i];
}
using (IXlCell cell = row.CreateCell()) {
cell.Value = qty[i];
}
using (IXlCell cell = row.CreateCell()) {
cell.Value = price[i];
}
using (IXlCell cell = row.CreateCell()) {
// Set the formula to calculate the amount per product.
cell.SetFormula(String.Format("B{0}*C{0}", i + 2));
}
}
}
#endregion #Formula_String
#region #Formula_IXlFormulaParameter
// Create the total row using IXlFormulaParameter.
using (IXlRow row = sheet.CreateRow()) {
row.SkipCells(2);
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Total:";
cell.ApplyFormatting(totalRowFormatting);
}
using (IXlCell cell = row.CreateCell()) {
// Set the formula to calculate the total amount plus 10 handling fee.
// =SUM($D$2:$D$5)+10
IXlFormulaParameter const10 = XlFunc.Param(10);
IXlFormulaParameter sumAmountFunction = XlFunc.Sum(XlCellRange.FromLTRB(cell.ColumnIndex, 1, cell.ColumnIndex, row.RowIndex - 1).AsAbsolute());
cell.SetFormula(XlOper.Add(sumAmountFunction, const10));
cell.ApplyFormatting(totalRowFormatting);
}
}
#endregion #Formula_IXlFormulaParameter
#region #Formula_XlExpression
// Create a formula using XlExpression.
using (IXlRow row = sheet.CreateRow()) {
row.SkipCells(2);
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Mean value:";
cell.ApplyFormatting(totalRowFormatting);
}
using (IXlCell cell = row.CreateCell()) {
// Set the formula to calculate the mean value.
// =$D$6/4
XlExpression expression = new XlExpression();
expression.Add(new XlPtgRef(new XlCellPosition(cell.ColumnIndex, row.RowIndex - 1, XlPositionType.Absolute, XlPositionType.Absolute)));
expression.Add(new XlPtgInt(row.RowIndex - 2));
expression.Add(new XlPtgBinaryOperator(XlPtgTypeCode.Div));
cell.SetFormula(expression);
cell.ApplyFormatting(totalRowFormatting);
}
}
#endregion #Formula_XlExpression
}
}
}
