diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index 13258c8..b841566 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -72,10 +72,7 @@ Address = address; ShareString = true; - if (defaultStyle == null) - m_style = new XLStyle(this, worksheet.Style); - else - m_style = new XLStyle(this, defaultStyle); + _style = defaultStyle == null ? new XLStyle(this, worksheet.Style) : new XLStyle(this, defaultStyle); _worksheet = worksheet; } @@ -84,9 +81,9 @@ public bool SettingHyperlink; public int SharedStringId; - private string m_formulaA1; - private string m_formulaR1C1; - private IXLStyle m_style; + private string _formulaA1; + private string _formulaR1C1; + private IXLStyle _style; public XLWorksheet Worksheet { @@ -101,10 +98,11 @@ { if (HasRichText) return _richText.ToString(); - else if (StringExtensions.IsNullOrWhiteSpace(_cellValue)) + + if (StringExtensions.IsNullOrWhiteSpace(_cellValue)) return FormulaA1; - else - return _cellValue; + + return _cellValue; } } @@ -120,11 +118,16 @@ get { return Address; } } - public IXLRange AsRange() + public XLRange AsRange() { return _worksheet.Range(Address, Address); } + IXLRange IXLCell.AsRange() + { + return AsRange(); + } + public IXLCell SetValue(T value) { FormulaA1 = String.Empty; @@ -146,10 +149,7 @@ { _dataType = XLCellValues.DateTime; var dtTest = (DateTime)Convert.ChangeType(value, typeof(DateTime)); - if (dtTest.Date == dtTest) - Style.NumberFormat.NumberFormatId = 14; - else - Style.NumberFormat.NumberFormatId = 22; + Style.NumberFormat.NumberFormatId = dtTest.Date == dtTest ? 14 : 22; _cellValue = dtTest.ToOADate().ToString(); } @@ -193,8 +193,8 @@ { if (typeof(T) == typeof(String)) return (T)Convert.ChangeType(Value.ToString(), typeof(T)); - else - return (T)Value; + + return (T)Value; } if (Value is IXLRichText) @@ -234,11 +234,7 @@ public string GetFormattedString() { - string cValue; - if (StringExtensions.IsNullOrWhiteSpace(FormulaA1)) - cValue = _cellValue; - else - cValue = GetString(); + string cValue = StringExtensions.IsNullOrWhiteSpace(FormulaA1) ? _cellValue : GetString(); if (_dataType == XLCellValues.Boolean) return (cValue != "0").ToString(); @@ -308,22 +304,23 @@ if (_dataType == XLCellValues.Boolean) return _cellValue != "0"; - else if (_dataType == XLCellValues.DateTime) + + if (_dataType == XLCellValues.DateTime) return DateTime.FromOADate(Double.Parse(_cellValue)); - else if (_dataType == XLCellValues.Number) + + if (_dataType == XLCellValues.Number) return Double.Parse(_cellValue); - else if (_dataType == XLCellValues.TimeSpan) + + if (_dataType == XLCellValues.TimeSpan) { // return (DateTime.FromOADate(Double.Parse(cellValue)) - baseDate); return TimeSpan.Parse(_cellValue); } - else - { - if (_richText == null) - return _cellValue; - else - return _richText.ToString(); - } + + if (_richText == null) + return _cellValue; + + return _richText.ToString(); } set @@ -359,7 +356,6 @@ { if (data != null && data.GetType() != typeof(String)) { - int co; int ro = Address.RowNumber + 1; int fRo = Address.RowNumber; bool hasTitles = false; @@ -367,7 +363,7 @@ bool isDataTable = false; foreach (object m in data) { - co = Address.ColumnNumber; + int co = Address.ColumnNumber; if (m.GetType().IsPrimitive || m.GetType() == typeof(String) || m.GetType() == typeof(DateTime)) { @@ -399,7 +395,7 @@ isDataTable = true; if (!hasTitles) { - foreach (DataColumn column in (m as DataRow).Table.Columns) + foreach (DataColumn column in ((DataRow)m).Table.Columns) { string fieldName = StringExtensions.IsNullOrWhiteSpace(column.Caption) ? column.ColumnName @@ -412,7 +408,7 @@ hasTitles = true; } - foreach (object item in (m as DataRow).ItemArray) + foreach (object item in ((DataRow)m).ItemArray) { SetValue(item, ro, co); co++; @@ -478,9 +474,9 @@ ClearMerged(); var range = _worksheet.Range( - Address.RowNumber, - Address.ColumnNumber, - ro - 1, + Address.RowNumber, + Address.ColumnNumber, + ro - 1, maxCo - 1); if (createTable) @@ -546,9 +542,9 @@ ClearMerged(); return _worksheet.Range( - Address.RowNumber, - Address.ColumnNumber, - Address.RowNumber + ro - 1, + Address.RowNumber, + Address.ColumnNumber, + Address.RowNumber + ro - 1, Address.ColumnNumber + maxCo - 1); } @@ -559,11 +555,11 @@ { get { - m_style = new XLStyle(this, m_style); - return m_style; + _style = new XLStyle(this, _style); + return _style; } - set { m_style = new XLStyle(this, value); } + set { _style = new XLStyle(this, value); } } public IXLCell SetDataType(XLCellValues dataType) @@ -608,16 +604,13 @@ { throw new ArgumentException( string.Format( - "Cannot set data type to DateTime because '{0}' is not recognized as a date.", + "Cannot set data type to DateTime because '{0}' is not recognized as a date.", _cellValue)); } if (Style.NumberFormat.Format == String.Empty && Style.NumberFormat.NumberFormatId == 0) { - if (_cellValue.Contains('.')) - Style.NumberFormat.NumberFormatId = 22; - else - Style.NumberFormat.NumberFormatId = 14; + Style.NumberFormat.NumberFormatId = _cellValue.Contains('.') ? 22 : 14; } } else if (value == XLCellValues.TimeSpan) @@ -639,7 +632,7 @@ { throw new ArgumentException( string.Format( - "Cannot set data type to TimeSpan because '{0}' is not recognized as a TimeSpan.", + "Cannot set data type to TimeSpan because '{0}' is not recognized as a TimeSpan.", _cellValue)); } } @@ -653,7 +646,7 @@ { throw new ArgumentException( string.Format( - "Cannot set data type to Number because '{0}' is not recognized as a number.", + "Cannot set data type to Number because '{0}' is not recognized as a number.", _cellValue)); } } @@ -666,22 +659,14 @@ _cellValue = TimeSpan.Parse(_cellValue).ToString(); else if (_dataType == XLCellValues.Number) { - string format; - if (Style.NumberFormat.NumberFormatId > 0) - format = formatCodes[Style.NumberFormat.NumberFormatId]; - else - format = Style.NumberFormat.Format; + string format = Style.NumberFormat.NumberFormatId > 0 ? formatCodes[Style.NumberFormat.NumberFormatId] : Style.NumberFormat.Format; if (!StringExtensions.IsNullOrWhiteSpace(format) && format != "@") _cellValue = Double.Parse(_cellValue).ToString(format); } else if (_dataType == XLCellValues.DateTime) { - string format; - if (Style.NumberFormat.NumberFormatId > 0) - format = formatCodes[Style.NumberFormat.NumberFormatId]; - else - format = Style.NumberFormat.Format; + string format = Style.NumberFormat.NumberFormatId > 0 ? formatCodes[Style.NumberFormat.NumberFormatId] : Style.NumberFormat.Format; _cellValue = DateTime.FromOADate(Double.Parse(_cellValue)).ToString(format); } } @@ -694,13 +679,12 @@ public void Clear() { - _worksheet.Range(Address, Address).Clear(); + AsRange().Clear(); } public void ClearStyles() { - var newStyle = new XLStyle(this, _worksheet.Style); - newStyle.NumberFormat = Style.NumberFormat; + var newStyle = new XLStyle(this, _worksheet.Style) {NumberFormat = Style.NumberFormat}; Style = newStyle; } @@ -713,28 +697,30 @@ { get { - if (StringExtensions.IsNullOrWhiteSpace(m_formulaA1)) + if (StringExtensions.IsNullOrWhiteSpace(_formulaA1)) { - if (!StringExtensions.IsNullOrWhiteSpace(m_formulaR1C1)) + if (!StringExtensions.IsNullOrWhiteSpace(_formulaR1C1)) { - m_formulaA1 = GetFormulaA1(m_formulaR1C1); + _formulaA1 = GetFormulaA1(_formulaR1C1); return FormulaA1; } - else + return String.Empty; } - else if (m_formulaA1.Trim()[0] == '=') - return m_formulaA1.Substring(1); - else if (m_formulaA1.Trim().StartsWith("{=")) - return "{" + m_formulaA1.Substring(2); - else - return m_formulaA1; + + if (_formulaA1.Trim()[0] == '=') + return _formulaA1.Substring(1); + + if (_formulaA1.Trim().StartsWith("{=")) + return "{" + _formulaA1.Substring(2); + + return _formulaA1; } set { - m_formulaA1 = value; - m_formulaR1C1 = String.Empty; + _formulaA1 = value; + _formulaR1C1 = String.Empty; } } @@ -742,15 +728,15 @@ { get { - if (StringExtensions.IsNullOrWhiteSpace(m_formulaR1C1)) - m_formulaR1C1 = GetFormulaR1C1(FormulaA1); + if (StringExtensions.IsNullOrWhiteSpace(_formulaR1C1)) + _formulaR1C1 = GetFormulaR1C1(FormulaA1); - return m_formulaR1C1; + return _formulaR1C1; } set { - m_formulaR1C1 = value; + _formulaR1C1 = value; // FormulaA1 = GetFormulaA1(value); } @@ -789,10 +775,14 @@ } } - public IXLDataValidation DataValidation + public XLDataValidation DataValidation { get { return AsRange().DataValidation; } } + IXLDataValidation IXLCell.DataValidation + { + get { return DataValidation; } + } public IXLCells InsertCellsAbove(int numberOfRows) { @@ -846,10 +836,7 @@ { if (_richText == null) { - if (StringExtensions.IsNullOrWhiteSpace(_cellValue)) - _richText = new XLRichText(m_style.Font); - else - _richText = new XLRichText(GetFormattedString(), m_style.Font); + _richText = StringExtensions.IsNullOrWhiteSpace(_cellValue) ? new XLRichText(_style.Font) : new XLRichText(GetFormattedString(), _style.Font); _dataType = XLCellValues.Text; if (!Style.Alignment.WrapText) @@ -865,6 +852,22 @@ get { return _richText != null; } } + public Boolean IsMerged() + { + return Worksheet.Internals.MergedRanges.Any(AsRange().Intersects); + } + + public Boolean IsUsed() + { + return IsUsed(false); + } + + public Boolean IsUsed(Boolean includeFormats) + { + return !StringExtensions.IsNullOrWhiteSpace(InnerText) || + (includeFormats && (!Style.Equals(Worksheet.Style) || IsMerged())); + } + #endregion #region IXLStylized Members @@ -891,8 +894,7 @@ { get { - var retVal = new XLRanges(); - retVal.Add(AsRange()); + var retVal = new XLRanges {AsRange()}; return retVal; } } @@ -902,11 +904,11 @@ private bool IsDateFormat() { return _dataType == XLCellValues.Number - && StringExtensions.IsNullOrWhiteSpace(Style.NumberFormat.Format) - && ((Style.NumberFormat.NumberFormatId >= 14 - && Style.NumberFormat.NumberFormatId <= 22) - || (Style.NumberFormat.NumberFormatId >= 45 - && Style.NumberFormat.NumberFormatId <= 47)); + && StringExtensions.IsNullOrWhiteSpace(Style.NumberFormat.Format) + && ((Style.NumberFormat.NumberFormatId >= 14 + && Style.NumberFormat.NumberFormatId <= 22) + || (Style.NumberFormat.NumberFormatId >= 45 + && Style.NumberFormat.NumberFormatId <= 47)); } private string GetFormat() @@ -941,61 +943,40 @@ { var tmp = rangeObject as XLCell; if (tmp != null) - { - asRange = tmp.AsRange() as XLRangeBase; - } + asRange = tmp.AsRange(); } if (asRange != null) { - Int32 maxRows; - Int32 maxColumns; - if (asRange is XLRow || asRange is XLColumn) + + if (!(asRange is XLRow || asRange is XLColumn)) { - var lastCellUsed = asRange.LastCellUsed(); - maxRows = lastCellUsed.Address.RowNumber; - maxColumns = lastCellUsed.Address.ColumnNumber; - } - else - { - maxRows = asRange.RowCount(); - maxColumns = asRange.ColumnCount(); + Int32 maxRows = asRange.RowCount(); + Int32 maxColumns = asRange.ColumnCount(); Worksheet.Range(Address.RowNumber, Address.ColumnNumber, maxRows, maxColumns).Clear(); } Int32 minRow = asRange.RangeAddress.FirstAddress.RowNumber; Int32 minColumn = asRange.RangeAddress.FirstAddress.ColumnNumber; - foreach (var sourceCell in asRange.CellsUsed(true)) + foreach (XLCell sourceCell in asRange.CellsUsed(true)) { Worksheet.Cell( Address.RowNumber + sourceCell.Address.RowNumber - minRow, Address.ColumnNumber + sourceCell.Address.ColumnNumber - minColumn - ).CopyFrom(sourceCell as XLCell); + ).CopyFrom(sourceCell); } - - var rangesToMerge = new List(); - foreach (var mergedRange in (asRange.Worksheet).Internals.MergedRanges) - { - if (asRange.Contains(mergedRange)) - { - var initialRo = Address.RowNumber + - (mergedRange.RangeAddress.FirstAddress.RowNumber - asRange.RangeAddress.FirstAddress.RowNumber); - var initialCo = Address.ColumnNumber + - (mergedRange.RangeAddress.FirstAddress.ColumnNumber - asRange.RangeAddress.FirstAddress.ColumnNumber); - rangesToMerge.Add(Worksheet.Range(initialRo, - initialCo, - initialRo + mergedRange.RowCount() - 1, - initialCo + mergedRange.ColumnCount() - 1)); - } - } + + var rangesToMerge = (from mergedRange in (asRange.Worksheet).Internals.MergedRanges + where asRange.Contains(mergedRange) + let initialRo = Address.RowNumber + (mergedRange.RangeAddress.FirstAddress.RowNumber - asRange.RangeAddress.FirstAddress.RowNumber) + let initialCo = Address.ColumnNumber + (mergedRange.RangeAddress.FirstAddress.ColumnNumber - asRange.RangeAddress.FirstAddress.ColumnNumber) + select Worksheet.Range(initialRo, initialCo, initialRo + mergedRange.RowCount() - 1, initialCo + mergedRange.ColumnCount() - 1)).Cast().ToList(); rangesToMerge.ForEach(r => r.Merge()); return true; } - else - { + return false; - } } private bool SetEnumerable(object collectionObject) @@ -1006,14 +987,7 @@ private void ClearMerged() { - List mergeToDelete = new List(); - foreach (var merge in Worksheet.Internals.MergedRanges) - { - if (merge.Intersects(AsRange())) - { - mergeToDelete.Add(merge); - } - } + var mergeToDelete = Worksheet.Internals.MergedRanges.Where(merge => merge.Intersects(AsRange())).ToList(); mergeToDelete.ForEach(m => Worksheet.Internals.MergedRanges.Remove(m)); } @@ -1037,7 +1011,7 @@ DateTime dtTest; bool bTest; TimeSpan tsTest; - if (m_style.NumberFormat.Format == "@") + if (_style.NumberFormat.Format == "@") { _dataType = XLCellValues.Text; if (val.Contains(Environment.NewLine) && !Style.Alignment.WrapText) @@ -1064,10 +1038,7 @@ if (Style.NumberFormat.Format == String.Empty && Style.NumberFormat.NumberFormatId == 0) { - if (dtTest.Date == dtTest) - Style.NumberFormat.NumberFormatId = 14; - else - Style.NumberFormat.NumberFormatId = 22; + Style.NumberFormat.NumberFormatId = dtTest.Date == dtTest ? 14 : 22; } val = dtTest.ToOADate().ToString(); @@ -1092,35 +1063,37 @@ { if (_formatCodes == null) { - var fCodes = new Dictionary(); - fCodes.Add(0, string.Empty); - fCodes.Add(1, "0"); - fCodes.Add(2, "0.00"); - fCodes.Add(3, "#,##0"); - fCodes.Add(4, "#,##0.00"); - fCodes.Add(9, "0%"); - fCodes.Add(10, "0.00%"); - fCodes.Add(11, "0.00E+00"); - fCodes.Add(12, "# ?/?"); - fCodes.Add(13, "# ??/??"); - fCodes.Add(14, "MM-dd-yy"); - fCodes.Add(15, "d-MMM-yy"); - fCodes.Add(16, "d-MMM"); - fCodes.Add(17, "MMM-yy"); - fCodes.Add(18, "h:mm AM/PM"); - fCodes.Add(19, "h:mm:ss AM/PM"); - fCodes.Add(20, "h:mm"); - fCodes.Add(21, "h:mm:ss"); - fCodes.Add(22, "M/d/yy h:mm"); - fCodes.Add(37, "#,##0 ;(#,##0)"); - fCodes.Add(38, "#,##0 ;[Red](#,##0)"); - fCodes.Add(39, "#,##0.00;(#,##0.00)"); - fCodes.Add(40, "#,##0.00;[Red](#,##0.00)"); - fCodes.Add(45, "mm:ss"); - fCodes.Add(46, "[h]:mm:ss"); - fCodes.Add(47, "mmss.0"); - fCodes.Add(48, "##0.0E+0"); - fCodes.Add(49, "@"); + var fCodes = new Dictionary + { + {0, string.Empty}, + {1, "0"}, + {2, "0.00"}, + {3, "#,##0"}, + {4, "#,##0.00"}, + {9, "0%"}, + {10, "0.00%"}, + {11, "0.00E+00"}, + {12, "# ?/?"}, + {13, "# ??/??"}, + {14, "MM-dd-yy"}, + {15, "d-MMM-yy"}, + {16, "d-MMM"}, + {17, "MMM-yy"}, + {18, "h:mm AM/PM"}, + {19, "h:mm:ss AM/PM"}, + {20, "h:mm"}, + {21, "h:mm:ss"}, + {22, "M/d/yy h:mm"}, + {37, "#,##0 ;(#,##0)"}, + {38, "#,##0 ;[Red](#,##0)"}, + {39, "#,##0.00;(#,##0.00)"}, + {40, "#,##0.00;[Red](#,##0.00)"}, + {45, "mm:ss"}, + {46, "[h]:mm:ss"}, + {47, "mmss.0"}, + {48, "##0.0E+0"}, + {49, "@"} + }; _formatCodes = fCodes; } @@ -1129,15 +1102,15 @@ private string GetFormulaR1C1(string value) { - return GetFormula(value, FormulaConversionType.A1toR1C1, 0, 0); + return GetFormula(value, FormulaConversionType.A1ToR1C1, 0, 0); } private string GetFormulaA1(string value) { - return GetFormula(value, FormulaConversionType.R1C1toA1, 0, 0); + return GetFormula(value, FormulaConversionType.R1C1ToA1, 0, 0); } - private string GetFormula(string strValue, FormulaConversionType conversionType, int rowsToShift, + private string GetFormula(string strValue, FormulaConversionType conversionType, int rowsToShift, int columnsToShift) { if (StringExtensions.IsNullOrWhiteSpace(strValue)) @@ -1145,7 +1118,7 @@ string value = ">" + strValue + "<"; - var regex = conversionType == FormulaConversionType.A1toR1C1 ? _a1Regex : r1c1Regex; + var regex = conversionType == FormulaConversionType.A1ToR1C1 ? _a1Regex : r1c1Regex; var sb = new StringBuilder(); int lastIndex = 0; @@ -1158,10 +1131,9 @@ { // Check if the match is in between quotes sb.Append(value.Substring(lastIndex, matchIndex - lastIndex)); - if (conversionType == FormulaConversionType.A1toR1C1) - sb.Append(GetR1C1Address(matchString, rowsToShift, columnsToShift)); - else - sb.Append(GetA1Address(matchString, rowsToShift, columnsToShift)); + sb.Append(conversionType == FormulaConversionType.A1ToR1C1 + ? GetR1C1Address(matchString, rowsToShift, columnsToShift) + : GetA1Address(matchString, rowsToShift, columnsToShift)); } else sb.Append(value.Substring(lastIndex, matchIndex - lastIndex + matchString.Length)); @@ -1175,9 +1147,9 @@ return retVal.Substring(1, retVal.Length - 2); } - private string GetA1Address(string r1c1Address, int rowsToShift, int columnsToShift) + private string GetA1Address(string r1C1Address, int rowsToShift, int columnsToShift) { - string addressToUse = r1c1Address.ToUpper(); + string addressToUse = r1C1Address.ToUpper(); if (addressToUse.Contains(':')) { @@ -1199,17 +1171,15 @@ return leftPart + ":" + rightPart; } - else - { - string rowPart = addressToUse.Substring(0, addressToUse.IndexOf("C")); - string rowToReturn = GetA1Row(rowPart, rowsToShift); - string columnPart = addressToUse.Substring(addressToUse.IndexOf("C")); - string columnToReturn = GetA1Column(columnPart, columnsToShift); + string rowPart = addressToUse.Substring(0, addressToUse.IndexOf("C")); + string rowToReturn = GetA1Row(rowPart, rowsToShift); - string retAddress = columnToReturn + rowToReturn; - return retAddress; - } + string columnPart = addressToUse.Substring(addressToUse.IndexOf("C")); + string columnToReturn = GetA1Column(columnPart, columnsToShift); + + string retAddress = columnToReturn + rowToReturn; + return retAddress; } private string GetA1Column(string columnPart, int columnsToShift) @@ -1235,9 +1205,11 @@ ); } else + { columnToReturn = "$" + ExcelHelper.GetColumnLetterFromNumber(Int32.Parse(columnPart.Substring(1)) + columnsToShift); + } } return columnToReturn; @@ -1304,10 +1276,7 @@ int rowDiff = rowNumber - Address.RowNumber; if (rowDiff != 0 || fixedRow) { - if (fixedRow) - rowPart = String.Format("R{0}", rowNumber); - else - rowPart = String.Format("R[{0}]", rowDiff); + rowPart = fixedRow ? String.Format("R{0}", rowNumber) : String.Format("R[{0}]", rowDiff); } else rowPart = "R"; @@ -1322,10 +1291,7 @@ int columnDiff = columnNumber - Address.ColumnNumber; if (columnDiff != 0 || fixedColumn) { - if (fixedColumn) - columnPart = String.Format("C{0}", columnNumber); - else - columnPart = String.Format("C[{0}]", columnDiff); + columnPart = fixedColumn ? String.Format("C{0}", columnNumber) : String.Format("C[{0}]", columnDiff); } else columnPart = "C"; @@ -1349,7 +1315,7 @@ _dataType = source._dataType; FormulaR1C1 = source.FormulaR1C1; - m_style = new XLStyle(this, source.m_style); + _style = new XLStyle(this, source._style); if (source._hyperlink != null) { @@ -1360,24 +1326,17 @@ var asRange = source.AsRange(); if (source.Worksheet.DataValidations.Any(dv => dv.Ranges.Contains(asRange))) - (DataValidation as XLDataValidation).CopyFrom(source.DataValidation); + DataValidation.CopyFrom(source.DataValidation); return this; } - // internal void ShiftFormula(Int32 rowsToShift, Int32 columnsToShift) - // { - // if (!StringExtensions.IsNullOrWhiteSpace(formulaA1)) - // FormulaR1C1 = GetFormula(formulaA1, FormulaConversionType.A1toR1C1, rowsToShift, columnsToShift); - // else if (!StringExtensions.IsNullOrWhiteSpace(formulaR1C1)) - // FormulaA1 = GetFormula(formulaR1C1, FormulaConversionType.R1C1toA1, rowsToShift, columnsToShift); - // } internal void ShiftFormulaRows(XLRange shiftedRange, int rowsShifted) { if (!StringExtensions.IsNullOrWhiteSpace(FormulaA1)) { - string value = ">" + m_formulaA1 + "<"; + string value = ">" + _formulaA1 + "<"; var regex = _a1SimpleRegex; @@ -1442,10 +1401,9 @@ else row2 = (Int32.Parse(row2String) + rowsShifted).ToStringLookup(); - if (useSheetName) - sb.Append(String.Format("'{0}'!{1}:{2}", sheetName, row1, row2)); - else - sb.Append(String.Format("{0}:{1}", row1, row2)); + sb.Append(useSheetName + ? String.Format("'{0}'!{1}:{2}", sheetName, row1, row2) + : String.Format("{0}:{1}", row1, row2)); } else if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= matchRange.RangeAddress.FirstAddress.RowNumber) @@ -1454,50 +1412,50 @@ { if (useSheetName) { - sb.Append(String.Format("'{0}'!{1}:{2}", - sheetName, - new XLAddress(_worksheet, + sb.Append(String.Format("'{0}'!{1}:{2}", + sheetName, + new XLAddress(_worksheet, matchRange.RangeAddress. FirstAddress.RowNumber + - rowsShifted, + rowsShifted, matchRange.RangeAddress. - FirstAddress.ColumnLetter, + FirstAddress.ColumnLetter, matchRange.RangeAddress. - FirstAddress.FixedRow, + FirstAddress.FixedRow, matchRange.RangeAddress. - FirstAddress.FixedColumn), - new XLAddress(_worksheet, + FirstAddress.FixedColumn), + new XLAddress(_worksheet, matchRange.RangeAddress. LastAddress.RowNumber + - rowsShifted, + rowsShifted, matchRange.RangeAddress. - LastAddress.ColumnLetter, + LastAddress.ColumnLetter, matchRange.RangeAddress. - LastAddress.FixedRow, + LastAddress.FixedRow, matchRange.RangeAddress. LastAddress.FixedColumn))); } else { - sb.Append(String.Format("{0}:{1}", - new XLAddress(_worksheet, + sb.Append(String.Format("{0}:{1}", + new XLAddress(_worksheet, matchRange.RangeAddress. FirstAddress.RowNumber + - rowsShifted, + rowsShifted, matchRange.RangeAddress. - FirstAddress.ColumnLetter, + FirstAddress.ColumnLetter, matchRange.RangeAddress. - FirstAddress.FixedRow, + FirstAddress.FixedRow, matchRange.RangeAddress. - FirstAddress.FixedColumn), - new XLAddress(_worksheet, + FirstAddress.FixedColumn), + new XLAddress(_worksheet, matchRange.RangeAddress. LastAddress.RowNumber + - rowsShifted, + rowsShifted, matchRange.RangeAddress. - LastAddress.ColumnLetter, + LastAddress.ColumnLetter, matchRange.RangeAddress. - LastAddress.FixedRow, + LastAddress.FixedRow, matchRange.RangeAddress. LastAddress.FixedColumn))); } @@ -1506,30 +1464,30 @@ { if (useSheetName) { - sb.Append(String.Format("'{0}'!{1}", - sheetName, - new XLAddress(_worksheet, + sb.Append(String.Format("'{0}'!{1}", + sheetName, + new XLAddress(_worksheet, matchRange.RangeAddress. FirstAddress.RowNumber + - rowsShifted, + rowsShifted, matchRange.RangeAddress. - FirstAddress.ColumnLetter, + FirstAddress.ColumnLetter, matchRange.RangeAddress. - FirstAddress.FixedRow, + FirstAddress.FixedRow, matchRange.RangeAddress. FirstAddress.FixedColumn))); } else { - sb.Append(String.Format("{0}", - new XLAddress(_worksheet, + sb.Append(String.Format("{0}", + new XLAddress(_worksheet, matchRange.RangeAddress. FirstAddress.RowNumber + - rowsShifted, + rowsShifted, matchRange.RangeAddress. - FirstAddress.ColumnLetter, + FirstAddress.ColumnLetter, matchRange.RangeAddress. - FirstAddress.FixedRow, + FirstAddress.FixedRow, matchRange.RangeAddress. FirstAddress.FixedColumn))); } @@ -1539,32 +1497,32 @@ { if (useSheetName) { - sb.Append(String.Format("'{0}'!{1}:{2}", - sheetName, - matchRange.RangeAddress.FirstAddress, - new XLAddress(_worksheet, + sb.Append(String.Format("'{0}'!{1}:{2}", + sheetName, + matchRange.RangeAddress.FirstAddress, + new XLAddress(_worksheet, matchRange.RangeAddress. LastAddress.RowNumber + - rowsShifted, + rowsShifted, matchRange.RangeAddress. - LastAddress.ColumnLetter, + LastAddress.ColumnLetter, matchRange.RangeAddress. - LastAddress.FixedRow, + LastAddress.FixedRow, matchRange.RangeAddress. LastAddress.FixedColumn))); } else { - sb.Append(String.Format("{0}:{1}", - matchRange.RangeAddress.FirstAddress, - new XLAddress(_worksheet, + sb.Append(String.Format("{0}:{1}", + matchRange.RangeAddress.FirstAddress, + new XLAddress(_worksheet, matchRange.RangeAddress. LastAddress.RowNumber + - rowsShifted, + rowsShifted, matchRange.RangeAddress. - LastAddress.ColumnLetter, + LastAddress.ColumnLetter, matchRange.RangeAddress. - LastAddress.FixedRow, + LastAddress.FixedRow, matchRange.RangeAddress. LastAddress.FixedColumn))); } @@ -1588,7 +1546,7 @@ sb.Append(value.Substring(lastIndex)); string retVal = sb.ToString(); - m_formulaA1 = retVal.Substring(1, retVal.Length - 2); + _formulaA1 = retVal.Substring(1, retVal.Length - 2); } } @@ -1596,7 +1554,7 @@ { if (!StringExtensions.IsNullOrWhiteSpace(FormulaA1)) { - string value = ">" + m_formulaA1 + "<"; + string value = ">" + _formulaA1 + "<"; var regex = _a1SimpleRegex; @@ -1675,10 +1633,9 @@ columnsShifted); } - if (useSheetName) - sb.Append(String.Format("'{0}'!{1}:{2}", sheetName, column1, column2)); - else - sb.Append(String.Format("{0}:{1}", column1, column2)); + sb.Append(useSheetName + ? String.Format("'{0}'!{1}:{2}", sheetName, column1, column2) + : String.Format("{0}:{1}", column1, column2)); } else if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= matchRange.RangeAddress.FirstAddress.ColumnNumber) @@ -1687,50 +1644,50 @@ { if (useSheetName) { - sb.Append(String.Format("'{0}'!{1}:{2}", - sheetName, - new XLAddress(_worksheet, + sb.Append(String.Format("'{0}'!{1}:{2}", + sheetName, + new XLAddress(_worksheet, matchRange.RangeAddress. - FirstAddress.RowNumber, + FirstAddress.RowNumber, matchRange.RangeAddress. FirstAddress.ColumnNumber + - columnsShifted, + columnsShifted, matchRange.RangeAddress. - FirstAddress.FixedRow, + FirstAddress.FixedRow, matchRange.RangeAddress. - FirstAddress.FixedColumn), - new XLAddress(_worksheet, + FirstAddress.FixedColumn), + new XLAddress(_worksheet, matchRange.RangeAddress. - LastAddress.RowNumber, + LastAddress.RowNumber, matchRange.RangeAddress. LastAddress.ColumnNumber + - columnsShifted, + columnsShifted, matchRange.RangeAddress. - LastAddress.FixedRow, + LastAddress.FixedRow, matchRange.RangeAddress. LastAddress.FixedColumn))); } else { - sb.Append(String.Format("{0}:{1}", - new XLAddress(_worksheet, + sb.Append(String.Format("{0}:{1}", + new XLAddress(_worksheet, matchRange.RangeAddress. - FirstAddress.RowNumber, + FirstAddress.RowNumber, matchRange.RangeAddress. FirstAddress.ColumnNumber + - columnsShifted, + columnsShifted, matchRange.RangeAddress. - FirstAddress.FixedRow, + FirstAddress.FixedRow, matchRange.RangeAddress. - FirstAddress.FixedColumn), - new XLAddress(_worksheet, + FirstAddress.FixedColumn), + new XLAddress(_worksheet, matchRange.RangeAddress. - LastAddress.RowNumber, + LastAddress.RowNumber, matchRange.RangeAddress. LastAddress.ColumnNumber + - columnsShifted, + columnsShifted, matchRange.RangeAddress. - LastAddress.FixedRow, + LastAddress.FixedRow, matchRange.RangeAddress. LastAddress.FixedColumn))); } @@ -1739,30 +1696,30 @@ { if (useSheetName) { - sb.Append(String.Format("'{0}'!{1}", - sheetName, - new XLAddress(_worksheet, + sb.Append(String.Format("'{0}'!{1}", + sheetName, + new XLAddress(_worksheet, matchRange.RangeAddress. - FirstAddress.RowNumber, + FirstAddress.RowNumber, matchRange.RangeAddress. FirstAddress.ColumnNumber + - columnsShifted, + columnsShifted, matchRange.RangeAddress. - FirstAddress.FixedRow, + FirstAddress.FixedRow, matchRange.RangeAddress. FirstAddress.FixedColumn))); } else { - sb.Append(String.Format("{0}", - new XLAddress(_worksheet, + sb.Append(String.Format("{0}", + new XLAddress(_worksheet, matchRange.RangeAddress. - FirstAddress.RowNumber, + FirstAddress.RowNumber, matchRange.RangeAddress. FirstAddress.ColumnNumber + - columnsShifted, + columnsShifted, matchRange.RangeAddress. - FirstAddress.FixedRow, + FirstAddress.FixedRow, matchRange.RangeAddress. FirstAddress.FixedColumn))); } @@ -1772,32 +1729,32 @@ { if (useSheetName) { - sb.Append(String.Format("'{0}'!{1}:{2}", - sheetName, - matchRange.RangeAddress.FirstAddress, - new XLAddress(_worksheet, + sb.Append(String.Format("'{0}'!{1}:{2}", + sheetName, + matchRange.RangeAddress.FirstAddress, + new XLAddress(_worksheet, matchRange.RangeAddress. - LastAddress.RowNumber, + LastAddress.RowNumber, matchRange.RangeAddress. LastAddress.ColumnNumber + - columnsShifted, + columnsShifted, matchRange.RangeAddress. - LastAddress.FixedRow, + LastAddress.FixedRow, matchRange.RangeAddress. LastAddress.FixedColumn))); } else { - sb.Append(String.Format("{0}:{1}", - matchRange.RangeAddress.FirstAddress, - new XLAddress(_worksheet, + sb.Append(String.Format("{0}:{1}", + matchRange.RangeAddress.FirstAddress, + new XLAddress(_worksheet, matchRange.RangeAddress. - LastAddress.RowNumber, + LastAddress.RowNumber, matchRange.RangeAddress. LastAddress.ColumnNumber + - columnsShifted, + columnsShifted, matchRange.RangeAddress. - LastAddress.FixedRow, + LastAddress.FixedRow, matchRange.RangeAddress. LastAddress.FixedColumn))); } @@ -1822,34 +1779,20 @@ string retVal = sb.ToString(); - m_formulaA1 = retVal.Substring(1, retVal.Length - 2); + _formulaA1 = retVal.Substring(1, retVal.Length - 2); } } // -- + #region Nested type: FormulaConversionType private enum FormulaConversionType { - A1toR1C1, - R1C1toA1 - }; + A1ToR1C1, + R1C1ToA1 + } ; #endregion - - - public Boolean IsMerged() - { - return Worksheet.Internals.MergedRanges.Any(AsRange().Intersects); - } - - public Boolean IsUsed() - { - return IsUsed(false); - } - public Boolean IsUsed(Boolean includeFormats) - { - return !StringExtensions.IsNullOrWhiteSpace(InnerText) || (includeFormats && (!Style.Equals(Worksheet.Style) || IsMerged())); - } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs index 259fe75..5a38cb4 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs @@ -249,6 +249,7 @@ /// IXLSheetView SheetView { get; } + IXLTable Table(Int32 index); IXLTable Table(String name); IXLTables Tables { get; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index dcd87e3..b9c071d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -70,13 +70,13 @@ } } - public IXLDataValidation DataValidation + public XLDataValidation DataValidation { get { var thisRange = AsRange(); if (Worksheet.DataValidations.ContainsSingle(thisRange)) - return Worksheet.DataValidations.Where(dv => dv.Ranges.Contains(thisRange)).Single(); + return Worksheet.DataValidations.Where(dv => dv.Ranges.Contains(thisRange)).Single() as XLDataValidation; var dvEmpty = new List(); foreach (IXLDataValidation dv in Worksheet.DataValidations) { @@ -106,6 +106,11 @@ } } + IXLDataValidation IXLRangeBase.DataValidation + { + get { return DataValidation; } + } + public Object Value { set { Cells().ForEach(c => c.Value = value); } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs index 1c678fa..a2e6650 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs @@ -1,4 +1,5 @@ using System; +using System.Collections.Generic; namespace ClosedXML.Excel { public enum XLTableTheme @@ -81,6 +82,7 @@ IXLRangeRow TotalsRow(); IXLTableField Field(string fieldName); IXLTableField Field(int fieldIndex); + IEnumerable Fields { get; } /// /// Gets the first data row of the table. diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTables.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTables.cs index 6c12d67..ce14b63 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTables.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTables.cs @@ -6,7 +6,7 @@ public interface IXLTables: IEnumerable { void Add(IXLTable table); - //IXLTable Table(Int32 index); + 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 7a07fcf..96f6daa 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs @@ -8,22 +8,26 @@ internal class XLTable : XLRange, IXLTable { #region Private fields - private string m_name; - internal bool m_showTotalsRow; - internal Dictionary m_fields = new Dictionary(); - private readonly Dictionary m_fieldNames = new Dictionary(); - internal HashSet m_uniqueNames; + + private readonly Dictionary _fieldNames = new Dictionary(); + internal Dictionary _fields = new Dictionary(); + private string _name; + internal bool _showTotalsRow; + internal HashSet _uniqueNames; + #endregion + #region Constructor + public XLTable(XLRange range, Boolean addToTables) - : base(range.RangeParameters) + : base(range.RangeParameters) { InitializeValues(); Int32 id = 1; while (true) { - var tableName = String.Format("Table{0}", id); + string tableName = String.Format("Table{0}", id); if (!Worksheet.Tables.Any(t => t.Name == tableName)) { Name = tableName; @@ -33,16 +37,33 @@ id++; } } + public XLTable(XLRange range, String name, Boolean addToTables) - : base(range.RangeParameters) + : base(range.RangeParameters) { InitializeValues(); Name = name; AddToTables(range, addToTables); } + #endregion + public String RelId { get; set; } + + public IXLRange DataRange + { + get + { + if (_showTotalsRow) + return Range(2, 1, RowCount() - 1, ColumnCount()); + + return Range(2, 1, RowCount(), ColumnCount()); + } + } + + #region IXLTable Members + public Boolean EmphasizeFirstColumn { get; set; } public Boolean EmphasizeLastColumn { get; set; } public Boolean ShowRowStripes { get; set; } @@ -52,110 +73,42 @@ public String Name { - get { return m_name; } + get { return _name; } set { if (Worksheet.Tables.Any(t => t.Name == value)) - { - throw new ArgumentException(String.Format("This worksheet already contains a table named '{0}'", value)); - } + throw new ArgumentException(String.Format("This worksheet already contains a table named '{0}'", + value)); - m_name = value; + _name = value; } } public Boolean ShowTotalsRow { - get { return m_showTotalsRow; } + get { return _showTotalsRow; } set { - if (value && !m_showTotalsRow) - { + if (value && !_showTotalsRow) InsertRowsBelow(1); - } - else if (!value && m_showTotalsRow) - { + else if (!value && _showTotalsRow) TotalsRow().Delete(); - } - m_showTotalsRow = value; + _showTotalsRow = value; } } - public IXLRange DataRange - { - get - { - if (m_showTotalsRow) - { - return base.Range(2, 1, RowCount() - 1, ColumnCount()); - } - else - { - return base.Range(2, 1, RowCount(), ColumnCount()); - } - } - } - - private void InitializeValues() - { - ShowRowStripes = true; - ShowAutoFilter = true; - Theme = XLTableTheme.TableStyleLight9; - } - - private void AddToTables(XLRange range, Boolean addToTables) - { - if (addToTables) - { - m_uniqueNames = new HashSet(); - Int32 co = 1; - foreach (var c in range.Row(1).Cells()) - { - if (StringExtensions.IsNullOrWhiteSpace(((XLCell) c).InnerText)) - { - c.Value = GetUniqueName("Column" + co.ToStringLookup()); - } - m_uniqueNames.Add(c.GetString()); - co++; - } - Worksheet.Tables.Add(this); - } - } - - private String GetUniqueName(String originalName) - { - String name = originalName; - if (m_uniqueNames.Contains(name)) - { - Int32 i = 1; - name = originalName + i.ToStringLookup(); - while (m_uniqueNames.Contains(name)) - { - i++; - name = originalName + i.ToStringLookup(); - } - } - - m_uniqueNames.Add(name); - return name; - } - public IXLRangeRow HeadersRow() { - return new XLTableRow(this, (XLRangeRow) base.FirstRow()); + return new XLTableRow(this, base.FirstRow()); } public IXLRangeRow TotalsRow() { if (ShowTotalsRow) - { - return new XLTableRow(this, (XLRangeRow) base.LastRow()); - } - else - { + return new XLTableRow(this, (XLRangeRow)base.LastRow()); + throw new InvalidOperationException("Cannot access TotalsRow if ShowTotals property is false"); - } } public new IXLTableRow FirstRow() @@ -165,41 +118,36 @@ public new IXLTableRow FirstRowUsed() { - return new XLTableRow(this, (XLRangeRow) (DataRange.FirstRowUsed())); + return new XLTableRow(this, (XLRangeRow)(DataRange.FirstRowUsed())); } public new IXLTableRow LastRow() { if (ShowTotalsRow) - { - return new XLTableRow(this, (XLRangeRow) base.Row(RowCount() - 1)); - } - else - { - return new XLTableRow(this, (XLRangeRow) base.Row(RowCount())); - } + return new XLTableRow(this, base.Row(RowCount() - 1)); + + return new XLTableRow(this, base.Row(RowCount())); } public new IXLTableRow LastRowUsed() { - return new XLTableRow(this, (XLRangeRow) (DataRange.LastRowUsed())); + return new XLTableRow(this, (XLRangeRow)(DataRange.LastRowUsed())); } public new IXLTableRow Row(int row) { if (row <= 0 || row > ExcelHelper.MaxRowNumber) - throw new IndexOutOfRangeException(String.Format("Row number must be between 1 and {0}", ExcelHelper.MaxRowNumber)); + throw new IndexOutOfRangeException(String.Format("Row number must be between 1 and {0}", + ExcelHelper.MaxRowNumber)); - return new XLTableRow(this, (XLRangeRow) base.Row(row + 1)); + return new XLTableRow(this, base.Row(row + 1)); } public new IXLTableRows Rows() { var retVal = new XLTableRows(Worksheet.Style); - foreach (var r in Enumerable.Range(1, DataRange.RowCount())) - { + foreach (int r in Enumerable.Range(1, DataRange.RowCount())) retVal.Add(Row(r)); - } return retVal; } @@ -207,10 +155,8 @@ { var retVal = new XLTableRows(Worksheet.Style); - for (var ro = firstRow; ro <= lastRow; ro++) - { + for (int ro = firstRow; ro <= lastRow; ro++) retVal.Add(Row(ro)); - } return retVal; } @@ -218,17 +164,15 @@ { var retVal = new XLTableRows(Worksheet.Style); var rowPairs = rows.Split(','); - foreach (var pair in rowPairs) + foreach (string pair in rowPairs) { - var tPair = pair.Trim(); + string 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]; @@ -239,10 +183,8 @@ firstRow = tPair; lastRow = tPair; } - foreach (var row in Rows(Int32.Parse(firstRow), Int32.Parse(lastRow))) - { + foreach (IXLTableRow row in Rows(Int32.Parse(firstRow), Int32.Parse(lastRow))) retVal.Add(row); - } } return retVal; } @@ -251,38 +193,36 @@ { return DataRange.Column(column); } + public new IXLRangeColumn Column(String column) { if (ExcelHelper.IsValidColumn(column)) { Int32 coNum = ExcelHelper.GetColumnNumberFromLetter(column); if (coNum > ColumnCount()) - { return DataRange.Column(GetFieldIndex(column) + 1); - } - else - { - return DataRange.Column(coNum); - } + + return DataRange.Column(coNum); } - else - { - return DataRange.Column(GetFieldIndex(column) + 1); - } + + return DataRange.Column(GetFieldIndex(column) + 1); } public new IXLRangeColumns Columns() { return DataRange.Columns(); } + public new IXLRangeColumns Columns(Int32 firstColumn, Int32 lastColumn) { return DataRange.Columns(firstColumn, lastColumn); } + public new IXLRangeColumns Columns(String firstColumn, String lastColumn) { return DataRange.Columns(firstColumn, lastColumn); } + public new IXLRangeColumns Columns(String columns) { return DataRange.Columns(columns); @@ -292,14 +232,17 @@ { return Cell(row, column); } + IXLCell IXLTable.Cell(string cellAddressInRange) { return Cell(cellAddressInRange); } + IXLCell IXLTable.Cell(int row, string column) { return Cell(row, column); } + IXLCell IXLTable.Cell(IXLAddress cellAddressInRange) { return Cell(cellAddressInRange); @@ -309,22 +252,27 @@ { return Range(rangeAddress); } + IXLRange IXLTable.Range(string rangeAddress) { return Range(rangeAddress); } + IXLRange IXLTable.Range(IXLCell firstCell, IXLCell lastCell) { return Range(firstCell, lastCell); } + IXLRange IXLTable.Range(string firstCellAddress, string lastCellAddress) { return Range(firstCellAddress, lastCellAddress); } + IXLRange IXLTable.Range(IXLAddress firstCellAddress, IXLAddress lastCellAddress) { return Range(firstCellAddress, lastCellAddress); } + IXLRange IXLTable.Range(int firstCellRow, int firstCellColumn, int lastCellRow, int lastCellColumn) { return Range(firstCellRow, firstCellColumn, lastCellRow, lastCellColumn); @@ -337,58 +285,32 @@ public IXLTableField Field(Int32 fieldIndex) { - if (!m_fields.ContainsKey(fieldIndex)) + if (!_fields.ContainsKey(fieldIndex)) { if (fieldIndex >= HeadersRow().CellCount()) - { throw new ArgumentOutOfRangeException(); - } - var newField = new XLTableField(this) {Index = fieldIndex, Name = HeadersRow().Cell(fieldIndex + 1).GetString()}; - m_fields.Add(fieldIndex, newField); + var newField = new XLTableField(this) + {Index = fieldIndex, Name = HeadersRow().Cell(fieldIndex + 1).GetString()}; + _fields.Add(fieldIndex, newField); } - return m_fields[fieldIndex]; + return _fields[fieldIndex]; } - public Int32 GetFieldIndex(String name) + public IEnumerable Fields { - if (m_fieldNames.ContainsKey(name)) + get { - return m_fieldNames[name].Index; - } - else - { - var headersRow = HeadersRow(); - Int32 cellCount = headersRow.CellCount(); - for (Int32 cellPos = 1; cellPos <= cellCount; cellPos++) - { - if (headersRow.Cell(cellPos).GetString().Equals(name)) - { - if (m_fieldNames.ContainsKey(name)) - { - throw new ArgumentException("The header row contains more than one field name '" + name + "'."); - } - else - { - m_fieldNames.Add(name, Field(cellPos - 1)); - } - } - } - if (m_fieldNames.ContainsKey(name)) - { - return m_fieldNames[name].Index; - } - else - { - throw new ArgumentOutOfRangeException("The header row doesn't contain field name '" + name + "'."); - } + Int32 columnCount = ColumnCount(); + for (int co = 0; co < columnCount; co++) + yield return Field(co); } } public new IXLRange Sort(String elementsToSortBy) { - StringBuilder toSortBy = new StringBuilder(); + var toSortBy = new StringBuilder(); foreach (String coPair in elementsToSortBy.Split(',')) { String coPairTrimmed = coPair.Trim(); @@ -408,9 +330,7 @@ Int32 co; if (!Int32.TryParse(coString, out co)) - { co = Field(coString).Index + 1; - } toSortBy.Append(co); toSortBy.Append(" "); @@ -420,13 +340,77 @@ return DataRange.Sort(toSortBy.ToString(0, toSortBy.Length - 1)); } - public IXLTable SetEmphasizeFirstColumn() { EmphasizeFirstColumn = true; return this; } public IXLTable SetEmphasizeFirstColumn(Boolean value) { EmphasizeFirstColumn = value; return this; } - public IXLTable SetEmphasizeLastColumn() { EmphasizeLastColumn = true; return this; } public IXLTable SetEmphasizeLastColumn(Boolean value) { EmphasizeLastColumn = value; return this; } - public IXLTable SetShowRowStripes() { ShowRowStripes = true; return this; } public IXLTable SetShowRowStripes(Boolean value) { ShowRowStripes = value; return this; } - public IXLTable SetShowColumnStripes() { ShowColumnStripes = true; return this; } public IXLTable SetShowColumnStripes(Boolean value) { ShowColumnStripes = value; return this; } - public IXLTable SetShowTotalsRow() { ShowTotalsRow = true; return this; } public IXLTable SetShowTotalsRow(Boolean value) { ShowTotalsRow = value; return this; } - public IXLTable SetShowAutoFilter() { ShowAutoFilter = true; return this; } public IXLTable SetShowAutoFilter(Boolean value) { ShowAutoFilter = value; return this; } + public IXLTable SetEmphasizeFirstColumn() + { + EmphasizeFirstColumn = true; + return this; + } + public IXLTable SetEmphasizeFirstColumn(Boolean value) + { + EmphasizeFirstColumn = value; + return this; + } + + public IXLTable SetEmphasizeLastColumn() + { + EmphasizeLastColumn = true; + return this; + } + + public IXLTable SetEmphasizeLastColumn(Boolean value) + { + EmphasizeLastColumn = value; + return this; + } + + public IXLTable SetShowRowStripes() + { + ShowRowStripes = true; + return this; + } + + public IXLTable SetShowRowStripes(Boolean value) + { + ShowRowStripes = value; + return this; + } + + public IXLTable SetShowColumnStripes() + { + ShowColumnStripes = true; + return this; + } + + public IXLTable SetShowColumnStripes(Boolean value) + { + ShowColumnStripes = value; + return this; + } + + public IXLTable SetShowTotalsRow() + { + ShowTotalsRow = true; + return this; + } + + public IXLTable SetShowTotalsRow(Boolean value) + { + ShowTotalsRow = value; + return this; + } + + public IXLTable SetShowAutoFilter() + { + ShowAutoFilter = true; + return this; + } + + public IXLTable SetShowAutoFilter(Boolean value) + { + ShowAutoFilter = value; + return this; + } IXLRangeColumn IXLTable.FirstColumn() @@ -443,5 +427,75 @@ { return LastColumnUsed(); } + + #endregion + + private void InitializeValues() + { + ShowRowStripes = true; + ShowAutoFilter = true; + Theme = XLTableTheme.TableStyleLight9; + } + + private void AddToTables(XLRange range, Boolean addToTables) + { + if (addToTables) + { + _uniqueNames = new HashSet(); + Int32 co = 1; + foreach (IXLCell c in range.Row(1).Cells()) + { + if (StringExtensions.IsNullOrWhiteSpace(((XLCell)c).InnerText)) + c.Value = GetUniqueName("Column" + co.ToStringLookup()); + _uniqueNames.Add(c.GetString()); + co++; + } + Worksheet.Tables.Add(this); + } + } + + private String GetUniqueName(String originalName) + { + String name = originalName; + if (_uniqueNames.Contains(name)) + { + Int32 i = 1; + name = originalName + i.ToStringLookup(); + while (_uniqueNames.Contains(name)) + { + i++; + name = originalName + i.ToStringLookup(); + } + } + + _uniqueNames.Add(name); + return name; + } + + public Int32 GetFieldIndex(String name) + { + if (_fieldNames.ContainsKey(name)) + return _fieldNames[name].Index; + else + { + var headersRow = HeadersRow(); + Int32 cellCount = headersRow.CellCount(); + for (Int32 cellPos = 1; cellPos <= cellCount; cellPos++) + { + if (headersRow.Cell(cellPos).GetString().Equals(name)) + { + if (_fieldNames.ContainsKey(name)) + throw new ArgumentException("The header row contains more than one field name '" + name + + "'."); + else + _fieldNames.Add(name, Field(cellPos - 1)); + } + } + if (_fieldNames.ContainsKey(name)) + return _fieldNames[name].Index; + else + throw new ArgumentOutOfRangeException("The header row doesn't contain field name '" + name + "'."); + } + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTables.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTables.cs index fdd0bb8..3f8dacc 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTables.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTables.cs @@ -1,36 +1,42 @@ using System; using System.Collections.Generic; +using System.Linq; namespace ClosedXML.Excel { - public class XLTables: IXLTables + using System.Collections; + + public class XLTables : IXLTables { - private Dictionary tables = new Dictionary(); + private readonly Dictionary _tables = new Dictionary(); + + #region IXLTables Members + public IEnumerator GetEnumerator() { - return tables.Values.GetEnumerator(); + return _tables.Values.GetEnumerator(); } - System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() + IEnumerator IEnumerable.GetEnumerator() { return GetEnumerator(); } public void Add(IXLTable table) { - tables.Add(table.Name, table); + _tables.Add(table.Name, table); } - //public IXLTable Table(Int32 index) - //{ - // return tables[index]; - //} + public IXLTable Table(Int32 index) + { + return _tables.ElementAt(index).Value; + } public IXLTable Table(String name) { - return tables[name]; + return _tables[name]; } - + #endregion } -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index 01503a9..c67ae8a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -199,7 +199,7 @@ string reference = dTable.Reference.Value; var xlTable = ws.Range(reference).CreateTable(dTable.Name); if (dTable.TotalsRowCount != null && dTable.TotalsRowCount.Value > 0) - ((XLTable) xlTable).m_showTotalsRow = true; + ((XLTable) xlTable)._showTotalsRow = true; if (dTable.TableStyleInfo != null) { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index 24cded0..33af857 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -599,6 +599,10 @@ public IXLSheetView SheetView { get; private set; } public IXLTables Tables { get; private set; } + public IXLTable Table(Int32 index) + { + return Tables.Table(index); + } public IXLTable Table(String name) { return Tables.Table(name); @@ -661,10 +665,10 @@ table.ShowColumnStripes = t.ShowColumnStripes; table.ShowAutoFilter = t.ShowAutoFilter; table.Theme = t.Theme; - table.m_showTotalsRow = t.ShowTotalsRow; - table.m_uniqueNames.Clear(); + table._showTotalsRow = t.ShowTotalsRow; + table._uniqueNames.Clear(); - t.m_uniqueNames.ForEach(n => table.m_uniqueNames.Add(n)); + t._uniqueNames.ForEach(n => table._uniqueNames.Add(n)); Int32 fieldCount = t.ColumnCount(); for (Int32 f = 0; f < fieldCount; f++) {