private void CreateColumnCopies(uint colFromIdx, int numOfColumns, Func <CellProxy, CellProxy> fnCreate)
        {
            Action <uint, uint> actionCopyColumn = (rowIdx, colIdx) =>
            {
                RowColumn rcOld = new RowColumn()
                {
                    Row = rowIdx, Column = colIdx
                };
                if (rcOld.Column == colFromIdx)
                {
                    foreach (var newColIdx in Enumerable.Range((int)colFromIdx + 1, numOfColumns))
                    {
                        RowColumn rcNew = new RowColumn()
                        {
                            Row = rcOld.Row, Column = (uint)newColIdx
                        };
                        CellProxy cOld = GetCell(rcOld.Row, rcOld.Column);
                        if (cOld != null)
                        {
                            CellProxy cNew = fnCreate(cOld);
                            if (cNew != null)
                            {
                                AddCellToCache(cNew, rcNew.Row, rcNew.Column);
                            }
                        }
                    }
                }
            };

            LoopCells(actionCopyColumn);
        }
 public ExcelCell this[string address]
 {
     get
     {
         RowColumn rc = ExcelAddress.ToRowColumn(address);
         return(this[rc.Row, rc.Column]);
     }
 }
        public static RowColumn CacheIndexToRowCol(ulong cacheIdx)
        {
            RowColumn rc = new RowColumn();

            // Note: 0-indexed --> 1-indexed
            rc.Row    = (uint)(cacheIdx / (ulong)ExcelConstraints.MaxColumns) + 1;
            rc.Column = (uint)(cacheIdx % (ulong)ExcelConstraints.MaxColumns) + 1;
            return(rc);
        }
        private IEnumerable <OpenXmlAttribute> EnumCellProxyAttributes(uint row, uint col, CellProxy cellProxy)
        {
            yield return(new OpenXmlAttribute(null, "r", null, RowColumn.ToAddress(row, col)));

            if (cellProxy.DataType != null)
            {
                yield return(new OpenXmlAttribute(null, "t", null, STCellType((CellValues)cellProxy.DataType)));
            }
            if (cellProxy.StyleIndex != null)
            {
                yield return(new OpenXmlAttribute(null, "s", null, cellProxy.StyleIndex.Value.ToString()));
            }
        }
        private void CreateRowCopies(uint rowFromIdx, int numOfRows, Func <CellProxy, CellProxy> fnCreate)
        {
            SortedList <uint, CellProxy> rowCache;

            if (_cachedCells.TryGetValue(rowFromIdx, out rowCache))
            {
                Row cachedRow;
                _cachedRows.TryGetValue(rowFromIdx, out cachedRow);

                foreach (var newRowIdx in Enumerable.Range((int)rowFromIdx + 1, numOfRows))
                {
                    if (cachedRow != null)
                    {
                        Row cachedRowCopy = (Row)cachedRow.CloneNode(false);
                        cachedRowCopy.RowIndex = (uint)newRowIdx;
                        _cachedRows.Add(cachedRowCopy.RowIndex, cachedRowCopy);
                    }

                    foreach (var colIdx in rowCache.Keys)
                    {
                        RowColumn rcOld = new RowColumn()
                        {
                            Row = rowFromIdx, Column = colIdx
                        };
                        RowColumn rcNew = new RowColumn()
                        {
                            Row = (uint)newRowIdx, Column = colIdx
                        };
                        CellProxy cOld = GetCell(rcOld.Row, rcOld.Column);
                        CellProxy cNew = fnCreate(cOld);
                        if (cNew != null)
                        {
                            AddCellToCache(cNew, rcNew.Row, rcNew.Column);
                        }
                    }
                }
            }
        }
        public void Load()
        {
            WorksheetPart wp = _wsheet.GetOWorksheetPart();

            if (wp == null)
            {
                return;
            }

            Action <Cell> readCell = (cell) =>
            {
                RowColumn rc        = ExcelAddress.ToRowColumn(cell.CellReference);
                CellProxy cellProxy = this.EnsureCell(rc.Row, rc.Column);
                if (cell.DataType != null)
                {
                    cellProxy.DataType = cell.DataType.Value;
                    if (cell.DataType.Value == CellValues.InlineString)
                    {
                        cellProxy.Value = cell.InlineString.Text.Text;
                    }
                    else
                    {
                        if (cell.CellValue != null)
                        {
                            cellProxy.Value = cell.CellValue.Text;
                        }
                        else
                        {
                            cellProxy.Value = string.Empty;
                        }
                    }
                }
                else
                {
                    if (cell.CellValue != null)
                    {
                        cellProxy.Value = cell.CellValue.Text;
                    }
                }
                if (cell.StyleIndex != null)
                {
                    cellProxy.StyleIndex = cell.StyleIndex;
                }
                if (cell.ShowPhonetic != null)
                {
                    cellProxy.ShowPhonetic = cell.ShowPhonetic;
                }
                if (cell.ValueMetaIndex != null)
                {
                    cellProxy.ValueMetaIndex = cell.ValueMetaIndex;
                }
                if (cell.CellFormula != null)
                {
                    cellProxy.CreateFormula();
                    cellProxy.Formula.Text      = cell.CellFormula.Text;
                    cellProxy.Formula.R1        = cell.CellFormula.R1;
                    cellProxy.Formula.R2        = cell.CellFormula.R2;
                    cellProxy.Formula.Reference = cell.CellFormula.Reference;
                    if (cell.CellFormula.AlwaysCalculateArray != null)
                    {
                        cellProxy.Formula.AlwaysCalculateArray = cell.CellFormula.AlwaysCalculateArray;
                    }
                    if (cell.CellFormula.Bx != null)
                    {
                        cellProxy.Formula.Bx = cell.CellFormula.Bx;
                    }
                    if (cell.CellFormula.CalculateCell != null)
                    {
                        cellProxy.Formula.CalculateCell = cell.CellFormula.CalculateCell;
                    }
                    if (cell.CellFormula.DataTable2D != null)
                    {
                        cellProxy.Formula.DataTable2D = cell.CellFormula.DataTable2D;
                    }
                    if (cell.CellFormula.DataTableRow != null)
                    {
                        cellProxy.Formula.DataTableRow = cell.CellFormula.DataTableRow;
                    }
                    if (cell.CellFormula.FormulaType != null)
                    {
                        cellProxy.Formula.FormulaType = cell.CellFormula.FormulaType;
                    }
                    if (cell.CellFormula.Input1Deleted != null)
                    {
                        cellProxy.Formula.Input1Deleted = cell.CellFormula.Input1Deleted;
                    }
                    if (cell.CellFormula.Input2Deleted != null)
                    {
                        cellProxy.Formula.Input2Deleted = cell.CellFormula.Input2Deleted;
                    }
                    if (cell.CellFormula.SharedIndex != null)
                    {
                        cellProxy.Formula.SharedIndex = cell.CellFormula.SharedIndex;
                    }

                    cellProxy.Value = null; // Don't cache/store values for formulas
                }
            };

            OpenXmlReader reader      = OpenXmlReader.Create(_wsheet.GetOWorksheetPart());
            bool          inWorksheet = false;
            bool          inSheetData = false;

            while (reader.Read())
            {
                if (inWorksheet && reader.IsStartElement)
                {
                    if (reader.ElementType == typeof(Row) && reader.IsStartElement)
                    {
                        // ----------------------------------------
                        // Scan row if anything other than RowIndex and Spans has been set,
                        // if so then cache
                        Row r = (Row)reader.LoadCurrentElement();
                        var needToCacheRow = (from a in r.GetAttributes()
                                              let ln = a.LocalName
                                                       where ln != "r" && ln != "spans"
                                                       select a).Any();

                        if (needToCacheRow)
                        {
                            _cachedRows.Add(r.RowIndex, (Row)r.CloneNode(false));
                        }
                        foreach (Cell cell in r.Elements <Cell>())
                        {
                            readCell(cell);
                        }
                        // ----------------------------------------
                    }
                    else if (reader.ElementType == typeof(SheetData))
                    {
                        inSheetData = reader.IsStartElement;
                    }
                    else if (reader.IsStartElement)
                    {
                        var e = reader.LoadCurrentElement();
                        _cachedElements.Add(e);
                    }
                }
                else if (reader.ElementType == typeof(Worksheet))
                {
                    inWorksheet = reader.IsStartElement;
                }
            }

            // Reset modified to false (loading sets it to true due to CellProxy loading)
            this.Modified = false;
        }
        public void WriteWorksheetPart(OpenXmlWriter writer)
        {
            // TODO: final cleanup
            // - merge redundant columns
            // - remove unused shared strings

            // Remove rows without cells
            foreach (var rowItem in _cachedCells.ToList())
            {
                if (rowItem.Value.Count == 0)
                {
                    _cachedCells.Remove(rowItem.Key);
                }
            }

            // Simulate rows for cached rows
            foreach (var rowIdx in _cachedRows.Keys)
            {
                if (!_cachedCells.ContainsKey(rowIdx))
                {
                    _cachedCells[rowIdx] = new SortedList <uint, CellProxy>();
                }
            }

            // Get first and last addresses
            uint minRow = uint.MaxValue;
            uint minCol = uint.MaxValue;
            uint maxRow = 0;
            uint maxCol = 0;

            foreach (var rowItem in _cachedCells)
            {
                uint rowIdx = rowItem.Key;
                var  cells  = rowItem.Value;
                if (minRow == uint.MaxValue)
                {
                    minRow = rowIdx;
                }
                maxRow = rowIdx;
                if (cells.Count > 0)
                {
                    minCol = Math.Min(minCol, cells.Keys.First());
                    maxCol = Math.Max(maxCol, cells.Keys.Last());
                }
            }

            string firstAddress = null, lastAddress = null;

            if (minRow < uint.MaxValue && minCol < uint.MaxValue)
            {
                firstAddress = RowColumn.ToAddress(minRow, minCol);
                if (minRow != maxRow || minCol != maxCol)
                {
                    lastAddress = RowColumn.ToAddress(maxRow, maxCol);
                }
            }
            else
            {
                firstAddress = "A1";
            }

            writer.WriteStartDocument();
            writer.WriteStartElement(new Worksheet());
            foreach (string childTagName in SchemaInfo.WorksheetChildSequence)
            {
                if (childTagName == "sheetData")
                {
                    WriteSheetData(writer);
                }
                else if (childTagName == "dimension")
                {
                    string dimensionRef = firstAddress + (lastAddress != null ? ":" + lastAddress : "");
                    writer.WriteElement(new SheetDimension()
                    {
                        Reference = dimensionRef
                    });
                }
                else if (childTagName == "sheetViews")
                {
                    SheetViews svs = GetFirstElement <SheetViews>();
                    if (svs != null)
                    {
                        foreach (SheetView sv in svs.Elements <SheetView>())
                        {
                            foreach (Selection sel in sv.Elements <Selection>())
                            {
                                if (minRow < uint.MaxValue)
                                {
                                    sel.ActiveCell           = firstAddress;
                                    sel.SequenceOfReferences = new ListValue <StringValue>(new StringValue[] { new StringValue(firstAddress) });
                                }
                                else
                                {
                                    sel.Remove();
                                }
                            }
                        }
                        writer.WriteElement(svs);
                    }
                }
                else
                {
                    foreach (var e in GetElementsByTagName(childTagName))
                    {
                        writer.WriteElement(e);
                    }
                }
            }
            writer.WriteEndElement(); // worksheet
        }
        private static string TranslateInternal(RangeComponents er, uint rowStart, uint colStart, int rowDelta, int colDelta, bool followAbsoluteRefs)
        {
            string newCellRef1 = null;
            string newCellRef2 = null;
            bool   errRef1 = false, errRef2 = false;

            if (true)
            {
                if (er.Cell1Error != "")
                {
                    newCellRef1 = er.Cell1Error;
                }
                else
                {
                    RowColumn rc1 = er.Cell1RowColumn;
                    newCellRef1 = TranslateInternal(
                        er.Cell1RowDollar, rc1.Row,
                        er.Cell1ColDollar, rc1.Column,
                        rowStart, colStart,
                        rowDelta, colDelta,
                        followAbsoluteRefs,
                        out errRef1);
                }
            }

            if (er.Cell2 != "")
            {
                if (er.Cell2Error != "")
                {
                    newCellRef2 = er.Cell2Error;
                }
                else
                {
                    RowColumn rc2 = er.Cell2RowColumn;
                    newCellRef2 = TranslateInternal(
                        er.Cell2RowDollar, rc2.Row,
                        er.Cell2ColDollar, rc2.Column,
                        rowStart, colStart,
                        rowDelta, colDelta,
                        followAbsoluteRefs,
                        out errRef2);
                }
            }

            string newRange = "";

            if (er.SheetName != "")
            {
                newRange += er.EscapedSheetName + "!";
            }
            if (errRef1 && (errRef2 || newCellRef2 == null))
            {
                newRange += "#REF!";
            }
            else
            {
                newRange += newCellRef1;
                if (newCellRef2 != null)
                {
                    newRange += ":" + newCellRef2;
                }
            }

            return(newRange);
        }