C# Console App Writing To Excel Is Returning Empty Cells

Question

I have been stuck with this problem for a while and cannot seem to find a way around it. I have read numerous articles on Stack Overflow yet nothing seems to answer my question. The tutorials I have followed as well all give different solutions but none seem to work.

I am trying to write data from a DataTable into an excel spreadsheet. I am using Microsoft.Office.Interop.Excel package and I do have Excel installed on my pc with admin rights.

At the top of my console app I have the following:

using Microsoft.Office.Interop.Excel;

I am using the following code to create the excel spreadsheet and populate it where dt is the DataTable:

            var excel = new Application();

            excel.Visible = false;
            excel.DisplayAlerts = false;

            var workBook = (Workbook)excel.Workbooks.Add(Type.Missing);
            var workSheet = (Worksheet)workBook.ActiveSheet;
            workSheet.Name = "Transformed Table";

            for(int row = 0; row < dt.Rows.Count; row++)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    workSheet.Cells[row + 2, i + 1] = dt.Rows[row][i].ToString();
                }
            }

            workBook.SaveAs("C:\\Users\\******\\Downloads\\test-file.xlsx");
            workBook.Close();
            excel.Quit();

The end result is an empty sheet.

try different approach like here : https://stackoverflow.com/questions/8207869/how-to-export-datatable-to-excel — Kuba Do, Aug 23 '19 at 08:17

score 0 · Accepted Answer · answered Aug 23 '19 at 13:18

Based on on the suggestions in this post: How to export DataTable to Excel I was able to use the following code to generate a csv file that did what I needed:

            var dtLines = new List<string>();
            string[] columnNames = dt.Columns
                .Cast<DataColumn>()
                .Select(column => column.ColumnName)
                .ToArray();

            var header = string.Join(",", columnNames.Select(name => $"\"{name}\""));
            dtLines.Add(header);

            var valueLines = dt.AsEnumerable()
                .Select(row => string.Join(",", row.ItemArray.Select(val => $"\"{val}\"")));

            dtLines.AddRange(valueLines);

            File.WriteAllLines("excel.csv", dtLines);

C# Console App Writing To Excel Is Returning Empty Cells

1 Answers1