1个回答
展开全部
public void ExcelToDa(string filePath, DataGridView dgv, out DataSet data)
{
//根据路径打开一个Excel文件并将数据填充到DataSet中
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
//HDR=YES 有两个值:YES/NO,表示第一行是否字段名,默认是YES,第一行是字段名
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
data = ds;
//根据DataGridView的列构造一个新的DataTable
DataTable tb = new DataTable();
foreach (DataGridViewColumn dgvc in dgv.Columns)
{
if (dgvc.Visible && dgvc.CellType != typeof(DataGridViewCheckBoxCell))
{
DataColumn dc = new DataColumn();
dc.ColumnName = dgvc.DataPropertyName;
//dc.DataType = dgvc.ValueType;//若需要限制导入时的数据类型则取消注释,前提是DataGridView必须先绑定一个数据源那怕是空的DataTable
tb.Columns.Add(dc);
}
}
//根据Excel的行逐一对上面构造的DataTable的列进行赋值
foreach (DataRow excelRow in ds.Tables[0].Rows)
{
int i = 0;
DataRow dr = tb.NewRow();
foreach (DataColumn dc in tb.Columns)
{
dr[dc] = excelRow[i];
i++;
}
tb.Rows.Add(dr);
}
//在DataGridView中显示导入的数据
dgv.DataSource = tb;
}
{
//根据路径打开一个Excel文件并将数据填充到DataSet中
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
//HDR=YES 有两个值:YES/NO,表示第一行是否字段名,默认是YES,第一行是字段名
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
data = ds;
//根据DataGridView的列构造一个新的DataTable
DataTable tb = new DataTable();
foreach (DataGridViewColumn dgvc in dgv.Columns)
{
if (dgvc.Visible && dgvc.CellType != typeof(DataGridViewCheckBoxCell))
{
DataColumn dc = new DataColumn();
dc.ColumnName = dgvc.DataPropertyName;
//dc.DataType = dgvc.ValueType;//若需要限制导入时的数据类型则取消注释,前提是DataGridView必须先绑定一个数据源那怕是空的DataTable
tb.Columns.Add(dc);
}
}
//根据Excel的行逐一对上面构造的DataTable的列进行赋值
foreach (DataRow excelRow in ds.Tables[0].Rows)
{
int i = 0;
DataRow dr = tb.NewRow();
foreach (DataColumn dc in tb.Columns)
{
dr[dc] = excelRow[i];
i++;
}
tb.Rows.Add(dr);
}
//在DataGridView中显示导入的数据
dgv.DataSource = tb;
}
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询