Export DataRow from Selected Grid row to Excel on Button Click

1 posts, 0 answers
  1. Peter
    Peter avatar
    13 posts
    Member since:
    May 2016

    Posted 25 Sep 2018 Link to this post

    Requirements

    Telerik Product and Version

    UI for ASP.NET AJAX 2018 R3, Document Processing Library

    Supported Browsers and Platforms

    all browsers supported by Telerik UI for ASP.NET AJAX suite

    Components/Widgets used (JS frameworks, etc.)

    RadGrid, .NET 4.0/4.5 C#

    PROJECT DESCRIPTION



    This project shows how to: 
    <telerik:RadButton runat="server" ID="RadButton1" Text="Export Selected row" AutoPostBack="true" OnClick="RadButton1_Click" />
    <telerik:RadGrid ID="RadGrid1" runat="server"
        OnNeedDataSource="RadGrid1_NeedDataSource"
        ClientSettings-Selecting-AllowRowSelect="true">
        <MasterTableView AutoGenerateColumns="False" DataKeyNames="OrderID">
            <Columns>
                <telerik:GridBoundColumn DataField="OrderID" DataType="System.Int32"
                    HeaderText="OrderID"
                    ReadOnly="True" UniqueName="OrderID">
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn DataField="ShipName" HeaderText="ShipName"
                    UniqueName="ShipName">
                </telerik:GridBoundColumn>
            </Columns>
        </MasterTableView>
    </telerik:RadGrid>

    protected void RadGrid1_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
    {
        (sender as RadGrid).DataSource = GetGridSource();
    }
     
    private DataTable GetGridSource()
    {
        DataTable dataTable = new DataTable();
     
        dataTable.Columns.Add(new DataColumn("OrderID", typeof(int)));
        dataTable.Columns.Add(new DataColumn("OrderDate", typeof(DateTime)));
        dataTable.Columns.Add(new DataColumn("Freight", typeof(decimal)));
        dataTable.Columns.Add(new DataColumn("ShipName", typeof(string)));
        dataTable.Columns.Add(new DataColumn("ShipCountry", typeof(string)));
     
        dataTable.PrimaryKey = new DataColumn[] { dataTable.Columns["OrderID"] };
     
        for (int i = 0; i < 10; i++)
        {
            // OrderID, OrderDate, Freight, ShipName, ShipCountry,
            dataTable.Rows.Add(i + 1, DateTime.Now, (i + 1) + (i + 1) * 0.1 + (i + 1) * 0.01, "Name " + (i + 1), "Country " + (i + 1));
        }
     
        return dataTable;
    }
     
    protected void RadButton1_Click(object sender, EventArgs e)
    {
        if (RadGrid1.SelectedItems.Count > 0)
        {
            var selectedItems = RadGrid1.SelectedItems;
            var datatable = GetGridSource();
     
            // when Advanced Binding is used, another option to get the source is
            // rebinding the grid and getting its DataSource
            // RadGrid1.Rebind(); // needed to populate the DataSource property
            // var datatable = RadGrid1.DataSource as DataTable;
     
            if (datatable != null)
            {
                DplModel.Workbook workbook = new DplModel.Workbook();
                workbook.Sheets.Add(DplModel.SheetType.Worksheet);
     
                DplModel.Worksheet worksheet = workbook.Worksheets[0];
                int currentRow = 0;
     
                foreach (GridDataItem item in selectedItems)
                {
                    // get the Primary key which can be used to find the DataRow
                    var orderID = item.GetDataKeyValue("OrderID");
                    var datarows = datatable.Select(string.Format("OrderID = '{0}'", orderID)) as DataRow[];
     
                    foreach (DataRow datarow in datarows)
                    {
                        foreach (DataColumn datacolumn in datatable.Columns)
                        {
                            var value = datarow[datacolumn];
                            worksheet.Cells[currentRow, datatable.Columns.IndexOf(datacolumn)].SetValue(value.ToString());
                        }
                        currentRow++;
                    }
                }
     
                // autofit the columns
                DplModel.ColumnSelection columnSelection = worksheet.Columns[0, datatable.Columns.Count];
                columnSelection.AutoFitWidth();
     
                ExportToExcel(workbook);
            }
        }
    }
     
    private void ExportToExcel(DplModel.Workbook workbook)
    {
        Telerik.Windows.Documents.Spreadsheet.FormatProviders.IWorkbookFormatProvider formatProvider =
                    new Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.XlsxFormatProvider();
     
        using (MemoryStream output = new MemoryStream())
        {
            formatProvider.Export(workbook, output);
     
            Response.Clear();
            Response.BinaryWrite(output.ToArray());
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AppendHeader("content-disposition", "attachment; filename=myfile.xlsx");
            Response.End();
        }
    }


Back to Top

This Code Library is part of the product documentation and subject to the respective product license agreement.