将Excel工作表中的图像导入SQL Server表C＃

Here I want to insert all the data from my excel sheet into the SQL Server Table using C# code

在这里，我想使用C＃代码将Excel工作表中的所有数据插入到SQL Server表中

I have the Excel sheet with data like this

我的Excel表格包含这样的数据

ID    Name    Designation    ProfilePicture
--------------------------------------------
1    ABC      Manager        C:\Pictures\1.jpg
2    DEF      Asst.Manager   C:\Pictures\2.jpg

And I have the Code to Insert the Datas into the Table

我有代码将数据插入表中

String filePath = filePathText.Text;
String fileExtension = "Excel 12.0";
if (filePath != null)
{
    String xlsConnection = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=" + "\"" + fileExtension + ";HDR=YES;\"";

    String sqlConnection = "Your Connection String";

    //Connection to Excel work book
    OleDbConnection xlsConnectionString = new OleDbConnection(xlsConnection);

    //Fetch data from Excel
    OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Designation],[ProfilePicture] from [Sheet1$]", xlsConnectionString);

    xlsConnectionString.Open();

    OleDbDataReader dReader;
    dReader = cmd.ExecuteReader();
    SqlBulkCopy sqlBulk = new SqlBulkCopy(sqlConnection);

    //Destination table name
    sqlBulk.DestinationTableName = "EXCEL_DATA";

    sqlBulk.WriteToServer(dReader);

    xlsConnectionString.Close();
}

This piece of code is run, if I click the button.

如果单击按钮，则会运行此段代码。

My Question is, How can I upload the picture from Excel Sheet (Sheet have the path of the image). to SQL Server Table. I want to get the picture by using the Imagepath provided in Excel Sheet and store it as varbinary(MAX) in SQL Server.

我的问题是，如何从Excel工作表上传图片（工作表具有图像的路径）。到SQL Server表。我想通过使用Excel Sheet中提供的Imagepath来获取图片，并将其作为varbinary（MAX）存储在SQL Server中。

1 个解决方案

#1

Thanks for the guys who really works to post the answer. Finally I got the solution to the problem myself.

感谢真正努力发布答案的人。最后我自己解决了这个问题。

Here is the code that helps to insert the images into the SQL Server by using the Path provided in Excel sheet.

下面的代码有助于使用Excel工作表中提供的路径将图像插入SQL Server。

    private void insert_Click(object sender, EventArgs e)
    {

        UInt64 ID = 0;
        String Name = String.Empty;
        String Designation = String.Empty;
        String ProfilePicture = String.Empty;

        String filePath = filePathText.Text;

        Excel.Application xlApp = null;
        Excel.Workbook xlWorkbook = null;
        Excel._Worksheet xlWorksheet = null;
        Excel.Range xlRange = null;

        String sqlConnectionString = "Your Connection String goes here";

        String insertRecord = "INSERT_USER_RECORDS";

        SqlConnection sqlConnection = new SqlConnection(sqlConnectionString);
        SqlCommand sqlCommand = new SqlCommand(insertRecord, sqlConnection);
        sqlCommand.CommandType = CommandType.StoredProcedure;

        sqlConnection.Open();

        if (filePath != null)
        {

            try
            {

                xlApp = new Excel.Application();
                xlWorkbook = xlApp.Workbooks.Open(filePath);
                xlWorksheet = (Excel._Worksheet)xlWorkbook.Sheets[1];
                xlRange = xlWorksheet.UsedRange;

                int rowCount = xlRange.Rows.Count;
                int colCount = xlRange.Columns.Count;

                for (int row = 1; row <= rowCount; row++)
                {
                    for (int col = 1; col <= colCount; col++)
                    {
                        MessageBox.Show((xlRange.Cells[row, col] as Microsoft.Office.Interop.Excel.Range).Value2.ToString());

                        // Check xlRange for Every run. And assign values to local variables. Here I just show the values using MsgBox

                        // If you get the Path of Image then call the function to Convert Image into byte

                        // Convert Image to Byte Function definition.

                        /* System.IO.FileStream fs = new System.IO.FileStream(ProfilePicture, System.IO.FileMode.Open);
                           Byte[] imageAsBytes = new Byte[fs.Length];
                           fs.Read(imageAsBytes, 0, imageAsBytes.Length);
                           fs.Close();
                           return imageAsBytes; */

                    }

                    sqlCommand.Parameters.Clear();
                    sqlCommand.Parameters.Add("@Name", SqlDbType.NVarChar).Value = FirstName;
                    sqlCommand.Parameters.Add("@Designation", SqlDbType.NVarChar).Value = LastName;
                    sqlCommand.Parameters.Add("@ProfilePicture", SqlDbType.VarBinary).Value = imageAsBytes;
                    sqlCommand.Parameters.Add("@ID", SqlDbType.BigInt).Value = ID;

                    sqlCommand.ExecuteNonQuery();
                }


                MessageBox.Show(Path.GetFileName(filePath) + "is Successfully imported to SQL Server", "Result", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                //Release All objects and close the Connection to prevent the Excel file from lock.

                sqlConnection.Close();

                GC.Collect();
                GC.WaitForPendingFinalizers();

                Marshal.FinalReleaseComObject(xlRange);
                Marshal.FinalReleaseComObject(xlWorksheet);

                xlWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
                Marshal.FinalReleaseComObject(xlWorkbook);

                xlApp.Quit();
                Marshal.FinalReleaseComObject(xlApp);

            }
        }
        else
        {
            MessageBox.Show("Please Select the Valid file to import");
        }
    }

This code works fine and helps me to insert the image into the SQL database from Excel. No matter about the version of excel file.

此代码工作正常，并帮助我将图像从Excel插入SQL数据库。无论excel文件的版本如何。

#1