Click here to Skip to main content
15,885,920 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

C#
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
Posted
Comments
Thanh Xuan Vu 14-Feb-15 3:03am    
If you want read data of excel file and insert to DB pls read this article:
http://www.codeproject.com/Tips/801032/Csharp-How-To-Read-xlsx-Excel-File-With-Lines-of
After that use should use these data read from excel file to make DataTable and insert to DB.
.net333 14-Feb-15 3:12am    
excel sheet data is reading and inserting but images are not reading from excel
.net333 14-Feb-15 3:13am    
i am inserting ExcelSheet Data ItemName, Description and Images to sql server db using c#

1 solution

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[^]
 
Share this answer
 
Comments
.net333 14-Feb-15 2:54am    
Hi Mika
thanks for your reply..can u send me a sample solution to upload excel sheet Data(ItemName, Description and Image) to sql server db using c#.net
Wendelius 14-Feb-15 3:27am    
Have a look at this: http://joymonscode.blogspot.fi/2009/01/reading-image-from-excel-file.html[^]. It doesn't contain the save to database code but there are a lot of examples for saving images to database.
.net333 14-Feb-15 3:30am    
Thank you
Wendelius 15-Feb-15 5:32am    
You're welcome
.net333 16-Feb-15 8:37am    
hi Mika
i used above code to read images from excel..
but i got one error while reading image

Error Code : Exception from HRESULT: 0x800A03EC

Here is my code


if (dtItem.Rows[i]["PicName"].ToString() != "")
{
Worksheet sheet = worksheetItemOption;
if (sheet != null)
{
string Pic = dtItem.Rows[i]["PicName"].ToString();
Picture pict = sheet.Pictures(Pic) as Picture;
pict.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlBitmap);
System.Drawing.Image img = Clipboard.GetImage();
MemoryStream ms = new MemoryStream();
img.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
byte[] bytes = ms.ToArray();
postImportRequest.CommonMenu.Image = bytes;
}


}

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900