Hello every body
I am curently working on windows project. I want to import the excel data into database using c#.
My SpreadSheet is
ExcelSheet
|--------------------------------------------------|
| |
|--------------------------------------------------|
| |
|--------------------------------------------------|
| Merchant no:12345 |
|--------------------------------------------------|
| merchant no| Id No |Amount | branch name |
|------------|-------------|---------|-------------|
| 12345 | 101 | 10000 | Hyd |
|------------| ------------| --------|-------------|
| 12345 | 102 | 20000 | Bombay |
|------------|-------------|---------|-------------|
| 12345 | 103 | 30000 | Delhi |
--------------------------------------------------
Now I want to read total excel sheet based on cell
value=12345
(here starting two rows are empty and my code read all records ignore the empteis) and stored all records into database.This is my task. I already implement the code like below
private void btnImport_Click(object sender, EventArgs e)
{
try
{
DataTable dtExcel=new DataTable();
string SourceConstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + txtExcelFile.Text + "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'";
OleDbConnection con = new OleDbConnection(SourceConstr);
string query="Select * from [Sheet1$]";
OleDbDataAdapter data=new OleDbDataAdapter(query,con);
data.Fill(dtExcel);
string DestConstr = @"Data Source=COMPUTER-8EB749;Initial Catalog=TRMSDB;Integrated Security=true";
SqlConnection connection = new SqlConnection(DestConstr);
connection.Open();
string Mno = "";
foreach (DataRow rowExcel in dtExcel.Rows)
{
foreach (DataColumn colExcel in dtExcel.Columns)
{
Mno = rowExcel[colExcel].ToString().Trim();
if (Mno != "")
{
string Mno1 = Mno.Substring(16, 10);
Mno =Mno1.ToString();
}
break;
}
if(Mno!="")
{
for(int i=0;i<dtexcel.rows.count;i++)>
{
if (dtExcel.Rows.Contains("MerchantNo=1105393011"))
{
string str = dtExcel.Rows[i][0].ToString().Trim();
string str1 = dtExcel.Rows[i][1].ToString().Trim();
}
}
}
}
}
Yes I have this type of knowledge for import the excel into data table and data table to data base.
My requirements on this excel sheet are
1. ignore the empty rows.
2. not read the first two rows and treated as empty rows.
3. read records based on cell value(12345)
my Queries are
1. I want to read total record at a time or read cell values based on row no and store into variable?
I am in confusion. Anybody give me good suggestion for import data and give good examples on this
or modify my code, please.