将xls数据上传到数据库表中 [英] Upload xls data into database table

问题描述

每当我尝试将xls datasheet上传到我的数据库表时，我目前都遇到错误。我收到异常错误是：

异常详细信息 s：System.InvalidCastException：从'DateTime'到'Single'的转换无效。

数据源中DateTime类型的给定值无法转换为指定目标列的实数类型。



这是我的数据库表schema：

I am currently experiencing an error, whenever I try to upload xls datasheet into my database table. I am getting an exception error is:
Exception Details: System.InvalidCastException: Invalid cast from 'DateTime' to 'Single'.
The given value of type DateTime from the data source cannot be converted to type real of the specified target column.

This is my database table schema:

[id]
    ,[tag]
    ,[decimalP]
    ,[type]
    ,[UploadDate]
    ,[name]
FROM [dummy].[dbo].[database_data]

我尝试添加'for循环'对于转换日期时间的sqlBulkCopy属性，但我无法使其工作。



这是我的上传方法：

I tried adding a 'for loop' for sqlBulkCopy property for convert datetime, but I could not get that to work either.

Here is my Upload method:

protected void Upload(object sender, EventArgs e)
    {
        //Upload and save the file
        string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
        FileUpload1.SaveAs(excelPath);

        string conString = string.Empty;
        string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
        switch (extension)
        {
            case ".xls": //Excel 97-03
                conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;
            case ".xlsx": //Excel 07 or higher
                conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
                break;

        }
        conString = string.Format(conString, excelPath);
        using (OleDbConnection excel_con = new OleDbConnection(conString))
        {
            excel_con.Open();
            string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
            DataTable dtExcelData = new DataTable();

            using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
            {
                oda.Fill(dtExcelData);
            }
            excel_con.Close();

            string consString = ConfigurationManager.ConnectionStrings["dummyConnectionString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(consString))
            {
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                {
                    //Set the database table name

                    sqlBulkCopy.DestinationTableName = "dbo.comp2";

                    con.Open();
                    sqlBulkCopy.WriteToServer(dtExcelData);
                    con.Close();
                }
            }
        }
    }

任何帮助都将非常感谢。

非常感谢。

Any help would be very much appreciated.
Many thanks.

推荐答案

您使用的数据库是什么？该错误表明 UploadDate 列预计 Single 但获得 DateTime 。



如果你调试，日期字段是什么样的？您可能会发现它的格式为 12345.98765 ，即 Excel如何存储内部日期。

What database are you using? The error suggests that the UploadDate column is expecting a Single but getting a DateTime.

If you debug, what does the date field look like? You may find it is in the format 12345.98765, which is how Excel stores dates internally.

这篇关于将xls数据上传到数据库表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！