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 } } }