Apply a TableStyle to a QueryTable

Question

I can use a query table:

var sheet = (_excel.ActiveSheet as Excel.Worksheet);
var rng = sheet.Range("A1");

var qt = sheet.QueryTables.Add("ODBC;...", rng, "SELECT * FROM myTable");
qt.Refresh();

and this will import the data correctly (e.g. dates actually display as dates etc...). But when I try and access the ListObject to apply a TableStyle I get an exception.

Now if I add a list object like:

var sheet = (_excel.ActiveSheet as Excel.Worksheet);

var qt = sheet.ListObjects.Add(
    Excel.Enums.XlListObjectSourceType.xlSrcQuery,
    "ODBC;...",
    null,
    Excel.Enums.XlYesNoGuess.xlNo,
    rng,
    "TableStyleMedium9").QueryTable;

qt.CommandType = Excel.Enums.XlCmdType.xlCmdSql;
qt.CommandText = "SELECT * FROM myTable";
qt.Refresh();

The the dates in the query display as decimal numbers and not dates...

I could just format the columns afterwards, but the problem is that I won't actually know the query that is being run as the user types this at runtime, so I would prefer to get Excel to do this.

So essentially, what I want, is to use the first bit of code and apply a TableStyle to it.

Can anyone help?

Answer 1

This doesn't do coloring, but it does organize the data into a datatable, and when you're debugging with breakpoints and mouse over the datatable after its been filled you can see all the data organized into columns. What you can then do with the datatable is bind it to a datagrid view on your win forms element.

I have this DataTable DataTable = new DataTable(); as a global field at the top.

        OleDbConnection conn = new OleDbConnection();

        //This is making a connection to the excel file, don't worry about this I think you did it differently.
        conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + stringFileName + ";" + "Extended Properties=\"Excel 12.0;HDR=Yes;\"";             OleDbCommand cmd = new OleDbCommand
        ("SELECT * FROM [" + sheetFromTo + "]", conn);

        DataSet dataSet1 = new DataSet();
        OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmd);

        try
        {
            conn.Open();//opens connection
                dataSet1.Clear();//empties, incase they refill it later
                dataAdapter.SelectCommand = cmd;//calls the cmd up above
                dataAdapter.Fill(dataSet1);//fills the dataset
                dataGridView1.DataSource = dataSet1.Tables[0];//puts the dataset in the dataGridview
                //important** creates a datatable from the dataset, most of our work with the server is with this datatable
                DataTable dataTable = dataSet1.Tables[0];
                DataTable = dataTable;
        }
        catch (Exception ex)
        {
        }
        finally
        {
            conn.Close();
        }