Hi,
I am working on a Blazor Server application. I am uploading an Excel on one of my razor pages. After the upload, I am trying to generate another Excel and download it in the controller but I couldn't manage, no errors either. How can I download Excel in the controller action?
Here is the related razor uploading;
<div class="col" style="border-left: 0.1rem solid black !important;">
<h4 class="mb-4">Upload Stocks With Excel</h4>
<RadzenUpload Url="upload/stock" Progress=@TrackProgressStock Complete=@CompleteUploadStock class="w-100" Error=@(args => UploadErrorStock(args)) />
<RadzenProgressBar Value=@progressStock class="mt-4" Visible=@showProgressStock Mode="ProgressBarMode.Indeterminate" />
<RadzenLabel Visible=@showCompleteStock class="mt-4 font-weight-bold" Text=@completeMessage />
<RadzenLabel Visible=@showErrorStock class="mt-4" Text="Error occurred, contact Admin!" id="errorlabel" />
</div>
And here is the controller that I am uploading the Excel and generating and trying to download a new one:
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using Microsoft.JSInterop;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using OyunPalas.CoreBusiness.Models;
using OyunPalas.UseCases.Interfaces.Reconciliation;
using OyunPalas.UseCases.Interfaces.Stock;
namespace OyunPalas.WebApp.Controller;
[DisableRequestSizeLimit]
public class UploadController : ControllerBase
{
private readonly IWebHostEnvironment env;
private readonly ILogger<UploadController> logger;
private readonly IAddStocksUseCase _stocksUseCase;
private readonly IAddStockUniqueSerialPinUseCase _stockUniqueSerialPinUseCase;
private readonly IDeleteReconciliationsUseCase _deleteReconciliationsUseCase;
private readonly IConfiguration configuration;
private readonly IJSRuntime _jsRuntime;
public UploadController(IWebHostEnvironment env,
IConfiguration configuration, ILogger<UploadController> logger, IAddStocksUseCase stocksUseCase, IDeleteReconciliationsUseCase deleteReconciliationsUseCase, IAddStockUniqueSerialPinUseCase stockUniqueSerialPinUseCase, IJSRuntime jsRuntime)
{
this.env = env;
this.logger = logger;
_stocksUseCase = stocksUseCase;
_deleteReconciliationsUseCase = deleteReconciliationsUseCase;
this.configuration = configuration;
_stockUniqueSerialPinUseCase = stockUniqueSerialPinUseCase;
_jsRuntime = jsRuntime;
}
// POST: Upload
[HttpPost("upload/stock")]
public async Task<IActionResult> Stock(IFormFile file)
{
try
{
var count = await UploadStocksFile(file);
if (count < 0) { count = 0; }
return StatusCode(200, count);
}
catch (Exception ex)
{
return StatusCode(500, ex.Message);
}
}
public async Task<int> UploadStocksFile(IFormFile file)
{
var untrustedFileName = file.FileName;
var totalRows = 0;
var duplicates = 0;
try
{
var path = Path.Combine(env.ContentRootPath,
env.EnvironmentName, "unsafe_uploads_stock",
untrustedFileName);
await using FileStream fs = new(path, FileMode.Create);
await file.CopyToAsync(fs);
logger.LogInformation("{untrustedFileName} saved at {Path}",
untrustedFileName, path);
var fi = new FileInfo(path);
// Check if the file exists
if (!fi.Exists)
throw new Exception("File " + path + " Does Not Exists");
//Check if file is an Excel File
if (untrustedFileName.Contains(".xls"))
{
using var ms = new MemoryStream();
await file.OpenReadStream().CopyToAsync(ms);
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using var package = new ExcelPackage(ms);
var workSheet = package.Workbook.Worksheets["Stock"];
totalRows = workSheet.Dimension.Rows;
var customerList = new List<GameBank>();
for (var i = 2; i <= totalRows; i++)
{
customerList.Add(new GameBank
{
ProductDescription = workSheet.Cells[i, 1].Value.ToString(),
ProductCode = workSheet.Cells[i, 2].Value.ToString(),
UnitPrice = Convert.ToDouble(workSheet.Cells[i, 3].Value),
Quantity = Convert.ToInt16(workSheet.Cells[i, 4].Value),
Version = workSheet.Cells[i, 5].Value.ToString(),
Currency = workSheet.Cells[i, 6].Value.ToString(),
TotalPrice = Convert.ToDouble(workSheet.Cells[i, 7].Value),
Status = Convert.ToInt16(workSheet.Cells[i, 8].Value),
Used = Convert.ToInt16(workSheet.Cells[i, 9].Value),
RequestDateTime = DateTime.Now,
Signature = User.Identity.Name
});
customerList[i - 2].coupons = new GameBankPin
{
ExpiryDate = Convert.ToDateTime(workSheet.Cells[i, 10].Value),
Serial = workSheet.Cells[i, 11].Value.ToString(),
Pin = workSheet.Cells[i, 12].Value.ToString()
};
}
//await _stocksUseCase.ExecuteAsync(customerList);
var existingRecords = await _stockUniqueSerialPinUseCase.ExecuteAsync(customerList);
if (existingRecords.Count > 0)
{
await Export(existingRecords);
duplicates = existingRecords.Count;
}
}
}
catch (Exception ex)
{
logger.LogError("{untrustedFileName} error on upload (Err: 3): {Message}",
untrustedFileName, ex.Message);
}
return totalRows - 1 - duplicates;
}
private async Task<FileStreamResult> Export(List<GameBank> existingRecords)
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
var stream = new MemoryStream();
var fileName = $"RazerDuplicateCodesList-{DateTime.Now:ddMMyyyyHHmm}.xlsx";
using var package = new ExcelPackage(stream);
var workSheet = package.Workbook.Worksheets.Add("Duplicate Codes");
workSheet.Protection.IsProtected = true;
var recordIndex = 2;
workSheet.Row(1).Style.Font.Bold = true;
var headerCells = workSheet.Cells["A1:B1"];
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:B1"];
// Set their background color to DarkBlue.
headerCells.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
headerCells.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
workSheet.Cells[1, 1].Value = "Serial";
workSheet.Cells[1, 2].Value = "Pin";
foreach (var duplicate in existingRecords)
{
workSheet.Cells[recordIndex, 1].Value = duplicate.coupons.Serial;
workSheet.Cells[recordIndex, 2].Value = duplicate.coupons.Pin;
recordIndex++;
}
//Make all text fit the cells
workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();
await package.SaveAsync();
stream.Position = 0;
return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
//using var streamRef = new DotNetStreamReference(stream: stream);
//await _jsRuntime.InvokeVoidAsync("downloadFileFromStream", excelName, streamRef);
}
}
Thank you.