-1

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);
10
  • 1
    If your data is empty, FirstOrDefault().CarName will throw a NullReferenceException. If the user has passed this in a request, you would be better using the request data to generate the filename. Commented Dec 19, 2024 at 11:26
  • var fileName = data.FirstOrDefault().CarName + DateOnly.FromDateTime(DateTime.UtcNow) + ".xlsx"; will throw exception beause there is no data Commented Dec 19, 2024 at 11:27
  • What exactly is your question? How do you get the date range when one is available and what type do the corresponding properties have? Are they nullable? Commented Dec 19, 2024 at 12:45
  • @OlivierJacot-Descombes The properties are mainly floats, which are Nullable. At the moment it only generates the date in the file name when there is data, I would like it to generate even if there are NULLs in the date range requested. The User enters the date range and the car name. Commented Dec 19, 2024 at 17:01
  • @HirenPatel it does throw an exception at the moment but only if there is no data. Is there another way to do this that is more recommended? Commented Dec 19, 2024 at 17:03

2 Answers 2

0

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";
}
Sign up to request clarification or add additional context in comments.

16 Comments

Thanks for the example, however there will always be a car name, the problem is if there are no records in the date range. So currently if there are is an exception (and does not create the file) for RedRangeRoverVogue_10_12_2024.xlsx (as there was no data on the 10th dec for the Red Range Rover Vogue. I still want to create the .xlsx file even if the spreadsheet is empty.
Then you have to get car name first.
Can you please tell how you are getting data object, and where your card details are saved?
It is part of an API controller (I have added to the original code). Would this help?
So you can use card id or you have to get car name from there api.
|
0

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.

7 Comments

Thanks for looking at this, however when I tried this before I got 'Operator + cannot be applied to operands of type int and DateTime'. I think the above could still generate a file name if there is an empty record set in the spreadsheet though
Yes, you'll need to convert them all the parameters to strings. Answer updated.
Sorry I have just seen this, how would I convert these parameters to a strings in this code?
I've updated the answer to include ToString() on each of the params to do this.
That is brilliant. It works! However do you know how to shorten the date returned in the string? Also although saving as an .xslx file Excel says it could possible be be corrupt when opening it (although it opens fine)
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.