首先,我已经获取到一张Excel表;例如(D:/Excel1.xls)然后,我需要遍历这张表的所有工作表最后,遍历以后没有此工作表名的,就自动创建一个还有,并将指定的一张工作表明内容复制与新创建的工作表中还有,如何遍历指定一张工作表的列的每一行,然后获取每一行的值。
真的很急啊~~各位前辈,请出手相助,给个范例也行!跪求了!在线真心等!
真的很急啊~~各位前辈,请出手相助,给个范例也行!跪求了!在线真心等!
调试欢乐多
{
OpenFileDialog openFileDialog1 = new OpenFileDialog();
openFileDialog1.Filter = "Excel文件(*.xls)|*.xls|Excel2007文件(*.xlsx)|*.xlsx";
openFileDialog1.FilterIndex = 1;
openFileDialog1.RestoreDirectory = true;
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
if (openFileDialog1.FileName == "")
{
MessageBox.Show("没有选择Excel文件!无法进行数据导入");
return;
}
EcxelToDataGridView(openFileDialog1.FileName, dataGridView1);
}
} public void EcxelToDataGridView(string filePath, DataGridView dgv)
{
try
{
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + filePath + " ;Extended Properties=Excel 8.0";
System.Data.OleDb.OleDbConnection myConn = new System.Data.OleDb.OleDbConnection(strCon);
myConn.Open();
DataTable sheetNames = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
ArrayList al = new ArrayList();
foreach (DataRow dr in sheetNames.Rows)
{
al.Add(dr[2]);
}
string bm = al[0].ToString();
string strCom = " SELECT * FROM " + "[" + bm + "]";
System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, myConn);
System.Data.DataTable dt = new System.Data.DataTable();
myCommand.Fill(dt);
this.dataGridView1.DataSource = dt;
myConn.Close();
}
catch
{
try
{
string strCon = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + @" ;Extended Properties=""Excel 12.0;HDR=YES;""";
System.Data.OleDb.OleDbConnection myConn = new System.Data.OleDb.OleDbConnection(strCon);
myConn.Open();
DataTable sheetNames = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
ArrayList al = new ArrayList();
foreach (DataRow dr in sheetNames.Rows)
{
al.Add(dr[2]);
}
string bm = al[0].ToString();
string strCom = " SELECT * FROM " + "[" + bm + "]";
System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, myConn);
System.Data.DataTable dt = new System.Data.DataTable();
myCommand.Fill(dt);
this.dataGridView1.DataSource = dt;
myConn.Close();
}
catch
{
MessageBox.Show("文件可能已被其他应用程序占用或者文件格式不兼容");
}
}
int hs = 0;
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
DataGridViewRowHeaderCell rowHeaderCell = dataGridView1.Rows[i].HeaderCell;
hs++;
rowHeaderCell.Value = hs.ToString();
}
dataGridView1.Parent = this;
dataGridView1.Visible = false;
MessageBox.Show("导入成功");
button2.Enabled = true;
}
protected DataTable CallExcel(string filepath)
{
try
{
OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0");
con.Open();
string sql = "select * from [Sheet1$]";//选择第一个数据SHEET
OleDbDataAdapter adapter = new OleDbDataAdapter(sql, con);
DataTable dt = new DataTable();
adapter.Fill(dt);
con.Close();
con.Dispose();
return dt;
}
catch (Exception ex)
{
MessageBox.Show("打开Excel文件时:" + ex.Message, "提示");
return null;
}
}
//遍历
DataTable dt = CallExcel(fName);
if (dt == null) return;
for (int i = 0; i < dt.Rows.Count; i++)
{
string number = dt.Rows[i][2].ToString();
string name = dt.Rows[i][1].ToString();
if (Regex.IsMatch(number, @"^1[358]\d{9}$", RegexOptions.IgnoreCase))
{
InsertToList(number, name);
}
}
OleDbConnection这个要去找下引用的DLL,引用一下
http://download.csdn.net/detail/tongiii/3697976
http://ufo-crackerx.blog.163.com/blog/static/113078778201201542333900/