将ExcelSheet数据和图像导入到c＃中的sql server数据库 [英] Import ExcelSheet Data and Images to sql server data base in c#
本文介绍了将ExcelSheet数据和图像导入到c＃中的sql server数据库的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
嗨朋友，
i想要浏览excel表格数据并将其导入sql server数据库..
数据导入已完成且工作正常..但图像无法上传。 。
这是我的代码
protected void btnupload_Click（ object sender，ImageClickEventArgs e）
{
UploadDetails。另存为（使用Server.Mappath（UploadDetails.FileName））;
string fileName = UploadDetails.ResolveClientUrl（UploadDetails.PostedFile.FileName）;
ViewState [ FileName] = fileName;
System.Data.DataTable dtAppDesign = new System.Data.DataTable（）;
string SourceConstr = @ Provider = Microsoft .ACE.OLEDB.12.0;数据源= + Server.MapPath（fileName）+ ;扩展属性='Excel 8.0; HDR =是; IMEX = 1';
OleDbConnection con = new OleDbConnection（SourceConstr）;
string finalFileNameWithPath = string .Format（Server.MapPath（fileName））;
FileInfo workBook = new FileInfo（Server.MapPath（fileName））;
System.Data.DataTable dtItem = new System.Data.DataTable（）;
string queryItem = 选择*来自[项$];
OleDbDataAdapter dataItem = new OleDbDataAdapter（queryItem，con）;
dataItem.Fill（dtItem）;
dtItem.Columns.Add（ 名称）;
dtItem.Columns.Add（ Desc）;
dtItem.Columns.Add（ Pic）;
ImportItemsOnly（dtItem，fileName）;
}
private void ImportItemsOnly（System.Data.DataTable dtItem， string fileName）
{
try
{
FileInfo workBook = new FileInfo（Server.MapPath（fileName））;
使用（ExcelPackage xlPackage = new ExcelPackage（workBook））
{
ExcelWorksheet worksheetItemOption = xlPackage.Workbook.Worksheets [ CommonMenuItems];
for （ int i = 0 ; i < dtItem.Rows.Count; i ++）
{
try
{
if （dtItem.Rows [i] [ 1 ] .ToString（）！= || dtItem.Rows [i] [ 1 ]。ToString（）！= ）
{
尝试
{
postImportRequest.CommonMenu = new CommonMenu（）;
postImportRequest.Action = InsertCommonItems;
if （dtItem.Rows [i] [ ItemName]。ToString（）！= ）
{
postImportRequest.CommonMenu.ItemName = dtItem.Rows [i] [ ItemName< /跨度>]的ToString（）;
}
if （dtItem.Rows [i] [ 描述]。ToString（）！= ）
{
postImportRequest.CommonMenu.Description = dtItem.Rows [i] [ Description ]的ToString（）;
}
if （dtItem.Rows [i] [ 图像]。ToString（）！= ）
{
string ImgfileName = dtItem.Rows [i] [ 图像]。ToString（）;
string fileExtension = Path.GetExtension（dtItem.Rows [i] [ 图像]的ToString（））;
string NewFileName = GenerateCode（）+ fileName;
byte [] buffer = File.ReadAllBytes（ImgfileName）;
postImportRequest.CommonMenu.Image = buffer;
}
postImportResponse = ImportService.CommonItems（postImportRequest）;
if （postImportResponse.SuccessMsg.ResponseErrors [ 0 ]。SuccessMsg == 已成功插入）
{
lblmsg.Text = 已成功导入;
}
}
}
xlPackage.Save（）;
}
请帮我解决这个问题问题
提前致谢
解决方案
;
OleDbDataAdapter dataItem = new OleDbDataAdapter（queryItem，con）;
dataItem.Fill（dtItem）;
dtItem.Columns.Add（ 名称）;
dtItem.Columns.Add（ Desc）;
dtItem.Columns.Add（ Pic）;
ImportItemsOnly（dtItem，fileName）;
}
private void ImportItemsOnly（System.Data.DataTable dtItem， string fileName）
{
tr y
{
FileInfo workBook = new FileInfo（Server.MapPath（fileName））;
使用（ExcelPackage xlPackage = new ExcelPackage（workBook））
{
ExcelWorksheet worksheetItemOption = xlPackage.Workbook.Worksheets [ CommonMenuItems];
for （ int i = 0 ; i < dtItem.Rows.Count; i ++）
{
try
{
if （dtItem.Rows [i] [ 1 ] .ToString（）！= || dtItem.Rows [i] [ 1 ]。ToString（）！= ）
{
尝试
{
postImportRequest.CommonMenu = new CommonMenu（）;
postImportRequest.Action = InsertCommonItems;
if （dtItem.Rows [i] [ ItemName]。ToString（）！= ）
{
postImportRequest.CommonMenu.ItemName = dtItem.Rows [i] [ ItemName< /跨度>]的ToString（）;
}
if （dtItem.Rows [i] [ 描述]。ToString（）！= ）
{
postImportRequest.CommonMenu.Description = dtItem.Rows [i] [ Description ]的ToString（）;
}
if （dtItem.Rows [i] [ 图像]。ToString（）！= ）
{
string ImgfileName = dtItem.Rows [i] [ 图像]。ToString（）;
string fileExtension = Path.GetExtension（dtItem.Rows [i] [ 图像]的ToString（））;
string NewFileName = GenerateCode（）+ fileName;
byte [] buffer = File.ReadAllBytes（ImgfileName）;
postImportRequest.CommonMenu.Image = buffer;
}
postImportResponse = ImportService.CommonItems（postImportRequest）;
if （postImportResponse.SuccessMsg.ResponseErrors [ 0 ]。SuccessMsg == 已成功插入）
{
lblmsg.Text = 已成功导入;
}
}
}
xlPackage.Save（）;
}
请帮我解决这个问题问题
提前致谢
如果我理解正确的问题，你不能使用OleDb从Excel文件中选择图像连接。该连接能够获取Excel工作表的单元格中的数据，但不能获取对象。
如果要提取我建议使用的对象，例如interop to do那。请查看 Microsoft.Office.Interop.Excel命名空间 [< a href =https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.aspx\"target =_ blanktitle =New Window> ^ ]
Hi Friends,
i want to browse and import excel sheet data to sql server database..
Data importing completed and working fine..but images are not uploading..
here is my code
protected void btnupload_Click(object sender, ImageClickEventArgs e)
{
UploadDetails.SaveAs(Server.MapPath(UploadDetails.FileName));
string fileName = UploadDetails.ResolveClientUrl(UploadDetails.PostedFile.FileName);
ViewState["FileName"] = fileName;
System.Data.DataTable dtAppDesign = new System.Data.DataTable();
string SourceConstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(fileName) + ";Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'";
OleDbConnection con = new OleDbConnection(SourceConstr);
string finalFileNameWithPath = string.Format(Server.MapPath(fileName));
FileInfo workBook = new FileInfo(Server.MapPath(fileName));
System.Data.DataTable dtItem = new System.Data.DataTable();
string queryItem = "Select * from [Items$]";
OleDbDataAdapter dataItem = new OleDbDataAdapter(queryItem, con);
dataItem.Fill(dtItem);
dtItem.Columns.Add("Name");
dtItem.Columns.Add("Desc");
dtItem.Columns.Add("Pic");
ImportItemsOnly(dtItem, fileName);
}
private void ImportItemsOnly(System.Data.DataTable dtItem, string fileName)
{
try
{
FileInfo workBook = new FileInfo(Server.MapPath(fileName));
using (ExcelPackage xlPackage = new ExcelPackage(workBook))
{
ExcelWorksheet worksheetItemOption = xlPackage.Workbook.Worksheets["CommonMenuItems"];
for (int i = 0; i < dtItem.Rows.Count; i++)
{
try
{
if (dtItem.Rows[i][1].ToString() != "" || dtItem.Rows[i][1].ToString() != "")
{
try
{
postImportRequest.CommonMenu = new CommonMenu();
postImportRequest.Action = "InsertCommonItems";
if (dtItem.Rows[i]["ItemName"].ToString() != "")
{
postImportRequest.CommonMenu.ItemName = dtItem.Rows[i]["ItemName"].ToString();
}
if (dtItem.Rows[i]["Description"].ToString() != "")
{
postImportRequest.CommonMenu.Description = dtItem.Rows[i]["Description"].ToString();
}
if (dtItem.Rows[i]["Image"].ToString() != "")
{
string ImgfileName = dtItem.Rows[i]["Image"].ToString();
string fileExtension = Path.GetExtension(dtItem.Rows[i]["Image"].ToString());
string NewFileName = GenerateCode() + fileName;
byte[] buffer = File.ReadAllBytes(ImgfileName);
postImportRequest.CommonMenu.Image = buffer;
}
postImportResponse = ImportService.CommonItems(postImportRequest);
if (postImportResponse.SuccessMsg.ResponseErrors[0].SuccessMsg == "Successfully Inserted")
{
lblmsg.Text = "Successfully Imported";
}
}
}
xlPackage.Save();
}
Please help me to solve this issue
Thanks in advance
解决方案
"; OleDbDataAdapter dataItem = new OleDbDataAdapter(queryItem, con); dataItem.Fill(dtItem); dtItem.Columns.Add("Name"); dtItem.Columns.Add("Desc"); dtItem.Columns.Add("Pic"); ImportItemsOnly(dtItem, fileName); } private void ImportItemsOnly(System.Data.DataTable dtItem, string fileName) { try { FileInfo workBook = new FileInfo(Server.MapPath(fileName)); using (ExcelPackage xlPackage = new ExcelPackage(workBook)) { ExcelWorksheet worksheetItemOption = xlPackage.Workbook.Worksheets["CommonMenuItems"]; for (int i = 0; i < dtItem.Rows.Count; i++) { try { if (dtItem.Rows[i][1].ToString() != "" || dtItem.Rows[i][1].ToString() != "") { try { postImportRequest.CommonMenu = new CommonMenu(); postImportRequest.Action = "InsertCommonItems"; if (dtItem.Rows[i]["ItemName"].ToString() != "") { postImportRequest.CommonMenu.ItemName = dtItem.Rows[i]["ItemName"].ToString(); } if (dtItem.Rows[i]["Description"].ToString() != "") { postImportRequest.CommonMenu.Description = dtItem.Rows[i]["Description"].ToString(); } if (dtItem.Rows[i]["Image"].ToString() != "") { string ImgfileName = dtItem.Rows[i]["Image"].ToString(); string fileExtension = Path.GetExtension(dtItem.Rows[i]["Image"].ToString()); string NewFileName = GenerateCode() + fileName; byte[] buffer = File.ReadAllBytes(ImgfileName); postImportRequest.CommonMenu.Image = buffer; } postImportResponse = ImportService.CommonItems(postImportRequest); if (postImportResponse.SuccessMsg.ResponseErrors[0].SuccessMsg == "Successfully Inserted") { lblmsg.Text = "Successfully Imported"; } } } xlPackage.Save(); }
Please help me to solve this issue
Thanks in advance
If I understood the question correctly, you cannot select the images from an Excel file using an OleDb connection. The connection is able to fetch the data in the cells of an Excel worksheet but not objects.
If you want to extract the objects I recommend using for example interop to do that. Have a look at Microsoft.Office.Interop.Excel namespace[^]
这篇关于将ExcelSheet数据和图像导入到c＃中的sql server数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
