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 :