Import Excel with Images row and column wise using Aspose.Cells library in ASP.Net

Last Reply one month ago By dharmendr

Posted one month ago

How to Read (import) Image column wise from excel using c#

GetPicture(Worksheet.Pictures, i). it’s taking all the images at one time for this reason i could not able to figure out which images comes from which column.

    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 = 1;
        int firstColumn = 1;
        int lastRow = 4;
        int lastColumn = 5;
        int imageColumn = 4;
        int imageColumn2 = 5;
        //Exporting the contents to DataTable
        DataTable dataTable = worksheet.Cells.ExportDataTable(firstRow, firstColumn, lastRow, lastColumn, true);
        Byte[] bytes = null;
        foreach (DataRow dr in dataTable.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 sql = "insert into import (employee_id ,employee_name,employee_pic, Addressproff_pic) VALUES (@employee_id,@employee_name,r@employee_pic,@Addressproff_pic)";
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = dr["employee_id"].ToString();
            cmd.Parameters.Add("@Customer", SqlDbType.VarChar, 50).Value = dr["employee_name"].ToString();
            cmd.Parameters.Add("@Logo", SqlDbType.Image).Value = bytes;
            cmd.Parameters.Add("@addresproofid", SqlDbType.Image).Value = bytes;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();            
        }
    }

 

Posted one month ago

Hi afrozeameera,

Check this example. Now please take its reference and correct your code.

Here i am reading the images from Excel and displaying in the GridView. You need to write code to save the record with images in database.

HTML

<asp:Button Text="Import" OnClick="Button2_Click" runat="server" />
<hr />
<asp:GridView runat="server" ID="gvDetails" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="Id" HeaderText="Id" />
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:TemplateField HeaderText="Picture">
            <ItemTemplate>
                <img src='<%#Eval("Picture") %>' />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Flag">
            <ItemTemplate>
                <img src='<%#Eval("Flag") %>' />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

Namespaces

C#

using System.Data;
using System.IO;
using Aspose.Cells;
using Aspose.Cells.Drawing;
using Aspose.Cells.Rendering;

VB.Net

Imports System.Data
Imports System.IO
Imports Aspose.Cells
Imports Aspose.Cells.Drawing
Imports Aspose.Cells.Rendering

Code

C#

protected void Button2_Click(object sender, EventArgs e)
{
    string filename = Server.MapPath("~/Folder/Country.xlsx");
    Workbook workbook = new Workbook(filename);
    Worksheet worksheet = workbook.Worksheets[0];
    //Exporting the contents to DataTable.
    DataTable dt = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.Rows.Count, worksheet.Cells.Columns.Count + 1, true);

    int noImageColumn = 2;
    int picCount = worksheet.Pictures.Count / noImageColumn;
    int i = 0;
    foreach (DataRow dr in dt.Rows)
    {
        Picture pic = worksheet.Pictures[i];
        dr[2] = GetBase64StringFromPicture(pic);
        pic = worksheet.Pictures[i + worksheet.Cells.Rows.Count - 1];
        dr[3] = GetBase64StringFromPicture(pic);
        i++;
    }

    gvDetails.DataSource = dt;
    gvDetails.DataBind();
}

private string GetBase64StringFromPicture(Picture pic)
{
    byte[] bytes = null;
    using (MemoryStream ms = new MemoryStream())
    {
        pic.ToImage(ms, new ImageOrPrintOptions());
        ms.Position = 0;
        bytes = ms.ToArray();
    }

    return "data:image/jpg;base64," + Convert.ToBase64String(bytes, 0, bytes.Length);
}

VB.Net

Protected Sub Button2_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim filename As String = Server.MapPath("~/Folder/Country.xlsx")
    Dim workbook As Workbook = New Workbook(filename)
    Dim worksheet As Worksheet = workbook.Worksheets(0)
    Dim dt As DataTable = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.Rows.Count, worksheet.Cells.Columns.Count + 1, True)
    Dim noImageColumn As Integer = 2
    Dim picCount As Integer = worksheet.Pictures.Count / noImageColumn
    Dim i As Integer = 0
    For Each dr As DataRow In dt.Rows
        Dim pic As Picture = worksheet.Pictures(i)
        dr(2) = GetBase64StringFromPicture(pic)
        pic = worksheet.Pictures(i + worksheet.Cells.Rows.Count - 1)
        dr(3) = GetBase64StringFromPicture(pic)
        i += 1
    Next
    gvDetails.DataSource = dt
    gvDetails.DataBind()
End Sub

Private Function GetBase64StringFromPicture(ByVal pic As Picture) As String
    Dim bytes As Byte() = Nothing
    Using ms As MemoryStream = New MemoryStream()
        pic.ToImage(ms, New ImageOrPrintOptions())
        ms.Position = 0
        bytes = ms.ToArray()
    End Using

    Return "data:image/jpg;base64," & Convert.ToBase64String(bytes, 0, bytes.Length)
End Function

Screenshots

Excel file

Images with GridView