This quick tutorial will guide you on how to convert DataTable to Excel in C#. First, we will create a DataTable and then import it into the worksheet of the newly created workbook object using the ImportData function of WorkSheet.Cells class. After writing DataTable data to Excel in C#, we will save it as an XLSX file on the disc.
Steps to Convert DataTable to Excel in C#
- Using the NuGet package manager, add Aspose.Cells for .NET to import DataTable into Excel
- Create an instance of an empty Workbook for exporting DataTable to it
- Create and initialize a DataTable for writing to Excel file
- Declare an object of ImportTableOptions class for setting parameters while importing data from DataTable
- Get a reference to the first worksheet in the newly created workbook
- Call the Cells.ImportData function in the WorkSheet class to import DataTable
- Save the resultant Workbook having data from the DataTable
These steps describe the process to export data from DataTable to Excel in C# in a step-by-step manner such that first the empty workbook is created and then a DataTable is initialized and filled with some dummy data. An object of ImportTableOptions class is created that contains a lot of parameters that can be set, however here the default options are used. In the end, the DataTable is imported into the first worksheet of the Workbook at a specified starting cell.
Code to Export DataTable Data to Excel in C#
| using System; | |
| using System.Data; | |
| using Aspose.Cells; | |
| namespace ConvertDataTableToExcelInCSharp | |
| { | |
| class Program | |
| { | |
| static void Main(string[] args) | |
| { | |
| // Use Aspose.Cells license to remove trial version watermark from the Excel file after exporting DataTable | |
| License licenseForCells = new License(); | |
| licenseForCells.SetLicense("Aspose.Cells.lic"); | |
| // Create an object of a workbook to export DataTable | |
| Workbook workbookForDataTable = new Workbook(); | |
| // Create a sample DataTable for the student | |
| DataTable studentTable = new DataTable("Student"); | |
| // Add multiple columns in the newly created DataTable | |
| studentTable.Columns.Add("Roll No", typeof(long)); | |
| studentTable.Columns.Add("Age", typeof(short)); | |
| studentTable.Columns.Add("Name", typeof(string)); | |
| // Create a new row for adding to the data table | |
| DataRow studentRecord = studentTable.NewRow(); | |
| // Set the fields data in the row | |
| studentRecord["Roll No"] = 1002; | |
| studentRecord["Age"] = 19; | |
| studentRecord["Name"] = "Alfred Keam"; | |
| // Add this newly created record into the student table | |
| studentTable.Rows.Add(studentRecord); | |
| // Create another row for the student table | |
| studentRecord = studentTable.NewRow(); | |
| // Set data in the newly created row | |
| studentRecord["Roll No"] = 1003; | |
| studentRecord["Age"] = 20; | |
| studentRecord["Name"] = "Bernadette Thares"; | |
| // Add this record to the student table | |
| studentTable.Rows.Add(studentRecord); | |
| // Instantiate an object of ImportTableOptions for controlling the import of DataTable into Excel | |
| ImportTableOptions importOptions = new ImportTableOptions(); | |
| // Get reference to the first worksheet in the workbook | |
| Worksheet dataTableWorksheet = workbookForDataTable.Worksheets[0]; | |
| // Call the ImportData function to import DataTable starting from cell A1 described by row 0 and column 0 | |
| dataTableWorksheet.Cells.ImportData(studentTable, 0, 0, importOptions); | |
| // Set the columns width so that entire data is visible within the cell | |
| dataTableWorksheet.AutoFitColumns(); | |
| // Save the output workbook on the disc | |
| workbookForDataTable.Save("DataTableImported.xlsx"); | |
| } | |
| } | |
| } |
In this code, ImportTableOptions is used with default settings however, you can set a variety of parameters like a list of 0 index based column numbers whose data is to be imported from the DataTable, set the date format, set total rows and columns to be imported, and many more. You can also decide whether column titles are to be imported or not.
This short tutorial has explained how in C# create Excel file from DataTable. If you want to learn the reverse process like Convert Excel to DataTable, refer to the article on how to convert Excel to DataTable in C#.
