When I need to populate a cell that I find using a DefinedName I should parse RefersTo to get Worksheet name. But DefinedName class contains Scope property which has CurrentWorksheet and Workbook properties (internal). It would be very useful if these properties could be public.
Add API to get the list of ranges to which a defined name refers.
Example:
List<CellRange> ranges = workbook.Names["definedName"].Ranges;
CellSelection selection = worksheet.Cells[ranges];
The cell range referred by the defined name could be accessed using code similar to the following one:
foreach (var name in workbook.Names)
{
if (name.Name == givenName)
{
string[] v = name.RefersTo.Split("!".ToCharArray());
foreach (Worksheet sheet in workbook.Sheets)
{
if (sheet.Name.ToUpper() == v[0].Replace("=", String.Empty).ToUpper())
{
string rangeName = v[1];
string firstIndexName = rangeName.Split(":".ToCharArray())[0];
int rowIndex;
int columnIndex;
bool isRowAbsolute;
bool isColumnAbsolute;
bool nameRefersToIndex = NameConverter.TryConvertCellNameToIndex(firstIndexName, out isRowAbsolute, out rowIndex, out isColumnAbsolute, out columnIndex);
if (nameRefersToIndex)
{
sheet.Cells[rowIndex, columnIndex].SetValue(givenValue);
}
}
}
}
}
Add support for the AVERAGEIF function: https://support.office.com/en-us/article/AVERAGEIF-function-faec8e2e-0dec-4308-af69-f5576d8ac642 Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria: AVERAGEIF(range, criteria, [average_range]) Workaround: This could be implemented as a custom function: http://docs.telerik.com/devtools/document-processing/libraries/radspreadprocessing/features/formulas/custom-functions.html
Add support for the SUBTOTAL function: https://support.office.com/en-us/article/SUBTOTAL-function-7b027003-f060-4ade-9040-e478765b9939 It computes the subtotal for some ranges, given the code for the subtotal function: SUBTOTAL(function_num,ref1,[ref2],...) where function_num is code referring to some of the functions: AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR, VARP. Note that we should implement the support for all of the above functions. As of now, COUNTA, VAR, VARP are not supported. Workaround: This could be implemented as a custom function: http://docs.telerik.com/devtools/document-processing/libraries/radspreadprocessing/features/formulas/custom-functions.html , but multiple functions (4 as of now) has to be additionally implemented.
Add support for ROW function: https://support.office.com/en-us/article/ROW-function-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d?ui=en-US&rs=en-US&ad=US Examples: =ROW() returns the row in which the formula appears =ROW(C10) returns the row of the reference - 10 Workaround: This could be implemented as a custom function: http://docs.telerik.com/devtools/document-processing/libraries/radspreadprocessing/features/formulas/custom-functions.html
Implement SMALL function (https://support.office.com/en-us/article/SMALL-function-17da8222-7c82-42b2-961b-14c45384df07 ), which returns k-th smallest value from a range: SMALL(array, k) Workaround: This could be implemented as a custom function: http://docs.telerik.com/devtools/document-processing/libraries/radspreadprocessing/features/formulas/custom-functions.html
The MATCH function searches for a specified item in a range of cells and returns the relative position of the item found in this range. A list of the supported functions is available at http://docs.telerik.com/devtools/document-processing/libraries/radspreadprocessing/features/formulas/functions This function can be implemented as a custom function. Check the following resources for more details on how to achieve that: - http://docs.telerik.com/devtools/document-processing/libraries/radspreadprocessing/features/formulas/custom-functions - https://github.com/telerik/xaml-sdk/tree/master/Spreadsheet/CustomFunctions Available in R3 2017 Official Release Version
An InvalidOperationException could be thrown in multi-threaded export to XLSX format. However, the problem is not verified. Here is the stacktrace: ERROR System.InvalidOperationException: isParsing must be true. at Telerik.Windows.Documents.Spreadsheet.Utilities.Guard.ThrowExceptionIfTrue(Boolean param, String paramName) at Telerik.Windows.Documents.Spreadsheet.Model.Printing.HeaderFooterSectionTextParser.OnBeforeParse(String headerFooterSectionText) at Telerik.Windows.Documents.Spreadsheet.Model.Printing.HeaderFooterSectionTextParser.Parse(String headerFooterSectionText) at Telerik.Windows.Documents.Spreadsheet.Model.Printing.HeaderFooterSectionTextSanitizer.Sanitize(String sectionText) at Telerik.Windows.Documents.Spreadsheet.Model.Printing.HeaderFooterContent.BuildContentText() at Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.Model.Elements.Worksheets.HeaderFooterElement.WriteChild[T](OpenXmlChildElement`1 child, HeaderFooterContent content) at Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.Model.Elements.Worksheets.HeaderFooterElement.WriteChildren(HeaderFooterSettings settings) at Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.Model.Elements.Worksheets.HeaderFooterElement.OnBeforeWrite(IXlsxWorksheetExportContext context) at Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.Model.Elements.Worksheets.WorksheetElementBase.OnBeforeWrite(IXlsxWorkbookExportContext context) at Telerik.Windows.Documents.FormatProviders.OpenXml.Model.Elements.OpenXmlElementBase`3.OnBeforeWrite(IOpenXmlExportContext context) at Telerik.Windows.Documents.FormatProviders.OpenXml.Model.Elements.OpenXmlElementBase.Write(IOpenXmlWriter writer, IOpenXmlExportContext context) at Telerik.Windows.Documents.FormatProviders.OpenXml.Model.Elements.OpenXmlElementBase.Write(IOpenXmlWriter writer, IOpenXmlExportContext context) at Telerik.Windows.Documents.FormatProviders.OpenXml.Model.Parts.OpenXmlPartBase.Export(IOpenXmlWriter writer, IOpenXmlExportContext context) at Telerik.Windows.Documents.FormatProviders.OpenXml.Export.OpenXmlExporter`2.ExportPart(OpenXmlPartBase part, TContext context) at Telerik.Windows.Documents.FormatProviders.OpenXml.Export.OpenXmlExporter`2.Export(Stream output, TContext context, OpenXmlExportSettings settings) at Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.XlsxFormatProvider.ExportOverride(Workbook workbook, Stream output) at Telerik.Windows.Documents.Spreadsheet.FormatProviders.WorkbookFormatProviderBase.Export(Workbook workbook, Stream output) Available in LIB Version 2017.2.828.
There are scenarios when the default value of the columns is changed and may be different from 65. However, RadSpreadprocessing always exports the non-custom column values as 65. WORKAROUND: After importing the XLSX file you may make all columns to have custom values in order to preserve the column widths after the export. The following code snippet shows how this may be achieved for some Worksheet instance: CellRange usedRange = worksheet.UsedCellRange; for (int i = 0; i < usedRange.ColumnCount; i++) { ColumnSelection column = worksheet.Columns[i]; double width = column.GetWidth().Value.Value; column.SetWidth(new ColumnWidth(width, true)); }
Comparison operators (equal '=', not equal '<>') doesn't work as expected with references to empty cells. For example, expressions of type '=IF(A1="", TRUE, FALSE)' are evaluated to false when A1 cell is empty. In MS Excel, such expression is evaluated to TRUE if the cell is empty - i.e. empty cell value is considered equal to an empty string "". Workaround: Use ISBLANK function if possible. Its results are consistent with MS Excel.
For instance, when exporting FLOOR.MATH function a #NAME error is displayed initially. Workaround: Select the cell in Excel, click in the formula bar and press Enter without changing anything. This fixes the #NAME error and Excel manages to successfully display the function result.
Add support for CHOOSE function. CHOOSE uses the number parameter to return a value from the list of value arguments. A list of the supported functions is available at http://docs.telerik.com/devtools/document-processing/libraries/radspreadprocessing/features/formulas/functions This function can be implemented as a custom function. Check the following resources for more details on how to achieve that: - http://docs.telerik.com/devtools/document-processing/libraries/radspreadprocessing/features/formulas/custom-functions - https://github.com/telerik/xaml-sdk/tree/master/Spreadsheet/CustomFunctions Available in R2 2017 Official Release.
Add support for TRIM function. TRIM removes all spaces from text except for single spaces between words. A list of the supported functions is available at http://docs.telerik.com/devtools/document-processing/libraries/radspreadprocessing/features/formulas/functions This function can be implemented as a custom function. Check the following resources for more details on how to achieve that: - http://docs.telerik.com/devtools/document-processing/libraries/radspreadprocessing/features/formulas/custom-functions - https://github.com/telerik/xaml-sdk/tree/master/Spreadsheet/CustomFunctions Available in R2 2017 Official Release.
Formulas like =0.0001/10 or formulas that produce small values results in 0 instead of exponential numbers. Available in LIB version: 2017.1.313
Add support for strikethrough font effect for cell formatting. Currently this is not supported by the model and is omitted on import.
When the default number formats for Accounting and Currency are used and the current culture is Russian, the number format is not parsed correctly.
Add support for array formulas (CellFormulaType "array"). These formulas can accept as parameters and return results containing whole arrays (one- or two- dimensional) of data. In MS Excel, such formulas are inserted with CTRL + SHIFT + Enter, and visualized enclosed with curly brackets ('{' and '}').