AUTHOR: Peter Milchev
DATE POSTED: November 27, 2018
Server-side export Spreadsheet to Excel using Document Processing.
In order to use the exporting capabilities of the DocumentProcessing library, we need to create a Telerik Ajax Workbook, populate it with the Telerik Ajax sheets and then convert the Telerik Workbook to DocumentProcessingLibrary workbook by using the .ToDocument() method of the Telerik Ajax Workbook.
<
telerik:RadButton
runat
=
"server"
ID
"RadButton1"
Text
"Save Excel server-side"
OnClick
"RadButton1_Click"
AutoPostBack
"true"
/>
telerik:RadSpreadsheet
"RadSpreadsheet1"
></
>
using
System;
System.Data;
System.Collections.Generic;
System.IO;
// using alias directive https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/using-directive
Ajax = Telerik.Web.Spreadsheet;
DplSpreadsheet = Telerik.Windows.Documents.Spreadsheet;
protected
void
RadButton1_Click(
object
sender, EventArgs e)
{
Ajax.Workbook ajaxWorkbook =
new
Ajax.Workbook();
// populate the ajax workbook with dummy data
ajaxWorkbook.Sheets = GetSheets();
// alternatively, the ajax workbook sheets can be populated from the Sheets of the Spreadsheet
#region Populate sheets from RadSpreadsheet
// if the Spreadsheet is using a provider, you can call the .DataBind method before accessing the Spreadsheet sheets
// RadSpreadsheet1.DataBind();
// ajaxWorkbook.Sheets = RadSpreadsheet1.Sheets;
// Also, you can just call the GetSheets() method of the Spreadsheet provider directly
// ajaxWorkbook.Sheets = RadSpreadsheet1.Provider.GetSheets();
#endregion
// convert ajax workbook to DPL workbook
DplSpreadsheet.Model.Workbook dplWorkbook = ajaxWorkbook.ToDocument();
DplSpreadsheet.FormatProviders.IWorkbookFormatProvider formatProvider =
DplSpreadsheet.FormatProviders.OpenXml.Xlsx.XlsxFormatProvider();
(MemoryStream output =
MemoryStream())
// export DPL workbook to MemoryStream
formatProvider.Export(dplWorkbook, output);
#region Send a file to the client
Response.Clear();
// Add a MemoryStream to the Response
// https://stackoverflow.com/questions/13779139/writing-memorystream-to-response-object
Response.BinaryWrite(output.ToArray());
// Set correct Content type for Excel files
// https://www.codeproject.com/Questions/481262/contentplustypeplusforplusxlsxplusfile
Response.ContentType =
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
;
Response.AppendHeader(
"content-disposition"
,
"attachment; filename=myfile.xlsx"
);
Response.End();
}
public
List<Ajax.Worksheet> GetSheets()
var result =
List<Ajax.Worksheet>();
// populate a DataTable with dummy data instead of from DataSource
DataTable data = GetDataSource();
var sheet =
Ajax.Worksheet();
sheet.ShowGridLines =
true
int
rowIndex = 0;
foreach
(DataRow dataRow
in
data.Rows)
var row =
Ajax.Row() { Index = rowIndex++ };
columnIndex = 0;
(DataColumn dataColumn
data.Columns)
if
(dataColumn.ColumnName ==
"ID"
)
continue
// Skip the ID column
var cellValue = dataRow[dataColumn.ColumnName];
var cell =
Ajax.Cell() { Index = columnIndex++, Value = cellValue };
row.AddCell(cell);
sheet.AddRow(row);
result.Add(sheet);
return
result;
private
DataTable GetDataSource()
DataTable dataTable =
DataTable();
dataTable.Columns.Add(
DataColumn(
"OrderID"
typeof
(
)));
"Freight"
decimal
"ShipName"
string
"ShipCountry"
dataTable.PrimaryKey =
DataColumn[] { dataTable.Columns[
] };
for
i = 0; i < 10; i++)
DataRow row = dataTable.NewRow();
row[
] = i + 1;
] = (i + 1) + (i + 1) * 0.1 + (i + 1) * 0.01;
] =
"Name "
+ (i + 1);
"Country "
dataTable.Rows.Add(row);
dataTable;
Resources Buy Try