以前只做过 导入EXCEL 从第一行开始导入,
现在客户的EXCEL模板发生变化,正式数据从第八行才是正式表数据,原来的代码如下 DataTable tb = new DataTable(); public void EcxelToDataGridView(string filePath, DataGridView dgv)
{ // string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;data source=" + filePath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'"; string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=YES\""; OleDbConnection conn = new 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");
//根据DataGridView的列构造一个新的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; conn.Close(); }
现在客户的EXCEL模板发生变化,正式数据从第八行才是正式表数据,原来的代码如下 DataTable tb = new DataTable(); public void EcxelToDataGridView(string filePath, DataGridView dgv)
{ // string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;data source=" + filePath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'"; string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=YES\""; OleDbConnection conn = new 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");
//根据DataGridView的列构造一个新的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; conn.Close(); }
int startRow = 8;
int currentRow = 0;
//根据Excel的行逐一对上面构造的DataTable的列进行赋值
foreach (DataRow excelRow in ds.Tables[0].Rows)
{
if(currentRow < startRow){
currentRow ++;
continue;
}
if(i >= 8 )
{
tb.Rows.Add(dr);
}