using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Xml;
using System.IO;


namespace OpenXMLWindowsApp
{
    class OpenXMLHelper
    {

        public static SpreadsheetDocument OpenSpreadSheetForUpdate(string docName)
        {
            return SpreadsheetDocument.Open(docName, true);
        }


        public static void CloseSpreadSheet(SpreadsheetDocument spreadSheet)
        {
            spreadSheet.Close();
        }


        public static void UpdateCell(SpreadsheetDocument spreadSheet, 
                                      string sheetName,
                                      string text,
                                      uint rowIndex,
                                      string columnName,
                                      bool createCell)
        {
            WorksheetPart worksheetPart = GetWorksheetPartByName(spreadSheet, sheetName);

            if (worksheetPart != null)
            {
                Cell cell = GetCell(worksheetPart.Worksheet, columnName, rowIndex, createCell);

                if (cell != null)
                {
                    cell.CellValue = new CellValue(text);
                    cell.DataType = new EnumValue<CellValues>(CellValues.String);
                }

                // Save the worksheet.
                worksheetPart.Worksheet.Save();
            }
            else
            {
                throw new Exception("Worksheet not found in spreadsheet document");
            }

        }

        public static WorksheetPart GetWorksheetPartByName(SpreadsheetDocument document, string sheetName)
        {
            IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName);

            if (sheets.Count() == 0)
            {
                // The specified worksheet does not exist.
                // todo: create a new sheet

                return null;
            }

            string relationshipId = sheets.First().Id.Value;
            WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
            return worksheetPart;
        }


        // Given a worksheet, a column name, and a row index, gets the cell at the specified column and 
        public static Cell GetCell(Worksheet worksheet, 
                                   string columnName, 
                                   uint rowIndex,
                                   bool createCell)
        {
            Row row = GetRow(worksheet, rowIndex, createCell);

            Cell cell = null;

            if (row.Elements<Cell>().Where(c => string.Compare(c.CellReference.Value, columnName + rowIndex, true) == 0).Count() > 0)
            {
                cell = row.Elements<Cell>().Where(c => string.Compare(c.CellReference.Value, columnName + rowIndex, true) == 0).First();
            }
            else if (createCell)
            {
                // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                Cell refCell = null;
                string cellReference = columnName + rowIndex;

                foreach (Cell ecell in row.Elements<Cell>())
                {
                    if (string.Compare(ecell.CellReference.Value, cellReference, true) > 0)
                    {
                        refCell = ecell;
                        break;
                    }
                }

                cell = new Cell() { CellReference = cellReference };
                row.InsertBefore(cell, refCell);
                worksheet.Save();
            }

            return cell;
        }


        // Given a worksheet and a row index, return the row.
        public static Row GetRow(Worksheet worksheet, 
                                 uint rowIndex,
                                 bool createCell)
        {
            Row row = null;

            if (worksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() > 0)
            {
                row = worksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
            }
            else if (createCell)
            {
                row = new Row() { RowIndex = rowIndex };
                worksheet.GetFirstChild<SheetData>().Append(row);
            }

            return row;
        }


        // Append a blank worksheet to the end of the Workbook
        public static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
        {
            // Add a new worksheet part to the workbook.
            WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            newWorksheetPart.Worksheet = new Worksheet(new SheetData());
            newWorksheetPart.Worksheet.Save();

            Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
            string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);

            // Get a unique ID for the new sheet.
            uint sheetId = 1;
            if (sheets.Elements<Sheet>().Count() > 0)
            {
                sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
            }

            string sheetName = "Sheet" + sheetId;

            // Append the new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
            sheets.Append(sheet);
            workbookPart.Workbook.Save();

            return newWorksheetPart;
        }


        // remove all values of cells with formulas on a sheet so that Excel refreshes the them upon Open
        public static void ClearAllValuesInSheet(SpreadsheetDocument spreadSheet, string sheetName)
        {
            WorksheetPart worksheetPart = GetWorksheetPartByName(spreadSheet, sheetName);

            foreach (Row row in worksheetPart.Worksheet.GetFirstChild<SheetData>().Elements<Row>())
            {
                foreach (Cell cell in row.Elements<Cell>())
                {
                    if (cell.CellFormula != null && cell.CellValue != null)
                    {
                        cell.CellValue.Remove();
                    }
                }

            }

            worksheetPart.Worksheet.Save();
        }


    }
}