0

I have an MVC Razor application where I am returning a view. I have overloaded my action to accept a null-able "export" bool which will change the action by adding headers but still returning the same view as a file (in a new window).

        //if there is a value passed in, set the bool to true
        if (export.HasValue)
        {
            ViewBag.Exporting = true;

            var UniqueFileName = string.Format(@"{0}.xls", Guid.NewGuid());
            Response.AddHeader("content-disposition", "attachment; filename="+UniqueFileName);
            Response.ContentType = "application/ms-excel";
        }

As the file was generated based on a view, its not an .xls file so when opening it, I get the message "the file format and extension of don't match". So after a Google, I have found THIS POST on SO where one of the answers uses VBA to open the file on the server (which includes the HTML mark-up) then saves it again (as .xls).

I am hoping to do the same, call the controller action which will call the view and create the .xls file on the server, then have the server open it, save it then return it as a download.

What I don't want to do is to create a new view to return a clean file as the current view is an extremely complex page with a lot of logic which would only need to be repeated (and maintained). What I have done in the view is to wrap everything except the table in an if statement so that only the table is exported and not the rest of the page layout.

Is this possible?

Community
  • 1
  • 1
wf4
  • 3,727
  • 2
  • 34
  • 45
  • So your view still outputs HTML except that you changed the content type and added the content-disposition header? – LostInComputer Feb 04 '14 at 14:33
  • yes, that is correct. I want to be able to export a formatted table, colours etc. The problem that I have is that there is a HUGE amount of logic in the view, over 400 lines. I don't want to have to recode each view in the controller action. So far it works but you get the warning message about the file format, but I am trying to get the web server to open the file then do a "save as" in an attempt to ditch the html which is causing the warning and present a formatted .xls to the user. – wf4 Feb 04 '14 at 14:40
  • What you want to do is not possible! You must generate an excel file instead of HTML – LostInComputer Feb 04 '14 at 14:53
  • oh... I was optimistic after reading this post http://stackoverflow.com/a/9575683/2394259 but that is VBA and not done in a controller action. Back to the drawing board - Thank you – wf4 Feb 04 '14 at 15:25
  • Oh wait. I miss read your question. It's possible for a controller action to generate an excel file from the HTML output of another action. It's ugly though design wise and running excel in the server is probably problematic! I had bad experience with it. Let me take a stab at writing the code. – LostInComputer Feb 04 '14 at 15:42

1 Answers1

1

You can implement the VBA in .net

private void ConvertToExcel(string srcPath, string outputPath, XlFileFormat format)
{
    if (srcPath== null) { throw new ArgumentNullException("srcPath"); }
    if (outputPath== null) { throw new ArgumentNullException("outputPath"); }

    var excelApp = new Application();
    try
    {
        var wb = excelApp.Workbooks.Open(srcPath);
        try
        {
            wb.SaveAs(outputPath, format);
        }
        finally
        {
            Marshal.ReleaseComObject(wb);
        }
    }
    finally
    {
        excelApp.Quit();
    }
}

You must install Microsoft.Office.Interop and add reference to a COM oject named Microsoft Excel XX.0 Object Library

Sample usage:

//generate excel file from the HTML output of GenerateHtml action.
var generateHtmlUri = new Uri(this.Request.Url, Url.Action("GenerateHtml"));
ConvertToExcel(generateHtmlUri.AbsoluteUri, @"D:\output.xlsx", XlFileFormat.xlOpenXMLStrictWorkbook);

I however discourage this solution because:

  1. You have to install MS Excel in your web server.
  2. MS Excel may sometimes misbehave like prompting a dialog box.
  3. You must find a way to delete the generated Excel file afterwards.
  4. Ugly design.

I suggest to generate excel directly because there doesn't seem to be better ways to covert HTML to Excel except using Excel itself or DocRaptor.

LostInComputer
  • 15,188
  • 4
  • 41
  • 49