Import data from excel

Environment

Product Version	Product	Author
2018.3.1016	RadGridView for WinForms	Dimitar Karamfilov

Description

While there are several ways to export the data from RadGridView, there is no direct way to import excel data.

Solution

Use the RadSpreadProcessing library to read the file and import the data from it. The example assumes that the first line contains the column names and below them is the data.

In order to use the model of the RadSpreadProcessing library in your project, you need to add references to the following assemblies: Assembly References.

Create Data table from an Excel file

private void radButton1_Click(object sender, EventArgs e)
{
    XlsxFormatProvider formatProvider = new XlsxFormatProvider();
    Workbook workbook = formatProvider.Import(File.ReadAllBytes(@"D:\Book1.xlsx"));

    var worksheet = workbook.Sheets[0] as Worksheet;
    var table = new DataTable();


    for (int i = 0; i < worksheet.UsedCellRange.ColumnCount; i++)
    {
        CellSelection selection = worksheet.Cells[0, i];
        var columnName = selection.GetValue().Value.RawValue.ToString();

        table.Columns.Add(columnName);
    }


    for (int i = 1; i < worksheet.UsedCellRange.RowCount; i++)
    {
        var values = new object[worksheet.UsedCellRange.ColumnCount];

        for (int j = 0; j < worksheet.UsedCellRange.ColumnCount; j++)
        {
            CellSelection selection = worksheet.Cells[i, j];

            ICellValue value = selection.GetValue().Value;
            CellValueFormat format = selection.GetFormat().Value;
            CellValueFormatResult formatResult = format.GetFormatResult(value);
            string result = formatResult.InfosText;

            values[j] = result;
        }
        table.Rows.Add(values);

    }

    radGridView1.DataSource = table;

}

This functionality is now available out-of-the-box with the Document Processing's DataTableFormatProvider API.