Sunday, 23 October 2011

Exporting the data to Excel sheet with image embedded using Excel Application object



I had blogged some post previously for exporting the data to Excel sheet from ASP.NET page. Even though the basic concept is same, each example can be useful for specific situation.

This post concentrates on another aspect of exporting the data to excel with image where the image stored in database or path of the file. In this example, the image will be loaded and exported to the excel file. So, the excel file can be transferred to any place and not required web server (internet) connection to show the image - the image will show without any issue.

Note :
  1. I am using Northwind database in this example for implementation. So please make sure to have Northwind database and update the configuration in the Web.Config.
  2. This code uses ApplicationClass class in Microsoft.Office.Interop.Excel namespace. So to implement this code, we must add reference to Microsoft.Office.Interop.Excel assembly in the project.
  3. As all the image files are exported to excel, the file size of the excel file will be big.

Preparing the Database:
The Products table in Northwind database has list of Product details. I am planning to use the same table to show list of products with the image in the grid and to export to excel. But the Products table does not contain column for storing the Image. So, I am altering the table to add a new column ProductImage for storing image.

Below is the script for adding ProductImage column in Products table.
alter table Products add ProductImage Image
To update the Image in the table, I added a separate page (UpdateImage.aspx in the source code) in the project which will accept an image and update in the table.The page contains a textbox (ID : txtProductID) and a FileUpload (ID : fuImage) control for getting the ProductID and the image of the product. There is a button control on the page (ID : btnSave) for saving the selected image for the product id entered in the text box.
protected void btnSave_Click(object sender, EventArgs e)
{
    if ((fuImage.PostedFile.FileName.Trim().Length > 0) &&
        (fuImage.PostedFile != null))
    {
        byte[] image = new byte[fuImage.PostedFile.ContentLength];

        fuImage.PostedFile.InputStream.Read(image, 0, (int)fuImage.PostedFile.ContentLength);


        SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString);

        SqlCommand command = new SqlCommand();
        command.CommandText = "Update Products set ProductImage = @Image where ProductID = '" + txtProductID.Text + "'";

        command.CommandType = CommandType.Text;
        command.Connection = connection;

        PrepareSQLParameter(command, "@Image", SqlDbType.Image, image.Length, image);

        connection.Open();

        int result = command.ExecuteNonQuery();
        connection.Close();

        txtProductID.Text = "";
    }
}
private SqlParameter PrepareSQLParameter(SqlCommand command, string parameterName, SqlDbType parameterType, int parameterLength, object parameterValue)
{
    SqlParameter parameter = new SqlParameter(parameterName, parameterType, parameterLength);
    parameter.Value = parameterValue;

    command.Parameters.Add(parameter);
    return parameter;
}
By running this code, I have updated some image for each product. So the table contains image for some products. Below is the query output of the Products table -

Preparing the ASP.NET page:
Now we are ready to show the image in the GridView on the page. Below script and code used for displaying list of products with image in the ASP.NET page. (The code present in Default.aspx in source code)
<asp:Button ID="btnExport" runat="server" Text="Export" onclick="btnExport_Click" />

<asp:GridView ID="grdViewProducts" runat="server" 
    AutoGenerateColumns="False" GridLines="None"
    AllowPaging="True" PageSize="8"
    DataKeyNames="ProductID" Width="100%" CellPadding="4" ForeColor="#333333">
    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
    
    <Columns>
        <asp:BoundField DataField="ProductID" HeaderText="Product ID" />
        <asp:BoundField DataField="ProductName" HeaderText="Product" />
        <asp:BoundField DataField="CompanyName" HeaderText="Supplier" />
        <asp:BoundField DataField="CategoryName" HeaderText="Category" />
        <asp:BoundField DataField="QuantityPerUnit" HeaderText="Quantity Per Unit"/>
        <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" />
        <asp:TemplateField HeaderText="Photo">
            <ItemStyle Width="10%" HorizontalAlign="Center" />
            <ItemTemplate>
                <img id="imgPhoto" src='<%# "GetImageHandler.ashx?ProductID=" + Eval("ProductID") %>'
                    alt="<%# Eval("ProductName") %>" width="125px" height="125px" 
                    title="<%# Eval("ProductName") %>"/>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
    
    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Right" />
    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <EditRowStyle BackColor="#999999" />
    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
The Code behind
protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
        BindGrid();
}
private void BindGrid()
{
    grdViewProducts.DataSource = ProductList();
    grdViewProducts.DataBind();
}
public IList<ProductView> ProductList()
{
    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
    {
        SqlCommand command = new SqlCommand(
           "SELECT Top 20 ProductID, ProductName, CompanyName, CategoryName, ProductImage, " +
           //"QuantityPerUnit, UnitPrice, 'GetImageHandler.ashx?ProductID=' + CAST(ProductID AS VARCHAR) ProductImagePath " +
           "QuantityPerUnit, UnitPrice, ProductImagePath " +
           "FROM Products JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID " +
           "JOIN Categories ON Products.CategoryID = Categories.CategoryID " +
           "Order by ProductID", connection);

        connection.Open();
        SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);

        IList<ProductView> productViewList = new List<ProductView>();
        while (dr.Read())
        {
            ProductView productView = new ProductView();
            productView.ProductID = dr["ProductID"].ToString();
            productView.ProductName = dr["ProductName"].ToString();
            productView.CompanyName = dr["CompanyName"].ToString();
            productView.CategoryName = dr["CategoryName"].ToString();
            productView.QuantityPerUnit = dr["QuantityPerUnit"].ToString();
            productView.UnitPrice = Convert.ToDouble(dr["UnitPrice"].ToString());
            productView.ProductImagePath = dr["ProductImagePath"].ToString();
            //productView.ProductImagePath = HttpContext.Current.Request.Url.AbsoluteUri.Substring(0, HttpContext.Current.Request.Url.AbsoluteUri.LastIndexOf("/")) + "/" + dr["ProductImagePath"].ToString();
            productView.IsImageAvailable = ((dr["ProductImage"].ToString().Length > 0) ? true : false);
            productViewList.Add(productView);
        }
        return productViewList;
    }
}
I am using a GenericHandler (GetImageHandler.ashx) for getting the image content to assign in the image control.
public class GetImageHandler : IHttpHandler
{
    public void ProcessRequest(HttpContext context)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
        {
            SqlCommand command = new SqlCommand("Select ProductImage from Products where ProductID = '" + context.Request.QueryString["ProductID"].ToString() + "'", connection);

            connection.Open();
            SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
            while (dr.Read())
            {
                if (dr["ProductImage"].ToString().Length > 0)
                {
                    context.Response.BinaryWrite((byte[])dr["ProductImage"]);
                }
            }
        }
    }
    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}
As shown in the code, I used an Entity class ProductView for storing Product Information. The code for ProductView would be:
public class ProductView
{
    public string ProductID { get; set; }
    public string ProductName { get; set; }
    public string CompanyName { get; set; }
    public string CategoryName { get; set; }
    public string QuantityPerUnit { get; set; }
    public double UnitPrice { get; set; }
    public string ProductImagePath { get; set; }
    public bool IsImageAvailable { get; set; }
}
Below figure shows the output of the code execution -

Exporting to Excel sheet with image:
I am using a reusable class which can be used for exporting the data with image to an excel sheet. But only requirement is to provide the input as IList collection for defining the data with image. The c# code for the class will be:
/// <summary>
/// Class to Export the Data to Excel sheet. Input must be a IList colection
/// </summary>
/// <typeparam name="T">Entity</typeparam>
public class ExcelAppExportor<T>
{
    /// <summary>
    /// Constructor
    /// </summary>
    public ExcelAppExportor()
    {
        TemplateFileName = string.Empty;
        IsExportIncludesHeader = true;
        ExportSheetName = "Export";
    }

    /// <summary>
    /// Holds the Data
    /// </summary>
    private IList<T> ListData;

    /// <summary>
    /// Holds the Export Columns
    /// </summary>
    private IList<ExportColumn> ExportColumns;

    /// <summary>
    /// Add the export column
    /// </summary>
    /// <param name="ExportColumn">ExportColumn</param>
    public void AddExportColumn(ExportColumn ExportColumn)
    {
        if (ExportColumns == null) ExportColumns = new List<ExportColumn>();
        ExportColumns.Add(ExportColumn);
    }

    /// <summary>
    /// List contains the list of entity object which are export to excel
    /// </summary>
    public IList<T> BindDataList
    {
        get { return ListData; }
        set { ListData = value; }
    }

    /// <summary>
    /// File Name of the Export output file
    /// </summary>
    public string ExportFileName { get; set; }

    /// <summary>
    /// Template File Name - Using Template file to Export
    /// </summary>
    public string TemplateFileName { get; set; }

    /// <summary>
    /// Sheet name to Export the data
    /// </summary>
    public string ExportSheetName { get; set; }

    /// <summary>
    /// Is the header data needs to be exported
    /// </summary>
    public bool IsExportIncludesHeader { get; set; }

    public void Export()
    {
        try
        {
            #region Filling Export Columns
            // Check the columns to export is mentioned, if not
            if (ExportColumns == null)
            {
                // Create an entity object. If the list count == 0 ??? - needs to be handled from client
                T tEntity = ListData[0];

                // Export the columns to export from the property name
                ExportColumns = new List<ExportColumn>();
                foreach (System.Reflection.PropertyInfo propertyInfo in tEntity.GetType().GetProperties())
                    ExportColumns.Add(new ExportColumn(propertyInfo.Name, propertyInfo.Name));
            }
            #endregion

            // Create excel application
            Application ExcelApp = new ApplicationClass();
            Workbook workbook;
            Sheets sheets;
            Worksheet worksheet;

            // Is Export needs to be exported to a Template file
            if (TemplateFileName != string.Empty)
            {
                #region Load the Template file
                // Load the work book
                workbook = ExcelApp.Workbooks.Open(TemplateFileName, 0, false, 5, "", "", false,
                    XlPlatform.xlWindows, "", true, false, 0, true, false, false);

                sheets = workbook.Sheets;
                worksheet = (Worksheet)sheets.get_Item(1); // To avoid unassigned variable error

                bool IsWorkSheetFound = false;

                //Check is there any worksheet with the name provided. If yes, clear all data inside to fill new data
                for (int intSheetIndex = 1; intSheetIndex <= sheets.Count; intSheetIndex++)
                {
                    worksheet = (Worksheet)sheets.get_Item(intSheetIndex);
                    if (worksheet.Name.ToString().Equals(ExportSheetName))
                    {
                        IsWorkSheetFound = true;
                        break;
                    }
                }

                // If No work sheet found, add it at the last
                if (!IsWorkSheetFound)
                {
                    worksheet = (Worksheet)workbook.Sheets.Add(
                        Type.Missing, (Worksheet)sheets.get_Item(sheets.Count),
                        Type.Missing, Type.Missing);
                    worksheet.Name = ExportSheetName;
                }
                #endregion
            }
            else
            {
                #region Crate the Template File
                // Adding new work book
                workbook = ExcelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);

                sheets = workbook.Sheets;

                worksheet = (Worksheet)sheets.get_Item(1);

                worksheet.Name = ExportSheetName;
                #endregion
            }
            int intCol = 0;

            #region Populating the Header
            bool IsImageColumnPresent = false;
            // If the header needs to exported. In templated files, normally we will have the headings
            if (IsExportIncludesHeader == true)
            {
                // Exporting Header
                foreach (ExportColumn exportColumn in ExportColumns)
                {
                    Range range = (Range)worksheet.Cells[exportColumn.StartRowIndex++, ((exportColumn.ExcelColumnIndex == 0) ? ++intCol : exportColumn.ExcelColumnIndex)];
                    range.Select();
                    range.Value2 = exportColumn.HeaderText.ToString();
                    if (exportColumn.ValueFormat == "Image")
                    {
                        IsImageColumnPresent = true;
                        range.ColumnWidth = exportColumn.ImageColumnWidth;
                    }
                    else
                        range.Columns.EntireColumn.AutoFit();

                    range.Font.Bold = true;
                }
            }
            #endregion
            string strTempImagePath = HttpContext.Current.Request.PhysicalApplicationPath + Guid.NewGuid() + @"\";

            if (IsImageColumnPresent == true)
                System.IO.Directory.CreateDirectory(strTempImagePath);

            // Exporting Data
            foreach (T tEntity in BindDataList)
            {
                intCol = 0;
                foreach (ExportColumn exportColumn in ExportColumns)
                {
                    Range range = (Range)worksheet.Cells[exportColumn.StartRowIndex++, ((exportColumn.ExcelColumnIndex == 0) ? ++intCol : exportColumn.ExcelColumnIndex)];
                    range.Select();
                    if (exportColumn.ValueFormat == "Image")
                    {
                        string strImagePath = tEntity.GetType().GetProperty(exportColumn.ColumnName.ToString()).GetValue(tEntity, null).ToString();

                        Image image = null;
                        if (strImagePath.StartsWith("http"))
                            image = DownloadImage(strImagePath);
                        else
                            image = (strImagePath.Trim().Length > 0) ? (Image)Image.FromFile(strImagePath, true) : null;
                        if (image != null)
                        {
                            strImagePath = strTempImagePath + Guid.NewGuid() + @".jpg";

                            if (image != null)
                                image.Save(strImagePath);
                            range.RowHeight = exportColumn.ImageHeight;
                            range.ColumnWidth = exportColumn.ImageColumnWidth;
                            worksheet.Shapes.AddPicture(strImagePath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, ((float)Convert.ToDecimal(range.Left)) + 1, ((float)Convert.ToDecimal(range.Top)) + 1, exportColumn.ImageWidth - 2, exportColumn.ImageHeight - 2);
                        }
                    }
                    else
                    {
                        range.Value2 = tEntity.GetType().GetProperty(exportColumn.ColumnName.ToString()).GetValue(tEntity, null).ToString();
                        range.Columns.EntireColumn.AutoFit();
                    }
                }
            }
            intCol = 0;
            foreach (ExportColumn exportColumn in ExportColumns)
            {
                Range range = (Range)worksheet.Cells[exportColumn.StartRowIndex++, ((exportColumn.ExcelColumnIndex == 0) ? ++intCol : exportColumn.ExcelColumnIndex)];
                if (exportColumn.ValueFormat != string.Empty)
                    range.Columns.EntireColumn.NumberFormat = exportColumn.ValueFormat;
            }
            try
            {
                Range range = (Range)worksheet.Cells[1, 1];
                range.Select();
                
                // Save the workbook with saving option
                workbook.Close(true, ExportFileName, Type.Missing);
                ExcelApp.UserControl = false;
                ExcelApp.Quit();
                ExcelApp = null;

                System.IO.Directory.Delete(strTempImagePath, true);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            Process[] ps = Process.GetProcesses();
            foreach (Process p in ps)
            {
                if (p.ProcessName.ToLower().Equals("excel"))
                    p.Kill();
            }
        }
    }
    /// <summary>
    /// Function to download Image from website
    /// Method from http://www.digitalcoding.com/Code-Snippets/C-Sharp/C-Code-Snippet-Download-Image-from-URL.html
    /// Thanks to digitialcoding.com
    /// </summary>
    /// <param name="_URL">URL address to download image</param>
    /// <returns>Image</returns>
    public Image DownloadImage(string _URL)
    {
        Image _tmpImage = null;

        try
        {
            // Open a connection
            System.Net.HttpWebRequest _HttpWebRequest = (System.Net.HttpWebRequest)System.Net.HttpWebRequest.Create(_URL);

            _HttpWebRequest.AllowWriteStreamBuffering = true;

            // You can also specify additional header values like the user agent or the referer: (Optional)
            _HttpWebRequest.UserAgent = "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1)";
            _HttpWebRequest.Referer = "http://www.google.com/";

            // set timeout for 20 seconds (Optional)
            _HttpWebRequest.Timeout = 20000;

            // Request response:
            System.Net.WebResponse _WebResponse = _HttpWebRequest.GetResponse();

            // Open data stream:
            System.IO.Stream _WebStream = _WebResponse.GetResponseStream();

            // convert webstream to image
            _tmpImage = Image.FromStream(_WebStream);

            // Cleanup
            _WebResponse.Close();
            _WebResponse.Close();
        }
        catch (Exception _Exception)
        {
            // Error
            Console.WriteLine("Exception caught in process: {0}", _Exception.ToString());
            return null;
        }

        return _tmpImage;
    }
}
I have a button in the page which will trigger the exporting functionality. In this event, I have code for getting the data as IList and calling the Export class object by passing the IList collection as input.
protected void btnExport_Click(object sender, EventArgs e)
{
    try
    {
        ExcelAppExportor<ProductView> exportor = new ExcelAppExportor<ProductView>();
        exportor.BindDataList = ProductList();
        exportor.AddExportColumn(new ExportColumn("ProductID", "Product ID", 1, 1));
        exportor.AddExportColumn(new ExportColumn("ProductName", "Product Name", 1, 2));
        exportor.AddExportColumn(new ExportColumn("CompanyName", "Company Name", 1, 3));
        exportor.AddExportColumn(new ExportColumn("CategoryName", "Category Name", 1, 4));
        exportor.AddExportColumn(new ExportColumn("QuantityPerUnit", "Quantity Per Unit", 1, 5));
        exportor.AddExportColumn(new ExportColumn("UnitPrice", "Unit Price", 1, 6));
        exportor.AddExportColumn(new ExportColumn("IsImageAvailable", "Is Image Available", 1, 7));
        exportor.AddExportColumn(new ExportColumn("ProductImagePath", "Image", "Image", 1, 8, 100, 80, 20));

        string strExportFileName = Path.Combine(@"C:\Temp", Guid.NewGuid().ToString("N")) + ".xls";

        exportor.ExportFileName = strExportFileName;
        exportor.IsExportIncludesHeader = true;
        exportor.ExportSheetName = "Orders";
        exportor.Export();

        byte[] ExcelStream = File.ReadAllBytes(strExportFileName);

        Context.Response.ClearContent();
        Context.Response.ContentType = "application/ms-excel";
        Context.Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xls", "Product List"));
        Context.Response.Charset = "";
        Context.Response.BinaryWrite(ExcelStream);
        Context.Response.End();
    }
    catch (Exception ex)
    {
    }
}
The code for adding the export column -
/// <summary>
/// Holds the details of te columns to be exported to Excel from IList
/// </summary>
public class ExportColumn
{
    /// <summary>
    /// Entity object(class) property name, mandatory
    /// </summary>
    public string ColumnName { get; set; }

    /// <summary>
    /// What needs to be shows in the Excel sheet for that col, mandatory
    /// </summary>
    public string HeaderText { get; set; }

    /// <summary>
    /// The format of the value. To get the format string, from the excel sheet -> Column -> Format Cells -> Number -> Custom -> Take the Type
    /// </summary>
    public string ValueFormat { get; set; }

    /// <summary>
    /// From which row the data needs to be exported, for template file it will be useful
    /// </summary>
    public int StartRowIndex { get; set; }

    /// <summary>
    /// On which column the data needs to be exports, So it can be any column. Useful for Templated file
    /// Needs to assign the Index of the column starting by 1
    /// A - 1, B - 2, C - 3 etc., Counting will include the hidden column
    /// </summary>
    public int ExcelColumnIndex { get; set; }

    /// <summary>
    /// Height of the Image
    /// </summary>
    public int ImageHeight { get; set; }

    /// <summary>
    /// Width of the Image
    /// </summary>
    public int ImageWidth { get; set; }

    /// <summary>
    /// Width of the image column
    /// </summary>
    public int ImageColumnWidth { get; set; }

    public ExportColumn()
    {
        StartRowIndex = 1;
        this.ValueFormat = string.Empty;
        this.StartRowIndex = 1;
        this.ExcelColumnIndex = 0;
    }
    public ExportColumn(string ColumnName, string HeaderText)
    {
        this.ColumnName = ColumnName;
        this.HeaderText = HeaderText;
        this.ValueFormat = string.Empty;
        this.StartRowIndex = 1;
        this.ExcelColumnIndex = 0;
    }
    public ExportColumn(string ColumnName, string HeaderText, int StartRowIndex, int ExcelColumnIndex)
    {
        this.ColumnName = ColumnName;
        this.HeaderText = HeaderText;
        this.ValueFormat = string.Empty;
        this.StartRowIndex = StartRowIndex;
        this.ExcelColumnIndex = ExcelColumnIndex;
    }
    public ExportColumn(string ColumnName, string HeaderText, string ValueFormat)
    {
        this.ColumnName = ColumnName;
        this.HeaderText = HeaderText;
        this.ValueFormat = ValueFormat;
        this.StartRowIndex = 1;
        this.ExcelColumnIndex = 0;
    }
    public ExportColumn(string ColumnName, string HeaderText, string ValueFormat, int StartRowIndex, int ExcelColumnIndex)
    {
        this.ColumnName = ColumnName;
        this.HeaderText = HeaderText;
        this.ValueFormat = ValueFormat;
        this.StartRowIndex = StartRowIndex;
        this.ExcelColumnIndex = ExcelColumnIndex;
    }
    public ExportColumn(string ColumnName, string HeaderText, string ValueFormat, int StartRowIndex, int ExcelColumnIndex, int ImageWidth, int ImageHeight, int ImageColumnWidth)
    {
        this.ColumnName = ColumnName;
        this.HeaderText = HeaderText;
        this.ValueFormat = ValueFormat;
        this.StartRowIndex = StartRowIndex;
        this.ExcelColumnIndex = ExcelColumnIndex;
        this.ImageHeight = ImageHeight;
        this.ImageWidth = ImageWidth;
        this.ImageColumnWidth = ImageColumnWidth;
    }
}
Adding the ExportColumn to ExcelAppExportor object
The ExportColumn class is used for adding the exporting column name, header text and format of the data. The format of the data should as per the format defined in excel sheet (Select the column in Excel Sheet -> right click the column -> select Format Cells... -> Define any format -> Select the Custom -> tab -> Copy the Type value).

  1. ExportColumn(string ColumnName, string HeaderText) - Defining column name of the database table/view/query and the column header text on the Excel Sheet.
  2. ExportColumn(string ColumnName, string HeaderText, int StartRowIndex, int ExcelColumnIndex) - Defining column name, column header text, row index where the data should start to export, column index where the data should start to export.
  3. ExportColumn(string ColumnName, string HeaderText, string ValueFormat) - Defining column name, column header text, value format of the data.
  4. ExportColumn(string ColumnName, string HeaderText, string ValueFormat, int StartRowIndex, int ExcelColumnIndex) - Defining column name, column header text, value format of the data, row index where the data should start to export, column index where the data should start to export.
  5. ExportColumn(string ColumnName, string HeaderText, string ValueFormat, int StartRowIndex, int ExcelColumnIndex, int ImageWidth, int ImageHeight, int ImageColumnWidth) - Defining column name, column header text, value format of the data, row index where the data should start to export, column index where the data should start to export, image width, image height, image column width.

Here, the last ExportColumn constructor is used for adding the information about the image column.The image url path can be defined in two ways:
  1. The physical path of the file. - For Ex: D:\ImageExportTest\WebApp\Images\Image01.jpg. So the code assign the physical path as defined below
    productView.ProductImagePath = @"D:\ImageExportTest\WebApp\Images\Image01.jpg"
    Note: The image files must be readable from the application.
  2. The http/https url of the path of the file - For Ex: http://localhost/images/world.jpg. So the code assign the value as defined below
    productView.ProductImagePath = @"http://localhost/images/world.jpg"

Download the working example in C# here and in VB here.

The screen shot of the exported excel sheet for the example defined above:


10 Responses to “Exporting the data to Excel sheet with image embedded using Excel Application object”

  • Anonymous says:
    8 June 2012 at 07:00

    is it possible to importing excel with image

  • Thiru says:
    8 June 2012 at 10:36

    Hi Anonymous,
    This example shows importing to excel with image only. I feel you are meaning something different requirement. If yes, pls let me know I can help.
    You can send mail to me to pm.thirumalai@gmail.com

  • swati says:
    13 June 2013 at 03:24

    i tried out this code for C# but it is not working .Mainly i'm getting an exception ....

    system.runtime.interopservices.com exception Microsoft Office Excel cannot access the file.

    I have no idea what is the problem and why the excel sheet is not created.

    Please help me !!

  • Ullas Krishnan says:
    26 July 2013 at 17:11

    sir
    I am using excel sheet as data base with images. I need to show the excel sheet with imgages in gridview in my window application using asp.net.
    so let me know what to do for this thank you

  • Thirumalai M says:
    1 August 2013 at 00:26

    Excel sheet as database, is different concept together. This example won't work in this requirement. You could find some examples in net.

  • Saranya says:
    28 November 2013 at 17:15

    Wow..its working great, Thanks

  • Mahmoud Al refaei says:
    4 May 2014 at 18:23

    can i write binary image from stream buffer to word or excel direct without save it physically

  • Jill says:
    11 June 2014 at 18:35

    Can this be done with Classic ASP?

  • Pallavi Srinath says:
    13 August 2015 at 15:01

    Thanks for this solution. It was a great help. Thanks.

  • Unknown says:
    29 June 2016 at 09:35

    Is possible to include an image and a text in the same cell?

    Thanks


Post a Comment