CODEWITHSUNDEEP
c#asp.net-mvc
user3799570
user3799570

Reputation: 99

Export to Excel the complete MVC View with Non-table Content

I am working on an MVC application. We have a view that has 30 different textbox/dropdown controls and 2 tables.

When user clicks on Export to Excel, I need to export all the controls and table data.

What is the best method to export these results to Excel?

I created a separate view for excel and tried to export the view to excel.

public ActionResult ExprotToExcel(int id)

{

var model = GetExportModel(id);

HttpContext.Response.AddHeader("content-disposition", "attachment; filename=XX_" + DateTime.Now.ToString() + ".xls");

      this.Response.ContentType = "application/vnd.ms-excel";
      return View(model);

}

But above code is not formatting the excel data. Currency and decimal fields are not getting formatted.

Can we format the data in the above scenario?

Please help me with the correct approach.

Upvotes: 2

Views: 3204

Answers (2)

Khizar Belim
Khizar Belim

Reputation: 26

Example of how it could be done...:

public ActionResult ExportToExcel()
{
    var products = new System.Data.DataTable("test");
    products.Columns.Add("col1", typeof(int));
    products.Columns.Add("col2", typeof(string));

    products.Rows.Add(1, "product 1");
    products.Rows.Add(2, "product 2");
    products.Rows.Add(3, "product 3");
    products.Rows.Add(4, "product 4");
    products.Rows.Add(5, "product 5");
    products.Rows.Add(6, "product 6");
    products.Rows.Add(7, "product 7");


    var grid = new GridView();
    grid.DataSource = products;
    grid.DataBind();

    Response.ClearContent();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls");
    Response.ContentType = "application/ms-excel";

    Response.Charset = "";
    StringWriter sw = new StringWriter();
    HtmlTextWriter htw = new HtmlTextWriter(sw);

    grid.RenderControl(htw);

    Response.Output.Write(sw.ToString());
    Response.Flush();
    Response.End();

    return View("MyView");
}

Upvotes: 0

MachinaGod
MachinaGod

Reputation: 26

You could use an excel library such as NPOI or Aspose to create and in memory excel book that can then be returned as a FileResult.

Below is an simple rough example of writing a collection of data rows to a workbook using NPOI that can then be returned as the file result.

    public FileResult ExportResults(int id)
    {
        var model = GetExportModel(id);
        var workbook = new HSSFWorkbook();
        var sheet = workbook.CreateSheet("TestSheet1");
        var headerRow = sheet.CreateRow(0);
        headerRow.CreateCell(0).SetCellValue("ID");
        headerRow.CreateCell(1).SetCellValue("TestDataCol1");
        headerRow.CreateCell(2).SetCellValue("TestDataCol2");
        sheet.CreateFreezePane(0, 1, 0, 1);

        int rowNumber = 1;
        foreach (var datarow in model.data)
        {
            var datestyle = SetCellStyle(workbook, "dd/MM/yyyy");
            var row = sheet.CreateRow(rowNumber++);
            row.CreateCell(0).SetCellValue(datarow.Id);
            row.CreateCell(1).SetCellValue(datarow.TestData1);
            row.CreateCell(2).SetCellValue(datarow.TestData2);

            row.Cells[1].CellStyle = datestyle;
            row.Cells[2].CellStyle = datestyle;
        }

        MemoryStream output = new MemoryStream();
        workbook.Write(output);
        return File(output.ToArray(), "application/vnd.ms-excel", "ExcelData.xls");
    }

NPOI is an open source project which can help you read/write xls, doc, ppt files.

https://npoi.codeplex.com/

Hope this helps to point you in the right direction,

Upvotes: 1

Related Questions