Export RadGridView to Excel and freeze top row

2 Answers 150 Views
GridView
Brian
Top achievements
Rank 1
Iron
Iron
Brian asked on 25 Aug 2022, 04:07 PM

Is there a way to export from the gridview and freeze the top row?

Thanks,

2 Answers, 1 is accepted

Sort by
1
Accepted
Dimitar
Telerik team
answered on 29 Aug 2022, 10:03 AM

Hi Brian,

You can use the following approach for this: 

private void radButton1_Click(object sender, EventArgs e)
{ 
    GridViewSpreadExport spreadExporter = new GridViewSpreadExport(this.radGridView1);
    SpreadExportRenderer exportRenderer = new SpreadExportRenderer();
    exportRenderer.WorkbookCreated += ExportRenderer_WorkbookCreated;
    spreadExporter.RunExport(@"C:\my_temp\exportedFile.xlsx", exportRenderer);
}

private void ExportRenderer_WorkbookCreated(object sender, WorkbookCreatedEventArgs e)
{
    var worksheet = e.Workbook.Worksheets[0];
    worksheet.ViewState.FreezePanes(new CellIndex(0, 0), 1, 0);
}

I hope this helps. Should you have any other questions do not hesitate to ask.

Regards,
Dimitar
Progress Telerik

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

Brian
Top achievements
Rank 1
Iron
Iron
commented on 29 Aug 2022, 10:40 AM

Thanks Dimitar!
Brian
Top achievements
Rank 1
Iron
Iron
commented on 29 Aug 2022, 05:04 PM

Dimitar, that worked exactly as I needed it. 

Thanks again.

Brian
Top achievements
Rank 1
Iron
Iron
commented on 30 Aug 2022, 12:08 PM

Dimitar, a follow up question if you do not mind. 

How about adding a header?

Dimitar
Telerik team
commented on 31 Aug 2022, 05:00 AM

Hi Brian,

You can use the same event for this. The following article shows how you can add the header:  SpreadProcessing - Headers and Footers.

Regards,

Dimitar

Brian
Top achievements
Rank 1
Iron
Iron
commented on 31 Aug 2022, 12:15 PM

I looked at that article. Although it showed me the items to call. It was not helping me.

Could you provide a working snippit?

 

Thank you!

Dimitar
Telerik team
commented on 01 Sep 2022, 04:41 AM

Hi Brian,

Here is an example of this: 

private void ExportRenderer_WorkbookCreated(object sender, WorkbookCreatedEventArgs e)
{
    var worksheet = e.Workbook.Worksheets[0];
    worksheet.ViewState.FreezePanes(new CellIndex(0, 0), 1, 0);

    HeaderFooterSettings settings = worksheet.WorksheetPageSetup.HeaderFooterSettings;
    settings.Header.RightSection.Text = "Right Header";
    settings.Header.LeftSection.Text = "Left Header";
}

Please note that this header is only visible when printing and it will appear when you print the file. If you want to achieve different results and add content before the exported data you will need to insert some rows

Let me know if I can assist you further.

Regards,

Dimitar

 

 

 

Brian
Top achievements
Rank 1
Iron
Iron
commented on 06 Sep 2022, 12:50 PM

Ah! Ok. Interesting. Inserting row/rows maybe how I need to go. 

The attached is what I am trying to accomplish with the headers.

Sorry I guess I should have posted this previously.

Formatting the data in to the columns is another issue I am not ready to tackle yet. That is my next issue. If I need help on that I will ask later, however that is not a Telerik/Progress issue.

Any suggestions on how to make the Row 1 look like the attached would be appreciated.

 

thanks, and  I will try the insert row piece and see if I can figure this out with that.

 

Thank you!

Brian
Top achievements
Rank 1
Iron
Iron
commented on 06 Sep 2022, 01:13 PM

It looks like the RadSpreadProcessing Repeat Values option would fit the bill to move all data based on a row value into a new column set.

IN the example document I attached, the Repeat Values looks like I can take the stuff from Day 1, Day 15 and Day xx into separate columns. Would that be correct?

Dimitar
Telerik team
commented on 07 Sep 2022, 10:39 AM

Hi Brian, 

I am not sure how the result looks like after the export and which part is missing. It seems to me that you need to set the style of the cells in the first row. For example:

var worksheet = e.Workbook.Worksheets[0];

for (int i = 0; i < worksheet.UsedCellRange.ColumnCount; i++)
{
    var cell = worksheet.Cells[0, i];
    cell.SetIsBold(true);
    cell.SetHorizontalAlignment(Telerik.Windows.Documents.Spreadsheet.Model.RadHorizontalAlignment.Center);
    cell.SetForeColor(new ThemableColor(Colors.Gray));
    cell.SetFill(new PatternFill(PatternType.Solid, Colors.LightGray, Colors.LightGray));
}

More information is available here: SpreadProcessing - Get, Set and Clear Cell Properties.

As to the second post at hand, I cannot understand your requirements. Could you provide a more detailed example of the desired functionality. Perhaps it would be better to open a new support ticket for this. 

I hope this helps. Should you have any other questions do not hesitate to ask.

Regards,

Dimitar

0
Arun
Top achievements
Rank 1
Iron
answered on 26 Aug 2022, 07:11 AM | edited on 26 Aug 2022, 07:13 AM
hi Brian ,

this code will export the Radgridview to excel

 


 Dim GridView As New RadGridView
        Dim Title As String = String.Empty
        Dim Extension As String = String.Empty
        Dim Index As Integer = 0
        Dim tempath As String
        Try
            If MessageBox.toYesNo(gstrExportMessage) = DialogResult.Yes Then
                GridView = grdReport
                Title = lobjDynamicReports.gstrReportsName


                SaveFileDialog1.Filter = "Excel (.xlsx)|*.xlsx" ' |Pdf Files (.Pdf)|*.pdf"
                SaveFileDialog1.FileName = Title.Replace(" ", String.Empty).Replace("/", "") & "_" & gdtServerDate.ToString("ddMMyyyy") & Now.Hour & Now.Minute & Now.Second

                If SaveFileDialog1.ShowDialog = System.Windows.Forms.DialogResult.OK Then

                    Index = SaveFileDialog1.FileName.LastIndexOf(".")
                    Extension = SaveFileDialog1.FileName.Substring(Index)

                   

                                         Select Case Extension.ToUpper
                            Case ".PDF"
                                Dim exporter As Telerik.WinControls.Export.GridViewPdfExport = New Telerik.WinControls.Export.GridViewPdfExport(GridView) With {.HiddenColumnOption = UI.Export.HiddenOption.DoNotExport, .HiddenRowOption = UI.Export.HiddenOption.DoNotExport, .ShowHeaderAndFooter = True, .SummariesExportOption = UI.Export.SummariesOption.ExportAll, .ExportVisualSettings = True, .FitToPageWidth = True}
                                exporter.RunExport(SaveFileDialog1.FileName, New Telerik.WinControls.Export.PdfExportRenderer())

                            Case ".XLSX"
                                Dim exporter As Telerik.WinControls.Export.GridViewSpreadExport = New Telerik.WinControls.Export.GridViewSpreadExport(GridView) With {.HiddenColumnOption = UI.Export.HiddenOption.DoNotExport, .HiddenRowOption = UI.Export.HiddenOption.DoNotExport, .SummariesExportOption = UI.Export.SummariesOption.ExportAll, .ExportVisualSettings = True}
                                '  AddHandler exporter.CellFormatting, AddressOf spreadExporter_CellFormatting
                                exporter.RunExport(SaveFileDialog1.FileName, New Telerik.WinControls.Export.SpreadExportRenderer())

                        End Select
                 
                    GridView.Columns(0).IsVisible = True
                    MessageBox.toInfoMessage("Exported Successfully" & vbNewLine & "Path: " & SaveFileDialog1.FileName)
                    GridView.Focus()

                End If

            End If
Brian
Top achievements
Rank 1
Iron
Iron
commented on 26 Aug 2022, 10:21 AM

Thanks, I am using C#. the portion of the answer I really need to figure out is Freeze Top Row as part of the export.

I can export the data fine, however I am not seeing a way to Freeze Top Row  or Freeze any rows for that matter.

Arun
Top achievements
Rank 1
Iron
commented on 26 Aug 2022, 11:00 AM | edited

hii , 

Try to open the saved excel  file, do the excel modification and save the  file with  new name

How to Freeze Top Row and Apply Filter in Excel Automation with C#

 

Regards

Arun

Brian
Top achievements
Rank 1
Iron
Iron
commented on 26 Aug 2022, 12:13 PM

Thanks
Tags
GridView
Asked by
Brian
Top achievements
Rank 1
Iron
Iron
Answers by
Dimitar
Telerik team
Arun
Top achievements
Rank 1
Iron
Share this question
or