CODEWITHSUNDEEP
c#excelfileexport-to-exceldate-range
Need Help
Need Help

Reputation: 1

Saving an Excel file using the data range as file name in C#

I have an export to a .xlsx spreadsheet in Excel which is generated when the user supplies a date range and the car name. This works at the moment and displays the data correctly. The file name must be generated from the date range and the car name.
However, if the date range has no data then an error is returned (as it can not generate the file name as that requires the date range and the car name). Ideally I would like it to generate the date range as the name of the file as usual, even if there is no data (potentially generating an empty spreadsheet). So far I have this -

[ApiController]
public class CarController(ISender sender, IDataExportService 
dataExportService) : BaseController
{
/// <summary>
/// Get data to be exported from the database for a given car within a given timeframe and converts to Excel (XLSX) format.
/// URL: /excel/{carId}
/// </summary>
/// <param name="carId"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
[HttpGet("excel/{carId:int}")]
public async Task<FileStreamResult> CarDataExport(int carId, [FromQuery] ExportCarDataRequest request, CancellationToken cancellationToken)
{
    var data = await sender.Send(new ExportAssetDataDataQuery(carId, request), cancellationToken);

    var fileName = data.FirstOrDefault().CarName + DateOnly.FromDateTime(DateTime.UtcNow) + ".xlsx";
    
    var memoryStream = dataExportService.ExportToExcel(data, fileName);
    
    return memoryStream;
}

I was then able to get the filename to be what I wanted if there was data in the spreadsheet (see after the else). However I have gone slightly wrong with the Dapper to get the CarName into the file name when there is no data in the spreadsheet.

public async Task<FileStreamResult> CarDataExport(int carId, 
[FromQuery] ExportCarDataRequest request, CancellationToken 
cancellationToken)
 {
   var data = await sender.Send(new ExportCarDataDataQuery(carId, request), cancellationToken);

   var varCar = data.Select (x => x.CarName).Distinct();

String fileName;
 if (data.FirstOrDefault().CarName == null)

    {
        fileName = varCar + 
           (DateOnly.FromDateTime(DateTime.UtcNow) + ".xlsx";
    }
else
    {
    fileName = data.FirstOrDefault().CarName + 
           DateOnly.FromDateTime(DateTime.UtcNow) + ".xlsx";
    }
}

I also have this in the request

 Public class CarExportDataResponse
     {
   public CarExportDataResponse() { }

     private CarExportDataResponse(carExportData carExportData)
     {
       CarName = carExportData.CarName;
       RegId = carExportData.RegId;
       ModelId = carExportData.ModelId;
       EngineNum = carExportData.EngineNum;
       ServiceType = carExportData.ServiceType;
     }
   }

And this in the response

  namespace CarYard.Services.Features.Cars.Requests;

  public record ExportAssetDataRequest(DateTime DateFrom, DateTime 
   DateTo, String CarName);
  public record GetAssetsRequest(int? PageSize, int? PageNumber);

Upvotes: -1

Views: 122

Answers (2)

oscar
oscar

Reputation: 158

OK, so your endpoint requires the user to send a carID and anExportAssetDataRequest (which I presume includes the date range to search on).

So you could do something like this.

public async Task<FileStreamResult> AssetDataExport(int carId, [FromQuery] ExportAssetDataRequest request, CancellationToken cancellationToken)
{
    ...
    var fileName = carId.ToString() + request.StartDate.ToString() + request.EndDate.ToString() + ".xslx";
    ...
}

From your question, I'm not sure if the carId is the same as the car name for your application. If so, you'd need a way to get the car name from the carId and replace the carId in the above.

Upvotes: 0

Hiren Patel
Hiren Patel

Reputation: 1157

Below line is throwing an exception.

var fileName = data.FirstOrDefault().CarName + DateOnly.FromDateTime(DateTime.UtcNow) + ".xlsx";

So What you can do, You to use only daterange only to save file.

You can do these thing as below

string filename;
var car = data.FirstOrDefault();
if(car==null)
{
     filename= DateOnly.FromDateTime(DateTime.UtcNow) + ".xlsx";
}
else
{
     filename= car.CarName + DateOnly.FromDateTime(DateTime.UtcNow) + ".xlsx";
}

Upvotes: 0

Related Questions