//点击按钮,获得excel数据放到一个dataset中
private void button1_Click(object sender, EventArgs e)
{
DataSet ds;
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
textBox1.Text = openFileDialog1.FileName;
ds = ImportExcel(this.openFileDialog1.FileName);//获得Excel
dataGridView1.DataSource = ds.Tables[0];
DataTable dataTable = new DataTable();
dataTable = ds.Tables[0];
cell = 0;
rowNum = 0;
cell = dataTable.Columns.Count;
rowNum = dataTable.Rows.Count;
}
else
{
return;
}
}
//读取excel中sheet1中的数据
public DataSet ImportExcel (string file)
{
FileInfo fileInfo = new FileInfo(file);
if (!fileInfo.Exists)
return null;
string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
OleDbConnection objConn = new OleDbConnection(strConn);
DataSet dsExcel = new DataSet();
try
{
objConn.Open();
string strSql = "select * from [Sheet1$]";
OleDbDataAdapter odbcExcelDataAdapter = new OleDbDataAdapter(strSql, objConn);
odbcExcelDataAdapter.Fill(dsExcel);
return dsExcel;
}
catch (Exception ex)
{
throw ex;
}
}