diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCells.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCells.cs index 4ee70e2..e368d19 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCells.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCells.cs @@ -53,5 +53,7 @@ String FormulaR1C1 { set; } IXLStyle Style { get; set; } + + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index 6f993e2..4be0f9f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -231,8 +231,8 @@ cAddress = fA1; } - - if (worksheet.Internals.Workbook.Worksheets.Where(w => w.Name.ToLower().Equals(sName.ToLower())).Any() + + if (worksheet.Internals.Workbook.Worksheets.Any(w => w.Name.ToLower().Equals(sName.ToLower())) && XLAddress.IsValidA1Address(cAddress) ) { @@ -319,7 +319,7 @@ } } var rangesToMerge = new List(); - foreach (var mergedRange in asRange.Worksheet.Internals.MergedRanges) + foreach (var mergedRange in (asRange.Worksheet as XLWorksheet).Internals.MergedRanges) { if (asRange.Contains(mergedRange)) { @@ -1076,7 +1076,7 @@ } else { - var address = new XLAddress(a1Address); + var address = new XLAddress(worksheet, a1Address); String rowPart = GetR1C1Row(address.RowNumber, address.FixedRow, rowsToShift); String columnPart = GetR1C1Column(address.ColumnNumber, address.FixedRow, columnsToShift); @@ -1143,6 +1143,10 @@ SettingHyperlink = false; } + var asRange = source.AsRange(); + if (source.Worksheet.DataValidations.Any(dv => dv.Ranges.Contains(asRange))) + (DataValidation as XLDataValidation).CopyFrom(source.DataValidation); + return this; } @@ -1224,21 +1228,21 @@ { if (useSheetName) sb.Append(String.Format("'{0}'!{1}:{2}", sheetName, - new XLAddress( + new XLAddress(worksheet, matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted, matchRange.RangeAddress.FirstAddress.ColumnLetter, matchRange.RangeAddress.FirstAddress.FixedRow, matchRange.RangeAddress.FirstAddress.FixedColumn), - new XLAddress( + new XLAddress(worksheet, matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted, matchRange.RangeAddress.LastAddress.ColumnLetter, matchRange.RangeAddress.LastAddress.FixedRow, matchRange.RangeAddress.LastAddress.FixedColumn))); else sb.Append(String.Format("{0}:{1}", - new XLAddress( + new XLAddress(worksheet, matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted, matchRange.RangeAddress.FirstAddress.ColumnLetter, matchRange.RangeAddress.FirstAddress.FixedRow, matchRange.RangeAddress.FirstAddress.FixedColumn), - new XLAddress( + new XLAddress(worksheet, matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted, matchRange.RangeAddress.LastAddress.ColumnLetter, matchRange.RangeAddress.LastAddress.FixedRow, matchRange.RangeAddress.LastAddress.FixedColumn))); @@ -1247,13 +1251,13 @@ { if (useSheetName) sb.Append(String.Format("'{0}'!{1}", sheetName, - new XLAddress( + new XLAddress(worksheet, matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted, matchRange.RangeAddress.FirstAddress.ColumnLetter, matchRange.RangeAddress.FirstAddress.FixedRow, matchRange.RangeAddress.FirstAddress.FixedColumn))); else sb.Append(String.Format("{0}", - new XLAddress( + new XLAddress(worksheet, matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted, matchRange.RangeAddress.FirstAddress.ColumnLetter, matchRange.RangeAddress.FirstAddress.FixedRow, matchRange.RangeAddress.FirstAddress.FixedColumn))); @@ -1264,14 +1268,14 @@ if (useSheetName) sb.Append(String.Format("'{0}'!{1}:{2}", sheetName, matchRange.RangeAddress.FirstAddress.ToString(), - new XLAddress( + new XLAddress(worksheet, matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted, matchRange.RangeAddress.LastAddress.ColumnLetter, matchRange.RangeAddress.LastAddress.FixedRow, matchRange.RangeAddress.LastAddress.FixedColumn))); else sb.Append(String.Format("{0}:{1}", matchRange.RangeAddress.FirstAddress.ToString(), - new XLAddress( + new XLAddress(worksheet, matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted, matchRange.RangeAddress.LastAddress.ColumnLetter, matchRange.RangeAddress.LastAddress.FixedRow, matchRange.RangeAddress.LastAddress.FixedColumn))); @@ -1377,21 +1381,21 @@ { if (useSheetName) sb.Append(String.Format("'{0}'!{1}:{2}", sheetName, - new XLAddress( + new XLAddress(worksheet, matchRange.RangeAddress.FirstAddress.RowNumber, matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted, matchRange.RangeAddress.FirstAddress.FixedRow, matchRange.RangeAddress.FirstAddress.FixedColumn), - new XLAddress( + new XLAddress(worksheet, matchRange.RangeAddress.LastAddress.RowNumber, matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted, matchRange.RangeAddress.LastAddress.FixedRow, matchRange.RangeAddress.LastAddress.FixedColumn))); else sb.Append(String.Format("{0}:{1}", - new XLAddress( + new XLAddress(worksheet, matchRange.RangeAddress.FirstAddress.RowNumber, matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted, matchRange.RangeAddress.FirstAddress.FixedRow, matchRange.RangeAddress.FirstAddress.FixedColumn), - new XLAddress( + new XLAddress(worksheet, matchRange.RangeAddress.LastAddress.RowNumber, matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted, matchRange.RangeAddress.LastAddress.FixedRow, matchRange.RangeAddress.LastAddress.FixedColumn))); @@ -1400,13 +1404,13 @@ { if (useSheetName) sb.Append(String.Format("'{0}'!{1}", sheetName, - new XLAddress( + new XLAddress(worksheet, matchRange.RangeAddress.FirstAddress.RowNumber, matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted, matchRange.RangeAddress.FirstAddress.FixedRow, matchRange.RangeAddress.FirstAddress.FixedColumn))); else sb.Append(String.Format("{0}", - new XLAddress( + new XLAddress(worksheet, matchRange.RangeAddress.FirstAddress.RowNumber, matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted, matchRange.RangeAddress.FirstAddress.FixedRow, matchRange.RangeAddress.FirstAddress.FixedColumn))); @@ -1417,14 +1421,14 @@ if (useSheetName) sb.Append(String.Format("'{0}'!{1}:{2}", sheetName, matchRange.RangeAddress.FirstAddress.ToString(), - new XLAddress( + new XLAddress(worksheet, matchRange.RangeAddress.LastAddress.RowNumber, matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted, matchRange.RangeAddress.LastAddress.FixedRow, matchRange.RangeAddress.LastAddress.FixedColumn))); else sb.Append(String.Format("{0}:{1}", matchRange.RangeAddress.FirstAddress.ToString(), - new XLAddress( + new XLAddress(worksheet, matchRange.RangeAddress.LastAddress.RowNumber, matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted, matchRange.RangeAddress.LastAddress.FixedRow, matchRange.RangeAddress.LastAddress.FixedColumn))); @@ -1479,7 +1483,7 @@ hyperlink = value; hyperlink.Worksheet = worksheet; hyperlink.Cell = this; - if (worksheet.Hyperlinks.Where(hl => hl.Cell.Address == Address).Any()) + if (worksheet.Hyperlinks.Any(hl => hl.Cell.Address == Address)) worksheet.Hyperlinks.Delete(Address); worksheet.Hyperlinks.Add(hyperlink); @@ -1541,7 +1545,7 @@ { get { - var retVal = new XLRanges(worksheet.Internals.Workbook, this.Style); + var retVal = new XLRanges(); retVal.Add(this.AsRange()); return retVal; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs index 65b5ea5..f42b38f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs @@ -7,74 +7,106 @@ { internal class XLCells : IXLCells, IXLStylized { - private XLWorksheet worksheet; private Boolean usedCellsOnly; private Boolean includeStyles; - public XLCells(XLWorksheet worksheet, Boolean entireWorksheet, Boolean usedCellsOnly, Boolean includeStyles) + public XLCells(Boolean entireWorksheet, Boolean usedCellsOnly, Boolean includeStyles) { - this.worksheet = worksheet; - this.style = new XLStyle(this, worksheet.Style); + this.style = new XLStyle(this, XLWorkbook.DefaultStyle); this.usedCellsOnly = usedCellsOnly; this.includeStyles = includeStyles; } private List rangeAddresses = new List(); + private struct MinMax { public Int32 MinRow; public Int32 MaxRow; public Int32 MinColumn; public Int32 MaxColumn; } public IEnumerator GetEnumerator() { - HashSet usedCells; - Boolean multipleRanges = rangeAddresses.Count > 1; + var cellsInRanges = new Dictionary>(); + foreach (var range in rangeAddresses) + { + HashSet hash; + if (cellsInRanges.ContainsKey(range.Worksheet)) + { + hash = cellsInRanges[range.Worksheet]; + } + else + { + hash = new HashSet(); + cellsInRanges.Add(range.Worksheet, hash); + } - if (multipleRanges) - usedCells = new HashSet(); - else - usedCells = null; + if (usedCellsOnly) + { + var addressList = (range.Worksheet as XLWorksheet).Internals.CellsCollection.Keys.Where(a => + a.RowNumber >= range.FirstAddress.RowNumber + && a.RowNumber <= range.LastAddress.RowNumber + && a.ColumnNumber >= range.FirstAddress.ColumnNumber + && a.ColumnNumber <= range.LastAddress.ColumnNumber).Select(a => a); + foreach (var a in addressList) + { + if (!hash.Contains(a)) + hash.Add(a); + } + + } + else + { + var mm = new MinMax(); + mm.MinRow = range.FirstAddress.RowNumber; + mm.MaxRow = range.LastAddress.RowNumber; + mm.MinColumn = range.FirstAddress.ColumnNumber; + mm.MaxColumn = range.LastAddress.ColumnNumber; + if (mm.MaxRow > 0 && mm.MaxColumn > 0) + { + for (Int32 ro = mm.MinRow; ro <= mm.MaxRow; ro++) + { + for (Int32 co = mm.MinColumn; co <= mm.MaxColumn; co++) + { + var address = new XLAddress(range.Worksheet, ro, co, false, false); + if (!hash.Contains(address)) + hash.Add(address); + } + } + } + } + } if (usedCellsOnly) { - var cells = from c in worksheet.Internals.CellsCollection - where ( !StringExtensions.IsNullOrWhiteSpace(c.Value.InnerText) - || (includeStyles && !c.Value.Style.Equals(worksheet.Style))) - && rangeAddresses.FirstOrDefault(r=> - r.FirstAddress.RowNumber <= c.Key.RowNumber - && r.FirstAddress.ColumnNumber <= c.Key.ColumnNumber - && r.LastAddress.RowNumber >= c.Key.RowNumber - && r.LastAddress.ColumnNumber >= c.Key.ColumnNumber - ) != null - select (IXLCell)c.Value; - foreach (var cell in cells) + foreach (var cir in cellsInRanges) { - yield return cell; - } - } - else - { - foreach (var range in rangeAddresses) - { - Int32 firstRo = range.FirstAddress.RowNumber; - Int32 lastRo = range.LastAddress.RowNumber; - Int32 firstCo = range.FirstAddress.ColumnNumber; - Int32 lastCo = range.LastAddress.ColumnNumber; - - for (Int32 ro = firstRo; ro <= lastRo; ro++) + var cellsCollection = (cir.Key as XLWorksheet).Internals.CellsCollection; + foreach (var a in cir.Value) { - for (Int32 co = firstCo; co <= lastCo; co++) - { - var cell = worksheet.Cell(ro, co); - if (multipleRanges) - { - if (!usedCells.Contains(cell.Address)) - { - usedCells.Add(cell.Address); - yield return cell; - } - } - else + if (cellsCollection.ContainsKey(a)) + { + var cell = cellsCollection[a]; + if (!StringExtensions.IsNullOrWhiteSpace((cell as XLCell).InnerText) + || (includeStyles && !cell.Style.Equals(cir.Key.Style))) { yield return cell; } } } + + //foreach (var cell in (cir.Key as XLWorksheet).Internals.CellsCollection + // .Where(kp => cir.Value.Contains(kp.Key) + // && (!StringExtensions.IsNullOrWhiteSpace((kp.Value as XLCell).InnerText) + // || (includeStyles && !kp.Value.Style.Equals(cir.Key.Style)))) + // .Select(kp => kp.Value)) + //{ + // yield return cell; + //} + } + } + else + { + foreach (var cir in cellsInRanges) + { + foreach (var address in cir.Value) + { + yield return cir.Key.Cell(address); + } } } } @@ -88,6 +120,10 @@ rangeAddresses.Add(rangeAddress); } + public void Add(IXLCell cell) + { + rangeAddresses.Add(new XLRangeAddress(cell.Address, cell.Address)); + } #region IXLStylized Members @@ -173,10 +209,12 @@ { get { - var retVal = new XLRanges(worksheet.Internals.Workbook, this.Style); + var retVal = new XLRanges(); this.ForEach(c => retVal.Add(c.AsRange())); return retVal; } } + + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs index 0c4eb65..0c52b71 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs @@ -7,6 +7,7 @@ { public interface IXLColumn : IXLRangeBase { + /// /// Gets or sets the width of this column. /// @@ -163,9 +164,6 @@ IXLColumn Sort(XLSortOrder sortOrder, Boolean matchCase); IXLRangeColumn Column(Int32 start, Int32 end); - - IXLColumn Replace(String oldValue, String newValue); - IXLColumn Replace(String oldValue, String newValue, XLSearchContents searchContents); - IXLColumn Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions); + IXLRangeColumns Columns(String columns); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs index 52761d3..a92ed74 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs @@ -105,8 +105,6 @@ IXLStyle Style { get; set; } - IXLColumns Replace(String oldValue, String newValue); - IXLColumns Replace(String oldValue, String newValue, XLSearchContents searchContents); - IXLColumns Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions); + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs index 6debf7c..bb2e4b3 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs @@ -12,13 +12,11 @@ : base(new XLRangeAddress(new XLAddress(xlColumnParameters.Worksheet, 1, column, false, false), new XLAddress(xlColumnParameters.Worksheet, XLWorksheet.MaxNumberOfRows, column, false, false))) { SetColumnNumber(column); - Worksheet = xlColumnParameters.Worksheet; - - + this.IsReference = xlColumnParameters.IsReference; if (IsReference) { - Worksheet.RangeShiftedColumns += new RangeShiftedColumnsDelegate(Worksheet_RangeShiftedColumns); + (Worksheet as XLWorksheet).RangeShiftedColumns += new RangeShiftedColumnsDelegate(Worksheet_RangeShiftedColumns); } else { @@ -27,15 +25,14 @@ } } - public XLColumn(XLColumn column, XLWorksheet worksheet) - : base(new XLRangeAddress(new XLAddress(worksheet, 1, column.ColumnNumber(), false, false), new XLAddress(worksheet, XLWorksheet.MaxNumberOfRows, column.ColumnNumber(), false, false))) + public XLColumn(XLColumn column) + : base(new XLRangeAddress(new XLAddress(column.Worksheet, 1, column.ColumnNumber(), false, false), new XLAddress(column.Worksheet, XLWorksheet.MaxNumberOfRows, column.ColumnNumber(), false, false))) { width = column.width; IsReference = column.IsReference; collapsed = column.collapsed; isHidden = column.isHidden; outlineLevel = column.outlineLevel; - this.Worksheet = worksheet; style = new XLStyle(this, column.Style); } @@ -69,7 +66,7 @@ { if (IsReference) { - return Worksheet.Internals.ColumnsCollection[this.ColumnNumber()].Width; + return (Worksheet as XLWorksheet).Internals.ColumnsCollection[this.ColumnNumber()].Width; } else { @@ -80,7 +77,7 @@ { if (IsReference) { - Worksheet.Internals.ColumnsCollection[this.ColumnNumber()].Width = value; + (Worksheet as XLWorksheet).Internals.ColumnsCollection[this.ColumnNumber()].Width = value; } else { @@ -93,13 +90,13 @@ { var columnNumber = this.ColumnNumber(); this.AsRange().Delete(XLShiftDeletedCells.ShiftCellsLeft); - Worksheet.Internals.ColumnsCollection.Remove(columnNumber); + (Worksheet as XLWorksheet).Internals.ColumnsCollection.Remove(columnNumber); List columnsToMove = new List(); - columnsToMove.AddRange(Worksheet.Internals.ColumnsCollection.Where(c => c.Key > columnNumber).Select(c => c.Key)); + columnsToMove.AddRange((Worksheet as XLWorksheet).Internals.ColumnsCollection.Where(c => c.Key > columnNumber).Select(c => c.Key)); foreach (var column in columnsToMove.OrderBy(c=>c)) { - Worksheet.Internals.ColumnsCollection.Add(column - 1, Worksheet.Internals.ColumnsCollection[column]); - Worksheet.Internals.ColumnsCollection.Remove(column); + (Worksheet as XLWorksheet).Internals.ColumnsCollection.Add(column - 1, (Worksheet as XLWorksheet).Internals.ColumnsCollection[column]); + (Worksheet as XLWorksheet).Internals.ColumnsCollection.Remove(column); } } @@ -117,7 +114,7 @@ public IXLCells Cells(String cellsInColumn) { - var retVal = new XLCells(Worksheet, false, false, false); + var retVal = new XLCells(false, false, false); var rangePairs = cellsInColumn.Split(','); foreach (var pair in rangePairs) { @@ -141,7 +138,7 @@ get { if (IsReference) - return Worksheet.Internals.ColumnsCollection[this.ColumnNumber()].Style; + return (Worksheet as XLWorksheet).Internals.ColumnsCollection[this.ColumnNumber()].Style; else return style; } @@ -149,28 +146,36 @@ { if (IsReference) { - Worksheet.Internals.ColumnsCollection[this.ColumnNumber()].Style = value; + (Worksheet as XLWorksheet).Internals.ColumnsCollection[this.ColumnNumber()].Style = value; } else { style = new XLStyle(this, value); - Int32 thisCo = this.ColumnNumber(); - foreach (var c in Worksheet.Internals.CellsCollection.Values.Where(c => c.Address.ColumnNumber == thisCo)) + + Int32 minRow = 1; + Int32 maxRow = 0; + var column = this.ColumnNumber(); + if ((Worksheet as XLWorksheet).Internals.CellsCollection.Values.Any(c => c.Address.ColumnNumber == column)) { - c.Style = value; + minRow = (Worksheet as XLWorksheet).Internals.CellsCollection.Values + .Where(c => c.Address.ColumnNumber == column) + .Min(c => c.Address.RowNumber); + maxRow = (Worksheet as XLWorksheet).Internals.CellsCollection.Values + .Where(c => c.Address.ColumnNumber == column) + .Max(c => c.Address.RowNumber); } - Int32 maxRow = 0; - Int32 minRow = 1; - if (Worksheet.Internals.RowsCollection.Count > 0) + if ((Worksheet as XLWorksheet).Internals.RowsCollection.Count > 0) { - maxRow = Worksheet.Internals.RowsCollection.Keys.Max(); - minRow = Worksheet.Internals.RowsCollection.Keys.Min(); + Int32 minInCollection = (Worksheet as XLWorksheet).Internals.RowsCollection.Keys.Min(); + Int32 maxInCollection = (Worksheet as XLWorksheet).Internals.RowsCollection.Keys.Max(); + if (minInCollection < minRow) minRow = minInCollection; + if (maxInCollection > maxRow) maxRow = maxInCollection; } for (Int32 ro = minRow; ro <= maxRow; ro++) { - Worksheet.Cell(ro, thisCo).Style = value; + Worksheet.Cell(ro, column).Style = value; } } } @@ -182,22 +187,23 @@ { UpdatingStyle = true; - yield return style; + yield return Style; - var co = this.ColumnNumber(); - - foreach (var c in Worksheet.Internals.CellsCollection.Values.Where(c => c.Address.ColumnNumber == co)) + var column = this.ColumnNumber(); + Int32 minRow = 1; + Int32 maxRow = 0; + if ((Worksheet as XLWorksheet).Internals.CellsCollection.Values.Any(c => c.Address.ColumnNumber == column)) + maxRow = (Worksheet as XLWorksheet).Internals.CellsCollection.Values.Where(c => c.Address.ColumnNumber == column).Max(c => c.Address.RowNumber); + + if ((Worksheet as XLWorksheet).Internals.RowsCollection.Count > 0) { - yield return c.Style; + Int32 maxInCollection = (Worksheet as XLWorksheet).Internals.RowsCollection.Keys.Max(); + if (maxInCollection > maxRow) maxRow = maxInCollection; } - var maxRow = 0; - if (Worksheet.Internals.RowsCollection.Count > 0) - maxRow = Worksheet.Internals.RowsCollection.Keys.Max(); - - for (var ro = 1; ro <= maxRow; ro++) + for (var ro = minRow; ro <= maxRow; ro++) { - yield return Worksheet.Cell(ro, co).Style; + yield return Worksheet.Cell(ro, column).Style; } UpdatingStyle = false; @@ -211,7 +217,7 @@ get { if (IsReference) - return Worksheet.Internals.ColumnsCollection[this.ColumnNumber()].InnerStyle; + return (Worksheet as XLWorksheet).Internals.ColumnsCollection[this.ColumnNumber()].InnerStyle; else return new XLStyle(new XLStylizedContainer(this.style, this), style); } @@ -219,7 +225,7 @@ { if (IsReference) { - Worksheet.Internals.ColumnsCollection[this.ColumnNumber()].InnerStyle = value; + (Worksheet as XLWorksheet).Internals.ColumnsCollection[this.ColumnNumber()].InnerStyle = value; } else { @@ -233,7 +239,7 @@ public new IXLColumns InsertColumnsAfter(Int32 numberOfColumns) { var columnNum = this.ColumnNumber(); - this.Worksheet.Internals.ColumnsCollection.ShiftColumnsRight(columnNum + 1, numberOfColumns); + (Worksheet as XLWorksheet).Internals.ColumnsCollection.ShiftColumnsRight(columnNum + 1, numberOfColumns); XLRange range = (XLRange)this.Worksheet.Column(columnNum).AsRange(); range.InsertColumnsAfter(true, numberOfColumns); return Worksheet.Columns(columnNum + 1, columnNum + numberOfColumns); @@ -241,7 +247,7 @@ public new IXLColumns InsertColumnsBefore(Int32 numberOfColumns) { var columnNum = this.ColumnNumber(); - this.Worksheet.Internals.ColumnsCollection.ShiftColumnsRight(columnNum, numberOfColumns); + (Worksheet as XLWorksheet).Internals.ColumnsCollection.ShiftColumnsRight(columnNum, numberOfColumns); // We can't use this.AsRange() because we've shifted the columns // and we want to use the old columnNum. XLRange range = (XLRange)this.Worksheet.Column(columnNum).AsRange(); @@ -271,7 +277,7 @@ rangeAddressToUse = FixColumnAddress(rangeAddressStr); } - var rangeAddress = new XLRangeAddress(rangeAddressToUse); + var rangeAddress = new XLRangeAddress(Worksheet, rangeAddressToUse); return Range(rangeAddress); } public IXLRangeColumn Range(int firstRow, int lastRow) @@ -294,7 +300,7 @@ { Boolean isMerged = false; var cellAsRange = c.AsRange(); - foreach (var m in Worksheet.Internals.MergedRanges) + foreach (var m in (Worksheet as XLWorksheet).Internals.MergedRanges) { if (cellAsRange.Intersects(m)) { @@ -333,7 +339,7 @@ { if (IsReference) { - return Worksheet.Internals.ColumnsCollection[this.ColumnNumber()].IsHidden; + return (Worksheet as XLWorksheet).Internals.ColumnsCollection[this.ColumnNumber()].IsHidden; } else { @@ -344,7 +350,7 @@ { if (IsReference) { - Worksheet.Internals.ColumnsCollection[this.ColumnNumber()].IsHidden = value; + (Worksheet as XLWorksheet).Internals.ColumnsCollection[this.ColumnNumber()].IsHidden = value; } else { @@ -361,7 +367,7 @@ { if (IsReference) { - return Worksheet.Internals.ColumnsCollection[this.ColumnNumber()].Collapsed; + return (Worksheet as XLWorksheet).Internals.ColumnsCollection[this.ColumnNumber()].Collapsed; } else { @@ -372,7 +378,7 @@ { if (IsReference) { - Worksheet.Internals.ColumnsCollection[this.ColumnNumber()].Collapsed = value; + (Worksheet as XLWorksheet).Internals.ColumnsCollection[this.ColumnNumber()].Collapsed = value; } else { @@ -387,7 +393,7 @@ { if (IsReference) { - return Worksheet.Internals.ColumnsCollection[this.ColumnNumber()].OutlineLevel; + return (Worksheet as XLWorksheet).Internals.ColumnsCollection[this.ColumnNumber()].OutlineLevel; } else { @@ -401,12 +407,12 @@ if (IsReference) { - Worksheet.Internals.ColumnsCollection[this.ColumnNumber()].OutlineLevel = value; + (Worksheet as XLWorksheet).Internals.ColumnsCollection[this.ColumnNumber()].OutlineLevel = value; } else { - Worksheet.IncrementColumnOutline(value); - Worksheet.DecrementColumnOutline(outlineLevel); + (Worksheet as XLWorksheet).IncrementColumnOutline(value); + (Worksheet as XLWorksheet).DecrementColumnOutline(outlineLevel); outlineLevel = value; } } @@ -549,21 +555,16 @@ { return Range(start, 1, end, 1).Column(1); } + public IXLRangeColumns Columns(String columns) + { + var retVal = new XLRangeColumns(); + var columnPairs = columns.Split(','); + foreach (var pair in columnPairs) + { + this.AsRange().Columns(pair.Trim()).ForEach(c => retVal.Add(c)); + } + return retVal; + } - public new IXLColumn Replace(String oldValue, String newValue) - { - base.Replace(oldValue, newValue); - return this; - } - public new IXLColumn Replace(String oldValue, String newValue, XLSearchContents searchContents) - { - base.Replace(oldValue, newValue, searchContents); - return this; - } - public new IXLColumn Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions) - { - base.Replace(oldValue, newValue, searchContents, useRegularExpressions); - return this; - } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumnCollection.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumnCollection.cs index 30d84c2..80596da 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumnCollection.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumnCollection.cs @@ -22,7 +22,7 @@ Int32 newColumn = ro + columnsToShift; if (newColumn <= XLWorksheet.MaxNumberOfColumns) { - dictionary.Add(newColumn, new XLColumn(columnToMove, columnToMove.Worksheet)); + dictionary.Add(newColumn, new XLColumn(columnToMove)); } dictionary.Remove(ro); } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs index 28cc7d9..48ee06a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs @@ -7,13 +7,11 @@ { internal class XLColumns : IXLColumns, IXLStylized { - private Boolean entireWorksheet; private XLWorksheet worksheet; - public XLColumns(XLWorksheet worksheet, Boolean entireWorksheet = false) + public XLColumns(XLWorksheet worksheet) { this.worksheet = worksheet; - this.entireWorksheet = entireWorksheet; - style = new XLStyle(this, worksheet.Style); + style = new XLStyle(this, XLWorkbook.DefaultStyle); } List columns = new List(); @@ -41,29 +39,16 @@ set { style = new XLStyle(this, value); - //Styles.ForEach(s => s = new XLStyle(this, value)); - if (entireWorksheet) + if (worksheet != null) { worksheet.Style = value; } else { - var maxRow = 0; - if (worksheet.Internals.RowsCollection.Count > 0) - maxRow = worksheet.Internals.RowsCollection.Keys.Max(); - foreach (var col in columns) + foreach (var column in columns) { - col.Style = value; - foreach (var c in col.Worksheet.Internals.CellsCollection.Values.Where(c => c.Address.ColumnNumber == col.RangeAddress.FirstAddress.ColumnNumber)) - { - c.Style = value; - } - - for (var ro = 1; ro <= maxRow; ro++) - { - worksheet.Cell(ro, col.ColumnNumber()).Style = value; - } + column.Style = value; } } } @@ -75,27 +60,16 @@ { UpdatingStyle = true; yield return style; - if (entireWorksheet) + if (worksheet != null) { yield return worksheet.Style; } else { - var maxRow = 0; - if (worksheet.Internals.RowsCollection.Count > 0) - maxRow = worksheet.Internals.RowsCollection.Keys.Max(); foreach (var col in columns) { - yield return col.Style; - foreach (var c in col.Worksheet.Internals.CellsCollection.Values.Where(c => c.Address.ColumnNumber == col.RangeAddress.FirstAddress.ColumnNumber)) - { - yield return c.Style; - } - - for (var ro = 1; ro <= maxRow; ro++) - { - yield return worksheet.Cell(ro, col.ColumnNumber()).Style; - } + foreach (var s in col.Styles) + yield return s; } } UpdatingStyle = false; @@ -118,7 +92,7 @@ { columns.ForEach(c => c.Width = value); - if (entireWorksheet) + if (worksheet !=null) { worksheet.ColumnWidth = value; worksheet.Internals.ColumnsCollection.ForEach(c => c.Value.Width = value); @@ -128,20 +102,28 @@ public void Delete() { - if (entireWorksheet) + if (worksheet != null) { worksheet.Internals.ColumnsCollection.Clear(); worksheet.Internals.CellsCollection.Clear(); } else { - var toDelete = new List(); + var toDelete = new Dictionary>(); foreach (var c in columns) - toDelete.Add(c.ColumnNumber()); + { + if (!toDelete.ContainsKey(c.Worksheet)) + toDelete.Add(c.Worksheet, new List()); - foreach(var c in toDelete.OrderByDescending(c=>c)) - worksheet.Column(c).Delete(); - } + toDelete[c.Worksheet].Add(c.ColumnNumber()); + } + + foreach (var kp in toDelete) + { + foreach(var c in kp.Value.OrderByDescending(c=>c)) + kp.Key.Column(c).Delete(); + } + } } public void Add(XLColumn column) @@ -208,7 +190,7 @@ public IXLCells Cells() { - var cells = new XLCells(worksheet, false, false, false); + var cells = new XLCells(false, false, false); foreach (var container in columns) { cells.Add(container.RangeAddress); @@ -218,7 +200,7 @@ public IXLCells CellsUsed() { - var cells = new XLCells(worksheet, false, true, false); + var cells = new XLCells(false, true, false); foreach (var container in columns) { cells.Add(container.RangeAddress); @@ -228,7 +210,7 @@ public IXLCells CellsUsed(Boolean includeStyles) { - var cells = new XLCells(worksheet, false, true, includeStyles); + var cells = new XLCells(false, true, includeStyles); foreach (var container in columns) { cells.Add(container.RangeAddress); @@ -240,26 +222,12 @@ { get { - var retVal = new XLRanges(worksheet.Internals.Workbook, this.Style); + var retVal = new XLRanges(); this.ForEach(c => retVal.Add(c.AsRange())); return retVal; } } - public IXLColumns Replace(String oldValue, String newValue) - { - columns.ForEach(r => r.Replace(oldValue, newValue)); - return this; - } - public IXLColumns Replace(String oldValue, String newValue, XLSearchContents searchContents) - { - columns.ForEach(r => r.Replace(oldValue, newValue, searchContents)); - return this; - } - public IXLColumns Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions) - { - columns.ForEach(r => r.Replace(oldValue, newValue, searchContents, useRegularExpressions)); - return this; - } + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/IXLCustomProperties.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/IXLCustomProperties.cs index 380ba41..95eb1b0 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/IXLCustomProperties.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/IXLCustomProperties.cs @@ -11,5 +11,6 @@ void Add(String name, T value); void Delete(String name); IXLCustomProperty CustomProperty(String name); + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/XLCustomProperties.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/XLCustomProperties.cs index 6d13fa7..cae934b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/XLCustomProperties.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/XLCustomProperties.cs @@ -42,5 +42,7 @@ { return GetEnumerator(); } + + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/XLCustomProperty.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/XLCustomProperty.cs index 3a45ea5..d671aa4 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/XLCustomProperty.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/XLCustomProperty.cs @@ -21,7 +21,7 @@ } set { - if (workbook.CustomProperties.Where(t => t.Name == value).Any()) + if (workbook.CustomProperties.Any(t => t.Name == value)) throw new ArgumentException(String.Format("This workbook already contains a custom property named '{0}'", value)); name = value; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/IXLDataValidation.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/IXLDataValidation.cs index 214b046..211c9d1 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/IXLDataValidation.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/IXLDataValidation.cs @@ -11,7 +11,7 @@ public interface IXLDataValidation { IXLRanges Ranges { get; set; } - void Delete(); + //void Delete(); //void CopyFrom(IXLDataValidation dataValidation); Boolean ShowInputMessage { get; set; } Boolean ShowErrorMessage { get; set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/IXLDataValidations.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/IXLDataValidations.cs index 424a0f0..627910c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/IXLDataValidations.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/IXLDataValidations.cs @@ -9,5 +9,6 @@ { void Add(IXLDataValidation dataValidation); Boolean ContainsSingle(IXLRange range); + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidation.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidation.cs index 6c6a3c8..aab2010 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidation.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidation.cs @@ -30,15 +30,17 @@ public void Delete() { - foreach (var dv in worksheet.DataValidations) - { + throw new NotImplementedException(); + //foreach (var dv in worksheet.DataValidations) + //{ - } + //} } public void CopyFrom(IXLDataValidation dataValidation) { - Ranges = new XLRanges(worksheet.Internals.Workbook, worksheet.Style); - dataValidation.Ranges.ForEach(r => Ranges.Add(r)); + if (Ranges == null) + Ranges = new XLRanges(); + //dataValidation.Ranges.ForEach(r => Ranges.Add(r)); IgnoreBlanks = dataValidation.IgnoreBlanks; InCellDropdown = dataValidation.InCellDropdown; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidations.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidations.cs index b20d6f5..9c3531c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidations.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidations.cs @@ -37,5 +37,7 @@ } return false; } + + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/IXLHyperlinks.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/IXLHyperlinks.cs index e298d95..4ce3a1b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/IXLHyperlinks.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/IXLHyperlinks.cs @@ -10,5 +10,6 @@ void Add(XLHyperlink hyperlink); void Delete(XLHyperlink hyperlink); void Delete(IXLAddress address); + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/XLHyperlinks.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/XLHyperlinks.cs index 430409b..2cf6b9d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/XLHyperlinks.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/XLHyperlinks.cs @@ -32,5 +32,6 @@ { hyperlinks.Remove(address); } + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs index 79fe6da..2111609 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs @@ -8,6 +8,8 @@ public enum XLWorksheetVisibility { Visible, Hidden, VeryHidden } public interface IXLWorksheet: IXLRangeBase { + XLWorkbook Workbook { get; } + /// /// Gets or sets the default column width for this worksheet. /// @@ -287,20 +289,20 @@ IXLRange Sort(XLSortOrientation sortOrientation, XLSortOrder sortOrder, Boolean matchCase); IXLRange Sort(XLSortOrientation sortOrientation, String elementsToSortBy, Boolean matchCase); - IXLCharts Charts { get; } + //IXLCharts Charts { get; } - IXLRows FindRows(String search); - IXLRows FindRows(String search, XLSearchContents searchContents); - IXLRows FindRows(String search, XLSearchContents searchContents, Boolean useRegularExpressions); - IXLRows FindRows(String search, XLSearchContents searchContents, Boolean matchCase, Boolean entireCell); + Boolean ShowFormulas { get; set; } - IXLColumns FindColumns(String search); - IXLColumns FindColumns(String search, XLSearchContents searchContents); - IXLColumns FindColumns(String search, XLSearchContents searchContents, Boolean useRegularExpressions); - IXLColumns FindColumns(String search, XLSearchContents searchContents, Boolean matchCase, Boolean entireCell); + Boolean ShowGridLines { get; set; } - IXLWorksheet Replace(String oldValue, String newValue); - IXLWorksheet Replace(String oldValue, String newValue, XLSearchContents searchContents); - IXLWorksheet Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions); - } + Boolean ShowOutlineSymbols { get; set; } + + Boolean ShowRowColHeaders { get; set; } + + Boolean ShowRuler { get; set; } + + Boolean ShowWhiteSpace { get; set; } + + Boolean ShowZeros { get; set; } + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheets.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheets.cs index 7fb62f2..ed80778 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheets.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheets.cs @@ -2,6 +2,7 @@ using System.Collections.Generic; using System.Linq; using System.Text; +using System.Data; namespace ClosedXML.Excel { @@ -11,11 +12,11 @@ IXLWorksheet Worksheet(Int32 position); IXLWorksheet Add(String sheetName); IXLWorksheet Add(String sheetName, Int32 position); + IXLWorksheet Add(DataTable dataTable); + void Add(DataSet dataSet); void Delete(String sheetName); void Delete(Int32 position); - IXLWorksheets Replace(String oldValue, String newValue); - IXLWorksheets Replace(String oldValue, String newValue, XLSearchContents searchContents); - IXLWorksheets Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions); + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs index 2020d9f..a272302 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs @@ -40,7 +40,7 @@ /// Note: A named range can point to multiple ranges. /// /// The range address to add. - IXLRanges Add(String rangeAddress); + IXLRanges Add(XLWorkbook workbook, String rangeAddress); /// /// Adds the specified range to this named range. diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/IXLNamedRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/IXLNamedRanges.cs index ca8c88c..18f12ef 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/IXLNamedRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/IXLNamedRanges.cs @@ -78,5 +78,7 @@ /// Deletes all named ranges (not the cells). /// void DeleteAll(); + + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs index e68be0a..6bde0ef 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs @@ -30,7 +30,7 @@ { get { - var ranges = new XLRanges(namedRanges.Workbook, namedRanges.Workbook.Style); + var ranges = new XLRanges(); foreach (var rangeAddress in rangeList) { var byExclamation = rangeAddress.Split('!'); @@ -51,15 +51,20 @@ } public String Comment { get; set; } - public IXLRanges Add(String rangeAddress) + public IXLRanges Add(XLWorkbook workbook, String rangeAddress) { - var ranges = new XLRanges(namedRanges.Workbook, namedRanges.Workbook.Style); - ranges.Add(rangeAddress); + var ranges = new XLRanges(); + var byExclamation = rangeAddress.Split('!'); + var wsName = byExclamation[0].Replace("'", ""); + var rng = byExclamation[1]; + var rangeToAdd = workbook.Worksheets.Worksheet(wsName).Range(rng); + + ranges.Add(rangeToAdd); return Add(ranges); } public IXLRanges Add(IXLRange range) { - var ranges = new XLRanges(((XLRange)range).Worksheet.Internals.Workbook, range.Style); + var ranges = new XLRanges(); ranges.Add(range); return Add(ranges); } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs index e7eefb0..92cd994 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs @@ -42,7 +42,7 @@ } public IXLNamedRange Add(String rangeName, IXLRange range, String comment) { - var ranges = new XLRanges(((XLRange)range).Worksheet.Internals.Workbook, range.Style); + var ranges = new XLRanges(); ranges.Add(range); return Add(rangeName, ranges, comment); } @@ -86,5 +86,6 @@ #endregion + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs index a1d0002..760e580 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs @@ -240,19 +240,6 @@ IXLRange Sort(XLSortOrientation sortOrientation, XLSortOrder sortOrder, Boolean matchCase); IXLRange Sort(XLSortOrientation sortOrientation, String elementsToSortBy, Boolean matchCase); - IXLRangeRows FindRows(String search); - IXLRangeRows FindRows(String search, XLSearchContents searchContents); - IXLRangeRows FindRows(String search, XLSearchContents searchContents, Boolean useRegularExpressions); - IXLRangeRows FindRows(String search, XLSearchContents searchContents, Boolean matchCase, Boolean entireCell); - - IXLRangeColumns FindColumns(String search); - IXLRangeColumns FindColumns(String search, XLSearchContents searchContents); - IXLRangeColumns FindColumns(String search, XLSearchContents searchContents, Boolean useRegularExpressions); - IXLRangeColumns FindColumns(String search, XLSearchContents searchContents, Boolean matchCase, Boolean entireCell); - - IXLRange Replace(String oldValue, String newValue); - IXLRange Replace(String oldValue, String newValue, XLSearchContents searchContents); - IXLRange Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeAddress.cs index 827257f..d2bf8b3 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeAddress.cs @@ -7,6 +7,8 @@ { public interface IXLRangeAddress { + IXLWorksheet Worksheet { get; } + /// /// Gets or sets the first address in the range. /// diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs index f21b34e..318e268 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -2,6 +2,7 @@ using System.Collections.Generic; using System.Linq; using System.Text; +using System.Text.RegularExpressions; namespace ClosedXML.Excel { @@ -10,6 +11,8 @@ public interface IXLRangeBase { + IXLWorksheet Worksheet { get; } + /// /// Returns the collection of cells. /// @@ -200,11 +203,8 @@ String ToStringRelative(); String ToStringFixed(); - IXLCells FindCells(String search); - IXLCells FindCells(String search, XLSearchContents searchContents); - IXLCells FindCells(String search, XLSearchContents searchContents, Boolean useRegularExpressions); - IXLCells FindCells(String search, XLSearchContents searchContents, Boolean matchCase, Boolean entireCell); - IXLChart CreateChart(Int32 firstRow, Int32 firstColumn, Int32 lastRow, Int32 lastColumn); + + //IXLChart CreateChart(Int32 firstRow, Int32 firstColumn, Int32 lastRow, Int32 lastColumn); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs index 6b6ff9c..9c1ec7d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs @@ -88,12 +88,9 @@ IXLRangeColumn Sort(Boolean matchCase); IXLRangeColumn Sort(XLSortOrder sortOrder); IXLRangeColumn Sort(XLSortOrder sortOrder, Boolean matchCase); - - IXLRangeColumn Replace(String oldValue, String newValue); - IXLRangeColumn Replace(String oldValue, String newValue, XLSearchContents searchContents); - IXLRangeColumn Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions); - + IXLRangeColumn Column(Int32 start, Int32 end); + IXLRangeColumns Columns(String columns); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs index ff59304..2c4458c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs @@ -41,8 +41,7 @@ IXLStyle Style { get; set; } - IXLRangeColumns Replace(String oldValue, String newValue); - IXLRangeColumns Replace(String oldValue, String newValue, XLSearchContents searchContents); - IXLRangeColumns Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions); + + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs index b864e68..12e1806 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs @@ -96,11 +96,9 @@ IXLRangeRow Sort(XLSortOrder sortOrder); IXLRangeRow Sort(XLSortOrder sortOrder, Boolean matchCase); - IXLRangeRow Replace(String oldValue, String newValue); - IXLRangeRow Replace(String oldValue, String newValue, XLSearchContents searchContents); - IXLRangeRow Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions); IXLRangeRow Row(Int32 start, Int32 end); + IXLRangeRows Rows(String rows); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs index 341ab6b..f39ef9b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs @@ -40,8 +40,6 @@ IXLStyle Style { get; set; } - IXLRangeRows Replace(String oldValue, String newValue); - IXLRangeRows Replace(String oldValue, String newValue, XLSearchContents searchContents); - IXLRangeRows Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions); + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs index 5e6e5a3..a9fac2e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs @@ -67,8 +67,6 @@ IXLRanges SetValue(T value); - IXLRanges Replace(String oldValue, String newValue); - IXLRanges Replace(String oldValue, String newValue, XLSearchContents searchContents); - IXLRanges Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions); + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs index 3877ee5..a649692 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs @@ -13,11 +13,11 @@ public XLRange(XLRangeParameters xlRangeParameters): base(xlRangeParameters.RangeAddress) { this.RangeParameters = xlRangeParameters; - Worksheet = xlRangeParameters.Worksheet; + if (!xlRangeParameters.IgnoreEvents) { - Worksheet.RangeShiftedRows += new RangeShiftedRowsDelegate(Worksheet_RangeShiftedRows); - Worksheet.RangeShiftedColumns += new RangeShiftedColumnsDelegate(Worksheet_RangeShiftedColumns); + (Worksheet as XLWorksheet).RangeShiftedRows += new RangeShiftedRowsDelegate(Worksheet_RangeShiftedRows); + (Worksheet as XLWorksheet).RangeShiftedColumns += new RangeShiftedColumnsDelegate(Worksheet_RangeShiftedColumns); xlRangeParameters.IgnoreEvents = true; } this.defaultStyle = new XLStyle(this, xlRangeParameters.DefaultStyle); @@ -49,13 +49,13 @@ var columnCount = this.ColumnCount(); Int32 minColumnUsed = Int32.MaxValue; Int32 minColumnInCells = Int32.MaxValue; - if (this.Worksheet.Internals.CellsCollection.Any(c => c.Key.ColumnNumber >= firstColumn && c.Key.ColumnNumber <= columnCount)) - minColumnInCells = this.Worksheet.Internals.CellsCollection + if ((Worksheet as XLWorksheet).Internals.CellsCollection.Any(c => c.Key.ColumnNumber >= firstColumn && c.Key.ColumnNumber <= columnCount)) + minColumnInCells = (Worksheet as XLWorksheet).Internals.CellsCollection .Where(c => c.Key.ColumnNumber >= firstColumn && c.Key.ColumnNumber <= columnCount).Select(c => c.Key.ColumnNumber).Min(); Int32 minCoInColumns = Int32.MaxValue; - if (this.Worksheet.Internals.ColumnsCollection.Any(c => c.Key >= firstColumn && c.Key <= columnCount)) - minCoInColumns = this.Worksheet.Internals.ColumnsCollection + if ((Worksheet as XLWorksheet).Internals.ColumnsCollection.Any(c => c.Key >= firstColumn && c.Key <= columnCount)) + minCoInColumns = (Worksheet as XLWorksheet).Internals.ColumnsCollection .Where(c => c.Key >= firstColumn && c.Key <= columnCount).Select(c => c.Key).Min(); minColumnUsed = minColumnInCells < minCoInColumns ? minColumnInCells : minCoInColumns; @@ -71,13 +71,13 @@ var columnCount = this.ColumnCount(); Int32 maxColumnUsed = 0; Int32 maxColumnInCells = 0; - if (this.Worksheet.Internals.CellsCollection.Any(c => c.Key.ColumnNumber >= firstColumn && c.Key.ColumnNumber <= columnCount)) - maxColumnInCells = this.Worksheet.Internals.CellsCollection + if ((Worksheet as XLWorksheet).Internals.CellsCollection.Any(c => c.Key.ColumnNumber >= firstColumn && c.Key.ColumnNumber <= columnCount)) + maxColumnInCells = (Worksheet as XLWorksheet).Internals.CellsCollection .Where(c => c.Key.ColumnNumber >= firstColumn && c.Key.ColumnNumber <= columnCount).Select(c => c.Key.ColumnNumber).Max(); Int32 maxCoInColumns = 0; - if (this.Worksheet.Internals.ColumnsCollection.Any(c => c.Key >= firstColumn && c.Key <= columnCount)) - maxCoInColumns = this.Worksheet.Internals.ColumnsCollection + if ((Worksheet as XLWorksheet).Internals.ColumnsCollection.Any(c => c.Key >= firstColumn && c.Key <= columnCount)) + maxCoInColumns = (Worksheet as XLWorksheet).Internals.ColumnsCollection .Where(c => c.Key >= firstColumn && c.Key <= columnCount).Select(c => c.Key).Max(); maxColumnUsed = maxColumnInCells > maxCoInColumns ? maxColumnInCells : maxCoInColumns; @@ -102,13 +102,13 @@ var rowCount = this.RowCount(); Int32 minRowUsed = Int32.MaxValue; Int32 minRowInCells = Int32.MaxValue; - if (this.Worksheet.Internals.CellsCollection.Any(c => c.Key.RowNumber >= firstRow && c.Key.RowNumber <= rowCount)) - minRowInCells = this.Worksheet.Internals.CellsCollection + if ((Worksheet as XLWorksheet).Internals.CellsCollection.Any(c => c.Key.RowNumber >= firstRow && c.Key.RowNumber <= rowCount)) + minRowInCells = (Worksheet as XLWorksheet).Internals.CellsCollection .Where(c => c.Key.RowNumber >= firstRow && c.Key.RowNumber <= rowCount).Select(c => c.Key.RowNumber).Min(); Int32 minRoInRows = Int32.MaxValue; - if (this.Worksheet.Internals.RowsCollection.Any(r => r.Key >= firstRow && r.Key <= rowCount)) - minRoInRows = this.Worksheet.Internals.RowsCollection + if ((Worksheet as XLWorksheet).Internals.RowsCollection.Any(r => r.Key >= firstRow && r.Key <= rowCount)) + minRoInRows = (Worksheet as XLWorksheet).Internals.RowsCollection .Where(r => r.Key >= firstRow && r.Key <= rowCount).Select(r => r.Key).Min(); minRowUsed = minRowInCells < minRoInRows ? minRowInCells : minRoInRows; @@ -124,13 +124,13 @@ var rowCount = this.RowCount(); Int32 maxRowUsed = 0; Int32 maxRowInCells = 0; - if (this.Worksheet.Internals.CellsCollection.Any(c => c.Key.RowNumber >= firstRow && c.Key.RowNumber <= rowCount)) - maxRowInCells = this.Worksheet.Internals.CellsCollection + if ((Worksheet as XLWorksheet).Internals.CellsCollection.Any(c => c.Key.RowNumber >= firstRow && c.Key.RowNumber <= rowCount)) + maxRowInCells = (Worksheet as XLWorksheet).Internals.CellsCollection .Where(c => c.Key.RowNumber >= firstRow && c.Key.RowNumber <= rowCount).Select(c => c.Key.RowNumber).Max(); Int32 maxRoInRows = 0; - if (this.Worksheet.Internals.RowsCollection.Any(r => r.Key >= firstRow && r.Key <= rowCount)) - maxRoInRows = this.Worksheet.Internals.RowsCollection + if ((Worksheet as XLWorksheet).Internals.RowsCollection.Any(r => r.Key >= firstRow && r.Key <= rowCount)) + maxRoInRows = (Worksheet as XLWorksheet).Internals.RowsCollection .Where(r => r.Key >= firstRow && r.Key <= rowCount).Select(r => r.Key).Max(); maxRowUsed = maxRowInCells > maxRoInRows ? maxRowInCells : maxRoInRows; @@ -146,9 +146,7 @@ IXLAddress firstCellAddress = new XLAddress(Worksheet, RangeAddress.FirstAddress.RowNumber + row - 1, RangeAddress.FirstAddress.ColumnNumber, false, false); IXLAddress lastCellAddress = new XLAddress(Worksheet, RangeAddress.FirstAddress.RowNumber + row - 1, RangeAddress.LastAddress.ColumnNumber, false, false); return new XLRangeRow( - new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), - Worksheet, - Worksheet.Style)); + new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet.Style)); } public IXLRangeRow RowQuick(Int32 row) @@ -156,9 +154,7 @@ IXLAddress firstCellAddress = new XLAddress(Worksheet, RangeAddress.FirstAddress.RowNumber + row - 1, RangeAddress.FirstAddress.ColumnNumber, false, false); IXLAddress lastCellAddress = new XLAddress(Worksheet, RangeAddress.FirstAddress.RowNumber + row - 1, RangeAddress.LastAddress.ColumnNumber, false, false); return new XLRangeRow( - new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), - Worksheet, - Worksheet.Style), true); + new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress),Worksheet.Style), true); } public IXLRangeColumn Column(Int32 column) @@ -166,18 +162,14 @@ IXLAddress firstCellAddress = new XLAddress(Worksheet, RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber + column - 1, false, false); IXLAddress lastCellAddress = new XLAddress(Worksheet, RangeAddress.LastAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber + column - 1, false, false); return new XLRangeColumn( - new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), - Worksheet, - Worksheet.Style)); + new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress),Worksheet.Style)); } public IXLRangeColumn ColumnQuick(Int32 column) { IXLAddress firstCellAddress = new XLAddress(Worksheet, RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber + column - 1, false, false); IXLAddress lastCellAddress = new XLAddress(Worksheet, RangeAddress.LastAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber + column - 1, false, false); return new XLRangeColumn( - new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), - Worksheet, - Worksheet.Style), true); + new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet.Style), true); } public IXLRangeColumn Column(String column) { @@ -186,7 +178,7 @@ public IXLRangeColumns Columns() { - var retVal = new XLRangeColumns(Worksheet); + var retVal = new XLRangeColumns(); foreach (var c in Enumerable.Range(1, this.ColumnCount())) { retVal.Add(this.Column(c)); @@ -195,7 +187,7 @@ } public IXLRangeColumns Columns(Int32 firstColumn, Int32 lastColumn) { - var retVal = new XLRangeColumns(Worksheet); + var retVal = new XLRangeColumns(); for (var co = firstColumn; co <= lastColumn; co++) { @@ -209,7 +201,7 @@ } public IXLRangeColumns Columns(String columns) { - var retVal = new XLRangeColumns(Worksheet); + var retVal = new XLRangeColumns(); var columnPairs = columns.Split(','); foreach (var pair in columnPairs) { @@ -248,7 +240,7 @@ public IXLRangeRows Rows() { - var retVal = new XLRangeRows(Worksheet); + var retVal = new XLRangeRows(); foreach (var r in Enumerable.Range(1, this.RowCount())) { retVal.Add(this.Row(r)); @@ -257,7 +249,7 @@ } public IXLRangeRows Rows(Int32 firstRow, Int32 lastRow) { - var retVal = new XLRangeRows(Worksheet); + var retVal = new XLRangeRows(); for (var ro = firstRow; ro <= lastRow; ro++) { @@ -267,7 +259,7 @@ } public IXLRangeRows Rows(String rows) { - var retVal = new XLRangeRows(Worksheet); + var retVal = new XLRangeRows(); var rowPairs = rows.Split(','); foreach (var pair in rowPairs) { @@ -365,7 +357,7 @@ { var oldCell = rngToTranspose.Cell(ro, co); var newKey = rngToTranspose.Cell(co, ro).Address; // new XLAddress(Worksheet, c.Address.ColumnNumber, c.Address.RowNumber); - var newCell = new XLCell(newKey, oldCell.Style, Worksheet); + var newCell = new XLCell(newKey, oldCell.Style, Worksheet as XLWorksheet); newCell.CopyFrom(oldCell); cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(oldCell.Address); @@ -380,8 +372,8 @@ // cellsToInsert.Add(newKey, newCell); // cellsToDelete.Add(c.Address); //} - cellsToDelete.ForEach(c => this.Worksheet.Internals.CellsCollection.Remove(c)); - cellsToInsert.ForEach(c => this.Worksheet.Internals.CellsCollection.Add(c.Key, c.Value)); + cellsToDelete.ForEach(c => (Worksheet as XLWorksheet).Internals.CellsCollection.Remove(c)); + cellsToInsert.ForEach(c => (Worksheet as XLWorksheet).Internals.CellsCollection.Add(c.Key, c.Value)); } private void TransposeMerged(Int32 squareSide) @@ -394,15 +386,15 @@ List mergeToDelete = new List(); List mergeToInsert = new List(); - foreach (var merge in Worksheet.Internals.MergedRanges) + foreach (var merge in (Worksheet as XLWorksheet).Internals.MergedRanges) { if (this.Contains(merge)) { merge.RangeAddress.LastAddress = rngToTranspose.Cell(merge.ColumnCount(), merge.RowCount()).Address; } } - mergeToDelete.ForEach(m => this.Worksheet.Internals.MergedRanges.Remove(m)); - mergeToInsert.ForEach(m => this.Worksheet.Internals.MergedRanges.Add(m)); + mergeToDelete.ForEach(m => (Worksheet as XLWorksheet).Internals.MergedRanges.Remove(m)); + mergeToInsert.ForEach(m => (Worksheet as XLWorksheet).Internals.MergedRanges.Add(m)); } private void MoveOrClearForTranspose(XLTransposeOptions transposeOption, int rowCount, int columnCount) @@ -789,8 +781,8 @@ cell1.Address = cell2.Address; cell2.Address = cell1Address; - Worksheet.Internals.CellsCollection[cell1.Address] = cell1; - Worksheet.Internals.CellsCollection[cell2.Address] = cell2; + (Worksheet as XLWorksheet).Internals.CellsCollection[cell1.Address] = cell1; + (Worksheet as XLWorksheet).Internals.CellsCollection[cell2.Address] = cell2; } } @@ -871,8 +863,8 @@ cell1.Address = cell2.Address; cell2.Address = cell1Address; - Worksheet.Internals.CellsCollection[cell1.Address] = cell1; - Worksheet.Internals.CellsCollection[cell2.Address] = cell2; + (Worksheet as XLWorksheet).Internals.CellsCollection[cell1.Address] = cell1; + (Worksheet as XLWorksheet).Internals.CellsCollection[cell2.Address] = cell2; } } @@ -960,54 +952,6 @@ lastColumnNumber); } - public IXLRangeColumns FindColumns(String search) - { - return FindColumns(search, XLSearchContents.ValuesAndFormulas); - } - public IXLRangeColumns FindColumns(String search, XLSearchContents searchContents) - { - return FindColumns(search, searchContents, false, false); - } - public IXLRangeColumns FindColumns(String search, XLSearchContents searchContents, Boolean useRegularExpressions) - { - throw new NotImplementedException(); - } - public IXLRangeColumns FindColumns(String search, XLSearchContents searchContents, Boolean matchCase, Boolean entireCell) - { - throw new NotImplementedException(); - } - public IXLRangeRows FindRows(String search) - { - return FindRows(search, XLSearchContents.ValuesAndFormulas); - } - public IXLRangeRows FindRows(String search, XLSearchContents searchContents) - { - return FindRows(search, searchContents, false, false); - } - public IXLRangeRows FindRows(String search, XLSearchContents searchContents, Boolean useRegularExpressions) - { - throw new NotImplementedException(); - } - public IXLRangeRows FindRows(String search, XLSearchContents searchContents, Boolean matchCase, Boolean entireCell) - { - throw new NotImplementedException(); - } - - public new IXLRange Replace(String oldValue, String newValue) - { - base.Replace(oldValue, newValue); - return this; - } - public new IXLRange Replace(String oldValue, String newValue, XLSearchContents searchContents) - { - base.Replace(oldValue, newValue, searchContents); - return this; - } - public new IXLRange Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions) - { - base.Replace(oldValue, newValue, searchContents, useRegularExpressions); - return this; - } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs index bd5f2f4..0845dd0 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs @@ -7,7 +7,7 @@ { internal class XLRangeAddress: IXLRangeAddress { - //public IXLWorksheet Worksheet { get; set; } + public IXLWorksheet Worksheet { get; internal set; } private IXLAddress firstAddress; public IXLAddress FirstAddress @@ -37,19 +37,17 @@ public Boolean IsInvalid { get; set; } - public XLRangeAddress(String firstCellAddress, String lastCellAddress) - { - FirstAddress = new XLAddress(firstCellAddress); - LastAddress = new XLAddress(lastCellAddress); - } - public XLRangeAddress(IXLAddress firstAddress, IXLAddress lastAddress) { + if (firstAddress.Worksheet != lastAddress.Worksheet) + throw new ArgumentException("First and last addresses must be in the same worksheet"); + + Worksheet = firstAddress.Worksheet; FirstAddress = firstAddress; LastAddress = lastAddress; } - public XLRangeAddress(String rangeAddress) + public XLRangeAddress(IXLWorksheet worksheet, String rangeAddress) { String addressToUse; if (rangeAddress.Contains("!")) @@ -64,16 +62,17 @@ String[] arrRange = addressToUse.Split(':'); var firstPart = arrRange[0]; var secondPart = arrRange[1]; - firstAddress = new XLAddress(firstPart); - lastAddress = new XLAddress(secondPart); + firstAddress = new XLAddress(worksheet, firstPart); + lastAddress = new XLAddress(worksheet, secondPart); } else { - firstAddress = new XLAddress(addressToUse); - lastAddress = new XLAddress(addressToUse); + firstAddress = new XLAddress(worksheet, addressToUse); + lastAddress = new XLAddress(worksheet, addressToUse); } FirstAddress = firstAddress; LastAddress = lastAddress; + Worksheet = worksheet; } public override string ToString() @@ -93,14 +92,18 @@ public override bool Equals(object obj) { var other = (XLRangeAddress)obj; - return this.FirstAddress.Equals(other.FirstAddress) + return + this.Worksheet.Equals(other.Worksheet) + && this.FirstAddress.Equals(other.FirstAddress) && this.LastAddress.Equals(other.LastAddress); } public override int GetHashCode() { - return FirstAddress.GetHashCode() - ^ LastAddress.GetHashCode(); + return + Worksheet.GetHashCode() + ^ FirstAddress.GetHashCode() + ^ LastAddress.GetHashCode(); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index 05b67db..5f0372a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -2,6 +2,7 @@ using System.Collections.Generic; using System.Linq; using System.Text; +using System.Text.RegularExpressions; namespace ClosedXML.Excel { @@ -14,7 +15,13 @@ protected IXLStyle defaultStyle; public IXLRangeAddress RangeAddress { get; protected set; } - public XLWorksheet Worksheet { get; set; } + public IXLWorksheet Worksheet + { + get + { + return RangeAddress.Worksheet; + } + } public IXLCell FirstCell() { @@ -33,7 +40,7 @@ { var cellsUsed = CellsUsed(includeStyles); - if (cellsUsed.Count() == 0) + if (!cellsUsed.Any()) { return null; } @@ -42,6 +49,8 @@ var firstRow = cellsUsed.Min(c => c.Address.RowNumber); var firstColumn = cellsUsed.Min(c => c.Address.ColumnNumber); return Worksheet.Cell(firstRow, firstColumn); + //var firstAddress = cellsUsed.Min(c => c.Address); + //return Worksheet.Cell(firstAddress); } } @@ -52,8 +61,7 @@ public IXLCell LastCellUsed(Boolean includeStyles) { var cellsUsed = CellsUsed(includeStyles); - - if (cellsUsed.Count() == 0) + if (!cellsUsed.Any()) { return null; } @@ -62,6 +70,8 @@ var lastRow = cellsUsed.Max(c => c.Address.RowNumber); var lastColumn = cellsUsed.Max(c => c.Address.ColumnNumber); return Worksheet.Cell(lastRow, lastColumn); + // var lastAddress = cellsUsed.Max(c => c.Address); + // return Worksheet.Cell(lastAddress); } } @@ -80,29 +90,30 @@ } public IXLCell Cell(IXLAddress cellAddressInRange) { - return Cell((XLAddress)cellAddressInRange); + return Cell(cellAddressInRange.RowNumber, cellAddressInRange.ColumnNumber); } + public IXLCell Cell(XLAddress cellAddressInRange) { IXLAddress absoluteAddress = cellAddressInRange + (XLAddress)this.RangeAddress.FirstAddress - 1; - if (this.Worksheet.Internals.CellsCollection.ContainsKey(absoluteAddress)) + if ((Worksheet as XLWorksheet).Internals.CellsCollection.ContainsKey(absoluteAddress)) { - return this.Worksheet.Internals.CellsCollection[absoluteAddress]; + return (Worksheet as XLWorksheet).Internals.CellsCollection[absoluteAddress]; } else { IXLStyle style = this.Style; if (this.Style != null && this.Style.Equals(this.Worksheet.Style)) { - if (this.Worksheet.Internals.RowsCollection.ContainsKey(absoluteAddress.RowNumber) - && !this.Worksheet.Internals.RowsCollection[absoluteAddress.RowNumber].Style.Equals(this.Worksheet.Style)) - style = this.Worksheet.Internals.RowsCollection[absoluteAddress.RowNumber].Style; - else if (this.Worksheet.Internals.ColumnsCollection.ContainsKey(absoluteAddress.ColumnNumber) - && !this.Worksheet.Internals.ColumnsCollection[absoluteAddress.ColumnNumber].Style.Equals(this.Worksheet.Style)) - style = this.Worksheet.Internals.ColumnsCollection[absoluteAddress.ColumnNumber].Style; + if ((Worksheet as XLWorksheet).Internals.RowsCollection.ContainsKey(absoluteAddress.RowNumber) + && !(Worksheet as XLWorksheet).Internals.RowsCollection[absoluteAddress.RowNumber].Style.Equals(this.Worksheet.Style)) + style = (Worksheet as XLWorksheet).Internals.RowsCollection[absoluteAddress.RowNumber].Style; + else if ((Worksheet as XLWorksheet).Internals.ColumnsCollection.ContainsKey(absoluteAddress.ColumnNumber) + && !(Worksheet as XLWorksheet).Internals.ColumnsCollection[absoluteAddress.ColumnNumber].Style.Equals(this.Worksheet.Style)) + style = (Worksheet as XLWorksheet).Internals.ColumnsCollection[absoluteAddress.ColumnNumber].Style; } - var newCell = new XLCell(absoluteAddress, style, Worksheet); - this.Worksheet.Internals.CellsCollection.Add(absoluteAddress, newCell); + var newCell = new XLCell(absoluteAddress, style, Worksheet as XLWorksheet); + (Worksheet as XLWorksheet).Internals.CellsCollection.Add(absoluteAddress, newCell); return newCell; } } @@ -130,7 +141,7 @@ public virtual IXLRange Range(String rangeAddressStr) { - var rangeAddress = new XLRangeAddress(rangeAddressStr); + var rangeAddress = new XLRangeAddress(Worksheet, rangeAddressStr); return Range(rangeAddress); } @@ -140,7 +151,7 @@ } public IXLRange Range(String firstCellAddress, String lastCellAddress) { - var rangeAddress = new XLRangeAddress(firstCellAddress, lastCellAddress); + var rangeAddress = new XLRangeAddress(new XLAddress(Worksheet, firstCellAddress), new XLAddress(Worksheet, lastCellAddress)); return Range(rangeAddress); } public IXLRange Range(Int32 firstCellRow, Int32 firstCellColumn, Int32 lastCellRow, Int32 lastCellColumn) @@ -162,7 +173,7 @@ newLastCellAddress.FixedRow = rangeAddress.LastAddress.FixedRow; newLastCellAddress.FixedColumn = rangeAddress.LastAddress.FixedColumn; var newRangeAddress = new XLRangeAddress(newFirstCellAddress, newLastCellAddress); - var xlRangeParameters = new XLRangeParameters(newRangeAddress, this.Worksheet, this.Style); + var xlRangeParameters = new XLRangeParameters(newRangeAddress, this.Style); if ( newFirstCellAddress.RowNumber < this.RangeAddress.FirstAddress.RowNumber || newFirstCellAddress.RowNumber > this.RangeAddress.LastAddress.RowNumber @@ -178,7 +189,7 @@ public IXLRanges Ranges( String ranges) { - var retVal = new XLRanges(Worksheet.Internals.Workbook, Worksheet.Style); + var retVal = new XLRanges(); var rangePairs = ranges.Split(','); foreach (var pair in rangePairs) { @@ -188,7 +199,7 @@ } public IXLRanges Ranges(params String[] ranges) { - var retVal = new XLRanges(Worksheet.Internals.Workbook, Worksheet.Style); + var retVal = new XLRanges(); foreach (var pair in ranges) { retVal.Add(this.Range(pair)); @@ -213,19 +224,19 @@ } public IXLCells Cells() { - var cells = new XLCells(Worksheet, false, false, false); + var cells = new XLCells(false, false, false); cells.Add(this.RangeAddress); return (IXLCells)cells; } public IXLCells CellsUsed() { - var cells = new XLCells(Worksheet, false, true, false); + var cells = new XLCells(false, true, false); cells.Add(this.RangeAddress); return (IXLCells)cells; } public IXLCells CellsUsed(Boolean includeStyles) { - var cells = new XLCells(Worksheet, false, true, includeStyles); + var cells = new XLCells(false, true, includeStyles); cells.Add(this.RangeAddress); return (IXLCells)cells; } @@ -234,7 +245,7 @@ { var tAddress = this.RangeAddress.ToString(); Boolean foundOne = false; - foreach (var m in this.Worksheet.Internals.MergedRanges) + foreach (var m in (Worksheet as XLWorksheet).Internals.MergedRanges) { var mAddress = m.RangeAddress.ToString(); if (mAddress == tAddress) @@ -245,18 +256,18 @@ } if (!foundOne) - this.Worksheet.Internals.MergedRanges.Add(this.AsRange()); + (Worksheet as XLWorksheet).Internals.MergedRanges.Add(this.AsRange()); return AsRange(); } public IXLRange Unmerge() { var tAddress = this.RangeAddress.ToString(); - foreach (var m in this.Worksheet.Internals.MergedRanges) + foreach (var m in (Worksheet as XLWorksheet).Internals.MergedRanges) { var mAddress = m.RangeAddress.ToString(); if (mAddress == tAddress) { - this.Worksheet.Internals.MergedRanges.Remove(this.AsRange()); + (Worksheet as XLWorksheet).Internals.MergedRanges.Remove(this.AsRange()); break; } } @@ -313,7 +324,7 @@ } public IXLRangeColumns InsertColumnsBefore(Boolean onlyUsedCells, Int32 numberOfColumns) { - foreach (var ws in Worksheet.Internals.Workbook.Worksheets) + foreach (var ws in (Worksheet as XLWorksheet).Internals.Workbook.Worksheets) { var xlWorksheet = (XLWorksheet)ws; foreach (var cell in xlWorksheet.Internals.CellsCollection.Values.Where(c => !StringExtensions.IsNullOrWhiteSpace(c.FormulaA1))) @@ -341,15 +352,15 @@ var newColumn = co + numberOfColumns; var newKey = new XLAddress(Worksheet, ro, newColumn, false, false); IXLCell oldCell; - if (this.Worksheet.Internals.CellsCollection.ContainsKey(oldKey)) + if ((Worksheet as XLWorksheet).Internals.CellsCollection.ContainsKey(oldKey)) { - oldCell = this.Worksheet.Internals.CellsCollection[oldKey]; + oldCell = (Worksheet as XLWorksheet).Internals.CellsCollection[oldKey]; } else { oldCell = this.Worksheet.Cell(oldKey); } - var newCell = new XLCell(newKey, oldCell.Style, Worksheet); + var newCell = new XLCell(newKey, oldCell.Style, Worksheet as XLWorksheet); newCell.CopyValues((XLCell)oldCell); cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(oldKey); @@ -360,7 +371,7 @@ } else { - foreach (var c in this.Worksheet.Internals.CellsCollection + foreach (var c in (Worksheet as XLWorksheet).Internals.CellsCollection .Where(c => c.Key.ColumnNumber >= firstColumn && c.Key.RowNumber >= firstRow @@ -369,7 +380,7 @@ { var newColumn = c.Key.ColumnNumber + numberOfColumns; var newKey = new XLAddress(Worksheet, c.Key.RowNumber, newColumn, false, false); - var newCell = new XLCell(newKey, c.Value.Style, Worksheet); + var newCell = new XLCell(newKey, c.Value.Style, Worksheet as XLWorksheet); newCell.CopyValues(c.Value); cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(c.Key); @@ -377,19 +388,19 @@ cellsToBlank.Add(c.Key); } } - cellsToDelete.ForEach(c => this.Worksheet.Internals.CellsCollection.Remove(c)); - cellsToInsert.ForEach(c => this.Worksheet.Internals.CellsCollection.Add(c.Key, c.Value)); + cellsToDelete.ForEach(c => (Worksheet as XLWorksheet).Internals.CellsCollection.Remove(c)); + cellsToInsert.ForEach(c => (Worksheet as XLWorksheet).Internals.CellsCollection.Add(c.Key, c.Value)); foreach (var c in cellsToBlank) { IXLStyle styleToUse; - if (this.Worksheet.Internals.RowsCollection.ContainsKey(c.RowNumber)) - styleToUse = this.Worksheet.Internals.RowsCollection[c.RowNumber].Style; + if ((Worksheet as XLWorksheet).Internals.RowsCollection.ContainsKey(c.RowNumber)) + styleToUse = (Worksheet as XLWorksheet).Internals.RowsCollection[c.RowNumber].Style; else styleToUse = this.Worksheet.Style; - this.Worksheet.Cell(c).Style = styleToUse; + this.Worksheet.Cell(c.RowNumber, c.ColumnNumber).Style = styleToUse; } - Worksheet.NotifyRangeShiftedColumns((XLRange)this.AsRange(), numberOfColumns); + (Worksheet as XLWorksheet).NotifyRangeShiftedColumns((XLRange)this.AsRange(), numberOfColumns); return Worksheet.Range( RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber - numberOfColumns, @@ -447,10 +458,10 @@ } public IXLRangeRows InsertRowsAbove(Boolean onlyUsedCells, Int32 numberOfRows ) { - foreach (var ws in Worksheet.Internals.Workbook.Worksheets) + foreach (var ws in (Worksheet as XLWorksheet).Internals.Workbook.Worksheets) { var xlWorksheet = (XLWorksheet)ws; - foreach (var cell in xlWorksheet.Internals.CellsCollection.Values.Where(c => !StringExtensions.IsNullOrWhiteSpace(c.FormulaA1))) + foreach (var cell in (xlWorksheet as XLWorksheet).Internals.CellsCollection.Values.Where(c => !StringExtensions.IsNullOrWhiteSpace(c.FormulaA1))) { cell.ShiftFormulaRows((XLRange)this.AsRange(), numberOfRows); } @@ -475,15 +486,15 @@ var newRow = ro + numberOfRows; var newKey = new XLAddress(Worksheet, newRow, co, false, false); IXLCell oldCell; - if (this.Worksheet.Internals.CellsCollection.ContainsKey(oldKey)) + if ((Worksheet as XLWorksheet).Internals.CellsCollection.ContainsKey(oldKey)) { - oldCell = this.Worksheet.Internals.CellsCollection[oldKey]; + oldCell = (Worksheet as XLWorksheet).Internals.CellsCollection[oldKey]; } else { oldCell = this.Worksheet.Cell(oldKey); } - var newCell = new XLCell(newKey, oldCell.Style, Worksheet); + var newCell = new XLCell(newKey, oldCell.Style, Worksheet as XLWorksheet); newCell.CopyFrom(oldCell); cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(oldKey); @@ -494,7 +505,7 @@ } else { - foreach (var c in this.Worksheet.Internals.CellsCollection + foreach (var c in (Worksheet as XLWorksheet).Internals.CellsCollection .Where(c => c.Key.RowNumber >= firstRow && c.Key.ColumnNumber >= firstColumn @@ -503,7 +514,7 @@ { var newRow = c.Key.RowNumber + numberOfRows; var newKey = new XLAddress(Worksheet, newRow, c.Key.ColumnNumber, false, false); - var newCell = new XLCell(newKey, c.Value.Style, Worksheet); + var newCell = new XLCell(newKey, c.Value.Style, Worksheet as XLWorksheet); newCell.CopyFrom(c.Value); cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(c.Key); @@ -511,18 +522,18 @@ cellsToBlank.Add(c.Key); } } - cellsToDelete.ForEach(c => this.Worksheet.Internals.CellsCollection.Remove(c)); - cellsToInsert.ForEach(c => this.Worksheet.Internals.CellsCollection.Add(c.Key, c.Value)); + cellsToDelete.ForEach(c => (Worksheet as XLWorksheet).Internals.CellsCollection.Remove(c)); + cellsToInsert.ForEach(c => (Worksheet as XLWorksheet).Internals.CellsCollection.Add(c.Key, c.Value)); foreach (var c in cellsToBlank) { IXLStyle styleToUse; - if (this.Worksheet.Internals.ColumnsCollection.ContainsKey(c.ColumnNumber)) - styleToUse = this.Worksheet.Internals.ColumnsCollection[c.ColumnNumber].Style; + if ((Worksheet as XLWorksheet).Internals.ColumnsCollection.ContainsKey(c.ColumnNumber)) + styleToUse = (Worksheet as XLWorksheet).Internals.ColumnsCollection[c.ColumnNumber].Style; else styleToUse = this.Worksheet.Style; - this.Worksheet.Cell(c).Style = styleToUse; + this.Worksheet.Cell(c.RowNumber, c.ColumnNumber).Style = styleToUse; } - Worksheet.NotifyRangeShiftedRows((XLRange)this.AsRange(), numberOfRows); + (Worksheet as XLWorksheet).NotifyRangeShiftedRows((XLRange)this.AsRange(), numberOfRows); return Worksheet.Range( RangeAddress.FirstAddress.RowNumber - numberOfRows, RangeAddress.FirstAddress.ColumnNumber, @@ -534,7 +545,7 @@ public void Clear() { // Remove cells inside range - this.Worksheet.Internals.CellsCollection.RemoveAll(c => + (Worksheet as XLWorksheet).Internals.CellsCollection.RemoveAll(c => c.Address.ColumnNumber >= this.RangeAddress.FirstAddress.ColumnNumber && c.Address.ColumnNumber <= this.RangeAddress.LastAddress.ColumnNumber && c.Address.RowNumber >= this.RangeAddress.FirstAddress.RowNumber @@ -565,14 +576,14 @@ private void ClearMerged() { List mergeToDelete = new List(); - foreach (var merge in Worksheet.Internals.MergedRanges) + foreach (var merge in (Worksheet as XLWorksheet).Internals.MergedRanges) { if (this.Intersects(merge)) { mergeToDelete.Add(merge); } } - mergeToDelete.ForEach(m => this.Worksheet.Internals.MergedRanges.Remove(m)); + mergeToDelete.ForEach(m => (Worksheet as XLWorksheet).Internals.MergedRanges.Remove(m)); } public Boolean Contains(String rangeAddress) @@ -615,6 +626,7 @@ public Boolean Intersects(IXLRangeBase range) { + if (range.RangeAddress.IsInvalid || RangeAddress.IsInvalid) return false; var ma = range.RangeAddress; var ra = RangeAddress; @@ -636,20 +648,20 @@ var lastCell = Worksheet.Cell(XLWorksheet.MaxNumberOfRows, RangeAddress.LastAddress.ColumnNumber); shiftedRangeFormula = Worksheet.Range(RangeAddress.FirstAddress, lastCell.Address); if (StringExtensions.IsNullOrWhiteSpace(lastCell.GetString()) && StringExtensions.IsNullOrWhiteSpace(lastCell.FormulaA1)) - Worksheet.Internals.CellsCollection.Remove(lastCell.Address); + (Worksheet as XLWorksheet).Internals.CellsCollection.Remove(lastCell.Address); } else { var lastCell = Worksheet.Cell(RangeAddress.LastAddress.RowNumber, XLWorksheet.MaxNumberOfColumns); shiftedRangeFormula = Worksheet.Range(RangeAddress.FirstAddress, lastCell.Address); if (StringExtensions.IsNullOrWhiteSpace(lastCell.GetString()) && StringExtensions.IsNullOrWhiteSpace(lastCell.FormulaA1)) - Worksheet.Internals.CellsCollection.Remove(lastCell.Address); + (Worksheet as XLWorksheet).Internals.CellsCollection.Remove(lastCell.Address); } - foreach (var ws in Worksheet.Internals.Workbook.Worksheets) + foreach (var ws in (Worksheet as XLWorksheet).Internals.Workbook.Worksheets) { var xlWorksheet = (XLWorksheet)ws; - foreach (var cell in xlWorksheet.Internals.CellsCollection.Values.Where(c => !StringExtensions.IsNullOrWhiteSpace(c.FormulaA1))) + foreach (var cell in (xlWorksheet as XLWorksheet).Internals.CellsCollection.Values.Where(c => !StringExtensions.IsNullOrWhiteSpace(c.FormulaA1))) { if (shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp) cell.ShiftFormulaRows((XLRange)shiftedRangeFormula, numberOfRows * -1); @@ -661,13 +673,13 @@ // Range to shift... var cellsToInsert = new Dictionary(); var cellsToDelete = new List(); - var shiftLeftQuery = this.Worksheet.Internals.CellsCollection + var shiftLeftQuery = (Worksheet as XLWorksheet).Internals.CellsCollection .Where(c => c.Key.RowNumber >= this.RangeAddress.FirstAddress.RowNumber && c.Key.RowNumber <= this.RangeAddress.LastAddress.RowNumber && c.Key.ColumnNumber >= this.RangeAddress.FirstAddress.ColumnNumber); - var shiftUpQuery = this.Worksheet.Internals.CellsCollection + var shiftUpQuery = (Worksheet as XLWorksheet).Internals.CellsCollection .Where(c => c.Key.ColumnNumber >= this.RangeAddress.FirstAddress.ColumnNumber && c.Key.ColumnNumber <= this.RangeAddress.LastAddress.ColumnNumber @@ -679,7 +691,7 @@ foreach (var c in cellsQuery) { var newKey = new XLAddress(Worksheet, c.Key.RowNumber - rowModifier, c.Key.ColumnNumber - columnModifier, false, false); - var newCell = new XLCell(newKey, c.Value.Style, Worksheet); + var newCell = new XLCell(newKey, c.Value.Style, Worksheet as XLWorksheet); newCell.CopyValues(c.Value); //newCell.ShiftFormula(rowModifier * -1, columnModifier * -1); cellsToDelete.Add(c.Key); @@ -691,16 +703,16 @@ if (canInsert) cellsToInsert.Add(newKey, newCell); } - cellsToDelete.ForEach(c => this.Worksheet.Internals.CellsCollection.Remove(c)); - cellsToInsert.ForEach(c => this.Worksheet.Internals.CellsCollection.Add(c.Key, c.Value)); + cellsToDelete.ForEach(c => (Worksheet as XLWorksheet).Internals.CellsCollection.Remove(c)); + cellsToInsert.ForEach(c => (Worksheet as XLWorksheet).Internals.CellsCollection.Add(c.Key, c.Value)); List mergesToRemove = new List(); - foreach (var merge in Worksheet.Internals.MergedRanges) + foreach (var merge in (Worksheet as XLWorksheet).Internals.MergedRanges) { if (this.Contains(merge)) mergesToRemove.Add(merge); } - mergesToRemove.ForEach(r => Worksheet.Internals.MergedRanges.Remove(r)); + mergesToRemove.ForEach(r => (Worksheet as XLWorksheet).Internals.MergedRanges.Remove(r)); List hyperlinksToRemove = new List(); foreach (var hl in Worksheet.Hyperlinks) @@ -713,11 +725,11 @@ var shiftedRange = (XLRange)this.AsRange(); if (shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp) { - Worksheet.NotifyRangeShiftedRows(shiftedRange, rowModifier * -1); + (Worksheet as XLWorksheet).NotifyRangeShiftedRows(shiftedRange, rowModifier * -1); } else { - Worksheet.NotifyRangeShiftedColumns(shiftedRange, columnModifier * -1); + (Worksheet as XLWorksheet).NotifyRangeShiftedColumns(shiftedRange, columnModifier * -1); } } @@ -806,17 +818,17 @@ IXLNamedRanges namedRanges; if (scope == XLScope.Workbook) { - namedRanges = Worksheet.Internals.Workbook.NamedRanges; + namedRanges = (Worksheet as XLWorksheet).Internals.Workbook.NamedRanges; } else { namedRanges = Worksheet.NamedRanges; } - if (namedRanges.Where(nr => nr.Name.ToLower() == rangeName.ToLower()).Any()) + if (namedRanges.Any(nr => nr.Name.ToLower() == rangeName.ToLower())) { var namedRange = namedRanges.Where(nr => nr.Name.ToLower() == rangeName.ToLower()).Single(); - namedRange.Add(this.ToStringFixed()); + namedRange.Add((Worksheet as XLWorksheet).Internals.Workbook, this.ToStringFixed()); } else { @@ -973,9 +985,9 @@ dvEmpty.ForEach(dv => (Worksheet.DataValidations as XLDataValidations).Delete(dv)); - var newRanges = new XLRanges(Worksheet.Internals.Workbook, Style); + var newRanges = new XLRanges(); newRanges.Add(this.AsRange()); - var dataValidation = new XLDataValidation(newRanges, Worksheet); + var dataValidation = new XLDataValidation(newRanges, Worksheet as XLWorksheet); Worksheet.DataValidations.Add(dataValidation); return dataValidation; @@ -1009,7 +1021,7 @@ { get { - var retVal = new XLRanges(Worksheet.Internals.Workbook, this.Style); + var retVal = new XLRanges(); retVal.Add(this.AsRange()); return retVal; } @@ -1038,60 +1050,17 @@ Worksheet.AutoFilterRange = null; } - public IXLCells FindCells(String search) - { - return FindCells(search, XLSearchContents.ValuesAndFormulas); - } - public IXLCells FindCells(String search, XLSearchContents searchContents) - { - return FindCells(search, searchContents, false, false); - } - public IXLCells FindCells(String search, XLSearchContents searchContents, Boolean useRegularExpressions) - { - var cells = new XLCells(Worksheet, false, false, false); - foreach (var cell in CellsUsed()) - { - Boolean isMatch = false; - if (useRegularExpressions) - { + - } - else - { - //if (searchContents == XLSearchContents.Formulas - } - if (isMatch) - cells.Add(new XLRangeAddress(cell.Address.ToString())); - } - return cells; - } - public IXLCells FindCells(String search, XLSearchContents searchContents, Boolean matchCase, Boolean entireCell) - { - throw new NotImplementedException(); - } - - public IXLRangeBase Replace(String oldValue, String newValue) - { - return Replace(oldValue, newValue, XLSearchContents.ValuesAndFormulas); - } - public IXLRangeBase Replace(String oldValue, String newValue, XLSearchContents searchContents) - { - return Replace(oldValue, newValue, XLSearchContents.ValuesAndFormulas, false); - } - public IXLRangeBase Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions) - { - throw new NotImplementedException(); - } - - public IXLChart CreateChart(Int32 firstRow, Int32 firstColumn, Int32 lastRow, Int32 lastColumn) - { - IXLChart chart = new XLChart(Worksheet); - chart.FirstRow = firstRow; - chart.LastRow = lastRow; - chart.LastColumn = lastColumn; - chart.FirstColumn = firstColumn; - Worksheet.Charts.Add(chart); - return chart; - } + //public IXLChart CreateChart(Int32 firstRow, Int32 firstColumn, Int32 lastRow, Int32 lastColumn) + //{ + // IXLChart chart = new XLChart(Worksheet); + // chart.FirstRow = firstRow; + // chart.LastRow = lastRow; + // chart.LastColumn = lastColumn; + // chart.FirstColumn = firstColumn; + // Worksheet.Charts.Add(chart); + // return chart; + //} } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs index b5c1ad6..0b01341 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs @@ -11,15 +11,14 @@ public XLRangeColumn(XLRangeParameters xlRangeParameters) : base(xlRangeParameters.RangeAddress) { - Worksheet = xlRangeParameters.Worksheet; - Worksheet.RangeShiftedRows += new RangeShiftedRowsDelegate(Worksheet_RangeShiftedRows); - Worksheet.RangeShiftedColumns += new RangeShiftedColumnsDelegate(Worksheet_RangeShiftedColumns); + (Worksheet as XLWorksheet).RangeShiftedRows += new RangeShiftedRowsDelegate(Worksheet_RangeShiftedRows); + (Worksheet as XLWorksheet).RangeShiftedColumns += new RangeShiftedColumnsDelegate(Worksheet_RangeShiftedColumns); this.defaultStyle = new XLStyle(this, xlRangeParameters.DefaultStyle); } public XLRangeColumn(XLRangeParameters xlRangeParameters, Boolean quick) : base(xlRangeParameters.RangeAddress) { - Worksheet = xlRangeParameters.Worksheet; + } void Worksheet_RangeShiftedColumns(XLRange range, int columnsShifted) @@ -38,7 +37,7 @@ public IXLCells Cells(String cellsInColumn) { - var retVal = new XLCells(Worksheet, false, false, false); + var retVal = new XLCells(false, false, false); var rangePairs = cellsInColumn.Split(','); foreach (var pair in rangePairs) { @@ -74,7 +73,7 @@ rangeAddressToUse = FixColumnAddress(rangeAddressStr); } - var rangeAddress = new XLRangeAddress(rangeAddressToUse); + var rangeAddress = new XLRangeAddress(Worksheet, rangeAddressToUse); return Range(rangeAddress); } @@ -214,25 +213,37 @@ .Column(1); } - public new IXLRangeColumn Replace(String oldValue, String newValue) - { - base.Replace(oldValue, newValue); - return this; - } - public new IXLRangeColumn Replace(String oldValue, String newValue, XLSearchContents searchContents) - { - base.Replace(oldValue, newValue, searchContents); - return this; - } - public new IXLRangeColumn Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions) - { - base.Replace(oldValue, newValue, searchContents, useRegularExpressions); - return this; - } public IXLRangeColumn Column(Int32 start, Int32 end) { - return Range(start, 1, end, 1).Column(1); + return Range(start, end).FirstColumn(); + } + public IXLRangeColumns Columns(String columns) + { + var retVal = new XLRangeColumns(); + var rowPairs = columns.Split(','); + foreach (var pair in rowPairs) + { + var tPair = pair.Trim(); + String firstRow; + String lastRow; + if (tPair.Contains(':') || tPair.Contains('-')) + { + if (tPair.Contains('-')) + tPair = tPair.Replace('-', ':'); + + var rowRange = tPair.Split(':'); + firstRow = rowRange[0]; + lastRow = rowRange[1]; + } + else + { + firstRow = tPair; + lastRow = tPair; + } + retVal.Add(this.Range(firstRow, lastRow).FirstColumn()); + } + return retVal; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs index 14d8732..8350066 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs @@ -7,11 +7,9 @@ { internal class XLRangeColumns : IXLRangeColumns, IXLStylized { - XLWorksheet worksheet; - public XLRangeColumns(XLWorksheet worksheet) + public XLRangeColumns() { - style = new XLStyle(this, worksheet.Style); - this.worksheet = worksheet; + style = new XLStyle(this, XLWorkbook.DefaultStyle); } List ranges = new List(); @@ -69,7 +67,7 @@ foreach (var rng in ranges) { yield return rng.Style; - foreach (var r in rng.Worksheet.Internals.CellsCollection.Values.Where(c => + foreach (var r in (rng.Worksheet as XLWorksheet).Internals.CellsCollection.Values.Where(c => c.Address.RowNumber >= rng.RangeAddress.FirstAddress.RowNumber && c.Address.RowNumber <= rng.RangeAddress.LastAddress.RowNumber && c.Address.ColumnNumber >= rng.RangeAddress.FirstAddress.ColumnNumber @@ -96,7 +94,7 @@ public IXLCells Cells() { - var cells = new XLCells(worksheet, false, false, false); + var cells = new XLCells(false, false, false); foreach (var container in ranges) { cells.Add(container.RangeAddress); @@ -106,7 +104,7 @@ public IXLCells CellsUsed() { - var cells = new XLCells(worksheet, false, true, false); + var cells = new XLCells(false, true, false); foreach (var container in ranges) { cells.Add(container.RangeAddress); @@ -116,7 +114,7 @@ public IXLCells CellsUsed(Boolean includeStyles) { - var cells = new XLCells(worksheet, false, true, includeStyles); + var cells = new XLCells(false, true, includeStyles); foreach (var container in ranges) { cells.Add(container.RangeAddress); @@ -128,26 +126,13 @@ { get { - var retVal = new XLRanges(worksheet.Internals.Workbook, this.Style); + var retVal = new XLRanges(); this.ForEach(c => retVal.Add(c.AsRange())); return retVal; } } - public IXLRangeColumns Replace(String oldValue, String newValue) - { - ranges.ForEach(r => r.Replace(oldValue, newValue)); - return this; - } - public IXLRangeColumns Replace(String oldValue, String newValue, XLSearchContents searchContents) - { - ranges.ForEach(r => r.Replace(oldValue, newValue, searchContents)); - return this; - } - public IXLRangeColumns Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions) - { - ranges.ForEach(r => r.Replace(oldValue, newValue, searchContents, useRegularExpressions)); - return this; - } + + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeParameters.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeParameters.cs index a6dd5d1..2d745f0 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeParameters.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeParameters.cs @@ -8,17 +8,17 @@ { internal class XLRangeParameters { - public XLRangeParameters(IXLRangeAddress rangeAddress, XLWorksheet worksheet, IXLStyle defaultStyle) + public XLRangeParameters(IXLRangeAddress rangeAddress, IXLStyle defaultStyle) { RangeAddress = rangeAddress; - Worksheet = worksheet; + DefaultStyle = defaultStyle; } #region Properties // Public public IXLRangeAddress RangeAddress { get; private set; } - public XLWorksheet Worksheet { get; private set; } + public IXLStyle DefaultStyle { get; private set; } public Boolean IgnoreEvents { get; set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs index 9d3563c..6817c02 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs @@ -12,16 +12,16 @@ public XLRangeRow(XLRangeParameters xlRangeParameters): base(xlRangeParameters.RangeAddress) { this.RangeParameters = xlRangeParameters; - Worksheet = xlRangeParameters.Worksheet; - Worksheet.RangeShiftedRows += new RangeShiftedRowsDelegate(Worksheet_RangeShiftedRows); - Worksheet.RangeShiftedColumns += new RangeShiftedColumnsDelegate(Worksheet_RangeShiftedColumns); + + (Worksheet as XLWorksheet).RangeShiftedRows += new RangeShiftedRowsDelegate(Worksheet_RangeShiftedRows); + (Worksheet as XLWorksheet).RangeShiftedColumns += new RangeShiftedColumnsDelegate(Worksheet_RangeShiftedColumns); this.defaultStyle = new XLStyle(this, xlRangeParameters.DefaultStyle); } public XLRangeRow(XLRangeParameters xlRangeParameters, Boolean quick) : base(xlRangeParameters.RangeAddress) { this.RangeParameters = xlRangeParameters; - Worksheet = xlRangeParameters.Worksheet; + } void Worksheet_RangeShiftedColumns(XLRange range, int columnsShifted) @@ -72,7 +72,7 @@ public IXLCells Cells(String cellsInRow) { - var retVal = new XLCells(Worksheet, false, false, false); + var retVal = new XLCells(false, false, false); var rangePairs = cellsInRow.Split(','); foreach (var pair in rangePairs) { @@ -99,7 +99,7 @@ rangeAddressToUse = FixRowAddress(rangeAddressStr); } - var rangeAddress = new XLRangeAddress(rangeAddressToUse); + var rangeAddress = new XLRangeAddress(Worksheet, rangeAddressToUse); return Range(rangeAddress); } @@ -227,26 +227,39 @@ .Row(1); } - public new IXLRangeRow Replace(String oldValue, String newValue) - { - base.Replace(oldValue, newValue); - return this; - } - public new IXLRangeRow Replace(String oldValue, String newValue, XLSearchContents searchContents) - { - base.Replace(oldValue, newValue, searchContents); - return this; - } - public new IXLRangeRow Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions) - { - base.Replace(oldValue, newValue, searchContents, useRegularExpressions); - return this; - } public IXLRangeRow Row(Int32 start, Int32 end) { return Range(1, start, 1, end).Row(1); } + public IXLRangeRows Rows(String rows) + { + var retVal = new XLRangeRows(); + var columnPairs = rows.Split(','); + foreach (var pair in columnPairs) + { + var tPair = pair.Trim(); + String firstColumn; + String lastColumn; + if (tPair.Contains(':') || tPair.Contains('-')) + { + if (tPair.Contains('-')) + tPair = tPair.Replace('-', ':'); + + var columnRange = tPair.Split(':'); + firstColumn = columnRange[0]; + lastColumn = columnRange[1]; + } + else + { + firstColumn = tPair; + lastColumn = tPair; + } + + retVal.Add(this.Range(firstColumn, lastColumn).FirstRow()); + } + return retVal; + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs index 70338b4..c7142ff 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs @@ -7,11 +7,9 @@ { internal class XLRangeRows : IXLRangeRows, IXLStylized { - XLWorksheet worksheet; - public XLRangeRows(XLWorksheet worksheet) + public XLRangeRows() { - style = new XLStyle(this, worksheet.Style); - this.worksheet = worksheet; + style = new XLStyle(this, XLWorkbook.DefaultStyle); } List ranges = new List(); @@ -69,7 +67,7 @@ foreach (var rng in ranges) { yield return rng.Style; - foreach (var r in rng.Worksheet.Internals.CellsCollection.Values.Where(c => + foreach (var r in (rng.Worksheet as XLWorksheet).Internals.CellsCollection.Values.Where(c => c.Address.RowNumber >= rng.RangeAddress.FirstAddress.RowNumber && c.Address.RowNumber <= rng.RangeAddress.LastAddress.RowNumber && c.Address.ColumnNumber >= rng.RangeAddress.FirstAddress.ColumnNumber @@ -95,7 +93,7 @@ public IXLCells Cells() { - var cells = new XLCells(worksheet, false, false, false); + var cells = new XLCells(false, false, false); foreach (var container in ranges) { cells.Add(container.RangeAddress); @@ -105,7 +103,7 @@ public IXLCells CellsUsed() { - var cells = new XLCells(worksheet, false, true, false); + var cells = new XLCells(false, true, false); foreach (var container in ranges) { cells.Add(container.RangeAddress); @@ -115,7 +113,7 @@ public IXLCells CellsUsed(Boolean includeStyles) { - var cells = new XLCells(worksheet, false, true, includeStyles); + var cells = new XLCells(false, true, includeStyles); foreach (var container in ranges) { cells.Add(container.RangeAddress); @@ -127,26 +125,12 @@ { get { - var retVal = new XLRanges(worksheet.Internals.Workbook, this.Style); + var retVal = new XLRanges(); this.ForEach(c => retVal.Add(c.AsRange())); return retVal; } } - public IXLRangeRows Replace(String oldValue, String newValue) - { - ranges.ForEach(r => r.Replace(oldValue, newValue)); - return this; - } - public IXLRangeRows Replace(String oldValue, String newValue, XLSearchContents searchContents) - { - ranges.ForEach(r => r.Replace(oldValue, newValue, searchContents)); - return this; - } - public IXLRangeRows Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions) - { - ranges.ForEach(r => r.Replace(oldValue, newValue, searchContents, useRegularExpressions)); - return this; - } + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs index a5f3d2e..483ec66 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs @@ -7,11 +7,9 @@ { internal class XLRanges : IXLRanges, IXLStylized { - private XLWorkbook workbook; - public XLRanges(XLWorkbook workbook, IXLStyle defaultStyle) + public XLRanges() { - this.workbook = workbook; - Style = defaultStyle; + this.style = new XLStyle(this, XLWorkbook.DefaultStyle); } List ranges = new List(); @@ -26,15 +24,15 @@ count++; ranges.Add((XLRange)range); } - public void Add(String rangeAddress) - { - count++; - var byExclamation = rangeAddress.Split('!'); - var wsName = byExclamation[0].Replace("'", ""); - var rng = byExclamation[1]; - var rangeToAdd = workbook.Worksheets.Worksheet(wsName).Range(rng); - ranges.Add((XLRange)rangeToAdd); - } + //public void Add(String rangeAddress) + //{ + // count++; + // var byExclamation = rangeAddress.Split('!'); + // var wsName = byExclamation[0].Replace("'", ""); + // var rng = byExclamation[1]; + // var rangeToAdd = workbook.Worksheets.Worksheet(wsName).Range(rng); + // ranges.Add((XLRange)rangeToAdd); + //} public void Remove(IXLRange range) { count--; @@ -87,7 +85,7 @@ foreach (var rng in ranges) { yield return rng.Style; - foreach (var r in rng.Worksheet.Internals.CellsCollection.Values.Where(c => + foreach (var r in (rng.Worksheet as XLWorksheet).Internals.CellsCollection.Values.Where(c => c.Address.RowNumber >= rng.RangeAddress.FirstAddress.RowNumber && c.Address.RowNumber <= rng.RangeAddress.LastAddress.RowNumber && c.Address.ColumnNumber >= rng.RangeAddress.FirstAddress.ColumnNumber @@ -152,7 +150,7 @@ { foreach (var r in this.ranges) { - if (r.Equals(range)) return true; + if (!r.RangeAddress.IsInvalid && r.Equals(range)) return true; } return false; } @@ -190,7 +188,7 @@ } } } - var dataValidation = new XLDataValidation(this, ranges.First().Worksheet); + var dataValidation = new XLDataValidation(this, ranges.First().Worksheet as XLWorksheet); ranges.First().Worksheet.DataValidations.Add(dataValidation); return dataValidation; @@ -232,21 +230,6 @@ return this; } } - - public IXLRanges Replace(String oldValue, String newValue) - { - ranges.ForEach(r => r.Replace(oldValue, newValue)); - return this; - } - public IXLRanges Replace(String oldValue, String newValue, XLSearchContents searchContents) - { - ranges.ForEach(r => r.Replace(oldValue, newValue, searchContents)); - return this; - } - public IXLRanges Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions) - { - ranges.ForEach(r => r.Replace(oldValue, newValue, searchContents, useRegularExpressions)); - return this; - } + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs index d90866e..d2cb7fe 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs @@ -7,6 +7,8 @@ { public interface IXLRow : IXLRangeBase { + + /// /// Gets or sets the height of this row. /// @@ -163,9 +165,6 @@ IXLRow Sort(XLSortOrder sortOrder, Boolean matchCase); IXLRangeRow Row(Int32 start, Int32 end); - - IXLRow Replace(String oldValue, String newValue); - IXLRow Replace(String oldValue, String newValue, XLSearchContents searchContents); - IXLRow Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions); + IXLRangeRows Rows(String columns); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs index 3bb21b6..0f591d7 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs @@ -105,8 +105,6 @@ IXLStyle Style { get; set; } - IXLRows Replace(String oldValue, String newValue); - IXLRows Replace(String oldValue, String newValue, XLSearchContents searchContents); - IXLRows Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions); + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs index 5ba348d..4b6bb7c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs @@ -12,12 +12,11 @@ : base(new XLRangeAddress(new XLAddress(xlRowParameters.Worksheet, row, 1, false, false), new XLAddress(xlRowParameters.Worksheet, row, XLWorksheet.MaxNumberOfColumns, false, false))) { SetRowNumber(row); - Worksheet = xlRowParameters.Worksheet; - + this.IsReference = xlRowParameters.IsReference; if (IsReference) { - Worksheet.RangeShiftedRows += new RangeShiftedRowsDelegate(Worksheet_RangeShiftedRows); + (Worksheet as XLWorksheet).RangeShiftedRows += new RangeShiftedRowsDelegate(Worksheet_RangeShiftedRows); } else { @@ -26,15 +25,14 @@ } } - public XLRow(XLRow row, XLWorksheet worksheet) - : base(new XLRangeAddress(new XLAddress(worksheet, row.RowNumber(), 1, false, false), new XLAddress(worksheet, row.RowNumber(), XLWorksheet.MaxNumberOfColumns, false, false))) + public XLRow(XLRow row) + : base(new XLRangeAddress(new XLAddress(row.Worksheet, row.RowNumber(), 1, false, false), new XLAddress(row.Worksheet, row.RowNumber(), XLWorksheet.MaxNumberOfColumns, false, false))) { height = row.height; IsReference = row.IsReference; collapsed = row.collapsed; isHidden = row.isHidden; outlineLevel = row.outlineLevel; - this.Worksheet = worksheet; style = new XLStyle(this, row.Style); } @@ -74,7 +72,7 @@ { if (IsReference) { - return Worksheet.Internals.RowsCollection[this.RowNumber()].Height; + return (Worksheet as XLWorksheet).Internals.RowsCollection[this.RowNumber()].Height; } else { @@ -85,7 +83,7 @@ { if (IsReference) { - Worksheet.Internals.RowsCollection[this.RowNumber()].Height = value; + (Worksheet as XLWorksheet).Internals.RowsCollection[this.RowNumber()].Height = value; } else { @@ -98,13 +96,13 @@ { var rowNumber = this.RowNumber(); this.AsRange().Delete(XLShiftDeletedCells.ShiftCellsUp); - Worksheet.Internals.RowsCollection.Remove(rowNumber); + (Worksheet as XLWorksheet).Internals.RowsCollection.Remove(rowNumber); List rowsToMove = new List(); - rowsToMove.AddRange(Worksheet.Internals.RowsCollection.Where(c => c.Key > rowNumber).Select(c => c.Key)); + rowsToMove.AddRange((Worksheet as XLWorksheet).Internals.RowsCollection.Where(c => c.Key > rowNumber).Select(c => c.Key)); foreach (var row in rowsToMove.OrderBy(r=>r)) { - Worksheet.Internals.RowsCollection.Add(row - 1, Worksheet.Internals.RowsCollection[row]); - Worksheet.Internals.RowsCollection.Remove(row); + (Worksheet as XLWorksheet).Internals.RowsCollection.Add(row - 1, (Worksheet as XLWorksheet).Internals.RowsCollection[row]); + (Worksheet as XLWorksheet).Internals.RowsCollection.Remove(row); } } @@ -112,7 +110,7 @@ public new IXLRows InsertRowsBelow(Int32 numberOfRows) { var rowNum = this.RowNumber(); - this.Worksheet.Internals.RowsCollection.ShiftRowsDown(rowNum + 1, numberOfRows); + (Worksheet as XLWorksheet).Internals.RowsCollection.ShiftRowsDown(rowNum + 1, numberOfRows); XLRange range = (XLRange)this.Worksheet.Row(rowNum).AsRange(); range.InsertRowsBelow(true, numberOfRows); return Worksheet.Rows(rowNum + 1, rowNum + numberOfRows); @@ -121,7 +119,7 @@ public new IXLRows InsertRowsAbove(Int32 numberOfRows) { var rowNum = this.RowNumber(); - this.Worksheet.Internals.RowsCollection.ShiftRowsDown(rowNum, numberOfRows); + (Worksheet as XLWorksheet).Internals.RowsCollection.ShiftRowsDown(rowNum, numberOfRows); // We can't use this.AsRange() because we've shifted the rows // and we want to use the old rowNum. XLRange range = (XLRange)this.Worksheet.Row(rowNum).AsRange(); @@ -147,7 +145,7 @@ public IXLCells Cells(String cellsInRow) { - var retVal = new XLCells(Worksheet, false, false, false); + var retVal = new XLCells(false, false, false); var rangePairs = cellsInRow.Split(','); foreach (var pair in rangePairs) { @@ -174,7 +172,7 @@ rangeAddressToUse = FixRowAddress(rangeAddressStr); } - var rangeAddress = new XLRangeAddress(rangeAddressToUse); + var rangeAddress = new XLRangeAddress(Worksheet, rangeAddressToUse); return Range(rangeAddress); } @@ -203,7 +201,7 @@ { Boolean isMerged = false; var cellAsRange = c.AsRange(); - foreach (var m in Worksheet.Internals.MergedRanges) + foreach (var m in (Worksheet as XLWorksheet).Internals.MergedRanges) { if (cellAsRange.Intersects(m)) { @@ -241,7 +239,7 @@ { if (IsReference) { - return Worksheet.Internals.RowsCollection[this.RowNumber()].IsHidden; + return (Worksheet as XLWorksheet).Internals.RowsCollection[this.RowNumber()].IsHidden; } else { @@ -252,7 +250,7 @@ { if (IsReference) { - Worksheet.Internals.RowsCollection[this.RowNumber()].IsHidden = value; + (Worksheet as XLWorksheet).Internals.RowsCollection[this.RowNumber()].IsHidden = value; } else { @@ -269,14 +267,14 @@ { if (IsReference) { - Worksheet.Internals.RowsCollection[this.RowNumber()].SetStyleNoColumns(value); + (Worksheet as XLWorksheet).Internals.RowsCollection[this.RowNumber()].SetStyleNoColumns(value); } else { style = new XLStyle(this, value); var row = this.RowNumber(); - foreach (var c in Worksheet.Internals.CellsCollection.Values.Where(c => c.Address.RowNumber == row)) + foreach (var c in (Worksheet as XLWorksheet).Internals.CellsCollection.Values.Where(c => c.Address.RowNumber == row)) { c.Style = value; } @@ -289,7 +287,7 @@ get { if (IsReference) - return Worksheet.Internals.RowsCollection[this.RowNumber()].Style; + return (Worksheet as XLWorksheet).Internals.RowsCollection[this.RowNumber()].Style; else return style; } @@ -297,27 +295,34 @@ { if (IsReference) { - Worksheet.Internals.RowsCollection[this.RowNumber()].Style = value; + (Worksheet as XLWorksheet).Internals.RowsCollection[this.RowNumber()].Style = value; } else { style = new XLStyle(this, value); - var row = this.RowNumber(); - foreach (var c in Worksheet.Internals.CellsCollection.Values.Where(c => c.Address.RowNumber == row)) - { - c.Style = value; - } - Int32 maxColumn = 0; Int32 minColumn = 1; - if (Worksheet.Internals.ColumnsCollection.Count > 0) + Int32 maxColumn = 0; + var row = this.RowNumber(); + if ((Worksheet as XLWorksheet).Internals.CellsCollection.Values.Any(c => c.Address.RowNumber == row)) { - maxColumn = Worksheet.Internals.ColumnsCollection.Keys.Max(); - minColumn = Worksheet.Internals.ColumnsCollection.Keys.Min(); + minColumn = (Worksheet as XLWorksheet).Internals.CellsCollection.Values + .Where(c => c.Address.RowNumber == row) + .Min(c => c.Address.ColumnNumber); + maxColumn = (Worksheet as XLWorksheet).Internals.CellsCollection.Values + .Where(c => c.Address.RowNumber == row) + .Max(c => c.Address.ColumnNumber); } - + if ((Worksheet as XLWorksheet).Internals.ColumnsCollection.Count > 0) + { + Int32 minInCollection = (Worksheet as XLWorksheet).Internals.ColumnsCollection.Keys.Min(); + Int32 maxInCollection = (Worksheet as XLWorksheet).Internals.ColumnsCollection.Keys.Max(); + if (minInCollection < minColumn) minColumn = minInCollection; + if (maxInCollection > maxColumn) maxColumn = maxInCollection; + } + for (Int32 co = minColumn; co <= maxColumn; co++) { Worksheet.Cell(row, co).Style = value; @@ -332,20 +337,21 @@ { UpdatingStyle = true; - yield return style; + yield return Style; var row = this.RowNumber(); + Int32 minColumn = 1; + Int32 maxColumn = 0; + if ((Worksheet as XLWorksheet).Internals.CellsCollection.Values.Any(c => c.Address.RowNumber == row)) + maxColumn = (Worksheet as XLWorksheet).Internals.CellsCollection.Values.Where(c => c.Address.RowNumber == row).Max(c => c.Address.ColumnNumber); - foreach (var c in Worksheet.Internals.CellsCollection.Values.Where(c => c.Address.RowNumber == row)) + if ((Worksheet as XLWorksheet).Internals.ColumnsCollection.Count > 0) { - yield return c.Style; + Int32 maxInCollection = (Worksheet as XLWorksheet).Internals.ColumnsCollection.Keys.Max(); + if (maxInCollection > maxColumn) maxColumn = maxInCollection; } - - var maxColumn = 0; - if (Worksheet.Internals.ColumnsCollection.Count > 0) - maxColumn = Worksheet.Internals.ColumnsCollection.Keys.Max(); - for (var co = 1; co <= maxColumn; co++) + for (var co = minColumn; co <= maxColumn; co++) { yield return Worksheet.Cell(row, co).Style; } @@ -361,7 +367,7 @@ get { if (IsReference) - return Worksheet.Internals.RowsCollection[this.RowNumber()].InnerStyle; + return (Worksheet as XLWorksheet).Internals.RowsCollection[this.RowNumber()].InnerStyle; else return new XLStyle(new XLStylizedContainer(this.style, this), style); } @@ -369,7 +375,7 @@ { if (IsReference) { - Worksheet.Internals.RowsCollection[this.RowNumber()].InnerStyle = value; + (Worksheet as XLWorksheet).Internals.RowsCollection[this.RowNumber()].InnerStyle = value; } else { @@ -392,7 +398,7 @@ { if (IsReference) { - return Worksheet.Internals.RowsCollection[this.RowNumber()].Collapsed; + return (Worksheet as XLWorksheet).Internals.RowsCollection[this.RowNumber()].Collapsed; } else { @@ -403,7 +409,7 @@ { if (IsReference) { - Worksheet.Internals.RowsCollection[this.RowNumber()].Collapsed = value; + (Worksheet as XLWorksheet).Internals.RowsCollection[this.RowNumber()].Collapsed = value; } else { @@ -418,7 +424,7 @@ { if (IsReference) { - return Worksheet.Internals.RowsCollection[this.RowNumber()].OutlineLevel; + return (Worksheet as XLWorksheet).Internals.RowsCollection[this.RowNumber()].OutlineLevel; } else { @@ -432,12 +438,12 @@ if (IsReference) { - Worksheet.Internals.RowsCollection[this.RowNumber()].OutlineLevel = value; + (Worksheet as XLWorksheet).Internals.RowsCollection[this.RowNumber()].OutlineLevel = value; } else { - Worksheet.IncrementColumnOutline(value); - Worksheet.DecrementColumnOutline(outlineLevel); + (Worksheet as XLWorksheet).IncrementColumnOutline(value); + (Worksheet as XLWorksheet).DecrementColumnOutline(outlineLevel); outlineLevel = value; } } @@ -581,21 +587,15 @@ { return Range(1, start, 1, end).Row(1); } - - public new IXLRow Replace(String oldValue, String newValue) + public IXLRangeRows Rows(String rows) { - base.Replace(oldValue, newValue); - return this; - } - public new IXLRow Replace(String oldValue, String newValue, XLSearchContents searchContents) - { - base.Replace(oldValue, newValue, searchContents); - return this; - } - public new IXLRow Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions) - { - base.Replace(oldValue, newValue, searchContents, useRegularExpressions); - return this; + var retVal = new XLRangeRows(); + var rowPairs = rows.Split(','); + foreach (var pair in rowPairs) + { + this.AsRange().Rows(pair.Trim()).ForEach(r => retVal.Add(r)); + } + return retVal; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRowCollection.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRowCollection.cs index 354d1cc..37d6e32 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRowCollection.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRowCollection.cs @@ -22,7 +22,7 @@ Int32 newRow = ro + rowsToShift; if (newRow <= XLWorksheet.MaxNumberOfRows) { - dictionary.Add(newRow, new XLRow(rowToMove, rowToMove.Worksheet)); + dictionary.Add(newRow, new XLRow(rowToMove)); } dictionary.Remove(ro); } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs index 55614f4..6e9458d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs @@ -7,13 +7,11 @@ { internal class XLRows : IXLRows, IXLStylized { - private Boolean entireWorksheet; private XLWorksheet worksheet; - public XLRows(XLWorksheet worksheet, Boolean entireWorksheet = false) + public XLRows(XLWorksheet worksheet) { this.worksheet = worksheet; - this.entireWorksheet = entireWorksheet; - style = new XLStyle(this, worksheet.Style); + style = new XLStyle(this, XLWorkbook.DefaultStyle); } List rows = new List(); @@ -43,28 +41,15 @@ { style = new XLStyle(this, value); - if (entireWorksheet) + if (worksheet != null) { worksheet.Style = value; } else { - var maxColumn = 0; - if (worksheet.Internals.ColumnsCollection.Count > 0) - maxColumn = worksheet.Internals.ColumnsCollection.Keys.Max(); - foreach (var row in rows) { row.Style = value; - foreach (var c in row.Worksheet.Internals.CellsCollection.Values.Where(c => c.Address.RowNumber == row.RangeAddress.FirstAddress.RowNumber)) - { - c.Style = value; - } - - for (var co = 1; co <= maxColumn; co++) - { - worksheet.Cell(row.RowNumber(), co).Style = value; - } } } @@ -77,28 +62,16 @@ { UpdatingStyle = true; yield return style; - if (entireWorksheet) + if (worksheet != null) { yield return worksheet.Style; } else { - var maxColumn = 0; - if (worksheet.Internals.ColumnsCollection.Count > 0) - maxColumn = worksheet.Internals.ColumnsCollection.Keys.Max(); - foreach (var row in rows) { - yield return row.Style; - foreach (var c in row.Worksheet.Internals.CellsCollection.Values.Where(c => c.Address.RowNumber == row.RangeAddress.FirstAddress.RowNumber)) - { - yield return c.Style; - } - - for (var co = 1; co <= maxColumn; co++) - { - yield return worksheet.Cell(row.RowNumber(), co).Style; - } + foreach (var s in row.Styles) + yield return s; } } UpdatingStyle = false; @@ -120,7 +93,7 @@ set { rows.ForEach(c => c.Height = value); - if (entireWorksheet) + if (worksheet != null) { worksheet.RowHeight = value; worksheet.Internals.RowsCollection.ForEach(r => r.Value.Height = value); @@ -130,19 +103,27 @@ public void Delete() { - if (entireWorksheet) + if (worksheet != null) { worksheet.Internals.RowsCollection.Clear(); worksheet.Internals.CellsCollection.Clear(); } else { - var toDelete = new List(); + var toDelete = new Dictionary>(); foreach (var r in rows) - toDelete.Add(r.RowNumber()); + { + if (!toDelete.ContainsKey(r.Worksheet)) + toDelete.Add(r.Worksheet, new List()); - foreach (var r in toDelete.OrderByDescending(r => r)) - worksheet.Row(r).Delete(); + toDelete[r.Worksheet].Add(r.RowNumber()); + } + + foreach (var kp in toDelete) + { + foreach (var r in kp.Value.OrderByDescending(r => r)) + kp.Key.Row(r).Delete(); + } } } @@ -210,7 +191,7 @@ public IXLCells Cells() { - var cells = new XLCells(worksheet, false, false, false); + var cells = new XLCells(false, false, false); foreach (var container in rows) { cells.Add(container.RangeAddress); @@ -220,7 +201,7 @@ public IXLCells CellsUsed() { - var cells = new XLCells(worksheet, false, true, false); + var cells = new XLCells(false, true, false); foreach (var container in rows) { cells.Add(container.RangeAddress); @@ -230,7 +211,7 @@ public IXLCells CellsUsed(Boolean includeStyles) { - var cells = new XLCells(worksheet, false, true, includeStyles); + var cells = new XLCells(false, true, includeStyles); foreach (var container in rows) { cells.Add(container.RangeAddress); @@ -242,26 +223,12 @@ { get { - var retVal = new XLRanges(worksheet.Internals.Workbook, this.Style); + var retVal = new XLRanges(); this.ForEach(c => retVal.Add(c.AsRange())); return retVal; } } - public IXLRows Replace(String oldValue, String newValue) - { - rows.ForEach(r => r.Replace(oldValue, newValue)); - return this; - } - public IXLRows Replace(String oldValue, String newValue, XLSearchContents searchContents) - { - rows.ForEach(r => r.Replace(oldValue, newValue, searchContents)); - return this; - } - public IXLRows Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions) - { - rows.ForEach(r => r.Replace(oldValue, newValue, searchContents, useRegularExpressions)); - return this; - } + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs index 8b8568f..3275fe5 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs @@ -305,13 +305,6 @@ IXLSortElements SortRows { get; } IXLSortElements SortColumns { get; } - IXLTableRows FindRows(String search); - IXLTableRows FindRows(String search, XLSearchContents searchContents); - IXLTableRows FindRows(String search, XLSearchContents searchContents, Boolean useRegularExpressions); - IXLTableRows FindRows(String search, XLSearchContents searchContents, Boolean matchCase, Boolean entireCell); - - IXLTable Replace(String oldValue, String newValue); - IXLTable Replace(String oldValue, String newValue, XLSearchContents searchContents); - IXLTable Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions); + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRow.cs index 6a71358..1390aa1 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRow.cs @@ -15,8 +15,6 @@ new IXLTableRow Sort(XLSortOrder sortOrder); new IXLTableRow Sort(XLSortOrder sortOrder, Boolean matchCase); - new IXLTableRow Replace(String oldValue, String newValue); - new IXLTableRow Replace(String oldValue, String newValue, XLSearchContents searchContents); - new IXLTableRow Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions); + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRows.cs index a2ad34f..ea423e0 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRows.cs @@ -36,8 +36,6 @@ IXLStyle Style { get; set; } - IXLTableRows Replace(String oldValue, String newValue); - IXLTableRows Replace(String oldValue, String newValue, XLSearchContents searchContents); - IXLTableRows Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions); + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTables.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTables.cs index 7e00f81..5f88def 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTables.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTables.cs @@ -10,5 +10,6 @@ void Add(IXLTable table); //IXLTable Table(Int32 index); IXLTable Table(String name); + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs index 5369185..8d9950d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs @@ -25,7 +25,7 @@ } set { - if (Worksheet.Tables.Where(t => t.Name == value).Any()) + if (Worksheet.Tables.Any(t => t.Name == value)) throw new ArgumentException(String.Format("This worksheet already contains a table named '{0}'", value)); name = value; @@ -66,7 +66,7 @@ while (true) { String tableName = String.Format("Table{0}", id); - if (!Worksheet.Tables.Where(t => t.Name == tableName).Any()) + if (!Worksheet.Tables.Any(t => t.Name == tableName)) { Name = tableName; AddToTables(range, addToTables); @@ -171,7 +171,7 @@ public new IXLTableRows Rows() { - var retVal = new XLTableRows(Worksheet); + var retVal = new XLTableRows(Worksheet as XLWorksheet); foreach (var r in Enumerable.Range(1, DataRange.RowCount())) { retVal.Add(this.Row(r)); @@ -181,7 +181,7 @@ public new IXLTableRows Rows(int firstRow, int lastRow) { - var retVal = new XLTableRows(Worksheet); + var retVal = new XLTableRows(Worksheet as XLWorksheet); for (var ro = firstRow; ro <= lastRow; ro++) { @@ -192,7 +192,7 @@ public new IXLTableRows Rows(string rows) { - var retVal = new XLTableRows(Worksheet); + var retVal = new XLTableRows(Worksheet as XLWorksheet); var rowPairs = rows.Split(','); foreach (var pair in rowPairs) { @@ -350,37 +350,6 @@ return DataRange.Sort(toSortBy.ToString(0, toSortBy.Length - 1)); } - public new IXLTableRows FindRows(String search) - { - return FindRows(search, XLSearchContents.ValuesAndFormulas); - } - public new IXLTableRows FindRows(String search, XLSearchContents searchContents) - { - return FindRows(search, searchContents, false, false); - } - public new IXLTableRows FindRows(String search, XLSearchContents searchContents, Boolean useRegularExpressions) - { - throw new NotImplementedException(); - } - public new IXLTableRows FindRows(String search, XLSearchContents searchContents, Boolean matchCase, Boolean entireCell) - { - throw new NotImplementedException(); - } - public new IXLTable Replace(String oldValue, String newValue) - { - base.Replace(oldValue, newValue); - return this; - } - public new IXLTable Replace(String oldValue, String newValue, XLSearchContents searchContents) - { - base.Replace(oldValue, newValue, searchContents); - return this; - } - public new IXLTable Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions) - { - base.Replace(oldValue, newValue, searchContents, useRegularExpressions); - return this; - } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRow.cs index 643ae8d..7dfd0a6 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRow.cs @@ -46,20 +46,5 @@ return this; } - public new IXLTableRow Replace(String oldValue, String newValue) - { - base.Replace(oldValue, newValue); - return this; - } - public new IXLTableRow Replace(String oldValue, String newValue, XLSearchContents searchContents) - { - base.Replace(oldValue, newValue, searchContents); - return this; - } - public new IXLTableRow Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions) - { - base.Replace(oldValue, newValue, searchContents, useRegularExpressions); - return this; - } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs index 2df9cee..5a81e8e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs @@ -63,7 +63,7 @@ foreach (var rng in ranges) { yield return rng.Style; - foreach (var r in rng.Worksheet.Internals.CellsCollection.Values.Where(c => + foreach (var r in (rng.Worksheet as XLWorksheet).Internals.CellsCollection.Values.Where(c => c.Address.RowNumber >= rng.RangeAddress.FirstAddress.RowNumber && c.Address.RowNumber <= rng.RangeAddress.LastAddress.RowNumber && c.Address.ColumnNumber >= rng.RangeAddress.FirstAddress.ColumnNumber @@ -89,7 +89,7 @@ public IXLCells Cells() { - var cells = new XLCells(worksheet, false, false, false); + var cells = new XLCells(false, false, false); foreach (var container in ranges) { cells.Add(container.RangeAddress); @@ -99,7 +99,7 @@ public IXLCells CellsUsed() { - var cells = new XLCells(worksheet, false, true, false); + var cells = new XLCells(false, true, false); foreach (var container in ranges) { cells.Add(container.RangeAddress); @@ -109,7 +109,7 @@ public IXLCells CellsUsed(Boolean includeStyles) { - var cells = new XLCells(worksheet, false, false, includeStyles); + var cells = new XLCells(false, false, includeStyles); foreach (var container in ranges) { cells.Add(container.RangeAddress); @@ -121,26 +121,11 @@ { get { - var retVal = new XLRanges(worksheet.Internals.Workbook, this.Style); + var retVal = new XLRanges(); this.ForEach(c => retVal.Add(c.AsRange())); return retVal; } } - - public IXLTableRows Replace(String oldValue, String newValue) - { - ranges.ForEach(r => r.Replace(oldValue, newValue)); - return this; - } - public IXLTableRows Replace(String oldValue, String newValue, XLSearchContents searchContents) - { - ranges.ForEach(r => r.Replace(oldValue, newValue, searchContents)); - return this; - } - public IXLTableRows Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions) - { - ranges.ForEach(r => r.Replace(oldValue, newValue, searchContents, useRegularExpressions)); - return this; - } + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTables.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTables.cs index 78a7f79..5a00793 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTables.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTables.cs @@ -32,5 +32,7 @@ { return tables[name]; } + + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs index 0d5975f..addcc81 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs @@ -255,11 +255,13 @@ return GetColumnNumberFromLetter(cellAddressString.Substring(startPos, rowPos)); } } + + #endregion #region Properties - public IXLWorksheet Worksheet { get; private set; } + public IXLWorksheet Worksheet { get; internal set; } private Boolean fixedRow; public Boolean FixedRow @@ -322,13 +324,6 @@ #region Overrides public override string ToString() { - //var sb = new StringBuilder(); - //if (FixedColumn) sb.Append("$"); - //sb.Append(ColumnLetter); - //if (FixedRow) sb.Append("$"); - //sb.Append(rowNumber.ToString()); - //return sb.ToString(); - String retVal = ColumnLetter; if (fixedColumn) retVal = "$" + retVal; @@ -337,6 +332,19 @@ retVal += rowNumber.ToStringLookup(); return retVal; } + + public String ToString(XLReferenceStyle referenceStyle) + { + if (referenceStyle == XLReferenceStyle.A1) + return ColumnLetter + rowNumber.ToStringLookup(); + else if (referenceStyle == XLReferenceStyle.R1C1) + return rowNumber.ToStringLookup() + "," + ColumnLetter; + else + if ((Worksheet as XLWorksheet).Internals.Workbook.ReferenceStyle == XLReferenceStyle.R1C1) + return rowNumber.ToStringLookup() + "," + ColumnLetter; + else + return ColumnLetter + rowNumber.ToStringLookup(); + } #endregion #region Methods @@ -361,35 +369,36 @@ public static XLAddress operator +(XLAddress xlCellAddressLeft, XLAddress xlCellAddressRight) { - return new XLAddress(Worksheet, xlCellAddressLeft.RowNumber + xlCellAddressRight.RowNumber, xlCellAddressLeft.ColumnNumber + xlCellAddressRight.ColumnNumber, + return new XLAddress(xlCellAddressLeft.Worksheet, xlCellAddressLeft.RowNumber + xlCellAddressRight.RowNumber, xlCellAddressLeft.ColumnNumber + xlCellAddressRight.ColumnNumber, xlCellAddressLeft.fixedRow, xlCellAddressLeft.fixedColumn); } public static XLAddress operator -(XLAddress xlCellAddressLeft, XLAddress xlCellAddressRight) { - return new XLAddress(Worksheet, xlCellAddressLeft.RowNumber - xlCellAddressRight.RowNumber, xlCellAddressLeft.ColumnNumber - xlCellAddressRight.ColumnNumber, + return new XLAddress(xlCellAddressLeft.Worksheet, xlCellAddressLeft.RowNumber - xlCellAddressRight.RowNumber, xlCellAddressLeft.ColumnNumber - xlCellAddressRight.ColumnNumber, xlCellAddressLeft.fixedRow, xlCellAddressLeft.fixedColumn); } public static XLAddress operator +(XLAddress xlCellAddressLeft, Int32 right) { - return new XLAddress(Worksheet, xlCellAddressLeft.RowNumber + right, xlCellAddressLeft.ColumnNumber + right, + return new XLAddress(xlCellAddressLeft.Worksheet, xlCellAddressLeft.RowNumber + right, xlCellAddressLeft.ColumnNumber + right, xlCellAddressLeft.fixedRow, xlCellAddressLeft.fixedColumn); } public static XLAddress operator -(XLAddress xlCellAddressLeft, Int32 right) { - return new XLAddress(Worksheet, xlCellAddressLeft.RowNumber - right, xlCellAddressLeft.ColumnNumber - right, + return new XLAddress(xlCellAddressLeft.Worksheet, xlCellAddressLeft.RowNumber - right, xlCellAddressLeft.ColumnNumber - right, xlCellAddressLeft.fixedRow, xlCellAddressLeft.fixedColumn); } public static Boolean operator ==(XLAddress xlCellAddressLeft, XLAddress xlCellAddressRight) { - if (xlCellAddressLeft.rowNumber == xlCellAddressRight.rowNumber) + if (//xlCellAddressLeft.Worksheet == xlCellAddressRight.Worksheet && + xlCellAddressLeft.rowNumber == xlCellAddressRight.rowNumber) if (xlCellAddressRight.columnNumber > 0) - return xlCellAddressLeft.columnNumber == xlCellAddressRight.columnNumber; + return xlCellAddressLeft.ColumnNumber == xlCellAddressRight.columnNumber; else - return xlCellAddressLeft.columnLetter == xlCellAddressRight.columnLetter; + return xlCellAddressLeft.ColumnLetter == xlCellAddressRight.columnLetter; else return false; } @@ -401,16 +410,14 @@ public static Boolean operator >(XLAddress xlCellAddressLeft, XLAddress xlCellAddressRight) { - return !(xlCellAddressLeft == xlCellAddressRight) - && (xlCellAddressLeft.RowNumber >= xlCellAddressRight.RowNumber - && xlCellAddressLeft.ColumnNumber >= xlCellAddressRight.ColumnNumber); + return (xlCellAddressLeft.RowNumber > xlCellAddressRight.RowNumber + || xlCellAddressLeft.ColumnNumber > xlCellAddressRight.ColumnNumber); } public static Boolean operator <(XLAddress xlCellAddressLeft, XLAddress xlCellAddressRight) { - return !(xlCellAddressLeft == xlCellAddressRight) - && (xlCellAddressLeft.RowNumber <= xlCellAddressRight.RowNumber - && xlCellAddressLeft.ColumnNumber <= xlCellAddressRight.ColumnNumber); + return (xlCellAddressLeft.RowNumber < xlCellAddressRight.RowNumber + || xlCellAddressLeft.ColumnNumber < xlCellAddressRight.ColumnNumber); } public static Boolean operator >=(XLAddress xlCellAddressLeft, XLAddress xlCellAddressRight) @@ -451,7 +458,10 @@ public override Int32 GetHashCode() { - return rowNumber ^ ColumnNumber; + return + Worksheet.GetHashCode() + ^ rowNumber + ^ ColumnNumber; } #endregion @@ -460,7 +470,14 @@ public Boolean Equals(IXLAddress other) { - return this == (XLAddress)other; + var right = other as XLAddress; + if (this.rowNumber == right.rowNumber) + if (right.columnNumber > 0) + return this.ColumnNumber == right.columnNumber; + else + return this.ColumnLetter == right.columnLetter; + else + return false; } public override Boolean Equals(Object other) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs index f6838b1..ca492f9 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs @@ -34,6 +34,13 @@ CalculateMode = XLCalculateMode.Default; ReferenceStyle = XLReferenceStyle.Default; InitializeTheme(); + ShowFormulas = DefaultShowFormulas; + ShowGridLines = DefaultShowGridLines; + ShowOutlineSymbols = DefaultShowOutlineSymbols; + ShowRowColHeaders = DefaultShowRowColHeaders; + ShowRuler = DefaultShowRuler; + ShowWhiteSpace = DefaultShowWhiteSpace; + ShowZeros = DefaultShowZeros; } private void InitializeTheme() @@ -396,20 +403,61 @@ public IXLCustomProperties CustomProperties { get; private set; } - public XLWorkbook Replace(String oldValue, String newValue) + public IXLCells FindCells(Func predicate) { - Worksheets.Replace(oldValue, newValue); - return this; + var cells = new XLCells(false, false, false); + foreach (var ws in Worksheets) + { + foreach (var cell in ws.CellsUsed(true)) + { + if (predicate(cell)) + cells.Add(cell); + } + } + return cells; } - public XLWorkbook Replace(String oldValue, String newValue, XLSearchContents searchContents) + public IXLRows FindRows(Func predicate) { - Worksheets.Replace(oldValue, newValue, searchContents); - return this; + var rows = new XLRows(null); + foreach (var ws in Worksheets) + { + foreach (var row in ws.Rows()) + { + if (predicate(row)) + rows.Add(row as XLRow); + } + } + return rows; } - public XLWorkbook Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions) + public IXLColumns FindColumns(Func predicate) { - Worksheets.Replace(oldValue, newValue, searchContents, useRegularExpressions); - return this; + var columns = new XLColumns(null); + foreach (var ws in Worksheets) + { + foreach (var column in ws.Columns()) + { + if (predicate(column)) + columns.Add(column as XLColumn); + } + } + return columns; } + + public Boolean ShowFormulas { get; set; } + public Boolean ShowGridLines { get; set; } + public Boolean ShowOutlineSymbols { get; set; } + public Boolean ShowRowColHeaders { get; set; } + public Boolean ShowRuler { get; set; } + public Boolean ShowWhiteSpace { get; set; } + public Boolean ShowZeros { get; set; } + + public Boolean DefaultShowFormulas { get { return false; } } + public Boolean DefaultShowGridLines { get { return true; } } + public Boolean DefaultShowOutlineSymbols { get { return true; } } + public Boolean DefaultShowRowColHeaders { get { return true; } } + public Boolean DefaultShowRuler { get { return true; } } + public Boolean DefaultShowWhiteSpace { get { return true; } } + public Boolean DefaultShowZeros { get { return true; } } } } + diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index 2cfeb78..7148445 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -118,6 +118,8 @@ if (sheetFormatProperties.DefaultRowHeight != null) ws.RowHeight = sheetFormatProperties.DefaultRowHeight; + ws.RowHeightChanged = (sheetFormatProperties.CustomHeight != null && sheetFormatProperties.CustomHeight.Value); + if (sheetFormatProperties.DefaultColumnWidth != null) ws.ColumnWidth = sheetFormatProperties.DefaultColumnWidth; @@ -135,7 +137,7 @@ if (defaultColumns.Count() > 0) wsDefaultColumn = defaultColumns.Single(); - if (wsDefaultColumn != null && wsDefaultColumn.Width != null) ws.ColumnWidth = wsDefaultColumn.Width; + if (wsDefaultColumn != null && wsDefaultColumn.Width != null) ws.ColumnWidth = wsDefaultColumn.Width - COLUMN_WIDTH_OFFSET; Int32 styleIndexDefault = wsDefaultColumn != null && wsDefaultColumn.Style != null ? Int32.Parse(wsDefaultColumn.Style.InnerText) : -1; if (styleIndexDefault >= 0) @@ -150,7 +152,7 @@ { var xlColumns = (XLColumns)ws.Columns(col.Min, col.Max); if (col.Width != null) - xlColumns.Width = col.Width; + xlColumns.Width = col.Width - COLUMN_WIDTH_OFFSET; else xlColumns.Width = ws.ColumnWidth; @@ -219,9 +221,6 @@ Int32 styleIndex = dCell.StyleIndex != null ? Int32.Parse(dCell.StyleIndex.InnerText) : 0; var xlCell = (XLCell)ws.Cell(dCell.CellReference); - //if (dCell.CellReference.Value == "A9") - // dCell.CellReference = dCell.CellReference.Value; - if (styleIndex > 0) { ApplyStyle(xlCell, styleIndex, s, fills, borders, fonts, numberingFormats); @@ -311,7 +310,7 @@ var numberFormatId = ((CellFormat)((CellFormats)s.CellFormats).ElementAt(styleIndex)).NumberFormatId; Double val = Double.Parse(dCell.CellValue.Text, CultureInfo.InvariantCulture); xlCell.Value = val; - if (s.NumberingFormats != null && s.NumberingFormats.Where(nf => ((NumberingFormat)nf).NumberFormatId.Value == numberFormatId).Any()) + if (s.NumberingFormats != null && s.NumberingFormats.Any(nf => ((NumberingFormat)nf).NumberFormatId.Value == numberFormatId)) xlCell.Style.NumberFormat.Format = ((NumberingFormat)s.NumberingFormats.Where(nf => ((NumberingFormat)nf).NumberFormatId.Value == numberFormatId).Single()).FormatCode.Value; else @@ -723,6 +722,14 @@ var sheetView = (SheetView)worksheetPart.Worksheet.Descendants().FirstOrDefault(); if (sheetView != null) { + if (sheetView.ShowFormulas != null) ws.ShowFormulas = sheetView.ShowFormulas.Value; + if (sheetView.ShowGridLines != null) ws.ShowGridLines = sheetView.ShowGridLines.Value; + if (sheetView.ShowOutlineSymbols != null) ws.ShowOutlineSymbols = sheetView.ShowOutlineSymbols.Value; + if (sheetView.ShowRowColHeaders != null) ws.ShowRowColHeaders = sheetView.ShowRowColHeaders.Value; + if (sheetView.ShowRuler != null) ws.ShowRuler = sheetView.ShowRuler.Value; + if (sheetView.ShowWhiteSpace != null) ws.ShowWhiteSpace = sheetView.ShowWhiteSpace.Value; + if (sheetView.ShowZeros != null) ws.ShowZeros = sheetView.ShowZeros.Value; + var pane = (Pane)sheetView.Descendants().FirstOrDefault(); if (pane != null) { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index c8808ff..5a69318 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -20,6 +20,8 @@ { public partial class XLWorkbook { + private const Double COLUMN_WIDTH_OFFSET = 0.71; + private List> fillPatternValues = new List>(); private List> alignmentHorizontalValues = new List>(); private List> alignmentVerticalValues = new List>(); @@ -406,7 +408,7 @@ foreach (var worksheet in Worksheets.Cast().OrderBy(w=>w.Position)) { WorksheetPart worksheetPart; - if (workbookPart.Parts.Where(p => p.RelationshipId == worksheet.RelId).Any()) + if (workbookPart.Parts.Any(p => p.RelationshipId == worksheet.RelId)) { worksheetPart = (WorksheetPart)workbookPart.GetPartById(worksheet.RelId); var wsPartsToRemove = worksheetPart.TableDefinitionParts.ToList(); @@ -438,7 +440,7 @@ GenerateThemePartContent(themePart); } - if (CustomProperties.Count() > 0) + if (CustomProperties.Any()) { document.GetPartsOfType().ToList().ForEach(p => document.DeletePart(p)); CustomFilePropertiesPart customFilePropertiesPart = document.AddNewPart(relId.GetNext(RelType.Workbook)); @@ -451,7 +453,7 @@ private void GenerateTables(XLWorksheet worksheet, WorksheetPart worksheetPart) { worksheetPart.Worksheet.RemoveAllChildren(); - if (worksheet.Tables.Count() > 0) + if (worksheet.Tables.Any()) { foreach (var table in worksheet.Tables) { @@ -503,11 +505,13 @@ var modifiedWorksheets = Worksheets.Select(w => new { w.Name, Order = w.Position }); var modifiedNamedRanges = GetModifiedNamedRanges(); + var modifiedWorksheetsCount = modifiedWorksheets.Count(); + var modifiedNamedRangesCount = modifiedNamedRanges.Count(); - InsertOnVTVector(vTVector_One, "Worksheets", 0, modifiedWorksheets.Count().ToString()); - InsertOnVTVector(vTVector_One, "Named Ranges", 2, (modifiedNamedRanges.Count()).ToString()); + InsertOnVTVector(vTVector_One, "Worksheets", 0, modifiedWorksheetsCount.ToString()); + InsertOnVTVector(vTVector_One, "Named Ranges", 2, modifiedNamedRangesCount.ToString()); - vTVector_Two.Size = (UInt32)(modifiedNamedRanges.Count() + modifiedWorksheets.Count()); + vTVector_Two.Size = (UInt32)(modifiedNamedRangesCount + modifiedWorksheetsCount); foreach (var w in modifiedWorksheets.OrderBy(w=>w.Order)) { @@ -557,7 +561,7 @@ var m = from e1 in vTVector.Elements() where e1.Elements().Any(e2 => e2.Text == property) select e1; - if (m.Count() == 0) + if (!m.Any()) { if (vTVector.Size == null) vTVector.Size = new UInt32Value(0U); @@ -596,7 +600,7 @@ private List GetExistingNamedRanges(Vt.VTVector vTVector_Two) { - if (vTVector_Two.Count() > 0) + if (vTVector_Two.Any()) return vTVector_Two.Elements().Select(e => e.Text).ToList(); else return new List(); @@ -650,8 +654,8 @@ foreach (var sheet in workbook.Sheets.Elements()) { var sName = sheet.Name.Value; - //if (Worksheets.Where(w => w.Name.ToLower() == sName.ToLower()).Any()) - if (Worksheets.Where(w=>(w as XLWorksheet).SheetId == (Int32)sheet.SheetId.Value).Any()) + //if (Worksheets.Where(w => w.Name.ToLower() == sName.ToLower())) + if (Worksheets.Any(w => (w as XLWorksheet).SheetId == (Int32)sheet.SheetId.Value)) { var wks = (XLWorksheet)Worksheets.Where(w => (w as XLWorksheet).SheetId == (Int32)sheet.SheetId.Value).Single(); //wks.SheetId = (Int32)sheet.SheetId.Value; @@ -663,7 +667,7 @@ foreach (var xlSheet in Worksheets.Cast().Where(w => w.SheetId == 0).OrderBy(w => w.Position)) { String rId = relId.GetNext(RelType.Workbook); - while (Worksheets.Cast().Where(w=>w.SheetId == Int32.Parse(rId.Substring(3))).Any()) + while (Worksheets.Cast().Any(w => w.SheetId == Int32.Parse(rId.Substring(3)))) rId = relId.GetNext(RelType.Workbook); xlSheet.SheetId = Int32.Parse(rId.Substring(3)); @@ -726,14 +730,14 @@ sheetId++; } - if (worksheet.PageSetup.PrintAreas.Count() == 0) + if (!worksheet.PageSetup.PrintAreas.Any()) { var minCell = worksheet.FirstCellUsed(); var maxCell = worksheet.LastCellUsed(); if (minCell != null && maxCell != null) worksheet.PageSetup.PrintAreas.Add(minCell.Address.ToString(), maxCell.Address.ToString()); } - if (worksheet.PageSetup.PrintAreas.Count() > 0) + if (worksheet.PageSetup.PrintAreas.Any()) { DefinedName definedName = new DefinedName() { Name = "_xlnm.Print_Area", LocalSheetId = sheetId}; var definedNameText = String.Empty; @@ -899,7 +903,7 @@ workbookStylesPart.Stylesheet.CellStyles = new CellStyles(); UInt32 defaultFormatId; - if (workbookStylesPart.Stylesheet.CellStyles.Elements().Where(c => c.Name == "Normal").Any()) + if (workbookStylesPart.Stylesheet.CellStyles.Elements().Any(c => c.Name == "Normal")) defaultFormatId = workbookStylesPart.Stylesheet.CellStyles.Elements().Where(c => c.Name == "Normal").Single().FormatId.Value; else if (workbookStylesPart.Stylesheet.CellStyles.Elements().Any()) @@ -992,8 +996,8 @@ var allCellStyleFormats = ResolveCellStyleFormats(workbookStylesPart); ResolveRest(workbookStylesPart); - - if (!workbookStylesPart.Stylesheet.CellStyles.Elements().Where(c => c.Name == "Normal").Any()) + + if (!workbookStylesPart.Stylesheet.CellStyles.Elements().Any(c => c.Name == "Normal")) { //var defaultFormatId = sharedStyles.Values.Where(s => s.Style.Equals(DefaultStyle)).Single().StyleId; @@ -1353,11 +1357,11 @@ private void ResolveFillWithPattern(Fills fills, PatternValues patternValues) { - if (!fills.Elements().Where(f => + if (!fills.Elements().Any(f => f.PatternFill.PatternType == patternValues && f.PatternFill.ForegroundColor == null && f.PatternFill.BackgroundColor == null - ).Any()) + )) { Fill fill1 = new Fill(); PatternFill patternFill1 = new PatternFill() { PatternType = patternValues }; @@ -1611,22 +1615,22 @@ UInt32 maxRow = 0; String sheetDimensionReference = "A1"; - if (xlWorksheet.Internals.CellsCollection.Count > 0) + if ((xlWorksheet as XLWorksheet).Internals.CellsCollection.Count > 0) { - maxColumn = (UInt32)xlWorksheet.Internals.CellsCollection.Select(c => c.Key.ColumnNumber).Max(); - maxRow = (UInt32)xlWorksheet.Internals.CellsCollection.Select(c => c.Key.RowNumber).Max(); + maxColumn = (UInt32)(xlWorksheet as XLWorksheet).Internals.CellsCollection.Select(c => c.Key.ColumnNumber).Max(); + maxRow = (UInt32)(xlWorksheet as XLWorksheet).Internals.CellsCollection.Select(c => c.Key.RowNumber).Max(); sheetDimensionReference = "A1:" + XLAddress.GetColumnLetterFromNumber((Int32)maxColumn) + ((Int32)maxRow).ToStringLookup(); } - if (xlWorksheet.Internals.ColumnsCollection.Count > 0) + if ((xlWorksheet as XLWorksheet).Internals.ColumnsCollection.Count > 0) { - UInt32 maxColCollection = (UInt32)xlWorksheet.Internals.ColumnsCollection.Keys.Max(); + UInt32 maxColCollection = (UInt32)(xlWorksheet as XLWorksheet).Internals.ColumnsCollection.Keys.Max(); if (maxColCollection > maxColumn) maxColumn = maxColCollection; } - if (xlWorksheet.Internals.RowsCollection.Count > 0) + if ((xlWorksheet as XLWorksheet).Internals.RowsCollection.Count > 0) { - UInt32 maxRowCollection = (UInt32)xlWorksheet.Internals.RowsCollection.Keys.Max(); + UInt32 maxRowCollection = (UInt32)(xlWorksheet as XLWorksheet).Internals.RowsCollection.Keys.Max(); if (maxRowCollection > maxRow) maxRow = maxRowCollection; } @@ -1641,13 +1645,48 @@ cm.SetElement(XLWSContentManager.XLWSContents.SheetViews, worksheetPart.Worksheet.SheetViews); - var sheetView = worksheetPart.Worksheet.SheetViews.FirstOrDefault(); - if (worksheetPart.Worksheet.SheetViews.Count() == 0) + SheetView sheetView = (SheetView)worksheetPart.Worksheet.SheetViews.FirstOrDefault(); + if (sheetView == null) { sheetView = new SheetView() { WorkbookViewId = (UInt32Value)0U }; worksheetPart.Worksheet.SheetViews.Append(sheetView); } + if (xlWorksheet.ShowFormulas) + sheetView.ShowFormulas = true; + else + sheetView.ShowFormulas = null; + + if (xlWorksheet.ShowGridLines) + sheetView.ShowGridLines = null; + else + sheetView.ShowGridLines = false; + + if (xlWorksheet.ShowOutlineSymbols) + sheetView.ShowOutlineSymbols = null; + else + sheetView.ShowOutlineSymbols = false; + + if (xlWorksheet.ShowRowColHeaders) + sheetView.ShowRowColHeaders = null; + else + sheetView.ShowRowColHeaders = false; + + if (xlWorksheet.ShowRuler) + sheetView.ShowRuler = null; + else + sheetView.ShowRuler = false; + + if (xlWorksheet.ShowWhiteSpace) + sheetView.ShowWhiteSpace = null; + else + sheetView.ShowWhiteSpace = false; + + if (xlWorksheet.ShowZeros) + sheetView.ShowZeros = null; + else + sheetView.ShowZeros = false; + var pane = sheetView.Elements().FirstOrDefault(); if (pane == null) @@ -1721,7 +1760,7 @@ #region Columns Columns columns = null; - if (xlWorksheet.Internals.CellsCollection.Count == 0 && xlWorksheet.Internals.ColumnsCollection.Count == 0) + if ((xlWorksheet as XLWorksheet).Internals.CellsCollection.Count == 0 && (xlWorksheet as XLWorksheet).Internals.ColumnsCollection.Count == 0) { worksheetPart.Worksheet.RemoveAllChildren(); } @@ -1729,7 +1768,7 @@ { var worksheetColumnWidth = GetColumnWidth(xlWorksheet.ColumnWidth); - if (worksheetPart.Worksheet.Elements().Count() == 0) + if (!worksheetPart.Worksheet.Elements().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Columns); worksheetPart.Worksheet.InsertAfter(new Columns(), previousElement); @@ -1743,10 +1782,10 @@ Int32 minInColumnsCollection; Int32 maxInColumnsCollection; - if (xlWorksheet.Internals.ColumnsCollection.Count > 0) + if ((xlWorksheet as XLWorksheet).Internals.ColumnsCollection.Count > 0) { - minInColumnsCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Min(); - maxInColumnsCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Max(); + minInColumnsCollection = (xlWorksheet as XLWorksheet).Internals.ColumnsCollection.Keys.Min(); + maxInColumnsCollection = (xlWorksheet as XLWorksheet).Internals.ColumnsCollection.Keys.Max(); } else { @@ -1782,13 +1821,13 @@ Boolean isHidden = false; Boolean collapsed = false; Int32 outlineLevel = 0; - if (xlWorksheet.Internals.ColumnsCollection.ContainsKey(co)) + if ((xlWorksheet as XLWorksheet).Internals.ColumnsCollection.ContainsKey(co)) { - styleId = sharedStyles[xlWorksheet.Internals.ColumnsCollection[co].Style].StyleId; - columnWidth = GetColumnWidth(xlWorksheet.Internals.ColumnsCollection[co].Width); - isHidden = xlWorksheet.Internals.ColumnsCollection[co].IsHidden; - collapsed = xlWorksheet.Internals.ColumnsCollection[co].Collapsed; - outlineLevel = xlWorksheet.Internals.ColumnsCollection[co].OutlineLevel; + styleId = sharedStyles[(xlWorksheet as XLWorksheet).Internals.ColumnsCollection[co].Style].StyleId; + columnWidth = GetColumnWidth((xlWorksheet as XLWorksheet).Internals.ColumnsCollection[co].Width); + isHidden = (xlWorksheet as XLWorksheet).Internals.ColumnsCollection[co].IsHidden; + collapsed = (xlWorksheet as XLWorksheet).Internals.ColumnsCollection[co].Collapsed; + outlineLevel = (xlWorksheet as XLWorksheet).Internals.ColumnsCollection[co].OutlineLevel; } else { @@ -1839,7 +1878,7 @@ #region SheetData SheetData sheetData; - if (worksheetPart.Worksheet.Elements().Count() == 0) + if (!worksheetPart.Worksheet.Elements().Any()) { OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.SheetData); worksheetPart.Worksheet.InsertAfter(new SheetData(), previousElement); @@ -1849,7 +1888,7 @@ cm.SetElement(XLWSContentManager.XLWSContents.SheetData, sheetData); var cellsByRow = new Dictionary>(); - foreach (var c in xlWorksheet.Internals.CellsCollection.Values) + foreach (var c in (xlWorksheet as XLWorksheet).Internals.CellsCollection.Values) { Int32 rowNum = c.Address.RowNumber; if (!cellsByRow.ContainsKey(rowNum)) @@ -1858,7 +1897,7 @@ cellsByRow[rowNum].Add(c); } - var distinctRows = cellsByRow.Keys.Union(xlWorksheet.Internals.RowsCollection.Keys); + var distinctRows = cellsByRow.Keys.Union((xlWorksheet as XLWorksheet).Internals.RowsCollection.Keys); Boolean noRows = (sheetData.Elements().FirstOrDefault() == null); var sheetDataRows = sheetData.Elements().ToDictionary(r => (Int32)r.RowIndex.Value, r => r); foreach (var distinctRow in distinctRows.OrderBy(r => r)) @@ -1878,7 +1917,7 @@ } else { - if (sheetDataRows.Where(r => r.Key > row.RowIndex.Value).Any()) + if (sheetDataRows.Any(r => r.Key > row.RowIndex.Value)) { var minRow = sheetDataRows.Where(r => r.Key > (Int32)row.RowIndex.Value).Min(r=>r.Key); Row rowBeforeInsert = sheetDataRows[minRow]; @@ -1899,9 +1938,9 @@ row.Hidden = null; row.StyleIndex = null; row.CustomFormat = null; - if (xlWorksheet.Internals.RowsCollection.ContainsKey(distinctRow)) + if ((xlWorksheet as XLWorksheet).Internals.RowsCollection.ContainsKey(distinctRow)) { - var thisRow = xlWorksheet.Internals.RowsCollection[distinctRow]; + var thisRow = (xlWorksheet as XLWorksheet).Internals.RowsCollection[distinctRow]; if (thisRow.Height != xlWorksheet.RowHeight) { row.Height = thisRow.Height; @@ -1927,7 +1966,7 @@ foreach (var cell in row.Elements()) { var cellReference = cell.CellReference; - if (xlWorksheet.Internals.CellsCollection.Deleted.ContainsKey(new XLAddress(cellReference))) + if ((xlWorksheet as XLWorksheet).Internals.CellsCollection.Deleted.ContainsKey(new XLAddress(xlWorksheet, cellReference))) cellsToRemove.Add(cell); } cellsToRemove.ForEach(cell => row.RemoveChild(cell)); @@ -1952,7 +1991,7 @@ { //isNewCell = true; cell = new Cell() { CellReference = cellReference }; - if (row.Elements().Count() == 0) + if (!row.Elements().Any()) { row.Append(cell); } @@ -2050,7 +2089,7 @@ SheetProtection sheetProtection = null; if (xlWorksheet.Protection.Protected) { - if (worksheetPart.Worksheet.Elements().Count() == 0) + if (!worksheetPart.Worksheet.Elements().Any()) { OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.SheetProtection); worksheetPart.Worksheet.InsertAfter(new SheetProtection(), previousElement); @@ -2087,7 +2126,7 @@ #region AutoFilter if (xlWorksheet.AutoFilterRange != null) { - if (worksheetPart.Worksheet.Elements().Count() == 0) + if (!worksheetPart.Worksheet.Elements().Any()) { OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.AutoFilter); worksheetPart.Worksheet.InsertAfter(new AutoFilter(), previousElement); @@ -2107,9 +2146,9 @@ #region MergeCells MergeCells mergeCells = null; - if (xlWorksheet.Internals.MergedRanges.Count() > 0) + if ((xlWorksheet as XLWorksheet).Internals.MergedRanges.Any()) { - if (worksheetPart.Worksheet.Elements().Count() == 0) + if (!worksheetPart.Worksheet.Elements().Any()) { OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.MergeCells); worksheetPart.Worksheet.InsertAfter(new MergeCells(), previousElement); @@ -2119,7 +2158,7 @@ cm.SetElement(XLWSContentManager.XLWSContents.MergeCells, mergeCells); mergeCells.RemoveAllChildren(); - foreach (var merged in xlWorksheet.Internals.MergedRanges.Select(m => m.RangeAddress.FirstAddress.ToString() + ":" + m.RangeAddress.LastAddress.ToString())) + foreach (var merged in (xlWorksheet as XLWorksheet).Internals.MergedRanges.Select(m => m.RangeAddress.FirstAddress.ToString() + ":" + m.RangeAddress.LastAddress.ToString())) { MergeCell mergeCell = new MergeCell() { Reference = merged }; mergeCells.Append(mergeCell); @@ -2137,7 +2176,7 @@ #region DataValidations DataValidations dataValidations = null; - if (xlWorksheet.DataValidations.Count() == 0) + if (!xlWorksheet.DataValidations.Any()) { worksheetPart.Worksheet.RemoveAllChildren(); cm.SetElement(XLWSContentManager.XLWSContents.DataValidations, null); @@ -2145,7 +2184,7 @@ else { worksheetPart.Worksheet.Elements().FirstOrDefault(); - if (worksheetPart.Worksheet.Elements().Count() == 0) + if (!worksheetPart.Worksheet.Elements().Any()) { OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.DataValidations); worksheetPart.Worksheet.InsertAfter(new DataValidations(), previousElement); @@ -2195,7 +2234,7 @@ Hyperlinks hyperlinks = null; var relToRemove = worksheetPart.HyperlinkRelationships.ToList(); relToRemove.ForEach(h => worksheetPart.DeleteReferenceRelationship(h)); - if (xlWorksheet.Hyperlinks.Count() == 0) + if (!xlWorksheet.Hyperlinks.Any()) { worksheetPart.Worksheet.RemoveAllChildren(); cm.SetElement(XLWSContentManager.XLWSContents.Hyperlinks, null); @@ -2203,7 +2242,7 @@ else { worksheetPart.Worksheet.Elements().FirstOrDefault(); - if (worksheetPart.Worksheet.Elements().Count() == 0) + if (!worksheetPart.Worksheet.Elements().Any()) { OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Hyperlinks); worksheetPart.Worksheet.InsertAfter(new Hyperlinks(), previousElement); @@ -2239,7 +2278,7 @@ #region PrintOptions PrintOptions printOptions = null; - if (worksheetPart.Worksheet.Elements().Count() == 0) + if (!worksheetPart.Worksheet.Elements().Any()) { OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PrintOptions); worksheetPart.Worksheet.InsertAfter(new PrintOptions(), previousElement); @@ -2255,7 +2294,7 @@ #endregion #region PageMargins - if (worksheetPart.Worksheet.Elements().Count() == 0) + if (!worksheetPart.Worksheet.Elements().Any()) { OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PageMargins); worksheetPart.Worksheet.InsertAfter(new PageMargins(), previousElement); @@ -2272,7 +2311,7 @@ #endregion #region PageSetup - if (worksheetPart.Worksheet.Elements().Count() == 0) + if (!worksheetPart.Worksheet.Elements().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PageSetup); worksheetPart.Worksheet.InsertAfter(new PageSetup(), previousElement); @@ -2333,7 +2372,7 @@ #endregion #region HeaderFooter - if (worksheetPart.Worksheet.Elements().Count() == 0) + if (!worksheetPart.Worksheet.Elements().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.HeaderFooter); worksheetPart.Worksheet.InsertAfter(new HeaderFooter(), previousElement); @@ -2368,7 +2407,7 @@ #endregion #region RowBreaks - if (worksheetPart.Worksheet.Elements().Count() == 0) + if (!worksheetPart.Worksheet.Elements().Any()) { OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.RowBreaks); worksheetPart.Worksheet.InsertAfter(new RowBreaks(), previousElement); @@ -2397,7 +2436,7 @@ #region ColumnBreaks - if (worksheetPart.Worksheet.Elements().Count() == 0) + if (!worksheetPart.Worksheet.Elements().Any()) { OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.ColumnBreaks); worksheetPart.Worksheet.InsertAfter(new ColumnBreaks(), previousElement); @@ -2489,7 +2528,7 @@ private Double GetColumnWidth(Double columnWidth) { if (columnWidth > 0) - return columnWidth + 0.71; + return columnWidth + COLUMN_WIDTH_OFFSET; else return columnWidth; } @@ -2648,7 +2687,7 @@ m.ToList().ForEach(cc=>calculationChain.RemoveChild(cc)); } - if (calculationChain.Count() == 0) + if (!calculationChain.Any()) { workbookPart.DeletePart(workbookPart.CalculationChainPart); } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index e09e01c..0793066 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -2,8 +2,6 @@ using System.Collections.Generic; using System.Linq; using System.Text; -using System.Text.RegularExpressions; - namespace ClosedXML.Excel { @@ -22,21 +20,24 @@ #endregion - private XLWorkbook workbook; + + public XLWorkbook Workbook { get; private set; } public XLWorksheet(String sheetName, XLWorkbook workbook) : base((IXLRangeAddress)new XLRangeAddress(new XLAddress(null, 1, 1, false, false), new XLAddress(null, MaxNumberOfRows, MaxNumberOfColumns, false, false))) { - RangeAddress.FirstAddress.Worksheet - Worksheet = this; + (RangeAddress as XLRangeAddress).Worksheet = this; + (RangeAddress.FirstAddress as XLAddress).Worksheet = this; + (RangeAddress.LastAddress as XLAddress).Worksheet = this; + NamedRanges = new XLNamedRanges(workbook); SheetView = new XLSheetView(); Tables = new XLTables(); Hyperlinks = new XLHyperlinks(); DataValidations = new XLDataValidations(); Protection = new XLSheetProtection(); - this.workbook = workbook; + this.Workbook = workbook; style = new XLStyle(this, workbook.Style); - Internals = new XLWorksheetInternals(new XLCellCollection(), new XLColumnsCollection(), new XLRowsCollection(), new XLRanges(workbook, workbook.Style) , workbook); + Internals = new XLWorksheetInternals(new XLCellCollection(), new XLColumnsCollection(), new XLRowsCollection(), new XLRanges() , workbook); PageSetup = new XLPageSetup(workbook.PageOptions, this); Outline = new XLOutline(workbook.Outline); ColumnWidth = workbook.ColumnWidth; @@ -46,11 +47,18 @@ RangeShiftedRows += new RangeShiftedRowsDelegate(XLWorksheet_RangeShiftedRows); RangeShiftedColumns += new RangeShiftedColumnsDelegate(XLWorksheet_RangeShiftedColumns); Charts = new XLCharts(); + ShowFormulas = workbook.ShowFormulas; + ShowGridLines = workbook.ShowGridLines; + ShowOutlineSymbols = workbook.ShowOutlineSymbols; + ShowRowColHeaders = workbook.ShowRowColHeaders; + ShowRuler = workbook.ShowRuler; + ShowWhiteSpace = workbook.ShowWhiteSpace; + ShowZeros = workbook.ShowZeros; } void XLWorksheet_RangeShiftedColumns(XLRange range, int columnsShifted) { - var newMerge = new XLRanges(workbook, workbook.Style); + var newMerge = new XLRanges(); foreach (var rngMerged in Internals.MergedRanges) { if (range.RangeAddress.FirstAddress.ColumnNumber <= rngMerged.RangeAddress.FirstAddress.ColumnNumber @@ -76,7 +84,7 @@ void XLWorksheet_RangeShiftedRows(XLRange range, int rowsShifted) { - var newMerge = new XLRanges(workbook, workbook.Style); + var newMerge = new XLRanges(); foreach (var rngMerged in Internals.MergedRanges) { if (range.RangeAddress.FirstAddress.RowNumber <= rngMerged.RangeAddress.FirstAddress.RowNumber @@ -179,8 +187,8 @@ } set { + (Workbook.Worksheets as XLWorksheets).Rename(name, value); name = value; - (workbook.Worksheets as XLWorksheets).Rename(name, value); } } @@ -196,14 +204,14 @@ } set { - if (value > workbook.Worksheets.Count() + 1) + if (value > Workbook.Worksheets.Count() + 1) throw new IndexOutOfRangeException("Index must be equal or less than the number of worksheets + 1."); if (value < position) - workbook.Worksheets.Where(w => ((XLWorksheet)w).Position >= value && ((XLWorksheet)w).Position < position).ForEach(w => ((XLWorksheet)w).position += 1); + Workbook.Worksheets.Where(w => ((XLWorksheet)w).Position >= value && ((XLWorksheet)w).Position < position).ForEach(w => ((XLWorksheet)w).position += 1); if (value > position) - workbook.Worksheets.Where(w => ((XLWorksheet)w).Position <= value && ((XLWorksheet)w).Position > position).ForEach(w => ((XLWorksheet)w).position -= 1); + Workbook.Worksheets.Where(w => ((XLWorksheet)w).Position <= value && ((XLWorksheet)w).Position > position).ForEach(w => ((XLWorksheet)w).position -= 1); position = value; } @@ -280,7 +288,7 @@ public IXLColumns Columns() { - var retVal = new XLColumns(this, true); + var retVal = new XLColumns(this); var columnList = new List(); if (this.Internals.CellsCollection.Count > 0) @@ -298,7 +306,7 @@ } public IXLColumns Columns(String columns) { - var retVal = new XLColumns(this); + var retVal = new XLColumns(null); var columnPairs = columns.Split(','); foreach (var pair in columnPairs) { @@ -340,7 +348,7 @@ } public IXLColumns Columns( Int32 firstColumn, Int32 lastColumn) { - var retVal = new XLColumns(this); + var retVal = new XLColumns(null); for (var co = firstColumn; co <= lastColumn; co++) { @@ -351,7 +359,7 @@ public IXLRows Rows() { - var retVal = new XLRows(this, true); + var retVal = new XLRows(this); var rowList = new List(); if (this.Internals.CellsCollection.Count > 0) @@ -367,9 +375,9 @@ return retVal; } - public IXLRows Rows( String rows) + public IXLRows Rows(String rows) { - var retVal = new XLRows(this); + var retVal = new XLRows(null); var rowPairs = rows.Split(','); foreach (var pair in rowPairs) { @@ -399,7 +407,7 @@ } public IXLRows Rows( Int32 firstRow, Int32 lastRow) { - var retVal = new XLRows(this); + var retVal = new XLRows(null); for (var ro = firstRow; ro <= lastRow; ro++) { @@ -435,7 +443,7 @@ var usedColumns = from c in this.Internals.ColumnsCollection join dc in distinctColumns on c.Key equals dc - where !this.Internals.CellsCollection.ContainsKey(new XLAddress(row, c.Key, false, false)) + where !this.Internals.CellsCollection.ContainsKey(new XLAddress(Worksheet, row, c.Key, false, false)) select c.Key; usedColumns.ForEach(c => Cell(row, c)); @@ -530,7 +538,7 @@ public void Delete() { - workbook.Worksheets.Delete(Name); + Workbook.Worksheets.Delete(Name); } public new void Clear() { @@ -553,12 +561,12 @@ public IXLWorksheet CopyTo(String newSheetName) { - return CopyTo(this.workbook, newSheetName, workbook.Worksheets.Count() + 1); + return CopyTo(this.Workbook, newSheetName, Workbook.Worksheets.Count() + 1); } public IXLWorksheet CopyTo(String newSheetName, Int32 position) { - return CopyTo(this.workbook, newSheetName, position); + return CopyTo(this.Workbook, newSheetName, position); } public IXLWorksheet CopyTo(XLWorkbook workbook, String newSheetName) @@ -570,10 +578,10 @@ { var ws = (XLWorksheet)workbook.Worksheets.Add(newSheetName, position); - this.Internals.CellsCollection.ForEach(kp => (ws.Cell(kp.Value.Address) as XLCell).CopyFrom(kp.Value)); + this.Internals.CellsCollection.ForEach(kp => (ws.Cell(kp.Value.Address.RowNumber, kp.Value.Address.ColumnNumber) as XLCell).CopyFrom(kp.Value)); this.DataValidations.ForEach(dv => ws.DataValidations.Add(new XLDataValidation(dv, ws))); - this.Internals.ColumnsCollection.ForEach(kp => ws.Internals.ColumnsCollection.Add(kp.Key, new XLColumn(kp.Value, ws))); - this.Internals.RowsCollection.ForEach(kp => ws.Internals.RowsCollection.Add(kp.Key, new XLRow(kp.Value, ws))); + this.Internals.ColumnsCollection.ForEach(kp => ws.Internals.ColumnsCollection.Add(kp.Key, new XLColumn(kp.Value))); + this.Internals.RowsCollection.ForEach(kp => ws.Internals.RowsCollection.Add(kp.Key, new XLRow(kp.Value))); ws.Visibility = this.Visibility; ws.ColumnWidth = this.ColumnWidth; ws.RowHeight = this.RowHeight; @@ -585,7 +593,7 @@ foreach (var r in this.NamedRanges) { - var ranges = new XLRanges(workbook, this.style); + var ranges = new XLRanges(); r.Ranges.ForEach(rr => ranges.Add(rr)); ws.NamedRanges.Add(r.Name, ranges); } @@ -593,7 +601,7 @@ foreach(var t in this.Tables) { XLTable table; - if (ws.Tables.Where(tt => tt.Name == t.Name).Any()) + if (ws.Tables.Any(tt => tt.Name == t.Name)) table = new XLTable((XLRange)ws.Range(t.RangeAddress.ToString()), true); else table = new XLTable((XLRange)ws.Range(t.RangeAddress.ToString()), t.Name, true); @@ -606,6 +614,7 @@ table.ShowAutoFilter = t.ShowAutoFilter; table.Theme = t.Theme; table.showTotalsRow = t.ShowTotalsRow; + table.uniqueNames.Clear(); (t as XLTable).uniqueNames.ForEach(n => table.uniqueNames.Add(n)); Int32 fieldCount = t.ColumnCount(); @@ -812,54 +821,13 @@ public IXLCharts Charts { get; private set; } - public IXLColumns FindColumns(String search) - { - return FindColumns(search, XLSearchContents.ValuesAndFormulas); - } - public IXLColumns FindColumns(String search, XLSearchContents searchContents) - { - return FindColumns(search, searchContents, false, false); - } - public IXLColumns FindColumns(String search, XLSearchContents searchContents, Boolean useRegularExpressions) - { - throw new NotImplementedException(); - } - public IXLColumns FindColumns(String search, XLSearchContents searchContents, Boolean matchCase, Boolean entireCell) - { - throw new NotImplementedException(); - } + public Boolean ShowFormulas { get; set; } + public Boolean ShowGridLines { get; set; } + public Boolean ShowOutlineSymbols { get; set; } + public Boolean ShowRowColHeaders { get; set; } + public Boolean ShowRuler { get; set; } + public Boolean ShowWhiteSpace { get; set; } + public Boolean ShowZeros { get; set; } - public IXLRows FindRows(String search) - { - return FindRows(search, XLSearchContents.ValuesAndFormulas); - } - public IXLRows FindRows(String search, XLSearchContents searchContents) - { - return FindRows(search, searchContents, false, false); - } - public IXLRows FindRows(String search, XLSearchContents searchContents, Boolean useRegularExpressions) - { - throw new NotImplementedException(); - } - public IXLRows FindRows(String search, XLSearchContents searchContents, Boolean matchCase, Boolean entireCell) - { - throw new NotImplementedException(); - } - - public new IXLWorksheet Replace(String oldValue, String newValue) - { - base.Replace(oldValue, newValue); - return this; - } - public new IXLWorksheet Replace(String oldValue, String newValue, XLSearchContents searchContents) - { - base.Replace(oldValue, newValue, searchContents); - return this; - } - public new IXLWorksheet Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions) - { - base.Replace(oldValue, newValue, searchContents, useRegularExpressions); - return this; - } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs index 168ee95..379f08e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs @@ -2,6 +2,7 @@ using System.Collections.Generic; using System.Linq; using System.Text; +using System.Data; namespace ClosedXML.Excel { @@ -104,20 +105,17 @@ #endregion - public IXLWorksheets Replace(String oldValue, String newValue) + public IXLWorksheet Add(DataTable dataTable) { - worksheets.Values.ForEach(r => r.Replace(oldValue, newValue)); - return this; + var ws = Add(dataTable.TableName); + ws.Cell(1, 1).InsertTable(dataTable.AsEnumerable()); + ws.Columns().AdjustToContents(1, 75); + return ws; } - public IXLWorksheets Replace(String oldValue, String newValue, XLSearchContents searchContents) + public void Add(DataSet dataSet) { - worksheets.Values.ForEach(r => r.Replace(oldValue, newValue, searchContents)); - return this; - } - public IXLWorksheets Replace(String oldValue, String newValue, XLSearchContents searchContents, Boolean useRegularExpressions) - { - worksheets.Values.ForEach(r => r.Replace(oldValue, newValue, searchContents, useRegularExpressions)); - return this; + foreach (DataTable t in dataSet.Tables) + Add(t); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj index 7def1ed..39bfc5b 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -91,6 +91,8 @@ + + diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs index adb8342..732351c 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs @@ -75,6 +75,8 @@ new AutoFilter().Create(@"C:\Excel Files\Created\AutoFilter.xlsx"); new Sorting().Create(@"C:\Excel Files\Created\Sorting.xlsx"); new SortExample().Create(@"C:\Excel Files\Created\SortExample.xlsx"); + new AddingDataSet().Create(@"C:\Excel Files\Created\AddingDataSet.xlsx"); + new AddingDataTableAsWorksheet().Create(@"C:\Excel Files\Created\AddingDataTableAsWorksheet.xlsx"); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/AddingDataSet.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/AddingDataSet.cs new file mode 100644 index 0000000..270f19a --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/AddingDataSet.cs @@ -0,0 +1,91 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; + +using System.Drawing; +using System.Data; + +namespace ClosedXML_Examples.Misc +{ + public class AddingDataSet + { + #region Variables + + // Public + + // Private + + + #endregion + + #region Properties + + // Public + + // Private + + // Override + + + #endregion + + #region Events + + // Public + + // Private + + // Override + + + #endregion + + #region Methods + + // Public + public void Create(String filePath) + { + var wb = new XLWorkbook(); + + var dataSet = GetDataSet(); + + // Add all DataTables in the DataSet as a worksheets + wb.Worksheets.Add(dataSet); + + wb.SaveAs(filePath); + } + + // Private + private DataSet GetDataSet() + { + var ds = new DataSet(); + ds.Tables.Add(GetTable("Patients")); + ds.Tables.Add(GetTable("Employees")); + ds.Tables.Add(GetTable("Information")); + return ds; + } + + private DataTable GetTable(String tableName) + { + DataTable table = new DataTable(); + table.TableName = tableName; + table.Columns.Add("Dosage", typeof(int)); + table.Columns.Add("Drug", typeof(string)); + table.Columns.Add("Patient", typeof(string)); + table.Columns.Add("Date", typeof(DateTime)); + + table.Rows.Add(25, "Indocin", "David", DateTime.Now); + table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now); + table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now); + table.Rows.Add(21, "Combivent", "Janet", DateTime.Now); + table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now); + return table; + } + // Override + + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/AddingDataTableAsWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/AddingDataTableAsWorksheet.cs new file mode 100644 index 0000000..33a2372 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/AddingDataTableAsWorksheet.cs @@ -0,0 +1,82 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; + +using System.Drawing; +using System.Data; + +namespace ClosedXML_Examples.Misc +{ + public class AddingDataTableAsWorksheet + { + #region Variables + + // Public + + // Private + + + #endregion + + #region Properties + + // Public + + // Private + + // Override + + + #endregion + + #region Events + + // Public + + // Private + + // Override + + + #endregion + + #region Methods + + // Public + public void Create(String filePath) + { + var wb = new XLWorkbook(); + + var dataTable = GetTable("Information"); + + // Add a DataTable as a worksheet + wb.Worksheets.Add(dataTable); + + wb.SaveAs(filePath); + } + + // Private + private DataTable GetTable(String tableName) + { + DataTable table = new DataTable(); + table.TableName = tableName; + table.Columns.Add("Dosage", typeof(int)); + table.Columns.Add("Drug", typeof(string)); + table.Columns.Add("Patient", typeof(string)); + table.Columns.Add("Date", typeof(DateTime)); + + table.Rows.Add(25, "Indocin", "David", DateTime.Now); + table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now); + table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now); + table.Rows.Add(21, "Combivent", "Janet", DateTime.Now); + table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now); + return table; + } + // Override + + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index 7d70228..60c7487 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -14,11 +14,11 @@ { static void Main(string[] args) { - //var fileName = "BasicTable"; + //var fileName = "DataValidation"; var fileName = "Sandbox"; //var fileName = "Issue_6609"; - var wb = new XLWorkbook(String.Format(@"c:\Excel Files\ForTesting\{0}.xlsx", fileName)); - //var wb = new XLWorkbook(); + //var wb = new XLWorkbook(String.Format(@"c:\Excel Files\ForTesting\{0}.xlsx", fileName)); + var wb = new XLWorkbook(); //var ws = wb.Worksheets.Add("Sheet1"); //ws.Cell("A1").Value = "Category"; //ws.Cell("A2").Value = "A"; @@ -30,10 +30,11 @@ //ws.RangeUsed().CreateChart(4, 4, 22, 12); - - var ws = wb.Worksheet(1); - String a = ws.Cell("c1").ValueCached; - + var ws = wb.Worksheets.Add("Sheet1"); + ws.Name = "Sheet X"; + var wsX = wb.Worksheet("Sheet X"); + wsX.Cell(1, 1).Value = "Hello"; + wb.SaveAs(String.Format(@"c:\Excel Files\ForTesting\{0}_Saved.xlsx", fileName)); //Console.ReadKey(); } @@ -63,9 +64,9 @@ foreach (var i in Enumerable.Range(1, 1)) { var ws = wb.Worksheets.Add("Sheet" + i); - foreach (var ro in Enumerable.Range(1, 10000)) + foreach (var ro in Enumerable.Range(1, 2000)) { - foreach (var co in Enumerable.Range(1, 5)) + foreach (var co in Enumerable.Range(1, 100)) { ws.Cell(ro, co).Style = GetRandomStyle(); //if (rnd.Next(1, 5) == 1) @@ -76,12 +77,13 @@ //System.Threading.Thread.Sleep(10); } - Int32 rowCount = ws.LastRowUsed().RowNumber(); - for (Int32 ro = 1; ro <= rowCount; ro += 100) - { - var dv = ws.Range(ro, 1, ro + 99, 5).DataValidation; - } + //Int32 rowCount = ws.LastRowUsed().RowNumber(); + //for (Int32 ro = 1; ro <= rowCount; ro += 100) + //{ + // var dv = ws.Range(ro, 1, ro + 99, 5).DataValidation; + //} + //var rngUsed = ws.RangeUsed(); //ws.RangeUsed().Style.Border.BottomBorder = XLBorderStyleValues.DashDot; //ws.RangeUsed().Style.Border.BottomBorderColor = XLColor.AirForceBlue; //ws.RangeUsed().Style.Border.TopBorder = XLBorderStyleValues.DashDotDot; @@ -125,14 +127,14 @@ Console.WriteLine("Saved in {0} secs.", saved); var start1 = DateTime.Now; - //var wb1 = new XLWorkbook(@"C:\Excel Files\ForTesting\Benchmark.xlsx"); + var wb1 = new XLWorkbook(@"C:\Excel Files\ForTesting\Benchmark.xlsx"); var end1 = DateTime.Now; var loaded = (end1 - start1).TotalSeconds; runningLoad.Add(loaded); Console.WriteLine("Loaded in {0} secs.", loaded); var start2 = DateTime.Now; - //wb1.SaveAs(@"C:\Excel Files\ForTesting\Benchmark_Saved.xlsx"); + wb1.SaveAs(@"C:\Excel Files\ForTesting\Benchmark_Saved.xlsx"); var end2 = DateTime.Now; var savedBack = (end2 - start2).TotalSeconds; runningSavedBack.Add(savedBack); @@ -145,7 +147,7 @@ Console.WriteLine("Avg Save time: {0}", runningSave.Average()); Console.WriteLine("Avg Load time: {0}", runningLoad.Average()); Console.WriteLine("Avg Save Back time: {0}", runningSavedBack.Average()); - //Console.ReadKey(); + Console.ReadKey(); } private static IXLStyle style1;