public static bool getExcel(DataGridView dgv,System.Windows.Forms.Label lbl)
{
bool fflag = true;
OpenFileDialog open = new OpenFileDialog();
open.Title = "请选择要导入的Excel文件";
open.Filter = "Excel文件(*.xls)|*.xls";
if(open.ShowDialog()==DialogResult.OK)
{
string fileName = open.FileName;
//根据路径打开一个Excel文件并将数据填充到DataSet中
string strconn = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=" + fileName+";Extended Properties=\"Excel 8.0;HDR=YEs;IMEX=1\"" ;
//导入时包含Excel中的第一行数据,并且将数字和字符混合的单元格视为文本进行导入
OleDbConnection conn = new OleDbConnection(strconn);
conn.Open();
string strExcel = "select * from [sheet1$]";
OleDbDataAdapter comm = new OleDbDataAdapter(strExcel, conn);
DataSet ds =new DataSet();
try
{
comm.Fill(ds, "table1");
}
catch
{
MessageBox.Show("错误信息:009", "错误");
}
int c = ds.Tables[0].Rows.Count;
string s = ds.Tables[0].Rows[0][0].ToString();
// 根据DataGridView的列构造一个新的Datatable
System.Data.DataTable dt = new System.Data.DataTable();
foreach(DataGridViewColumn dgvc in dgv.Columns)
{
if(dgvc.Visible)
{
DataColumn dc = new DataColumn();
dc.ColumnName = dgvc.DataPropertyName;
dt.Columns.Add(dc);
}
}
int r = dt.Rows.Count;
int f = dt.Columns.Count;
//根据Excel的行逐一对上面构造的DataTable的列进行赋值
foreach(DataRow excelrow in ds.Tables[0].Rows)//遍历Excel中的行
{
int i = 0;
DataRow dr = dt.NewRow();
foreach(DataColumn dc in dt.Columns)//DataGridView中的列
{
dr[dc] = excelrow[i];
i++;
}
dt.Rows.Add(dr);
}
//导入到DataGridView
dgv.DataSource = dt;
lbl.Text = dgv.RowCount.ToString();
}
else
{
fflag = false;
}
return fflag;
}
}
将Excel导入DataGridView
最新推荐文章于 2019-04-27 23:09:16 发布