Server-Side Processing

To export huge datasets to Excel, you can use the RadSpreadStreamProcessing library which is part of Telerik Document Processing (TDP) by Progress.

The Telerik Document Processing libraries are distributed as part of the Telerik UI for ASP.NET MVC and are available for the Telerik UI Enterprise and DevCraft bundles.

For examples on how to export Excel files, refer to the RadSpreadProcessing library.

TDP handles the data import, export, and processing from the following formats:

  • Excel Microsoft Office Open XML Spreadsheet (.xlsx)
  • Comma-separated values (.csv)
  • Tab-separated values (.txt)
  • Portable document format (.pdf) (export only)

Dependencies

The .NET server-side module is distributed as a part of the Telerik UI for ASP.NET MVC bundle. The telerik.ui.for.aspnetmvc.<version>.zip archive contains a spreadsheet folder that contains assemblies for both .NET 4.0 and .NET 4.5 versions. Include a reference to the Telerik.Web.Spreadsheet.dll assembly for the respective framework version. The main entry point for the project is the Telerik.Web.Spreadsheet.Workbook class. This is a POCO object that mirrors the object structure of the JSON and provides format conversion methods. Under the hood, it uses TDP to perform the actual conversion.

No strict dependency on the type of the server framework that is used exists. Popular choices, such as MVC, WebAPI, and WebForms, work equally well.

Loading Data from External Files

The following example demonstrates how to load a file from the file system and convert it to a Workbook for serialization.

The supported file extensions are:

  • .xlsx
  • .csv
  • .txt
  • .json
public class HomeController : Controller
{
    public ActionResult Read()
    {
        var path = Server.MapPath("~/App_Data/path/to/document.xlsx");
        var workbook = Telerik.Web.Spreadsheet.Workbook.Load(path);

        //Uses Newtonsoft.Json internally to serialize fields correctly.
        return Content(workbook.ToJson(), Telerik.Web.Spreadsheet.MimeTypes.JSON);
    }
}
@(Html.Kendo().Spreadsheet()
    .Name("spreadsheet")
)

<script>
    $(document).ready(function () {
        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");

        $.getJSON("@Url.Action("Read", "Home")")
        .done(function (data) {
            spreadsheet.fromJSON(data);
        });
    });
</script>

Loading Data from External Files by Using BindTo Method

The following example demonstrates how to load a file from the file system and use it to populate the Spreadsheet.

The supported file extensions are:

  • .xlsx
  • .csv
  • .txt
  • .json

    @{ var path = Server.MapPath("~/App_Data/path/to/document.xlsx"); var workbook = Telerik.Web.Spreadsheet.Workbook.Load(path); }

    @(Html.Kendo().Spreadsheet() .Name("spreadsheet") .BindTo(workbook) )

Loading Data by Using BindTo and Document Model

The following example demonstrates how to load the data by using the BindTo method and the Spreadsheet document model.

    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            var document = new Telerik.Windows.Documents.Spreadsheet.Model.Workbook();
            var worksheet = document.Worksheets.Add();
            worksheet.Cells[0, 0].SetValue("1.23");
            worksheet.Name = "Worksheet 1";

            return View(Telerik.Web.Spreadsheet.Workbook.FromDocument(document));
        }
    }
@model Telerik.Web.Spreadsheet.Workbook

@(Html.Kendo().Spreadsheet()
    .Name("spreadsheet")
    .BindTo(Model)
);

Saving Workbooks to External Files

The following example demonstrates how to post a workbook to a controller and save it as a local file.

The supported file extensions are:

  • .xlsx
  • .csv
  • .txt
  • .pdf
  • .json

Set aspnet:MaxJsonDeserializerMembers to a higher value than the default one in the web.config.

[HttpPost]
public ActionResult Save(Telerik.Web.Spreadsheet.Workbook workbook)
{
    workbook.Save("path/to/document.xlsx");
    return new EmptyResult();
}
@(Html.Kendo().Spreadsheet()
    .Name("spreadsheet")
)

<button id="save">Save</button>
<script>
    $("#save").click(function () {
        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        $.ajax({
            url: "@Url.Action("Save", "Home")",
            data: JSON.stringify(spreadsheet.toJSON()),
            contentType: "application/json",
            type: "POST"
        });
    });
</script>
<configuration>
  <appSettings>
    <add key="aspnet:MaxJsonDeserializerMembers" value="150000" />
  </appSettings>
</configuration>

Converting TDP Documents to Workbooks

Telerik Document Processing provides a full-blown model for a Spreadsheet document. To display the result in a Spreadsheet, convert it to Telerik.Web.Spreadsheet.Workbook.

var document = new Telerik.Windows.Documents.Spreadsheet.Model.Workbook();
var worksheet = document.Worksheets.Add();
worksheet.Cells[0, 0].SetValue("1.23");

return Telerik.Web.Spreadsheet.Workbook.FromDocument(document);

Converting Workbooks to TDP Documents

You can convert a Telerik.Web.Spreadsheet.Workbook Spreadsheet model and to a TDP document and then process it further and convert and store it as needed.

[HttpPost]
public ActionResult Process(Telerik.Web.Spreadsheet.Workbook workbook)
{
    var document = workbook.ToDocument();

    // Continue with the TDP API as usual.
    var worksheet = document.ActiveWorksheet;
    var A1Cell = new CellIndex(0, 0);
    var B2Cell = new CellIndex(1, 1);

    worksheet.Cells[A1Cell, B2Cell].Merge();

    return new EmptyResult();
}

See Also

In this article
Not finding the help you need? Improve this article