Insert (Save) Form data to Excel file and download it in ASP.Net

Tejashre...

SOLVED CLOSED ACTIVE ANSWERED
User: Tejashree
Posted: on Sep 10, 2015 06:17 AM
Forum: Excel
Answer: 1
Views: 11240

I want to create daily new blank excel workbook using asp.net C# and insert daily login time and user name into it.

I am able to insert data successfully but not able to create new excel sheet on daily basis and save it in gridview.

please provide solution.

I am referring following code :

HTML Page 1 :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
        .auto-style2 {
            width: 103px;
            text-align: right;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <table cellpadding="0" cellspacing="0" class="auto-style1">
                <tr>
                    <td class="auto-style2">Name:</td>
                    <td>
                        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td class="auto-style2">Email:</td>
                    <td>
                        <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td class="auto-style2">Mobile No.:</td>
                    <td>
                        <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td class="auto-style2">Location:</td>
                    <td>
                        <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td class="auto-style2">Qualification:</td>
                    <td>
                        <asp:TextBox ID="TextBox5" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td class="auto-style2"></td>
                    <td>
                        <asp:Button ID="Button1" runat="server" Text="Submit" OnClick="Button1_Click" />
                    </td>
                </tr>
            </table>
        </div>


    </form>
</body>
</html>

Code Page 1 :

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;

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 ConStr = "";
        //getting the path of the file   
        string path = Server.MapPath("~/LogFiles/InsertDataExcel.xlsx");
        //connection string for that file which extantion is .xlsx  
        ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;ReadOnly=False;HDR=Yes;\"";
        //making query  
        string query = "INSERT INTO [Sheet1$] ([Name], [Email], [MobileNo], [Location], [Qualification]) VALUES('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox3.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "')";
        //Providing connection  
        OleDbConnection conn = new OleDbConnection(ConStr);
        //checking that connection state is closed or not if closed the   
        //open the connection  
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }
        //create command object  
        OleDbCommand cmd = new OleDbCommand(query, conn);
        int result = cmd.ExecuteNonQuery();
        if (result > 0)
        {
            Response.Write("<script>alert('Sucessfully Data Inserted Into Excel')</script>");
        }
        else
        {
            Response.Write("<script>alert('Sorry!\n Insertion Failed')</script>");
        }
        conn.Close();

        Response.Redirect("Default2.aspx");
    }

}

HTML Page 2 :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div class="container">
            <div class="panel panel-default">

                <div class="panel-heading">
                    <h3><b>User Log</b></h3>
                </div>

                <div class="container-fluid" style="background: #fff; padding: 10px; margin: 2px; border: 1px solid #ccc;">
                    <div class="container">
                        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" EmptyDataText="No files uploaded" Width="80%" GridLines="Vertical" BorderStyle="Solid" BorderColor="Transparent" BorderWidth="0" AlternatingRowStyle-BackColor="#e6e6e6" CellPadding="-1" CellSpacing="0" RowStyle-Height="30">
                            <Columns>
                                <asp:BoundField DataField="Text" HeaderText="File Name" />
                                <asp:TemplateField>
                                    <ItemTemplate>
                                        <asp:LinkButton ID="lnkDownload" Text="Download" CommandArgument='<%# Eval("Value") %>' runat="server" OnClick="DownloadFile"></asp:LinkButton>
                                    </ItemTemplate>
                                </asp:TemplateField>
                            </Columns>
                        </asp:GridView>
                    </div>
                </div>

            </div>
        </div>
    </form>
</body>
</html>

Code Page 2 :

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            try
            {
                string[] filePaths = Directory.GetFiles(Server.MapPath("~/LogFiles/"));
                List<ListItem> files = new List<ListItem>();
                foreach (string filePath in filePaths)
                {
                    files.Add(new ListItem(Path.GetFileName(filePath), filePath));
                }
                GridView1.DataSource = files;
                GridView1.DataBind();
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }
        }
    }

    protected void DownloadFile(object sender, EventArgs e)
    {
        string filePath = (sender as LinkButton).CommandArgument;
        Response.ContentType = ContentType;
        Response.AppendHeader("Content-Disposition", "attachment; filename=" + Path.GetFileName(filePath));
        Response.WriteFile(filePath);
        Response.End();
    }
}

there is one folder with name "LogFiles" inside that folder excel sheet with name "InsertDataExcel.xlsx"

Shashika...

0 1

Reply
ANSWER
User: Shashikant
Replied: on Sep 10, 2015 06:34 AM
Report

Here I have created sample that will help you out.

In this I have created SampleLogFile.xlsx (Note : make this file protected) that I have used to get structure of excelfile to create daily basis excel file.

Page1

HTML

<div>
    <table cellpadding="0" cellspacing="0" class="auto-style1">
        <tr>
            <td class="auto-style2">
                Name:
            </td>
            <td>
                <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td class="auto-style2">
                Email:
            </td>
            <td>
                <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td class="auto-style2">
                Mobile No.:
            </td>
            <td>
                <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td class="auto-style2">
                Location:
            </td>
            <td>
                <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td class="auto-style2">
                Qualification:
            </td>
            <td>
                <asp:TextBox ID="TextBox5" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td class="auto-style2">
            </td>
            <td>
                <asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="CreateExcel" />
            </td>
        </tr>
    </table>
</div>

Code

protected void CreateExcel(object sender, EventArgs e)
{
    string ConStr = "";
    string sourceFile = Server.MapPath("~/SampleFiles/SampleLogFile.xlsx");
    string destinationFile = Server.MapPath("~/LogFiles/InsertDataExcel" + DateTime.Now.ToShortDateString().Replace("/", "_") + ".xlsx");
    if (!File.Exists(destinationFile))
    {
        File.Copy(sourceFile, destinationFile);
    }
    ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + destinationFile + ";Extended Properties='Excel 12.0;ReadOnly=False;HDR=Yes;'";
    string query = "INSERT INTO [Sheet1$] ([Name], [Email], [MobileNo], [Location], [Qualification]) VALUES('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox3.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "')";
    OleDbConnection conn = new OleDbConnection(ConStr);
    if (conn.State == ConnectionState.Closed)
    {
        conn.Open();
    }
    OleDbCommand cmd = new OleDbCommand(query, conn);
    int result = cmd.ExecuteNonQuery();
    if (result > 0)
    {
        Response.Write("<script>alert('Sucessfully Data Inserted Into Excel')</script>");
    }
    else
    {
        Response.Write("<script>alert('Sorry!\n Insertion Failed')</script>");
    }
    conn.Close();
    Response.Redirect("Default2.aspx");
}

Page2

<div class="container">
    <div class="panel panel-default">
        <div class="panel-heading">
            <h3>
                <b>User Log</b></h3>
        </div>
        <div class="container-fluid" style="background: #fff; padding: 10px; margin: 2px;
            border: 1px solid #ccc;">
            <div class="container">
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" EmptyDataText="No files uploaded"
                    Width="80%" GridLines="Vertical" BorderStyle="Solid" BorderColor="Transparent"
                    BorderWidth="0" AlternatingRowStyle-BackColor="#e6e6e6" CellPadding="-1" CellSpacing="0"
                    RowStyle-Height="30">
                    <Columns>
                        <asp:BoundField DataField="Text" HeaderText="File Name" />
                        <asp:TemplateField>
                            <ItemTemplate>
                                <asp:LinkButton ID="lnkDownload" Text="Download" CommandArgument='<%# Eval("Value") %>'
                                    runat="server" OnClick="DownloadFile"></asp:LinkButton>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                </asp:GridView>
            </div>
        </div>
    </div>
</div>

Code

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        try
        {
            string[] filePaths = Directory.GetFiles(Server.MapPath("~/LogFiles/"));
            List<ListItem> files = new List<ListItem>();
            foreach (string filePath in filePaths)
            {
                files.Add(new ListItem(Path.GetFileName(filePath), filePath));
            }
            GridView1.DataSource = files;
            GridView1.DataBind();
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
    }
}

protected void DownloadFile(object sender, EventArgs e)
{
    string filePath = (sender as LinkButton).CommandArgument;
    Response.ContentType = ContentType;
    Response.AppendHeader("Content-Disposition", "attachment; filename=" + Path.GetFileName(filePath));
    Response.WriteFile(filePath);
    Response.End();
}

Screenshot

Insert (Save) Form data to Excel file and download it in ASP.Net

Company

Explore

Follow us

Contact Us