DataBase provider

Environment

Product

Telerik WebForms SpreadSheet for ASP.NET AJAX

Description

Тhe article aims to demonstrate how a custom DataBase Provider can be used to connect the Spreadsheet to SqlDataSource. The example is build to use Northwind database.

Solution

Spreadsheet setup

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

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        SpreadsheetDataBaseProvider provider = new SpreadsheetDataBaseProvider();
        RadSpreadsheet1.Provider = provider;
    }
}

Custom provider

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

public class SpreadsheetDataBaseProvider : SpreadsheetProviderBase
{
    public SpreadsheetDataBaseProvider()
        : base()
    {
    }

    public override void SaveWorkbook(Workbook workbook)
    {
        using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))
        {
            conn.Open();
            string query = "DELETE FROM [Products]";
            SqlCommand clearCmd = new SqlCommand(query, conn);
            clearCmd.ExecuteNonQuery();

            string insertQuery = "INSERT INTO Products(ProductName,Description) VALUES(@param1,@param2)";
            foreach (var row in workbook.Sheets[0].Rows)
            {
                SqlCommand insertCmd = new SqlCommand(insertQuery, conn);

                insertCmd.Parameters.Add("@param1", SqlDbType.NVarChar, 50).Value = row.Cells[0].Value;
                insertCmd.Parameters.Add("@param2", SqlDbType.NVarChar, 50).Value = row.Cells[1].Value;

                insertCmd.CommandType = CommandType.Text;
                insertCmd.ExecuteNonQuery();
            }
        }
    }

    public override List<Worksheet> GetSheets()
    {
        var result = new List<Worksheet>();

        DataTable data = GetData();

        var sheet = new Worksheet();

        int rowIndex = 0;
        foreach (DataRow dataRow in data.Rows)
        {
            var row = new Row() { Index = rowIndex++ };

            int columnIndex = 0;
            foreach (DataColumn dataColumn in data.Columns)
            {
                if (dataColumn.ColumnName == "ID") continue; // Skip the ID column

                var cellValue = dataRow[dataColumn.ColumnName];

                var cell = new Cell() { Index = columnIndex++, Value = cellValue };

                row.AddCell(cell);
            }

            sheet.AddRow(row);
        }

        result.Add(sheet);

        return result;
    }

    public DataTable GetData()
    {
        using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))
        {
            conn.Open();
            string query = "SELECT * FROM [Products]";
            SqlCommand cmd = new SqlCommand(query, conn);

            DataTable dt = new DataTable();
            dt.Load(cmd.ExecuteReader());
            return dt;
        }
    }
}

Imports System.Data
Imports Telerik.Web.UI
Imports Telerik.Windows.Documents.Flow.FormatProviders.Docx
Imports Telerik.Windows.Documents.Flow.FormatProviders.Html
Imports Telerik.Windows.Documents.Flow.Model
Imports System.IO
Imports Telerik.Windows.Documents.Common.FormatProviders
Imports Telerik.Windows.Documents.Primitives
Imports Telerik.Windows.Documents.Model

Partial Class Cases_Grid_Exporting_1145130_Formatting_Word_Docx
    Inherits System.Web.UI.Page

    Protected Sub RadGrid1_NeedDataSource(sender As Object, e As GridNeedDataSourceEventArgs)
        RadGrid1.DataSource = GetGridSource()
    End Sub
    Private Function GetGridSource() As DataTable
        Dim dataTable As New DataTable()

        Dim column As New DataColumn()
        column.DataType = Type.[GetType]("System.Int32")
        column.ColumnName = "OrderID"
        dataTable.Columns.Add(column)

        column = New DataColumn()
        column.DataType = Type.[GetType]("System.DateTime")
        column.ColumnName = "OrderDate"
        dataTable.Columns.Add(column)

        column = New DataColumn()
        column.DataType = Type.[GetType]("System.Decimal")
        column.ColumnName = "Freight"
        dataTable.Columns.Add(column)

        column = New DataColumn()
        column.DataType = Type.[GetType]("System.String")
        column.ColumnName = "ShipName"
        dataTable.Columns.Add(column)

        column = New DataColumn()
        column.DataType = Type.[GetType]("System.String")
        column.ColumnName = "ShipCountry"
        dataTable.Columns.Add(column)

        Dim PrimaryKeyColumns As DataColumn() = New DataColumn(0) {}
        PrimaryKeyColumns(0) = dataTable.Columns("OrderID")
        dataTable.PrimaryKey = PrimaryKeyColumns

        For i As Integer = 0 To 4
            Dim row As DataRow = dataTable.NewRow()
            row("OrderID") = i + 1
            row("OrderDate") = DateTime.Now
            row("Freight") = (i + 1) + (i + 1) * 0.1 + (i + 1) * 0.01
            row("ShipName") = "<i>ShipName</i>"
            row("ShipCountry") = "<a href=""javascript:void(0)"">ShipCountry</a>"

            dataTable.Rows.Add(row)
        Next

        Return dataTable
    End Function

    Protected Sub RadGrid1_GridExporting(sender As Object, e As GridExportingArgs)
        If (e.ExportType = ExportType.Word) Then
            '' Create a RadFlowDocument object
            Dim document As RadFlowDocument = New RadFlowDocument()

            '' Importing the HTML output from the grid into the htmlProvider
            Dim htmlProvider As HtmlFormatProvider = New HtmlFormatProvider()
            document = htmlProvider.Import(e.ExportOutput)

            Dim data As Byte() = Nothing

            '' Changing the page orientation of all sections in a document
            For Each section As Section In document.EnumerateChildrenOfType(Of Section)()
                section.Rotate(PageOrientation.Landscape)
            Next

            'Dim iProvider As IFormatProvider(Of RadFlowDocument) = docxProvider

            '' export the final document (docx) into the MemoryStream
            Using ms As New MemoryStream()
                Dim docxProvider As DocxFormatProvider = New DocxFormatProvider()
                docxProvider.Export(document, ms)
                data = ms.ToArray() '' get the byte data of the document
            End Using

            '' send the data in the response for download
            Response.ContentType = "application/vnd.openxmlformats-officedocument.wordprocessingml.document"
            Response.Headers.Remove("Content-Disposition")
            Response.AppendHeader("Content-Disposition", "attachment; filename=" & RadGrid1.ExportSettings.FileName & ".docx")
            Response.BinaryWrite(data)
            Response.End()
        End If
    End Sub
    Protected Sub rbExportClearanceRptAsPDF_Click(sender As Object, e As EventArgs)
        RadGrid1.ExportToPdf()
    End Sub

    Protected Sub rbExportClearanceRptAsWord_Click(sender As Object, e As EventArgs)
        RadGrid1.ExportToWord()
    End Sub

    Protected Sub RadGrid1_PreRender(sender As Object, e As EventArgs)
        If RadGrid1.IsExporting Then
            '' Setting borders for the Header Cells
            For Each headerCell As TableHeaderCell In RadGrid1.MasterTableView.GetItems(GridItemType.Header)(0).Cells
                headerCell.Style("border") = "1px solid"
            Next
            '' Setting borders for the data cells
            For Each dataItem As GridDataItem In RadGrid1.Items
                For Each cell As GridTableCell In dataItem.Cells
                    cell.Style("border") = "1px solid"
                Next
            Next
        End If
    End Sub
End Class