Exemple #1
0
        protected override IExternalExcelSheet AddOrGetInternal(string sheetName)
        {
            if (_obj == null)
            {
                return(null);
            }

            XLWorksheet ws = null;

            if (!_write)
            {
                foreach (var ws1 in _obj.Worksheets.OfType <XLWorksheet>())
                {
                    try
                    {
                        if (ws1.Name.IgnoreCaseCompare(sheetName))
                        {
                            ws = ws1;
                            break;
                        }
                    }
                    catch { }
                }
            }

            if (ws == null)
            {
                ws = _obj.Worksheets.Add(sheetName) as XLWorksheet;
            }

            return(new CloseXMLExcelSheet(ws));
        }
        /// <summary>
        /// The direct contructor should only be used in <see cref="XLWorksheet.RangeFactory"/>.
        /// </summary>
        public XLRow(XLWorksheet worksheet, Int32 row)
            : base(XLRangeAddress.EntireRow(worksheet, row), worksheet.StyleValue)
        {
            SetRowNumber(row);

            _height = worksheet.RowHeight;
        }
Exemple #3
0
 public XLHeaderFooter(XLWorksheet worksheet)
 {
     Left   = new XLHFItem(worksheet);
     Right  = new XLHFItem(worksheet);
     Center = new XLHFItem(worksheet);
     SetAsInitial();
 }
Exemple #4
0
        /// <summary>
        /// The direct contructor should only be used in <see cref="XLWorksheet.RangeFactory"/>.
        /// </summary>
        public XLColumn(XLWorksheet worksheet, Int32 column)
            : base(XLRangeAddress.EntireColumn(worksheet, column), worksheet.StyleValue)
        {
            SetColumnNumber(column);

            Width = worksheet.ColumnWidth;
        }
Exemple #5
0
        public XLPageSetup(XLPageSetup defaultPageOptions, XLWorksheet worksheet)
        {
            if (defaultPageOptions != null)
            {
                PrintAreas                = new XLPrintAreas(defaultPageOptions.PrintAreas as XLPrintAreas, worksheet);
                CenterHorizontally        = defaultPageOptions.CenterHorizontally;
                CenterVertically          = defaultPageOptions.CenterVertically;
                FirstPageNumber           = defaultPageOptions.FirstPageNumber;
                HorizontalDpi             = defaultPageOptions.HorizontalDpi;
                PageOrientation           = defaultPageOptions.PageOrientation;
                VerticalDpi               = defaultPageOptions.VerticalDpi;
                FirstRowToRepeatAtTop     = defaultPageOptions.FirstRowToRepeatAtTop;
                LastRowToRepeatAtTop      = defaultPageOptions.LastRowToRepeatAtTop;
                FirstColumnToRepeatAtLeft = defaultPageOptions.FirstColumnToRepeatAtLeft;
                LastColumnToRepeatAtLeft  = defaultPageOptions.LastColumnToRepeatAtLeft;
                ShowComments              = defaultPageOptions.ShowComments;


                PaperSize  = defaultPageOptions.PaperSize;
                _pagesTall = defaultPageOptions.PagesTall;
                _pagesWide = defaultPageOptions.PagesWide;
                _scale     = defaultPageOptions.Scale;


                if (defaultPageOptions.Margins != null)
                {
                    Margins = new XLMargins
                    {
                        Top    = defaultPageOptions.Margins.Top,
                        Bottom = defaultPageOptions.Margins.Bottom,
                        Left   = defaultPageOptions.Margins.Left,
                        Right  = defaultPageOptions.Margins.Right,
                        Header = defaultPageOptions.Margins.Header,
                        Footer = defaultPageOptions.Margins.Footer
                    };
                }
                AlignHFWithMargins       = defaultPageOptions.AlignHFWithMargins;
                ScaleHFWithDocument      = defaultPageOptions.ScaleHFWithDocument;
                ShowGridlines            = defaultPageOptions.ShowGridlines;
                ShowRowAndColumnHeadings = defaultPageOptions.ShowRowAndColumnHeadings;
                BlackAndWhite            = defaultPageOptions.BlackAndWhite;
                DraftQuality             = defaultPageOptions.DraftQuality;
                PageOrder = defaultPageOptions.PageOrder;

                ColumnBreaks    = defaultPageOptions.ColumnBreaks.ToList();
                RowBreaks       = defaultPageOptions.RowBreaks.ToList();
                Header          = new XLHeaderFooter(defaultPageOptions.Header as XLHeaderFooter, worksheet);
                Footer          = new XLHeaderFooter(defaultPageOptions.Footer as XLHeaderFooter, worksheet);
                PrintErrorValue = defaultPageOptions.PrintErrorValue;
            }
            else
            {
                PrintAreas   = new XLPrintAreas(worksheet);
                Header       = new XLHeaderFooter(worksheet);
                Footer       = new XLHeaderFooter(worksheet);
                ColumnBreaks = new List <Int32>();
                RowBreaks    = new List <Int32>();
            }
        }
Exemple #6
0
 public XLHeaderFooter(XLWorksheet worksheet)
 {
     this.Worksheet = worksheet;
     Left           = new XLHFItem(this);
     Right          = new XLHFItem(this);
     Center         = new XLHFItem(this);
     SetAsInitial();
 }
Exemple #7
0
 public XLSheetView(XLWorksheet worksheet, XLSheetView sheetView)
     : this(worksheet)
 {
     this.SplitRow           = sheetView.SplitRow;
     this.SplitColumn        = sheetView.SplitColumn;
     this.FreezePanes        = sheetView.FreezePanes;
     this.TopLeftCellAddress = new XLAddress(this.Worksheet, sheetView.TopLeftCellAddress.RowNumber, sheetView.TopLeftCellAddress.ColumnNumber, sheetView.TopLeftCellAddress.FixedRow, sheetView.TopLeftCellAddress.FixedColumn);
 }
Exemple #8
0
 public XLHeaderFooter(XLHeaderFooter defaultHF, XLWorksheet worksheet)
 {
     defaultHF.innerTexts.ForEach(kp => innerTexts.Add(kp.Key, kp.Value));
     Left   = new XLHFItem(defaultHF.Left as XLHFItem, worksheet);
     Center = new XLHFItem(defaultHF.Center as XLHFItem, worksheet);
     Right  = new XLHFItem(defaultHF.Right as XLHFItem, worksheet);
     SetAsInitial();
 }
Exemple #9
0
 public XLRangeFactory(XLWorksheet worksheet)
 {
     if (worksheet == null)
     {
         throw new ArgumentNullException(nameof(worksheet));
     }
     Worksheet = worksheet;
 }
Exemple #10
0
        public XLSheetView(XLWorksheet worksheet)
        {
            Worksheet = worksheet;
            View      = XLSheetViewOptions.Normal;

            ZoomScale                = 100;
            ZoomScaleNormal          = 100;
            ZoomScalePageLayoutView  = 100;
            ZoomScaleSheetLayoutView = 100;
        }
        /// <summary>
        /// Initializes a new <see cref = "XLAddress" /> struct using R1C1 notation.
        /// </summary>
        /// <param name = "worksheet"></param>
        /// <param name = "rowNumber">The row number of the cell address.</param>
        /// <param name = "columnNumber">The column number of the cell address.</param>
        /// <param name = "fixedRow"></param>
        /// <param name = "fixedColumn"></param>
        public XLAddress(XLWorksheet worksheet, int rowNumber, int columnNumber, bool fixedRow, bool fixedColumn) : this()

        {
            Worksheet = worksheet;

            _rowNumber    = rowNumber;
            _columnNumber = columnNumber;
            _fixedColumn  = fixedColumn;
            _fixedRow     = fixedRow;
        }
Exemple #12
0
        public static XLAddress Create(XLWorksheet worksheet, string cellAddressString)
        {
            var   fixedColumn = cellAddressString[0] == '$';
            Int32 startPos;

            if (fixedColumn)
            {
                startPos = 1;
            }
            else
            {
                startPos = 0;
            }

            int rowPos = startPos;

            while (cellAddressString[rowPos] > '9')
            {
                rowPos++;
            }

            var    fixedRow = cellAddressString[rowPos] == '$';
            string columnLetter;
            int    rowNumber;

            if (fixedRow)
            {
                if (fixedColumn)
                {
                    columnLetter = cellAddressString.Substring(startPos, rowPos - 1);
                }
                else
                {
                    columnLetter = cellAddressString.Substring(startPos, rowPos);
                }

                rowNumber = int.Parse(cellAddressString.Substring(rowPos + 1), XLHelper.NumberFormatForParse);
            }
            else
            {
                if (fixedColumn)
                {
                    columnLetter = cellAddressString.Substring(startPos, rowPos - 1);
                }
                else
                {
                    columnLetter = cellAddressString.Substring(startPos, rowPos);
                }

                rowNumber = Int32.Parse(cellAddressString.Substring(rowPos), XLHelper.NumberFormatForParse);
            }
            return(new XLAddress(worksheet, rowNumber, columnLetter, fixedRow, fixedColumn));
        }
Exemple #13
0
        internal IXLPicture CopyTo(XLWorksheet targetSheet)
        {
            if (targetSheet == null)
            {
                targetSheet = Worksheet as XLWorksheet;
            }

            IXLPicture newPicture;

            if (targetSheet == Worksheet)
            {
                newPicture = targetSheet.AddPicture(ImageStream, Format);
            }
            else
            {
                newPicture = targetSheet.AddPicture(ImageStream, Format, Name);
            }

            newPicture = newPicture
                         .WithPlacement(XLPicturePlacement.FreeFloating)
                         .WithSize(Width, Height)
                         .WithPlacement(Placement);

            switch (Placement)
            {
            case XLPicturePlacement.FreeFloating:
                newPicture.MoveTo(Left, Top);
                break;

            case XLPicturePlacement.Move:
                var newAddress = new XLAddress(targetSheet, TopLeftCellAddress.RowNumber,
                                               TopLeftCellAddress.ColumnNumber, false, false);
                newPicture.MoveTo(newAddress, GetOffset(XLMarkerPosition.TopLeft));
                break;

            case XLPicturePlacement.MoveAndSize:
                var newFromAddress = new XLAddress(targetSheet, TopLeftCellAddress.RowNumber,
                                                   TopLeftCellAddress.ColumnNumber, false, false);
                var newToAddress = new XLAddress(targetSheet, BottomRightCellAddress.RowNumber,
                                                 BottomRightCellAddress.ColumnNumber, false, false);

                newPicture.MoveTo(newFromAddress, GetOffset(XLMarkerPosition.TopLeft), newToAddress,
                                  GetOffset(XLMarkerPosition.BottomRight));
                break;
            }

            return(newPicture);
        }
Exemple #14
0
        public XLChart(XLWorksheet worksheet)
        {
            Container      = this;
            this.worksheet = worksheet;
            Int32 zOrder;

            if (worksheet.Charts.Any())
            {
                zOrder = worksheet.Charts.Max(c => c.ZOrder) + 1;
            }
            else
            {
                zOrder = 1;
            }
            ZOrder         = zOrder;
            ShapeId        = worksheet.Workbook.ShapeIdManager.GetNext();
            RightAngleAxes = true;
        }
Exemple #15
0
        public XLRangeAddress(XLWorksheet worksheet, String rangeAddress)
        {
            string addressToUse = rangeAddress.Contains("!")
                                      ? rangeAddress.Substring(rangeAddress.IndexOf("!") + 1)
                                      : rangeAddress;

            string firstPart;
            string secondPart;

            if (addressToUse.Contains(':'))
            {
                var arrRange = addressToUse.Split(':');
                firstPart  = arrRange[0];
                secondPart = arrRange[1];
            }
            else
            {
                firstPart  = addressToUse;
                secondPart = addressToUse;
            }

            if (XLHelper.IsValidA1Address(firstPart))
            {
                FirstAddress = XLAddress.Create(worksheet, firstPart);
                LastAddress  = XLAddress.Create(worksheet, secondPart);
            }
            else
            {
                firstPart  = firstPart.Replace("$", String.Empty);
                secondPart = secondPart.Replace("$", String.Empty);
                if (char.IsDigit(firstPart[0]))
                {
                    FirstAddress = XLAddress.Create(worksheet, "A" + firstPart);
                    LastAddress  = XLAddress.Create(worksheet, XLHelper.MaxColumnLetter + secondPart);
                }
                else
                {
                    FirstAddress = XLAddress.Create(worksheet, firstPart + "1");
                    LastAddress  = XLAddress.Create(worksheet, secondPart + XLHelper.MaxRowNumber.ToInvariantString());
                }
            }

            Worksheet = worksheet;
        }
Exemple #16
0
        internal IXLTable CopyTo(XLWorksheet targetSheet, bool copyData = true)
        {
            if (targetSheet == Worksheet)
            {
                throw new InvalidOperationException("Cannot copy table to the worksheet it already belongs to.");
            }

            var targetRange = targetSheet.Range(RangeAddress.WithoutWorksheet());

            if (copyData)
            {
                RangeUsed().CopyTo(targetRange);
            }
            else
            {
                HeadersRow().CopyTo(targetRange.FirstRow());
            }

            String tableName = Name;
            var    newTable  = (XLTable)targetSheet.Table(targetRange, tableName, true);

            newTable.RelId = RelId;
            newTable.EmphasizeFirstColumn = EmphasizeFirstColumn;
            newTable.EmphasizeLastColumn  = EmphasizeLastColumn;
            newTable.ShowRowStripes       = ShowRowStripes;
            newTable.ShowColumnStripes    = ShowColumnStripes;
            newTable.ShowAutoFilter       = ShowAutoFilter;
            newTable.Theme          = Theme;
            newTable._showTotalsRow = ShowTotalsRow;

            Int32 fieldCount = ColumnCount();

            for (Int32 f = 0; f < fieldCount; f++)
            {
                var tableField = newTable.Field(f) as XLTableField;
                var tField     = Field(f) as XLTableField;
                tableField.Index             = tField.Index;
                tableField.Name              = tField.Name;
                tableField.totalsRowLabel    = tField.totalsRowLabel;
                tableField.totalsRowFunction = tField.totalsRowFunction;
            }
            return(newTable);
        }
        internal IXLPicture CopyTo(XLWorksheet targetSheet)
        {
            if (targetSheet == null)
            {
                targetSheet = Worksheet as XLWorksheet;
            }

            IXLPicture newPicture;

            if (targetSheet == Worksheet)
            {
                newPicture = targetSheet.AddPicture(ImageStream, Format);
            }
            else
            {
                newPicture = targetSheet.AddPicture(ImageStream, Format, Name);
            }

            newPicture = newPicture
                         .WithPlacement(XLPicturePlacement.FreeFloating)
                         .WithSize(Width, Height)
                         .WithPlacement(Placement);

            switch (Placement)
            {
            case XLPicturePlacement.FreeFloating:
                newPicture.MoveTo(Left, Top);
                break;

            case XLPicturePlacement.Move:
                newPicture.MoveTo(targetSheet.Cell(TopLeftCell.Address), GetOffset(XLMarkerPosition.TopLeft));
                break;

            case XLPicturePlacement.MoveAndSize:
                newPicture.MoveTo(targetSheet.Cell(TopLeftCell.Address), GetOffset(XLMarkerPosition.TopLeft), targetSheet.Cell(BottomRightCell.Address),
                                  GetOffset(XLMarkerPosition.BottomRight));
                break;
            }

            return(newPicture);
        }
Exemple #18
0
        internal void SwapRanges(XLSheetRange sheetRange1, XLSheetRange sheetRange2, XLWorksheet worksheet)
        {
            Int32 rowCount    = sheetRange1.LastPoint.Row - sheetRange1.FirstPoint.Row + 1;
            Int32 columnCount = sheetRange1.LastPoint.Column - sheetRange1.FirstPoint.Column + 1;

            for (int row = 0; row < rowCount; row++)
            {
                for (int column = 0; column < columnCount; column++)
                {
                    var sp1   = new XLSheetPoint(sheetRange1.FirstPoint.Row + row, sheetRange1.FirstPoint.Column + column);
                    var sp2   = new XLSheetPoint(sheetRange2.FirstPoint.Row + row, sheetRange2.FirstPoint.Column + column);
                    var cell1 = GetCell(sp1);
                    var cell2 = GetCell(sp2);

                    if (cell1 == null)
                    {
                        cell1 = worksheet.Cell(sp1.Row, sp1.Column);
                    }
                    if (cell2 == null)
                    {
                        cell2 = worksheet.Cell(sp2.Row, sp2.Column);
                    }

                    //if (cell1 != null)
                    //{
                    cell1.Address = new XLAddress(cell1.Worksheet, sp2.Row, sp2.Column, false, false);
                    Remove(sp1);
                    //if (cell2 != null)
                    Add(sp1, cell2);
                    //}

                    //if (cell2 == null) continue;

                    cell2.Address = new XLAddress(cell2.Worksheet, sp1.Row, sp1.Column, false, false);
                    Remove(sp2);
                    //if (cell1 != null)
                    Add(sp2, cell1);
                }
            }
        }
Exemple #19
0
        private IEnumerable <XLSheetPoint> GetUsedCellsCandidates(XLWorksheet worksheet)
        {
            var candidates = Enumerable.Empty <XLSheetPoint>();

            if (_options.HasFlag(XLCellsUsedOptions.MergedRanges))
            {
                candidates = candidates.Union(
                    worksheet.Internals.MergedRanges.SelectMany(r => GetAllCellsInRange(r.RangeAddress)));
            }

            if (_options.HasFlag(XLCellsUsedOptions.ConditionalFormats))
            {
                candidates = candidates.Union(
                    worksheet.ConditionalFormats.SelectMany(cf => cf.Ranges.SelectMany(r => GetAllCellsInRange(r.RangeAddress))));
            }

            if (_options.HasFlag(XLCellsUsedOptions.DataValidation))
            {
                candidates = candidates.Union(
                    worksheet.DataValidations.SelectMany(dv => dv.Ranges.SelectMany(r => GetAllCellsInRange(r.RangeAddress))));
            }

            return(candidates.Distinct());
        }
Exemple #20
0
 public XLRows(XLWorksheet worksheet)
 {
     _worksheet = worksheet;
     style      = new XLStyle(this, XLWorkbook.DefaultStyle);
 }
 public XLColumnParameters(XLWorksheet worksheet, Int32 defaultStyleId, Boolean isReference)
 {
     Worksheet      = worksheet;
     DefaultStyleId = defaultStyleId;
     IsReference    = isReference;
 }
Exemple #22
0
 private XLDataValidation(XLWorksheet worksheet)
 {
     _worksheet = worksheet ?? throw new ArgumentNullException(nameof(worksheet));
     _ranges    = new XLRanges();
     Initialize();
 }
 public XLPrintAreas(XLWorksheet worksheet)
 {
     this.worksheet = worksheet;
 }
 public XLColumnParameters(XLWorksheet worksheet, IXLStyle defaultStyle)
 {
     Worksheet    = worksheet;
     DefaultStyle = defaultStyle;
     //IsReference = isReference;
 }
Exemple #25
0
 public XLDataValidation(IXLDataValidation dataValidation, XLWorksheet worksheet)
     : this(worksheet)
 {
     _worksheet = worksheet;
     CopyFrom(dataValidation);
 }
Exemple #26
0
 public XLNamedRanges(XLWorksheet worksheet)
     : this(worksheet.Workbook)
 {
     Worksheet = worksheet;
 }
Exemple #27
0
 /// <summary>
 ///     Initializes a new <see cref = "XLAddress" /> struct using a mixed notation.
 /// </summary>
 /// <param name = "worksheet"></param>
 /// <param name = "rowNumber">The row number of the cell address.</param>
 /// <param name = "columnLetter">The column letter of the cell address.</param>
 /// <param name = "fixedRow"></param>
 /// <param name = "fixedColumn"></param>
 public XLAddress(XLWorksheet worksheet, int rowNumber, string columnLetter, bool fixedRow, bool fixedColumn)
     : this(worksheet, rowNumber, XLHelper.GetColumnNumberFromLetter(columnLetter), fixedRow, fixedColumn)
 {
     _columnLetter = columnLetter;
 }
 public XLPictures(XLWorksheet worksheet)
 {
     _worksheet = worksheet;
     Deleted    = new HashSet <String>();
 }
Exemple #29
0
 public XLHFItem(XLWorksheet worksheet)
 {
     _worksheet = worksheet;
 }
Exemple #30
0
 public XLHFItem(XLHFItem defaultHFItem, XLWorksheet worksheet)
     : this(worksheet)
 {
     defaultHFItem.texts.ForEach(kp => texts.Add(kp.Key, kp.Value));
 }