Importing Excel File to DataGridView in C#, some columns are missing

Question

**

• I want to import Excel to DataGridView and Save to Database but first of all I'm getting some blank data in columns despite it has data.

• Data is presented in Excel file as shown, I want to import this data to my DataGridView and Save it to my database name 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();


    }

}

Not sure why I'm getting blank data in 2nd, 4th and 6th column.

My Table column does not have space but does this matter?

Answer 1

Try it like this.

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());
            }
        }
   }
}

If that doesn't work there must be something in Excel that is throwing it off. Step through this code sample line by line (F11) and see where it is failing.

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