Click here to Skip to main content
12,457,938 members (50,609 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C# MS-Excel
Hi,

My module is to insert values in the front end and want to save that in an excel sheet.. I passed the textbox values to grid and passed the grid to excel..
After rntering the values are passed in grid, i ll give save and it ll be saved in my excel..
If i reopen the application and enter the values and give save, then the new values are not updating in the same excel sheet.. Its again opening in a different sheet..

I want the values to be updated in the same excel sheet..
This is how my frontend look :

Name --------------
Age --------------
 
[submit] [save] 

after giving submit, the grid appears with the value and if i click save, the excel sheet populates.

Below is my code. :
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.Reflection;
using System.IO;
 
public partial class _Default : System.Web.UI.Page
{
    //Microsoft.Office.Interop.Excel.Application oXL;
    //Microsoft.Office.Interop.Excel._Workbook oWB;
    //Microsoft.Office.Interop.Excel._Worksheet oSheet; 
    private DataTable _dt;
 
    public DataTable dt
    {
        get
        {
            return _dt;
        }
        set
        {
            _dt = value;
        }
    }
 
    protected void Page_Load(object sender, EventArgs e)
    {
        
        if (!IsPostBack)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("CodeID", typeof(string));
            dt.Columns.Add("Content", typeof(string));
            dt.Columns.Add("MappingCode", typeof(string));
            Session["dt"] = dt;
        }
        _dt = (DataTable)Session["dt"];
      
 
           }
    private void BindGrid()
    {
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
 
    private void ExporttoExcel(DataTable table)
    {
 
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.ClearContent();
        HttpContext.Current.Response.ClearHeaders();
        HttpContext.Current.Response.Buffer = true;
        HttpContext.Current.Response.ContentType = "application/ms-excel";
        //HttpContext.Current.Response.ContentType = "application/ms-word";
        HttpContext.Current.Response.Write(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=Reports.xls");
        // HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=Reports.doc");
        HttpContext.Current.Response.Charset = "utf-8";
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
        HttpContext.Current.Response.Write("<font style='font-size:10.0pt; font-family:Calibri;'>");
        HttpContext.Current.Response.Write("<BR><BR><BR>");
        HttpContext.Current.Response.Write("<Table border='1' bgColor='#ffffff' borderColor='#000000' cellSpacing='0' cellPadding='0' style='font-size:10.0pt; font-family:Calibri; background:white;'> <TR>");
        int columnscount = GridView1.Columns.Count;
 
        for (int j = 0; j < columnscount; j++)
        {
            HttpContext.Current.Response.Write("<Td>");
            HttpContext.Current.Response.Write("");
            HttpContext.Current.Response.Write(GridView1.Columns[j].HeaderText.ToString());
            HttpContext.Current.Response.Write("");
            HttpContext.Current.Response.Write("</Td>");
        }
        HttpContext.Current.Response.Write("</TR>");
        foreach (DataRow row in table.Rows)
        {
            HttpContext.Current.Response.Write("<TR>");
            for (int i = 0; i < table.Columns.Count; i++)
            {
                HttpContext.Current.Response.Write("<Td>");
                HttpContext.Current.Response.Write(row[i].ToString());
                HttpContext.Current.Response.Write("</Td>");
            }
 
            HttpContext.Current.Response.Write("</TR>");
        }
        HttpContext.Current.Response.Write("</Table>");
        HttpContext.Current.Response.Write("</font>");
        HttpContext.Current.Response.Flush();
        HttpContext.Current.Response.End();
    }
    protected void Btn_Export_Click(object sender, EventArgs e)
    {
                Response.Clear();
        Response.AddHeader("content-disposition", "attachment;filename=GridView1.xls");
        Response.Charset = "";
        Response.ContentType = "application/vnd.xls";
        StringWriter StringWriter = new System.IO.StringWriter();
        HtmlTextWriter HtmlTextWriter = new HtmlTextWriter(StringWriter);
        GridView1.RenderControl(HtmlTextWriter);
        Response.Write(StringWriter.ToString());
        Response.End();
           }
    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        DataTable dt = (DataTable)Session["dt"];
        DataRow drToGrid = dt.NewRow();
        drToGrid["CodeID"] = txtCodeID.Text;
        drToGrid["Content"] = txtContent.Text;
        drToGrid["MappingCode"] = txtMappingCode.Text;
 
        dt.Rows.Add(drToGrid);
        GridView1.DataSource = dt;
        GridView1.DataBind();
        BindGrid();
        txtCodeID.Text = txtContent.Text = txtMappingCode.Text = string.Empty;
           
 
    }
    
 

    public override void VerifyRenderingInServerForm(Control control)
    {
 
        /* Verifies that the control is rendered */
 
    }
   
}

Can someone please help me or give me any idea..

Thanks a ton. Smile | :)

Anusha
Posted 13-Sep-12 18:06pm

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

hi....

u can make manipulate data in excel file in the same way u do with sql server or any other database. There will be only some minor changes. First of all, connection string will change. for e.g. ur excel file is "test.xlsx" in D drive, then:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\test.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES';

(Hope it works!! If it does not, then there are many more.)

Then u need to use OledbConnection and similar classes for connectivity.

The table name will be the Sheet name with '$' sign at end.

For e.g. to select all rows from "Sheet1", query will be:

select * from [Sheet1$]

In similar way, to update:

update [Sheet1$] set Name = 'abc',Age = 15 where Id = 1


try this type of code and believe it will definately work. Works fine for me.

Happy coding.... Smile | :)
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160826.1 | Last Updated 14 Sep 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100