Export DataTable to Excel using c# with Formatting Styles

This following c# Function is used to export DataTable to Excel With Formatting(Font Bold, Font Size etc)

For example, let us assume that a data table(Employee) consists of details about the Employees. (Column names:Emp Name, Emp ID, DOJ)

We may want to export Employee DataTable to excel sheet. We may want to make the Font of the Heading Row of the excel sheet to Bold so that the Column names will be clearly identified.

This function handles that style formatting of excel sheet.

I have used XML tags to write the content of the DataTable so that we can apply formatting

such as Font Size, Bold etc.

For Header Columns, I have used Font Bold.

private void exportDataTableToExcel(DataTable dt, string filePath)
{
    
    //  Excel file Path

    string myFile = filePath ;

    System.Data.DataRow dr = default(System.Data.DataRow);

    int colIndex = 0;
    int rowIndex = 0;
    
    // Open the file and write the headers
    IO.StreamWriter fs = new IO.StreamWriter(myFile, false);
  
    fs.WriteLine("");
    fs.WriteLine("");
    fs.WriteLine("");
    
    // Create the styles for the worksheet
    fs.WriteLine(" ");
    
    // Style for the column headers
    fs.WriteLine(" ");
    fs.WriteLine(" ");
    fs.WriteLine(" ");
    fs.WriteLine(" ");
    fs.WriteLine(" ");
    // Style for the column information
    fs.WriteLine(" ");
    fs.WriteLine(" ");
    fs.WriteLine(" ");
    fs.WriteLine(" ");
    
    // Write the worksheet contents
    fs.WriteLine("");
    fs.WriteLine(" ");
    fs.WriteLine(" ");

colIndex = 0;
//Write the column names
        foreach (var dc in dt.Columns) {
            colIndex = colIndex + 1;
            fs.WriteLine(string.Format(" " + "{0}", dc.ColumnName));


        }


    fs.WriteLine(" ");
    

    // Write contents for each cell      
    string cellText = null;
    foreach (var dr in dt.Rows) {
        rowIndex = rowIndex + 1;
        colIndex = 0;
     
          foreach (var dc in dt.Columns) {
            colIndex = colIndex + 1;
           cellText = dr(dc.ColumnName);
                       // Check for null cell and change it to empty to avoid error
            if (cellText == Constants.vbNullString) cellText = ""; 
            fs.WriteLine(string.Format(" " + "{0}", cellText.ToString));


        }

    }


    
    // Close up the document
    fs.WriteLine(" ");
    fs.WriteLine("");
    fs.WriteLine("");
    fs.Close();
    
}