The Page having a FileUpload control and the Upload button, on selecting the Excel or CSV file user needs to click on Upload button to store the data to Server. Here we are treating the uploaded file as database hence we need to create OLEDB connection to this file, from this connection will be created and the data is fetched to C# as DataTable. ‘[Sheet1$]’ is the Name of the Worksheet where requires data is present.
Thank you for reading this post, don't forget to subscribe!Table
CREATE TABLE [dbo].[Tag](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TagType] [int] NULL,
[TagCode] [nvarchar](100) NULL,
[Status] [bit] NULL,
[Duration] [int] NULL)
Stored Procedure
Create proc [dbo].[ExcelTag]
@TagType int,
@TagCode nvarchar(100),
@Status bit,
@Duration int
as
if Not exists(Select top 1 TagCode from dbo.[Tag] where TagCode=@TagCode)
Begin
insert into dbo.[Tag](TagType,TagCode,[Status],Duration)values(@TagType,@TagCode,@Status,@Duration)
End
Design your aspx page like this
<form id=”form1″ runat=”server”>
<div>
<table>
<tr>
<td style=”vertical-align:top”>
<asp:Label id=”lblupload” cssclass=”SubHead” runat=”server” Text=”Upload” />
</td>
<td>
<asp:FileUpload ID=”Fupload” runat=”server” /><br />
<asp:Label ID=”lblextMsg” ForeColor=”Red” CssClass=”NormalRed” runat=”server”></asp:Label>
</td>
</tr>
<tr><td colspan=”2″>
<asp:Button ID=”btnUpload” runat=”server” Text=”Upload” OnClientClick=”return ValidateFile()”
onclick=”btnUpload_Click” />
<br />
<br />
<asp:Literal ID=”LtrlFinalMessage” runat=”server”></asp:Literal>
</td></tr>
</table>
</div>
</form>
Validation on File Upload Control using Jquery
<script type=”text/javascript”>
//Jquery Validation for Upload File
var validFilesTypes = [“xls”, “csv”];
function ValidateFile() {
debugger;
var file = document.getElementById(“<%=Fupload.ClientID%>”);
var lblextMsg = document.getElementById(“<%=lblextMsg.ClientID%>”);
var path = file.value;
var ext = path.substring(path.lastIndexOf(“.”) + 1, path.length).toLowerCase();
var isValidFile = false;
for (var i = 0; i < validFilesTypes.length; i++) {
if (ext == validFilesTypes[i]) {
isValidFile = true;
break;
}
}
if (!isValidFile) {
lblextMsg.style.color = “red”;
lblextMsg.innerHTML = “Please upload a File with” + ” extension:\n\n” + validFilesTypes.join(“, “);
}
return isValidFile;
}
</script>
After that write the following code in Codebehind Button Click
FuploadInfo finfo = new FuploadInfo();
SqlConnection con = new SqlConnection(“Data Source=.\\SQLExpress;Database=Practise;Integrated Security=true;”);
protected void Page_Load(object sender, EventArgs e)
{
}
public void SaveFileContent()
{
string path = Fupload.PostedFile.FileName;
string extension = Path.GetExtension(path);
switch (extension)
{
case “.xls”:
SaveDataXLS();
break;
case “.csv”:
SaveDataCSV();
break;
}
}
public void SaveDataCSV()
{
if (Fupload.HasFile)
{
//upload = Folder Name
FileInfo f = new FileInfo(Server.MapPath(“~/upload/”) + Fupload.FileName.ToString());
DataTable dts = new DataTable();
Fupload.SaveAs(Server.MapPath(“~/upload/”) + Fupload.FileName.ToString());
string[] Lines = File.ReadAllLines(f.FullName);
string[] Fields;
Fields = Lines[0].Split(new char[] { ‘,’ });
int Cols = Fields.GetLength(0);
DataTable dt = new DataTable();
//1st row must be column names; force lower case to ensure matching later on.
for (int i = 0; i < Cols; i++)
{
dt.Columns.Add(Fields[i].ToLower(), typeof(string));
}
DataRow Row;
for (int i = 1; i < Lines.GetLength(0); i++)
{
Fields = Lines[i].Split(new char[] { ‘,’ });
Row = dt.NewRow();
for (int j = 0; j < Cols; j++)
Row[j] = Fields[j];
dt.Rows.Add(Row);
}
foreach (DataRow dr in dt.Rows)
{
if (dr[“TagType”].ToString().ToLower() == “free”)
{
finfo.TagType = 1;
}
else if (dr[“TagType”].ToString().ToLower() == “paid”)
{
finfo.TagType = 2;
}
else if (dr[“TagType”].ToString().ToLower() == “unpaid”)
{
finfo.TagType = 3;
}
else
{
lblextMsg.Text = “TagType fields is not in correct format.”;
lblextMsg.Visible = true;
break;
}
if (dr[“Duration”] != string.Empty || dr[“Duration”] != “”)
finfo.ExpirationDate = Convert.ToString(dr[“Duration”]);
else
{
lblextMsg.Text = “Duration should be integer value.”;
lblextMsg.Visible = true;
break;
}
finfo.TagCode = dr[“TagCode”].ToString();
finfo.Status = 0;
InsertedInfo(finfo);
lblextMsg.Visible = false;
}
}
}
public void SaveDataXLS()
{
if (Fupload.HasFile)
{
DataTable dts = new DataTable();
//upload = Folder Name
Fupload.SaveAs(Server.MapPath(“~/upload/”) + Fupload.FileName.ToString());
string ExcelFilePathName = Server.MapPath(“~/upload/”) + Fupload.FileName;
DataTable dts1 = new DataTable();
OleDbConnection con = new OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + ExcelFilePathName + “;Extended Properties=’Excel 8.0;HDR=Yes;'”);
DbCommand command = con.CreateCommand();
OleDbDataAdapter da = new OleDbDataAdapter(“select * from [Sheet1$]”, con);
con.Open();
DataSet ds = new DataSet();
da.Fill(dts);
if (dts.Rows.Count > 0)
{
foreach (DataRow dr in dts.Rows)
{
if (dr[“TagType”].ToString().ToLower() == “free”)
{
finfo.TagType = 1;
}
else if (dr[“TagType”].ToString().ToLower() == “paid”)
{
finfo.TagType = 2;
}
else if (dr[“TagType”].ToString().ToLower() == “unpaid”)
{
finfo.TagType = 3;
}
else
{
lblextMsg.Text = “TagType fields is not in currect formate”;
lblextMsg.Visible = true;
break;
}
if (dr[“Duration”] != string.Empty || dr[“Duration”] != “”)
finfo.ExpirationDate = Convert.ToString(dr[“Duration”]);
else
{
lblextMsg.Text = “Duration should be integer value.”;
lblextMsg.Visible = true;
break;
}
finfo.TagCode = dr[“TagCode”].ToString();
finfo.Status = 0;
InsertedInfo(finfo);
lblextMsg.Visible = false;
}
}
}
}
void showFinalmessage()
{
int ins = Convert.ToInt32(ViewState[“INSERTEFROWS”]),
fail = Convert.ToInt32(ViewState[“FAILEDFROWS”]);
LtrlFinalMessage.Text = String.Format(“Inserted Successfull:<b>{0}</b> ” +
” <br> Duplicate found:<b>{1}</b> <br> Out of Total:<b>{2}</b>”,
ins.ToString(), fail.ToString(), (ins + fail).ToString());
}
void InsertedInfo(FuploadInfo finfo)
{
if (AddFileToTable(finfo) > 0)
{
ViewState[“INSERTEFROWS”] = Convert.ToInt32(ViewState[“INSERTEFROWS”]) + 1;
}
else
{
ViewState[“FAILEDFROWS”] = Convert.ToInt32(ViewState[“FAILEDFROWS”]) + 1;
}
}
protected int AddFileToTable(FuploadInfo finfo)
{
con.Open();
SqlCommand cmd = new SqlCommand(“ExcelTag”, con);
cmd.Parameters.AddWithValue(“@TagType”, finfo.TagType);
cmd.Parameters.AddWithValue(“@TagCode”, finfo.TagCode);
cmd.Parameters.AddWithValue(“@Status”, finfo.Status);
cmd.Parameters.AddWithValue(“@Duration”, finfo.ExpirationDate);
cmd.CommandType = CommandType.StoredProcedure;
int res = cmd.ExecuteNonQuery();
con.Close();
return res;
}
protected void btnUpload_Click(object sender, EventArgs e)
{
ViewState[“INSERTEFROWS”] = 0;
ViewState[“FAILEDFROWS”] = 0;
SaveFileContent();
showFinalmessage();
}
Class
public class FuploadInfo
{
public string _expirationDate;
public int _Status;
public string _TagCode;
public int _TagType;
public string ExpirationDate { get; set; }
public int Status { get; set; }
public string TagCode { get; set; }
public int TagType { get; set; }
}
Success:
Duplicate :
Success and Duplicate:
Excel Image:
CSV Image:
Download Code: Import Excel and Csv
Comments are closed