EleaorStark
asked on
asp.net 2.0 c# exporting gridview to excel (with pictures instead of checkboxes!!!)
Hello!
I made one gridview just to export the data to Excel. It's "easier" than the first becuase I don't need all the data (columns). I can replace the checkboxes with the string but I need to display pictures for all values in table where one column is set to true. The picture is attached. Bellow is the code of the whole project...
thanks for your time and help!
I made one gridview just to export the data to Excel. It's "easier" than the first becuase I don't need all the data (columns). I can replace the checkboxes with the string but I need to display pictures for all values in table where one column is set to true. The picture is attached. Bellow is the code of the whole project...
thanks for your time and help!
<%@ Control Language="C#" AutoEventWireup="true" CodeFile="seznam_test.ascx.cs" Inherits="test_seznam_test" %>
<br /><br />
<b>Natisni seznam >>></b> <asp:ImageButton runat="server" ID="imgButtList" ImageUrl="~/ikone/icon_Excel2007.gif" OnClick="imgButtEx_Click" /> <asp:ImageButton runat="server" ID="ImageButtWord" ImageUrl="~/ikone/icon_Word2007.gif" OnClick="imgButtWo_Click" />
<br /><br /><br />
<asp:GridView ID="GridView1" runat="server" DataSourceID="sqlDataSource1" AutoGenerateColumns="false" CssClass="headertable" DataKeyNames="ID" >
<%--http://miha.ef.uni-lj.si/admin/govorilne/izpisPregleda.asp--%>
<%--Style="border-color: #cccccc; border-width:2px; border-bottom-style:solid;"--%>
<HeaderStyle CssClass="headertr"/>
<RowStyle CssClass="itemtemplate2" />
<%--<SelectedRowStyle CssClass="selectedrowcolor" />--%>
<Columns>
<%--<asp:BoundField Visible="false" SortExpression="ID" DataField="ID" />--%>
<asp:TemplateField HeaderText="Priimek in ime" SortExpression="priimek" >
<ItemTemplate>
<asp:Label runat="server" ID="lblSodelavec" Font-Size="11px" Text='<%#"<b>" + Eval("priimek").ToString() + " " + Eval("ime").ToString()+ "</b>" + " " + Eval("izobrazba").ToString() %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Prostor" SortExpression="prostor">
<ItemTemplate>
<asp:Label runat="server" ID="lblProstor" Text='<%#Eval("prostor") %>' Font-Size="11px"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Dan" SortExpression="gudan">
<ItemTemplate>
<asp:Label ID="lblDan" runat="server" Font-Size="11px" Text='<%# govorilneure.dansprememba(Eval("gudan")) %> '></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Ura" SortExpression="guura">
<ItemTemplate>
<asp:Label runat="server" ID="lblUra" Font-Size="11px" Text='<%#Eval("guura", "{0:t}")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Opomba" SortExpression="guopomba">
<ItemTemplate>
<asp:Label Font-Size="11px" runat="server" ID="lblOpomba" Text='<%#Eval("guopomba") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField ShowHeader="True" HeaderText="P" >
<ItemTemplate>
<%--<asp:ImageButton ImageAlign="Middle" ID="LinkButtonToggleDefaultChecked" SkinID="iconCheckedDisabled" runat="server" BackColor="white" CausesValidation="False"
visible='<%# (bool) Eval("guspr") %>' CommandName="ToggleDefault" ForeColor="black" Enabled="false" />
<asp:ImageButton ImageAlign="Middle" ID="LinkButtonToggleDefaultUnChecked" SkinID="iconUnCheckedDisabled" runat="server" BackColor="white" CausesValidation="False"
visible='<%# !(bool) Eval("guspr") %>' CommandName="ToggleDefault" ForeColor="black" Enabled="false" />
--%>
<asp:CheckBox runat="server" ID="chckBoxGS" Enabled="false" Checked='<%# (bool) Eval("guspr") %>' Visible='<%# (bool) Eval("guspr") %>' />
<asp:Image runat="server" ID="imgGuSpr" ImageUrl='~/ikone/je.gif' Visible='<%# (bool) Eval("guspr") %>' />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:SqlDataSource runat="server" ID="sqlDataSource1" ConnectionString="<%$ ConnectionStrings:Conn %>"
SelectCommand="ISELECT" SelectCommandType="StoredProcedure">
</asp:SqlDataSource>
<br /><br /><br />
c# code
protected void imgButtEx_Click(object sender, EventArgs e)
{
//this.GridView1.Columns[2].Visible = false;
GridView1.AllowPaging = false;
//GridView1.Columns.RemoveAt(0);
//GridView1.Columns.Add("priimek");
//GridView1.Columns.RemoveAt(5);
GridView1.DataBind();
govorilneure.PrepareControlForExport(GridView1);
govorilneure.Export2("Govorilneure.xls", GridView1);
}
protected void imgButtWo_Click(object sender, EventArgs e)
{
Control control;
//this.GridView1.Columns[2].Visible = false;
GridView1.AllowPaging = false;
//GridView1.Columns.RemoveAt(0);
//GridView1.Columns.Add("priimek");
//GridView1.Columns.RemoveAt(5);
GridView1.DataBind();
govorilneure.PrepareControlForExport(GridView1);
//for (int i = 0; i < control.Controls.Count; i++)
//{
// Control current = control.Controls[i];
// if (current is CheckBox)
// {
// control.Controls.Remove(current);
// control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "~/ikone/je.gif" : " "));
// //control.Controls.AddAt(i, new LiteralControl((current as CheckBox).ApplyStyle =
// }
// //if (current.HasControls())
// //{
// // govorilneure.PrepareControlForExport(current);
// //}
//}
govorilneure.Export4Word("Govorilneure.doc", GridView1);
}
class:
#region priprava kontrole (ex GridViewExportutil)
//public class GridViewExportUtil
//{
/// <summary>
/// Replace any of the contained controls with literals
/// </summary>
#region priprava kontrole v plain text
public static void PrepareControlForExport(Control control)
{
//as namesto is ; as vrne null, is pa ne
for (int i = 0; i < control.Controls.Count; i++)
{
Control current = control.Controls[i];
if (current is LinkButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
}
else if (current is ImageButton)
{
//je.gif
//ImageUrl="~/ikone/je.gif"
//"~/ikone/je.gif
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
}
else if (current is HyperLink)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
}
else if (current is TextBox)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as TextBox).Text));
}
else if (current is DropDownList)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
}
else if (current is HiddenField)
{
control.Controls.Remove(current);
}
else if (current is CheckBox)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "~/ikone/je.gif" : " "));
//control.Controls.AddAt(i, new LiteralControl((current as CheckBox).ApplyStyle =
//? "True" : "False";
}
if (current.HasControls())
{
govorilneure.PrepareControlForExport(current);
}
}
}
public static void Export4Word (string fileName, Control gv)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.AddHeader(
"content-disposition", string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/vnd.ms-word";
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.Write("\r\n");
HttpContext.Current.Response.Write("<html ");
HttpContext.Current.Response.Write("xmlns:o='urn:schemas-microsoft-com:office:office' ");
HttpContext.Current.Response.Write("xmlns:w='urn:schemas-microsoft-com:office:word' ");
HttpContext.Current.Response.Write("xmlns='http://www.w3.org/TR/REC-html40'>");
HttpContext.Current.Response.Write("<head>");
HttpContext.Current.Response.Write("<!--[if gte mso 9]> <xml> <w:WordDocument> <w:View>Print</w:View> <w:Zoom>100</w:Zoom> <w:DoNotOptimizeForBrowser/> </w:WordDocument> </xml> <![endif]-->");
HttpContext.Current.Response.Write("<META HTTP-EQUIV='Content-Type' CONTENT='text/html; charset=windows-1250'>");
HttpContext.Current.Response.Write("<meta name=ProgId content=Word.Document>");
HttpContext.Current.Response.Write("<meta name=Generator content='Microsoft Word 9'>");
HttpContext.Current.Response.Write("<meta name=Originator content='Microsoft Word 9'>");
//HttpContext.Current.Response.Write("<style>");
//HttpContext.Current.Response.Write("p.MsoFooter, li.MsoFooter, div.MsoFooter{margin:0in;margin-bottom:.0001pt;mso-pagination:widow-orphan;tab-stops:center 3.0in right6.0in;font-size:8.0pt;}");
//HttpContext.Current.Response.Write("p.MsoHeader, li.MsoHeader, div.MsoHeader{margin:0in;margin-bottom:.0001pt;mso-pagination:widow-orphan;tab-stops:center 3.0in right6.0in;font-size:8.0pt;}");
//HttpContext.Current.Response.Write("@page Section {margin:1.0in 1.0in 1.0in 1.0in;mso-header-margin:.5in;mso-footer-margin:.5in;mso-footer: f1;mso-header: h1;mso-paper-source:0;}");
//HttpContext.Current.Response.Write("div.Section {page:Section;}");
//HttpContext.Current.Response.Write("</style>");
HttpContext.Current.Response.Write("</head>");
HttpContext.Current.Response.Write("<body>");
//vsebina dokumenta word (izvo~ena kontrola)
HttpContext.Current.Response.Write("<div>");
//HttpContext.Current.Response.Write("<h4><span style=" + "'color:#BF1428;'><b>" + naslov + " ");
//if (vrstaaplikacije == 2)
//{
// HttpContext.Current.Response.Write(StLetoMesec.StudijskoLeto.DolociStLeto_NacPO_String());
//}
HttpContext.Current.Response.Write("</b></span></h4>");
//HttpContext.Current.Response.Write("<span style=" + "'color:red;'><i>" + opomba + "</i></span>");
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
gv.RenderControl(oHtmlTextWriter);
HttpContext.Current.Response.Write(oStringWriter.ToString());
HttpContext.Current.Response.Write("</div>");
////Header
//HttpContext.Current.Response.Write("<div style='mso-element:header;' id=h1><p class=MsoHeader style='text-align:left;'><span style='mso-tab-count:1'></span>" + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString() + "");
//HttpContext.Current.Response.Write("<span style='mso-tab-count:16'></span>Stran <span style='mso-field-code:\" PAGE \"'></span> od <span style='mso-field-code:\" NUMPAGES \"'></span></p></div>");
////Footer
//HttpContext.Current.Response.Write("<div style='mso-element:footer;' id=f1><p class=MsoFooter style='text-align:center;'>Copyright © 1997-2008 EF WebTeam. Vse pravice pridr~ane.</p></div>");
//HttpContext.Current.Response.Write("</body>");
HttpContext.Current.Response.Write("</html>");
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.End();
}
#endregion
//}
#region Export druga verzija
public static void Export(string fileName, GridView gv)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader(
"content-disposition", string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";
// Response.ContentEncoding = System.Text.Encoding.Default;
//HttpContext.Current.Response.Charset = "UTF7";
//HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// Create a table to contain the grid
Table table = new Table();
// include the gridline settings
table.GridLines = gv.GridLines;
// add the header row to the table
if (gv.HeaderRow != null)
{
govorilneure.PrepareControlForExport(gv.HeaderRow);
table.Rows.Add(gv.HeaderRow);
}
// add each of the data rows to the table
foreach (GridViewRow row in gv.Rows)
{
govorilneure.PrepareControlForExport(row);
table.Rows.Add(row);
//table.Rows.Remove[1];
}
// add the footer row to the table
if (gv.FooterRow != null)
{
govorilneure.PrepareControlForExport(gv.FooterRow);
table.Rows.Add(gv.FooterRow);
}
// render the table into the htmlwriter
table.RenderControl(htw);
// render the htmlwriter into the response
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
}
}
#endregion
#region koda za export v excel dokument verzija 2
public static void Export2(string fileName, GridView gv)
{
string studijskoleto = DateTime.Now.Year.ToString();
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader(
"content-disposition", string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.Charset = "utf-8";
HttpContext.Current.Response.Write("<meta http-equiv=Content-Type content=\"text/html; charset=windows-1250\">\n");
HttpContext.Current.Response.Write("<html xmlns:o='urn:schemas-microsoft-com:office:office'\n" +
"xmlns:x='urn:schemas-microsoft-com:office:excel'\n" +
"xmlns='http://www.w3.org/TR/REC-html40'>\n" +
"<head>\n");
HttpContext.Current.Response.Write("<style>\n");
HttpContext.Current.Response.Write(@".number {mso-number-format:0\.00;}\n");
HttpContext.Current.Response.Write("@page");
HttpContext.Current.Response.Write("{margin:1.0in 1.0in 1.0in 1.0in;\n");
HttpContext.Current.Response.Write("mso-header-margin:.5in;\n");
HttpContext.Current.Response.Write("mso-footer-margin:.5in;\n");
HttpContext.Current.Response.Write("mso-header-data:'&L &D &T&RStran &P od &N';");
//<%Response.Write(DateTime.Now.Year); %>
HttpContext.Current.Response.Write("mso-footer-data:'Copyright © 1997-" + studijskoleto + " EF WebTeam. Vse pravice pridr~ane.';");
HttpContext.Current.Response.Write("mso-page-orientation:portrait;}\n");
HttpContext.Current.Response.Write("</style>\n");
HttpContext.Current.Response.Write("<!--[if gte mso 9]><xml>\n");
HttpContext.Current.Response.Write("<x:ExcelWorkbook>\n");
HttpContext.Current.Response.Write("<x:ExcelWorksheets>\n");
HttpContext.Current.Response.Write("<x:ExcelWorksheet>\n");
HttpContext.Current.Response.Write("<x:Name>Sheet 1</x:Name>\n");
HttpContext.Current.Response.Write("<x:WorksheetOptions>\n");
//spravi vse podatke na eno stran!!!
//HttpContext.Current.Response.Write("<x:FitToPage/>");
HttpContext.Current.Response.Write("<x:Print>\n");
HttpContext.Current.Response.Write("<x:ValidPrinterInfo/>\n");
//HttpContext.Current.Response.Write("<x:FitHeight>" + maxdolzinatisk + "</x:FitHeight>");
//HttpContext.Current.Response.Write("<x:FitWidth>" + maxsirinatisk + "</x:FitWidth>");
HttpContext.Current.Response.Write("<x:PaperSizeIndex>9</x:PaperSizeIndex>\n");
HttpContext.Current.Response.Write("<x:HorizontalResolution>600</x:HorizontalResolution\n");
HttpContext.Current.Response.Write("<x:VerticalResolution>600</x:VerticalResolution\n");
HttpContext.Current.Response.Write("</x:Print>\n");
HttpContext.Current.Response.Write("<x:Selected/>\n");
HttpContext.Current.Response.Write("<x:DoNotDisplayGridlines/>\n");
HttpContext.Current.Response.Write("<x:ProtectContents>False</x:ProtectContents>\n");
HttpContext.Current.Response.Write("<x:ProtectObjects>False</x:ProtectObjects>\n");
HttpContext.Current.Response.Write("<x:ProtectScenarios>False</x:ProtectScenarios>\n");
HttpContext.Current.Response.Write("</x:WorksheetOptions>\n");
HttpContext.Current.Response.Write("</x:ExcelWorksheet>\n");
HttpContext.Current.Response.Write("</x:ExcelWorksheets>\n");
HttpContext.Current.Response.Write("<x:WindowHeight>12780</x:WindowHeight>\n");
HttpContext.Current.Response.Write("<x:WindowWidth>19035</x:WindowWidth>\n");
HttpContext.Current.Response.Write("<x:WindowTopX>0</x:WindowTopX>\n");
HttpContext.Current.Response.Write("<x:WindowTopY>15</x:WindowTopY>\n");
HttpContext.Current.Response.Write("<x:ProtectStructure>False</x:ProtectStructure>\n");
HttpContext.Current.Response.Write("<x:ProtectWindows>False</x:ProtectWindows>\n");
HttpContext.Current.Response.Write("</x:ExcelWorkbook>\n");
HttpContext.Current.Response.Write("</xml><![endif]-->\n");
HttpContext.Current.Response.Write("</head>\n");
HttpContext.Current.Response.Write("<body>\n");
//HttpContext.Current.Response.Write("<h4><span style=" + "'color:#BF1428;'><b>" + naslov + " ");
//if (vrstaaplikacije == 2)
//{
// HttpContext.Current.Response.Write(StLetoMesec.StudijskoLeto.DolociStLeto_NacPO_String());
//}
//HttpContext.Current.Response.Write("</b></span></h4>");
//HttpContext.Current.Response.Write("<span style=" + "'color:red;'><i>" + opomba + "</i></span>");
//render the htmlwriter into the response
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
gv.RenderControl(oHtmlTextWriter);
HttpContext.Current.Response.Write(oStringWriter.ToString());
HttpContext.Current.Response.Write("</body>");
HttpContext.Current.Response.Write("</html>");
HttpContext.Current.Response.End();
}
Praveen Venu
try this
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.