how do I export C# datatable to Excel 365

Question

I have been using the following code to export a dataTable to Excel. I now have users that have upgraded to Microsoft 365 and this no longer works. My research to make the export work to Excel 365 is not panning out. any help would be grateful

public static void ExportToExcel(this DataTable tbl, string excelFilePath = null) {

        //List<string> list = new List<string>();

        //list = tbl;

        try            {

            if (tbl == null || tbl.Columns.Count == 0)

                throw new Exception("ExportToExcel: Null or empty input table!\n");

            // load excel, and create a new workbook

            var excelApp = new Excel.Application();

            excelApp.Workbooks.Add();

            // single worksheet

            Excel._Worksheet workSheet = excelApp.ActiveSheet;

            // column headings

            for (var i = 0; i < tbl.Columns.Count; i++)                {

                workSheet.Cells[1, i + 1] = tbl.Columns[i].ColumnName;

            }

            // rows

            for (var i = 0; i < tbl.Rows.Count; i++)                {

                // to do: format datetime values before printing

                for (var j = 0; j < tbl.Columns.Count; j++)                    {

                    workSheet.Cells[i + 2, j + 1] = tbl.Rows[i][j];

                }

            }

            // check file path

            if (!string.IsNullOrEmpty(excelFilePath))                {

                try                    {

                    workSheet.SaveAs(excelFilePath);

                    excelApp.Quit();

                    MessageBox.Show("Excel file saved!");

                }

                catch (Exception ex)                    {

                    throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"

                                        + ex.Message);

                }

            }

            else

            { // no file path is given

                excelApp.Visible = true;

            }

        }

        catch (Exception ex)

        {

            throw new Exception("ExportToExcel: \n" + ex.Message);

        }

    }

Answer 1

Take a look at Spreadsheetlight NuGet package (free).

Example with no formatting.

public static void ExportToExcel(DataTable table, string fileName, bool includeHeader, string sheetName)
{
    using var document = new SLDocument();

    // import to first row, first column
    document.ImportDataTable(1, SLConvert.ToColumnIndex("A"), table, includeHeader);

    // give sheet a useful name
    document.RenameWorksheet(SLDocument.DefaultFirstSheetName, sheetName);

    document.SaveAs(fileName);
}

Example showing how to format a date column and specify the start row.

public static void ExportToExcel(DataTable table, string fileName, bool includeHeader, string sheetName, int row)
{
    using var document = new SLDocument();


    document.ImportDataTable(row, SLConvert.ToColumnIndex("A"), table, includeHeader);

    // give sheet a useful name
    document.RenameWorksheet(SLDocument.DefaultFirstSheetName, sheetName);

    SLStyle dateStyle = document.CreateStyle();
    dateStyle.FormatCode = "mm-dd-yyyy";
    // format a specific column using above style
    int dateColumnIndex = 6;
    document.SetColumnStyle(dateColumnIndex, dateStyle);

    document.SaveAs(fileName);
}