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

Fokwa Divine

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 = ViewBa

Ron Nicholson

I'm doing something similar with product called Gembox. In a nutshell, I create the excel file and save it to a path under the website. In my case it is normally a folder called ExcelTemp. I save the file to that folder then use the following code to save it on the enduser's pc. This code will prompt them to save it unless the browser is set to automatically put it somewhere.

		Response.Clear();
		Response.Buffer = true;
		Response.ContentType = "application/vnd.ms-excel";
		Response.AddHeader("Content-Disposition", "attachment;filename=" + System.IO.Path.GetFileName(fileName));
		Response.Charset = "";
		Response.WriteFile(fileName);
		Response.End();

The code need the fileName as a string. HTH

Jack of all trades, master of none, though often times better than master of one.

Richard Deeming

Fokwa Divine wrote:

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

Well, that's hardly surprising - your code swallows any exceptions that are thrown! :doh:

Fokwa Divine wrote:

catch (Exception ex)
{
// Exception is swallowed; no details will be logged.
}

At a guess, the server has response buffering turned off for performance reasons. As a result, the Stream returned from Context.Response.Body is not "seekable", and the wb.Write call is failing for that reason. Looking through the NPOI examples repo[^], you should write the workbook to a MemoryStream first, then copy that to the response. Also, there's no need to use HttpContextAccessor within your view; you already have access to the current response via the Context.Response property. I would strongly recommend generating the workbook in your controller, or preferable a service that you call from your controller, and sending it back to the client in a custom response type. For example:

public HSSFExcelResult : FileStreamResult
{
private const ExcelMimeType = "application/vnd.ms-excel";

private static Stream SaveWorkbook(HSSFWorkbook workbook)
{
    ArgumentNullException.ThrowIfNull(workbook);
    
    MemoryStream ms = new();
    workbook.Write(ms);
    ms.Seek(0L, SeekOrigin.Begin);
    return ms;
}

public HSSFExcelResult(HSSFWorkbook workbook) : base(SaveWorkbook(workbook), ExcelMimeType)
{
}

}

[HttpGet]
public ActionResult ExportSummaryToExcel(string startDate, string endDate, string postalCode)
{
HSSFWorkbook workbook = _summaryByAgeStagePostalCodeService.ExportSummaryToExcel(startDate, endDate, postalCode);
return new HSSFExcelResult(workbook) { FileDownloadName = "Summary.xls" };
}

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Fokwa Divine

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".

jschell

Your code needs better error detection. As already noted do not eat exceptions. Also do checks to make sure you actually did something. For example, did you read the file but it was empty?