Tags

Need a consistent an easy way to read from or write to any (reasonably) type of data file? Excel, tab-delimited text, CSV, XML, JSON, custom-delimited, whatever? Read on then, my friend.

The products I develop for my current company have been engineered individually over time, and common libraries have converged into a nice class library that I now use for all of the projects. One particularly useful pair of classes that we have are the DataFileReader and DataFileWriter classes. Like any new attempts to create a new class, they arose out of an immediate need, and were not perfectly planned or implemented. This pair of classes is about 5 years old now, however, and now I have strictly enforced a policy that any reading from or writing to data files should be done through them. It is not a policy that is strictly followed, but for the most part, the classes meet all of our data file needs.

The basic structure of the classes is based on an abstract class pair “DataFileReaderBase” and “DataFileWriterBase”. We have engineered these to be easy to use, and to either take a DataTable object and write them to a file of your choice, or to take a data file of your choice and read them into a DataTable object.

An example of usage:
I have a file test.csv that is (of course) a CSV file with the column names in the first row.

DataFileReaderBase _reader = DataFileReaderBase.GetDataFileReaderBase(@“C:\files\test.csv”);
_reader.HasColumnNames = true;
DataTable _dtData = _reader.Read();

All of the data is now in the data table. Now, I want to write the data table out to an Excel format document, with the headers again. FYI – if I drop the “x” from the end of the file extension, it will write it in Excel 2003 format…

DataFileWriterBase _writer = DataFileWriterBase.GetDataFileWriterBase(@”C:\files\test.xlsx”);
_writer.HasColumns = true;
_writer.Write(_dtData);

You will need to install a few packages from NuGet, but they are an easy google search away if you cannot figure it out.

You will notice in the code that there are options for how many rows to read, which row to start with, and what delimiter to use. That could probably be moved to a more applicable subclass to text files, so there is some room for improvement here, obviously. Either way, I hope someone finds this little pair of classes helpful in their development…

Here is the DataFileReaderBase class:

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using ExcelLibrary.SpreadSheet;
using OfficeOpenXml;
using System.Collections;
using System.Text;
using System.Xml;
using Newtonsoft.Json;

namespace TKUtilities.DataAccess
{
    public abstract class DataFileReaderBase
    {
        protected string _TableName;
        protected int _RecordCount;
        public string FilePath { get; set; }
        public bool HasColumnNames { get; set; }
        public bool TextHasQuotes { get; set; }
        public string Delimiter { get; set; }
        public int MaxRecords { get; set; }
        public string TableName { get { return this._TableName; } }
        public List<string> TableNames { get; set; }
        public int RecordCount { get { return this._RecordCount; } }

        public abstract DataTable Read(int _startRow = 0);
        public abstract void SetSheetName(string _NewSheetName, bool _RefreshRowCount = false, bool _ValidateSheetName = true);

        public virtual string GetActiveSheetName()
        {
            return this._TableName;
        }

        public virtual List<string> GetSheetNames()
        {
            return this.TableNames;
        }

        public static DataFileReaderBase GetDataFileReaderBase(string _FilePath, bool _forceText = false)
        {
            if (_forceText)
            {
                return new DelimitedDataFileReader { FilePath = _FilePath };
            }

            switch (Path.GetExtension(_FilePath).ToLower())
            {
                case ".xls":
                    return new XLSDataFileReader { FilePath = _FilePath };
                case ".xlsx":
                    return new XLSXDataFileReader { FilePath = _FilePath };
                case ".csv":
                    return new DelimitedDataFileReader { FilePath = _FilePath, Delimiter = ",", TextHasQuotes = true };
                case ".xml":
                    return new XMLDataFileReader { FilePath = _FilePath };
                case ".json":
                    return new JsonDataFileReader { FilePath = _FilePath };
                case ".txt":
                default:
                    return new DelimitedDataFileReader { FilePath = _FilePath, Delimiter = "\t" };
            }
        }

        public DataFileReaderBase()
        {
            this.FilePath = string.Empty;
            this.HasColumnNames = false;
            this.MaxRecords = int.MaxValue;
            this._TableName = null;
            this.TableNames = null;
        }
    }
    
    public class XLSXDataFileReader : DataFileReaderBase
    {
        ExcelPackage pck { get; set; }

        public override void SetSheetName(string _NewSheetName, bool _RefreshRowCount = false, bool _ValidateSheetName = true)
        {
            bool loaded = false;

            using (ExcelPackage pck = new ExcelPackage())
            {
                if (_ValidateSheetName)
                {
                    if (this.TableNames == null || this.TableNames.Count <= 0)
                    {
                        using (FileStream stream = File.OpenRead(this.FilePath))
                        {
                            pck.Load(stream);
                            loaded = true;
                        }

                        this.TableNames = pck.Workbook.Worksheets.Select(x => x.Name).ToList();
                    }

                    if (!this.TableNames.Contains(_NewSheetName))
                        throw new Exception("Sheet not found in worksheet.");
                }

                if (!_RefreshRowCount)
                {
                    this._TableName = _NewSheetName;
                    return;
                }

                if (this._TableName == _NewSheetName)
                    return;

                this._TableName = _NewSheetName;

                if (!loaded)
                {
                    using (FileStream stream = File.OpenRead(this.FilePath))
                    {
                        pck.Load(stream);
                        loaded = true;
                    }
                }
                
                if(pck.Workbook.Worksheets.First(x => x.Name == this._TableName).Dimension != null)
                    this._RecordCount = pck.Workbook.Worksheets.First(x => x.Name == this._TableName).Dimension.End.Row;
            }
        }

        public override string GetActiveSheetName()
        {
            using (ExcelPackage pck = new ExcelPackage())
            {
                using (FileStream stream = File.OpenRead(this.FilePath))
                {
                    pck.Load(stream);
                }

                return pck.Workbook.Worksheets.First().Name;
            }
        }

        public override List<string> GetSheetNames()
        {
            if (this.TableNames == null || this.TableNames.Count <= 0)
            {
                using (ExcelPackage pck = new ExcelPackage())
                {
                    using (FileStream stream = File.OpenRead(this.FilePath))
                    {
                        pck.Load(stream);
                    }

                    this.TableNames = pck.Workbook.Worksheets.Select(x => x.Name).ToList();
                }
            }

            return this.TableNames;
        }

        public override DataTable Read(int _startRow = 0)
        {
            // source: http://stackoverflow.com/questions/13396604/excel-to-datatable-using-epplus-excel-locked-for-editing/13396787#13396787
            DataTable tbl = new DataTable();

            using (ExcelPackage pck = new ExcelPackage())
            {
                using (FileStream stream = File.OpenRead(this.FilePath))
                {
                    pck.Load(stream);
                }

                ExcelWorksheet ws;

                if (this.TableName == null)
                {
                    ws = pck.Workbook.Worksheets.First();
                    this._TableName = ws.Name;
                }
                else
                    ws = pck.Workbook.Worksheets.First(x => x.Name == this._TableName);

                if (ws.Dimension == null)
                    return tbl;

                foreach (ExcelRangeBase firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
                {
                    string _columnName = HasColumnNames ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column);

                    if (tbl.Columns.Contains(_columnName))
                    {
                        int _columnRepeat = 1;

                        string _repeatColumnName = _columnName + "_" + _columnRepeat;

                        while (tbl.Columns.Contains(_repeatColumnName))
                        {
                            _columnRepeat++;
                            _repeatColumnName = _columnName + "_" + _columnRepeat;
                        }

                        _columnName = _repeatColumnName;
                    }

                    tbl.Columns.Add(_columnName);
                }

                _startRow += this.HasColumnNames ? 2 : 1;
                int RowCount = ws.Dimension.End.Row;

                if (this.MaxRecords > 0 && this.MaxRecords < RowCount)
                    RowCount = this.MaxRecords + (this.HasColumnNames ? 1 : 0);

                for (int rowNum = _startRow; rowNum <= RowCount; rowNum++)
                {
                    ExcelRange wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];

                    DataRow row = tbl.NewRow();

                    foreach (ExcelRangeBase cell in wsRow)
                    {
                        if (tbl.Columns.Count > (cell.Start.Column - 1))
                            row[cell.Start.Column - 1] = cell.Text;
                    }

                    tbl.Rows.Add(row);
                }
            }

            return tbl;
        }
    }

    public class XLSDataFileReader : DataFileReaderBase
    {
        // using ExcelLibrary library
        Workbook workbook { get; set; }

        public override string GetActiveSheetName()
        {
            Workbook workbook = Workbook.Load(this.FilePath);
            return workbook.Worksheets.First().Name;
        }

        public override List<string> GetSheetNames()
        {
            Workbook workbook = Workbook.Load(this.FilePath);
            return workbook.Worksheets.Select(x => x.Name).ToList();
        }

        public override void SetSheetName(string _NewSheetName, bool _RefreshRowCount = false, bool _ValidateSheetName = true)
        {
            Workbook workbook = null;

            if (_ValidateSheetName)
            {
                if (this.TableNames == null || this.TableNames.Count <= 0)
                {
                    if (workbook == null)
                        workbook = Workbook.Load(this.FilePath);

                    this.TableNames = workbook.Worksheets.Select(x => x.Name).ToList();
                }

                if (!this.TableNames.Contains(_NewSheetName))
                    throw new Exception("Sheet not found in worksheet.");
            }

            if (!_RefreshRowCount)
            {
                this._TableName = _NewSheetName;
                return;
            }

            if (this._TableName == _NewSheetName)
                return;

            this._TableName = _NewSheetName;

            if (workbook == null)
                workbook = Workbook.Load(this.FilePath);

            this._RecordCount = workbook.Records.Count;
        }

        public override DataTable Read(int _startRow = 0)
        {
            // source: http://stackoverflow.com/a/11445573/1194816
            DataTable tbl = new DataTable();
            Workbook workbook = Workbook.Load(this.FilePath);

            if (this._TableName == null)
                this._TableName = workbook.Worksheets.First().Name;

            this.TableNames = workbook.Worksheets.Select(x => x.Name).ToList();

            CellCollection Cells = workbook.Worksheets.First(x => x.Name == TableName).Cells;

            // Extract columns
            for (int i = 0; i <= Cells.LastColIndex; i++)
            {
                string columnName = this.HasColumnNames ? Cells[0, i].StringValue : ("Column " + (i + 1).ToString());

                if (tbl.Columns.Contains(columnName))
                {
                    int columnRepeat = 1;
                    string repeatColumnName = columnName + "_" + columnRepeat;

                    while (tbl.Columns.Contains(repeatColumnName))
                    {
                        columnRepeat++;
                        repeatColumnName = columnName + "_" + columnRepeat;
                    }

                    columnName = repeatColumnName;
                }

                tbl.Columns.Add(columnName);
            }

            int RowCount = Cells.LastRowIndex;
            if (this.MaxRecords > 0 && this.MaxRecords < RowCount)
                RowCount = this.MaxRecords;

            // Extract data
            for (int currentRowIndex = _startRow + 1; currentRowIndex <= RowCount; currentRowIndex++)
            {
                DataRow dr = tbl.NewRow();

                for (int currentColumnIndex = 0; currentColumnIndex <= Cells.LastColIndex; currentColumnIndex++)
                    dr[currentColumnIndex] = Cells[currentRowIndex, currentColumnIndex].StringValue;

                tbl.Rows.Add(dr);
            }

            return tbl;
        }
    }

    public class DelimitedDataFileReader : DataFileReaderBase
    {
        protected const string _defaultDelimiter = "\t";

        public override void SetSheetName(string _NewSheetName, bool _RefreshRowCount = false, bool _ValidateSheetName = true)
        {
            //only need a record count.
            if (_RefreshRowCount)
                this._RecordCount = File.ReadAllLines(this.FilePath).Length;
        }

        public override DataTable Read(int _startRow = 0)
        {
            DataTable tbl = new DataTable();
            string strFirstRow = null;

            // this needs to be updated to handle quoted strings
            List<string> Rows = new List<string>();

            using (TextReader tr = new StreamReader(this.FilePath))
            {
                string str;
                int iRowCount = 0;

                if (MaxRecords <= 0)
                    MaxRecords = 2147483647;

                while ((str = tr.ReadLine()) != null && Rows.Count < MaxRecords)
                {
                    if (HasColumnNames && strFirstRow == null)
                    {
                        strFirstRow = str;
                        continue;
                    }

                    iRowCount++;

                    if (iRowCount < _startRow)
                        continue;

                    Rows.Add(str);
                }
            }

            if (Rows.Count <= 0)
                return tbl;

            if (string.IsNullOrEmpty(this.Delimiter))
                this.Delimiter = _defaultDelimiter;

            // Pipes need to be escaped.
            if (this.Delimiter == "|")
                this.Delimiter = "\\|";

            string regexString = "(?<=^|" + this.Delimiter + ")(\"(?:[^\"]|\"\")*\"|[^" + this.Delimiter + "]*)";
            Regex Splitter = new Regex(regexString, RegexOptions.Compiled);

            if (this.HasColumnNames)
            {
                List<string> FirstRow = this.UnescapeString(strFirstRow, Splitter);

                for (int i = 0; i < FirstRow.Count; i++)
                {
                    string columnName = FirstRow[i];

                    if (tbl.Columns.Contains(columnName))
                    {
                        int columnRepeat = 1;
                        string repeatColumnName = columnName + "_" + columnRepeat;

                        while (tbl.Columns.Contains(repeatColumnName))
                        {
                            columnRepeat++;
                            repeatColumnName = columnName + "_" + columnRepeat;
                        }

                        columnName = repeatColumnName;
                    }

                    tbl.Columns.Add(columnName);
                }
            }
            else
            {
                for (int i = 1; i <= this.UnescapeString(Rows[0], Splitter).Count; i++)
                {
                    tbl.Columns.Add("Column " + i);
                }
            }

            foreach (string str in Rows)
            {
                try
                {
                    tbl.Rows.Add(this.UnescapeString(str, Splitter).ToArray());
                }
                catch
                {

                }
            }

            return tbl;
        }

        private List<string> UnescapeString(string _Record, Regex _Splitter)
        {
            List<string> lst = new List<string>();

            foreach (Match match in _Splitter.Matches(_Record))
            {
                string str = match.Value.TrimStart(this.Delimiter.ToCharArray());

                if (this.TextHasQuotes)
                    str = str.Trim("\"".ToCharArray());

                lst.Add(str);
            }

            return lst;
        }
    }
    
    public class FixedWidthDataFileReader : DataFileReaderBase
    {
        private string[] _fieldData { get; set; }
        
        public override void SetSheetName(string _NewSheetName, bool _RefreshRowCount = false, bool _ValidateSheetName = true)
        {
            //only need a record count.
            if (_RefreshRowCount)
                this._RecordCount = File.ReadAllLines(this.FilePath).Length;
        }

        public FixedWidthDataFileReader(string[] fieldDataIn)
        {
            this._fieldData = fieldDataIn;
        }

        public override DataTable Read(int _startRow = 0)
        {
            //First, just read the first line to see how wide the row is.
            TextReader tr = File.OpenText(this.FilePath);
            string strBaseLine = tr.ReadLine();
            tr.Close();
            tr = null;

            int recordSize = strBaseLine.Length + Environment.NewLine.Length;

            //Calculate the byte at which I want to start
            long startPosition = Convert.ToInt64(recordSize) * Convert.ToInt64(_startRow);

            //Open a stream reader for the file
            StreamReader sr = new StreamReader(this.FilePath);
            sr.BaseStream.Position = startPosition;
            sr.DiscardBufferedData();

            //initialize my bucket to receive the bytes
            char[] buffer = new char[recordSize * this.MaxRecords];

            //read the rows into the buffer
            int countChars = sr.ReadBlock(buffer, 0, recordSize * this.MaxRecords);

            if (countChars == 0)
                return null;

            sr.Close();
            sr = null;

            //now just split the lines
            string[] lines = (new string(buffer)).TrimEnd().Split(new string[] { Environment.NewLine }, StringSplitOptions.None);

            if (lines.Length <= 1)
                lines = (new string(buffer)).TrimEnd().Split('\n');

            DataTable dtReturn = new DataTable();

            
            List<string> _lineData = this.GetLineDataFixedWidth(strBaseLine);

            for (int i = 0; i < _lineData.Count; i++)
            {
                if (this.HasColumnNames)
                {
                    if (this.TextHasQuotes)
                        dtReturn.Columns.Add(_lineData[i].Replace("\"", ""));
                    else
                        dtReturn.Columns.Add(_lineData[i]);
                }
                else
                    dtReturn.Columns.Add("Column " + i.ToString());
            }

            int curLine = 0;

            if (this.HasColumnNames && _startRow == 0)
                curLine = 1;

            while (lines.Length > curLine && lines[curLine] != null)
            {
                _lineData = this.GetLineDataFixedWidth(lines[curLine]);

                DataRow dr = dtReturn.NewRow();

                for (int i = 0; i < _lineData.Count; i++)
                {
                    if (dtReturn.Columns.Count > i)
                    {
                        if (this.TextHasQuotes)
                            dr[i] = _lineData[i].Replace("\"", "");
                        else
                            dr[i] = _lineData[i];
                    }
                }

                dtReturn.Rows.Add(dr);

                curLine++;

                if (curLine >= this.MaxRecords)
                    break;
            }

            return dtReturn;
        }

        private List<string> GetLineDataFixedWidth(string strLine)
        {
            List<string> lstValue = new List<string>();

            for (int i = 0; i < this._fieldData.Length; i++)
                lstValue.Add("");

            for (int i = 0; i < this._fieldData.Length; i++)
            {
                string _fieldDatum = this._fieldData[i];

                int _startPosition = Convert.ToInt32(_fieldDatum.Split(',').First());
                int _fieldLength = Convert.ToInt32(_fieldDatum.Split(',').Last());

                if (strLine.Length >= _startPosition + _fieldLength)
                    lstValue[i] = strLine.Substring(_startPosition, _fieldLength).Trim();
                else
                {
                    if (strLine.Length > _startPosition)
                        lstValue[i] = strLine.Substring(_startPosition).Trim();
                    else
                        lstValue[i] = "";
                }
            }

            return lstValue;
        }
    }

    public class XMLDataFileReader : DataFileReaderBase
    {
        public override void SetSheetName(string _NewSheetName, bool _RefreshRowCount = false, bool _ValidateSheetName = true)
        {
            //only need a record count.
            if (_RefreshRowCount)
            {
                XmlDocument doc = new XmlDocument();
                doc.Load(this.FilePath);

                this._RecordCount = doc.DocumentElement.ChildNodes.Count;
            }
        }

        public override DataTable Read(int _startRow = 0)
        {
            DataTable table = new DataTable();

            XmlDocument doc = new XmlDocument();
            doc.Load(this.FilePath);

            int counter = 0;

            foreach (XmlNode _rowNode in doc.DocumentElement.ChildNodes)
            {
                if (counter >= this.MaxRecords)
                    break;

                if (table.Columns.Count <= 0)
                {
                    table.TableName = System.Web.HttpUtility.HtmlDecode(_rowNode.Name);

                    foreach (XmlNode _childNode in _rowNode.ChildNodes)
                    {
                        table.Columns.Add(System.Web.HttpUtility.HtmlDecode(_childNode.Name));
                    }
                }

                if (counter >= _startRow)
                {
                    DataRow _dr = table.NewRow();

                    foreach (XmlNode _childNode in _rowNode.ChildNodes)
                        _dr[System.Web.HttpUtility.HtmlDecode(_childNode.Name)] = System.Web.HttpUtility.HtmlDecode(_childNode.InnerText);

                    table.Rows.Add(_dr);
                    counter++;
                }
            }

            return table;
        }
    }

    public class JsonDataFileReader : DataFileReaderBase
    {
        public override void SetSheetName(string _NewSheetName, bool _RefreshRowCount = false, bool _ValidateSheetName = true)
        {
            //only need a record count.
            if (_RefreshRowCount)
            {
                List<Dictionary<string, string>> _jsonObjects = JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(File.ReadAllText(this.FilePath));
                this._RecordCount = _jsonObjects.Count;
            }
        }

        public override DataTable Read(int _startRow = 0)
        {
            DataTable table = new DataTable();

            List<Dictionary<string, string>> _jsonObjects = JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(File.ReadAllText(this.FilePath));

            int counter = 0;

            foreach (Dictionary<string, string> _object in _jsonObjects)
            {
                if (counter >= this.MaxRecords)
                    break;

                foreach (string _propName in _object.Keys)
                {
                    if (!table.Columns.Contains(_propName))
                        table.Columns.Add(_propName);
                }

                if (counter >= _startRow)
                {
                    DataRow _drNew = table.NewRow();

                    foreach (KeyValuePair<string, string> _prop in _object)
                        _drNew[_prop.Key] = _prop.Value;

                    table.Rows.Add(_drNew);

                    counter++;
                }
            }

            return table;
        }
    }
}

Here is the DataFileWriterBase class:

using System.Collections.Generic;
using System.Data;
using System;
using System.IO;
using System.Linq;
using System.Text;
using ExcelLibrary.SpreadSheet;
using OfficeOpenXml;
using System.Globalization;
using System.Drawing;
using OfficeOpenXml.Style;
using System.Xml;
using Newtonsoft.Json;

namespace TKUtilities.DataAccess
{
    public abstract class DataFileWriterBase
    {
        protected FileInfo _fi { get; set; }
        public string FilePath { get; set; }
        public bool HasColumnNames { get; set; }
        public bool TextHasQuotes { get; set; }
        public string Delimiter { get; set; }

        public virtual void Write(DataTable table)
        {
            this._fi = new FileInfo(this.FilePath);

            if (!Directory.Exists(this._fi.Directory.FullName))
                Directory.CreateDirectory(this._fi.Directory.FullName);
        }

        protected string EscapeString(List<string> Fields)
        {
            StringBuilder sb = new StringBuilder();

            foreach (string str in Fields)
            {
                if (this.TextHasQuotes || str.IndexOf(this.Delimiter) >= 0)
                    sb.Append("\"" + str.Replace("\n", " ").Replace("\\", "").Replace("\"", "\"\"") + "\"");
                else
                    sb.Append(str.Replace("\n", " "));

                sb.Append(this.Delimiter);
            }

            return sb.ToString().Remove(sb.Length - this.Delimiter.Length);
        }

        public static DataFileWriterBase GetDataFileWriterBase(string FilePath)
        {
            switch (Path.GetExtension(FilePath).ToLower())
            {
                case ".xls":
                    return new XLSDataFileWriter { FilePath = FilePath };
                case ".xlsx":
                    return new XLSXDataFileWriter { FilePath = FilePath };
                case ".csv":
                    return new DelimitedDataFileWriter { FilePath = FilePath, Delimiter = ",", TextHasQuotes = true };
                case ".xml":
                    return new XMLDataFileWriter { FilePath = FilePath };
                case ".json":
                    return new JsonDataFileWriter { FilePath = FilePath };
                case ".txt":
                default:
                    return new DelimitedDataFileWriter { FilePath = FilePath, Delimiter = "\t" };
            }
        }

        public DataFileWriterBase()
        {
            this.FilePath = string.Empty;
            this.HasColumnNames = false;
            this.TextHasQuotes = false;
            this.Delimiter = string.Empty;
        }
    }

    public class XLSXDataFileWriter : DataFileWriterBase
    {
        public Dictionary<string, List<string>> ColumnDataTypes { get; set; }

        public override void Write(DataTable table)
        {
            base.Write(table);

            using (ExcelPackage pck = new ExcelPackage())
            {
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add(string.IsNullOrEmpty(table.TableName) ? "Sheet1" : table.TableName);
                ws.Cells["A1"].LoadFromDataTable(table, this.HasColumnNames);

                if (this.ColumnDataTypes != null
                    && this.ColumnDataTypes.Count > 0
                    && this.ColumnDataTypes.ContainsKey(table.TableName))
                {
                    for (int i = 0; i < this.ColumnDataTypes[table.TableName].Count; i++)
                    {
                        if (table.Columns.Count > i)
                        {
                            if (this.ColumnDataTypes[table.TableName][i] == "DateTime")
                                ws.Column(i).Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.FullDateTimePattern;
                        }
                    }
                }

                pck.SaveAs(this._fi);
            }
        }

        public void Write(DataSet workbookDataSet)
        {
            this._fi = new FileInfo(this.FilePath);

            if (!Directory.Exists(this._fi.Directory.FullName))
                throw new Exception("File path not found");
            
            using (ExcelPackage pck = new ExcelPackage())
            {
                foreach (DataTable table in workbookDataSet.Tables)
                {
                    string sheetName = string.IsNullOrEmpty(table.TableName) ? "Sheet" + pck.Workbook.Worksheets.Count + 1 : table.TableName;

                    ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName);

                    ws.Cells["A1"].LoadFromDataTable(table, this.HasColumnNames);

                    if (this.HasColumnNames)
                    {
                        for (int columnNum = 0; columnNum < this.ColumnDataTypes[table.TableName].Count; columnNum++)
                        {
                            ws.Cells[1, columnNum + 1].Style.Font.Bold = true;
                            ws.Cells[1, columnNum + 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
                            ws.Cells[1, columnNum + 1].Style.Fill.BackgroundColor.SetColor(Color.Silver);
                        }

                        ws.Cells[ws.Dimension.Address].AutoFilter = true;
                    }

                    if (this.ColumnDataTypes != null
                        && this.ColumnDataTypes.Count > 0
                        && this.ColumnDataTypes.ContainsKey(table.TableName))
                    {
                        int start = 0;

                        if (this.HasColumnNames)
                            start = 1;

                        for (int rowNum = 0; rowNum <= table.Rows.Count; rowNum++)
                        {
                            for (int columnNum = 0; columnNum < this.ColumnDataTypes[table.TableName].Count; columnNum++)
                            {
                                if (this.ColumnDataTypes[table.TableName][columnNum] == "DateTime")
                                {
                                    try
                                    {
                                        ws.Cells[rowNum + start + 1, columnNum + 1].Style.Numberformat.Format = "yyyy-mm-dd hh:mm:ss";
                                    }
                                    catch { }
                                }
                                else if (this.ColumnDataTypes[table.TableName][columnNum] == "Numeric")
                                {
                                    try
                                    {
                                        long longValue = 0;

                                        if (long.TryParse(table.Rows[rowNum][columnNum].ToString(), out longValue))
                                            ws.Cells[rowNum + start + 1, columnNum + 1].Style.Numberformat.Format = "#";

                                        if (longValue == 0)
                                        {
                                            ws.Cells[rowNum + start + 1, columnNum + 1].Value = 0;
                                            ws.Cells[rowNum + start + 1, columnNum + 1].Style.Numberformat.Format = "0";
                                        }
                                    }
                                    catch { }
                                }
                            }
                        }
                    }
                }

                pck.SaveAs(this._fi);
            }
        }
    }

    public class XLSDataFileWriter : DataFileWriterBase
    {
        public override void Write(DataTable table)
        {
            base.Write(table);

            Workbook workbook = new Workbook();

            Worksheet worksheet = new Worksheet(table.TableName);
            for (int i = 0; i < table.Columns.Count; i++)
            {
                worksheet.Cells[0, i] = new Cell(table.Columns[i].ColumnName);

                for (int j = 0; j < table.Rows.Count; j++)
                    worksheet.Cells[j + 1, i] = new Cell(table.Rows[j][i].ToString());
            }
            workbook.Worksheets.Add(worksheet);

            workbook.Save(this.FilePath);
        }
    }

    public class FixedFieldDataFileWriter : DataFileWriterBase
    {
        private string[] _fieldData { get; set; }
        private TextWriter _tw;

        public FixedFieldDataFileWriter(string[] fieldDataIn)
        {
            this._fieldData = fieldDataIn;
        }

        public override void Write(DataTable table)
        {
            base.Write(table);

            this._tw = File.AppendText(this.FilePath);

            int maxLength = 0;

            foreach (string _fieldDatum in this._fieldData)
            {
                int _startPosition = Convert.ToInt32(_fieldDatum.Split(',').First());
                int _fieldLength = Convert.ToInt32(_fieldDatum.Split(',').Last());

                if (_startPosition + _fieldLength > maxLength)
                    maxLength = _startPosition + _fieldLength;
            }

            List<string> columns = new List<string>();

            if (this.HasColumnNames)
            {
                foreach (DataColumn _dc in table.Columns)
                    columns.Add(_dc.ColumnName);

                this.WriteFixedLine(columns.ToArray(), maxLength);
            }

            foreach (DataRow _dr in table.Rows)
                this.WriteFixedLine(_dr.ItemArray, maxLength);

            this._tw.Close();    
        }

        private void WriteFixedLine(object[] values, int maxLength)
        {
            string strLine = string.Empty;
            strLine = strLine.PadRight(maxLength, ' ');

            for (int i = 0; i < values.Length; i++)
            {
                if (i >= this._fieldData.Length)
                    break;

                string strData = values[i].ToString();

                if (TextHasQuotes)
                    strData = "\"" + strData.Replace("\"", "\\\"").Replace("\n", " ") + "\"";

                int _startPosition = Convert.ToInt32(this._fieldData[i].Split(',').First());
                int _fieldLength = Convert.ToInt32(this._fieldData[i].Split(',').Last());

                strData = strData.PadRight(_fieldLength, ' ').Substring(0, _fieldLength);
                strLine = strLine.Insert(_startPosition, strData);

                strLine = strLine.Substring(0, maxLength);
            }

            this._tw.WriteLine(strLine);
        }
    }

    public class XMLDataFileWriter : DataFileWriterBase
    {
        public override void Write(DataTable table)
        {
            base.Write(table);

            XmlDocument doc = new XmlDocument();

            doc.LoadXml("<data/>");

            foreach (DataRow _dr in table.Rows)
            {
                XmlNode _rowNode = null;
                    
                if (!string.IsNullOrEmpty(table.TableName))
                    _rowNode = doc.CreateElement(table.TableName);
                else
                    _rowNode = doc.CreateElement("row");

                foreach (DataColumn _dc in table.Columns)
                {
                    XmlNode _valueNode = doc.CreateElement(_dc.ColumnName.Replace(' ', '_').Replace('+', '_').Replace('/', '_').Replace('\'', '_').Replace('&', '_').Replace(',', '_'));
                    _valueNode.InnerText = System.Web.HttpUtility.HtmlEncode(_dr[_dc.ColumnName].ToString());

                    _rowNode.AppendChild(_valueNode);
                }

                doc.DocumentElement.AppendChild(_rowNode);
            }

            doc.Save(this.FilePath);
        }
    }

    public class JsonDataFileWriter : DataFileWriterBase
    {
        public override void Write(DataTable table)
        {
            base.Write(table);

            List<Dictionary<string, string>> _jsonObjects = new List<Dictionary<string, string>>();
            
            foreach (DataRow _dr in table.Rows)
            {
                Dictionary<string, string> _object = new Dictionary<string, string>();

                foreach (DataColumn _dc in table.Columns)
                    _object.Add(_dc.ColumnName, _dr[_dc].ToString());

                _jsonObjects.Add(_object);
            }

            File.WriteAllText(this.FilePath, JsonConvert.SerializeObject(_jsonObjects));
        }
    }

    public class DelimitedDataFileWriter : DataFileWriterBase
    {
        public override void Write(DataTable table)
        {
            base.Write(table);

            StringBuilder sb = new StringBuilder();

            if (this.HasColumnNames)
            {
                List<string> columnNames = table.Columns.Cast<DataColumn>().Select(column => column.ColumnName).ToList();
                sb.AppendLine(this.EscapeString(columnNames));
            }

            foreach (DataRow row in table.Rows)
            {
                List<string> fields = row.ItemArray.Select(field => field.ToString()).ToList();
                sb.AppendLine(this.EscapeString(fields));
            }

            File.WriteAllText(this.FilePath, sb.ToString());
        }
    }
}
Advertisements