Question

将excel数据导入到datagridview中

datagridview表头我已经在winfrom中设置好了，然后exce的第一行为了填写数据方便依然写的有表头。
constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";

导入后表头依然有呀，如上图。应该如何设置呀

Answer 1

借助于SQL。代码如下：

using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Text;  
using System.Windows.Forms;  
using System.Diagnostics;  
using System.IO;  
//using Microsoft.Office.Interop.Excel;  
using System.Data.OleDb;  
using System.ComponentModel;  
using System.Data;  
using System.Drawing;  
  
namespace ClothingSales_0._5  
{  
    public class Import  
    {  
        //private string fileName;  
  
        /// <summary>  
        /// 将DataGridView中数据导入到Excel  
        /// </summary>  
        /// <param name="dgv">DataGridView的名称</param>  
        public bool getExcel(DataGridView dgv,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=NO;IMEX=1'";//导入时包含Excel中的第一行数据，并且将数字和字符混合的单元格视为文本进行导入  
                OleDbConnection conn = new OleDbConnection(strConn);  
                conn.Open();  
                string strExcel = "select  * from   [sheet1$]";  
                OleDbDataAdapter comm = new OleDbDataAdapter(strExcel, strConn);  
                DataSet ds = new DataSet();  
                try  
                {  
                    comm.Fill(ds, "table1");  
                }  
                catch  
                {  
                    MessageBox.Show("错误信息：009", "错误");  
                }  
                comm.Fill(ds, "table1");  
  
                //根据DataGridView的列构造一个新的DataTable  
                DataTable dt = new DataTable();  
                DataTable dt2 = new DataTable();  
                foreach (DataGridViewColumn dgvc in dgv.Columns)  
                {  
                    if (dgvc.Visible)  
                    {  
  
                        DataColumn dc = new DataColumn();  
                        dc.ColumnName = dgvc.DataPropertyName;  
                        dt.Columns.Add(dc);  
                        DataColumn dc2 = new DataColumn();  
                        dc2.ColumnName = dgvc.DataPropertyName;  
                        dt2.Columns.Add(dc2);  
  
                        if (dgvc.CellType == typeof(DataGridViewCheckBoxCell))  
                        {  
                            dc2.DataType = Type.GetType("System.Boolean");  
                        }  
                    }  
                }  
  
                //根据Excel的行逐一对上面构造的DataTable的列进行赋值  
                foreach (DataRow excelRow in ds.Tables[0].Rows)  
                {  
                    int i = 0;  
                    DataRow dr = dt.NewRow();  
                    foreach (DataColumn dc in dt.Columns)  
                    {  
                        dr[dc] = excelRow[i];  
                        i++;  
                    }  
                    dt.Rows.Add(dr);  
                }  
  
                //判断Excel的格式是否正确  
                int n = 0;  
                for (int j = 0; j < dt.Columns.Count; j++)  
                {  
                    bool flag = false;  
                    for (int k = n; k < dgv.ColumnCount; k++)  
                    {  
                        if (dgv.Columns[k].Visible)  //隐藏的列  
                        {  
                            if (dgv.Columns[k].HeaderText.Trim().ToString() == dt.Rows[0][j].ToString())  
                            {  
                                if (dgv.Columns[k].CellType == typeof(DataGridViewCheckBoxCell))  
                                {  
                                    //list.Add(j);  
                                    //num++;  
                                }  
                                flag = true;  
                                n = k + 1;  
                                break;  
                            }  
                        }  
                    }  
                    if (flag == false)  
                    {  
                        MessageBox.Show("导入的Excel的格式错误", "提示");  
                        fflag = false;  
                        return fflag;  
                    }  
                }  
  
                //删除多余的行  
                int rowCount = (dt.Rows.Count) / 2;  
  
                for (int i = 0; i <= rowCount; i++)  
                {  
                    dt.Rows.RemoveAt(0);  
                }  
  
                //处理Boolean类型的数据  
                for (int i = 0; i < dt.Rows.Count; i++)  
                {  
                    DataRow dr = dt2.NewRow();  
                    for (int j = 0; j < dt.Columns.Count; j++)  
                    {  
                        try  
                        {  
                            dr[j] = dt.Rows[i][j];  
                        }  
                        catch  
                        {  
                            dr[j] = false;  
                        }  
                    }  
                    dt2.Rows.Add(dr);  
                }  
  
                //导入到dataGridView  
                dgv.DataSource = dt2;  
  
                lbl.Text = dgv.RowCount.ToString();  
            }  
            else  
            {  
                fflag = false;  
            }  
            return fflag;  
        }  
    }  
}

Answer 2

给你个方法 你调用就可以了

public DataTable ExcelToDataTable(string pathName, string sheetName)
{
DataTable tbContainer = new DataTable();
string strConn = string.Empty;
try
{
FileInfo file = new FileInfo(pathName);
if (!file.Exists)
{
throw new Exception("文件不存在");
}
string extension = file.Extension;
switch (extension)
{
case ".xls":
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
break;
case ".xlsx":
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
break;
default:
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
break;
}

OleDbConnection connxls = new OleDbConnection(strConn);
connxls.Open();
DataTable dtExcelSchema = connxls.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
string sheetname = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();//获取表名
connxls.Close();
OleDbDataAdapter oda = new OleDbDataAdapter(string.Format("select * from [{0}]", sheetname), connxls);

DataSet ds = new DataSet();
oda.Fill(tbContainer);
return tbContainer;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
return tbContainer;
}
}

Answer 3

你是要连EXCEL的表头一起导入吗？

追问

表头我已经在程序里面设置好了。只是在excel里面第一行还是表头。我想导入excel数据到datagridview，如果我把程序里面写好的datagridview的表头都删除是个空的datagridview刚好导入成功。现在我在表头我已经在程序里面设置好了。需要怎么设置才能呢