Import Excel sheet with Images and save in database using Aspose.Cells library in ASP.Net

Last Reply one month ago By democloud

Posted one month ago

Hello Sir,

How to import the excel sheet with images and save the data in database using asp .net?

Please help me

Thanks


Posted one month ago Modified on one month ago

Hello Sir,

Below is the updated code,

    protected void Button2_Click(object sender, EventArgs e)
    {
        try
        {
            int i = 0;

            string filename = Server.MapPath("~/Folder/book1.xlsx");

            //Instantiating a Workbook object and open the uploaded file
            Workbook workbook = new Workbook(filename);

            //Obtaining the reference of first worksheet by passing its sheet index
            Worksheet worksheet = workbook.Worksheets[0];
            MemoryStream ms = new MemoryStream();

            Picture pic = null;
            int firstRow = 0;
            int firstColumn = 1;
            int lastRow = 6;
            int lastColumn = 5;
            int imageColumn = 4;

            //Exporting the contents to DataTable
            DataTable dataTable = worksheet.Cells.ExportDataTable(firstRow, firstColumn, lastRow, lastColumn, true);
            Byte[] bytes = null;
            foreach (DataRow dr in dataTable.Rows)
            {
                foreach (GridViewRow g1 in GridView1.Rows)
                {
                    pic = GetPicture(worksheet.Pictures, i);
                    //Convert picture to stream
                    pic.ToImage(ms, new Aspose.Cells.Rendering.ImageOrPrintOptions());
                    ms.Position = 0;
                    bytes = ms.ToArray();
                    i++;

                    string ID = (g1.FindControl("lblId") as System.Web.UI.WebControls.Label).Text;
                    string Customer = (g1.FindControl("lblName") as System.Web.UI.WebControls.Label).Text;
                    string Country = (g1.FindControl("lblPrice") as System.Web.UI.WebControls.Label).Text;
                    string Product = (g1.FindControl("lblDescription") as System.Web.UI.WebControls.Label).Text;

                    string sql = "insert into import (ID,Customer,Country,Product,Logo) VALUES (@ID,@Customer,@Country,@Product,@Logo)";
                    SqlCommand cmd = new SqlCommand(sql, con);
                    cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID;
                    cmd.Parameters.Add("@Customer", SqlDbType.VarChar, 50).Value = Customer;
                    cmd.Parameters.Add("@Country", SqlDbType.VarChar, 50).Value = Country;
                    cmd.Parameters.Add("@Product", SqlDbType.VarChar, 50).Value = Product;
                    cmd.Parameters.Add("@Logo", SqlDbType.Image).Value = bytes;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
        }
        catch(Exception ex)
        { throw ex; }
    }

Using the above code I'm now able to successfully insert the data into database with specified images for respective rows. 

Thanks