问题描述
在这里,我想使用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#的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!