Export DataTable To File Using C#

• Download file samples - 2.5 KB
• Download Vs2015_DatatableToFile - 9.2 KB

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

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

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

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.

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.

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

• 6^th April, 2020: Initial version