Set Background image to Cells in Excel

Question

1.00/5 (1 vote)

See more:

Hi Developer,

I want to export data from Asp.net datagrid to excel.
the grid cell contains images(png image),
i cannot set the image in excel cell.
plz anyone help to me...
Thanks

Posted 25-Sep-13 22:12pm

sankmahesh

Add a Solution

Comments

Maciej Los 26-Sep-13 5:04am

What have you done tille now? Where are you stuck? What kind of method do you use to export data into Excel?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sergey Vaselenko · Answer 1 · 2013-09-26T01:23:00

Solution 1

The Shapes.AddPicture[^] method is used to insert a picture.

The Range[^] Top and Left properties are used to define the top left corner.

Width and Height should be in points instead of pixels. Use the 3/4 multiplier.

You may scale your pictures also.

Here is a short example in VBA:

VB

Dim ws As Worksheet
Dim r As Range
Dim fileName As String
Dim pictureWidth As Integer
Dim pictureHeight As Integer
Dim shape As shape

Set ws = Worksheets(1)

Set r = ws.Cells(5, 2)

fileName = "d:\picture.png"

pictureWidth = 195
pictureHeight = 102

Set shape = ws.Shapes.AddPicture(fileName, msoTrue, msoTrue, r.Left, r.Top, pictureWidth * 3 / 4, pictureHeight * 3 / 4)

shape.LockAspectRatio = msoTrue
shape.ScaleHeight 2, msoTrue, msoScaleFromTopLeft

Posted 26-Sep-13 1:23am

Sergey Vaselenko

Comments

sankmahesh 26-Sep-13 8:28am

plz give me the C# code format..

sankmahesh 26-Sep-13 8:30am

msoTrue , msoScaleFromTopLeft are returning error. so please give the above code in C# format

Sergey Vaselenko 26-Sep-13 9:24am

msoTrue = -1
msoScaleFromTopLeft = 0
See Shape members at http://msdn.microsoft.com/en-us/library/ff193908(v=office.14).aspx.

sankmahesh 26-Sep-13 10:02am

msotristate savewithDocument value = ?

Sergey Vaselenko 26-Sep-13 11:29am

See MsoTriState Enumeration at http://msdn.microsoft.com/en-us/library/ff860737(v=office.14).aspx.
msoTrue = -1
msoFalse = 0
I don't found the corresponding VSTO enumeration.

sankmahesh 26-Sep-13 12:46pm

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
OleDbConnection con = new OleDbConnection ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:/Guna Sankari Documents/WebSite2/db1.accdb") ;
string Query2 = "select * from tblfiles" ;
OleDbDataAdapter da;
DataSet ds=new DataSet();
DataTable dtb;
con.Open() ;
da = new OleDbDataAdapter(Query2, con) ;
da.Fill(ds);
con.Close() ;
dtb = ds.Tables[0] ;
GridView1.DataSource = dtb ;
GridView1.DataBind();
}
}

protected string GetUrl(string imagepath)
{
string[] splits = Request.Url.AbsoluteUri.Split('/');
if (splits.Length >= 2)
{
string url = splits[0] + "//";

for (int i = 2; i < splits.Length - 1; i++)
{

removed += splits[i];

removed += "/";

}

return removed + imagepath;

}

return imagepath;

}

private void Excel_Export()
{

Response.Clear();

Response.Buffer = true;

Response.AddHeader("removed-disremoved", "attachment;filename=GridViewExport.xls");

Response.Charset = "";

Response.ContentType = "application/vnd.ms-excel";

StringWriter sw = new StringWriter();

HtmlTextWriter hw = new HtmlTextWriter(sw);

GridView1.AllowPaging = false;

GridView1.DataBind();

for (int i = 0; i < GridView1.Rows.Count; i++)
{

GridViewRow row = GridView1.Rows[i];
row.Attributes.Add("class", "textmode");

}

GridView1.RenderControl(hw);
string style = @"<style> .textmode { mso-number-format:\@; } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();

}

static DataTable GetData()
{
DataTable table = new DataTable();
table.Columns.Add("ID", typeof(string));
table.Columns.Add("FileName", typeof(string));
//table.Columns.Add("Location", typeof(string));
table.Columns.Add("Image");

table.Rows.Add("JP", "XXX", "http://localhost/images/fir.png");
table.Rows.Add("HP", "TTT", "http://localhost/images/sec.png");
table.Rows.Add("SQ", "YYY", "http://localhost/images/thr.png");
table.Rows.Add("XS", "EEE", "http://localhost/images/fur.png");
return table;
}

protected void Button1_Click(object sender, EventArgs e)
{
Excel_Export();

}
}
<pre lang="xml"><asp:GridView ID="GridView1" runat="server" AutoGenerateColumns = "False"
Font-Names = "Arial" >

<Columns>

<asp:BoundField DataField = "ID" HeaderText = "ID"

ItemStyle-Height = "150" />

<asp:BoundField DataField = "FileName" HeaderText = "Image Name"

ItemStyle-Height = "150" />

<asp:TemplateField ItemStyle-Height = "150" ItemStyle-Width = "170">

<ItemTemplate>

<asp:Image ID="Image1" runat="server"

ImageUrl = '<%# Eval("FilePath", GetUrl("{0}")) %>' />

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView></pre>

ID1 ID FileName FilePath
1 1 Image images/fir.png
2 2 Img2 images/sec.png

sankmahesh · Answer 2 · 2013-09-26T21:18:00

Solution 3

i added just the below code,

public override void VerifyRenderingInServerForm(Control control)
{
/* Confirms that an HtmlForm control is rendered for the specified ASP.NET
server control at run time. */
}

result came...

Posted 26-Sep-13 21:18pm

sankmahesh

sankmahesh · Answer 3 · 2013-09-26T06:45:00

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
OleDbConnection con = new OleDbConnection ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:/Guna Sankari Documents/WebSite2/db1.accdb") ;
string Query2 = "select * from tblfiles" ;
OleDbDataAdapter da;
DataSet ds=new DataSet();
DataTable dtb;
con.Open() ;
da = new OleDbDataAdapter(Query2, con) ;
da.Fill(ds);
con.Close() ;
dtb = ds.Tables[0] ;
GridView1.DataSource = dtb ;
GridView1.DataBind();
}
}

protected string GetUrl(string imagepath)
{

string[] splits = Request.Url.AbsoluteUri.Split('/');

if (splits.Length >= 2)
{

string url = splits[0] + "//";

for (int i = 2; i < splits.Length - 1; i++)
{

url += splits[i];

url += "/";

}

return url + imagepath;

}

return imagepath;

}

private void Excel_Export()
{

Response.Clear();

Response.Buffer = true;

Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");

Response.Charset = "";

Response.ContentType = "application/vnd.ms-excel";

StringWriter sw = new StringWriter();

HtmlTextWriter hw = new HtmlTextWriter(sw);

GridView1.AllowPaging = false;

GridView1.DataBind();

for (int i = 0; i < GridView1.Rows.Count; i++)
{

GridViewRow row = GridView1.Rows[i];
row.Attributes.Add("class", "textmode");

}

GridView1.RenderControl(hw);
string style = @" .textmode { mso-number-format:\@; } ";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();

}

static DataTable GetData()
{
DataTable table = new DataTable();
table.Columns.Add("ID", typeof(string));
table.Columns.Add("FileName", typeof(string));
//table.Columns.Add("Location", typeof(string));
table.Columns.Add("Image");

table.Rows.Add("JP", "XXX", "http://localhost/images/fir.png");
table.Rows.Add("HP", "TTT", "http://localhost/images/sec.png");
table.Rows.Add("SQ", "YYY", "http://localhost/images/thr.png");
table.Rows.Add("XS", "EEE", "http://localhost/images/fur.png");
return table;
}

protected void Button1_Click(object sender, EventArgs e)
{
Excel_Export();

}
}

XML

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns = "False"
Font-Names = "Arial" >

<Columns>

    <asp:BoundField DataField = "ID" HeaderText = "ID"

     ItemStyle-Height = "150" />

    <asp:BoundField DataField = "FileName" HeaderText = "Image Name"

     ItemStyle-Height = "150" />

  <asp:TemplateField  ItemStyle-Height = "150" ItemStyle-Width = "170">

        <ItemTemplate>

            <asp:Image ID="Image1" runat="server"

             ImageUrl = '<%# Eval("FilePath", GetUrl("{0}")) %>' />

        </ItemTemplate>

    </asp:TemplateField>

</Columns>

</asp:GridView>

ID1 ID FileName FilePath
1 1 Image images/fir.png
2 2 Img2 images/sec.png

Set Background image to Cells in Excel

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us