XlsxFormatProvider throw exceptions

7 posts, 0 answers
  1. Anmol
    Anmol avatar
    5 posts
    Member since:
    Jan 2018

    Posted 31 Jan Link to this post

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

  2. Polya
    Admin
    Polya avatar
    262 posts

    Posted 02 Feb Link to this post

    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

  3. Anmol
    Anmol avatar
    5 posts
    Member since:
    Jan 2018

    Posted 02 Feb in reply to Polya Link to this post

     

    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

  4. Polya
    Admin
    Polya avatar
    262 posts

    Posted 07 Feb Link to this post

    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

  5. Anmol
    Anmol avatar
    5 posts
    Member since:
    Jan 2018

    Posted 07 Feb in reply to Polya Link to this post

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

     

     

  6. Polya
    Admin
    Polya avatar
    262 posts

    Posted 12 Feb Link to this post

    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

  7. Zim
    Zim avatar
    7 posts
    Member since:
    May 2018

    Posted 5 days and 14 hours ago Link to this post

    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();
Back to Top