What version are you testing with? What types of data are you exporting? Do you have a lot of Appearances defined in your grid that are therefore exporting a lot of color and styling to your Excel workbook? 

It's really tough to say without seeing sample code demonstrating what you are doing. There are a number of optimizations you might be able to make in your code to improve the performance. But we'd have to see the issue and debug it in order to give you any useful advice.

That's a lot of data for one Excel sheet, though. Generally, I recommend filtering the data in some way to narrow down how much is in one sheet. No human user could possibly work with such a large set of data all at once.

Hi Mike,

Thank you for your reply.

I'm using v17.1

The user wants to have all information in the report (excel) to add as an appendix 

CSV experts almost instantly, but of course no formating...

this is the result of the export. It has some appearances and styling, does it help you?

Doesn't seem very complex.

Just a completely wild guess, but I wonder if it's related to the alternating row colors. Presumably that's coming from the RowAlternateAppearance in the grid. You might try turning that off, just as a test. Perhaps the exporting isn't caching that correctly or something. And I think the appearances apply to each cell in Excel, not to the row. So having an appearance that applies to 3679 cells in each row could be an issue.

Thanks Mike, I'll test it.

Does the export fires the InitializeRow and InitializeLayout events? I have some computation on these events.

In the InitializeLayout I create the groups and add the RowAlternateAppearance

and in the InitializeRows I highlight cells with values that exceed values in the standard value column... This is probably where it's loosing performace. I'll test and get back to you

Monica Araujo said:

Does the export fires the InitializeRow and InitializeLayout events? I have some computation on these events.

Yes. The layout is cloned for exporting and so are the rows, so I believe both events will fire when exporting. This allows you to make changes to the Export layout or the Export rows without affecting the on-screen grid. 

You can check IsExportLayout on e.Layout or e.Row.Band.Layout to determine if the event is firing for export or not. 

If it is the InitializeRow causing a problem, then the simplest way to test it would be to unhook the InitializeRow event before the export operation begins and then re-hook it again afterward. 

I added the breakpoint to both events and it seems to only stop in the InitializeRow, but disabling the event before calling the export surprisingly didn't make any difference to the performance. And it is still highlighting my cells with exceedance. it's like it does run it anyway, but it doesn't...

I also removed the alternate row color, but no visible difference in performance.

running out of ideas here... do you have anything?

I added the code I'm using for the events below.

private void UltraGridPivot_InitializeLayout(object sender, InitializeLayoutEventArgs e)
        {
            int selectedStats = GetCountSelectedStatsCount();

            // Set the RowAlternateAppearance on the layout's override.
            //e.Layout.Override.RowAlternateAppearance.BackColor = Color.Lavender;
            e.Layout.Override.AllowAddNew = AllowAddNew.No;
            e.Layout.Override.AllowUpdate = DefaultableBoolean.False;
            e.Layout.Override.AllowDelete = DefaultableBoolean.False;
            e.Layout.Override.WrapHeaderText = DefaultableBoolean.True;

            //**********
            //Add column groups and subgroups for pivot grid
            //**********

            UltraGridBand rootBand = e.Layout.Bands[0];
            rootBand.Groups.Clear();

            // Set the RowLayoutStyle to allow groups in RowLayout mode. 
            e.Layout.Bands[0].RowLayoutStyle = RowLayoutStyle.GroupLayout;

            // Add groups.
            UltraGridGroup groupParameters = rootBand.Groups.Add("Parameters");

            groupParameters.Header.Fixed = true;
            UltraGridGroup groupSamples = rootBand.Groups.Add("Samples");
            UltraGridGroup groupStats = null;
            if (selectedStats > 0)
                groupStats = rootBand.Groups.Add("Stats");

            // Add sub-groups which will be nested in the main groups. 
            // Assign each sub-group to a parent group
            UltraGridGroup subGroupParameters = rootBand.Groups.Add("Parameter");
            subGroupParameters.RowLayoutGroupInfo.ParentGroup = groupParameters;

            UltraGridGroup subGroupStandards = rootBand.Groups.Add("Standards");
            subGroupStandards.RowLayoutGroupInfo.ParentGroup = groupParameters;

            List<UltraGridGroup> subGroupSample = new List<UltraGridGroup>();
            for (int i = 0; i < m_samplesPerDateColumns.Rows.Count; i++)
            {
                string adate = "";
                if (m_samplesPerDateColumns.Rows[i][Structure.Sample.SampleDate] != DBNull.Value)
                    adate = string.Format("{0:MM/dd/yyyy}", Convert.ToDateTime(m_samplesPerDateColumns.Rows[i][Structure.Sample.SampleDate].ToString()));
                UltraGridGroup subGroupSampleItem = rootBand.Groups.Add(m_samplesPerDateColumns.Rows[i][0].ToString(),
                                                                        m_samplesPerDateColumns.Rows[i]["Sample"].ToString() + " [" + adate + "]");
                subGroupSampleItem.RowLayoutGroupInfo.ParentGroup = groupSamples;
                subGroupSample.Add(subGroupSampleItem);
            }

            UltraGridGroup subGroupSummary = null;
            if (selectedStats > 0)
            {
                subGroupSummary = rootBand.Groups.Add("Summary");
                subGroupSummary.RowLayoutGroupInfo.ParentGroup = groupStats;
            }
            m_sampleStartIndex = m_pivotDataTable.Columns.IndexOf(m_samplesPerDateColumns.Rows[0]["sample"].ToString() + "Qualifier");
            int j = 0;
            int cols = m_pivotDataTable.Columns.Count;

            for (int i = 0; i < cols - selectedStats; i++)
            {
                if (dataManagementGridPivot.UltraGrid.DisplayLayout.Bands[0].Columns[i].Hidden)
                    continue;
                if (i < 4)
                {
                    dataManagementGridPivot.UltraGrid.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.ParentGroup = subGroupParameters;
                }
                else if (i < m_sampleStartIndex)
                {
                    dataManagementGridPivot.UltraGrid.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.ParentGroup = subGroupStandards;
                }
                else
                {
                    dataManagementGridPivot.UltraGrid.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.ParentGroup = subGroupSample[j];
                    dataManagementGridPivot.UltraGrid.DisplayLayout.Bands[0].Columns[++i].RowLayoutColumnInfo.ParentGroup = subGroupSample[j];
                    dataManagementGridPivot.UltraGrid.DisplayLayout.Bands[0].Columns[++i].RowLayoutColumnInfo.ParentGroup = subGroupSample[j];
                    j++;
                }

            }
            for (int i = 0; i < selectedStats; i++)
                dataManagementGridPivot.UltraGrid.DisplayLayout.Bands[0].Columns[cols - i - 1].RowLayoutColumnInfo.ParentGroup = subGroupSummary;

            dataManagementGridPivot.UltraGrid.DisplayLayout.Bands[0].ColHeadersVisible = true;
            dataManagementGridPivot.UltraGrid.DisplayLayout.Bands[0].HeaderVisible = false;
            rootBand.Columns[Core.Data.Structure.Parameter.DisplayFormat].Hidden = true;
            rootBand.Columns[Structure.SampleAnalysis.SampleId].Hidden = true;
            e.Layout.UseFixedHeaders = true;
            dataManagementGridPivot.UpdateStatusBar();
        }
        
private void UltraGridPivot_InitializeRow(object sender, InitializeRowEventArgs e)
        {
            try
            {
                DefaultEditorOwnerSettings settings = new DefaultEditorOwnerSettings();
                settings.DataType = typeof(decimal);
                settings.Format = !string.IsNullOrWhiteSpace(e.Row.Cells[Core.Data.Structure.Parameter.DisplayFormat].Value?.ToString()) ? e.Row.Cells[Core.Data.Structure.Parameter.DisplayFormat].Value.ToString() : "#,##0.00";
                settings.MaskInput = settings.Format;
                EditorWithText editor = new EditorWithText(new DefaultEditorOwner(settings));

                DataTable dataTable = ((sender as UltraGrid).DataSource as DataTable);
                int selectedStats = GetCountSelectedStatsCount();
                for (int i = 0; i < e.Row.Cells.Count - selectedStats; i++)
                {
                    var type = dataTable.Columns[i].DataType;
                    if (type == typeof(double))
                    {
                        //set value format
                        e.Row.Cells[i].Editor = editor;

                        if (e.Row.Cells[i].Value == DBNull.Value)
                            continue;

                        if (i < m_sampleStartIndex)
                            continue;

                        //check for sample values higher then water quality standards
                        double value = Convert.ToDouble(e.Row.Cells[i].Value);
                        for (int w = 0; w < m_sampleStartIndex; w++)
                        {
                            type = dataTable.Columns[w].DataType;
                            if (type != typeof(double))
                                continue;

                            if (e.Row.Cells[w].Value == DBNull.Value)
                                continue;

                            double standard = Convert.ToDouble(e.Row.Cells[w].Value);

                            if (value >= standard && m_presenter.m_waterQualityStandard.ContainsKey(dataTable.Columns[w].ColumnName))
                                e.Row.Cells[i].Appearance.BackColor = m_presenter.m_waterQualityStandard[dataTable.Columns[w].ColumnName];
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Messaging.LogAndShowErrorMessage(ex);
            }
        }