FCC/Applications/Share/dotnet_WayneLib_WayneLibraries/IO/ExcelFile.cs

using System;
using System.Collections.Generic;
using System.IO;
using System.Text;

namespace Wayne.Lib.IO
{
    /// <summary>
    /// Class that converts a string-table to and from the CSV format.
    /// </summary>
    public class ExcelFile
    {
        #region Fields

        private readonly IFileSupport fileSupport;

        #endregion

        #region Construction

        /// <summary>
        /// Constructor.
        /// </summary>
        /// <param name="serviceLocator">The service locator.</param>
        public ExcelFile(IServiceLocator serviceLocator)
        {
            fileSupport = serviceLocator.GetService<IFileSupport>();
        }

        #endregion

        #region Properties

        /// <summary>
        /// The file extension.
        /// </summary>
        public const string FileExtension = ".csv";

        /// <summary>
        /// The actual rows.
        /// </summary>
        public readonly List<List<string>> Rows = new List<List<string>>();

        #endregion

        #region Methods

        /// <summary>
        /// Save the current rows to a CSV file.
        /// </summary>
        /// <param name="fileName">The name of the file. The extension "CSV" will be added if missing.</param>
        public void SaveToFile(string fileName)
        {
            if (!Path.GetExtension(fileName).Equals(FileExtension, StringComparison.CurrentCultureIgnoreCase))
                fileName += FileExtension;
            using (Stream stream = fileSupport.Open(fileName, FileMode.Create, FileAccess.Write, FileShare.None))
            {
                using (StreamWriter streamWriter = new StreamWriter(stream))
                {
                    foreach (List<string> row in Rows)
                        streamWriter.WriteLine(GetCsvRowString(row));
                }
            }
        }

        /// <summary>
        /// Fill the rows with the content of the CSV file.
        /// </summary>
        /// <param name="fileName">The name of the file.</param>
        public void LoadFromFile(string fileName)
        {
            Rows.Clear();
            using (Stream stream = fileSupport.Open(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                using (StreamReader streamReader = new StreamReader(stream))
                {
                    while (!streamReader.EndOfStream)
                    {
                        Rows.Add(GetCsvRowStringList(streamReader.ReadLine()));
                    }
                }
            }
        }

        private static string GetCsvRowString(IList<string> row)
        {
            List<string> stringItems = new List<string>();
            for (int i = 0; i < row.Count; i++)
            {
                if (row[i] != null)
                {
                    string stringItem = row[i].Replace("\"", "\"\"").Replace("\r", "").Replace("\n", "");
                    if ((stringItem.IndexOf(',') > -1) || (stringItem.IndexOf(';') > -1) ||
                        (stringItem.IndexOf('"') > -1) ||
                        stringItem.StartsWith(" ") || stringItem.EndsWith(" ") ||
                        stringItem.StartsWith("\t") || stringItem.EndsWith("\t"))
                    {
                        stringItems.Add(string.Concat("\"", stringItem, "\""));
                    }
                    else
                        stringItems.Add(stringItem);
                }
            }
            return string.Join(";", stringItems.ToArray());
        }

        private static List<string> GetCsvRowStringList(string row)
        {
            List<string> stringItems = new List<string>();
            bool inString = false;
            StringBuilder currentCell = new StringBuilder();
            for (int i = 0; i < row.Length; i++)
            {
                char thisChar = row[i];
                char nextChar = i < row.Length - 1 ? row[i + 1] : '\0';
                if (thisChar == '"')
                {
                    if (inString)
                    {
                        if (nextChar == '"')
                        {
                            // Found a ...""... symbol
                            currentCell.Append('"');
                            i++;
                        }
                        else
                            inString = false;
                    }
                    else
                        inString = true;
                }
                else if (thisChar == ';')
                {
                    if (inString)
                        currentCell.Append(thisChar);
                    else
                    {
                        stringItems.Add(currentCell.ToString());
                        currentCell.Length = 0;
                    }
                }
                else
                    currentCell.Append(thisChar);
            }
            stringItems.Add(currentCell.ToString());
            return stringItems;
        }

        #endregion
    }
}