如何在数据gridview中显示excel文件 [英] How to display the excel file in data gridview

问题描述

您好b $ b

我想使用comboxbox根据选定的工作表在datagridview中显示Excel工作表。

下面是代码，我是不知道为什么它不工作任何人都可以帮助我...

提前致谢。

  public   partial   class  Form1：Form 
 {
  public  Form1（）
 {
 InitializeComponent（）; 
 
} 
  public   string  [] GetExcelSheetNames（ string  excelfilename）
 {
 OleDbConnection con =  null ; 
 DataTable dt =  null ; 
 
  string  connStr =   Provider = Microsoft.ACE.OLEDB.12.0; Data Source = + excelfilename +  ;扩展属性= Excel 12.0;; 
 con =  new  OleDbConnection（connStr）; 
 con.Open（）; 
 dt = con.GetOleDbSchemaTable（OleDbSchemaGuid.Tables， null ）; 
  if （dt ==  null ）
 {
  return   null ; 
} 
  string  [] excelSheetNames =  new  字符串 [dt.Rows.Count]; 
  int  i =  0 ; 
 
  foreach （DataRow row  in  dt.Rows）
 { 
 excelSheetNames [i] = row [  TABLE_NAME]。ToString（）; 
 i ++; 
} 
 con.Close（）; 
  return  excelSheetNames; 
} 
 
 
 私有  void  button1_Click（< span class =code-keyword> object  sender，EventArgs e）
 {
 OpenFileDialog openDialog =  new  OpenFileDialog（）; 
 openDialog.Title =  ; 
 openDialog.InitialDirectory =  @  C：\\; 
 openDialog.Filter =   ExcelSheet（*。xsls）| * .xsls |所有文件（*。* 。）| *。*; 
 openDialog.FilterIndex =  1 ; 
 openDialog.RestoreDirectory =  true ; 
  if （openDialog.ShowDialog（）== DialogResult.OK）
 {
  if （openDialog.FileName！=  ）
 {
 textBox1.Text = openDialog.FileName; 
 
 comboBox1.DataSource = GetExcelSheetNames（openDialog.FileName）; 
 
} 
} 
  else  
 {
 MessageBox.Show（ 选择了Excel工作表路径..，  Information，MessageBoxButtons.OK，MessageBoxIcon.Information）; 
} 
 
} 
 
  private   void  comboBox1_SelectedIndexChanged_1（ object  sender，EventArgs e）
 {
 OleDbConnection con =  null ; 
 字符串 conStr =   Provider = Microsoft。 Jet.OLEDB..0; +  数据源= + textBox1.Text +  ;扩展属性= Excel 8.0;; 
 con =  new  OleDbConnection（conStr）; 
 OleDbCommand oconn =  new  OleDbCommand（ 选择*来自[ + comboBox1.SelectedValue +  ]，con）; 
 OleDbDataAdapter da =  new  OleDbDataAdapter（oconn）; 
 
 
 DataTable data =  new  DataTable（）; 
 da.Fill（data）; 
 dataGridView1.DataSource = data; 
 
} 
} 
} 

解决方案

试试这些





string excelConnectionString = @Provider = Microsoft.ACE.OLEDB.12.0; Data Source =+ textBox1.Text +; Extended Properties ='Excel 12.0 xml; HDR = YES;';



//创建与Excel工作簿的连接

// OleDbConnection connection = new OleDbConnection（） ;

使用（OleDbConnection连接=新的OleDbConnection（excelConnectionString））

//connection.Open（）;

{

OleDbCommand command = new OleDbCommand（select * from [Sheet1

，connection）;

OleDbDataAdapter adpt = new OleDbDataAdapter（command）;

DataTable dt = new DataTable（）;

adpt.Fill（dt）;

Hi
I want to display the excel sheet in datagridview based on the selected sheet using comboxbox.
Below is the code for that and i am not sure why it is not working can anyone please help me in that..
Thanks in advance.

public partial class Form1 : Form
    {
       public Form1()
        {
            InitializeComponent();
           
        }
        public string[] GetExcelSheetNames(string excelfilename)
        {
            OleDbConnection con = null;
            DataTable dt = null;

            string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelfilename + ";Extended Properties=Excel 12.0;";
con = new OleDbConnection(connStr);
                 con.Open();
                 dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dt == null)
            {
                return null;
            }
            string[] excelSheetNames = new String[dt.Rows.Count];
            int i = 0;

            foreach (DataRow row in dt.Rows)
            {
                excelSheetNames[i] = row["TABLE_NAME"].ToString();
                i++;
            }
            con.Close();
            return excelSheetNames;
        }
  

        private void button1_Click(object sender, EventArgs e)
        {
                OpenFileDialog openDialog = new OpenFileDialog();
                openDialog.Title = "";
                openDialog.InitialDirectory = @"C:\\";
                openDialog.Filter = "ExcelSheet(*.xsls)|*.xsls|All Files(*.*)|*.*";
                openDialog.FilterIndex = 1;
                openDialog.RestoreDirectory = true;
                if (openDialog.ShowDialog() == DialogResult.OK)
                {
                    if (openDialog.FileName != "")
                    {
                        textBox1.Text = openDialog.FileName;
                       
                        comboBox1.DataSource = GetExcelSheetNames(openDialog.FileName);
     
                    }
                }
                else
                {
                    MessageBox.Show("chose Excel sheet path..", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                        
        }

      private void comboBox1_SelectedIndexChanged_1(object sender, EventArgs e)
        {
            OleDbConnection con = null;
            String conStr = "Provider=Microsoft.Jet.OLEDB..0;" + "Data Source=" + textBox1.Text + ";Extended Properties=Excel 8.0;";
            con = new OleDbConnection(conStr);
            OleDbCommand oconn = new OleDbCommand("Select * From [" + comboBox1.SelectedValue + "]", con);
            OleDbDataAdapter da = new OleDbDataAdapter(oconn);


            DataTable data = new DataTable();
            da.Fill(data);
            dataGridView1.DataSource = data;
            
        }              
    }
}

解决方案

try these


string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + textBox1.Text + ";Extended Properties='Excel 12.0 xml;HDR=YES;'";

// Create Connection to Excel Workbook
//OleDbConnection connection =new OleDbConnection();
using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
//connection.Open();
{
OleDbCommand command = new OleDbCommand("select * from [Sheet1

", connection);
OleDbDataAdapter adpt = new OleDbDataAdapter(command);
DataTable dt = new DataTable();
adpt.Fill(dt);

这篇关于如何在数据gridview中显示excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！