Trouble saving excel file through C#

Question

I made an API using C# and am using is as a back end for my angular project. I was tasked to save data as am excel file from my page through a button made in angular. The logic is in the back end. I have tried everything to accomplish this, but nothing seems to work. I am using EPPlus now, with the SaveAs method, converting the excel package in a byte array.

So here is my fucntion in C#

public IHttpActionResult CreateExcelDocument()
    {
        //Creates a blank workbook. Using statement disposes the package.
        using (var p = new ExcelPackage())
        {
            var result = new MemoryStream();

            List<Hero> heroes = new List<Hero>();
            var documents = collection.Find(new BsonDocument()).ToList();
            foreach (BsonDocument docum in documents)
            {
                heroes.Add(BsonSerializer.Deserialize<Hero>(docum));
            }

            //Adding worksheet
            var ws = p.Workbook.Worksheets.Add("Heroes");

            //To set values in the spreadsheet use the Cells indexer.
            ws.Cells["A1"].Value = "ID";
            ws.Cells["B1"].Value = "Name";

            for(var row = 1; row <= heroes.Count(); row++)
            {
                ws.Cells[row, 1].LoadFromCollection(heroes[row].Id);
                ws.Cells[row, 2].LoadFromCollection(heroes[row].Name);
            }

            var stream = new MemoryStream(p.GetAsByteArray());

            //string filePath = @"c:\workbooks\myworkbook.xlsx";
            //FileInfo template = new FileInfo(filePath);
            //MemoryStream outputStream = new MemoryStream();
            p.SaveAs(stream);

            return Ok(stream);
        }
    }

Here is how it is how angular get it

saveDocument(): Observable<any> {
return this.http.get<any>(this.heroesUrl)
  .pipe(
    tap(p => this.log('saved excel document' + p)),
    catchError(this.handleError<any>('saveDocument', []))
  );
}

I made a message component that shows me the status of my API requests, so with saveDocument i an getting this: HeroService: saved excel document[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object]

I am using MongoDB, Angular 8, and ASP.NET Web API 2 (C#)

EDIT: here is the button in angular

<button (click)="saveDocument()"> Save heroes </button>

demoncrate · Answer 1 · 2019-09-25T12:56:31.110

0

If you already have the byte array why don't you simply use? The SaveAs command works for a given Workbook object only as you can check out here (https://learn.microsoft.com/en-us/dotnet/api/microsoft.office.tools.excel.workbook.saveas?view=vsto-2017)

File.WriteAllBytes(filename, stream);

EDIT: Answer taken from here and adapted to your problem: Save as using EPPlus?

byte[] data = p.GetAsByteArray();

string path = @"C:\test1.xlsx";
File.WriteAllBytes(path, data);

edited Sep 25 '19 at 12:56

answered Sep 25 '19 at 08:34

demoncrate

390
2
14

Ok, si i changed the code a little, only using the byte array, but still i can't see the file. This is how i added `string filePath = @"c:\workbooks\myworkbook.xlsx"; File.WriteAllBytes(filePath, bin);` and changed the Ok to return bin. – cyber_angel Sep 25 '19 at 09:00
Um, the OP doesn't look to be using an Microsoft Excel library, are you both talking about the same thing? OP is using EPPlus, this answer relies on Microsoft Excel Interop - they're different. – Jeremy Thompson Sep 25 '19 at 09:39
The stream might be your issue here or the package object might have been disposed already. Try the edited code, if it works then the problem will have been due to the stream object, if it still does not work then your package object might have been disposed already. Check out the answer from Han in the linked answer. – demoncrate Sep 25 '19 at 12:59

Trouble saving excel file through C#

1 Answers1