c# - 如何获取从网站上传的 Excel 文件并以编程方式将数据导入 SQL Server？

Question

我正在尝试做这样的事情:

public void ImportClick(object sender, EventArgs e) //the button used after selecting the spreadsheet file
{
    if (fileUpload.HasFile) //ASP.Net FileUpload control
    {
        if (fileUpload.FileName.EndsWith(".xls", StringComparison.OrdinalIgnoreCase) || fileUpload.FileName.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase))
        {
            Excel sheet = new Excel(fileUpload.Open()); //not sure how to do this part

            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["our_database"].ConnectionString))
            {
                using (SqlCommand command = new SqlCommand("INSERT INTO table_name SELECT * FROM " + sheet, connection))
                {
                    connection.Open();
                    command.ExecuteQuery(); //Intellisense only has "ExecuteNonQuery()" method, but that's for T-SQL
                    connection.Close();
                }
            }
        }
        else
        {
            error.Text = "File must be either *.xls or *.xlsx";
            error.Visible = true;
        }
    }
    else
    {
        error.Text = "No file was selected";
        error.Visible = true;
    }
}

Microsoft.Office.Interop.Excel namespace里面有很多类和接口(interface)，我不知道该用哪个。

我知道制作 Excel 对象和 SQL 命令可能不像我在这里做的那么容易，但这是我需要帮助的两件事。

如有任何建议/建议，我们将不胜感激!

Answer 1

我建议使用 Microsoft Jet Engine。

private static void UploadExcelToDB(string p)
    {
        try
        {
            using (SqlConnection conn = new SqlConnection(DBConnString))
            {
                conn.Open();
                if (conn.State == ConnectionState.Open)
                {
                    Log("Opened connection to DB");
                }
                SqlBulkCopy sbk = new SqlBulkCopy(conn);
                sbk.BulkCopyTimeout = 600;
                sbk.DestinationTableName = DbTableName;
                DataTable excelDT = new DataTable();
                OleDbConnection excelConn = new OleDbConnection(ExcelConnString.Replace("xFILEx",p));
                excelConn.Open();
                if (excelConn.State == ConnectionState.Open)
                {
                    Log("Opened connection to Excel");
                }
                OleDbCommand cmdExcel = new OleDbCommand();
                OleDbDataAdapter oda = new OleDbDataAdapter();
                cmdExcel.CommandText = "SELECT * FROM ["+ExcelTableName+"]";
                cmdExcel.Connection = excelConn;
                oda.SelectCommand = cmdExcel;
                oda.Fill(excelDT);
                if (excelDT != null)
                {
                    Log("Fetched records to local Data Table");
                }
                excelConn.Close();
                SqlCommand sqlCmd = new SqlCommand("TRUNCATE TABLE ICN_NUGGET_REPORT_RAW",conn);
                sqlCmd.CommandType = CommandType.Text;
                Log("Trying to clear current data in table");
                int i = sqlCmd.ExecuteNonQuery();
                Log("Table flushed");
                Log("Trying write new data to server");
                sbk.WriteToServer(excelDT);
                Log("Written to server");
                conn.Close();
            }
        }
        catch (Exception ex)
        {
            Log("ERROR: " + ex.Message);
            SendErrorReportMail();
        }
        finally
        {
            #if (DEBUG)
            {
            }
            #else
            {
            string archive_file = ArchiveDir+"\\" + DateTime.Now.ToString("yyyyMMdd-Hmmss") + ".xlsx";
            File.Move(p, archive_file);
            Log("Moved processed file to archive dir");
            Log("Starting archive process...");
            }
            #endif
        }
    }

这是 ExcelConnString 的样子:

public static string ExcelConnString { get { return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=xFILEx;Extended Properties=\"Excel 12.0;HDR=YES\";";} }

HDR=YES - 这意味着如果您在电子表格中有列名，它将被视为相互匹配的目标表列名。