Create API allowing conversion of .NET standard numeric format strings to Excel/RadSpreadsheet's number formats. For example, G3 .NET numeric format should be converted to scientific notation RadSpreadsheet mode - CellValueFormat("0.00E+00").
Use the below code snippet to generate XLSX document and export it. You will notice that the export operation is extremely slow:
Stopwatch sw = new Stopwatch();
sw.Start();
IWorkbookFormatProvider formatProvider = new Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.XlsxFormatProvider();
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
Worksheet worksheet2 = workbook.Worksheets.Add();
worksheet2.Name ="Days";
List<string> weekdays = new List<string>() { "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"};
for (int i = 0; i < 7; i++)
{
worksheet2.Cells[0, i].SetValue(weekdays[i]);
}
for (int i = 0; i < 200; i++)
{
for (int j = 0; j < 10; j++)
{
CellIndex cellIndex = new CellIndex(i, j);
CellSelection selection = worksheet.Cells[cellIndex];
selection.SetValue("Wednesday");
var context = new ListDataValidationRuleContext(worksheet, cellIndex)
{
InputMessageTitle = "InputMessageTitle",
InputMessageContent = "InputMessageTitle"
};
context.ErrorStyle = ErrorStyle.Stop;
context.ErrorAlertTitle = "ErrorAlertTitle";
context.ErrorAlertContent = "ErrorAlertContent";
context.InCellDropdown = true;
context.Argument1 = "=Days!A0:A6"; //"Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday";
ListDataValidationRule rule = new(context);
worksheet.Cells[cellIndex].SetDataValidationRule(rule);
}
}
string outputFile = @"..\..\..\output.xlsx";
File.Delete(outputFile);
using (Stream output = new FileStream(outputFile, FileMode.Create))
{
formatProvider.Export(workbook, output);
}
sw.Stop();
Debug.WriteLine("Export " + sw.ElapsedMilliseconds);
Example:
<sheetViews>
<sheetView tabSelected="1" view="pageBreakPreview" zoomScaleNormal="100" zoomScaleSheetLayoutView="100" workbookViewId="0">
<selection activeCell="J5" sqref="J5"/>
</sheetView>
</sheetViews>
ArgumentException at Telerik.Windows.Documents.FormatProviders.OpenXml.Model.Elements.Theme.SystemColorElement.GetColor(IOpenXmlImportContext context)
The problematic value is val="windowText"
When a chart is inserted in a document, generated by SpreadProcessing or WordsProcessing, the chart seems to lack any bars. The reason is that some theme information is missing, which effectively makes shapes take their default color, which is transparent. The same applies for shapes inserted in any Document Processing-generated OOXML documents - docx, xlsx. Workaround: (applicable for Excel) Manually change the theme for the document in MS Excel: - Choose Page Layout -> Themes -> Themes dropdown -> Office.
Exception "Format string is not in the correct format" is thrown when importing a file with the following format string: "US$"#,##0.0"m";($#,##0.0)
The exception is thrown in the ValidateDateTimeFormatDescriptor method.
The data validation rule should have values for formula1 and formula2 elements, which define the start and end values for a range to validate between. When such values are missing, a NullReferenceException is thrown on import and the document cannot be imported.
Add support for theme effects (format schemes). They are described in OOXML using the 'fmtScheme' element. Thing of providing predefined sets. In MS Excel the UI for changing is located in Page Layout tab -> Themes -> Effects.
Incorrect calculation of Round function returns wrong results in some cases. For example, test Microsoft Excel and Spreadsheet results of: ROUND(1.365, 2) to see the discrepancy. Available in R3 2018 SP1 release.
When a worksheet has name that has spaces and a hyperlink to this worksheet is being inserted in Excel, quotes are used to surround the name (ex: 'Sheet 12'!A1) for a valid hyperlink. If quotes are not added, the hyperlink is invalid. In RadSpreadsheet, it is the opposite: (Sheet 12!A1 is correct, while 'Sheet 12'!A1 is incorrect). Thus, opening such an exported document from RadSpreadsheet in Excel loads it with incorrect hyperlinks; and importing such an Excel document in RadSpreadsheet loads it with incorrect hyperlinks. A way to workaround this issue is to modify the hyperlinks on import/export from/to RadSpreadsheet. You can find attached a project demonstrating this approach.
The format string for dates should be in lowercase so they can work as expected. Ensure this in the internal logic.
Split allows you to split the window into different panes that each scroll separately. This feature is different than the horizontal/vertical split of freeze panes, which is supported.
When the worksheet contains many formulas which use large ranges (e.g. covering 50000 cells) as arguments and these formulas have to be calculated, this causes OutOfMemoryException.
The named ranges need to be translated when used in a certain cell just like the data validation rules. WORKAROUND: When you use RowSelection.Insert() method, for instance, after the row insertion you may iterate all defined names in the document. Parse their RefersTo property and if it is affected by the insertion, delete and recreate the DefinedName in the corresponding NameCollection by updating the RefersTo text value. Sample code for parsing the RefersTo property may be seen in the description of this feedback item: https://feedback.telerik.com/Project/184/Feedback/Details/190061-spreadprocessing-add-api-to-get-the-list-of-ranges-to-which-a-defined-name-refer
The ADDRESS function is used to obtain the address of a cell in a worksheet, given specified row and column numbers. For example, ADDRESS(2,3) returns $C$2.
Excel exports shapes as TwoCellAnchor elements, which specifies the top left and the bottom right location of a shape. The size of the shape is recorded in the extents element (a:ext), cx and cy attributes. At the moment, the spreadsheet import first looks at the extents element and if it doesn't find the size there, calculates it from the TwoCellAnchor element. However, if an XLSX document has a shape with incorrect size indicated in the extents element, Excel will still be able to open it correctly, probably because it looks at the TwoCellAnchor element. RadSpreadsheet/SpreadProcessing, on the other hand, will show the image with an incorrect size.
When there are split panes in a workbook, the topLeftCell property can be omitted. In this case, the format provider throws NullReferenceException.