Populate SQL Server data in Excel (2010) spreadsheet

Question

I'm trying to populate the data extracted from SQL Server into Excel 2010. The code below works fine, but the difficulty is that I don't create an Excel spreadsheet programmatically, it is aleady exists and I make a request for data via plugin in Excel written in C#. Even though I set the cursor to A10 cell, Excel starts filling-out the data from the very first cell and overwrites the header (that is already exists). Please help to fix. Code:

OdbcConnection cnn;
                cnn = new OdbcConnection(azureConn);
                using (OdbcCommand command = cnn.CreateCommand())
                {
                    command.CommandText = "{call sp_Get_Excel_Data(?,?,?,?,?,?,?,?)}";
                    command.Parameters.AddWithValue("@StartDate", startDate);
                    command.Parameters.AddWithValue("@EndDate", endDate);
                    command.Parameters.AddWithValue("@startTime", startTime);
                    command.Parameters.AddWithValue("@endTime", endTime);
                    command.Parameters.AddWithValue("@smp", smp);
                    command.Parameters.AddWithValue("@Reg", reg);
                    command.Parameters.AddWithValue("@event", events);
                    command.Parameters.AddWithValue("@userId", userId);

                    cnn.Open();

                    //DataTable
                    OdbcDataAdapter adapter = new OdbcDataAdapter(command);

                    //DataSet
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);

                    //Cast to DataTable
                    DataTable dataTable = ds.Tables[0];

                    string[] colNames = new string[dataTable.Columns.Count];
                    int col = 0;

                    foreach (DataColumn dc in dataTable.Columns)
                        colNames[col++] = dc.ColumnName;

                    w = this.Application.ActiveWorkbook;
                    ws = (Worksheet)w.ActiveSheet;

                    Range hdrRow = (Range)ws.Rows[9];

                    hdrRow.Value = colNames;
                    hdrRow.Font.Bold = true;
                    hdrRow.VerticalAlignment = XlVAlign.xlVAlignCenter;

                    //Position the cursor
                    var range = ws.get_Range("A10");
                    range.Select();

                    //Inserting the Column and Values into Excel file
                    string data = null;
                    int i = 0;
                    int j = 0;

                    for (i = 0; i <= dataTable.Rows.Count - 1; i++)
                    {
                        for (j = 0; j <= dataTable.Columns.Count - 1; j++)
                        {
                            data = dataTable.Rows[i].ItemArray[j].ToString();
                            ws.Cells[i + 2, j + 1] = data;

                        }
                    }

Answer 1

Hate to answer my own questions, but here is the solution (with optimized performance):

                    int column = 1;
                    foreach (DataColumn c in dataTable.Columns)
                    {
                        //Ninth row, starting from the first cell
                        ws.Cells[10, column] = c.ColumnName;
                        column++;
                    }

                    // Create a 2D array with the data from the data table
                    int i = 0;
                    string[,] data = new string[dataTable.Rows.Count, dataTable.Columns.Count];
                    foreach (DataRow row in dataTable.Rows)
                    {
                        int j = 0;
                        foreach (DataColumn c in dataTable.Columns)
                        {
                            data[i, j] = row[c].ToString();
                            j++;
                        }
                        i++;
                    }

                    // Set the range value to the 2D array in Excel (10th row, starting from 1st cell)
                    ws.Range[ws.Cells[11, 1], ws.Cells[dataTable.Rows.Count + 11, dataTable.Columns.Count]].Value = data;