public static async Task <ExcelResponse <List <User> > > ReadFile(IFormFile formFile, CancellationToken cancellationToken)
{
var list = new List <User>();
var errors = new List <Error>();
var sex = new List <string> {
"Male", "Female", "Other"
};
using (var stream = new MemoryStream())
{
await formFile.CopyToAsync(stream, cancellationToken);
using (var package = new ExcelPackage(stream))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
var rowCount = worksheet.Dimension.Rows;
int a;
for (int row = 2; row <= rowCount; row++)
{
if (worksheet.Cells[row, 1].Value.ToString().Trim().Length > 20)
{
errors.Add(new Error {
Row = row, Message = "The Name is too long"
});
}
if (!sex.Contains(worksheet.Cells[row, 2].Value.ToString().Trim()))
{
errors.Add(new Error {
Row = row, Message = "The Sex must be either Male, Female or Other"
});
}
if (!int.TryParse(worksheet.Cells[row, 3].Value.ToString().Trim(), out a) || a < 0)
{
errors.Add(new Error {
Row = row, Message = "The Age must be an integer greater then 0"
});
}
if (errors.Count == 0)
{
list.Add(new User
{
Name = worksheet.Cells[row, 1].Value.ToString().Trim(),
Sex = worksheet.Cells[row, 2].Value.ToString().Trim(),
Age = int.Parse(worksheet.Cells[row, 3].Value.ToString().Trim()),
});
}
}
}
}
return(errors.Count > 0 ?
ExcelResponse <List <User> > .GetResult(-2, errors, list) :
ExcelResponse <List <User> > .GetResult(0, errors, list));
}
public ExcelResponse DetailsDownloadReport(string fromdate, string todate)
{
string reportname = $"Detail_{Guid.NewGuid():N}.xlsx";
var list = _exportReportQueries.GetDateWiseUserDetailsReport(fromdate, todate);
var exportbytes = ExporttoExcel <UserDetailsReport>(list, reportname);
ExcelResponse excelResponse = new ExcelResponse()
{
ExcelBytes = exportbytes,
ExcelName = reportname
};
return(excelResponse);
}
public async Task <ExcelResponse <List <User> > > Import(IFormFile formFile, CancellationToken cancellationToken)
{
if (formFile == null || formFile.Length <= 0)
{
//return ExcelResponse<List<User>>.GetError("The file is empty");
return(ExcelResponse <List <User> > .GetError("The File is Empty"));
}
if (!Path.GetExtension(formFile.FileName).Equals(".xlsx", StringComparison.OrdinalIgnoreCase))
{
return(ExcelResponse <List <User> > .GetError("Not Supported file extension"));
}
//List<User> usersList = await ExcelHelper.ReadFile(formFile, cancellationToken);
// We Can Add The list of users to Db
return(await ExcelHelper.ReadFile(formFile, cancellationToken));
//return ExcelResponse<List<User>>.GetResult(0, "OK", usersList);
}
