1

I have this problem on displaying Excel cell values on a c# listbox.

I have this Employees.xls file that contains four (4) columns ("Code", "LastName", "FirstName", "MiddleName").

Here's the exact contents:

Code   LastName   FirstName   MiddleName

1      Dela Cruz  Juan        Santos

2      Severino   Miguel      Reyes

Now using an Openfiledialog, I browsed the file and want to display the column headers on a listbox.

This should be the result:

Code

LastName

FirstName

MiddleName

The problem is when I browse the file, the column header "Code" was not displayed.

It displays only:

      <--- (Blank Space)

LastName

FirstName

MiddleName

I noticed that there's a space provided for the first value but the Text "Code" was not displayed.

This is the code that I used to get the cell values:

  private void btn_Browse_Click(object sender, EventArgs e) // Browse Button

     {
        Stream myStream = null;
        OpenFileDialog openFileDialog1 = new OpenFileDialog();

        openFileDialog1.InitialDirectory = "c:\\";
        openFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*";
        openFileDialog1.FilterIndex = 2;
        openFileDialog1.RestoreDirectory = true;
        this.lbl_Path.Text = openFileDialog1.FileName;
        if (openFileDialog1.ShowDialog() == DialogResult.OK)
         {
             try
             {
                 if ((myStream = openFileDialog1.OpenFile()) != null)
                 {
                     using (myStream)
                     {
                         this.lbl_Path.Text = openFileDialog1.FileName;
                         source = openFileDialog1.FileName.Replace(@"\", @"\\");
                         this.lbl_Path.Visible = true;
                         GetExcelSheetNames(source);
                         lst_Fields.Items.Clear();
                         string SheetName = sheet;
                         string query = "Select * From ["+SheetName+"]";
                         DataTable table = conExcel(query);
                         int i = 0;
                         lst_Fields.Items.Add(Convert.ToString(table.Rows[i][0]));
                         lst_Fields.Items.Add(Convert.ToString(table.Rows[i][1]));
                         lst_Fields.Items.Add(Convert.ToString(table.Rows[i][2]));
                         lst_Fields.Items.Add(Convert.ToString(table.Rows[i][3]));
                         lst_Fields.Items.Add(Convert.ToString(table.Rows[i][4]));
                         lst_Fields.Items.Add(Convert.ToString(table.Rows[i][5]));
                         lst_Fields.Items.Add(Convert.ToString(table.Rows[i][6]));
                         lst_Fields.Items.Add(Convert.ToString(table.Rows[i][7]));
                         lst_Fields.Items.Add(Convert.ToString(table.Rows[i][8]));
                         lst_Fields.Items.Add(Convert.ToString(table.Rows[i][9]));
                     }

                 }
             }
             catch (Exception ex)
             {
                 MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
             } 
         }
    }

    private DataTable conExcel(string query) // Connection to Excel
    {
        OleDbConnection conn = new OleDbConnection();
        conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + source + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=-1;\"";
        conn.Open();
        OleDbDataAdapter da = new OleDbDataAdapter(query, conn);
        DataTable table = new DataTable();
        da.Fill(table);
        conn.Close();
        return table;
    }

    private String[] GetExcelSheetNames(string excelFile) //Getting the sheet name
    {
        OleDbConnection objConn = null;
        System.Data.DataTable dt = null;

        try
        {
            // Connection String. Change the excel file to the file you
            // will search.
            String connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + source + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=-1;\"";
            // Create connection object by using the preceding connection string.
            objConn = new OleDbConnection(connString);
            // Open connection with the database.
            objConn.Open();
            // Get the data table containg the schema guid.
            dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            if (dt == null)
            {
                return null;
            }

            String[] excelSheets = new String[dt.Rows.Count];
            int i = 0;

            // Add the sheet name to the string array.
            foreach (DataRow row in dt.Rows)
            {
                excelSheets[i] = row["TABLE_NAME"].ToString();
                i++;
            }

            // Loop through all of the sheets if you want too...
            for (int j = 0; j < excelSheets.Length; j++)
            {
                sheet = excelSheets[0];
            }

            return excelSheets;
        }
        catch (Exception ex)
        {
            return null;
        }
        finally
        {
            // Clean up.
            if (objConn != null)
            {
                objConn.Close();
                objConn.Dispose();
            }
            if (dt != null)
            {
                dt.Dispose();
            }
        }
    }

2 Answers 2

3

Change your connection string in the conExcel method to be expecting headers.

conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + source + ";Extended Properties=Excel 12.0;HDR=YES;";
// OR
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + source + ";Extended Properties=Excel 12.0;HDR=YES;IMEX=1";
//conn.ConnectionString = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + source + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=-1;\"";

Then you can change:

lst_Fields.Items.Add(Convert.ToString(table.Rows[i][0]));
lst_Fields.Items.Add(Convert.ToString(table.Rows[i][1]));
lst_Fields.Items.Add(Convert.ToString(table.Rows[i][2]));
lst_Fields.Items.Add(Convert.ToString(table.Rows[i][3]));
lst_Fields.Items.Add(Convert.ToString(table.Rows[i][4]));
lst_Fields.Items.Add(Convert.ToString(table.Rows[i][5]));
lst_Fields.Items.Add(Convert.ToString(table.Rows[i][6]));
lst_Fields.Items.Add(Convert.ToString(table.Rows[i][7]));
lst_Fields.Items.Add(Convert.ToString(table.Rows[i][8]));
lst_Fields.Items.Add(Convert.ToString(table.Rows[i][9]));

to:

foreach (DataColumn dc in table.Columns)
{
    lst_Fields.Items.Add(dc.ColumnName);
}
Sign up to request clarification or add additional context in comments.

3 Comments

Mark when I tried to Transfer the project to another computer, I get the error "External table is not in the expected format". It works fine on the other computer. Please help..
@JasonX Try the simplified connection string that I just editied into my post. I commented out the old one.
@JasonX Actually try both of the uncommented connection strings. See if one of those fixes the problem.
2

Try changing IMEX=-1 to IMEX=1

If it still doesn't work, then try google about treating excel data as text.

Edit:

for XLS file, Excel 8.0; should be used in Extended Properties

Source: http://www.connectionstrings.com/ace-oledb-12-0/

2 Comments

I've already tried that fix sir but it says "External Table is not in the Expected Format". That's why i put it back to -1. I exactly don't know why to use IMEX=-1 instead of IMEX=1 but that's the only way to have the values displayed on my listbox.
Try using Excel 8.0;. If it's still no good, try set Provider=Microsoft.Jet.OLEDB.4.0;.It seems xls and xlsx use different connection string: stackoverflow.com/a/2242720/2419922

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.