Post
Ask Question
newto net

newto net

  • NA
  • 74
  • 24.6k

Insert grid view data to already existing excel c#

Aug 5 2015 1:26 AM

My task is to add the Grid-view data to already formatted excel, I need to insert the fetching data from Grid-view to starting from second row after header, I don`t where I am doing wrong the code is inserting data after 2 row, please find my template excel, asp-dot-net code below.

Excel

Column1 Column2 Total1 =Sum(A2+B2) =Sum(A2+B2)

 Column1   Column2   Total  
                                     =SUM(A2+B2)
 
 
 
                                           =Sum(A2+B2)

 
Dotnet Code:
<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ExcelExport._Default" %>  <asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent"> </asp:Content> <asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent"> <h2> Welcome to ASP.NET! </h2> <p> To learn more about ASP.NET visit <a href="http://www.asp.net" title="ASP.NET Website">www.asp.net</a>. </p> <p> <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Show" /> <asp:Button ID="Button2" runat="server" Text="Export to Excel"              onclick="Button2_Click" /> </p> <p> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"> <Columns> <asp:BoundField DataField="Column1" HeaderText="A" /> <asp:BoundField DataField="Column2" HeaderText="B" /> <asp:BoundField DataField="Total" HeaderText="Total" /> <asp:BoundField DataField="Total1" HeaderText="FinalTotal" /> </Columns> </asp:GridView> </p> <p> You can also find <a href="http://go.microsoft.com/fwlink/?LinkID=152368&amp;clcid=0x409"             title="MSDN ASP.NET Docs">documentation on ASP.NET at MSDN</a>. </p> </asp:Content>

Code Bind

using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data.OleDb; using System.Data; using System.Configuration; using System.Data.SqlClient;  namespace ExcelExport { public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void Button1_Click(object sender, EventArgs e) { string connection = null;             connection = ConfigurationManager.ConnectionStrings["Local"].ConnectionString; SqlConnection con = new SqlConnection(connection); string query = "select * from calc";             con.Open(); SqlCommand cmd = new SqlCommand(query, con); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet();             da.Fill(ds);             con.Close(); if (ds.Tables.Count > 0) { GridView1.DataSource = ds; GridView1.DataBind(); } } protected void Button2_Click(object sender, EventArgs e) { int gridi, j, value1, value2, value3; int rowcount = GridView1.Rows.Count; int Colcount = 3; String[] cellText; string constr, sheetinfo = ""; string path = "D:\\Template.xlsx";             constr = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;             constr = string.Format(constr, path);             using (OleDbConnection excel_con = new OleDbConnection(constr)) {                 excel_con.Open();

Answers (4)