Click here to Skip to main content
15,566,394 members
Articles / Productivity Apps and Services / Microsoft Office / Microsoft Excel
Tip/Trick
Posted 6 Apr 2020

Tagged as

Stats

20K views
638 downloads
20 bookmarked

Export DataTable To File Using C#

Rate me:
Please Sign up or sign in to vote.
3.19/5 (5 votes)
6 Apr 2020CPOL2 min read
Create a file like Excel, CSV, txt, etc. from a DataTable object using C#
In this post and code sample, I will explain and show steps to export a DataTable object to a file at a local directory using C#.

Background

What are the exported file types?

  • Plain text files with separator (extension like .csv, .txt, .gm, etc.)
  • Excel files (.xls, .xlsx)

Helper Classes

Extention Method To Create Plain File Content

Using this extension, we will be able to create plain file content as a string, considering an input DataTable.

The extension method also have options to:

  • Set separator
  • Include column names as file header row
  • Handle a null valued column with a default value
  • Trim column value if needed
C#
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

public static class DataTableHelper
{
    public static string DataTableToFileContent(this DataTable datatable, 
                  string seperator = ",", bool inchuleColumnName = true, 
                  string defaultNullColumnValue = "", bool trimColumValue = true)
    {
        /*https://stackoverflow.com/a/28503521 */

        if (defaultNullColumnValue == null)
        {
            throw new NullReferenceException("'defaultNullColumnValue' should not be null");
        }
        StringBuilder sb = new StringBuilder();
        
        /*header*/
        if (inchuleColumnName)
        {
            for (int i = 0; i < datatable.Columns.Count; i++)
            {
                sb.Append(datatable.Columns[i]);
                if (i < datatable.Columns.Count - 1)
                {
                    sb.Append(seperator);
                }
            }
            sb.AppendLine();
        }

        /*data*/
        long lineNo = 0;
        string value;
        foreach (DataRow row in datatable.Rows)
        {
            lineNo++;
            if (lineNo > 1)
            {
                sb.AppendLine();
            }
            for (int i = 0; i < datatable.Columns.Count; i++)
            {
                var columnValue = row[i];
                value = columnValue == DBNull.Value ? 
                        defaultNullColumnValue : columnValue.ToString();
                if (trimColumValue)
                {
                    value = value.Trim();
                }
                sb.Append(value);
                if (i < datatable.Columns.Count - 1)
                {
                    sb.Append(seperator);
                }
            }
        }
        return sb.ToString();
    }
}

Extention Method to Create Excel File Object

To create Excel file content, we are using EPPlus. It is an awesome library with lots of cool features and options. Unfortunately, it is not free anymore from version 5, we are using a lower version 4.5.3.3.

The extension method also have options to:

  • set sheet name
  • include column names as header and auto filter to the header row
  • handle a null valued column with a default value
  • trim column value if needed
C#
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

public static class DataTableHelper
{
    private static string GetExcelColumnName(int columnNumber)
    {
        /*
        1 equal A
        https://stackoverflow.com/questions/181596/
        how-to-convert-a-column-number-e-g-127-into-an-excel-column-e-g-aa
        */
        int dividend = columnNumber;
        string columnName = String.Empty;
        int modulo;

        while (dividend > 0)
        {
            modulo = (dividend - 1) % 26;
            columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
            dividend = (int)((dividend - modulo) / 26);
        }

        return columnName;
    }

    public static ExcelPackage DataTableToExcel(this DataTable datatable, 
    string sheetName = "Sheet1",  bool inchuleColumnName = true, 
    string defaultNullColumnValue = null, bool trimColumValue = true)
    {
        if (String.IsNullOrEmpty(sheetName))
        {
            throw new NullReferenceException("'sheetName' should not be null or empty");
        }
        
        sheetName = sheetName.Trim();
        ExcelPackage excel = new ExcelPackage();
        var workSheet = excel.Workbook.Worksheets.Add(sheetName);
        int columnCount = datatable.Columns.Count;
        int lineNo = 1;

        /*header*/
        if (inchuleColumnName)
        {
            int headerNumber = 1;
            foreach (DataColumn column in datatable.Columns)
            {
                workSheet.Cells[lineNo, headerNumber].Value = column.ColumnName;
                headerNumber++;
            }
            workSheet.Row(lineNo).Style.Font.Bold = true;
            /*add filter to header*/
            workSheet.Cells[String.Format("{0}{1}:{2}{3}", GetExcelColumnName(1), 
            lineNo, GetExcelColumnName(columnCount), lineNo)].AutoFilter = true;
            lineNo++;
        }

        /*data*/
        string value;
        foreach (DataRow row in datatable.Rows)
        {
            for (int i = 0; i < columnCount; i++)
            {
                var columnValue = row[i];
                value = columnValue == DBNull.Value ? 
                        defaultNullColumnValue : columnValue.ToString();
                if (trimColumValue && value != null)
                {
                    value = value.Trim();
                }
                int columnNo = i + 1;
                /*https://stackoverflow.com/a/36824090*/
                workSheet.Cells[lineNo, columnNo].Value = 
                                string.IsNullOrEmpty(value) ? null : value;
            }
            lineNo++;
        }

        for (int i = 1; i <= columnCount; i++)
        {
            workSheet.Column(i).AutoFit();
        }
        return excel;
    }
}

Let's add the code to our existing extension helper class.

File Helper

This is a small file/directory/path helper class. Here, we are just going to use methods like:

  • DeleteFileIfExists(string filePath) deletes a file if exists to a location
  • WriteAllText(string filePath, string contents) creates a new file at a location with specific contents
C#
using System.IO;

public class FileSystemHelper
{
    /// <summary>
    /// string p1 = "c:\\temp\\";
    /// string p2 = "\\subdir\\file\\";
    /// to c:\temp\subdir\file
    /// </summary>
    public static string CombineDirectory(string rootDirectoryPath, string childDirectoryPath)
    {
        rootDirectoryPath = rootDirectoryPath.TrimEnd('\\');
        childDirectoryPath = childDirectoryPath.Trim('\\');
        return Path.Combine(rootDirectoryPath, childDirectoryPath);
    }

    /// <summary>
    /// string p1 = "c:\\temp\\";
    /// string p2 = "\\file.txt";
    /// to c:\temp\file.txt
    /// </summary>
    public static string CombineFile(string rootDirectoryPath, string filePathOrName)
    {
        rootDirectoryPath = rootDirectoryPath.TrimEnd('\\');
        filePathOrName = filePathOrName.Trim('\\');
        return Path.Combine(rootDirectoryPath, filePathOrName);
    }

    public static void CreateDirectoryIfNotExists(string directoryPath)
    {
        if (!DirectoryExists(directoryPath))
        {
            Directory.CreateDirectory(directoryPath);
        }
    }

    public static void DeleteFileIfExists(string filePath)
    {
        if (FileExists(filePath))
        {
            File.Delete(filePath);
        }
    }

    public static bool DirectoryExists(string directoryPath)
    {
        return Directory.Exists(directoryPath);
    }

    public static bool FileExists(string filePath)
    {
        return File.Exists(filePath);
    }

    /*file*/
    public static void MoveFile(string fromFilePath, string toFilePath)
    {
        File.Move(fromFilePath, toFilePath);
    }

    public static void WriteAllText(string filePath, string contents)
    {
        /*create file if doesn't exist*/
        File.WriteAllText(filePath, contents);
    }
}

Other methods are also useful, so have a look at them too.

Using the Code

Create Plain File

Here we are creating a CSV file.

C#
string filePath = @"c:\dataTable.csv";
DataTable dataTable = Data.Instance.DataTable;

FileSystemHelper.DeleteFileIfExists(filePath);
FileSystemHelper.WriteAllText(filePath, dataTable.DataTableToFileContent());

To use any other extension like txt, we only need to change the file name to "dataTable.txt".

Create Excel File

Here we are creating an XLSX file.

C#
string filePath = @"c:\dataTable.xlsx";
DataTable dataTable = Data.Instance.DataTable;

FileSystemHelper.DeleteFileIfExists(filePath);
using (var excel = dataTable.DataTableToExcel())
{
    excel.SaveAs(new FileInfo(filePath));
}

I believe XLS is also possible, just change the extension name and give a try.

Solution And Projects

It is a Visual Studio 2015 solution and .NET Framework 4.5

Limitations

  • Column values are converted to string using ToString() method. So DateTime, decimal, float or other seminar datatype conversations to the string may not be as expected. We need to create a formatted DataTable object first.
  • Column number, order or name also needs to be pre formatted.
  • It may not be the most efficient way to create large files.

The code may throw unexpected errors for untested inputs. If any, just let me know.

What's Next?

  • Creating XML, doc and pdf files
  • ASP.NET response example

History

  • 6th April, 2020: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Bangladesh Bangladesh
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
SuggestionFormatting Excel column as decimal Pin
Member 1463937713-Jan-21 2:50
Member 1463937713-Jan-21 2:50 
QuestionSimilar code in .Net Core 3.1 Pin
Debopriyo Das2-Jul-20 11:53
Debopriyo Das2-Jul-20 11:53 
AnswerRe: Similar code in .Net Core 3.1 Pin
DiponRoy3-Jul-20 4:12
DiponRoy3-Jul-20 4:12 
QuestionI use this to save DataTable to delimited file Pin
tolsen647-Apr-20 8:59
professionaltolsen647-Apr-20 8:59 
AnswerRe: I use this to save DataTable to delimited file Pin
DiponRoy7-Apr-20 21:37
DiponRoy7-Apr-20 21:37 
GeneralMy vote of 5 Pin
Member 78703457-Apr-20 1:38
professionalMember 78703457-Apr-20 1:38 
Very usufull
GeneralRe: My vote of 5 Pin
DiponRoy7-Apr-20 3:03
DiponRoy7-Apr-20 3:03 
PraiseNice effort man, Pin
Member 130408737-Apr-20 0:17
Member 130408737-Apr-20 0:17 
GeneralRe: Nice effort man, Pin
DiponRoy7-Apr-20 0:39
DiponRoy7-Apr-20 0:39 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.