Beispiel #1
0
        public IXLRangeRows Rows(String rows)
        {
            var retVal   = new XLRangeRows();
            var rowPairs = rows.Split(',');

            foreach (string tPair in rowPairs.Select(pair => pair.Trim()))
            {
                String firstRow;
                String lastRow;
                if (tPair.Contains(':') || tPair.Contains('-'))
                {
                    string[] rowRange = XLHelper.SplitRange(tPair);

                    firstRow = rowRange[0];
                    lastRow  = rowRange[1];
                }
                else
                {
                    firstRow = tPair;
                    lastRow  = tPair;
                }
                foreach (IXLRangeRow row in Rows(Int32.Parse(firstRow), Int32.Parse(lastRow)))
                {
                    retVal.Add(row);
                }
            }
            return(retVal);
        }
Beispiel #2
0
        public IEnumerator <IXLRange> GetEnumerator()
        {
            var retList = new List <IXLRange>();

            retList.AddRange(_ranges.Where(r => XLHelper.IsValidRangeAddress(r.RangeAddress)).Cast <IXLRange>());
            return(retList.GetEnumerator());
        }
Beispiel #3
0
        public void Delete(Int32 position)
        {
            int wsCount = _worksheets.Values.Count(w => w.Position == position);

            if (wsCount == 0)
            {
                throw new ArgumentException("There isn't a worksheet associated with that index.");
            }

            if (wsCount > 1)
            {
                throw new ArgumentException(
                          "Can't delete the worksheet because there are multiple worksheets associated with that index.");
            }

            var ws = _worksheets.Values.Single(w => w.Position == position);

            if (!XLHelper.IsNullOrWhiteSpace(ws.RelId) && !Deleted.Contains(ws.RelId))
            {
                Deleted.Add(ws.RelId);
            }

            _worksheets.RemoveAll(w => w.Position == position);
            _worksheets.Values.Where(w => w.Position > position).ForEach(w => w._position         -= 1);
            _workbook.UnsupportedSheets.Where(w => w.Position > position).ForEach(w => w.Position -= 1);
        }
Beispiel #4
0
        private void RescanFieldNames()
        {
            if (ShowHeaderRow)
            {
                var   detectedFieldNames = new Dictionary <String, IXLTableField>();
                var   headersRow         = HeadersRow(false);
                Int32 cellPos            = 0;
                foreach (var cell in headersRow.Cells())
                {
                    var name = cell.GetString();
                    if (_fieldNames.ContainsKey(name) && _fieldNames[name].Column.ColumnNumber() == cell.Address.ColumnNumber)
                    {
                        (_fieldNames[name] as XLTableField).Index = cellPos;
                        detectedFieldNames.Add(name, _fieldNames[name]);
                        cellPos++;
                        continue;
                    }

                    if (XLHelper.IsNullOrWhiteSpace(name))
                    {
                        name = GetUniqueName("Column", cellPos + 1, true);
                        cell.SetValue(name);
                        cell.DataType = XLDataType.Text;
                    }
                    if (_fieldNames.ContainsKey(name))
                    {
                        throw new ArgumentException("The header row contains more than one field name '" + name + "'.");
                    }

                    _fieldNames.Add(name, new XLTableField(this, name)
                    {
                        Index = cellPos++
                    });
                    detectedFieldNames.Add(name, _fieldNames[name]);
                }

                _fieldNames.Keys
                .Where(key => !detectedFieldNames.ContainsKey(key))
                .ToArray()
                .ForEach(key => _fieldNames.Remove(key));
            }
            else
            {
                Int32 colCount = ColumnCount();
                for (Int32 i = 1; i <= colCount; i++)
                {
                    if (_fieldNames.Values.All(f => f.Index != i - 1))
                    {
                        var name = "Column" + i;

                        _fieldNames.Add(name, new XLTableField(this, name)
                        {
                            Index = i - 1
                        });
                    }
                }
            }
        }
 public Boolean IsDirty()
 {
     return
         (AllowedValues != XLAllowedValues.AnyValue ||
          (ShowInputMessage &&
           (!XLHelper.IsNullOrWhiteSpace(InputTitle) || !XLHelper.IsNullOrWhiteSpace(InputMessage))) ||
          (ShowErrorMessage &&
           (!XLHelper.IsNullOrWhiteSpace(ErrorTitle) || !XLHelper.IsNullOrWhiteSpace(ErrorMessage))));
 }
Beispiel #6
0
 public void Add(String elementNumber, XLSortOrder sortOrder, Boolean ignoreBlanks, Boolean matchCase)
 {
     elements.Add(new XLSortElement()
     {
         ElementNumber = XLHelper.GetColumnNumberFromLetter(elementNumber),
         SortOrder     = sortOrder,
         IgnoreBlanks  = ignoreBlanks,
         MatchCase     = matchCase
     });
 }
Beispiel #7
0
        public new IXLRangeColumn Column(String column)
        {
            if (XLHelper.IsValidColumn(column))
            {
                Int32 coNum = XLHelper.GetColumnNumberFromLetter(column);
                return(coNum > ColumnCount() ? Column(_table.GetFieldIndex(column) + 1) : Column(coNum));
            }

            return(Column(_table.GetFieldIndex(column) + 1));
        }
Beispiel #8
0
        public IXLFilterColumn Column(String column)
        {
            var columnNumber = XLHelper.GetColumnNumberFromLetter(column);

            if (columnNumber < 1 || columnNumber > XLHelper.MaxColumnNumber)
            {
                throw new ArgumentOutOfRangeException(nameof(column), "Column '" + column + "' is outside the allowed column range.");
            }

            return(Column(columnNumber));
        }
Beispiel #9
0
        public void Rename(String oldSheetName, String newSheetName)
        {
            if (XLHelper.IsNullOrWhiteSpace(oldSheetName) || !_worksheets.ContainsKey(oldSheetName))
            {
                return;
            }

            var ws = _worksheets[oldSheetName];

            _worksheets.Remove(oldSheetName);
            _worksheets.Add(newSheetName, ws);
        }
Beispiel #10
0
        /// <summary>
        /// Adds the specified range name.
        /// </summary>
        /// <param name="rangeName">Name of the range.</param>
        /// <param name="rangeAddress">The range address.</param>
        /// <param name="comment">The comment.</param>
        /// <param name="validateName">if set to <c>true</c> validates the name.</param>
        /// <param name="validateRangeAddress">if set to <c>true</c> range address will be checked for validity.</param>
        /// <returns></returns>
        /// <exception cref="NotSupportedException"></exception>
        /// <exception cref="ArgumentException">
        /// For named ranges in the workbook scope, specify the sheet name in the reference.
        /// </exception>
        internal IXLNamedRange Add(String rangeName, String rangeAddress, String comment, Boolean validateName, Boolean validateRangeAddress)
        {
            // When loading named ranges from an existing file, we do not validate the range address or name.
            if (validateRangeAddress)
            {
                var match = XLHelper.NamedRangeReferenceRegex.Match(rangeAddress);

                if (!match.Success)
                {
                    if (XLHelper.IsValidRangeAddress(rangeAddress))
                    {
                        IXLRange range = null;
                        if (Scope == XLNamedRangeScope.Worksheet)
                        {
                            range = Worksheet.Range(rangeAddress);
                        }
                        else if (Scope == XLNamedRangeScope.Workbook)
                        {
                            range = Workbook.Range(rangeAddress);
                        }
                        else
                        {
                            throw new NotSupportedException($"Scope {Scope} is not supported");
                        }

                        if (range == null)
                        {
                            throw new ArgumentException(string.Format(
                                                            "The range address '{0}' for the named range '{1}' is not a valid range.", rangeAddress,
                                                            rangeName));
                        }

                        if (Scope == XLNamedRangeScope.Workbook || !XLHelper.NamedRangeReferenceRegex.Match(range.ToString()).Success)
                        {
                            throw new ArgumentException(
                                      "For named ranges in the workbook scope, specify the sheet name in the reference.");
                        }

                        rangeAddress = Worksheet.Range(rangeAddress).ToString();
                    }
                }
            }

            var namedRange = new XLNamedRange(this, rangeName, validateName, rangeAddress, comment);

            _namedRanges.Add(rangeName, namedRange);
            return(namedRange);
        }
Beispiel #11
0
        public static Double GetWidth(this IXLFontBase fontBase, String text, Dictionary <IXLFontBase, Font> fontCache)
        {
            if (XLHelper.IsNullOrWhiteSpace(text))
            {
                return(0);
            }

            var font      = GetCachedFont(fontBase, fontCache);
            var textWidth = GraphicsUtils.MeasureString(text, font).Width;

            double width = (textWidth / 7d * 256 - 128 / 7) / 256;

            width = Math.Round(width + 0.2, 2);

            return(width);
        }
        public static Double GetWidth(this IXLFontBase fontBase, String text, Dictionary <IXLFontBase, Font> fontCache)
        {
            if (XLHelper.IsNullOrWhiteSpace(text))
            {
                return(0);
            }

            var font = GetCachedFont(fontBase, fontCache);

            var    textSize = TextRenderer.MeasureText(text, font);
            double width    = (((textSize.Width / (double)7) * 256) - (128 / 7)) / 256;

            width = (double)decimal.Round((decimal)width + 0.2M, 2);

            return(width);
        }
Beispiel #13
0
        public void Rename(String oldSheetName, String newSheetName)
        {
            if (XLHelper.IsNullOrWhiteSpace(oldSheetName) || !_worksheets.ContainsKey(oldSheetName))
            {
                return;
            }

            if (_worksheets.Any(ws1 => ws1.Key.Equals(newSheetName, StringComparison.OrdinalIgnoreCase)))
            {
                throw new ArgumentException(String.Format("A worksheet with the same name ({0}) has already been added.", newSheetName), nameof(newSheetName));
            }

            var ws = _worksheets[oldSheetName];

            _worksheets.Remove(oldSheetName);
            Add(newSheetName, ws);
        }
Beispiel #14
0
        public static bool IsValidColumn(string column)
        {
            var length = column.Length;

            if (XLHelper.IsNullOrWhiteSpace(column) || length > 3)
            {
                return(false);
            }

            var theColumn = column.ToUpper();

            var isValid = theColumn[0] >= 'A' && theColumn[0] <= 'Z';

            if (length == 1)
            {
                return(isValid);
            }

            if (length == 2)
            {
                return(isValid && theColumn[1] >= 'A' && theColumn[1] <= 'Z');
            }

            if (theColumn[0] >= 'A' && theColumn[0] < 'X')
            {
                return(theColumn[1] >= 'A' && theColumn[1] <= 'Z' &&
                       theColumn[2] >= 'A' && theColumn[2] <= 'Z');
            }

            if (theColumn[0] != 'X')
            {
                return(false);
            }

            if (theColumn[1] < 'F')
            {
                return(theColumn[2] >= 'A' && theColumn[2] <= 'Z');
            }

            if (theColumn[1] != 'F')
            {
                return(false);
            }

            return(theColumn[2] >= 'A' && theColumn[2] <= 'D');
        }
Beispiel #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;
        }
Beispiel #16
0
        public static bool IsValidA1Address(string address)
        {
            if (XLHelper.IsNullOrWhiteSpace(address))
            {
                return(false);
            }

            address = address.Replace("$", "");
            var rowPos        = 0;
            var addressLength = address.Length;

            while (rowPos < addressLength && (address[rowPos] > '9' || address[rowPos] < '0'))
            {
                rowPos++;
            }

            return
                (rowPos < addressLength &&
                 IsValidRow(address.Substring(rowPos)) &&
                 IsValidColumn(address.Substring(0, rowPos)));
        }
Beispiel #17
0
        private void AddToTables(XLRange range, Boolean addToTables)
        {
            if (!addToTables)
            {
                return;
            }

            _uniqueNames = new HashSet <string>();
            Int32 co = 1;

            foreach (IXLCell c in range.Row(1).Cells())
            {
                if (XLHelper.IsNullOrWhiteSpace(((XLCell)c).InnerText))
                {
                    c.Value = GetUniqueName("Column", co, true);
                }
                _uniqueNames.Add(c.GetString());
                co++;
            }
            Worksheet.Tables.Add(this);
        }
Beispiel #18
0
        public IXLRangeColumns Columns(String columns)
        {
            var retVal      = new XLRangeColumns();
            var columnPairs = columns.Split(',');

            foreach (string tPair in columnPairs.Select(pair => pair.Trim()))
            {
                String firstColumn;
                String lastColumn;
                if (tPair.Contains(':') || tPair.Contains('-'))
                {
                    string[] columnRange = XLHelper.SplitRange(tPair);

                    firstColumn = columnRange[0];
                    lastColumn  = columnRange[1];
                }
                else
                {
                    firstColumn = tPair;
                    lastColumn  = tPair;
                }

                Int32 tmp;
                if (Int32.TryParse(firstColumn, out tmp))
                {
                    foreach (IXLRangeColumn col in Columns(Int32.Parse(firstColumn), Int32.Parse(lastColumn)))
                    {
                        retVal.Add(col);
                    }
                }
                else
                {
                    foreach (IXLRangeColumn col in Columns(firstColumn, lastColumn))
                    {
                        retVal.Add(col);
                    }
                }
            }
            return(retVal);
        }
Beispiel #19
0
        internal static string EscapeSheetName(this String sheetName)
        {
            if (String.IsNullOrEmpty(sheetName))
            {
                return(sheetName);
            }

            var needEscape = (!Char.IsLetter(sheetName[0]) && sheetName[0] != '_') ||
                             XLHelper.IsValidA1Address(sheetName) ||
                             XLHelper.IsValidRCAddress(sheetName) ||
                             sheetName.Any(c => (Char.IsPunctuation(c) && c != '.' && c != '_') ||
                                           Char.IsSeparator(c) ||
                                           Char.IsControl(c) ||
                                           Char.IsSymbol(c));

            if (needEscape)
            {
                return(String.Concat('\'', sheetName.Replace("'", "''"), '\''));
            }
            else
            {
                return(sheetName);
            }
        }
Beispiel #20
0
 public void SetColumnsToRepeatAtLeft(String firstColumnToRepeatAtLeft, String lastColumnToRepeatAtLeft)
 {
     SetColumnsToRepeatAtLeft(XLHelper.GetColumnNumberFromLetter(firstColumnToRepeatAtLeft), XLHelper.GetColumnNumberFromLetter(lastColumnToRepeatAtLeft));
 }
Beispiel #21
0
 public new IXLTableRows InsertRowsAbove(int numberOfRows)
 {
     return(XLHelper.InsertRowsWithoutEvents(base.InsertRowsAbove, this, numberOfRows, !Table.ShowTotalsRow));
 }
Beispiel #22
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;
 }
Beispiel #23
0
        public IXLTable Resize(IXLRange range)
        {
            if (!this.ShowHeaderRow)
            {
                throw new NotImplementedException("Resizing of tables with no headers not supported yet.");
            }

            if (this.Worksheet != range.Worksheet)
            {
                throw new InvalidOperationException("You cannot resize a table to a range on a different sheet.");
            }

            var totalsRowChanged   = this.ShowTotalsRow ? range.LastRow().RowNumber() - this.TotalsRow().RowNumber() : 0;
            var oldTotalsRowNumber = this.ShowTotalsRow ? this.TotalsRow().RowNumber() : -1;

            var existingHeaders = this.FieldNames.Keys;
            var newHeaders      = new HashSet <string>();
            var tempArray       = this.Fields.Select(f => f.Column).ToArray();

            var firstRow = range.Row(1);

            if (!firstRow.FirstCell().Address.Equals(this.HeadersRow().FirstCell().Address) ||
                !firstRow.LastCell().Address.Equals(this.HeadersRow().LastCell().Address))
            {
                _uniqueNames.Clear();
                var co = 1;
                foreach (var c in firstRow.Cells())
                {
                    if (XLHelper.IsNullOrWhiteSpace(((XLCell)c).InnerText))
                    {
                        c.Value = GetUniqueName("Column", co, true);
                    }

                    var header = c.GetString();
                    _uniqueNames.Add(header);

                    if (!existingHeaders.Contains(header))
                    {
                        newHeaders.Add(header);
                    }

                    co++;
                }
            }

            if (totalsRowChanged < 0)
            {
                range.Rows(r => r.RowNumber().Equals(this.TotalsRow().RowNumber() + totalsRowChanged)).Single().InsertRowsAbove(1);
                range = Worksheet.Range(range.FirstCell(), range.LastCell().CellAbove());
                oldTotalsRowNumber++;
            }
            else if (totalsRowChanged > 0)
            {
                this.TotalsRow().RowBelow(totalsRowChanged + 1).InsertRowsAbove(1);
                this.TotalsRow().AsRange().Delete(XLShiftDeletedCells.ShiftCellsUp);
            }

            this.RangeAddress = range.RangeAddress as XLRangeAddress;
            RescanFieldNames();

            if (this.ShowTotalsRow)
            {
                foreach (var f in this._fieldNames.Values)
                {
                    var c = this.TotalsRow().Cell(f.Index + 1);
                    if (!c.IsEmpty() && newHeaders.Contains(f.Name))
                    {
                        f.TotalsRowLabel = c.GetFormattedString();
                        c.DataType       = XLDataType.Text;
                    }
                }

                if (totalsRowChanged != 0)
                {
                    foreach (var f in this._fieldNames.Values.Cast <XLTableField>())
                    {
                        f.UpdateUnderlyingCellFormula();
                        var c = this.TotalsRow().Cell(f.Index + 1);
                        if (!XLHelper.IsNullOrWhiteSpace(f.TotalsRowLabel))
                        {
                            c.DataType = XLDataType.Text;

                            //Remove previous row's label
                            var oldTotalsCell = this.Worksheet.Cell(oldTotalsRowNumber, f.Column.ColumnNumber());
                            if (oldTotalsCell.Value.ToString() == f.TotalsRowLabel)
                            {
                                oldTotalsCell.Value = null;
                            }
                        }

                        if (f.TotalsRowFunction != XLTotalsRowFunction.None)
                        {
                            c.DataType = XLDataType.Number;
                        }
                    }
                }
            }

            return(this);
        }
Beispiel #24
0
 public IXLRangeColumns Columns(String firstColumn, String lastColumn)
 {
     return(Columns(XLHelper.GetColumnNumberFromLetter(firstColumn),
                    XLHelper.GetColumnNumberFromLetter(lastColumn)));
 }
Beispiel #25
0
 public IXLFilterColumn Column(String column)
 {
     return(Column(XLHelper.GetColumnNumberFromLetter(column)));
 }
Beispiel #26
0
 public new IXLTableRows InsertRowsBelow(int numberOfRows)
 {
     return(XLHelper.InsertRowsWithoutEvents(base.InsertRowsBelow, _tableRange, numberOfRows, !_tableRange.Table.ShowTotalsRow));
 }
Beispiel #27
0
 public virtual XLRangeColumn Column(String columnLetter)
 {
     return(Column(XLHelper.GetColumnNumberFromLetter(columnLetter)));
 }
Beispiel #28
0
 public IXLCells Cells(string firstColumn, string lastColumn)
 {
     return(Cells(XLHelper.GetColumnNumberFromLetter(firstColumn) + ":"
                  + XLHelper.GetColumnNumberFromLetter(lastColumn)));
 }
Beispiel #29
0
 public XLRangeColumn Column(String column)
 {
     return(Column(XLHelper.GetColumnNumberFromLetter(column)));
 }