从 Excel 加载数据表：无法将类型为"System.DateTime"的对象转换为类型"System.String"

Question

我正在尝试创建一个数据表，读取一个excel文件，该文件有一个DateTime列，其他所有列都是字符串。当尝试将DateTime列转换为DateTime Datatype时，我收到一个异常"无法将类型为"System.DateTime"的对象强制转换为类型为"System.String"。请有人指导我如何解决此问题。

try
{
string Ext = Path.GetExtension(Constants.XlFile);
string connectionString = "";
if (Ext == ".xls")
{   //For Excel 97-03
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" + Constants.XlFile + "; Extended Properties = 'Excel 8.0;HDR=YES'";
}
else if (Ext == ".xlsx")
{    //For Excel 07 and greater
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source =" + Constants.XlFile + "; Extended Properties = 'Excel 8.0;HDR=YES'";
}
OleDbConnection conn = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
cmd.Connection = conn;
conn.Open();
DataTable dtSchema;
dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string ExcelSheetName = dtSchema.Rows[0]["TABLE_NAME"].ToString();
conn.Close();
//Read all data of fetched Sheet to a Data Table
conn.Open();
cmd.CommandText = "SELECT * From [" + ExcelSheetName + "]";
dataAdapter.SelectCommand = cmd;
dataAdapter.FillSchema(dt, SchemaType.Source);
dt.Columns[0].DataType = typeof(String);
dt.Columns[1].DataType = typeof(String);
dt.Columns[2].DataType = typeof(String);
dt.Columns[3].DataType = typeof(String);
dt.Columns[4].DataType = typeof(DateTime);
dt.Columns[5].DataType = typeof(String);
dt.Columns[6].DataType = typeof(String);
dt.Columns[7].DataType = typeof(String);
//dataGrid2.Columns[4].CellTemplate.ValueType = typeof(DateTime);
dataAdapter.Fill(dt);
dataGrid2.DataSource = dt;
conn.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}

Answer 1

尝试加载到数据读取器中并循环值。

SqlDataReader dr = cmd.ExecuteReader();

foreach (DataRow drow in dtSchema.Rows)
{
//manually add columns here from dr
}

请访问此处了解详细信息：从数据读取器填充数据表