using DocumentFormat.OpenXml;

using DocumentFormat.OpenXml.Packaging;

using DocumentFormat.OpenXml.Spreadsheet;

using System;

using System.Collections.Generic;

using System.IO;

using System.Linq;

using System.Text;

using System.Text.RegularExpressions;

using System.Threading.Tasks;

namespace EArchivePermissionTool

{

    public class ExcelDataReader

    {

        private bool mIsCheck { get; set; }

        public ExcelDataReader(bool mIsCheck)

        {

            this.mIsCheck = mIsCheck;

        }

        public Dictionary<string, List<List<string>>> GetWholeSheets(Stream stream)

        {

            Dictionary<string, List<List<string>>> result = null;

            try

            {

                using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(stream, false))

                {

                    result = GetWholeSheets(spreadsheetDocument);

                }

            }

            catch

            {

            }

            finally

            {

                if (!mIsCheck && stream != null)

                {

                    stream.Dispose();

                }

            }

            return result;

        }

        private Dictionary<string, List<List<string>>> GetWholeSheets(SpreadsheetDocument spreadsheetDocument)

        {

            var data = new Dictionary<string, List<List<string>>>();

            WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;

            foreach (var worksheetInfo in workbookPart.Workbook.Descendants<Sheet>())

            {

                if (worksheetInfo.State != null && worksheetInfo.State == SheetStateValues.Hidden)

                {

                    continue;

                }

                string workSheetName = worksheetInfo.Name;

                var sheetData = GetSheetData(workbookPart, (WorksheetPart)workbookPart.GetPartById(worksheetInfo.Id));

                data.Add(workSheetName, sheetData);

            }

            return data;

        }

        private List<List<string>> GetSheetData(WorkbookPart workbookPart, WorksheetPart worksheetPart)

        {

            if (worksheetPart == null)

            {

                throw new Exception("Out of range.");

            }

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

            OpenXmlReader reader = OpenXmlReader.Create(worksheetPart, true);

            var rows = worksheetPart.Worksheet.Descendants<Row>();

            uint rowIndex = ;

            int rowIndexForCheck = ;

            foreach (var row in rows)

            {

                if (row.HasChildren)

                {

                    var currentRowIndex = row.RowIndex.Value;

                    while (currentRowIndex > rowIndex)

                    {

                        result.Add(new List<string>());

                        ++rowIndex;

                        if (mIsCheck)

                        {

                            ++rowIndexForCheck;

                            if (rowIndexForCheck == )

                            {

                                rowIndexForCheck = ;

                                break;

                            }

                        }

                    }

                    int columnIndex = ;

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

                    foreach (Cell cell in row.Descendants<Cell>())

                    {

                        if (cell.CellReference != null)

                        {

                            // Gets the column index of the cell with data

                            int cellColumnIndex = (int)GetColumnIndexFromName(GetColumnName(cell.CellReference));

                            if (columnIndex < cellColumnIndex)

                            {

                                do

                                {

                                    l.Add(string.Empty);//Insert blank data here;

                                    columnIndex++;

                                }

                                while (columnIndex < cellColumnIndex);

                            }

                        }

                        l.Add(GetCellValue(workbookPart, cell));

                        columnIndex++;

                    }

                    //Changed by EArchive

                    if (!string.IsNullOrEmpty(l[]))

                    {

                        result.Add(l);

                    }

                    ++rowIndex;

                    ++rowIndexForCheck;

                    if (mIsCheck && rowIndexForCheck == )

                    {

                        break;

                    }

                }

            }

            return result;

        }

        /// <summary>

        /// Given a cell name, parses the specified cell to get the column name.

        /// </summary>

        /// <param name="cellReference">Address of the cell (ie. B2)</param>

        /// <returns>Column Name (ie. B)</returns>

        public static string GetColumnName(string cellReference)

        {

            // Create a regular expression to match the column name portion of the cell name.

            Regex regex = new Regex("[A-Za-z]+");

            Match match = regex.Match(cellReference);

            return match.Value;

        }

        /// <summary>

        /// Given just the column name (no row index), it will return the zero based column index.

        /// Note: This method will only handle columns with a length of up to two (ie. A to Z and AA to ZZ).

        /// A length of three can be implemented when needed.

        /// </summary>

        /// <param name="columnName">Column Name (ie. A or AB)</param>

        /// <returns>Zero based index if the conversion was successful; otherwise null</returns>

        public static int? GetColumnIndexFromName(string columnName)

        {

            Regex alpha = new Regex("^[A-Z]+$");

            if (!alpha.IsMatch(columnName)) throw new ArgumentException();

            char[] colLetters = columnName.ToCharArray();

            Array.Reverse(colLetters);

            int convertedValue = ;

            for (int i = ; i < colLetters.Length; i++)

            {

                char letter = colLetters[i];

                int current = i ==  ? letter -  : letter - ; // ASCII 'A' = 65

                convertedValue += current * (int)Math.Pow(, i);

            }

            return convertedValue;

        }

        private string GetCellValue(WorkbookPart workbookPart, Cell c)

        {

            string cellValue = "";

            if (c.CellValue == null)

            {

                return cellValue;

            }

            if (c.DataType != null && c.DataType == CellValues.SharedString)

            {

                SharedStringItem ssi = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(int.Parse(c.CellValue.InnerText));

                cellValue = ssi.InnerText;

            }

            else

            {

                cellValue = c.CellValue.InnerText;

            }

            return cellValue.Trim();

        }

    }

}