Skip to content

ExcelWriter converts field containing hyphen into excel DateTime field #33

@idbates

Description

@idbates

Using the following code snippet to write an excel file to a memory stream.

using var ms = new MemoryStream();
var operators = _operatorRepository.Table.Where(p=> p.ForsApiProviderId == id ).ToList();
var records = _mapper.Map<IEnumerable<OperatorImportDto>>(operators);

 using (var excelWriter = new ExcelWriter(ms, CultureInfo.InvariantCulture))
 {
       excelWriter.Context.TypeConverterOptionsCache.AddOptions<DateTime?>(new CsvHelper.TypeConversion.TypeConverterOptions
       {
                Formats = new[] { "yyyy-MM-dd HH:mm:ss" }
       });

        excelWriter.WriteRecords(records);
 }

With a dto such as this:

    public class OperatorImportDto
    {
        public int company_id { get; set; }
        public string scheme_id { get; set; }
        public string level { get; set; }
        public string name { get; set; }
        public string status { get; set; }
        public string address_line_1 { get; set; }
        public string address_line_2 { get; set; }
        public string address_line_3 { get; set; }
        public string city { get; set; }
        public string postcode { get; set; }
        public string country { get; set; }
        public DateTime? timestamp { get; set; }
        public string primary_contact_name { get; set; }
        public string primary_contact_email { get; set; }
        public string primary_contact_number { get; set; }
       
    }

The input data looks like this:
image

But the scheme_id field in this record is written as the Date field "01/05/2782" in excel.
My excel culture is GB English.

image

Expected behavior
The scheme_id field "002782-5" is left as a string containing the hyphen.

Screenshots
See above.

Versions (please complete the following information):

  • CSVHelper: 27.2.1
  • CSVHelper.Excel.Core: 27.2.1

Additional context
The file is written to the memory stream, converted to an array and downloaded in an ASP.NET core web application.

        [HttpGet]
        public IActionResult ExportCsv(int id, string type = "csv")
        {
            var vm = _apiProviderService.Get(id);
            var ext = type == "csv" ? "csv" : "xlsx";
            var fileName = $"{vm.ProviderSchemeId}_Operators_{DateTime.Now:yyyyMMddHHmm}.{ext}";
            var data = _operatorImportExportService.Export(id, type);
            return File(data, type == "csv"? "text/csv": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
        }

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions