EXCEL有10行10列,导入后, DataGridView显示10行20列.会多出10列..意思就是EXCEL有M行N列.DataGridView就显示M行2N列.
private void button1_Click(object sender, EventArgs e)//导入Button
{
//dataGridView1.Columns.Clear();
//for (int i = 0; i < 27; i++)
// dataGridView1.Columns[i].SortMode = DataGridViewColumnSortMode.NotSortable; OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = "Excel文件";
ofd.FileName = "";
ofd.InitialDirectory = Environment.GetFolderPath(Environment .SpecialFolder .MyDocuments );
ofd.Filter = "Excel文件(*.xls)|*.xls";
ofd.ValidateNames = true;
ofd.CheckFileExists = true;
ofd.CheckPathExists = true; string strName = string.Empty;
if (ofd.ShowDialog() == DialogResult.OK)
{
strName = ofd.FileName;
} if (strName == "")
{
MessageBox.Show("没有选择Excel文件!无法进行数据导入");
return;
}
ExcelToDataGridView(strName ,this.dataGridView1);
} public void ExcelToDataGridView(string filePath,DataGridView dgv)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + filePath + ";Extended Properties ='Excel 8.0;HDR=YES;IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn );
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [sheet4$]";
myCommand = new OleDbDataAdapter(strExcel ,strConn );
ds = new DataSet();
//myCommand.Fill(ds,"[sheet4$]");
myCommand.Fill(ds, "table1"); 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;
tb.Columns.Add(dc);
}
} 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 );
}
dgv.DataSource = tb;
}
到底是什么原因造成上述问题..请教高人讲解一下...
private void button1_Click(object sender, EventArgs e)//导入Button
{
//dataGridView1.Columns.Clear();
//for (int i = 0; i < 27; i++)
// dataGridView1.Columns[i].SortMode = DataGridViewColumnSortMode.NotSortable; OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = "Excel文件";
ofd.FileName = "";
ofd.InitialDirectory = Environment.GetFolderPath(Environment .SpecialFolder .MyDocuments );
ofd.Filter = "Excel文件(*.xls)|*.xls";
ofd.ValidateNames = true;
ofd.CheckFileExists = true;
ofd.CheckPathExists = true; string strName = string.Empty;
if (ofd.ShowDialog() == DialogResult.OK)
{
strName = ofd.FileName;
} if (strName == "")
{
MessageBox.Show("没有选择Excel文件!无法进行数据导入");
return;
}
ExcelToDataGridView(strName ,this.dataGridView1);
} public void ExcelToDataGridView(string filePath,DataGridView dgv)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + filePath + ";Extended Properties ='Excel 8.0;HDR=YES;IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn );
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [sheet4$]";
myCommand = new OleDbDataAdapter(strExcel ,strConn );
ds = new DataSet();
//myCommand.Fill(ds,"[sheet4$]");
myCommand.Fill(ds, "table1"); 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;
tb.Columns.Add(dc);
}
} 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 );
}
dgv.DataSource = tb;
}
到底是什么原因造成上述问题..请教高人讲解一下...
{
for(int j=0;。)
{ }
}
是不是这句的条件表示式不对啊..
有没有高人来看看到底是哪里的代码有问题?应该怎么改啊...
{
//根据路径打开一个Excel文件并将数据填充到DataSet中
string strConn = "Provider=Microsoft.ACE.OleDb.12.0;Data Source = " + filePath + ";Extended Properties='Excel 12.0'";//HDR=YES 有两个值:YES/NO,表示第一行是否字段名,默认是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
DataTable tb = ds.Tables[0];
foreach (DataRow row1 in tb.Rows)
{
string Id = row1["id"].ToString();
string Name = row1[1].ToString();
string Age = row1[2].ToString();
//新建一行
DataGridViewRow row = new DataGridViewRow();
int index = dataGridView1.Rows.Add(row);
//插入
row = dataGridView1.Rows[index];
row.Cells["id"].Value = Id;//id列添加数据
row.Cells["name"].Value = Name;
row.Cells["age"].Value = Age;
}
}
可以这样,一行一行往datagridview中添加,不过字段多的话很麻烦
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + filePath + ";Extended Properties ='Excel 8.0;HDR=YES;IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn );
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [sheet4$]";
myCommand = new OleDbDataAdapter(strExcel ,strConn );
ds = new DataSet();
//myCommand.Fill(ds,"[sheet4$]");
myCommand.Fill(ds, "table1"); DataTable tb = ds.Tables[0];
dgv.Columns.Clear();//先清空下就可以了..
dgv.DataSource = tb;
}先清空下datagridview,在绑定就可以了,试试吧
根据你的下面这个处理,
foreach (DataGridViewColumn dgvc in dgv.Columns)
应该你的这个datagridview已经定义好列了,这样,你在执行最后这句
dgv.DataSource = tb;
也就是在绑定的时候,又会对datagridview加一次列,这样你就多出了一倍的列来,你可以这样
试试是不是就对的,也就是在绑定这句前面清除一次列:
dgv.Columns.Clear();
dgv.DataSource = tb;
这样应该就不会多出列来了,如果这样会把你预先定义的别的列也清除了,那只能是自己写个绑定
方法,手动添加列数据,应该就能解决了。
结贴了..谢谢啊!!!!