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

Last Reply 3 months ago By democloud

Posted 3 months 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 3 months ago Modified on 3 months 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