将图像从Excel表导入SQL Server表C＃ [英] Import image from Excel sheet into SQL Server table C#

问题描述

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

我有这样的Excel表格

  ID名称名称ProfilePicture 
 -------- ------------------------------------ 
 1 ABC经理C：\Pictures\ 1.jpg 
 2 DEF Asst.Manager C：\Pictures\2.jpg 
  

我有代码将数据插入表

  String filePath = filePathText.Text; 
 String fileExtension =Excel 12.0; 
 if（filePath！= null）
 {
 String xlsConnection = @Provider = Microsoft.ACE.OLEDB.12.0; Data Source =+ filePath +;扩展属性=+ \+ fileExtension +; HDR = YES; \; 
 
 String sqlConnection =Your Connection String; 
 
 //连接到Excel工作簿
 OleDbConnection xlsConnectionString = new OleDbConnection（xlsConnection）; 
 
 //从Excel中获取数据
 OleDbCommand cmd = new OleDbCommand（从[Sheet1 $]中选择[ID]，[名称]，[名称]，[ProfilePicture]，xlsConnectionString） ; 
 
 xlsConnectionString.Open（）; 
 
 OleDbDataReader dReader; 
 dReader = cmd.ExecuteReader（）; 
 SqlBulkCopy sqlBulk = new SqlBulkCopy（sqlConnection）; 
 
 //目标表名称
 sqlBulk.DestinationTableName =EXCEL_DATA; 
 
 sqlBulk.WriteToServer（dReader）; 
 
 xlsConnectionString.Close（）; 
} 
  

如果我点击按钮，这段代码就会运行。 >

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

解决方案

感谢那些真正努力发布答案的人。最后我自己得到了解决问题的方法。

这里是有助于将图像插入到 SQL Server 通过使用 Excel表格中提供的路径。

  private void insert_Click（object sender，EventArgs e）
 {
 
 UInt64 ID = 0; 
 String Name = String.Empty; 
 String指定= 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 =你的连接字符串到这里; 
 
 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; （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（））; 
 
 //检查每次运行的xlRange。并将值分配给局部变量。这里我只是使用MsgBox 
 
显示值//如果你得到图像的路径，然后调用函数将图像转换为字节
 
 //将图像转换为字节函数定义。 
 
 / * 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（@名称，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）+已成功导入到SQL Server，Result，MessageBoxButtons.OK，MessageBoxIcon.Information）; 
} 
 catch（Exception ex）
 {
 MessageBox.Show（ex.Message，Exception，MessageBoxButtons.OK，MessageBoxIcon.Error）; 
} 
 finally 
 {
 //释放所有对象并关闭连接以防止Excel文件锁定。 
 
 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（请选择要导入的有效文件）; 
} 
} 
  

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

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

I have the Excel sheet with data like this

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.

解决方案

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.

    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 Server表C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！