如何根据特定的单元格值从excel获取记录 [英] How to get the records from excel based on particular cell value
问题描述
大家好
我目前正在从事Windows项目.我想使用c#将excel数据导入数据库.
我的SpreadSheet是
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 |
--------------------------------------------------
现在我想读取基于单元格value=12345的全部excel工作表(这里前两行为空，我的代码读取所有记录都忽略了空白)并将所有记录存储到数据库中.这是我的任务.我已经实现了下面的代码
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);
//dgvExcelData.DataSource = dtExcel;
//dgvExcelData.ColumnHeadersVisible=false;
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();
//Int32 MerchNo = Convert.ToInt32(Mno);
}
break;
}
if(Mno!="")// Mno contains the exact MerchantNo.
{
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();
}
}
}
}
}
是的，我具有将Excel导入数据表并将数据表导入数据库的这种类型的知识.
我对这张Excel工作表的要求是
1.忽略空行.
2.不读取前两行并视为空行.
3.根据单元格值读取记录(12345)
我的查询是
1.我想一次读取总记录或基于行号读取单元格值并将其存储到变量中?
我很困惑.有人给我有关导入数据的好建议，并为此举了很好的例子
或修改我的代码.
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.
推荐答案
"; OleDbDataAdapter data = 新 OleDbDataAdapter(query，con); data.Fill(dtExcel); // dgvExcelData.DataSource = dtExcel; // dgvExcelData.ColumnHeadersVisible = false; 字符串 DestConstr = @" ; SqlConnection连接= 新 SqlConnection(DestConstr); connection.Open(); 字符串 Mno = " ; foreach (DataRow rowExcel in dtExcel.Rows中) { foreach (DataColumn colExcel in dtExcel.Columns) { Mno = rowExcel [colExcel] .ToString().Trim(); 如果(否！= " ) { 字符串 Mno1 = Mno.Substring( 16 ， 10 ); Mno = Mno1.ToString(); // Int32 MerchNo = Convert.ToInt32(Mno); } break ; } 如果(否！= " ) // Mno包含确切的商户编号. { for ( int i = 0 ; i< ; dtexcel.rows.count; i ++)> { 如果(dtExcel.Rows.Contains(" )) { 字符串 str = dtExcel.Rows [i] [ 0 ].ToString().Trim(); 字符串 str1 = dtExcel.Rows [i] [ 1 ].ToString().Trim(); } } } } }
"; OleDbDataAdapter data=new OleDbDataAdapter(query,con); data.Fill(dtExcel); //dgvExcelData.DataSource = dtExcel; //dgvExcelData.ColumnHeadersVisible=false; 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(); //Int32 MerchNo = Convert.ToInt32(Mno); } break; } if(Mno!="")// Mno contains the exact MerchantNo. { 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(); } } } } }
是的，我具有将Excel导入数据表并将数据表导入数据库的这种类型的知识.
我对这张Excel工作表的要求是
1.忽略空行.
2.不读取前两行并视为空行.
3.根据单元格值读取记录(12345)
我的查询是
1.我想一次读取总记录或基于行号读取单元格值并将其存储到变量中?
我很困惑.有人给我有关导入数据的好建议，并为此举了很好的例子
或修改我的代码.
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.
私人无效btn2Excel_Click(对象发送者，EventArgs e)
{
字符串strFileName =";
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter =文件(* .xls)| * .xls |(*.xlsm)| * .xlsm";
openFileDialog.DefaultExt =".xls";
如果(openFileDialog.ShowDialog()== DialogResult.OK)
{
strFileName = openFileDialog.FileName;
字符串connectionString = @"Provider = Microsoft.ACE.OLEDB.12.0;数据源=" + strFileName + @;扩展属性=""Excel 12.0; HDR = YES;"";
OleDbConnection objConn =新的OleDbConnection(connectionString);
//打开与数据库的连接.
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [MalePopulation
private void btn2Excel_Click(object sender, EventArgs e)
{
string strFileName = "";
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "Files (*.xls)|*.xls|(*.xlsm)|*.xlsm";
openFileDialog.DefaultExt = ".xls";
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
strFileName = openFileDialog.FileName;
string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + strFileName + @";Extended Properties=""Excel 12.0;HDR=YES;""";
OleDbConnection objConn = new OleDbConnection(connectionString);
// Open connection with the database.
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [MalePopulation
"，objConn);
OleDbDataAdapter objAdapter1 =新的OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
objAdapter1.Fill(objDataset1);
DataSet dset2 = new DataSet();
OleDbCommand objCmdSelect1 = new OleDbCommand("SELECT * FROM [FemalePopulation
", objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
objAdapter1.Fill(objDataset1);
DataSet dset2 = new DataSet();
OleDbCommand objCmdSelect1 = new OleDbCommand("SELECT * FROM [FemalePopulation
这篇关于如何根据特定的单元格值从excel获取记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
