How to convert string time to hh: mm format in excel using EPPlus?

Question

I need my excel output to have a column with time in HH: mm format.

My code is as follows:

ws.Cells[rowNumber, 11].Style.Numberformat.Format = "hh:mm"; string start = row.R_HourStart.ToString("HH:mm:ss"); ws.Cells[rowNumber, 11].Value = start; 

But when I open the file in excel, the cell displays "10:10:00".

When I paste the text into the formula bar and press the enter button, the cell text changes to "10:10", which is what I want.

I also tried using:

 ws.Cells[rowNumber, 11].Style.Numberformat.Format = "hh:mm"; string start = row.R_HourStart.ToString("HH:mm:ss"); ws.Cells[rowNumber, 11].LoadFromText(start); 

I get "10:10" in the cell, but today the formula bar displays the full date today with the hour 10:10.

In addition, I tried:

 ws.Cells[rowNumber, 12].Style.Numberformat.Format = "hh:mm"; ws.Cells[rowNumber, 12].Value = row.R_HourEnd; 

Which shows "10:10" in the cell, but the formula bar displays the entire date and time as "08/22/2014 10:10:00".

Answer 1

Can you convert your time to TimeSpan? This worked for me:

 var package = new ExcelPackage(new FileInfo("text.xlsx")); var sheet = package.Workbook.Worksheets.Add("Sheet 1"); var someTime = "10:10:00"; var timeSpan = TimeSpan.Parse(someTime); sheet.Cells[1, 1].Value = timeSpan; sheet.Column(1).Style.Numberformat.Format = "hh:mm"; package.Save(); 

Answer 2

I had the same problem, and that was because my DataTable was integer. Formation worked fine after I replaced the values ​​with paresed.

 for (int row = 1; row <= lastRow; row++) { DateTime dt; if (DateTime.TryParse(worksheet.Cells[row, colWithDates]?.Value?.ToString() ?? string.Empty, out dt)) { worksheet.Cells[row, 1].Value = dt; worksheet.Cells[row, 1].Style.Numberformat.Format = "yyyy-mm-dd"; } TimeSpan ts; if (TimeSpan.TryParse(worksheet.Cells[row, columnWithTimes]?.Value?.ToString() ?? string.Empty, out ts)) { worksheet.Cells[row, 5].Value = ts; worksheet.Cells[row, 5].Style.Numberformat.Format = "hh:mm"; } }