Resolved Data Export to Excel using HSSFWorkbook Works Locally but Not on Test

[fokwabest]

I am new to ASP.NET Core. I have written a code that works on my local computer but when I publish the app to our test environment the behavior is strange. The scenario is as follows:
When the user clicks a link, the app exports data to an excel file which is downloaded. On my PC / dev env, the data is downloaded fine and you can see it on the Excel sheet however on Test, it is rather the webpage that is on the Excel sheet hence giving a message "The file format and extension of summary.xls don't match. The file could be corrupted or unsafe" when you open the Excel file.
Find below my code:
In ReportController.cs

[HttpGet]  
public ActionResult ExportSummaryToExcel(string startDate, string endDate, string postalCode)
{
    string strStartDate = startDate;
    string strEndDate = endDate;
    string strRequestedPostalCode = postalCode;

    ViewBag.StartDate = startDate;
    ViewBag.EndDate = endDate;
    ViewBag.RequestedPostalCode = postalCode;

    DataTable rsReportDataByPostalCode = _summaryByAgeStagePostalCodeService.
        GetSummaryByPostalCode(strStartDate + " 00:00:00.000000000", strEndDate + " 23:59:59.000000000", strRequestedPostalCode);

    ReportViewModel reportViewModel = new ReportViewModel();

    reportViewModel.ReportDataByPostalCodeTable = rsReportDataByPostalCode;
   
    return View("_ExportSummaryToExcel", reportViewModel);
}

In _ExportSummaryToExcel.cshtml

@using System.Data;
@using BHTS.Service;
@using Microsoft.AspNetCore.Hosting;
@using NPOI.HSSF.UserModel;
@using NPOI.SS.UserModel;
@using NPOI.Util;
@using System.IO;
@using Microsoft.AspNetCore.Http;
@using System.Web;
@using NPOI.XSSF.UserModel;
@inject IHostingEnvironment _hostingEnvironment
@inject IHttpContextAccessor HttpContextAccessor
@model BHTS.Service.ViewModels.ReportViewModel


@{
    //Set the content type header with the razor directive
    Context.Response.ContentType = "application/vnd.ms-excel";

    // Set the content disposition header
    Context.Response.Headers.Add("Content-Disposition", "attachment; filename=\"Summary.xls\"");  
}

@{
    string strExceptionMessage ;

    try{

        string strNewPostalCode = "";

        string strRequestedPostalCode = "";        
        string strStartDate = "";
        string strEndDate = "";

        //Get the postal code, start date and end date for the requested data.
        strRequestedPostalCode = ViewBag.RequestedPostalCode;
        strStartDate = ViewBag.StartDate;
        strEndDate = ViewBag.EndDate;

        //Get the Excel worksheet that is used as the template.
       
        String strExcelTemplatePath = "";
        strExcelTemplatePath = _hostingEnvironment.ContentRootPath + "\\Views\\Reports\\SummaryByAgeStagePostalBlank.xls";
        HSSFWorkbook wb = new HSSFWorkbook(new FileStream(strExcelTemplatePath, FileMode.Open, FileAccess.ReadWrite));
       
        //Get the first sheet in the workbook.
        ISheet sheet = wb.GetSheetAt(0);

        DataTable rsReportDataByPostalCode;

        //Get the summary requested data.  
        rsReportDataByPostalCode = Model.ReportDataByPostalCodeTable;

        int rowCount = rsReportDataByPostalCode.Rows.Count;
        int currentRowNumber = 2;

        //Set the title at the top of the Excel sheet.
        sheet.GetRow(0).GetCell(0).SetCellValue("Summary Data For Postal Code " + strRequestedPostalCode );
        sheet.GetRow(0).GetCell(4).SetCellValue("Start Date: " + strStartDate );
        sheet.GetRow(0).GetCell(7).SetCellValue("End Date: " + strEndDate );

        //rsReportDataByPostalCode.beforeFirst();

        if(rsReportDataByPostalCode != null && rowCount!=0)
        {  
            try
            {
                //Iterate through the resultset.
                foreach (DataRow rsReportDataByPostalCodeRow in @rsReportDataByPostalCode.Rows)
                {      
                    strNewPostalCode = rsReportDataByPostalCodeRow["Postal_Code"].ToString();

                    //Set the resultset values into the appropriate cells in the Excel spreadsheet.
                    sheet.GetRow(currentRowNumber).GetCell(0).SetCellValue(rsReportDataByPostalCodeRow["Stage"].ToString());
                    sheet.GetRow(currentRowNumber).GetCell(1).SetCellValue(rsReportDataByPostalCodeRow["<20"].ToString());
                    sheet.GetRow(currentRowNumber).GetCell(2).SetCellValue(rsReportDataByPostalCodeRow["20-24"].ToString());
                    sheet.GetRow(currentRowNumber).GetCell(3).SetCellValue(rsReportDataByPostalCodeRow["25-29"].ToString());
                    sheet.GetRow(currentRowNumber).GetCell(4).SetCellValue(rsReportDataByPostalCodeRow["30-34"].ToString());
                    sheet.GetRow(currentRowNumber).GetCell(5).SetCellValue(rsReportDataByPostalCodeRow["35-39"].ToString());
                    sheet.GetRow(currentRowNumber).GetCell(6).SetCellValue(rsReportDataByPostalCodeRow["40-44"].ToString());
                    sheet.GetRow(currentRowNumber).GetCell(7).SetCellValue(rsReportDataByPostalCodeRow["45-49"].ToString());
                    sheet.GetRow(currentRowNumber).GetCell(8).SetCellValue(rsReportDataByPostalCodeRow["50-54"].ToString());
                    sheet.GetRow(currentRowNumber).GetCell(9).SetCellValue(rsReportDataByPostalCodeRow["55-59"].ToString());
                    sheet.GetRow(currentRowNumber).GetCell(10).SetCellValue(rsReportDataByPostalCodeRow["60-64"].ToString());
                    sheet.GetRow(currentRowNumber).GetCell(11).SetCellValue(rsReportDataByPostalCodeRow["65-69"].ToString());
                    sheet.GetRow(currentRowNumber).GetCell(12).SetCellValue(rsReportDataByPostalCodeRow["70-74"].ToString());
                    sheet.GetRow(currentRowNumber).GetCell(13).SetCellValue(rsReportDataByPostalCodeRow["75-79"].ToString());
                    sheet.GetRow(currentRowNumber).GetCell(14).SetCellValue(rsReportDataByPostalCodeRow["80-84"].ToString());
                    sheet.GetRow(currentRowNumber).GetCell(15).SetCellValue(rsReportDataByPostalCodeRow["85-89"].ToString());
                    sheet.GetRow(currentRowNumber).GetCell(16).SetCellValue(rsReportDataByPostalCodeRow["90-94"].ToString());
                    sheet.GetRow(currentRowNumber).GetCell(17).SetCellValue(rsReportDataByPostalCodeRow["95-99"].ToString());
                    sheet.GetRow(currentRowNumber).GetCell(18).SetCellValue(rsReportDataByPostalCodeRow["100+"].ToString());

                    currentRowNumber++;

                } //end foreach    
            }
            catch (Exception ex)
            {
                strExceptionMessage = ex.Message;
            }    

        }//end if  

        var originalBody = Context.Response.Body;
   
        wb.Write(originalBody);
        HttpContextAccessor.HttpContext.Response.Body.Flush();
        HttpContextAccessor.HttpContext.Response.Body.Close();    
    }
    catch (Exception ex)
    {
       
    }
}

Could the issue be that Context.Reponse.Body is not working right on our test environment ?


I have also tried another alternative by putting the entire download operation in my Controller but have experience same issue


On visual studio where is working I run the code with IIS Espress. Our test environment is on IIS


Also, we have checked the logs and there are no error messages to help point us to the root cause of the issue


I will appreciate any help to fix this

 

[fokwabest]

We finally found the issue. This template file SummaryByAgeStagePostalBlank.xls was not getting copied along when we publish the project to our Test environment. We set the property for the file on Visual Studio to always copy if newer. The initial setting was "Do not copy".

 

[Skydiver]

Glad you figured it out.

As aside, the C# naming conventions recommend not using Hungarian naming. Just curious why some places you use it strictly, yet, other places you use something like currentRowNumber and rsReportDataByPostalCode when the correct Hungarian for that would be iReportDataByPostalCode and datatableReportDataByPostalCode. If you are going to use Hungarian, use it properly.

 

[cjard]

"The file format and extension of summary.xls don't match. The file could be corrupted or unsafe"

Whenever you get this, actually open the file in a hex editor and look at what's inside it. Probably you'll find the HTML of an ASPNET error page rather than XLS binary bytes