C# excel导入datagridview显示问题

bdsutx 2012-09-26 03:29:59

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;

            

        }

到底是什么原因造成上述问题..请教高人讲解一下...

...全文

501 15 打赏收藏转发到动态举报

写回复

用AI写文章

15 条回复

切换为时间正序

请发表友善的回复…

发表回复

bdsutx 2012-09-27

打赏
举报

谢谢大家的热心回答.特别感谢"fsstolw","kawudi2".就是添加dgv.columns.clear();
结贴了..谢谢啊!!!!

fsstolw 2012-09-26

打赏
举报

楼主这个问题的原因可能是这样的：
根据你的下面这个处理，
foreach (DataGridViewColumn dgvc in dgv.Columns)
应该你的这个datagridview已经定义好列了，这样，你在执行最后这句
dgv.DataSource = tb;
也就是在绑定的时候，又会对datagridview加一次列，这样你就多出了一倍的列来，你可以这样
试试是不是就对的，也就是在绑定这句前面清除一次列：
dgv.Columns.Clear();
dgv.DataSource = tb;
这样应该就不会多出列来了，如果这样会把你预先定义的别的列也清除了，那只能是自己写个绑定
方法，手动添加列数据，应该就能解决了。

kawudi2 2012-09-26

打赏
举报

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 = ds.Tables[0];
dgv.Columns.Clear();//先清空下就可以了..
dgv.DataSource = tb;
}

先清空下datagridview,在绑定就可以了,试试吧

kawudi2 2012-09-26

打赏
举报

public void EcxelToDataGridView(string filePath, DataGridView dgv)
{
//根据路径打开一个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中添加,不过字段多的话很麻烦

bdsutx 2012-09-26