c# OleDB，从Excel导入，格式不好

Question

我使用OleDB从Excel导入到DataGridView。我要导入的单元格有这个字符串"01/01/2029"但是当我使用OleDB导入到DataGridView时，该字符串转换为日期时间01/01/2029 0:00:00"当我想保持字符串与他原来的格式&;01/01/2029&;。我试图转换它和一堆东西，我找不到解决方案…有人能找到问题在哪里吗?

string filePath = openFileDialog1.FileName;
string extension = Path.GetExtension(filePath);
string header = "YES";
string conStr, sheetName;
conStr = string.Empty;
switch (extension)
{
case ".xls":
conStr = string.Format(Excel03ConString, filePath, header);
break;
case ".xlsx":
conStr = string.Format(Excel07ConString, filePath, header);
break;
}
using (OleDbConnection con = new OleDbConnection(conStr))
{
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.Connection = con;
con.Open();
DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
con.Close();
}
}
using (OleDbConnection con = new OleDbConnection(conStr))
{
using (OleDbCommand cmd = new OleDbCommand())
{
using (OleDbDataAdapter oda = new OleDbDataAdapter())
{
DataTable dt = new DataTable();
cmd.CommandText = "SELECT * From [" + sheetName + "]";
cmd.Connection = con;
con.Open();
oda.SelectCommand = cmd;
oda.Fill(dt);
con.Close();
dataGridView1.DataSource = dt;
}
}
}

Answer 1

正如你的评论所指出的…"所以我可以用Replace("/01/"， "JAN"); "…这是不必要的。您很少会想要将DateTime对象"更改"为string，这只会给您带来更多的工作。您可以"格式化"网格DateTime列，以产生您想要显示给用户的string的任何日期/时间类型。

示例…针对您的情况，我建议您将网格DateTime列的格式设置为…

dataGridView1.Columns[1].DefaultCellStyle.Format = "MMM dd, yyyy";

会生成类似于" Jan 01, 2021 "的内容

或者在格式中添加额外的" M "…

dataGridView1.Columns[1].DefaultCellStyle.Format = "MMMM dd, yyyy";

会产生类似于…" January 01, 2021 "