Dirty header column after export in excel

[Dario Concilio]

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

 

[Dilyan Traykov]

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

[Dario Concilio]

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

[Dario Concilio]

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

 

[Dilyan Traykov]

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 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

[Dario Concilio]

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

 

[Dario Concilio]

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....

[Dilyan Traykov]

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