Thursday 3 January 2013

export and import excel sheet through c sharp

 c sharp code :


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web.SessionState;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.OleDb;

namespace excel_upload
{
    public partial class upload : System.Web.UI.Page
    {
        protected System.Web.UI.HtmlControls.HtmlInputFile File1;
        protected System.Web.UI.HtmlControls.HtmlInputButton Submit1;
        DataTable dt = new DataTable();
        protected void Page_Load(object sender, EventArgs e)
        {
            //if (IsPostBack)
            //{
            //    Button1_Click(sender,e);
            //}

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            if ((File1.PostedFile != null) && (File1.PostedFile.ContentLength > 0))
            {
                string fn = System.IO.Path.GetFileName(File1.PostedFile.FileName);

                string naming = fn.Split('.')[0] + DateTime.Now.ToString("yyyymmdd") + ".csv";// +fn.Split('.')[1]; //fn.Substring(0, 4);// fn + DateTime.Now;
                //string sure = naming + DateTime.Now + ".csv";
                string SaveLocation = Server.MapPath("Data") + "\\" + naming;
                try
                {
                    File1.PostedFile.SaveAs(SaveLocation);
                    Response.Write("The file has been uploaded.");
                }
                catch (Exception ex)
                {
                    Response.Write("Error: " + ex.Message);
                    //Note: Exception.Message returns a detailed message that describes the current exception.
                    //For security reasons, we do not recommend that you return Exception.Message to end users in
                    //production environments. It would be better to return a generic error message.
                }
            }
            else
            {
                Response.Write("Please select a file to upload.");
            }
        }

        protected void btnimport_Click(object sender, EventArgs e)
        {

            if ((File1.PostedFile != null) && (File1.PostedFile.ContentLength > 0))
            {
                string fn = System.IO.Path.GetFileName(File1.PostedFile.FileName);

                string naming = fn.Split('.')[0] + DateTime.Now.ToString("yyyymmdd") + ".xls";// +fn.Split('.')[1]; //fn.Substring(0, 4);// fn + DateTime.Now;
                //string sure = naming + DateTime.Now + ".csv";
                string SaveLocation = Server.MapPath("Data") + "\\" + naming;
                try
                {
                    File1.PostedFile.SaveAs(SaveLocation);
                    Response.Write("The file has been uploaded.");
                }
                catch (Exception ex)
                {
                    Response.Write("Error: " + ex.Message);
                    //Note: Exception.Message returns a detailed message that describes the current exception.
                    //For security reasons, we do not recommend that you return Exception.Message to end users in
                    //production environments. It would be better to return a generic error message.
                }
            }
            else
            {
                Response.Write("Please select a file to upload.");
            }

            string connString = "";
            string strFileType = Path.GetExtension(File1.PostedFile.FileName).ToLower();
            string path = File1.PostedFile.FileName;
            if (strFileType.Trim() == ".xls")
            {
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            }

            //if (strFileType.Trim() == ".csv")
            //{
            //    connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            //}
            if (strFileType.Trim() == ".xlsx")
            {
                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }

            string query = "SELECT * FROM [Sheet1$]";

            OleDbConnection conn = new OleDbConnection(connString);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            OleDbCommand cmd = new OleDbCommand(query, conn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
           
            dt = ds.Tables[0];
            foreach (DataRow dr in dt.Rows)
            {
                string name = dr["Name"].ToString();
                string Extention = dr["Extension"].ToString();
                string customer = dr["Customer"].ToString();
                string bt = dr["BASE TABLE"].ToString();

            }
            grvExcelData.DataSource = ds.Tables[0];
            grvExcelData.DataBind();
            ViewState["dtList"] = dt;


            da.Dispose();
            conn.Close();
            conn.Dispose();
        }
        public override void VerifyRenderingInServerForm(Control control)
        {
            /* Verifies that the control is rendered */
        }

        protected void btnexport_Click(object sender, EventArgs e)
        {
            //Response.ClearContent();
            //Response.Buffer = true;
            //Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Customers.xls"));
            //Response.ContentType = "application/ms-excel";
            //StringWriter sw = new StringWriter();
            //HtmlTextWriter htw = new HtmlTextWriter(sw);
            //grvExcelData.AllowPaging = false;
            //grvExcelData.DataBind();
            //grvExcelData.RenderControl(htw);
            //Response.Write(sw.ToString());
            //Response.End();

            try
            {
                DataTable dt1 = (DataTable)ViewState["dtList"];
                if (dt1 == null)
                {
                    throw new Exception("No Records to Export");
                }
                string Path = "D:\\ImportExcelFromDatabase\\myexcelfile_" + DateTime.Now.Day.ToString() + "_" + DateTime.Now.Month.ToString() + ".xls";
                FileInfo FI = new FileInfo(Path);
                StringWriter stringWriter = new StringWriter();
                HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWriter);
                DataGrid DataGrd = new DataGrid();
                DataGrd.DataSource = dt1;
                DataGrd.DataBind();

                DataGrd.RenderControl(htmlWrite);
                string directory = Path.Substring(0, Path.LastIndexOf("\\"));// GetDirectory(Path);
                if (!Directory.Exists(directory))
                {
                    Directory.CreateDirectory(directory);
                }

                System.IO.StreamWriter vw = new System.IO.StreamWriter(Path, true);
                stringWriter.ToString().Normalize();
                vw.Write(stringWriter.ToString());
                vw.Flush();
                vw.Close();
                WriteAttachment(FI.Name, "application/vnd.ms-excel", stringWriter.ToString());
            }
            catch (Exception ex)
            {
                //throw new Exception(ex.Message);
            }
         
        }
        public static void WriteAttachment(string FileName, string FileType, string content)
        {
            HttpResponse Response = System.Web.HttpContext.Current.Response;
            Response.ClearHeaders();
            Response.AppendHeader("Content-Disposition", "attachment; filename=" + FileName);
            Response.ContentType = FileType;
            Response.Write(content);
            Response.End();

        }

    }
}

asp.net code :

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="upload.aspx.cs" Inherits="excel_upload.upload" EnableEventValidation="false" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="Form1" method="post" enctype="multipart/form-data" runat="server">
<input type="file" id="File1" name="File1" runat="server" />
<br/>
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="upload" />
    <asp:Button ID="btnimport" runat="server" onclick="btnimport_Click"
        Text="Import" />
        <asp:GridView ID="grvExcelData" runat="server">
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
</asp:GridView>
<asp:Button ID="btnexport" runat="server" Text="Export to Excel"
        onclick="btnexport_Click" />
</form>
</body>
</html>

create one folder with name Data in project.

reference :



No comments:

Post a Comment