C# SQL query output to Excel not including newly added fields in output

Question

I have a small button click code which outputs a SQL query to Excel.

This worked fine until I added a new field in the table it queries and now it is still outputting the same fields and not including the new one.

Code:

 private void button4_Click(object sender, EventArgs e)
        {
            string SQLQuery = "SELECT * FROM Services";
            SqlConnection conn = new SqlConnection("CONNECTION STRING");
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(SQLQuery, conn);
            da.Fill(ds);
            Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
            Workbook ExcelWorkBook = null;
            Worksheet ExcelWorkSheet = null;
            ExcelApp.Visible = true;
            ExcelApp.WindowState = XlWindowState.xlMinimized;
            ExcelApp.WindowState = XlWindowState.xlMaximized;
            ExcelWorkBook = ExcelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            List<string> SheetNames = new List<string>();
            SheetNames.Add("Services Details");
            ExcelApp.ActiveWindow.Activate();
            try
            {
                for (int i = 1; i < ds.Tables.Count; i++)
                    ExcelWorkBook.Worksheets.Add();
                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    int r = 1;
                    ExcelWorkSheet = ExcelWorkBook.Worksheets[i + 1];
                    ExcelWorkSheet.Name = "Services";
                    for (int col = 1; col < ds.Tables[i].Columns.Count; col++)
                        ExcelWorkSheet.Cells[r, col] = ds.Tables[i].Columns[col - 1].ColumnName;
                    r++;
                    for (int row = 0; row < ds.Tables[i].Rows.Count; row++)
                    {
                        for (int col = 1; col < ds.Tables[i].Columns.Count; col++)
                            ExcelWorkSheet.Cells[r, col] = ds.Tables[i].Rows[row][col - 1].ToString();
                        r++;
                    }
                    ExcelWorkSheet.Rows[1].EntireRow.Font.Bold = true;
                    ExcelApp.Columns.AutoFit();
                }
                ExcelApp.Quit();
                Marshal.ReleaseComObject(ExcelWorkSheet);
                Marshal.ReleaseComObject(ExcelWorkBook);
                Marshal.ReleaseComObject(ExcelApp);
            }
            catch (Exception exHandle)
            {
                Console.WriteLine("Exception: " + exHandle.Message);
                Console.ReadLine();
            }
        }

I have also tried explicitly querying the new field SELECT ABC FROM Services and nothing is output.

There are values in the new fields.

If I run the same query on Azure query editor preview I get the correct results.

EDIT

Ok so I changed the query to SELECT *,1 FROM Services and then I get all the fields (bar the new "1" field) how can I change the loop to get all fields?

EDIT 2 SOLUTION Using EPPlus

Just to update anyone looking at this in the future, I used the NuGet Package Manager (Project > Manage NuGet Packages) and installed EPPlus by Jan Kallmän.

I then added:

using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.Data.SqlClient;
using System.IO;

And used the following code on the button:

    private void button4_Click(object sender, EventArgs e)
    {
        string SQLQuery = "SELECT * FROM Services";
        SqlConnection conn = new SqlConnection("CONNECTION STRING");
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(SQLQuery, conn);
        DataTable dt = new DataTable();
        da.Fill(dt);
        using (var p = new ExcelPackage())
        {
            var ws = p.Workbook.Worksheets.Add("Service");
            ws.Cells["A1"].LoadFromDataTable(dt, true);
            int totalRows = ws.Dimension.End.Row;
            int totalCols = ws.Dimension.End.Column;
            var headerCells = ws.Cells[1, 1, 1, totalCols];
            var headerFont = headerCells.Style.Font;
            headerFont.Bold = true;
            var allCells = ws.Cells[1, 1, totalRows, totalCols];
            allCells.AutoFitColumns();
            p.SaveAs(new FileInfo(@"d:\excel\Service" + DateTime.Now.ToFileTime() + ".xlsx"));
        }
    }

This is an instant output to a file and thanks to @Caius Jard

Answer 1

First up, I second the comment about using EPPlus. I think it even has a method to turn a datatable into a sheet, so this click event handler could be boiled down to about 4 lines of code. Take a look over this SO question - Export DataTable to excel with EPPlus

Second, I think your actual problem is a simple off-by-one error

You said (my paraphrase)

if I use a query that returns one column, like select abc from services, nothing is output

Here's how you're doing your column output:

for(int col = 1; col < table.Columns.Count; col++)

Your table has one column. The comparison to run the loop is thus: is 1 less than 1?

False

Loop doesn't run

If your table had 10 columns, the loop would run 9 times. The last column will never be output. You've got a query with a new column on the end, you're expecting to see it, it's not there because your c# doesn't output it, not because there is something weird about the data in the column..

In terms of what to do about it, change the comparison you do in the "should loop run" part:

for(int col = 1; col <= table.Columns.Count; col++)

Or change the way you index (index by 0 instead of index by 1):

for(int col = 0; col < table.Columns.Count; col++)
  excel[row, col+1] = table[row][col]; //excel is 1 based, c# is 0 based

Regarding those notions of "but the data hasn't changed, and the code hasn't changed, and something different is observed" - it's almost never the case. It's far more likely that something is being misremembered by the human in the equation. Maybe the first guy that wrote the code hit the same issue and just duplicated the last column in the SQL to get around it, then you/someone else saw it and though "that looks wrong, i'll just take that out..", then months later you're looking and going "this used to work, i'm sure it did..."

:)

But seriously; use EPPlus ;) it's delightful, easy, creates xlsx files directly (they're just xml files inside a zip, renamed to xlsx) and is super simple. Excel COM is a massive headache, and you'd be needlessly building a tech support nightmare for yourself. With EPPlus (use nuget package manager to add a reference to EPPlus) your code would look more like:

using(SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM services", "PUT YOUR CONN STRING IN A CONFIG FILE")){
    DataTable dt = new DataTable();
    da.Fill(dt);
    using (ExcelPackage ep = new ExcelPackage(newFile))
    {
      ExcelWorksheet ws = ep.Workbook.Worksheets.Add("Sheetname");
      ws.Cells["A1"].LoadFromDataTable(dt, true);
      ep.SaveAs(new FileInfo("YOUR SAVE PATH HERE"));
    }
}

Answer 2

Is "Services" a table or a view? You might be seeing results from an outdated schema (cached before you made your change). This is a danger with "select *". Try an explicit field list instead of "*".