|
Hi,
I have a gridview that is exported to Excel using C#.
This all works great. The GridView is populated after selecting from a dropdownlist to filter the results.
I would like to add a title to the worksheet so users know what the results refer to. I would like to use the value of the dropdownlist. Is this possible.
My code is
<%@ Page Title="" Language="C#" MasterPageFile="~/MasterPages/MasterPage.master" AutoEventWireup="true" CodeFile="TeamVolumeSplitQ32011.aspx.cs" Inherits="Printing_Team_Usage_TeamVolumeSplit" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="TeamNameSrc"
DataTextField="LongName" DataValueField="LedgerID"
AppendDataBoundItems="True" AutoPostBack="True">
<asp:ListItem Value = "">Please Make a Selection</asp:ListItem>
</asp:DropDownList>
<asp:SqlDataSource ID="TeamNameSrc" runat="server"
ConnectionString="<%$ ConnectionStrings:Docupro_ReportingConnectionString %>"
SelectCommand="SELECT [LedgerID], [LongName] FROM [tblLedger]">
</asp:SqlDataSource>
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
AllowSorting="True" AutoGenerateColumns="False" BackColor="White"
BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4"
DataSourceID="TeamVolumeSrc" ForeColor="Black" GridLines="Vertical"
ShowFooter="True"
EmptyDataText="There are no records to display"
onrowdatabound="GridView1_RowDataBound">
<PagerSettings Mode="NextPreviousFirstLast" NextPageText="Next"
PreviousPageText="Prev" />
<RowStyle BackColor="#F7F7DE" />
<Columns>
<asp:TemplateField HeaderText="User" SortExpression="User">
<EditItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("User") %>'></asp:Label>
</EditItemTemplate>
<FooterTemplate>
<asp:Label ID="lblUser" runat="server" Font-Bold="True" Text="Totals"></asp:Label>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("User") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Colour" SortExpression="Colour">
<EditItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Eval("Colour", "{0:#,0}") %>'></asp:Label>
</EditItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTotalColour" runat="server" Font-Bold="True" Text="Label"></asp:Label>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("Colour", "{0:#,0}") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="B&W" SortExpression="B&W">
<EditItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Eval("[B&W]", "{0:#,0}") %>'></asp:Label>
</EditItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTotalBW" runat="server" Font-Bold="True" Text="Label"></asp:Label>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("[B&W]", "{0:#,0}") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Total Clicks" SortExpression="Total">
<EditItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Eval("[Total Clicks]", "{0:#,0}") %>'></asp:Label>
</EditItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTotalClicks" runat="server" Font-Bold="True" Text="Label"></asp:Label>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("[Total Clicks]", "{0:#,0}") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#CCCC99" />
<PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
<SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<asp:SqlDataSource ID="TeamVolumeSrc" runat="server"
ConnectionString="<%$ ConnectionStrings:Docupro_ReportingConnectionString %>" SelectCommand="USE Docupro_Reporting
select
T5.DisplayName AS 'User',
0+COALESCE(SUM(CASE
WHEN T2.LongName LIKE '%Colour%' THEN T1.Quantity
END),0)AS Colour,
0+COALESCE(SUM(CASE
WHEN T2.LongName LIKE '%B&W%' THEN T1.Quantity
END),0)AS 'B&W',
SUM(T1.Quantity) AS 'Total Clicks'
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
JOIN tblTender T4 ON T1.TenderID = T4.TenderID
JOIN tblCustomer T5 ON T4.CustomerID = T5.CustomerID
WHERE (TransactionDateTime BETWEEN '2011-10-01' AND '2012-01-01')
AND
(T2.LongName <> 'Scan 2' AND T2.LongName <> 'Scan 1')
AND T3.LedgerID = @LedgerID
GROUP BY
T5.DisplayName
ORDER BY T5.DisplayName">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="LedgerID"
PropertyName="SelectedValue" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="ContentPlaceHolder2" Runat="Server">
<asp:Button ID="btnExcelExport" runat="server" Text="Export To Excel"
onclick="btnExcelExport_Click" />
<br />Paging Enabled?
<asp:RadioButtonList ID="rbPaging" runat="server">
<asp:ListItem Text = "Yes" Value = "True" Selected = "True">
</asp:ListItem>
<asp:ListItem Text = "No" Value = "False"></asp:ListItem>
</asp:RadioButtonList>
<br />
</asp:Content>
Code behind is
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.IO;
using System.Configuration;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
public partial class Printing_Team_Usage_TeamVolumeSplit : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
int totalColour = 0;
int totalBW = 0;
int totalClicks = 0;
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
totalColour += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "Colour"));
totalBW += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "B&W"));
totalClicks += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "Total clicks"));
}
if (e.Row.RowType == DataControlRowType.Footer)
{
Label lblColour = (Label)e.Row.FindControl("lblTotalColour");
Label lblBW = (Label)e.Row.FindControl("lblTotalBW");
Label lblClicks = (Label)e.Row.FindControl("lblTotalClicks");
lblColour.Text = totalColour.ToString("#,#");
lblBW.Text = totalBW.ToString("#,#");
lblClicks.Text = totalClicks.ToString("#,#");
}
}
protected void btnExcelExport_Click(object sender, EventArgs e)
{
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);
PrepareForExport(GridView1);
Table tb = new Table();
TableRow tr1 = new TableRow();
TableCell cell1 = new TableCell();
cell1.Controls.Add(GridView1);
tr1.Cells.Add(cell1);
TableCell cell2 = new TableCell();
cell2.Text = " ";
TableRow tr2 = new TableRow();
tr2.Cells.Add(cell2);
TableRow tr3 = new TableRow();
tb.Rows.Add(tr1);
tb.Rows.Add(tr2);
tb.Rows.Add(tr3);
ClearControls(GridView1);
tb.RenderControl(hw);
string style = @"<style>.textmode{mso-number-format:\@;}</style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
protected void PrepareForExport(GridView Gridview)
{
Gridview.AllowPaging = Convert.ToBoolean(rbPaging.SelectedItem.Value);
Gridview.DataBind();
Gridview.HeaderRow.Style.Add("background-color", "#FFFFFF");
for (int k = 0; k < Gridview.HeaderRow.Cells.Count; k++)
{
Gridview.HeaderRow.Cells[k].Style.Add("background-color", "green");
}
for (int i = 0; i < Gridview.Rows.Count; i++)
{
GridViewRow row = Gridview.Rows[i];
row.BackColor = System.Drawing.Color.White;
row.Attributes.Add("class", "number");
if (i % 2 != 0)
{
for (int j = 0; j < Gridview.Rows[i].Cells.Count; j++)
{
row.Cells[j].Style.Add("background-color", "#C2D69B");
}
}
}
}
private void ClearControls(Control control)
{
for (int i = control.Controls.Count - 1; i >= 0; i--)
{
ClearControls(control.Controls[i]);
}
if (!(control is TableCell))
{
if (control.GetType().GetProperty("SelectedItem") != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
try
{
literal.Text =
(string)control.GetType().GetProperty("SelectedItem").
GetValue(control, null);
}
catch
{ }
control.Parent.Controls.Remove(control);
}
else if (control.GetType().GetProperty("Text") != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
literal.Text =
(string)control.GetType().GetProperty("Text").
GetValue(control, null);
control.Parent.Controls.Remove(control);
}
}
return;
}
public override void VerifyRenderingInServerForm(Control control)
{
}
}
Many thanks
Andy
|
|
|
|
|
private void btnvalidate_Click(object sender, EventArgs e)
{
string adress;
Validateuradress vadrs = new Validateuradress();
adress = (dgv1.SelectedCells[0].Value.ToString());
if (vadrs.validate(adress))
{
DataGridViewImageColumn img = new DataGridViewImageColumn();
Image image = Image.FromFile(@"Valide.png");I have a problem at this level, never accepts the path of the image
dgv1.Columns.Add(img);
img.HeaderText = "Validate";
img.Name = "img";
}
|
|
|
|
|
That is probably because you aren't specifying one.
"Valide.png" does not have any path information, so it will be looked for in the current folder - which is likely to be the one the original EXE was run from, unless it has been changed in the meantime.
I would strongly suggest that you specify the path, at least relative to where the EXE started:
string appPath = Path.GetDirectoryName(Application.ExecutablePath); If your image file is in a floder below that, add the folder name on, and you should be able to get to your file.
Ideological Purity is no substitute for being able to stick your thumb down a pipe to stop the water
|
|
|
|
|
thank you , 
but I'll use it later because I want to click a submit button hard, I want each line before an image is displayed (valid or not valid)
private void btnvalidate_Click(object sender, EventArgs e)
{
string adress;
Validateuradress vadrs = new Validateuradress();
adress = (dgv1.SelectedCells[0].Value.ToString()); // test with a single line
if (vadrs.validate(adress))
{
Image image = Image.FromFile("Valide.png");
dgv1.Columns.Add(new DataGridViewImageColumn() { HeaderText = "Validate", DataPropertyName = "image" });
dgv1.CellFormatting += new DataGridViewCellFormattingEventHandler(dgv1_CellFormatting);
}
}
private void dgv1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
if (e.ColumnIndex == 1 && e.RowIndex != dgv1.NewRowIndex)
{
e.Value = File.ReadAllBytes(dgv1.Rows[e.RowIndex].Cells[1].Value.ToString()); // exeption file not found
}
}
|
|
|
|
|
I have a first form with a datagrid I view with one column there is a list of email addresses, I want to retrieve these values in the combobox the second form when click on the button here is the code, it does not work.
private void btnSend_Click(object sender, EventArgs e)
{
int i;
int j = dgv1.Rows.Count;
List<string> liste= new List<string>();
SendMail s = new SendMail();
s.MdiParent = this;
for (i = 0; i < j; i++)
{
liste[i] = dgv1.Rows[i].Cells[0].Value.ToString();
}
s.remplirCombo(liste);
s.Show();
}
|
|
|
|
|
MemberDotNetting wrote: it does not work.
That's a vague statement. What do you mean by "it does not work"?
Can you post the code for s.remplirCombo()? That's where you seem to populate the combobox.
[edit]
OriginalGriff is absolutely right. You need to first construct your list.
[/edit]
/ravi
|
|
|
|
|
public void remplirCombo(List<string> rw)
{
cmb.Items.Add(rw);
}
|
|
|
|
|
That needs to change too: Add is to add a single item. You need AddRange instead.
It may be worth your using Clear on the Items list as well, before you do this (to prevent it adding the values two or more times)
public void remplirCombo(List<string> rw)
{
cmb.Items.Clear();
cmb.Items.AddRange(rw);
}
Ideological Purity is no substitute for being able to stick your thumb down a pipe to stop the water
|
|
|
|
|
I tried it but AddRange does not accept the list as an argument
|
|
|
|
|
I forgot - AddRange needs an array, not an IEnumerable...
public void remplirCombo(List<string> rw)
{
cmb.Items.Clear();
cmb.Items.AddRange(rw.ToArray());
}I just love consistency in .NET... 
Ideological Purity is no substitute for being able to stick your thumb down a pipe to stop the water
|
|
|
|
|
okey 
|
|
|
|
|
That doesn't work, because liste does not have any elements, so trying to assign them with
liste[i] = dgv1.Rows[i].Cells[0].Value.ToString(); will give you an error.
Try this instead:
private void btnSend_Click(object sender, EventArgs e)
{
int i;
List<string> liste = new List<string>();
SendMail s = new SendMail();
s.MdiParent = this;
foreach (DataGridViewRow row in dgv1.Rows)
{
liste.Add(row.Cells[0].Value.ToString());
}
s.remplirCombo(liste);
s.Show();
}(Because you don't care about the row number, I would use a foreach loop instead of the for - it's just a bit clearer to read)
[edit]Typo: "lop" instead of "loop", "teh" instead of "the" - OriginalGriff[/edit]
Ideological Purity is no substitute for being able to stick your thumb down a pipe to stop the water
|
|
|
|
|
OriginalGriff wrote: because liste does not have any elements,
Note to self: don't answer posts before caffeinating.
/ravi
|
|
|
|
|
Took me a while to work out what you meant there - I didn't spot that we have both answered the question... 
Yes, I find a large ![Java | [Coffee]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/coffee.gif) injection is mandatory before my little grey cells begin to mesh with any form of efficiency either!
Ideological Purity is no substitute for being able to stick your thumb down a pipe to stop the water
|
|
|
|
|
thank you, but it generates an exception: Object reference not set to an instance of an object.
|
|
|
|
|
Where? Which line?
Ideological Purity is no substitute for being able to stick your thumb down a pipe to stop the water
|
|
|
|
|
liste.Add(row.Cells[0].Value.ToString());
|
|
|
|
|
Which means that either:
1) You missed the line saying:
List<string> liste = new List<string>();
So liste is null.
2) There is no value in the row - which means that it has probably come from a database as DBNull and been translated as null.
Try an experiment:
foreach (DataGridViewRow row in dgv1.Rows)
{
DataGridViewCell cell = row.Cells[0];
if (cell != null)
{
object o = cell.Value;
if (o != null)
{
liste.Add(o.ToString());
}
else
{
Message.Show("Null in cell.Value");
}
}
else
{
MessageBox.Show("Null in cell");
}
}
Or look in the debugger and see which of them is null.
Ideological Purity is no substitute for being able to stick your thumb down a pipe to stop the water
|
|
|
|
|
No, I already instantiate the list, ok I want to try it, thank you
|
|
|
|
|
"Null in cell"
I retrieve data from datagrid via an exel file!
|
|
|
|
|
So you need to check for it and provide a default, or not add it to the list. Or find out why some of your values are blank!
Ideological Purity is no substitute for being able to stick your thumb down a pipe to stop the water
|
|
|
|
|
anyway thank you 
|
|
|
|
|
You're welcome!
Ideological Purity is no substitute for being able to stick your thumb down a pipe to stop the water
|
|
|
|
|
hello guys... I am trying to write this simple service using this tutorial.
<a href="http://msdn.microsoft.com/en-us/library/aa984464(v=vs.71).aspx">MSDN Tutorial</a>.
To the best of my knowledge, i am doing exactly the same as the tutorial suggests. But still it does not insert the record in the SQL SERVER. The problem is, it does not give an error as well, thats why dont know what to do.
I have tested this insertion operation with another simple Windows Forms application and it works absolutely fine.
What can be the problem? thnx for any pointers.
|
|
|
|
|
We would have to see your code.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
