1
[EnableCors(origins: "*", headers: "*", methods: "*")]
public class ExcelReportsController : Controller
{

    private ExcelContext db = new ExcelContext();

    [Route("/ExportToExcel")]
    [HttpGet]
    public void ExportPersonsToExcel()
    {
        var sqlParameters = new List<SqlParameter>();
        sqlParameters.Add(new SqlParameter("@id", "1"));
        var persons = db.Database.SqlQuery<TestExcel>("SELECT * FROM persons(@id)", sqlParameters.ToArray()).ToArray();
        DataTable table = (DataTable)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(persons), (typeof(DataTable)));
        using (SpreadsheetDocument document = SpreadsheetDocument.Create("TestNewData.xlsx", 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 = "Sheet1" };

            sheets.Append(sheet);
            Row headerRow = new Row();
            List<String> columns = new List<string>();
            foreach (DataColumn column in table.Columns)
            {
                columns.Add(column.ColumnName);
                Cell cell = new Cell();
                cell.DataType = CellValues.String;
                cell.CellValue = new CellValue(column.ColumnName);
                headerRow.AppendChild(cell);
            }

            sheetData.AppendChild(headerRow);
            foreach (DataRow dsrow in table.Rows)
            {
                Row newRow = new Row();
                foreach (String col in columns)
                {
                    Cell cell = new Cell();
                    cell.DataType = CellValues.String;
                    cell.CellValue = new CellValue(dsrow[col].ToString());
                    newRow.AppendChild(cell);
                }

                sheetData.AppendChild(newRow);
            }
            workbookPart.Workbook.Save();
        }
    }
}
}

I have the following code, but when i ask for the excel file, nothing happens. I want it to download automatically when I enter that URL. Any solutions?

Maybe a stream? But how would I implement that? I'm pretty new using this library - so bear with me.

  • In Asp.Net you could just use FileResult: [please have a look](https://stackoverflow.com/a/42460443/8978576) – Serhii Feb 16 '21 at 13:07
  • @newbie You need to return something from your method, right now it's a `public void` method. [returning-a-file-to-view-download-in-asp-net-mvc](https://stackoverflow.com/questions/5826649/returning-a-file-to-view-download-in-asp-net-mvc) – Ryan Wilson Feb 16 '21 at 13:08
  • So, if I return a "File" instead. How can I do this? return new File(workbook etc..) –  Feb 16 '21 at 13:20
  • _"How can I do this?"_ You've been given two examples ^^. Have a look at them. If they don't work for you, please tell us why not. – Fildor Feb 16 '21 at 13:23

2 Answers2

1

First, change the return type of your method to IActionResult

Then, after the using block, add this:

var stream = System.IO.File.Open("TestNewData.xlsx", FileMode.Open);
return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

It creates a FileStream from your file, then returns a FileStreamResult. The long string is the MIME type for Microsoft Excel (OpenXML). Note, FileStreamResult should automatically dispose the stream.

If you wish to provide a name for the downloaded file, you can use an overloaded version of File, so just pass a third argument (of type string), like this:

return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "CustomName.xlsx");

Riwen
  • 4,734
  • 2
  • 19
  • 31
  • When I use this solution with IHttpActionResult (return Ok(File(etc..))) I get the following error: Non-invocable member 'File' cannot be used like a method. –  Feb 16 '21 at 13:49
  • Are you using Web API 2 (ASP.NET 4.x)? My answer assumes .NET Core or .NET 5. – Riwen Feb 16 '21 at 14:01
0

I found a solution.

We have to return a HttpResponseMessage. Then after the "using" block, we add the following code:

var path = "TestNewData.xlsx";
        HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
        var stream = new FileStream(path, FileMode.Open, FileAccess.Read);
        result.Content = new StreamContent(stream);
        result.Content.Headers.ContentType =
            new MediaTypeHeaderValue("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        return result;