using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace ClientManagementApp
{
public partial class Upload : Form
{
// Microsoft.Office.Interop.Excel.Range xlsRange;
string m_FlType;
public Upload()
{
InitializeComponent();
}
private void btnSelFile_Click(object sender, EventArgs e)
{
try
{
lblMsg.Text = "";
lblMsg.Update();
m_FlType = "*.xls";
OpenFileDialog op = new OpenFileDialog();
op.InitialDirectory = @"C:\";
if (m_FlType == "xls" || m_FlType == "Xls")
{
op.Filter = "Excel Files(*.xls)|*.xls|DBF Files(*.dbf)|*.dbf|Excel Files(*.xlsx)|*.xlsx";
}
op.ShowDialog();
txtSelFlNm.Text = op.FileName;
//btnImport.Enabled = true;
//btnImport.Focus();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
btnSelFile.Focus();
return;
}
}
private void btnUpload_Click(object sender, EventArgs e)
{
string M_CHQNO, M_TENURE, M_CUST_ID, M_CHQ_DATE, M_MICR;
m_Cnt1 = 0;
lblMsg.Text = "";
lblMsg.Update();
lblMsg.Text = "Upload is processing please wait..";
lblMsg.Update();
this.Cursor = Cursors.WaitCursor;
lblTotRecInFile.Visible = lblTotRecUpload.Visible = true;
//m_SysDt = Convert.ToString(DateTime.Now.ToShortDateString());
//DateTime date2 = Convert.ToDateTime(DateTime.Now.ToString());
//dt = string.Format("{0:ddMMyyyHHmmss}", date2);
//m_Inwdt = Convert.ToDateTime(m_Inwdt).ToShortDateString();
if (!File.Exists(m_FlNm))
{
Strmwrt = new StreamWriter(m_FlNm);
}
else
{
Strmwrt = File.AppendText(m_FlNm);
}
m_QryStr = "SELECT NVL(MAX(SRNO),0)+1 FROM " + cls_General.GL_NACHTxn + " WHERE NPCI_USERCODE = '" + cls_General.GL_NACHUtilityCd + "' ";
OracleDataReader drMaxsrno = DbObj.gl_ExeReader(m_QryStr);
if (drMaxsrno.Read())
{
M_SRNO = Convert.ToInt32(drMaxsrno.GetValue(0).ToString());
}
drMaxsrno.Close();
m_QryStr = "SELECT * FROM " + cls_General.GL_NACHClientPrDataTmp + " ORDER BY SRNO ";
OracleDataReader dr = DbObj.gl_ExeReader(m_QryStr);
while (dr.Read())
{
M_EcsDate = Convert.ToDateTime(dr["ECS_DATE"]).ToShortDateString();
m_QryStr = "SELECT * FROM " + cls_General.GL_NACHTxn + " WHERE UMRN='" + dr["UMRN"] + "' AND ECS_DATE=TO_DATE('" + M_EcsDate + "','DD/MM/YYYY') " +
" AND NVL(CLG_STATUS,1) <> 0 ";
OracleDataReader DR1 = DbObj.gl_ExeReader(m_QryStr);
if (DR1.Read())
{
//FOR DUPLICATE CHECKING IN DATA FILE
Strmwrt.WriteLine(string.Format("{0,-8} {1,-30} {2,-20} {3,-10} {4,-10} {5,-50}", M_RecNO, DR1["UMRN"], DR1["ACNO"], DR1["AMOUNT"], Convert.ToDateTime(DR1["ECS_DATE"]).ToString("dd/MM/yyyy"), "Duplicate Record In Data"));
M_RecNO++;
lblRejct.Visible = true;
lblRejct.Update();
m_RejCnt2++;
}
else
{
M_CycleDate = Convert.ToDateTime(dr["CYCLE_DATE"]).ToShortDateString();
M_StlmntDate = Convert.ToDateTime(dr["SETTLEMENT_DATE"]).ToShortDateString();
M_EcsDate = Convert.ToDateTime(dr["ECS_DATE"]).ToShortDateString();
m_QryStr = "INSERT INTO " + cls_General.GL_NACHTxn + " (SRNO, TXN_TYPE, TXN_DESCRIPTION, CYCLE_DATE, SETTLEMENT_DATE, CUSTOMER_NAME, " +
" AC_TYPE, LEDEGER_FOLIO, AMOUNT, ECS_DATE, BANK_CODE, ACNO, TRN_REFNO, UMRN, MOBILE_NO, EMAIL_ID, IMPORT_DATE, IMPORT_USER,NPCI_USERCODE,NPCI_USERNAME,CUST_REFNO, SCH_REFNO,PYMT_TYPE) VALUES " +
" ('" + M_SRNO + "','" + dr["TXN_TYPE"] + "','" + dr["TXN_DESCRIPTION"] + "',TO_DATE('" + M_CycleDate + "','DD/MM/YYYY'), " +
" TO_DATE('" + M_StlmntDate + "','DD/MM/YYYY'),'" + dr["CUSTOMER_NAME"] + "','" + dr["AC_TYPE"] + "','" + dr["LEDEGER_FOLIO"] + "'," +
" '" + dr["AMOUNT"] + "',TO_DATE('" + M_EcsDate + "','DD/MM/YYYY'),'" + dr["BANK_CODE"] + "','" + dr["ACNO"] + "'," +
"'" + dr["TRN_REFNO"] + "','" + dr["UMRN"] + "','" + dr["MOBILE_NO"] + "','" + dr["EMAIL_ID"] + "',TO_DATE('" + cls_General.GL_SystmDt + "','DD/MM/YYYY')," + cls_General.GL_UsrCd + ",'" + dr["NPCI_USERCODE"] + "','" + cls_General.GL_NPCIUserName.Trim() + "','" + dr["CUST_REFNO"] + "','" + dr["SCH_REFNO"] + "','" + dr["PYMT_TYPE"] + "')";
DbObj.gl_ExeNonQuery(m_QryStr);
M_SRNO++;
m_Cnt1++;
}
DR1.Close();
}
dr.Close();
Strmwrt.Close();
lblTotRecInFile.Text = "Total Record In Upload File:" + M_CNT;
lblTotRecUpload.Text = "Total Record Upload :" + m_Cnt1;
lblRejct.Text = "Total Record Rejct/Duplicate :" + m_RejCnt2;
lblTotRecInFile.Update();
lblTotRecUpload.Update();
if (m_RejCnt2 > 0)
{
lblMsg.Text = "File Uploaded with remarks";
MessageBox.Show("File Uploaded with remarks " + m_FlNm);
}
else
{
lblMsg.Text = "File Uploaded Sucessfully";
}
lblMsg.Update();
btnUpload.Enabled = false;
btnUpload.Update();
this.Cursor = Cursors.Default;
}
}How to Import Excel file into C# in Windows form application.
