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

Last Reply 2 months ago By democloud

Posted 2 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

You are viewing reply posted by: democloud 2 months ago.
Posted 2 months ago Modified on 2 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