写入Excel单元格的带毫秒时间字符串显示异常，该如何解决？

Hey there! The issue you're facing happens because Excel automatically interprets your time string as a date/time value and applies its default time format—which doesn’t include milliseconds. Let’s walk through a few straightforward solutions to get your desired hh:mm:ss.ffff display:

Solution 1: Set the Cell’s Number Format Directly #

After assigning your time string to the cell, explicitly define the number format to include milliseconds. This keeps the underlying time value intact (great if you might need to use it for calculations later) while forcing Excel to render it your way.

Here’s how to tweak your code:

var line = "2019-07-18 11:07:42.6101";
var time = line.Substring(11, 13);
var targetCell = worksheet.Cell(row, column);

// Assign the time value
targetCell.Value = time;

// Set format to show hours, minutes, seconds, and 4 decimal places of milliseconds
// Use "HH" for 24-hour format, or "hh" for 12-hour with AM/PM
targetCell.Style.Numberformat.Format = "HH:mm:ss.ffff";

Solution 2: Write the Time as Plain Text #

If you don’t need to perform any time-based calculations on this value, you can force Excel to treat it as pure text. Adding a single quote prefix to your string prevents Excel from parsing it as a date/time.

Modified code:

var line = "2019-07-18 11:07:42.6101";
var time = line.Substring(11, 13);

// The leading single quote tells Excel to store this as text
worksheet.Cell(row, column).Value = "'" + time;

Solution 3: Parse to a DateTime Object (Most Robust) #

For the cleanest, most maintainable approach (and best support for future calculations), parse your time string into a DateTime object first, then assign it to the cell and set the format. This ensures Excel recognizes the value correctly while displaying it exactly how you want.

You’ll need to add a using directive for System.Globalization:

using System.Globalization;

var line = "2019-07-18 11:07:42.6101";
var timeStr = line.Substring(11, 13);
DateTime parsedTime;

// Safely parse the time string (handles invalid formats gracefully)
if (DateTime.TryParseExact(timeStr, "HH:mm:ss.ffff", CultureInfo.InvariantCulture, DateTimeStyles.None, out parsedTime))
{
    var targetCell = worksheet.Cell(row, column);
    targetCell.Value = parsedTime;
    targetCell.Style.Numberformat.Format = "HH:mm:ss.ffff";
}
else
{
    // Handle cases where the time string is invalid
    worksheet.Cell(row, column).Value = "Invalid Time";
}

Quick Format String Notes #

• Use HH for 24-hour time (e.g., 13:07:42.6101)
• Use hh if you want 12-hour time with AM/PM (you’ll still get milliseconds: 01:07:42.6101 PM)
• Adjust .ffff to match your input’s millisecond precision (e.g., .fff for 3 digits)

内容的提问来源于stack exchange，提问作者Anthony14