导入Excel文件的DataGridView在C＃中，一些缺少列

问题描述：

我想导入Excel中的DataGridView并保存到数据库中，但所有的第一我得到在列的一些空白，尽管数据它有数据。
数据显示在Excel文件中，如图所示，我想将这个数据导入到我的DataGridView并将其保存到我的数据库名称Records.SDF。

private void importFromExcelToolStripMenuItem_Click(object sender, EventArgs e) 
    { 

     openFileDialog1.ShowDialog(); 

     try 
     { 
      string filePath = openFileDialog1.FileName; 
      string extension = Path.GetExtension(filePath); 
      string conStr; 

      conStr = string.Empty; 
      switch (extension) 
      { 

       case ".xls": //Excel 97-03 

        string Excel03ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0 XML;HDR=YES;';"; 
        conStr = Excel03ConString; //string.Format(Excel03ConString, filePath); 
        break; 

       case ".xlsx": //Excel 07 
        string Excel07ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + 
          filePath + 
          ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';"; 
        conStr = Excel07ConString; 
        break; 
      } 


      String name = "Sheet1"; 

       OleDbConnection con = new OleDbConnection(conStr); 
       OleDbCommand oconn = new OleDbCommand("Select * From [" + name + "$]", con); 
       con.Open(); 

       OleDbDataAdapter sda = new OleDbDataAdapter(oconn); 
       System.Data.DataTable data = new System.Data.DataTable(); 

       sda.Fill(data); 
       RecordsDataGridView.DataSource = data; 

      DialogResult result = MessageBox.Show("Are you sure you want to Save the Recreations?", "Save Format", 
      MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2); 

      if (result == DialogResult.Yes) { SaveData(); } 

     } 
     catch (Exception ex) { MessageBox.Show(ex.Message.ToString(), "Exception Occured"); } 
    } 

    public void SaveData() 
    { 
     // Save the data. 

     SqlCeConnection conn = 
       new SqlCeConnection(
        @"Data Source=|DataDirectory|\Records.sdf;Persist Security Info=False"); 

     SqlCeCommand com; 
     string str; 
     conn.Open(); 
     for (int index = 0; index < RecordsDataGridView.Rows.Count - 1; index++) 
     { 
      str = @"Insert Into ChequeRecords(ID,BankName,Date,AccountNo, Chequebook, ChequeNo, Payee, Amount, Remarks) Values(" + RecordsDataGridView.Rows[index].Cells[0].Value.ToString() + ", '" + RecordsDataGridView.Rows[index].Cells[1].Value.ToString() + "'," + RecordsDataGridView.Rows[index].Cells[2].Value.ToString() + "," + RecordsDataGridView.Rows[index].Cells[3].Value.ToString() + "," + RecordsDataGridView.Rows[index].Cells[4].Value.ToString() + "," + RecordsDataGridView.Rows[index].Cells[5].Value.ToString() + "," + RecordsDataGridView.Rows[index].Cells[6].Value.ToString() + "," + RecordsDataGridView.Rows[index].Cells[7].Value.ToString() + "," + RecordsDataGridView.Rows[index].Cells[8].Value.ToString() + ")"; 
      com = new SqlCeCommand(str, conn); 
      com.ExecuteNonQuery(); 
     } 
     conn.Close(); 


    } 

}

不知道为什么我得到的第2，第4和第6列空白数据。

我的表列没有空间，但做这件事情？

我首先要做的就是对其进行调试和DataTable'已经充满'后检查data'的'值。 –

数据填充完成后会出现断点？ – Patrick

如果我们可以通过SO网站连接到进程，那么我们会，但不幸的是，你将不得不调试它。 –

答

试试吧。

using System; 
using System.Drawing; 
using System.Windows.Forms; 
using Excel = Microsoft.Office.Interop.Excel; 

namespace WindowsApplication1 
{ 
    public partial class Form1 : Form 
    { 
     public Form1() 
     { 
      InitializeComponent(); 
     } 

     private void button1_Click(object sender, EventArgs e) 
     { 
      try 
      { 
       System.Data.OleDb.OleDbConnection MyConnection ; 
       System.Data.DataSet DtSet ; 
       System.Data.OleDb.OleDbDataAdapter MyCommand ; 
       MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\csharp.net-informations.xls';Extended Properties=Excel 8.0;"); 
       MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection); 
       MyCommand.TableMappings.Add("Table", "TestTable"); 
       DtSet = new System.Data.DataSet(); 
       MyCommand.Fill(DtSet); 
       dataGridView1.DataSource = DtSet.Tables[0]; 
       MyConnection.Close(); 
      } 
      catch (Exception ex) 
      { 
       MessageBox.Show (ex.ToString()); 
      } 
     } 
    } 
}

如果这样不起作用，那么Excel中肯定会有东西丢掉它。逐行通过此代码示例（F11）并查看它失败的位置。

http://csharp.net-informations.com/excel/csharp-excel-oledb.htm

导入Excel文件的DataGridView在C＃中，一些缺少列

相关推荐