-1

I have date like 08/09/21 which is MM/dd/yy format. when i am showing this date in excel by EPPlus then warning is showing.

this is sample code i used. many ways i tried but no luck. please have a look.

1)

ws.Cells[1 + 1].Value = Convert.ToDateTime(dtGroup.Rows[r][dtGroup.Columns[c].ColumnName].ToString()).Date;
ws.Cells[1 + 1].Style.Numberformat.Format = "m/d/yy";

Warning is generated for the above code.

2)

ws.Cells[1 + 1].Value = Convert.ToDateTime(dtGroup.Rows[r][dtGroup.Columns[c].ColumnName].ToString()).Date;
ws.Cells[1 + 1].Style.Numberformat.Format = "@";

Warning is generated for the above code.

I tried four way but nothing worked. so i am confused and not able to understand where i made the mistake. please suggest something because i need to show date in excel like 08/09/21 which is MM/dd/yy format

Thanks

EDIT

ws.Cells[r + 6, groupstartcol].Value = DateTime.Parse(dtGroup.Rows[r][dtGroup.Columns[c].ColumnName].ToString()).ToString("MM/dd/yy");
ws.Cells[r + 6, groupstartcol].Style.Numberformat.Format = "mm/dd/yy";

dtGroup is datatable and date is coming from db table where date has been stored as string.

i tried the above code and it is still not working. here i am pasting a screen shot that may help you to understand the issue. Thanks

enter image description here

  • 4
    What is the warning you are receiving? – D M Aug 30 '21 at 13:27
  • Warning is : Cell contains date string represented with only 2 digits for the year. –  Aug 30 '21 at 13:29
  • 2
    Is that 1921 or 2021? You have no way to tell. This isn't nitpicking, Lloyd's crashed in January 2020 due to such bugs. Don't use such strings. Dates in Excel are strongly-typed values, not strings. – Panagiotis Kanavos Aug 30 '21 at 13:32
  • `Convert.ToDateTime(dtGroup.Rows[r][dtGroup.Columns[c].ColumnName].ToString())` that's a critical bug. If that column already contains a date, all you need to do is `((DateTime)dtGroup.Rows[r][c]).Date`. If it's a string you need to specify the actual format or culture, otherwise your code will produce bad data if that string doesn't match the **current thread's** `CultureInfo` settings. In a web app those may be controlled by the browser's preferred language – Panagiotis Kanavos Aug 30 '21 at 13:35
  • i am working with winform application from where excel will be generating. the year 2021 but i need to show 21. when i put direct value in excel like 08/09/21 and set format mm/dd/yy then no warning is coming but when i do the same from EPPlus then warning is getting generate. looking help & suggestion. –  Aug 30 '21 at 13:46
  • Is it the same warning for all 4 formats you tried? – Rafalon Aug 30 '21 at 13:47
  • 1
    I already answered that. What is the field type? And the warning won't go away because you **simply can't say** whether that is a 2021 or 1921. None. You **assume* but that assumption is 200% wrong. Before COVID-19 turned everything off, that exact bug was the most important and hilarious news of 2020 in IT – Panagiotis Kanavos Aug 30 '21 at 13:47
  • @Rafalon yes getting same working for 4 different approaches. –  Aug 30 '21 at 13:48
  • What warning are you talking about anyway? A compiler warning? Resharper warning? Excel warning? You can't "fix" any compiler or analyzer warnings as long as you use ambiguous formats but you can turn them off for that specific line. BTW you should really simplify that line – Panagiotis Kanavos Aug 30 '21 at 13:49
  • That's weird, because #2 and #3 seemed to have 4 digits for the year. Also not really sure about Excel, but in c# `mm` is for minutes, and `MM` is for months, so you might want to check that too – Rafalon Aug 30 '21 at 13:50
  • As long as you store a `DateTime` there's no data lost. And one of the default Excel date formats does use two-digit years. Which suggests the warning comes from an analyzer. What is the actual warning text? – Panagiotis Kanavos Aug 30 '21 at 13:54
  • @Rafalon Format strings are definitely different between Excel and C#. In #2 and #3 the format is 4-digit year, yes. My suspicion is that the data to convert (input) is a 2-digit year string. – Fildor Aug 30 '21 at 13:55
  • 1
    @Fildor That's right, therefore what OP calls "a warning" is probably actually "an error", and occurs on the first line, rather than on the second line – Rafalon Aug 30 '21 at 13:57
  • @Rafalon I hope, OP can clarify that. Otherwise, we'll never know :D – Fildor Aug 30 '21 at 13:58
  • @Fildor that would result in a compilation error, not some kind of "warning". It's quite likely the OP is confusing errors with warnings, but we don't even have the actual warning yet so we could Google it – Panagiotis Kanavos Aug 30 '21 at 13:58
  • @PanagiotisKanavos don't you mean a runtime error, rather than a compilation error? (if `dtGroup.Rows[r][dtGroup.Columns[c].ColumnName]` is indeed a wrongly formatted date string) – Rafalon Aug 30 '21 at 13:59
  • @PanagiotisKanavos Exactly. Maybe it is a compiler warning/error or exception ... confused as I am right now, it could be anything. – Fildor Aug 30 '21 at 13:59
  • Please, post what the value and datatype of `dtGroup.Rows[r][dtGroup.Columns[c].ColumnName]` are. Also, edit the question to add the exact message you're receiving. – Magnetron Aug 30 '21 at 14:01
  • Hi i have added code again what i am trying & screen shot too. please have a look. –  Aug 30 '21 at 14:17
  • when manually adding this date 08/09/21 in excel and set format like mm/dd/yy then excel not showing any warning but when doing the same using EPPlus then getting the warning issue. please rectify me what code i should use. thanks –  Aug 30 '21 at 14:19
  • i follow this link https://stackoverflow.com/questions/22832423/excel-date-format-using-epplus but still no luck –  Aug 30 '21 at 14:20

1 Answers1

3

You want a date cell, so don't pass a string value to it, instead, pass a DateTime and apply a format (which will affect the presentation only, not the data). If dtGroup.Rows[r][dtGroup.Columns[c].ColumnName] is a DateTime, you can do

ws.Cells[r + 6, groupstartcol].Value =((DateTime)dtGroup.Rows[r][dtGroup.Columns[c].ColumnName]).Date;
ws.Cells[r + 6, groupstartcol].Style.Numberformat.Format = "mm/dd/yy";

If you have a string:

ws.Cells[r + 6, groupstartcol].Value = DateTime.Parse(myString).Date;
ws.Cells[r + 6, groupstartcol].Style.Numberformat.Format = "mm/dd/yy";
Magnetron
  • 7,495
  • 1
  • 25
  • 41
  • Thanks. i will try and let you know. –  Aug 30 '21 at 15:17
  • still no luck ` ws.Cells[r + 6, groupstartcol].Value = DateTime.Parse(dtGroup.Rows[r][dtGroup.Columns[c].ColumnName].ToString()).Date; ws.Cells[r + 6, groupstartcol].Style.Numberformat.Format = "mm/dd/yy";` –  Aug 30 '21 at 16:04
  • @BabuBhatt you're getting the same error? – Magnetron Aug 30 '21 at 16:25
  • yes getting same error. i used this code which suppose to work as you said `ws.Cells[r + 6, groupstartcol].Value=DateTime.Parse(dtGroup.Rows[r][dtGroup.Columns[c].ColumnName].ToString()).Date; ws.Cells[r + 6, groupstartcol].Style.Numberformat.Format = "mm/dd/yy"; ` –  Aug 30 '21 at 16:34