using System;
using System.Collections.Generic;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Xml;
using System.Xml.Serialization;

namespace ExcelZipLib
{
   public class Workbook
   {
      /// <summary>
      /// The shared strings
      /// </summary>
      public static sst SharedStrings;

      /// <summary>
      /// All worksheets in the Excel workbook deserialized
      /// </summary>
      /// <param name="ExcelFileName">Full path and filename of the Excel xlsx-file</param>
      /// <returns></returns>
      public static List<worksheet> Worksheets(string ExcelFileName)
      {
         worksheet ws;
         List<worksheet> allWorkSheets = new List<worksheet>();

         using (ZipArchive zipArchive = ZipFile.Open(ExcelFileName, ZipArchiveMode.Read))
         {
            SharedStrings = DeserializedZipEntry<sst>(GetZipArchiveEntry(zipArchive, @"xl/sharedStrings.xml"));

            foreach (var worksheetEntry in (WorkSheetFileNames(zipArchive)).OrderBy(x => x.FullName))
            {
               ws = DeserializedZipEntry<worksheet>(worksheetEntry);

               ws.NumberOfColumns = worksheet.MaxColumnIndex + 1;

               ws.ExpandRows();

               allWorkSheets.Add(ws);
            }
            return allWorkSheets;
         }
      }

      /// <summary>
      /// Method converting an Excel cell value to a date
      /// </summary>
      /// <param name="ExcelCellValue"></param>
      /// <returns></returns>
      public static DateTime DateFromExcelFormat(string ExcelCellValue)
      {
         return DateTime.FromOADate(Convert.ToDouble(ExcelCellValue));
      }

      /// <summary>
      /// Gets the zip archive entry.
      /// </summary>
      /// <param name="ZipArchive">The zip archive.</param>
      /// <param name="ZipEntryName">Name of the zip entry.</param>
      /// <returns></returns>
      private static ZipArchiveEntry GetZipArchiveEntry(ZipArchive ZipArchive, string ZipEntryName)
      {
         return ZipArchive.Entries.First<ZipArchiveEntry>(n => n.FullName.Equals(ZipEntryName));
      }

      /// <summary>
      /// Works the sheet file names.
      /// </summary>
      /// <param name="ZipArchive">The zip archive.</param>
      /// <returns></returns>
      private static IEnumerable<ZipArchiveEntry> WorkSheetFileNames(ZipArchive ZipArchive)
      {
         foreach (var zipEntry in ZipArchive.Entries)
            if (zipEntry.FullName.StartsWith("xl/worksheets/sheet"))
               yield return zipEntry;
      }

      /// <summary>
      /// Deserializeds the zip entry.
      /// </summary>
      /// <typeparam name="T"></typeparam>
      /// <param name="ZipArchiveEntry">The zip archive entry.</param>
      /// <returns></returns>
      private static T DeserializedZipEntry<T>(ZipArchiveEntry ZipArchiveEntry)
      {
         using (Stream stream = ZipArchiveEntry.Open())
            return (T)new XmlSerializer(typeof(T)).Deserialize(XmlReader.Create(stream));
      }
   }
}