Alternative to MS Interop to Export DataTable to Excel

Ah no, I just built a whole code to export the data into Excel but the client does not have MS Office on their computer.

Without wasting your time let me share with you why I am writing this article for you. There are scenarios when you will use this block of code given below.

1. You wrote a block of code in C# WinForms or WPF to Export the data into Excel, but the client does not have MS Office installed on their computer.

2. You wrote the same using MS Interop but the client machine has a different MS OFfice version

3. Client Computer does not have InterOP installed

4. You want to use OpenSource Library to export to Excel

I had almost all the challenges in different cases during my 15 years of experience with C#

Mostly I use ClosedXML library which is OpenSource LIbrary. I have always had the best experience playing around with large excel files for better performance and easy implementation

ClosedXML in Nuget

Just easy to implement using nuget package

PM> Install-Package ClosedXML

Here is a simple block of code to get it done easily.

using (var workbook = new XLWorkbook())
{
    var worksheet = workbook.Worksheets.Add("Sample Sheet");
    worksheet.Cell("A1").Value = "Hello World!";
    worksheet.Cell("A2").FormulaA1 = "=MID(A1, 7, 5)";
    workbook.SaveAs("HelloWorld.xlsx");
}

Convert DataTable to Excel Sheet using ClosedXML

I wanted to give you a just a ready method that will help you create an Excel File from the DataTable without Looping through the rows.

   public static void ConverTableToExcel(DataTable tblObject, string SheetName, string FilePath, bool OpenAfterSave)
        {
            try
            {

                string PathToSave = FilePath;
                if (PathToSave.Trim().Length == 0)
                {
                    SaveFileDialog sfd = new SaveFileDialog();
                    sfd.Filter = "Excel Worksheets|*.xlsx";
                    if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                    {
                        PathToSave = sfd.FileName;
                    }
                    else
                    {
                        return;
                    }
                }
                if (PathToSave.Length > 0)
                {
                    XLWorkbook workBook = new XLWorkbook();
                    if (File.Exists(FilePath))
                    {
                        workBook = new XLWorkbook(FilePath);
                    }
                    IXLWorksheet ws = workBook.Worksheets.Add(tblObject, SheetName.Length > 0 ? SheetName : "Sheet1");
                    ws.Unprotect();
                    Application.DoEvents();
                    workBook.SaveAs(PathToSave);

                    if (OpenAfterSave)
                    {

                        var p = new Process();
                        p.StartInfo = new ProcessStartInfo(PathToSave)
                        {
                            UseShellExecute = true
                        };
                        p.Start();

                    }
                    else
                    {

                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);

            }

        }

How to Export a DataTable into Excel in One Line?

 ConverTableToExcel(tblConvertedForMatirixify, "Products", TempFilePath, false);

Bingo that's simple, easy, and a time saver. That's why I have kept in my code library which is used in all my Data Analysis tools development.

Soon I will share a similar thing to Export DataTable to CSV

Thanks.