Unable to save to directory in azure cloud service folder

Question

I am trying to create an Excel file using Microsoft.Office.Interop.Excel and while this seems to be working on my local machine using the following code:

//Start Excel and get Application object.
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;

//Get a new workbook.
oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

//Add Contact Details for Manager
oSheet.Cells[1, 1] = "Account Manager";
oSheet.Cells[2, 1] = "Manager Name";
oSheet.Cells[3, 1] = "+44(0)2871262626";
oSheet.Cells[4, 1] = "[email protected]";
oSheet.get_Range("A1", "A4").Font.Bold = true;

oXL.DisplayAlerts = false;
oWB.SaveAs(Server.MapPath("~/TransitFiles/") + bill.Landlord.Name + " Group Bill " + bill.LandlordBillID + ".xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

When I try to deploy this to my azure cloud service I am not able to generate the file despite having the dll copied across and the relevant folder location being created due to a holder.txt document that I placed in there, what is the best way of displaying the Excel document as an ActionResult in MVC?

Answer 1

When I try to deploy this to my azure cloud service I am not able to generate the file despite having the dll

Since there is no MS Office present on Azure WebRole so we cannot use Office InterOP Dll.

We could use the OpenXml SDK to that. I do a demo ,it works correctly on my side. The following is my demo code according to your code. We also can get more code about operating excel with OPenXML from the document.

        var fileName = Server.MapPath("~/")+@"OpenXMltest.xlsx";
        using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
        {
            WorkbookPart workbookPart = document.AddWorkbookPart();
            workbookPart.Workbook = new Workbook();

            WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            var sheetData = new SheetData();
            worksheetPart.Worksheet = new Worksheet(sheetData);

            Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());

            Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Test Sheet" };

            List<String> columns = new List<string>();

            Row newRow1 = new Row();
            Row newRow2 = new Row();
            Row newRow3 = new Row();
            Row newRow4 = new Row();

            Cell cell1 = new Cell
            {
                DataType = CellValues.String,
                CellValue = new CellValue("Account Manager")
            };
            Cell cell2 = new Cell
            {
                DataType = CellValues.String,
                CellValue = new CellValue("Manager Name")
            };
            Cell cell3 = new Cell
            {
                DataType = CellValues.String,
                CellValue = new CellValue("+44(0)2871262626")
            };
            Cell cell4 = new Cell
            {
                DataType = CellValues.String,
                CellValue = new CellValue("[email protected]")
            };

            newRow1.AppendChild(cell1);
            newRow2.AppendChild(cell2);
            newRow3.AppendChild(cell3);
            newRow4.AppendChild(cell4);
            sheetData.AppendChild(newRow1);
            sheetData.AppendChild(newRow2);
            sheetData.AppendChild(newRow3);
            sheetData.AppendChild(newRow4);
            sheets.Append(sheet);

            workbookPart.Workbook.Save();
        }

what is the best way of displaying the Excel document as an ActionResult in MVC?

If we want to display the document, base on my experience we could use dataview to do that.