我要用C#导入一个带有固定表头excel表格,我怎样才能从表的第6行开始查询数据,excel表如图: 这是我导入excel和查询excel的方法:导入方法: private void button3_Click_1(object sender, EventArgs e)
{
//测试,将excel中的sheet1导入到sqlserver中 System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();
if (fd.ShowDialog() == DialogResult.OK)
{
//dataSet.Tables.Add(excelOperate.excelDatasource(fd.FileName, "Sheet1").Tables[0]); //dataSet.Tables.Add(excelOperate.selectSheetName(fd.FileName));
ds = excelOperate.excelDatasource(fd.FileName, "Sheet1");
DataGridView1.DataSource = ds.Tables[0];
dataTable1 = ds.Tables[0]; }
}
查询方法:
public System.Data.DataSet excelDatasource(string path, string sheetName)
{
Common cm = new Common();
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=NO\"";//声明一个连接字符串
ds = new System.Data.DataSet();//实例化dataset
//sheetName = "09P317";
try
{
string str = "select * from [" + sheetName + "$] where F1=3";//查询工作表中的数据
//where id>(select top 5 id from [" + sheetName + "$])
oldAdapter = new System.Data.OleDb.OleDbDataAdapter(str,strCon);//把查询出的数据放到oldadapter中
oldAdapter.Fill(ds);//填充dataset
System.Windows.Forms.MessageBox.Show(@"K.O!!", "Excel Show Message");
return ds;
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show("打开Excel表格失败");
System.Windows.Forms.MessageBox.Show(e.ToString());
return null;
}
}
{
//测试,将excel中的sheet1导入到sqlserver中 System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();
if (fd.ShowDialog() == DialogResult.OK)
{
//dataSet.Tables.Add(excelOperate.excelDatasource(fd.FileName, "Sheet1").Tables[0]); //dataSet.Tables.Add(excelOperate.selectSheetName(fd.FileName));
ds = excelOperate.excelDatasource(fd.FileName, "Sheet1");
DataGridView1.DataSource = ds.Tables[0];
dataTable1 = ds.Tables[0]; }
}
查询方法:
public System.Data.DataSet excelDatasource(string path, string sheetName)
{
Common cm = new Common();
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=NO\"";//声明一个连接字符串
ds = new System.Data.DataSet();//实例化dataset
//sheetName = "09P317";
try
{
string str = "select * from [" + sheetName + "$] where F1=3";//查询工作表中的数据
//where id>(select top 5 id from [" + sheetName + "$])
oldAdapter = new System.Data.OleDb.OleDbDataAdapter(str,strCon);//把查询出的数据放到oldadapter中
oldAdapter.Fill(ds);//填充dataset
System.Windows.Forms.MessageBox.Show(@"K.O!!", "Excel Show Message");
return ds;
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show("打开Excel表格失败");
System.Windows.Forms.MessageBox.Show(e.ToString());
return null;
}
}
ds.update更新数据
string str = "select * from [" + sheetName + "$] where F1=3";//查询工作表中的数据//这句的sql改成
string str = "select * from [" + sheetName + "$A6:N100] where F1=3";