Strange performance of data grid when exporting to excel

[ArvinZJC]

Hi,

I was trying to export data of a data grid to Excel files. However, I received the following exception when I tried to export an XLSX file.

System.NotSupportedException: 'No data is available for encoding 437. For information on defining a custom encoding, see the documentation for the Encoding.RegisterProvider method.'

When I tried to export an XLS file, I received another exception on Win10 pro.

System.EntryPointNotFoundException: 'Unable to find an entry point named 'CopyMemory' in DLL 'kernel32.dll'.'

My code is as follows:

          var options = new ExcelExportingOptions

            {

                ExportStackedHeaders = true

            };

            var excelEngine = dataGridStrikePrice_VolumeTable.ExportToExcel(dataGridStrikePrice_VolumeTable.View, options);

            var workBook = excelEngine.Excel.Workbooks[0];

            

            SaveFileDialog sfd = new SaveFileDialog

            {

                FileName = "test",

                FilterIndex = 5,

                Filter = "Excel 97 to 2003 Files(*.xls)|*.xls|Excel 2007 to 2010 Files(*.xlsx)|*.xlsx|Excel 2013 File(*.xlsx)|*.xlsx|Excel 2016 Files(*.xlsx)|*.xlsx|Excel 2019 Files(*.xlsx)|*.xlsx"

            };

            if (sfd.ShowDialog() == true)

            {

                using (Stream stream = sfd.OpenFile())

                {

                    switch (sfd.FilterIndex)

                    {

                        case 1:

                            workBook.Version = ExcelVersion.Excel97to2003;

                            break;

                        case 2:

                            workBook.Version = ExcelVersion.Excel2010;

                            break;

                        case 3:

                            workBook.Version = ExcelVersion.Excel2013;

                            break;

                        case 4:

                            workBook.Version = ExcelVersion.Excel2016;

                            break;

                        case 5:

                            workBook.Version = ExcelVersion.Xlsx;

                            break;

                    } // end switch-case

                    workBook.SaveAs(stream);

                }

                //Message box confirmation to view the created workbook.

                if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",

                                    MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes)

                {

                    //Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]

                    Process.Start(sfd.FileName);

                }

            }

I was wondering if anyone could tell me how I can handle these exceptions.

[Mohanram Anbukkarasu]

Hi Arvin, 

Thanks for contacting Syncfusion support.  

We have prepared a sample using the provided code example and checked in our Windows10 Pro machine. Unfortunately we are unable reproduce the reported issue in our end. The excel exporting is performed as expected without any exception. Please find the video from the link given below. 

Video link : https://www.syncfusion.com/downloads/support/directtrac/general/ze/ExportToExcel1657224477  

The sample we have used to check this issue is available in the following link for your reference.  

Sample link : https://www.syncfusion.com/downloads/support/directtrac/general/ze/SfDataGrid_ExportToExcel1072722325  

Please have a look at this sample. If still the issue exists, please let us know if we have missed any customizations you have done in your application or try to reproduce the issue in the above given sample. It will be helpful for us to find the exact cause for the issue and to provide a prompt solution at earlier. 

Regards, 

Mohanram A. 

[ArvinZJC]

Hi Mohanram,

Thanks for your reply! However, I use NET Core as the target framework. I tried to make the app avoid throwing the System.NotSupportedException by adding a line "Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);" before exporting, and I did it. I still cannot work out a solution to avoid throwing the System.EntryPointNotFoundException. I know it works well in the application using NET framework. Perhaps it is affected by the reference WindowsBase? I am just not sure.

Regards,

Arvin

P.S.

I guess the following link may help.

https://github.com/dotnet/wpf/issues/586

[Mohanram Anbukkarasu]

Hi Arvin, 

Thanks for the update. 

For Net Core, the code to open the saved file will have little different as in the following code example. 

Code example :  

    private void ExcelExport_Click(object sender, RoutedEventArgs e) 

    { 

        var options = new ExcelExportingOptions(); 

        options.ExcelVersion = ExcelVersion.Excel2013; 

        var excelEngine = sfDataGrid.ExportToExcel(sfDataGrid.View, options); 

        var workBook = excelEngine.Excel.Workbooks[0]; 

 

        SaveFileDialog sfd = new SaveFileDialog 

        { 

            FilterIndex = 2, 

            Filter = "Excel 97 to 2003 Files(*.xls)|*.xls|Excel 2007 to 2010 Files(*.xlsx)|*.xlsx", 

            FileName = "Book1" 

       }; 

 

        if (sfd.ShowDialog() == true) 

        { 

            using (Stream stream = sfd.OpenFile()) 

            { 

                switch (sfd.FilterIndex) 

                { 

                    case 1: 

                        workBook.Version = ExcelVersion.Excel97to2003; 

                        break; 

 

                    case 2: 

                        workBook.Version = ExcelVersion.Excel2010; 

                        break; 

 

                    case 3: 

                        workBook.Version = ExcelVersion.Excel2013; 

                        break; 

 

                    case 4: 

                        workBook.Version = ExcelVersion.Excel2016; 

                        break; 

 

                    case 5: 

                        workBook.Version = ExcelVersion.Xlsx; 

                        break; 

                } // end switch-case 

 

                workBook.SaveAs(stream); 

            } 

 

 

            //Message box confirmation to view the created workbook. 

 

            if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created", 

                                MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes) 

            { 

 

                //Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer] 

                System.Diagnostics.Process.Start(sfd.FileName); 

            } 

        } 

 

    } 

 

    private static void Open(string fileName) 

    { 

#if !NETCORE 

        System.Diagnostics.Process.Start(fileName); 

#else 

            System.Diagnostics.ProcessStartInfo psi = new System.Diagnostics.ProcessStartInfo 

            { 

                FileName = "cmd", 

                WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden, 

                UseShellExecute = false, 

                CreateNoWindow = true, 

                Arguments = "/c start " + fileName 

            }; 

            System.Diagnostics.Process.Start(psi); 

#endif 

    } 

We have prepared a sample using the above code example and it is available in the following link for your reference.  

Sample link : https://www.syncfusion.com/downloads/support/directtrac/general/ze/ExcelExporting_NetCore2114288013  

Please have a look at this sample and please revert to us with more details if still the issue exists. 

Regards, 

Mohanram A. 

[ArvinZJC]

Hi Mohanram,

Sorry for making you misunderstand me!

Actually, I encounter the exception "System.EntryPointNotFoundException: 'Unable to find an entry point named 'CopyMemory' in DLL 'kernel32.dll'.'" when I select "Excel 97-2003 files (.xls)" in the Save File Dialogue and then click "Save". My app crashes before executing the code of opening the saved file. Visual Studio Debugger shows me that the line making the application raise the exception is "workBook.Version = ExcelVersion.Excel97to2003;".

Regards,

Arvin

[Mohanram Anbukkarasu]

Hi Arvin, 

Thanks for the update. 

WE have also checked by executing the code block “workBook.Version = ExcelVersion.Excel97to2003”. But the issue doesn’t occur in our end. It is hard for us to find the exact cause for the issue without reproducing the issue in our end. We suspect that the issue may not be occur from the DataGrid. Kindly provide more details like stack trace of the exception and the version of the Microsoft Office package you are using the your end. These details may help us to find the cause for the issue without further delay.  

Regards, 

Mohanram A. 

[ArvinZJC]

Hi Mohanram,

As suggested, the stack trace is as follows:

System.EntryPointNotFoundException: 'Unable to find an entry point named 'CopyMemory' in DLL 'kernel32.dll'.'

at Syncfusion.XlsIO.Implementation.Memory.CopyMemory(IntPtr ptrDest, IntPtr ptrSource, Int32 iSize)

at Syncfusion.XlsIO.Parser.Biff_Records.IntPtrDataProvider.CopyTo(Int32 iSourceOffset, DataProvider destination, Int32 iDestOffset, Int32 iLength)

at Syncfusion.XlsIO.Implementation.Collections.RowStorage.ShrinkDataStorage()

at Syncfusion.XlsIO.Implementation.Collections.RowStorage.SetVersion(ExcelVersion version, Int32 iBlockSize)

at Syncfusion.XlsIO.Implementation.Collections.CellRecordCollection.set_Version(ExcelVersion value)

at Syncfusion.XlsIO.Implementation.WorksheetImpl.set_Version(ExcelVersion value)

at Syncfusion.XlsIO.Implementation.WorkbookImpl.set_Version(ExcelVersion value)

at ShSzStockHelper.HomeWindow.ButtonExportToExcel_Click(Object sender, RoutedEventArgs e) in D:\\SD\\C#\\ShSzStockHelper_Windows\\ShSzStockHelper\\HomeWindow.xaml.cs:line 222

at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised)

at System.Windows.UIElement.RaiseEventImpl(DependencyObject sender, RoutedEventArgs args)

at System.Windows.Controls.Primitives.ButtonBase.OnClick()

at Syncfusion.Windows.Tools.Controls.ButtonAdv.OnClick()

at System.Windows.Controls.Primitives.ButtonBase.OnMouseLeftButtonUp(MouseButtonEventArgs e)

at Syncfusion.Windows.Tools.Controls.ButtonAdv.OnMouseLeftButtonUp(MouseButtonEventArgs e)

at System.Windows.UIElement.OnMouseLeftButtonUpThunk(Object sender, MouseButtonEventArgs e)

at System.Windows.RoutedEventArgs.InvokeHandler(Delegate handler, Object target)

at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised)

at System.Windows.UIElement.ReRaiseEventAs(DependencyObject sender, RoutedEventArgs args, RoutedEvent newEvent)

at System.Windows.UIElement.OnMouseUpThunk(Object sender, MouseButtonEventArgs e)

at System.Windows.RoutedEventArgs.InvokeHandler(Delegate handler, Object target)

at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised)

at System.Windows.UIElement.RaiseEventImpl(DependencyObject sender, RoutedEventArgs args)

at System.Windows.UIElement.RaiseTrustedEvent(RoutedEventArgs args)

at System.Windows.Input.InputManager.ProcessStagingArea()

at System.Windows.Input.InputProviderSite.ReportInput(InputReport inputReport)

at System.Windows.Interop.HwndMouseInputProvider.ReportInput(IntPtr hwnd, InputMode mode, Int32 timestamp, RawMouseActions actions, Int32 x, Int32 y, Int32 wheel)

at System.Windows.Interop.HwndMouseInputProvider.FilterMessage(IntPtr hwnd, WindowMessage msg, IntPtr wParam, IntPtr lParam, Boolean& handled)

at System.Windows.Interop.HwndSource.InputFilterMessage(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)

at MS.Win32.HwndWrapper.WndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)

at MS.Win32.HwndSubclass.DispatcherCallbackOperation(Object o)

at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)

at System.Windows.Threading.ExceptionWrapper.TryCatchWhen(Object source, Delegate callback, Object args, Int32 numArgs, Delegate catchHandler)

at System.Windows.Threading.Dispatcher.LegacyInvokeImpl(DispatcherPriority priority, TimeSpan timeout, Delegate method, Object args, Int32 numArgs)

at MS.Win32.HwndSubclass.SubclassWndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam)

at MS.Win32.UnsafeNativeMethods.DispatchMessage(MSG& msg)

at System.Windows.Threading.Dispatcher.PushFrameImpl(DispatcherFrame frame)

at System.Windows.Threading.Dispatcher.PushFrame(DispatcherFrame frame)

at System.Windows.Threading.Dispatcher.Run()

at System.Windows.Application.RunDispatcher(Object ignore)

at System.Windows.Application.RunInternal(Window window)

at System.Windows.Application.Run()

at ShSzStockHelper.App.Main()

I tried to run the app on Win 10 Pro Version 2004 with the region set to the UK and the language set to English (first) and Chinese (second). The Office suites installed on my PC is Microsoft Office Pro Plus 2019 and are activated.

The relevant code page has also been attached. I would really appreciate it if you could find out the problem, 'cause honestly, I don't know why it happens, as you mentioned, in my development environment.

Regards,

Arvin

Attachment: ShSzStockHelper_cd74a808.zip

[Shamini Kiruba Sobers]

Hi Arvin, 

We regret for the delay in getting back to you. 

We have recently supported DataProviderType property in IApplication interface to fix the reported issue in .NET Core platform. Setting ByteArray to that property as below, resolves this issue. 

IApplication application = excelEngine.Excel; 

application.DataProviderType = ExcelDataProviderType.ByteArray; 

The fix with DataProviderType property will be included in our release version 18.2-SP1 which will be rolled out by mid of August 2020.  If you need patch for the fix, kindly confirm whether your XlsIO ASP.NET Core version is 17.1.0.47. We will check the feasibility in your version and provide you the patch for the fix. 

Regards, 

Shamini 

[ArvinZJC]

Hi Shamini,

Thanks for the code you provided! It resolves this issue.

IApplication application = excelEngine.Excel; 

application.DataProviderType = ExcelDataProviderType.ByteArray; 

My Syncfusion (WPF) version is 18.2.0.44. Do you mean that you would fix this issue completely in a future version so that I would not need to add the above code to make the function work normally? If yes, would the fix be included in Volume 3 planned to release by the end of September? It is not that necessary to provide a patch for me.

Regards,

Arvin

[Shamini Kiruba Sobers]

Hi Arvin, 

Thanks for the update. 

You need to add the above code to make the function work normally. What we meant is, DataProviderType property of IApplication interface was available only in Base platforms and now we are including that property in Syncfusion.XlsIO.Net.Core too, which would be available in Volume 3 release. Since you have mentioned that you are facing the issue only when using NET Core as target framework, we thought that you would be using Syncfusion.XlsIO.Net.Core. Now, it looks like you are using WPF package of version 18.2.0.44 and so you are already able to access the DataProviderType property. Hence, there is no need to wait for the release but setting the DataProviderType to ByteArray would be the solution. 

Regards, 

Shamini