GemBox.Spreadsheet Save method is not doing anything and no errors

Question

I have been trying to use Gembox to export some information from my web application into an excel spreadsheet.

When debugging I can step through the entirety of my SaveExcelFile method but nothing seems to happen, no file is downloaded and seemingly no errors to point me in the right direction to solve the issue.

private void SaveExcelFile(ExcelDataModel excelDataModel)
    {            
        var workbook = new ExcelFile();
        ExcelWorksheet employeesWorksheet = workbook.Worksheets.Add("Employees");
        ExcelWorksheet contractsWorksheet = workbook.Worksheets.Add("Contracts");

        employeesWorksheet.Cells["A1"].Value = "Employees:";
        employeesWorksheet.InsertDataTable(excelDataModel.EmployeeDatatable,
            new InsertDataTableOptions
            {
                ColumnHeaders = true,
                StartRow = 3
            });

        contractsWorksheet.Cells["A1"].Value = "Contracts:";
        contractsWorksheet.InsertDataTable(excelDataModel.ContractDatatable,
            new InsertDataTableOptions
            {
                ColumnHeaders = true,
                StartRow = 3
            });

        workbook.Save("Employees.xls");
    }

The model being passed in simply contains two DataTables:

    public class ExcelDataModel
{
    public DataTable EmployeeDatatable { get; set; }
    public DataTable ContractDatatable { get; set; }

}

I'm calling my SaveExcelFile method from:

public async Task<ActionResult> ExportEmployees()
    {
        SpreadsheetInfo.SetLicense(ConfigurationManager.AppSettings["GemBoxSpreadsheetSerialKey"]);

        var response = await **I have removed the call/url but this just calls a controller method on the api**;
        if (response.IsSuccessStatusCode)
        {
            var data = response.Content.ReadAsStringAsync().Result;
            var model = JsonConvert.DeserializeObject<ExcelDataModel>(data);

            SaveExcelFile(model);
            return Json(new { success = true, message = "Successfully exported employees" }, JsonRequestBehavior.AllowGet);
        }

        return Json(new { success = false, message = "Failed to export employees to excel" }, JsonRequestBehavior.AllowGet);
    }

The model that is being passed in seems fine, it has the datatables populated with the correct data.

score 0 · Accepted Answer · answered Nov 13 '20 at 06:45

The workbook.Save("Employees.xls") method will save the file to the specified path and in case of a relative path (like what you have there), it's going to be saved relative to the current working directory.

To download the file you need to use FileResult instead of JsonResult, as shown in the ASP.NET Core example.

// Changed "void" to "MemoryStream" and added "SaveOptions".
private MemoryStream SaveExcelFile(ExcelDataModel excelDataModel, SaveOptions options)
{
    // ...

    // Changed saving from path to stream.
    var stream = new MemoryStream();
    workbook.Save(stream, options);
    return stream;
}

public async Task<ActionResult> ExportEmployees()
{
    // ...

    if (response.IsSuccessStatusCode)
    {
        var data = response.Content.ReadAsStringAsync().Result;
        var model = JsonConvert.DeserializeObject<ExcelDataModel>(data);

        // Changed "JsonResult" to "FileResult".
        var excelOptions = SaveOptions.XlsDefault;
        using (var excelStream = SaveExcelFile(model, excelOptions))
            return File(excelStream.ToArray(), excelOptions.ContentType, "Employees.xls");
    }

    return Json(new { success = false, message = "Failed to export employees to excel" }, JsonRequestBehavior.AllowGet);
}

Thanks so much! That really helped, it's working perfectly now! — Kiwi, Nov 13 '20 at 11:05

GemBox.Spreadsheet Save method is not doing anything and no errors

1 Answers1