EXCEL:
1.Tran Date
2.Value Date
3.CHQNO
4.Transaction Particulars
5.Amount
6.DR/CR
7.Branch Name
SQL:
EXCEL:
1.Tran Date
2.Value Date
3.CHQNO
4.Transaction Particulars
5.Amount
6.DR/CR
7.Branch Name
8.Bank Name
DESIGN:
<table>
<tr class="pageText">
<td style="text-align: right">
<span style="color: red;">*</span>Category:
</td>
<td>
<asp:DropDownList ID="ddlCategory" runat="server">
<asp:ListItem>AXIS</asp:ListItem>
<asp:ListItem>INDUS</asp:ListItem>
<asp:ListItem>KVB</asp:ListItem>
<asp:ListItem></asp:ListItem>
</asp:DropDownList>
<asp:RequiredFieldValidator InitialValue="0" ID="RequiredFieldValidator1" runat="server"
ControlToValidate="ddlCategory" Display="None" ErrorMessage="Please Select Category"></asp:RequiredFieldValidator>
</td>
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
</tr>
<tr class="pageText">
<td style="text-align: right">
<span style="color: red;">*</span>Upload:
</td>
<td>
<asp:FileUpload ID="FileUpload1" runat="server" onchange="ShowImagePreview(this);" />
</td>
</tr>
<tr class="pageText">
<td style="text-align: right">
</td>
<td>
<asp:Button ID="Submit" runat="server" Text="Submit" OnClick="Submit_Click1" />
</td>
</tr>
</table>
SOURCE CODE:
protected void Page_Load(object sender, EventArgs e)
{
Label1.Visible = false;
}
protected void Submit_Click1(object sender, EventArgs e)
{
//Upload and save the file
string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(excelPath);
string conString = string.Empty;
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
switch (extension)
{
case ".xls": //Excel 97-03
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 or higher
conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
break;
}
conString = string.Format(conString, excelPath);
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
DataTable dtExcelData = new DataTable();
//[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
dtExcelData.Columns.AddRange(new DataColumn[8] {
new DataColumn("Tran Date", typeof(DateTime)),
new DataColumn("Value Date", typeof(DateTime)),
new DataColumn("CHQNO",typeof(string)),
new DataColumn("Transaction Particulars",typeof(string)),
new DataColumn("Amount(in Rs.)",typeof(decimal)),
new DataColumn("DR|CR",typeof(string)),
new DataColumn("Branch Name",typeof(string)),
new DataColumn("Bank Name",typeof(string)),
});
dtExcelData.Columns["Bank Name"].DefaultValue = ddlCategory.Text.Trim();
dtExcelData.Columns["Date"].DefaultValue = DateTime.Today.ToShortDateString();
dtExcelData.Columns["Status"].DefaultValue = 1;
dtExcelData.Columns["BStatus"].DefaultValue = 1;
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
{
SqlCommand cmd = new SqlCommand("");
//oda.Fill(dtExcelData);
}
excel_con.Close();
string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.tbl_BankTransaction";
//[OPTIONAL]: Map the Excel columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("Bank Name", "BankName");
sqlBulkCopy.ColumnMappings.Add("Tran Date", "TranDate");
sqlBulkCopy.ColumnMappings.Add("Value Date", "ValueDate");
sqlBulkCopy.ColumnMappings.Add("CHQNO", "ChekNo");
sqlBulkCopy.ColumnMappings.Add("Transaction Particulars", "Remark");
sqlBulkCopy.ColumnMappings.Add("Amount(in Rs.)", "Amount");
sqlBulkCopy.ColumnMappings.Add("DR|CR", "Type");
sqlBulkCopy.ColumnMappings.Add("Branch Name", "BranchName");
con.Open();
sqlBulkCopy.WriteToServer(dtExcelData);
con.Close();
}
}
}
}
ACTUAL RESULT:
14.10.2016 14.10.2016 123456 NEFT 2500 DR AXIS(KERALA) NULL
14.10.2016 14.10.2016 123456 NEFT 2500 DR AXIS(KERALA) NULL
14.10.2016 14.10.2016 123456 NEFT 2500 DR AXIS(KERALA) NULL
14.10.2016 14.10.2016 123456 NEFT 2500 DR AXIS(KERALA) NULL
NULL NULL NULL NULL NULL NULL NULL AXIS
EXCEPTING RESULT
14.10.2016 14.10.2016 123456 NEFT 2500 DR AXIS(KERALA) AXIS
14.10.2016 14.10.2016 123456 NEFT 2500 DR AXIS(KERALA) AXIS
14.10.2016 14.10.2016 123456 NEFT 2500 DR AXIS(KERALA) AXIS
14.10.2016 14.10.2016 123456 NEFT 2500 DR AXIS(KERALA) AXIS
14.10.2016 14.10.2016 123456 NEFT 2500 DR AXIS(KERALA) AXIS