程序读取excel中日期时,可能遇到各种各样的格式,转换时经常遇到错误,本示例使用C#程序演示如何转换为目标格式:
public static string ToDate(object val)
{
if (val==null || string.IsNullOrEmpty(val.ToString()))
{
return "0001-01-01";
}
string dateStr = val.ToString();
if (new Regex(@"^\d{4}-\d{2}-\d{2}$").IsMatch(dateStr))
{
//常规日期格式
}
else if(new Regex(@"^[0-9]{1,7}$").IsMatch(dateStr))
{
return DateTime.FromOADate(double.Parse(dateStr)).ToString("yyyy-MM-dd", System.Globalization.DateTimeFormatInfo.InvariantInfo);
}
else if (new Regex(@"^\d{8,}$").IsMatch(dateStr))
{
return DateTime.ParseExact(dateStr, "yyyyMMdd", null).ToString("yyyy-MM-dd");
}
else if (new Regex(@"^\d{1,2}-\d{1,2}月-\d{2,4}$").IsMatch(dateStr))
{
dateStr = new Regex("[\u4e00-\u9fff]").Replace(dateStr, "");
string[] temp = dateStr.Split('-');
dateStr = $"{temp[2]}-{temp[1]}-{temp[0]}";
}
else
{
dateStr = new Regex("[\u4e00-\u9fff]").Replace(dateStr, "");
dateStr = dateStr.Replace('/', '-').Replace('\\', '-').Replace('.', '-');
}
string[] dataArr = dateStr.Split('-');
if (dataArr.Length == 0)
{
return "0001-01-01";
}
string year = dataArr[0];
string month = dataArr.Length >= 1 ? dataArr[1] : "01";
string day = dataArr.Length >= 2 ? dataArr[2] : "01";
string date= $"{year}-{month}-{day}";
string format = $"{"".PadLeft(year.Length, 'y')}-{"".PadLeft(month.Length, 'M')}-{"".PadLeft(day.Length, 'd')}";
return DateTime.ParseExact(date, format, null).ToString("yyyy-MM-dd");
}