///<summary>Inserts one Sheet into the database.  Provides option to use the existing priKey.</summary>
 internal static long Insert(Sheet sheet,bool useExistingPK)
 {
     if(!useExistingPK && PrefC.RandomKeys) {
         sheet.SheetNum=ReplicationServers.GetKey("sheet","SheetNum");
     }
     string command="INSERT INTO sheet (";
     if(useExistingPK || PrefC.RandomKeys) {
         command+="SheetNum,";
     }
     command+="SheetType,PatNum,DateTimeSheet,FontSize,FontName,Width,Height,IsLandscape,InternalNote,Description,ShowInTerminal,IsWebForm) VALUES(";
     if(useExistingPK || PrefC.RandomKeys) {
         command+=POut.Long(sheet.SheetNum)+",";
     }
     command+=
              POut.Int   ((int)sheet.SheetType)+","
         +    POut.Long  (sheet.PatNum)+","
         +    POut.DateT (sheet.DateTimeSheet)+","
         +    POut.Float (sheet.FontSize)+","
         +"'"+POut.String(sheet.FontName)+"',"
         +    POut.Int   (sheet.Width)+","
         +    POut.Int   (sheet.Height)+","
         +    POut.Bool  (sheet.IsLandscape)+","
         +"'"+POut.String(sheet.InternalNote)+"',"
         +"'"+POut.String(sheet.Description)+"',"
         +    POut.Byte  (sheet.ShowInTerminal)+","
         +    POut.Bool  (sheet.IsWebForm)+")";
     if(useExistingPK || PrefC.RandomKeys) {
         Db.NonQ(command);
     }
     else {
         sheet.SheetNum=Db.NonQ(command,true);
     }
     return sheet.SheetNum;
 }

		public void UpdateSupport(Sheet supported) {
			// Update the support sets of cells referred from an array formula only once
			if (!supportAdded) {
				formula.AddToSupportSets(supported, formulaCol, formulaRow, 1, 1);
				supportAdded = true;
			}
		}

		public override void InsertRowCols(Dictionary<Expr, Adjusted<Expr>> adjusted,
										   Sheet modSheet,
										   bool thisSheet,
										   int R,
										   int N,
										   int r,
										   bool doRows) {}

		public abstract void InsertRowCols(Dictionary<Expr, Adjusted<Expr>> adjusted,
										   Sheet modSheet,
										   bool thisSheet,
										   int R,
										   int N,
										   int r,
										   bool doRows);

        public static bool SetCellValue(this SpreadsheetDocument document, Sheet metadataSheet, string addressName, string value)
        {
            // If the string exists in the shared string table, get its index.
            // If the string doesn't exist in the shared string table, add it and get the next index.
            // Assume failure.
            bool returnValue = false;

            // Open the document for editing.
            WorkbookPart wbPart = document.WorkbookPart;

            if (metadataSheet != null)
            {
                Worksheet ws = ((WorksheetPart)(wbPart.GetPartById(metadataSheet.Id))).Worksheet;

                Cell theCell = ws.InsertCellInWorksheet(addressName);

                // Either retrieve the index of an existing string,
                // or insert the string into the shared string table
                // and get the index of the new item.
                int stringIndex = wbPart.InsertSharedStringItem(value);

                theCell.CellValue = new CellValue(stringIndex.ToString());
                theCell.DataType = new EnumValue<CellValues>(CellValues.SharedString);

                // Save the worksheet.
                ws.Save();
                returnValue = true;
            }
            return returnValue;
        }

		public override bool RemoveCell(SupportSet set, Sheet sheet, int col, int row) {
			if (Contains(sheet, col, row)) {
				// To exclude cell at sheet[col, row], split into up to 4 support ranges
				if (rowInt.min < row) // North, column above [col,row]
				{
					set.Add(Make(sheet, new Interval(col, col), new Interval(rowInt.min, row - 1)));
				}
				if (row < rowInt.max) // South, column below [col,row]
				{
					set.Add(Make(sheet, new Interval(col, col), new Interval(row + 1, rowInt.max)));
				}
				if (colInt.min < col) // West, block to the left of [col,row]
				{
					set.Add(Make(sheet, new Interval(colInt.min, col - 1), rowInt));
				}
				if (col < colInt.max) // East, block to the right of [col,row]
				{
					set.Add(Make(sheet, new Interval(col + 1, colInt.max), rowInt));
				}
				return true;
			}
			else {
				return false;
			}
		}

		public void RemoveFromSupportSets(Sheet sheet, int col, int row) {
			// Update the support sets of cells referred from an array formula only once
			if (!supportRemoved) {
				formula.RemoveFromSupportSets(sheet, col, row);
				supportRemoved = true;
			}
		}

 public void TextCellsAreStored()
 {
     var sheet = new Sheet();
     const string theCell = "A21";
     sheet.Put(theCell, "A string");
     Assert.That(sheet.Get(theCell), Is.EqualTo("A string"));
 }

        /**
         * Called when a column is inserted on the specified sheet.  Notifies all
         * RCIR cells of this change. The default implementation here does nothing
         *
         * @param s the sheet on which the column was inserted
         * @param sheetIndex the sheet index on which the column was inserted
         * @param col the column number which was inserted
         */
        public override void columnInserted(Sheet s, int sheetIndex, int col)
        {
            try
                {
                if (parser == null)
                    {
                    byte[] formulaData = formula.getFormulaData();
                    byte[] formulaBytes = new byte[formulaData.Length - 16];
                    System.Array.Copy(formulaData, 16,
                                     formulaBytes, 0, formulaBytes.Length);
                    parser = new FormulaParser(formulaBytes,
                                               this,
                                               getSheet().getWorkbook(),
                                               getSheet().getWorkbook(),
                                               getSheet().getWorkbookSettings());
                    parser.parse();
                    }

                parser.columnInserted(sheetIndex, col, s == getSheet());
                }
            catch (FormulaException e)
                {
                //logger.warn("cannot insert column within formula:  " + e.Message);
                }
        }

        public static void CreateSpreadsheetWorkbook(string filepath)
        {
            // Create a spreadsheet document by supplying the filepath.
            // By default, AutoSave = true, Editable = true, and Type = xlsx.
            SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
                Create(filepath, SpreadsheetDocumentType.Workbook);

            // Add a WorkbookPart to the document.
            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            workbookpart.Workbook = new Workbook();

            // Add a WorksheetPart to the WorkbookPart.
            WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            // Add Sheets to the Workbook.
            Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
                AppendChild<Sheets>(new Sheets());

            // Append a new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet()
            {
                Id = spreadsheetDocument.WorkbookPart.
                    GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name = "mySheet"
            };
            sheets.Append(sheet);

            workbookpart.Workbook.Save();

            // Close the document.
            spreadsheetDocument.Close();

        }

        public void AddNewWorksheet()
        {
            WorkbookPart workbookPart = spreadSheet.WorkbookPart;

            WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            newWorksheetPart.Worksheet = new Worksheet(new SheetData());
            newWorksheetPart.Worksheet.Save();
            CurrentWorksheetPart = newWorksheetPart;
            //workbookPart.SharedStringTablePart.SharedStringTable.Count = workbookPart.SharedStringTablePart.SharedStringTable.Count + 1;
            //workbookPart.SharedStringTablePart.SharedStringTable.UniqueCount = workbookPart.SharedStringTablePart.SharedStringTable.UniqueCount + 1;
            string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
            Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
            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();
        }

        private static void InsertWorksheet(string docName)
        {
            // Open the document for editing.
            using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
            {
                // Add a blank WorksheetPart.
                WorksheetPart newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>();
                newWorksheetPart.Worksheet = new Worksheet(new SheetData());

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

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

                // Give the new worksheet a name.
                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);
            }
        }

        public static void OpenAndAddToSpreadsheetStream(Stream stream)
        {
            // Open a SpreadsheetDocument based on a stream.
            SpreadsheetDocument spreadsheetDocument =
                SpreadsheetDocument.Open(stream, true);

            // Add a new worksheet.
            WorksheetPart newWorksheetPart = spreadsheetDocument.WorkbookPart.AddNewPart<WorksheetPart>();
            newWorksheetPart.Worksheet = new Worksheet(new SheetData());
            newWorksheetPart.Worksheet.Save();

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

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

            // Give the new worksheet a name.
            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);
            spreadsheetDocument.WorkbookPart.Workbook.Save();

            // Close the document handle.
            spreadsheetDocument.Close();

            // Caller must close the stream.
        }

        /**
         * Constructs this object from the raw data
         *
         * @param t the raw data
         * @param s the sheet
         */
        public MergedCellsRecord(Record t, Sheet s)
            : base(t)
        {
            byte[] data = getRecord().getData();

            int numRanges = IntegerHelper.getInt(data[0],data[1]);

            ranges = new Range[numRanges];

            int pos = 2;
            int firstRow = 0;
            int lastRow = 0;
            int firstCol = 0;
            int lastCol = 0;

            for (int i = 0; i < numRanges; i++)
                {
                firstRow = IntegerHelper.getInt(data[pos],data[pos + 1]);
                lastRow = IntegerHelper.getInt(data[pos + 2],data[pos + 3]);
                firstCol = IntegerHelper.getInt(data[pos + 4],data[pos + 5]);
                lastCol = IntegerHelper.getInt(data[pos + 6],data[pos + 7]);

                ranges[i] = new SheetRangeImpl(s,firstCol,firstRow,
                                               lastCol,lastRow);

                pos += 8;
                }
        }

		public override void InsertRowCols(Dictionary<Expr, Adjusted<Expr>> adjusted,
										   Sheet modSheet,
										   bool thisSheet,
										   int R,
										   int N,
										   int r,
										   bool doRows) {
			Adjusted<Expr> ae;
			if (adjusted.ContainsKey(e) && r < adjusted[e].upper) {
				// There is a valid cached adjusted expression
				ae = adjusted[e];
			}
			else {
				// Compute a new adjusted expression and insert into the cache
				ae = e.InsertRowCols(modSheet, thisSheet, R, N, r, doRows);
				Console.WriteLine("Making new adjusted at rowcol " + r
								  + "; upper = " + ae.upper);
				if (ae.same) { // For better sharing, reuse unadjusted e if same
					ae = new Adjusted<Expr>(e, ae.upper, ae.same);
					Console.WriteLine("Reusing expression");
				}
				adjusted[e] = ae;
			}
			Debug.Assert(r < ae.upper, "Formula.InsertRowCols");
			e = ae.e;
		}

 // Mark cell, dirty if non-empty
 public static void MarkCellDirty(Sheet sheet, int col, int row)
 {
     // Console.WriteLine("MarkDirty({0})", new FullCellAddr(sheet, col, row));
       Cell cell = sheet[col, row];
       if (cell != null)
     cell.MarkDirty();
 }

    // Use this for initialization
    void Start()
    {
        sheetsDirectoryPath = Application.dataPath + "/../Fiches";
        System.IO.Path.GetFullPath(sheetsDirectoryPath);
        sheetsPath = System.IO.Directory.GetFiles(sheetsDirectoryPath, "*.xml", System.IO.SearchOption.AllDirectories);

        currentSheet= new Sheet(sheetsPath[0]);
    }

 public void ReplaceSortOrder(Sheet sheet, int oldSortOrder, int newSortOrder)
 {
     this._dbContext.Database.ExecuteSqlCommand(
         "UPDATE SheetEntry SET SortOrder = @newSortOrder WHERE SortOrder = @oldSortOrder WHERE Sheet_Id = @sheetId",
         new SqlParameter("newSortOrder", newSortOrder),
         new SqlParameter("oldSortOrder", oldSortOrder),
         new SqlParameter("sheetId", sheet.Id));
 }

		public Sheet Convert()
		{
			Sheet result = new Sheet();
			result.Width = SizeB;
			result.Height = SizeA;
			result.Thickness = Thickness;
			return result;
		}

 /// <summary>
 /// Создает экземпляр класса для работы с текущим файлом
 /// </summary>
 /// <param name="filePath">Путь к документу</param>
 /// <param name="removeAfterDestroy">Удалять ли файл после окончания работы с ним</param>
 public ExcellWorker(string filePath, bool removeAfterDestroy)
 {
     _currentFilePath = filePath;
     _currentDocument = SpreadsheetDocument.Open(filePath, true);
     _currentWorkBookPart = _currentDocument.WorkbookPart;
     _currentSheet = _currentWorkBookPart.Workbook.Descendants<Sheet>().FirstOrDefault();
     RemoveAfterDestroy = removeAfterDestroy;
 }