Export DataTable To Excel in C#

Kailash Chandra Behera | Thursday, June 04, 2020

Introduction

In this blog, we will discuss how to export data from DataTable to excel file without using third party library in C#.

Getting Started

Let’s way you have student details data in SQL, or any other data source and you want to export the student details into an excel sheet with the name “StudentDetails.xlsx”.

Here I am not going to discuss how to fetch data from the data sources as this blog is not focusing the data fetching part. As mentioned in the above paragraph, let’s say you have already fetched data from SQL or any other data source and stored in DataTable and you DataTable has a data structure like in the below image.

Export DataTable to Excel

If you don’t know how to fetch data from the data source, I will request you to please refer to any other article to fetch data. You will get a number of articles to fetch data from the various data source.

Now we will discuss the step by step code to export data from DataTable to Excel sheet. Let’s say you have declared a function called ‘ExportToExcel’, write code to fetch data from the data source, and store into DataTable variable (dataTable) or you can create another function to write fetching logic which will return DataTable.

Then create the object of StringBuilder and append the DataTable column names into the StringBuilder variable. See the bellow code for more clarification.

 StringBuilder stringBuilder = new StringBuilder();   
 stringBuilder.Append(string.Join(",", dataTable.Columns.Cast<DataColumn>().Select(column => column.ColumnName).ToList()) + "\n");

The above code appends column names as a string by using commas for separation. First the above code using a lambda expression, creates a list that contains column names then converts it into a comma-separated string. The string. Join function helps to build a comma-separated string from the list.

Again, append the cell data from the table into the StringBuilder object using the below codes.

 dataTable.AsEnumerable().ToList<DataRow>().ForEach(dataRow => stringBuilder.Append(string.Join(",", dataRow.ItemArray) + "\n"));

Finally create an excel file using FileStream class and write the StringBuilder content into the excel file. The below code helps to write data.

 FileStream studentExcel=new FileStream("StudentDetails.xlsx", FileMode.Create, FileAccess.Write);  
 StreamWriter streamWriter = new StreamWriter(studentExcel);   
 streamWriter.Write(stringBuilder.ToString());  
 streamWriter.Flush();   
 streamWriter.Dispose();   
 streamWriter.Close();  
 studentExcel.Close();  
 studentExcel.Dispose();

Now you are done with data Export, see the below code which contains complete codes to export Data to excel.

 public void ExportToExcel(string ExcelFilePath, DataTable dataTable)   
     {   
       // Initilization   
       bool isSuccess = false;   
       StreamWriter sw = null;   
       try   
       {   
         // Initialization.   
         StringBuilder stringBuilder = new StringBuilder();   
         // Saving Column header.   
         stringBuilder.Append(string.Join(",", dataTable.Columns.Cast<DataColumn>().Select(column => column.ColumnName).ToList()) + "\n");   
         // Saving rows.   
         dataTable.AsEnumerable().ToList<DataRow>().ForEach(row => stringBuilder.Append(string.Join(",", row.ItemArray) + "\n"));   
         sw = new StreamWriter(new FileStream(ExcelFilePath, FileMode.Create, FileAccess.Write));   
         // Saving.   
         sw.Write(stringBuilder.ToString(););   
       }   
       catch (Exception ex)   
       {   
         // Info.   
         throw ex;   
       }   
       finally   
       {   
         // Closing.   
         sw.Flush();   
         sw.Dispose();   
         sw.Close();   
       }   
       // Info.   
     }   
     #endregion   
   }

Related Articles

  1. Export to Excel in MVC
  2. Export HTML Table to Excel Using JavaScript
  3. Export Table Data into XML in SQL
  4. Validating Excel Sheet in C#
  5. Reading Excel file in AngularJS
  6. Creating controller for File Upload
  7. Import XML into SQL Table
  8. Import Excel in C#

Summary

In this blog Export DataTable To Excel in C#, we have discussed how to export DataTable data into excel file using simple C# Code and Linq. I hope you enjoyed it.

Thanks

By at