2

Not sure what to call those exactly, string-ified numbers? Anyways, the cells with the green triangles on them that you would write like so: '3309

Here is my working code...

Reading an excel file's schema, whose sheet is selected later on from a combobox (Here I utilize a backgroundworker thread):

try
{
   OleDbcon =
        new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + IO_Name +
                             ";Extended Properties='Excel 12.0 Xml; HDR = Yes; IMEX = 1'");
    OleDbcon.Open();
    SD.DataTable dt = OleDbcon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    OleDbcon.Close();
    MaxSchema = dt.Rows.Count;
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        String sheetName = dt.Rows[i]["TABLE_NAME"].ToString();
        sheetName = sheetName.Substring(0, sheetName.Length - 1);
        cmbSheets.Invoke((MethodInvoker)delegate {
            cmbSheets.Items.Add(sheetName);
        });
        backWorker2.ReportProgress(i);
    }
}
catch (Exception ex)
{
    MessageBox.Show("Write Excel: " + ex.Message);
}
finally
{
    GC.Collect();
    GC.WaitForPendingFinalizers();
    backWorker2.ReportProgress(100);
}

Lastly, how I bring my data to a datagridview:

private void cmbSheets_SelectedIndexChanged(object sender, EventArgs e)
{
        OleDbDataAdapter oledbDa = new OleDbDataAdapter("Select * from [" + cmbSheets.Text + "$]", OleDbcon);
    SD.DataTable dt = new SD.DataTable();
    oledbDa.Fill(dt);
    DGVExcel.DataSource = dt;
    lblItem.Text = DGVExcel.RowCount.ToString();
}

However, I noticed that data prefixed with an apostrophe, to make numbers into text, aren't being read at all, and are treated as if they were hidden. How do I make it so that my method can read the string-ified cells?

I realize that this might have to do with the fact that I read the schema of the excel file directly, it might naturally be avoiding certain things like filters or excel specific stuff, including my string-ified cells. My current method of reading it is quite efficient as far as I am concerned, so it would be great if the suggestion merely added to the current method.

Edit my current connection string's extended properties:

";Extended Properties= 'Excel 12.0 Xml;HDR=NO; IMEX = 1;TypeGuessRows=0;ImportMixedTypes=Text';");

Koopakiller
  • 2,838
  • 3
  • 32
  • 47
Aroueterra
  • 336
  • 3
  • 18
  • 1
    related: http://stackoverflow.com/a/5721521/1132334 does the column with the ' values contain data in all rows, also the first? – Cee McSharpface Apr 06 '17 at 07:32
  • 1
    The first row does contain my headers, which are plain text without the '. The cells with the ' are below the header row and are present in no particular order, littering my excel file's cells at random, so I'm not sure if I can set an individual pattern here. @dlatikay – Aroueterra Apr 06 '17 at 07:41
  • 1
    @dlatikay, so I'm gonna try cutting off the header, or maybe adding the extended properties, import mixed types, and see what happens. Edit: it didn't work. – Aroueterra Apr 06 '17 at 07:47
  • 1
    hm. what I would have suspected is this fuzzy IMEX algorithm, that guesses data types from the contents of the first few lines. does it happen also if you manually fill data into one of the "stringified" columns in row #2, assuming HDR=Yes? do you use xlsx, or xls files? – Cee McSharpface Apr 06 '17 at 08:54
  • 1
    [this one](http://stackoverflow.com/q/7423742/1132334) is also similar, but given the analysis you did, I'm not convinced its a duplicate. – Cee McSharpface Apr 06 '17 at 08:57
  • My Extended properties now looks like this: `";Extended Properties= 'Excel 12.0 Xml;HDR=NO; IMEX = 1;TypeGuessRows=0;ImportMixedTypes=Text';");` adding an extra semi-colon made the changes visible, though, no change on the apostrophe'd cells, they're still being read blank. – Aroueterra Apr 06 '17 at 09:07

0 Answers0