XlsxFormatProvider throw exceptions

[Anmol]

Hi, 

I have data in IList and i am trying to export the same to excel(.xls or .xlsx any one). But i am getting the exception.  So if any one can put a light on it.
                                for (var i = 0; i < this.RowList.Count; i++)
                                {
                                    for (var j = 0; j < this.Columns.Count; j++)
                                    {
                                          worksheet.Cells[i, j].SetValue("Test");
                                    }
                                }                          
                        if (workbook != null)
                        {                           
                            XlsxFormatProvider formatProvider = new XlsxFormatProvider();                         
                            formatProvider.Export(workbook, stream);                           // Exception  here                   
                        }

 

{System.TypeInitializationException: The type initializer for 'Telerik.Windows.Documents.Spreadsheet.Measurement.FontManager' threw an exception. ---> System.UnauthorizedAccessException: Invalid cross-thread access.
   at MS.Internal.XcpImports.CheckThread()
   at MS.Internal.XcpImports.Font_GetSystemGlyphTypefaces()
   at System.Windows.Media.Fonts.get_SystemTypefaces()
   at Telerik.Windows.Documents.Spreadsheet.Measurement.FontManager..cctor()
   --- End of inner exception stack trace ---
   at Telerik.Windows.Documents.Spreadsheet.Measurement.FontManager.IsMonospaced(String fontFamily)
   at Telerik.Windows.Documents.Spreadsheet.Model.FontProperties.get_IsMonospaced()
   at Telerik.Windows.Documents.Spreadsheet.Measurement.RadTextMeasurer.Measure(String text, FontProperties fontProperties, Nullable`1 wrappingWidth)
   at Telerik.Windows.Documents.Spreadsheet.Measurement.RadTextMeasurer.MeasureMultiline(String text, FontProperties fontProperties, Nullable`1 wrappingWidth)
   at Telerik.Windows.Documents.Spreadsheet.Layout.LayoutHelper.CalculateCellContentSize(ICellValue cellValue, CellValueFormat format, FontProperties fontProperties, Int32 indentFactor, Nullable`1 wrappingWidth)
   at Telerik.Windows.Documents.Spreadsheet.Layout.LayoutHelper.CalculateAutoColumnWidth(Worksheet worksheet, Int32 columnIndex, Int32 fromRowIndex, Int32 toRowIndex, Boolean respectNumberValuesOnly)
   at Telerik.Windows.Documents.Spreadsheet.Model.ColumnSelection.<>c__DisplayClass8.<AutoFitWidth>b__7(CellRange cellRange)
   at Telerik.Windows.Documents.Spreadsheet.Model.SelectionBase.ExecuteForEachRange(IEnumerable`1 cellRanges, Action`1 action, Predicate`1 canExecute)
   at Telerik.Windows.Documents.Spreadsheet.Model.SelectionBase.ExecuteForEachRangeInsideBeginEndUpdate(IEnumerable`1 cellRanges, Action`1 action, Predicate`1 canExecute)
   at Telerik.Windows.Documents.Spreadsheet.Model.ColumnSelection.AutoFitWidth(Boolean expandOnly)
   at Telerik.Windows.Documents.Spreadsheet.Model.CellSelection.<>c__DisplayClass1`1.<SetPropertyValue>b__0(CellRange cellRange)
   at Telerik.Windows.Documents.Spreadsheet.Model.SelectionBase.ExecuteForEachRange(IEnumerable`1 cellRanges, Action`1 action, Predicate`1 canExecute)
   at Telerik.Windows.Documents.Spreadsheet.Model.SelectionBase.ExecuteForEachRangeInsideBeginEndUpdate(IEnumerable`1 cellRanges, Action`1 action, Predicate`1 canExecute)
   at Telerik.Windows.Documents.Spreadsheet.Model.CellSelection.SetPropertyValue[T](ICellProperty`1 property, T value)
   at Telerik.Windows.Documents.Spreadsheet.Model.CellSelection.SetValueInternal(ICellValue cellValue, CellValueFormat newFormat)
   at Telerik.Windows.Documents.Spreadsheet.Model.CellSelection.SetValue(String value)
   at OnGuard.Connext.FrontEnd.Library.SL.ViewModel.ExportOptionsViewModel.ExportToFile()}

[Polya]

Hi Anmol,

I've tried to reproduce the issue at our side but to no avail. I've created a sample project using your code and everything works as expected. You can find the project attached.
From the description of the error it appers that there is a cross thread reference exception. Could you please elaborate more on your scenario; are you executing the export in a separate thread, or are the RowList and Columns being changed in a separate thread? Let me know how to modify the attached project to reproduce the issue. This way I can get a better understanding of your approach and assist you further. 

Regards,
Polya
Progress Telerik

[Anmol]

 

Hi Polya,

Now the performance issue is coming. Well the above issue was resolved the time I placed the export excel creation code with the dispatcher, check the bold part below

Below code is placed in view model as I am also updating the label on xaml side by side to show the progress

private void ExportToFile()
        {
            Deployment.Current.Dispatcher.BeginInvoke(() =>
            {
                ProgressInfo += GetCurrentDisplayTime() + " " + mlUtil.GetCachedLoadString(8745) + Environment.NewLine;
            });
            // Exporting data to file

            string content = string.Empty;
            switch (ExportFormat)
            {
                case ExportFormat.CSV:
                    CsvExporter csvExporter = new CsvExporter(Columns, RowList);
                    content = csvExporter.Export(this.IncludeHeader);
                    sw = new System.IO.StreamWriter(stream, Encoding.UTF8);
                    sw.Write(content);
                    break;
                case ExportFormat.Excel:
                    ExcelExporter excelExporter = new ExcelExporter(Columns, RowList);
                    if (Path.GetExtension(this.FilePath) == ".xls")
                    {
                        excelExporter.Export(stream, this.IncludeHeader, true);
                    }
                    else
                    {
                        
                        Deployment.Current.Dispatcher.BeginInvoke(() =>
                        {
                            excelExporter.ExportToXlsx(stream, this.IncludeHeader, true);
                        });
                    }
                    break;
                default:
                    break;
            }

        }

But now the issue is coming regarding the performance. If I export say 2 rowitems from my Ilist<object> its fine. But when it comes for say 8000 it takes lot of time and even hangs at the bold code line below.

public void ExportToXlsx(Stream outputStream, bool includeHeader, bool shouldAddEmptyRecords)
        {
            var workbook = new Telerik.Windows.Documents.Spreadsheet.Model.Workbook();
            var worksheet = workbook.Worksheets.Add();
            workbook.Worksheets[0].Name = "Worksheet1";
            bool HeaderCreated = false;
            int columnCount = 0;
            int rowCount = 0;
            foreach (var item in this.items)
            // for (var i = 0; i < this.items.Count; i++)
            {
                columnCount = 0;
                foreach (var col in this.columns)
                //for (var j = 0; j < this.columns.Count; j++)
                {
                    if (col.HeaderName != "'")
                    {
                        if (!HeaderCreated && rowCount == 0)
                        {
                            if (includeHeader)
                            {
                                CellValueFormat c = new CellValueFormat("@");
                                worksheet.Cells[rowCount, columnCount].SetFormat(c);
                                worksheet.Cells[rowCount, columnCount].SetValue(col.HeaderName);
                                HeaderCreated = true;
                            }
                        }
                        Cell cell = null;
                        cell = columns[columnCount].GetExcelCellForExport(item);

                        int rowIndex = rowCount + 1;
                        if (cell.Format.FormatType == CellFormatType.General)
                        {
                            //string str = Convert.ToString(cell.Value);
                            CellValueFormat c = new CellValueFormat("@");
                            worksheet.Cells[rowIndex, columnCount].SetFormat(c);
                            worksheet.Cells[rowIndex, columnCount].SetValue(cell.StringValue);
                        }
                        else if (cell.Format.FormatType == CellFormatType.Number)
                        {
                            worksheet.Cells[rowIndex, columnCount].SetValue(cell.NumberValue);
                        }
                        else if (cell.Format.FormatType == CellFormatType.DateTime)
                        {
                            worksheet.Cells[rowIndex, columnCount].SetValue(cell.DateTimeValue);
                        }
                    }
                    columnCount++;
                }
                // if (rowCount == 0)

                rowCount++;
            }
            if (workbook != null)
            {
                XlsxFormatProvider formatProvider = new XlsxFormatProvider();
                formatProvider.Export(workbook, outputStream);
            }
        }

I also followed the solution mentioned in the below link, by first coverting the value to specific datatype and then assign to cell value based on the data type, but no success

https://www.telerik.com/forums/performance-excel-export

Regards,

Anmol

[Polya]

Hello Anmol,

From the provided code I see that you are creating a Workbook and either exporting it to CSV or XLSX. In that case, I recommend using our RadSpreadStreamProcessing library which is more suitable for exporting large amounts of data. This is so because the RadSpreadStreamProcessing export writes the data directly into a stream, unlike RadSpreadProcessing which creates models for the elements in the document. This is why the memory used with the spread streaming library is significantly lower than when using RadSpreadProcessing.
You can refer to our documentation for information on how to create a similar model, and export to XLSX or CSV. Also you can take a look at our Developer focused examples for RadSpreadStreamProcessing: https://github.com/telerik/document-processing-sdk/tree/2d32fd229282a1f7101d6f9b0961a650e89a1dc1/SpreadStreamProcessing. 
If you are creating a WPF application, I also suggest checking our QSF example demonstrating how to export a large document using RadSpreadStreamProcessing: https://demos.telerik.com/wpf/ -> SpreadStreamProcessing -> Large Document Export example.

Hope this helps.

Regards,
Polya
Progress Telerik

[Anmol]

Hi Polya,

Its a silverlight application and  RadSpreadStreamProcessing  is not available for this. I have already gone through the telerik documentation of  RadSpreadStramProcessing . But my bad is that it is not available for silverlight.

I optimized my code

Now the stats are as following
Records    Time
2000         50 s
5000         2m 15sec
10000       gets crash here saying the following

 

The Writer is closed or in error state.
Stack Trace:    at System.Xml.XmlWellFormedWriter.AdvanceState(Token token)
   at System.Xml.XmlWellFormedWriter.WriteEndElement()
   at Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.Export.XlsxExporter.Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.OpenXml.IOpenXmlWriter.WriteElementEnd()
   at Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.OpenXml.OpenXmlElementBase.WriteTo(IOpenXmlWriter writer)
   at Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.Model.XlsxPartBase.WriteTo(IOpenXmlWriter writer)
   at Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.Export.XlsxExporter.ExportXlsxPart(ZipPackage package, OpenXmlPartBase part)
   at Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.Export.XlsxExporter.Export(Stream output, XlsxExportSettings 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)
   at OnGuard.Connext.FrontEnd.Library.SL.Util.DataExport.ExcelExporter.ExportToXlsx(Stream outputStream, Boolean includeHeader, Boolean shouldAddEmptyRecords)
   at OnGuard.Connext.FrontEnd.Library.SL.ViewModel.ExportOptionsViewModel.<>c__DisplayClass108_0.<ExportToFile>b__1()
Additional Info: unhandled exception


My updated code

public void ExportToXlsx(Stream outputStream, bool includeHeader, bool shouldAddEmptyRecords)
        {
            var workbook = new Telerik.Windows.Documents.Spreadsheet.Model.Workbook();
            var worksheet = workbook.Worksheets.Add();
            workbook.Worksheets[0].Name = "Worksheet1";

            workbook.History.IsEnabled = false;
            workbook.SuspendLayoutUpdate();
            int columnCount = 0;
            int rowCount = 0;
            int startRowCount = 0;
            CellValueFormat c;
            if (includeHeader)
            {
                //Writing Column Names 
                foreach (IExportableColumn column in this.columns)
                {
                    if (column.HeaderName != "'")
                    {
                        c = new CellValueFormat("@");
                        worksheet.Cells[rowCount, columnCount].SetFormat(c);
                        worksheet.Cells[rowCount, columnCount].SetValue(column.HeaderName);
                        columnCount++;
                    }
                }
                startRowCount = 1;
            }
            else
            {
                startRowCount = 0;
            }
            columnCount = 0;
            foreach (IExportableColumn columnLayoutBase in columns)
            {
                if (columnLayoutBase.HeaderName != "'")
                {
                    rowCount = startRowCount;
                    foreach (object item in this.items)
                    {
                        Cell cell = null;
                        cell = columnLayoutBase.GetExcelCellForExport(item);
                        switch (cell.Format.FormatType)
                        {
                            case CellFormatType.General:
                                c = new CellValueFormat("@");
                                worksheet.Cells[rowCount, columnCount].SetFormat(c);
                                worksheet.Cells[rowCount, columnCount].SetValue(cell.StringValue);
                                break;
                            case CellFormatType.Number:
                                c = new CellValueFormat("0.00");
                                worksheet.Cells[rowCount, columnCount].SetFormat(c);
                                worksheet.Cells[rowCount, columnCount].SetValue(cell.NumberValue);
                                break;
                            case CellFormatType.DateTime:
                                c = new CellValueFormat("dd-mm-yyyy");
                                worksheet.Cells[rowCount, columnCount].SetFormat(c);
                                worksheet.Cells[rowCount, columnCount].SetValue(cell.DateTimeValue);
                                break;
                        }
                        rowCount++;
                    }
                    columnCount++;
                }
            }
            if (workbook != null)
            {
                XlsxFormatProvider formatProvider = new XlsxFormatProvider();
                formatProvider.Export(workbook, outputStream);
            }
        }

 

 

[Polya]

Hello Anmol,

Indeed, RadSpreadStreamProcessing is not available for Silverlight. However, as a suggestion, if your application allows it, you could create a WPF application that exports XLSX on the server using RadSpreadStreamProcessing for WPF. Then you could call its Export method from a service from your Silverlight application.

Regarding the export optimizations for RadSpreadProcessing - you've correctly found our help article: https://docs.telerik.com/devtools/document-processing/libraries/radspreadprocessing/performance where all the optimization options I can suggest are listed.

Regarding the exception thrown - I've modified the previously sent project from me to export 1000x1000 workbook, but I could not reproduce this error. I will kindly ask you to open a new support thread and attach an application demonstrating the issue so we can investigate further.

Regards,
Polya
Progress Telerik

[Zim]

const Person = require("../index"); async function whatever() { try { const valentinogagliardi = new Person("valentinogagliardi"); await valentinogagliardi.getData(); // do stuff with the eventual result and return something } catch (error) { throw Error(error); } } whatever();