Hi on clicking export to excel null reference exception is arised on rowcreated method in DataTable dt = (e.Row.DataItem as DataRowView).DataView.Table; please help me to resolve it
thanks in advance
private void BindGrid()
{
string query = "SELECT OrderID,";
query += "(SELECT ProductName FROM Products WHERE ProductID = details.ProductId) ProductName,";
query += "(Quantity * UnitPrice) Price,";
query += "Quantity,";
query += "UnitPrice";
query += " FROM [Order Details] details";
query += " WHERE OrderID IN (10248, 10249, 10250)";
query += " ORDER BY OrderID";
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlCommand cmd = new SqlCommand(query))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
sda.Fill(dtt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}
}
}
int currentId = 0;
decimal subTotal = 0;
decimal subTotal1 = 0;
decimal subTotal2 = 0;
decimal total = 0;
decimal total1 = 0;
decimal total2 = 0;
int subTotalRowIndex = 0;
protected void OnRowCreated(object sender, GridViewRowEventArgs e)
{
subTotal = 0;
subTotal1 = 0;
subTotal2 = 0;
if (e.Row.RowType == DataControlRowType.DataRow)
{
DataTable dt = (e.Row.DataItem as DataRowView).DataView.Table;
int orderId = Convert.ToInt32(dtt.Rows[e.Row.RowIndex]["OrderID"]);
total2 += Convert.ToDecimal(dtt.Rows[e.Row.RowIndex]["Quantity"]);
total1 += Convert.ToDecimal(dtt.Rows[e.Row.RowIndex]["UnitPrice"]);
total += Convert.ToDecimal(dtt.Rows[e.Row.RowIndex]["Price"]);
if (orderId != currentId)
{
if (e.Row.RowIndex > 0)
{
for (int i = subTotalRowIndex; i < e.Row.RowIndex; i++)
{
subTotal += Convert.ToDecimal(GridView1.Rows[i].Cells[3].Text);
subTotal1 += Convert.ToDecimal(GridView1.Rows[i].Cells[2].Text);
subTotal2 += Convert.ToDecimal(GridView1.Rows[i].Cells[1].Text);
}
this.AddTotalRow("Sub Total", subTotal2.ToString("N2"), subTotal1.ToString("N2"), subTotal.ToString("N2"));
subTotalRowIndex = e.Row.RowIndex;
}
currentId = orderId;
}
}
}
private void AddTotalRow(string labelText, string value2,string value1, string value)
{
GridViewRow row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Normal);
row.BackColor = ColorTranslator.FromHtml("#F9F9F9");
row.Cells.AddRange(new TableCell[4] {new TableCell { Text = labelText, HorizontalAlign = HorizontalAlign.Right},
new TableCell { Text = value2, HorizontalAlign = HorizontalAlign.Right },
new TableCell { Text = value1, HorizontalAlign = HorizontalAlign.Right } ,//Empty Cell
new TableCell { Text = value, HorizontalAlign = HorizontalAlign.Right } });
GridView1.Controls[0].Controls.Add(row);
}
protected void OnDataBound(object sender, EventArgs e)
{
for (int i = subTotalRowIndex; i < GridView1.Rows.Count; i++)
{
subTotal += Convert.ToDecimal(GridView1.Rows[i].Cells[3].Text);
subTotal1 += Convert.ToDecimal(GridView1.Rows[i].Cells[2].Text);
subTotal2 += Convert.ToDecimal(GridView1.Rows[i].Cells[1].Text);
}
this.AddTotalRow("Sub Total", subTotal2.ToString("N2"), subTotal1.ToString("N2"), subTotal.ToString("N2"));
this.AddTotalRow("Total", total2.ToString("N2"), total1.ToString("N2"), total.ToString("N2"));
}
protected void btnExportExcel_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);
GridView1.AllowPaging = false;
//BindGrid();
GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");
GridView1.HeaderRow.Cells[0].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[1].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[2].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[3].Style.Add("background-color", "green");
for (int i = 0; i < GridView1.Rows.Count; i++)
{
GridViewRow row = GridView1.Rows[i];
row.BackColor = System.Drawing.Color.White;
row.Attributes.Add("class", "textmode");
if (i % 2 != 0)
{
row.Cells[0].Style.Add("background-color", "#C2D69B");
row.Cells[1].Style.Add("background-color", "#C2D69B");
row.Cells[2].Style.Add("background-color", "#C2D69B");
row.Cells[3].Style.Add("background-color", "#C2D69B");
}
}
GridView1.RenderControl(hw);
string style = @"<style> .textmode { mso-number-format:\@; } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{
/* Verifies that the control is rendered */
}
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" OnDataBound="OnDataBound"
OnRowCreated="OnRowCreated" DataKeyNames="OrderID">
<Columns>
<asp:BoundField DataField="OrderID" HeaderText="Product Name" ItemStyle-Width="210" />
<asp:BoundField DataField="Quantity" HeaderText="Quantity" ItemStyle-Width="60" DataFormatString="{0:N2}"
ItemStyle-HorizontalAlign="Right" />
<asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" ItemStyle-Width="60" DataFormatString="{0:N2}"
ItemStyle-HorizontalAlign="Right" />
<asp:BoundField DataField="Price" HeaderText="Price" ItemStyle-Width="60" DataFormatString="{0:N2}"
ItemStyle-HorizontalAlign="Right" />
</Columns>
</asp:GridView>
<br />
<asp:Button ID="Button1" Text="Export" runat="server" OnClick="btnExportExcel_Click" />