C#将excel文件表导入sql数据库报错 [英] C# import excel filesheet to sql database error

问题描述

我遇到了错误:多步 OLE DB 操作生成错误.检查每个 OLE DB 状态值(如果可用).没有完成任何工作.我使用的是 SQL Server 2005 和 Visual Studio 2005.

I have met the error: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. I'm using SQL Server 2005 and Visual Studio 2005.

我在将 excel 文件表导入到我的 sql 表中的表时遇到此错误.下面是我的代码:

I met this error during the import of an excel filesheet to a table in my sql table. Below is my code:

#region Using directives
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;
#endregion Using directives

namespace CSASPNETExcelImportExport
{
public partial class ExcelImport : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    // Get the row counts in SQL Server table. 
    protected int GetRowCounts()
    {
        int iRowCount = 0;

        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Data Source=<IP>;Initial Catalog=SOD;Persist Security Info=True;User ID=<username>;Password=<password>"].ToString()))
        {
            SqlCommand cmd = new SqlCommand("select count(*) from <database>", conn);
            conn.Open();

            // Execute the SqlCommand and get the row counts. 
            iRowCount = (int)cmd.ExecuteScalar();
        }

        return iRowCount;
    }

    // Retrieve data from the Excel spreadsheet. 
    protected DataTable RetrieveData(string strConn)
    {
        DataTable dtExcel = new DataTable();

        using (OleDbConnection conn = new OleDbConnection(strConn))
        {
            // Initialize an OleDbDataAdapter object. 
            OleDbDataAdapter da = new OleDbDataAdapter("select * from <database>", conn);

            // Fill the DataTable with data from the Excel spreadsheet. 
            da.Fill(dtExcel);
        }

        return dtExcel;
    }

    // Import the data from DataTable to SQL Server via SqlBulkCopy 
    protected void SqlBulkCopyImport(DataTable dtExcel)
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Data Source=<ip>;Initial Catalog=SOD;Persist Security Info=True;User ID=<username>;Password=<password>"].ToString()))
        {
            // Open the connection. 
            conn.Open();

            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
            {
                // Specify the destination table name. 
                bulkCopy.DestinationTableName = "<database>";

                foreach (DataColumn dc in dtExcel.Columns)
                {
                    // Because the number of the test Excel columns is not  
                    // equal to the number of table columns, we need to map  
                    // columns. 
                    bulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
                }

                // Write from the source to the destination. 
                bulkCopy.WriteToServer(dtExcel);
            }
        }
    }

    protected void btnImport_Click(object sender, EventArgs e)
    {
        // Before attempting to import the file, verify 
        // that the FileUpload control contains a file. 
        if (fupExcel.HasFile)
        {
            // Get the name of the Excel spreadsheet to upload. 
            string strFileName = Server.HtmlEncode(fupExcel.FileName);

            // Get the extension of the Excel spreadsheet. 
            string strExtension = Path.GetExtension(strFileName);

            // Validate the file extension. 
            if (strExtension != ".xls" && strExtension != ".xlsx")
            {
                Response.Write("<script>alert('Please select a Excel spreadsheet to import!');</script>");
                return;
            }

            // Generate the file name to save. 
            string strUploadFileName = "~/UploadFiles/" + DateTime.Now.ToString("yyyyMMddHHmmss") + strExtension;

            // Save the Excel spreadsheet on server. 
            fupExcel.SaveAs(Server.MapPath(strUploadFileName));

            // Generate the connection string for Excel file. 
            string strExcelConn = "";

            // There is no column name In a Excel spreadsheet.  
            // So we specify "HDR=YES" in the connection string to use  
            // the values in the first row as column names.  
            if (strExtension == ".xls")
            {
                // Excel 97-2003 
                strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(strUploadFileName) + ";Extended Properties=\"Excel 8.0;HDR=Yes;\"";

                //if the above doesn't work, you may need to prefix OLEDB; to the string, e.g.
                //strExcelConn = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<URL>" + Server.MapPath(strUploadFileName) + ";Extended Properties=\"Excel 8.0;HDR=Yes;\"";
            }
            else
            {
                // Excel 2007 
                strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<URL>" + Server.MapPath(strUploadFileName) + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
            }

            DataTable dtExcel = RetrieveData(strExcelConn);

            // Get the row counts before importing. 
            int iStartCount = GetRowCounts();

            // Import the data. 
            SqlBulkCopyImport(dtExcel);

            // Get the row counts after importing. 
            int iEndCount = GetRowCounts();

            // Display the number of imported rows.  
            lblMessages.Text = Convert.ToString(iEndCount - iStartCount) + " rows were imported into Person table";

            if (rblArchive.SelectedValue == "No")
            {
                // Remove the uploaded Excel spreadsheet from server. 
                File.Delete(Server.MapPath(strUploadFileName));
            }
        }
    }
}
}

是不是因为我的连接字符串错误?

Is it because my connectionstring error?

我已经按照 Nathan 的建议更改了我的连接字符串.现在我遇到了以下错误:

I have changed my connection strings as of what Nathan suggested. Now I met the following error:

http://i.stack.imgur.com/nWyte.png

这次是什么错误?:

推荐答案

看起来您的连接字符串实际上有几个问题.一方面，Excel 连接字符串不应包含初始目录"，它们应包含引用文件的数据源，而不是服务器.

It looks like you actually have several things wrong with your connection strings. For one thing, Excel connection strings should not include an "Initial Catalog", and they should include a Data Source referring to the file, not a server.

试试这个:

        // There is no column name In a Excel spreadsheet.  
        // So we specify "HDR=YES" in the connection string to use  
        // the values in the first row as column names.  
        if (strExtension == ".xls")
        {
            // Excel 97-2003 
            strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(strUploadFileName) + ";Extended Properties=\"Excel 8.0;HDR=Yes;\"";

            //if the above doesn't work, you may need to prefix OLEDB; to the string, e.g.
            //strExcelConn = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(strUploadFileName) + ";Extended Properties=\"Excel 8.0;HDR=Yes;\"";
        }
        else
        {
            // Excel 2007 
            strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(strUploadFileName) + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
        }

仅供参考，http://connectionstrings.com 是处理此类事情的好资源.

Fyi, http://connectionstrings.com is a good resource for these kind of things.

这篇关于C#将excel文件表导入sql数据库报错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！