Custom Action Method for Export to Excel

Today we will look at how to build Export to Excel functionality by deriving new type from ActionResult class.

To get started, lets setup following:

  1. An ASP.NET MVC project
  2. New controller with name "ExcelController.cs" inside controller folder
  3. ConnectionString to any database (in this one I am using Products table of Adventure works database)

Once initial setup is done, create a new class inside your project's "Models" folder with name "ExcelResult.cs"

(Note: To keep things simple we are creating this class in same project but you can set this up in a separate class library as well just make sure you add reference to System.Web.Mvc in class library project.)

After creating new class file there are few things that we will do:

  1. Create two private member variables
    1. One to hold data (in the form of DataTable)
    2. Second to hold name of excel file
  2. Create default constructor accepting two parameters to populate our private member variables
    1. DataTable
    2. String
  3. Override ExecuteResult method of ActionResult class to define our functionality
  4. Create four(4) methods to setup Excel file:
    1. GenerateInitialExcel: for the part of excel which include namespace, document properties, protection structure etc.
    2. GenerateExcelStyle: for any style you need to create to apply on cells
    3. GenerateExcelData: for adding rows and cells to the sheet (this is where we will use data in DataTable to integrate through and populate cells)
    4. GenerateExcelEnd: for generating end of excel which includes closing tags, print information etc.

Code for ExcelResult.cs:

public class ExcelResult : ActionResult

{

private DataTable _data;

private string _fileName;

 

public ExcelResult(DataTable dt,

string fileName)

{

_data = dt;

_fileName = fileName;

}

 

public override void ExecuteResult(ControllerContext context)

{

if (context==null)

{

throw new ArgumentNullException("context");

}

 

HttpResponseBase response = context.HttpContext.Response;

response.ContentType = "application/vnd.ms-excel";

response.AppendHeader("Content-Disposition","attachment;filename="+_fileName+".xls");

 

response.Write(GenerateInitialExcel());

response.Write(GenerateExcelStyle());

response.Write(GenerateExcelData());

response.Write(GenerateExcelEnd());

 

response.End();

}

 

private string GenerateExcelData()

{

int rowCount = _data.Rows.Count;

int columnCount = _data.Columns.Count;

StringBuilder excelData = new StringBuilder();

excelData.Append("<Worksheet ss:Name=\"Sheet1\">");

excelData.Append(" <Table ss:ExpandedColumnCount=\"" + columnCount + "\" ss:ExpandedRowCount=\""+(rowCount+1)+"\" x:FullColumns=\"1\" x:FullRows=\"1\">");

excelData.Append("<Row>");

for (int k = 0; k < columnCount; k++)

{

excelData.Append("<Cell ss:StyleID=\"s21\">");

excelData.Append("<Data ss:Type=\"String\">");

excelData.Append(_data.Columns[k].Caption.ToString());

excelData.Append("</Data>");

excelData.Append("</Cell>");

}

excelData.Append("</Row>");

for (int i = 0; i < rowCount; i++)

{

excelData.Append("<Row>");

 

for (int j = 0; j < columnCount; j++)

{

excelData.Append("<Cell>");

excelData.Append("<Data ss:Type=\"String\">");

excelData.Append(_data.Rows[i][j].ToString());

excelData.Append("</Data>");

excelData.Append("</Cell>");

}

excelData.Append("</Row>");

}

 

excelData.Append("</Table>");

return excelData.ToString();

}

 

private string GenerateExcelEnd()

{

StringBuilder excelEnd = new StringBuilder();

excelEnd.Append("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");

excelEnd.Append("<Print><ValidPrinterInfo /><HorizontalResolution>600</HorizontalResolution>");

excelEnd.Append("<VerticalResolution>600</VerticalResolution></Print>");

excelEnd.Append("<Selected />");

excelEnd.Append("<Panes><Pane><Number>3</Number><ActiveRow>5</ActiveRow>");

excelEnd.Append("<ActiveCol>1</ActiveCol></Pane></Panes>");

excelEnd.Append("<ProtectObjects>False</ProtectObjects>");

excelEnd.Append("<ProtectScenarios>False</ProtectScenarios>");

excelEnd.Append("</WorksheetOptions></Worksheet>");

excelEnd.Append("</Workbook>");

return excelEnd.ToString();

}

 

private string GenerateExcelStyle()

{

StringBuilder excelStyle = new StringBuilder();

excelStyle.Append("<Styles>");

excelStyle.Append(" <Style ss:ID=\"Default\" ss:Name=\"Normal\">");

excelStyle.Append("<Alignment ss:Vertical=\"Bottom\" />");

excelStyle.Append("<Borders /> <Font /><Interior /><NumberFormat /><Protection />");

excelStyle.Append("</Style>");

excelStyle.Append("<Style ss:ID=\"s21\"><Font x:Family=\"Swiss\" ss:Bold=\"1\" /> </Style>");

excelStyle.Append("</Styles>");

return excelStyle.ToString();

}

 

private string GenerateInitialExcel()

{

StringBuilder initSetup = new StringBuilder();

initSetup.Append("<?xml version=\"1.0\"?>");

initSetup.Append("<?mso-application progid=\"Excel.Sheet\"?>");

initSetup.Append("<Workbook ");

initSetup.Append("xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");

initSetup.Append(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");

initSetup.Append(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");

initSetup.Append(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");

initSetup.Append(" xmlns:html=\"http://www.w3.org/TR/REC-html40\">");

initSetup.Append("<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");

initSetup.Append("<Author>Anoop Tomar</Author>");

initSetup.Append("<LastAuthor>Anoop Tomar</LastAuthor>");

initSetup.Append("<Created>2012-03-15T13:04:04Z</Created>");

initSetup.Append("<Company>DevTechie.com</Company>");

initSetup.Append("<Version>11.8036</Version>");

initSetup.Append("</DocumentProperties>");

initSetup.Append("<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");

initSetup.Append("<WindowHeight>6795</WindowHeight>");

initSetup.Append("<WindowWidth>8460</WindowWidth>");

initSetup.Append("<WindowTopX>120</WindowTopX>");

initSetup.Append("<WindowTopY>15</WindowTopY>");

initSetup.Append("<ProtectStructure>False</ProtectStructure>");

initSetup.Append("<ProtectWindows>False</ProtectWindows>");

initSetup.Append("</ExcelWorkbook>");

return initSetup.ToString();

}

}

 

Next, we will add calling code to our previously created controller "ExcelController.cs".

We will add two Action Methods in this controller:

  1. Index : this will render view which contains an Action Link pointing to our Export to Excel action method.
  2. ExporttoExcel: Actual action method of type ExcelResult

ExcelController Code:

public class ExcelController : Controller

{

 

public ActionResult Index()

{

return View();

}

 

public ExcelResult ExportToExcel()

{

string sql = @"WITH PAGED_CUSTOMERS AS

(

SELECT ProductID, Name, ProductNumber, Color, StandardCost,

ROW_NUMBER() OVER (ORDER BY Name asc) AS RowNumber

FROM SalesLT.Product

)

SELECT ProductID, Name, ProductNumber, Color, StandardCost

FROM PAGED_CUSTOMERS

WHERE RowNumber BETWEEN 1 AND 200;";

DataTable dt = new DataTable();

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connString"].ConnectionString);

 

SqlDataAdapter adp = new SqlDataAdapter(sql, conn);

var row = adp.Fill(dt);

//return dt;

return new ExcelResult(dt,"Products");

}

}

 

As we can see here that we are passing DataTable and name of file in a string to ExcelResult which is writing data directly to response stream.

View associated to this controller only contains an ActionLink as shown below:

<h2>Index</h2>

<%: Html.ActionLink("Export to Excel", "ExportToExcel")%>

 

Now if we run the application in browser and click on link "Export to Excel" it should download file in Excel.

Tags: , , ,

ASP.NET | MVC