Venkat Vangalapudi's Technology Blog: Creating and Sending an Excel Work book with more than one Work sheet as a Response.

I came across a task that I need to send an Excel work book with more than one sheet as a response. Here My Excel work book is having a table structured data in all sheets, so each sheet uses same set of styles defined globally. The number of sheets are determined dynamically at run time based on the data. So I followed the below approach and able to complete the task. May be it will help those who are searching to accomplish the same task.

Steps:

1) Save the given Excel work book Template (or the given sample one) as XML document using Save As command of Excel.

2) Open the XML file in an Editor (you can use Visual Studio)

3) If we observe XML of the Excel document, we can see, the inner structure of Our Excel work book in XML.

<?xml version="1.0"?>

<?mso-application progid="Excel.Sheet"?>

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:o="urn:schemas-microsoft-com:office:office"

xmlns:x="urn:schemas-microsoft-com:office:excel"

xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"

xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:html="http://www.w3.org/TR/REC-html40">

</DocumentProperties>

</CustomDocumentProperties>

</OfficeDocumentSettings>

<ProtectStructure>False</ProtectStructure>

<ProtectWindows>False</ProtectWindows>

</ExcelWorkbook>

</Style>

<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2"

ss:Color="#EBEBEB"/>

</Borders>

</Style>

</Style>

Styles…follows here…! I have removed some of the styles, for space concern.

</Styles>

[WorkSheets]

</Workbook>

Here [WorkSheets] is a place holder to place Excel work sheets. I have extracted this XML into a separate xml file, like this I have created four xml templates for Excel table row and Excel content row as well. In each xml file I have added place holders (Ex:[MetricGroup] ) You better try to Save an Existing Excel sheet into XML format and check how the sheets are placed in between.

4) After that I have created an HTTP Handler (We need this on a SharePoint site), or else you can place your logic in an aspx file or elsewhere ever you need it.

Below is my code. I wrote it according to my needs, you can modify the below logic according to your requirements, but the basic functionality is same.

void IHttpHandler.ProcessRequest(HttpContext context)

{

string qsKpiID = context.Request.QueryString["k_id"];

int kpiID;

if (string.IsNullOrEmpty(qsKpiID))

return;

if (!int.TryParse(qsKpiID, out kpiID))

return;

context.Response.ContentType = "application/ms-excel";

context.Response.AddHeader("content-disposition", string.Format("attachment;filename=ExcelExport{0}.xls", DateTime.Now.ToString("MM_dd_yyyy_hh_mm")));

string workSheetTemplate = File.ReadAllText(context.Server.MapPath("~/_controltemplates/15/Export.BI.Controls/ExcelSheet.xml"));

string workBookTemplate = File.ReadAllText(context.Server.MapPath("~/_controltemplates/15/Export.BI.Controls/ExcelWorkloadPage.xml"));

string headRow = File.ReadAllText(context.Server.MapPath("~/_controltemplates/15/Export.BI.Controls/ExcelHeadRow.xml"));

string contentRow = File.ReadAllText(context.Server.MapPath("~/_controltemplates/15/Export.BI.Controls/ExcelContentRow.xml"));

MetricManager mgr = new MetricManager();

var metricSummaryData = mgr.GetTotalSummary(kpiID);

var metricGroups = mgr.GetMetricGroups(kpiID);

StringBuilder excelSheetsStr = new StringBuilder();

string newCellTemplate = "<Cell ss:StyleID=\"s57\"><Data ss:Type=\"Number\">{0}</Data></Cell>";

string newDateCellTemplate = "<Cell ss:StyleID=\"s58\"><Data ss:Type=\"DateTime\">{0:yyyy-MM-dd}T00:00:00.000</Data></Cell>";

string emptyCell = "<Cell ss:StyleID=\"s57\"/>";

foreach (var metricGroup in metricGroups)

{

var metricGroupData = metricSummaryData.Where(m => m.MetricGroupID == metricGroup.ID).ToList();

var metricTypes= mgr.GetMetricTypes();

string excelSheetStr = workSheetTemplate.Replace("[MetricGroup]", metricGroup.Name);

StringBuilder excelRows = new StringBuilder();

int expandedRowCount = 2;

int expandedColumnCount = 9;

foreach (var metricType in metricTypes)

{

excelRows.Append(headRow.Replace("[Metric Type]",metricType.Name));

expandedRowCount++;

foreach (var item in metricGroupData.Where(m => m.MetricTypeID == metricType.ID))

{

string newRow = contentRow.Replace("[MetricName]", item.Name);

newRow = newRow.Replace("[Target]", item.Target != null ?string.Format(newCellTemplate,item.Target) : emptyCell);

newRow = newRow.Replace("[Baseline]", item.BaseLine != null ? string.Format(newCellTemplate, item.BaseLine) : emptyCell);

newRow = newRow.Replace("[Target Date]", item.TargetDate != null ? string.Format(newDateCellTemplate, item.TargetDate) : emptyCell);

newRow = newRow.Replace("[Previous Month]", item.Previous != null ? string.Format(newCellTemplate, item.Previous) : emptyCell);

double itemActual = 0;

newRow = newRow.Replace("[Current]", item.Actual != null ? item.Actual : "");

if (double.TryParse(item.Actual, out itemActual))

{

newRow = newRow.Replace("[CurrentDataType]", "Number");

}

else

{

newRow = newRow.Replace("[CurrentDataType]", "String");

}

if(item.Result!=null && item.Result !="G")

newRow = newRow.Replace("s66", item.Result=="R"?"s76":"s75");

newRow = newRow.Replace("[Target YTD]", item.TotalTarget != null ? string.Format(newCellTemplate, item.TotalTarget) : emptyCell);

newRow = newRow.Replace("[Actual YTD]", item.TotalActual != null ? string.Format(newCellTemplate, item.TotalActual) : emptyCell);

excelRows.Append(newRow);

expandedRowCount++;

//break;

}

//break;

}

excelSheetStr = excelSheetStr.Replace("[ContentRows]", excelRows.ToString());

excelSheetStr = excelSheetStr.Replace("[LastUpdated]", string.Format("last update {0:MM/dd}", (from d in metricGroupData select d.DateCreated).Max().Value));

excelSheetStr = excelSheetStr.Replace("[ExpandedRowCount]", expandedRowCount.ToString());

excelSheetStr = excelSheetStr.Replace("[ExpandedColumnCount]", expandedColumnCount.ToString());

excelSheetsStr.Append(excelSheetStr);

}

workBookTemplate = workBookTemplate.Replace("[WorkSheets]", excelSheetsStr.ToString());

context.Response.Write(workBookTemplate);

context.Response.End();

}

Tuesday, August 06, 2013

Creating and Sending an Excel Work book with more than one Work sheet as a Response.

Subscribe To