none
How to read data from the cell of excel with image RRS feed

  • Question

  • I am facing problem to read image from the cell of excel sheet. Actually i am inserting data from the excel into sql server and if any cell containing image the we need to fetch this image and convert into base 64 and then insert data in the sql server. How to fetch this data with Image. If any one have idea please share. I am using this code:

    protected void btnImport_Click(object sender, EventArgs e)
        {
            string ConStr = "";
            //Extantion of the file upload control saving into ext because   
            //there are two types of extation .xls and .xlsx of Excel   
            string ext = Path.GetExtension(FileUpload1.FileName).ToLower();
            //getting the path of the file   
            string path = Server.MapPath("~/Uploads/" + FileUpload1.FileName);
            //saving the file inside the MyFolder of the server  
            FileUpload1.SaveAs(path);
    
            //checking that extantion is .xls or .xlsx  
            if (ext.Trim() == ".xls")
            {
                //connection string for that file which extantion is .xls  
                ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            }
            else if (ext.Trim() == ".xlsx")
            {
                //connection string for that file which extantion is .xlsx  
                ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }
            //making query  
            string query = "SELECT * FROM [Sheet1$]";
            //Providing connection  
            OleDbConnection conn = new OleDbConnection(ConStr);
            //checking that connection state is closed or not if closed the   
            //open the connection  
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            //create command object  
            OleDbCommand cmd = new OleDbCommand(query, conn);
            // create a data adapter and get the data into dataadapter  
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            //fill the Excel data to data set  
            da.Fill(ds);
            //set data source of the grid view  
            gvExcelFile.DataSource = ds.Tables[0];
            //binding the gridview  
            gvExcelFile.DataBind();
            //close the connection  
            conn.Close();
           this.QuestionPaperDataset = ds;
        }

    • Moved by CoolDadTx Thursday, June 30, 2016 4:23 PM Office related
    Thursday, June 30, 2016 3:09 PM

All replies

  • Hi Azeez Ansari,

    please visit the link below will give you an example of to upload an image from excel file and store in to sql server.

    Import image from Excel sheet into SQL Server table C#

    Hope this will solves your problem. if not please let us know then we will try to provide you further suggestion.

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 1, 2016 12:56 AM
    Moderator
  • Hello , I do not have image path , I have image in the excel sheet.   My question is how to insert image in the sql table or we can convert this image in the base 64 for inserting the data  in the sql server table.

    Friday, July 1, 2016 5:27 AM
  • Hi Azeez Ansari,

    use the code below to convert an image to base64.

    public string convertimg()
            {
                string path = @"C:\Users\v-padee\Desktop\myimg\c2.png";
                using (System.Drawing.Image image = System.Drawing.Image.FromFile(path))
                {
                    using (MemoryStream m = new MemoryStream())
                    {
                        image.Save(m, image.RawFormat);
                        byte[] imageBytes = m.ToArray();
                        base64String = Convert.ToBase64String(imageBytes);
                        return base64String;
                    }
                }
            }

    it will give you output like below which you can pass in sql.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 1, 2016 12:18 PM
    Moderator
  • Hello, Deepak

                  I think you did not understand my question.  I am clearing that . I have excel sheet with three column like as

    UserName    MobNo    UsrprofileImge      

    ABC              123456      Imag1

    xyz               987654      Image2        

    and so on. Image 1 and Image2 is the actual Image not a path. I am inserting data by the above method (which is mention in the question).

    I want to fetch the data  from the excel sheet with this image column and if column contain image then also we need insert data in the sql server. How to do this?

    Monday, July 4, 2016 5:56 AM
  • Hi Azeez Ansari,

    you can see in the code that I also use the object of image to convert it to base64.

    I used an image path to assign the image to the image object.

    it is converting image as base64 not a path.

    because we need a source of image to assign it to image object. directly how can we convert it.

    please see the code again.

    you have to assign your image to this function and you will get it converted in to base64.

    Hope now you are clear about it.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, July 5, 2016 6:48 AM
    Moderator
  • Hello, Deepak

    I have no Image path. I have image in the excel sheet. I have no idea that  how to fetch image from the cell of excel sheet.  I know that how to convert image in the base64.

    I want to know how to fetch all images and data from the cell of excel sheet (one by one)?

    Regards

    Azeez Ansari

    Wednesday, July 6, 2016 8:27 AM
  • Hi Azeez Ansari,

    can you tell us how you had get the data in Excel file?

    using manually entry of data in Excel or you store the data in excel using code?

    can you show us the snap shot of the excel file?

    are you storing the image using picture control or just directly on the sheet?

    it will help us to provide suggestion to you.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 6, 2016 8:44 AM
    Moderator
  • Hi Azeez Ansari,

    if you are inserting picture manually then you can see that actually you are not adding image in cell but you are placing an image on the cell.

    and after that you resize the cell so it looks like the cell is containing the image. but in real its not.

    so please tell us your approach to placing an image in cell.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 6, 2016 8:53 AM
    Moderator