Dirty header column after export in excel

8 posts, 0 answers
  1. Dario Concilio
    Dario Concilio avatar
    129 posts
    Member since:
    Apr 2016

    Posted 27 Apr Link to this post

    Hi,

    this is the strange behavior:

    I have a RadGridView its ItemSource setted with a VirtualQueryableCollectionView.

    User set a filter and then export, I use this method:

    public static void ExportToExcel(RadGridView p_GridView)
    {
        if (p_GridView == null)
            return;
     
        var view = p_GridView.ItemsSource as VirtualQueryableCollectionView;
        int loadSize = view.LoadSize;
     
        view.LoadSize = p_GridView.Items.TotalItemCount;
     
        string extension = "xlsx";
     
        SaveFileDialog dialog = new SaveFileDialog()
        {
            DefaultExt = extension,
            Filter = String.Format("{1} files (*.{0})|*.{0}|All files (*.*)|*.*", extension, "Excel"),
            FilterIndex = 1
        };
     
        if (dialog.ShowDialog() == DialogResult.OK)
        {
            using (Stream stream = dialog.OpenFile())
            {
                p_GridView.ElementExportingToDocument += P_GridView_ElementExportingToDocument;
                p_GridView.ExportToXlsx(stream,
                    new GridViewDocumentExportOptions()
                    {
                        ShowColumnFooters = true,
                        ShowColumnHeaders = true,
                        ShowGroupFooters = true
                    });
            }
     
            System.Diagnostics.Process.Start(dialog.FileName);
        }
     
        view.LoadSize = loadSize;
    }

    Sometimes it exports with first row of sheet dirty, because in first row there isn't columns header, but a row that does not belong to the collection (filtered by user).

    PS: Normally, the first times export correctly header row (columns label), after several times appears that showed in SequenceExport.PNG

     

  2. Dilyan Traykov
    Admin
    Dilyan Traykov avatar
    371 posts

    Posted 28 Apr Link to this post

    Hello Potito,

    I tried to reproduce the behavior you've described but without any success.

    Could you please have a look at the project I've attached to my reply and maybe modify it so that it demonstrates the undesired behavior?

    Thank you in advance for your cooperation.

    Regards,
    Dilyan Traykov
    Telerik
    Do you need help with upgrading your AJAX, WPF or WinForms project? Check the Telerik API Analyzer and share your thoughts.
  3. UI for WPF is Visual Studio 2017 Ready
  4. Dario Concilio
    Dario Concilio avatar
    129 posts
    Member since:
    Apr 2016

    Posted 29 Apr in reply to Dilyan Traykov Link to this post

    Thank you for support,

    I think I found the problem, in first time I have not solved number of rows in excel exported yet, then I added a portion of code to keep the correct number of rows base user filter.

    After that I saw that it counts total of rows (without filter applyed), this one generates a strange effect, it shows me the last rows of original collection instead of header rows.

    I corrected code to have to obtain "the real rows count" (with filter applyed), then resolve my problem.

    Synthesis:

    • Collection of 1000 rows used with VirtualQueryableCollectionView (loadsize = 10)
    • user apply filters, result => grid shows 50 rows
    • user export to excel (using view.LoadSize = p_GridView.Items.TotalItemCount;) => the first rows of excel show 1000th rows
    • CORRECTION: use export to excel (using view.LoadSize = p_GridView.Items.Count;) => the first rows of excel show header row of grid

    This is my code corrected.

    public static void ExportToExcel(RadGridView p_GridView)
    {
        if (p_GridView == null)
            return;
     
        var view = p_GridView.ItemsSource as VirtualQueryableCollectionView;
        int loadSize = view.LoadSize;
     
        //WRONG!!! view.LoadSize = p_GridView.Items.TotalItemCount;
        view.LoadSize = p_GridView.Items.Count;
     
        string extension = "xlsx";
     
        SaveFileDialog dialog = new SaveFileDialog()
        {
            DefaultExt = extension,
            Filter = String.Format("{1} files (*.{0})|*.{0}|All files (*.*)|*.*", extension, "Excel"),
            FilterIndex = 1
        };
     
        if (dialog.ShowDialog() == DialogResult.OK)
        {
            using (Stream stream = dialog.OpenFile())
            {
                p_GridView.ElementExportingToDocument += P_GridView_ElementExportingToDocument;
                p_GridView.ExportToXlsx(stream,
                    new GridViewDocumentExportOptions()
                    {
                        ShowColumnFooters = true,
                        ShowColumnHeaders = true,
                        ShowGroupFooters = true
                    });
            }
     
            System.Diagnostics.Process.Start(dialog.FileName);
        }
     
        view.LoadSize = loadSize;
    }

  5. Dario Concilio
    Dario Concilio avatar
    129 posts
    Member since:
    Apr 2016

    Posted 29 Apr in reply to Dario Concilio Link to this post

    Nothing. :-(

    I don't resolve.....

    The first one and last one should not be there, they are more.

    I don't understand where I wrong.

    My method for retrive items for DataGridView

    private void LoadClienti()
    {
        //Se già si stanno caricando i clienti non deve rifare la chiamata
        if ((_FirstLoad) || (_LoadingClienti))
            return;
     
        _LoadingClienti = true;
     
        ClienteProvider provider = new ClienteProvider(Comunicazione.GetConnectionReference(Enumeratori.TipiConnessione.DatabasePrincipale, true));
        provider.Notification += Provider_Notification;
     
        //Overload del metodo GetAll passando sempre tutti i campi della ricerca veloce
        //l'overload gestirà al suo interno la query più opportuna
        var response = provider.GetAll(FiltroCodice, FiltroRagioneSociale, MostraAncheObsoleti);
        if (response.Response)
        {
            var view = new VirtualQueryableCollectionView((List<Cliente>) response.Info) { LoadSize = 10 };
            Clienti = view;
     
            _LoadingClienti = false;
        }
    }

    My method export:

    public static void ExportToExcel(RadGridView p_GridView)
    {
        if (p_GridView == null)
            return;
     
        var view = p_GridView.ItemsSource as VirtualQueryableCollectionView;
        int loadSize = view.LoadSize;
     
        //view.LoadSize = p_GridView.Items.TotalItemCount;
        view.LoadSize = p_GridView.Items.Count;
     
        string extension = "xlsx";
     
        SaveFileDialog dialog = new SaveFileDialog()
        {
            DefaultExt = extension,
            Filter = String.Format("{1} files (*.{0})|*.{0}|All files (*.*)|*.*", extension, "Excel"),
            FilterIndex = 1
        };
     
        if (dialog.ShowDialog() == DialogResult.OK)
        {
            using (Stream stream = dialog.OpenFile())
            {
                p_GridView.ElementExportingToDocument += P_GridView_ElementExportingToDocument;
                p_GridView.ExportToXlsx(stream,
                    new GridViewDocumentExportOptions()
                    {
                        ShowColumnHeaders = true,
                        AutoFitColumnsWidth = true
                    });
            }
     
            System.Diagnostics.Process.Start(dialog.FileName);
        }
     
        view.LoadSize = loadSize;
    }

    Users can apply some filter by this method:

    private void VM_ApplyFilterDescriptor(string p_Field, object p_Value)
    {
        Telerik.Windows.Controls.GridViewColumn currentColumn = this.DocumentHostGridView.Columns[p_Field];
        Telerik.Windows.Controls.GridView.IColumnFilterDescriptor currentFilter = currentColumn.ColumnFilterDescriptor;
     
        currentFilter.SuspendNotifications();
     
        currentFilter.DistinctFilter.Clear();
     
        switch (p_Field)
        {
            case "Nazione":
                {
                    if (p_Value != null)
                        foreach (var item in (List<Nazione>) p_Value)
                            currentFilter.DistinctFilter.AddDistinctValue(item.Codice);
                }
                break;
            case "Provincia":
                {
                    if (p_Value != null)
                        foreach (var item in (List<Provincia>) p_Value)
                            currentFilter.DistinctFilter.AddDistinctValue(item.Codice);
                }
                break;
            case "Agente":
                {
                    if (p_Value != null)
                        foreach (var item in (List<Agente>) p_Value)
                            currentFilter.DistinctFilter.AddDistinctValue(item.Codice);
                }
                break;
            case "TipoBlocco":
                {
                    if (p_Value != null)
                        foreach (var item in (List<TipoBloccoSelezionabile>) p_Value)
                            currentFilter.DistinctFilter.AddDistinctValue(item.TipoBlocco);
                }
                break;
        }
     
        currentFilter.ResumeNotifications();
    }

     

  6. Dilyan Traykov
    Admin
    Dilyan Traykov avatar
    371 posts

    Posted 03 May Link to this post

    Hello Potito,

    As I am unable to reproduce the behavior at my end I would like to ask you a couple of questions which might help in finding a solution to your issue:

    1) What is the value of p_GridView.Items.Count right before the export? Is it different than the total amount of items exported?
    2) If you handle the ItemsLoading event of the VCQV and place a breakpoint in it, does it get hit while exporting the GridView?

    If the answer to any of these questions is yes, then probably this behavior is due to the fact that additional items are loaded while exporting the GridView. Using a Dispatcher might help in resolving this issue.

    Regards,
    Dilyan Traykov
    Telerik
    Do you need help with upgrading your AJAX, WPF or WinForms project? Check the Telerik API Analyzer and share your thoughts.
  7. Dario Concilio
    Dario Concilio avatar
    129 posts
    Member since:
    Apr 2016

    Posted 03 May in reply to Dilyan Traykov Link to this post

    Ok, I reply to your request:

    • CodeValues: values of VCQV
    • Count Of gridView: values of RadGridView
    • firstand last wrong: excel generated with first and last row wrong - they should not be there, instead of the first one should be columns header
    • loadingdata: ItemsLoading event first one
    • loading step 1: ItemsLoading event second one

     

  8. Dario Concilio
    Dario Concilio avatar
    129 posts
    Member since:
    Apr 2016

    Posted 03 May in reply to Dario Concilio Link to this post

    Other strange export.

    Applying a filter, and export I see this. I see (as column header) only column that I filtered, others column header is dirty....

  9. Dilyan Traykov
    Admin
    Dilyan Traykov avatar
    371 posts

    Posted 05 May Link to this post

    Hello Potito,

    Unfortunately, I am still unable to reproduce the issue you've described at my end. Could you please try to modify the project I attached in one of my previous replies in order to demonstrate the behavior you're observing and if you manage to achieve that, describe the steps needed to reproduce it?

    If you are unable to reproduce the behavior using my sample project, may I kindly ask you to open a new support ticket and send over a project of your own, where the issue is observed?

    Thank you in advance for your cooperation and understanding.

    Regards,
    Dilyan Traykov
    Telerik
    Do you need help with upgrading your AJAX, WPF or WinForms project? Check the Telerik API Analyzer and share your thoughts.
Back to Top
UI for WPF is Visual Studio 2017 Ready