Blazor Server - write to excel

Question

Hello nice people,

In my application, I have a premade excel which is located under the Develeopments->unsafe_downloads folder. And here is the code block for downloading this excel;

private Stream GetFileStream()  
    {  
        var trustedFileName = "Orders.xlsx";  
        return File.OpenRead(Path.Combine(Environment.ContentRootPath,  
            Environment.EnvironmentName, "unsafe_downloads", trustedFileName));  
    }  
  
    private async Task DownloadFileFromStream()  
    {  
        var fileStream = GetFileStream();  
        var fileName = "Orders.xlsx";  
  
        using var streamRef = new DotNetStreamReference(stream: fileStream);  
  
        await JS.InvokeVoidAsync("downloadFileFromStream", fileName, streamRef);  
    }  

My question is, how can I add another sheet to excel and add data to this excel and save it before downloading it from this location? By the way, I am using non-commercial EPPlus for excel upload scenarios for my other business rule in the project. Frankly, I couldn't imagine how it would be in my mind.

Thank you in advance.

Answer 1

According to the openly publish EPPlus docs, adding a worksheet is one line of code.

using (ExcelPackage package = new ExcelPackage(newFile))  
    {  
        // add a new worksheet to the empty workbook  
        ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Inventory");  

https://epplussoftware.com/docs/5.3/api/OfficeOpenXml.ExcelPackage.html

You did not share any code that is using EPPlus, your expected results, or anything that would help us to help you.

Answer 2

Hi @AgaveJoe ,

This excel is a template, I am populating the first sheet elsewhere. I am going to add a second sheet as you suggested and insert data from the Database. My point is should I add a method which adds second sheet etc. before using var streamRef = new DotNetStreamReference(stream: fileStream); this line? So everytime user wants to download this excel, current data would be inserted into second sheet, right?

Thank you.

Answer 3

Why the second sheet is not added to the existing excel? Any ideas? (_vendors is not null)

private Stream GetFileStream()  
    {  
        var trustedFileName = "Orders.xlsx";  
        return File.OpenRead(Path.Combine(Environment.ContentRootPath,  
            Environment.EnvironmentName, "unsafe_downloads", trustedFileName));  
    }  
  
    private async Task DownloadFileFromStream()  
    {  
        var fileStream = GetFileStream();  
        var fileName = "Orders.xlsx";  
  
        await AddVendorsToExcel(fileStream);  
  
        using var streamRef = new DotNetStreamReference(stream: fileStream);  
  
        await JS.InvokeVoidAsync("downloadFileFromStream", fileName, streamRef);  
    }  
  
    private async Task AddVendorsToExcel(Stream fileStream)  
    {  
  
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;  
  
        using var package = new ExcelPackage(fileStream);  
        const int recordIndex = 2;  
        var worksheet = package.Workbook.Worksheets.Add("Vendors");  
  
        worksheet.Row(1).Style.Font.Bold = true;  
        var headerCells = worksheet.Cells["A1:E1"];  
        headerCells.Style.Font.Bold = true;  
        headerCells.Style.Font.Size = 13;  
        headerCells.Style.Border.BorderAround(ExcelBorderStyle.Thin);  
        headerCells.Style.Border.Top.Style = ExcelBorderStyle.Thin;  
        headerCells.Style.Border.Left.Style = ExcelBorderStyle.Thin;  
        headerCells.Style.Border.Right.Style = ExcelBorderStyle.Thin;  
        headerCells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;  
        headerCells.Style.Font.Color.SetColor(System.Drawing.Color.Black);  
        headerCells = worksheet.Cells["A1:E1"];  
      
        headerCells.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;  
        headerCells.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);  
          
        worksheet.Cells[1, 1].Value = "Vendor Id";  
        worksheet.Cells[1, 2].Value = "Name";  
        worksheet.Cells[1, 3].Value = "Email";  
        worksheet.Cells[1, 4].Value = "Address";  
        worksheet.Cells[1, 5].Value = "Responsible Name";  
  
        foreach (var vendor in _vendors)  
        {  
            worksheet.Cells[recordIndex, 1].Value = vendor.Id;  
            worksheet.Cells[recordIndex, 1].Style.Font.Bold = true;  
            worksheet.Cells[recordIndex, 2].Value = vendor.Name;  
            worksheet.Cells[recordIndex, 3].Value = vendor.Email;  
            worksheet.Cells[recordIndex, 4].Value = vendor.Address;  
            worksheet.Cells[recordIndex, 5].Value = vendor.MainResponsibleName;  
              
        }  
        worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();  
        await package.SaveAsync();  
  
        fileStream.Position = 0;  
          
          
    }  

Answer 4

This works!

private Stream GetFileStream()  
    {  
        var trustedFileName = "Orders.xlsx";  
        return File.OpenRead(Path.Combine(Environment.ContentRootPath,  
            Environment.EnvironmentName, "unsafe_downloads", trustedFileName));  
    }  
  
    private async Task DownloadFileFromStream()  
    {  
        await AddVendorsToExcel();   
  
        var fileStream = GetFileStream();  
        var fileName = "Orders.xlsx";  
  
        using var streamRef = new DotNetStreamReference(stream: fileStream);  
  
        await JS.InvokeVoidAsync("downloadFileFromStream", fileName, streamRef);  
    }  
  
    private async Task AddVendorsToExcel()  
    {  
  
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;  
        var trustedFileName = "Orders.xlsx";  
  
        string filePath = Path.Combine(Environment.ContentRootPath,  
            Environment.EnvironmentName, "unsafe_downloads", trustedFileName);  
  
  
        var file = new FileInfo(filePath);  
  
        using var package = new ExcelPackage(file);  
        int recordIndex = 2;  
        if (package.Workbook.Worksheets.Count > 1){  
            package.Workbook.Worksheets.Delete(1);  
        }  
  
        var worksheet = package.Workbook.Worksheets.Add("Vendors");  
  
        worksheet.Row(1).Style.Font.Bold = true;  
        var headerCells = worksheet.Cells["A1:E1"];  
        headerCells.Style.Font.Bold = true;  
        headerCells.Style.Font.Size = 13;  
        headerCells.Style.Border.BorderAround(ExcelBorderStyle.Thin);  
        headerCells.Style.Border.Top.Style = ExcelBorderStyle.Thin;  
        headerCells.Style.Border.Left.Style = ExcelBorderStyle.Thin;  
        headerCells.Style.Border.Right.Style = ExcelBorderStyle.Thin;  
        headerCells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;  
        headerCells.Style.Font.Color.SetColor(System.Drawing.Color.Black);  
        headerCells = worksheet.Cells["A1:E1"];  
  
        headerCells.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;  
        headerCells.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);  
  
        worksheet.Cells[1, 1].Value = "Vendor Id";  
        worksheet.Cells[1, 2].Value = "Name";  
        worksheet.Cells[1, 3].Value = "Email";  
        worksheet.Cells[1, 4].Value = "Address";  
        worksheet.Cells[1, 5].Value = "Responsible Name";  
  
        foreach (var vendor in _vendors)  
        {  
            worksheet.Cells[recordIndex, 1].Value = vendor.Id;  
            worksheet.Cells[recordIndex, 1].Style.Font.Bold = true;  
            worksheet.Cells[recordIndex, 2].Value = vendor.Name;  
            worksheet.Cells[recordIndex, 3].Value = vendor.Email;  
            worksheet.Cells[recordIndex, 4].Value = vendor.Address;  
            worksheet.Cells[recordIndex, 5].Value = vendor.MainResponsibleName;  
  
            recordIndex++;  
        }  
        worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();  
        await package.SaveAsync();  
          
    }