Download/Upload an Import/Export in .NET 5.0 (edit)
Interface
public interface IExportDataService
{
byte[] ExportListUserDtosToExcel(List<UserDto> users);
Task<List<UserDto>> GetListUserDtos(IFormFile file);
}
Service
public class ExportDataService : IExportDataService
{
public byte[] ExportListUserDtosToExcel(List<UserDto> users)
{
// Lets converts our object data to Datatable for a simplified logic.
// Datatable is most easy way to deal with complex datatypes for easy reading and formatting.
DataTable table = (DataTable)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(users), typeof(DataTable));
using (MemoryStream stream = new MemoryStream())
{
SpreadsheetDocument document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" };
sheets.Append(sheet);
Row headerRow = new Row();
List<string> columns = new List<string>();
foreach (DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow row in table.Rows)
{
Row newRow = new Row();
foreach (string col in columns)
{
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(row[col].ToString());
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookPart.Workbook.Save();
document.Close();
return stream.ToArray();
}
}
public async Task<List<UserDto>> GetListUserDtos(IFormFile fileUpload)
{
try
{
var users = new List<UserDto>();
string str = string.Empty;
string sheetName = string.Empty;
int sheetIndex = 0;
int colIndex = 0;
using (var stream = new MemoryStream())
{
await fileUpload.CopyToAsync(stream);
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(stream, false);
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
SharedStringTablePart sstpart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
SharedStringTable sst = sstpart.SharedStringTable;
foreach (WorksheetPart worksheetpart in workbookPart.WorksheetParts)
{
try
{
Worksheet worksheet = worksheetpart.Worksheet;
sheetName = workbookPart.Workbook.Descendants<Sheet>().ElementAt(sheetIndex++).Name;
foreach (SheetData sheetData in worksheet.Elements<SheetData>())
{
try
{
var cells = sheetData.Descendants<Cell>();
var rows = sheetData.Descendants<Row>();
Debug.WriteLine("Row count = {0}", rows.LongCount());
Debug.WriteLine("Cell count = {0}", cells.LongCount());
string email = string.Empty;
string name = string.Empty;
string phone = string.Empty;
foreach (Cell cell in cells)
{
colIndex += 1;
if (colIndex == 1)
{
email = string.Empty;
name = string.Empty;
phone = string.Empty;
}
if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
{
int ssid = int.Parse(cell.CellValue.Text);
str = sst.ChildElements[ssid].InnerText;
}
else if (cell.CellValue != null)
{
str = cell.CellValue.Text;
}
if (colIndex % 3 == 1)
{
email = str;
}
if (colIndex % 3 == 2)
{
name = str;
}
if (colIndex % 3 == 0)
{
phone = str;
//Reset colIndex
colIndex = 0;
//Add to list
users.Add(new UserDto()
{
Email = email,
Name = name,
Phone = phone
});
}
}
}
catch (Exception e)
{
Debug.WriteLine(e.ToString());
throw;
}
}
}
catch (Exception e)
{
Debug.WriteLine(e.ToString());
throw;
}
}
}
return users;
}
catch (Exception e)
{
Debug.WriteLine(e.ToString());
throw;
}
}
}
DTO
public class UserDto
{
public string Name { get; set; }
public string Email { get; set; }
public string Phone { get; set; }
}
Startup.cs
services.AddTransient<IExportDataService, ExportDataService>();
BaseController
[Route("api/v{version:apiVersion}/[controller]")]
[ApiController]
public class BaseController : ControllerBase
{
protected IMediator Mediator { get; }
public BaseController(IMediator mediator)
{
Mediator = mediator;
}
}
Controller
[ApiVersion("1.0")]
[ApiVersion("2.0")]
[ProducesResponseType((int)HttpStatusCode.OK)]
[ProducesResponseType((int)HttpStatusCode.BadRequest)]
public class RevenueManagementController : BaseController
{
private readonly ILogger<RevenueManagementController> _logger;
private readonly IMailService _mailService;
private readonly IExportDataService _exportDataService;
private readonly IUserContextService _userContextService;
public RevenueManagementController(
ILogger<RevenueManagementController> logger,
IMediator mediator,
IMailService mailService,
IExportDataService exportDataService,
IUserContextService userContextService) : base(mediator)
{
_userContextService = userContextService;
_mailService = mailService;
_exportDataService = exportDataService;
_logger = logger;
}
[HttpGet("RevenueStoreByMonth")]
public async Task<ActionResult<PagedApiResult<RevenueStoreByMonthListResponse>>> RevenueStoreByMonth([FromQuery] RevenueStoreByMonthListRequest request, CancellationToken cancellationToken)
{
return Ok(await Mediator.Send(request, cancellationToken));
}
[HttpGet, DisableRequestSizeLimit]
[Route("DownloadRevenueStoreByMonth")]
public async Task<IActionResult> DownloadRevenueStoreByMonth([FromQuery] DownloadFileRequest request)
{
var filePath = Path.Combine(Directory.GetCurrentDirectory(), request.FileUrl);
if (!System.IO.File.Exists(filePath))
{
return Ok(new
{
Success = false,
Status = HttpStatusCode.NotFound,
StatusCode = (int)HttpStatusCode.NotFound
});
}
var memory = new MemoryStream();
await using (var stream = new FileStream(filePath, FileMode.Open))
{
await stream.CopyToAsync(memory);
}
memory.Position = 0;
return File(memory, GetContentType(filePath), filePath);
}
[HttpGet("RevenueRewardMobileUser")]
public async Task<ActionResult<PagedApiResult<RevenueRewardMobileUserListResponse>>> RevenueRewardMobileUser([FromQuery] RevenueRewardMobileUserListRequest request, CancellationToken cancellationToken)
{
return Ok(await Mediator.Send(request, cancellationToken));
}
[HttpGet, DisableRequestSizeLimit]
[Route("DownloadRevenueRewardMobileUser")]
public async Task<IActionResult> DownloadRevenueRewardMobileUser([FromQuery] DownloadFileRequest request)
{
var filePath = Path.Combine(Directory.GetCurrentDirectory(), request.FileUrl);
if (!System.IO.File.Exists(filePath))
{
return Ok(new
{
Success = false,
Status = HttpStatusCode.NotFound,
StatusCode = (int)HttpStatusCode.NotFound
});
}
var memory = new MemoryStream();
await using (var stream = new FileStream(filePath, FileMode.Open))
{
await stream.CopyToAsync(memory);
}
memory.Position = 0;
return File(memory, GetContentType(filePath), filePath);
}
[HttpPost, DisableRequestSizeLimit]
[Route("UploadGiftCardCode")]
public IActionResult UploadGiftCardCode()
{
try
{
var file = Request.Form.Files[0];
var folderName = Path.Combine("StaticFiles", "Images");
var pathToSave = Path.Combine(Directory.GetCurrentDirectory(), folderName);
if (file.Length > 0)
{
var fileName = ContentDispositionHeaderValue.Parse(file.ContentDisposition).FileName.Trim('"');
var fullPath = Path.Combine(pathToSave, fileName);
var dbPath = Path.Combine(folderName, fileName);
using (var stream = new FileStream(fullPath, FileMode.Create))
{
file.CopyTo(stream);
}
return Ok(new
{
Success = true,
Status = HttpStatusCode.OK,
StatusCode = (int)HttpStatusCode.OK,
Data = dbPath
});
}
else
{
return Ok(new
{
Success = false,
Status = HttpStatusCode.BadRequest,
StatusCode = (int)HttpStatusCode.BadRequest
});
}
}
catch (Exception ex)
{
string errors = ex.ToString();
return Ok(new
{
Success = false,
Status = HttpStatusCode.InternalServerError,
StatusCode = (int)HttpStatusCode.InternalServerError,
Data = errors
});
}
}
[HttpPost, DisableRequestSizeLimit]
[Route("UploadFile")]
public async Task<IActionResult> UploadFile(IFormFile fileUpload)
{
try
{
var users = await _exportDataService.GetListUserDtos(fileUpload);
return Ok(new
{
Success = true,
Status = HttpStatusCode.OK,
StatusCode = (int)HttpStatusCode.OK,
Data = users
});
}
catch (Exception ex)
{
var errors = ex.ToString();
Debug.WriteLine(errors);
return Ok(new
{
Success = false,
Status = HttpStatusCode.InternalServerError,
StatusCode = (int)HttpStatusCode.InternalServerError,
Data = errors
});
}
}
[HttpGet, DisableRequestSizeLimit]
[Route("DownloadExcelFile")]
public IActionResult DownloadExcelFile()
{
// Get the user list
var users = GetListOfUsers();
var stream = _exportDataService.ExportListUserDtosToExcel(users);
return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "users.xlsx");
}
[HttpGet, DisableRequestSizeLimit]
[Route("DownloadCsvFile")]
public IActionResult DownloadCsvFile()
{
// Get the user list
var users = GetListOfUsers();
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine("Email,Name,Phone");
foreach (var author in users)
{
stringBuilder.AppendLine($"{author.Email}, { author.Name},{ author.Phone}");
}
return File(Encoding.UTF8.GetBytes(stringBuilder.ToString()), "text/csv", "users.csv");
}
private List<UserDto> GetListOfUsers()
{
var users = new List<UserDto>()
{
new UserDto {
Email = "user1@gmail.com",
Name = "user1",
Phone = "123456"
},
new UserDto {
Email = "user2@gmail.com",
Name = "user2",
Phone = "222222"
},
new UserDto {
Email = "user3@gmail.com",
Name = "user3",
Phone = "33333"
}
};
return users;
}
private string GetContentType(string path)
{
var provider = new FileExtensionContentTypeProvider();
string contentType;
if (!provider.TryGetContentType(path, out contentType))
{
contentType = "application/octet-stream";
}
return contentType;
}
}
