Import datatable into telerik:RadSpreadsheet

[Bernie]

I have a datatable in memory that I would like to display in a telerik:RadSpreadsheet. I know I can iterate through the datatable and build a sheet to display. 

I'm trying to figure out how to use the DataTableFormatProvider to load the datatable in one shot. I found examples of how to do with the Telerik.Windows.Documents spreadsheet, but how do I do  it with the RadSpreadsheet model?

 

 

[Vasko]

Hi Bernie,

I hope you're doing well!

Here is an example demonstrating the following:

• Uploading an Excel file to a network-shared location using AsyncUpload
• Loading the Excel file from the network-shared location into the RadSpreadsheet component
• Importing the Excel from the network-shared location into a Workbook() object
• Exporting Workbook() object to DataTable
• Binding DataTable to RadGrid
  

You can copy/paste this code and will work except you will need to update the "TargetFolder" property to point to a location of your choice.

I have also passed a few links to the official documentation articles if you want to learn more about them.

<h3>Uploading Excel File with AsyncUpload</h3>
<telerik:RadAsyncUpload ID="RadAsyncUpload1" runat="server" TargetFolder="\\yourNetwork\location\\"
    PostbackTriggers="RadButton1" AllowedFileExtensions=".xlsx" OnFileUploaded="RadAsyncUpload1_FileUploaded"></telerik:RadAsyncUpload>
<telerik:RadButton runat="server" ID="RadButton1" Text="Upload" AutoPostBack="true" OnClick="RadButton1_Click" />

<h3>Loading Excel File into RadSpreadsheet Component</h3>
<telerik:RadSpreadsheet runat="server" ID="RadSpreadsheet1" Width="800px"/>

<h3>Binding Excel Content to RadGrid</h3>
<telerik:RadGrid ID="RadGrid1" runat="server" AllowPaging="True" Width="800px" OnNeedDataSource="RadGrid1_NeedDataSource">
    <MasterTableView AutoGenerateColumns="true">
    </MasterTableView>
</telerik:RadGrid>

string uploadTargetFolder { get; set; }
string uploadedFileName { get; set; }

protected void RadAsyncUpload1_FileUploaded(object sender, FileUploadedEventArgs e)
{
    RadAsyncUpload asyncUpload = (RadAsyncUpload)sender;

    uploadTargetFolder = asyncUpload.TargetFolder;
    uploadedFileName = e.File.FileName;
}

protected void RadGrid1_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
{
    (sender as RadGrid).DataSource = null;
}

protected void RadButton1_Click(object sender, EventArgs e)
{
    string fileName = string.Format("{0}{1}", uploadTargetFolder, uploadedFileName);

    if(string.IsNullOrEmpty(fileName) || !File.Exists(fileName))
    {
        return;
    }

    // Binding to a provider at runtime
    // https://docs.telerik.com/devtools/aspnet-ajax/controls/spreadsheet/data-binding/providers/using-a-data-provider#binding-to-a-provider-at-runtime
    RadSpreadsheet1.Provider = new SpreadsheetDocumentProvider(fileName);

    // Import Excel into a Workbook() object
    // https://docs.telerik.com/devtools/document-processing/libraries/radspreadprocessing/formats-and-conversion/import-and-export-to-excel-file-formats/xlsx/xlsxformatprovider#import

    Telerik.Windows.Documents.Spreadsheet.Model.Workbook workbook;
    IWorkbookFormatProvider formatProvider = new Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.XlsxFormatProvider();

    using (Stream input = new FileStream(fileName, FileMode.Open))
    {
        workbook = formatProvider.Import(input);
    }

    // Export Wokbook() into a DataTable object
    // https://docs.telerik.com/devtools/document-processing/libraries/radspreadprocessing/formats-and-conversion/data-table/using-data-table-format-provider#export
    DataTableFormatProvider provider = new DataTableFormatProvider();


    DataTable table = provider.Export(workbook.ActiveWorksheet);

    // Finally update the Grid's source
    RadGrid1.DataSource = table;
    // Rebind the Grid
    RadGrid1.Rebind();
}

Please let me know if you have any questions.

Regards,
Vasko
Progress Telerik

[Bernie]

We have missed the final point. We have to display the datatable on the webpage in a <Telerik:RadSpreadsheet> control, not a datagrid control.

 I’m looking for a simple way to display a datatable in a RadSpreadsheet control. Currently I have to iterate through all the data building the sheet manually. Is there a simple datatable to RadSpreadsheet control method?

 

Bernie

[Vasko]

Hello Bernie,

If you wish to display a DataTable in the Spreadsheet, you would need a custom Spreadsheet Provider that converts the DataTable cells/rows to be compatible with the Spreadsheet: 

<telerik:RadSpreadsheet runat="server" ID="RadSpreadsheet1" />

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        DataTable dt = GetSampleData();
        RadSpreadsheet1.Provider = new DataTableSpreadsheetProvider(dt);
    }
}

private DataTable GetSampleData()
{
    DataTable table = new DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Name", typeof(string));
    table.Columns.Add("Age", typeof(int));

    table.Rows.Add(1, "Alice", 25);
    table.Rows.Add(2, "Bob", 30);
    table.Rows.Add(3, "Charlie", 22);

    return table;
}

using System;
using System.Collections.Generic;
using System.Data;
using Telerik.Web.Spreadsheet;
using Telerik.Web.UI;

public class DataTableSpreadsheetProvider : SpreadsheetProviderBase
{
    private DataTable _dataTable;

    public DataTableSpreadsheetProvider(DataTable dataTable)
    {
        _dataTable = dataTable;
    }

    public override List<Worksheet> GetSheets()
    {
        List<Worksheet> sheets = new List<Worksheet>();
        Worksheet sheet = new Worksheet { Name = "Sheet1" };

        List<Row> rows = new List<Row>();

        // Add header row
        Row headerRow = new Row();

        headerRow.Cells = new List<Cell>();

        foreach (DataColumn column in _dataTable.Columns)
        {
            Cell cell = new Cell()
            {
                Value = column.ColumnName,
                Bold = true
            };

            headerRow.Cells.Add(cell);
        }

        rows.Add(headerRow);

        // Add data rows
        foreach (DataRow row in _dataTable.Rows)
        {
            Row sheetRow = new Row();
            sheetRow.Cells = new List<Cell>();

            foreach (object cellValue in row.ItemArray)
            {
                Cell cell = new Cell
                {
                    Value = cellValue.ToString()
                };

                sheetRow.Cells.Add(cell);
            }

            rows.Add(sheetRow);
        }

        sheet.Rows = rows;
        sheets.Add(sheet);

        return sheets;
    }

    public override void SaveWorkbook(Workbook workbook)
    {
        // This method is required by SpreadsheetProviderBase but is not needed for read-only display.
        throw new NotImplementedException();
    }
}

Try this and see if it will help in your case.

Regards,
Vasko
Progress Telerik

[Bernie]

Vasko,

Got it. So there is no built in function to load the table into a sheet. I was hoping to replace my method that walks through the table to a single function built into the spreadsheet control to shorten my code.

Thank you!

Bernie