diff --git a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj index 3f57037..5e59216 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj @@ -119,7 +119,6 @@ - diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs index af52236..8bc0903 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs @@ -182,8 +182,8 @@ Boolean HasRichText { get; } Boolean IsMerged(); - Boolean IsUsed(); - Boolean IsUsed(Boolean includeFormats); + Boolean IsEmpty(); + Boolean IsEmpty(Boolean includeFormats); IXLCell CellAbove(); IXLCell CellAbove(Int32 step); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index 270141f..55cd13f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -99,10 +99,7 @@ if (HasRichText) return _richText.ToString(); - if (StringExtensions.IsNullOrWhiteSpace(_cellValue)) - return FormulaA1; - - return _cellValue; + return StringExtensions.IsNullOrWhiteSpace(_cellValue) ? FormulaA1 : _cellValue; } } @@ -295,7 +292,7 @@ } if (_worksheet.Workbook.WorksheetsInternal.Any( - w => w.Name.ToLower().Equals(sName.ToLower())) + w => String.Compare(w.Name, sName, true)==0) && ExcelHelper.IsValidA1Address(cAddress) ) return _worksheet.Workbook.Worksheet(sName).Cell(cAddress).Value; @@ -317,23 +314,18 @@ return TimeSpan.Parse(_cellValue); } - if (_richText == null) - return _cellValue; - - return _richText.ToString(); + return _richText == null ? _cellValue : _richText.ToString(); } set { FormulaA1 = String.Empty; - if (!SetEnumerable(value)) - { - if (!SetRange(value)) - { - if (!SetRichText(value)) - SetValue(value); - } - } + if (SetEnumerable(value)) return; + + if (SetRange(value)) return; + + if (!SetRichText(value)) + SetValue(value); } } @@ -395,11 +387,10 @@ isDataTable = true; if (!hasTitles) { - foreach (DataColumn column in ((DataRow)m).Table.Columns) + foreach (string fieldName in from DataColumn column in ((DataRow)m).Table.Columns select StringExtensions.IsNullOrWhiteSpace(column.Caption) + ? column.ColumnName + : column.Caption) { - string fieldName = StringExtensions.IsNullOrWhiteSpace(column.Caption) - ? column.ColumnName - : column.Caption; SetValue(fieldName, fRo, co); co++; } @@ -574,106 +565,105 @@ get { return _dataType; } set { - if (_dataType != value) + if (_dataType == value) return; + + if (_richText != null) { - if (_richText != null) + _cellValue = _richText.ToString(); + _richText = null; + } + + if (_cellValue.Length > 0) + { + if (value == XLCellValues.Boolean) { - _cellValue = _richText.ToString(); - _richText = null; + bool bTest; + if (Boolean.TryParse(_cellValue, out bTest)) + _cellValue = bTest ? "1" : "0"; + else + _cellValue = _cellValue == "0" || String.IsNullOrEmpty(_cellValue) ? "0" : "1"; } - - if (_cellValue.Length > 0) + else if (value == XLCellValues.DateTime) { - if (value == XLCellValues.Boolean) + DateTime dtTest; + double dblTest; + if (DateTime.TryParse(_cellValue, out dtTest)) + _cellValue = dtTest.ToOADate().ToString(); + else if (Double.TryParse(_cellValue, out dblTest)) + _cellValue = dblTest.ToString(); + else { - bool bTest; - if (Boolean.TryParse(_cellValue, out bTest)) - _cellValue = bTest ? "1" : "0"; - else - _cellValue = _cellValue == "0" || String.IsNullOrEmpty(_cellValue) ? "0" : "1"; + throw new ArgumentException( + string.Format( + "Cannot set data type to DateTime because '{0}' is not recognized as a date.", + _cellValue)); } - else if (value == XLCellValues.DateTime) - { - DateTime dtTest; - double dblTest; - if (DateTime.TryParse(_cellValue, out dtTest)) - _cellValue = dtTest.ToOADate().ToString(); - else if (Double.TryParse(_cellValue, out dblTest)) - _cellValue = dblTest.ToString(); - else - { - throw new ArgumentException( - string.Format( - "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) + { + Style.NumberFormat.NumberFormatId = _cellValue.Contains('.') ? 22 : 14; + } + } + else if (value == XLCellValues.TimeSpan) + { + TimeSpan tsTest; + if (TimeSpan.TryParse(_cellValue, out tsTest)) + { + _cellValue = tsTest.ToString(); if (Style.NumberFormat.Format == String.Empty && Style.NumberFormat.NumberFormatId == 0) - { - Style.NumberFormat.NumberFormatId = _cellValue.Contains('.') ? 22 : 14; - } - } - else if (value == XLCellValues.TimeSpan) - { - TimeSpan tsTest; - if (TimeSpan.TryParse(_cellValue, out tsTest)) - { - _cellValue = tsTest.ToString(); - if (Style.NumberFormat.Format == String.Empty && Style.NumberFormat.NumberFormatId == 0) - Style.NumberFormat.NumberFormatId = 46; - } - else - { - try - { - _cellValue = (DateTime.FromOADate(Double.Parse(_cellValue)) - BaseDate).ToString(); - } - catch - { - throw new ArgumentException( - string.Format( - "Cannot set data type to TimeSpan because '{0}' is not recognized as a TimeSpan.", - _cellValue)); - } - } - } - else if (value == XLCellValues.Number) - { - double dTest; - if (Double.TryParse(_cellValue, out dTest)) - _cellValue = Double.Parse(_cellValue).ToString(); - else - { - throw new ArgumentException( - string.Format( - "Cannot set data type to Number because '{0}' is not recognized as a number.", - _cellValue)); - } + Style.NumberFormat.NumberFormatId = 46; } else { - var formatCodes = GetFormatCodes(); - if (_dataType == XLCellValues.Boolean) - _cellValue = (_cellValue != "0").ToString(); - else if (_dataType == XLCellValues.TimeSpan) - _cellValue = TimeSpan.Parse(_cellValue).ToString(); - else if (_dataType == XLCellValues.Number) + try { - string format = Style.NumberFormat.NumberFormatId > 0 ? formatCodes[Style.NumberFormat.NumberFormatId] : Style.NumberFormat.Format; - - if (!StringExtensions.IsNullOrWhiteSpace(format) && format != "@") - _cellValue = Double.Parse(_cellValue).ToString(format); + _cellValue = (DateTime.FromOADate(Double.Parse(_cellValue)) - BaseDate).ToString(); } - else if (_dataType == XLCellValues.DateTime) + catch { - string format = Style.NumberFormat.NumberFormatId > 0 ? formatCodes[Style.NumberFormat.NumberFormatId] : Style.NumberFormat.Format; - _cellValue = DateTime.FromOADate(Double.Parse(_cellValue)).ToString(format); + throw new ArgumentException( + string.Format( + "Cannot set data type to TimeSpan because '{0}' is not recognized as a TimeSpan.", + _cellValue)); } } } + else if (value == XLCellValues.Number) + { + double dTest; + if (Double.TryParse(_cellValue, out dTest)) + _cellValue = Double.Parse(_cellValue).ToString(); + else + { + throw new ArgumentException( + string.Format( + "Cannot set data type to Number because '{0}' is not recognized as a number.", + _cellValue)); + } + } + else + { + var formatCodes = GetFormatCodes(); + if (_dataType == XLCellValues.Boolean) + _cellValue = (_cellValue != "0").ToString(); + else if (_dataType == XLCellValues.TimeSpan) + _cellValue = TimeSpan.Parse(_cellValue).ToString(); + else if (_dataType == XLCellValues.Number) + { + string format = Style.NumberFormat.NumberFormatId > 0 ? formatCodes[Style.NumberFormat.NumberFormatId] : Style.NumberFormat.Format; - _dataType = value; + if (!StringExtensions.IsNullOrWhiteSpace(format) && format != "@") + _cellValue = Double.Parse(_cellValue).ToString(format); + } + else if (_dataType == XLCellValues.DateTime) + { + string format = Style.NumberFormat.NumberFormatId > 0 ? formatCodes[Style.NumberFormat.NumberFormatId] : Style.NumberFormat.Format; + _cellValue = DateTime.FromOADate(Double.Parse(_cellValue)).ToString(format); + } + } } + + _dataType = value; } } @@ -764,14 +754,13 @@ _worksheet.Hyperlinks.Add(_hyperlink); - if (!SettingHyperlink) - { - if (Style.Font.FontColor.Equals(_worksheet.Style.Font.FontColor)) - Style.Font.FontColor = XLColor.FromTheme(XLThemeColor.Hyperlink); + if (SettingHyperlink) return; - if (Style.Font.Underline == _worksheet.Style.Font.Underline) - Style.Font.Underline = XLFontUnderlineValues.Single; - } + if (Style.Font.FontColor.Equals(_worksheet.Style.Font.FontColor)) + Style.Font.FontColor = XLColor.FromTheme(XLThemeColor.Hyperlink); + + if (Style.Font.Underline == _worksheet.Style.Font.Underline) + Style.Font.Underline = XLFontUnderlineValues.Single; } } @@ -855,15 +844,20 @@ return Worksheet.Internals.MergedRanges.Any(AsRange().Intersects); } - public Boolean IsUsed() + public Boolean IsEmpty() { - return IsUsed(false); + return IsEmpty(false); } - public Boolean IsUsed(Boolean includeFormats) + public Boolean IsEmpty(Boolean includeFormats) { - return !StringExtensions.IsNullOrWhiteSpace(InnerText) || - (includeFormats && (!Style.Equals(Worksheet.Style) || IsMerged())); + if (InnerText.Length > 0) + return false; + + if (includeFormats) + return Style.Equals(Worksheet.Style) && !IsMerged(); + + return true; } #endregion @@ -1001,7 +995,7 @@ private void SetValue(object value) { FormulaA1 = String.Empty; - string val = value.ToString(); + string val = value == null ? String.Empty : value.ToString(); _richText = null; if (val.Length == 0) { @@ -1336,172 +1330,97 @@ internal void ShiftFormulaRows(XLRange shiftedRange, int rowsShifted) { - if (!StringExtensions.IsNullOrWhiteSpace(FormulaA1)) + if (StringExtensions.IsNullOrWhiteSpace(FormulaA1)) return; + + string value = ">" + _formulaA1 + "<"; + + var regex = A1SimpleRegex; + + var sb = new StringBuilder(); + int lastIndex = 0; + + String shiftedWsName = shiftedRange.Worksheet.Name; + foreach (Match match in regex.Matches(value).Cast()) { - string value = ">" + _formulaA1 + "<"; - - var regex = A1SimpleRegex; - - var sb = new StringBuilder(); - int lastIndex = 0; - - foreach (Match match in regex.Matches(value).Cast()) + string matchString = match.Value; + int matchIndex = match.Index; + if (value.Substring(0, matchIndex).CharCount('"') % 2 == 0) { - string matchString = match.Value; - int matchIndex = match.Index; - if (value.Substring(0, matchIndex).CharCount('"') % 2 == 0) - { // Check that the match is not between quotes - sb.Append(value.Substring(lastIndex, matchIndex - lastIndex)); - string sheetName; - bool useSheetName = false; - if (matchString.Contains('!')) - { - sheetName = matchString.Substring(0, matchString.IndexOf('!')); - if (sheetName[0] == '\'') - sheetName = sheetName.Substring(1, sheetName.Length - 2); - useSheetName = true; - } - else - sheetName = _worksheet.Name; + sb.Append(value.Substring(lastIndex, matchIndex - lastIndex)); + string sheetName; + bool useSheetName = false; + if (matchString.Contains('!')) + { + sheetName = matchString.Substring(0, matchString.IndexOf('!')); + if (sheetName[0] == '\'') + sheetName = sheetName.Substring(1, sheetName.Length - 2); + useSheetName = true; + } + else + sheetName = _worksheet.Name; - if (sheetName.ToLower().Equals(shiftedRange.Worksheet.Name.ToLower())) + if (String.Compare(sheetName, shiftedWsName, true)==0) + { + string rangeAddress = matchString.Substring(matchString.IndexOf('!') + 1); + if (!A1ColumnRegex.IsMatch(rangeAddress)) { - string rangeAddress = matchString.Substring(matchString.IndexOf('!') + 1); - if (!A1ColumnRegex.IsMatch(rangeAddress)) + var matchRange = _worksheet.Workbook.Worksheet(sheetName).Range(rangeAddress); + if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= + matchRange.RangeAddress.LastAddress.RowNumber + && + shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= + matchRange.RangeAddress.FirstAddress.ColumnNumber + && + shiftedRange.RangeAddress.LastAddress.ColumnNumber >= + matchRange.RangeAddress.LastAddress.ColumnNumber) { - var matchRange = _worksheet.Workbook.Worksheet(sheetName).Range(rangeAddress); - if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= - matchRange.RangeAddress.LastAddress.RowNumber - && - shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= - matchRange.RangeAddress.FirstAddress.ColumnNumber - && - shiftedRange.RangeAddress.LastAddress.ColumnNumber >= - matchRange.RangeAddress.LastAddress.ColumnNumber) + if (A1RowRegex.IsMatch(rangeAddress)) { - if (A1RowRegex.IsMatch(rangeAddress)) + var rows = rangeAddress.Split(':'); + string row1String = rows[0]; + string row2String = rows[1]; + string row1; + if (row1String[0] == '$') { - var rows = rangeAddress.Split(':'); - string row1String = rows[0]; - string row2String = rows[1]; - string row1; - if (row1String[0] == '$') - { - row1 = "$" + - (Int32.Parse(row1String.Substring(1)) + rowsShifted).ToStringLookup(); - } - else - row1 = (Int32.Parse(row1String) + rowsShifted).ToStringLookup(); - - string row2; - if (row2String[0] == '$') - { - row2 = "$" + - (Int32.Parse(row2String.Substring(1)) + rowsShifted).ToStringLookup(); - } - else - row2 = (Int32.Parse(row2String) + rowsShifted).ToStringLookup(); - - 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) - { - if (rangeAddress.Contains(':')) - { - if (useSheetName) - { - sb.Append(String.Format("'{0}'!{1}:{2}", - sheetName, - new XLAddress(_worksheet, - matchRange.RangeAddress. - FirstAddress.RowNumber + - rowsShifted, - matchRange.RangeAddress. - FirstAddress.ColumnLetter, - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn), - 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(_worksheet, - matchRange.RangeAddress. - FirstAddress.RowNumber + - rowsShifted, - matchRange.RangeAddress. - FirstAddress.ColumnLetter, - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn), - new XLAddress(_worksheet, - matchRange.RangeAddress. - LastAddress.RowNumber + - rowsShifted, - matchRange.RangeAddress. - LastAddress.ColumnLetter, - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - } - else - { - if (useSheetName) - { - sb.Append(String.Format("'{0}'!{1}", - sheetName, - 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(_worksheet, - matchRange.RangeAddress. - FirstAddress.RowNumber + - rowsShifted, - matchRange.RangeAddress. - FirstAddress.ColumnLetter, - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn))); - } - } + row1 = "$" + + (Int32.Parse(row1String.Substring(1)) + rowsShifted).ToStringLookup(); } else + row1 = (Int32.Parse(row1String) + rowsShifted).ToStringLookup(); + + string row2; + if (row2String[0] == '$') + { + row2 = "$" + + (Int32.Parse(row2String.Substring(1)) + rowsShifted).ToStringLookup(); + } + else + row2 = (Int32.Parse(row2String) + rowsShifted).ToStringLookup(); + + 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) + { + if (rangeAddress.Contains(':')) { if (useSheetName) { sb.Append(String.Format("'{0}'!{1}:{2}", sheetName, - matchRange.RangeAddress.FirstAddress, + new XLAddress(_worksheet, + matchRange.RangeAddress. + FirstAddress.RowNumber + + rowsShifted, + matchRange.RangeAddress. + FirstAddress.ColumnLetter, + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn), new XLAddress(_worksheet, matchRange.RangeAddress. LastAddress.RowNumber + @@ -1516,7 +1435,16 @@ else { sb.Append(String.Format("{0}:{1}", - matchRange.RangeAddress.FirstAddress, + new XLAddress(_worksheet, + matchRange.RangeAddress. + FirstAddress.RowNumber + + rowsShifted, + matchRange.RangeAddress. + FirstAddress.ColumnLetter, + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn), new XLAddress(_worksheet, matchRange.RangeAddress. LastAddress.RowNumber + @@ -1529,9 +1457,73 @@ LastAddress.FixedColumn))); } } + else + { + if (useSheetName) + { + sb.Append(String.Format("'{0}'!{1}", + sheetName, + 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(_worksheet, + matchRange.RangeAddress. + FirstAddress.RowNumber + + rowsShifted, + matchRange.RangeAddress. + FirstAddress.ColumnLetter, + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn))); + } + } } else - sb.Append(matchString); + { + if (useSheetName) + { + sb.Append(String.Format("'{0}'!{1}:{2}", + sheetName, + matchRange.RangeAddress.FirstAddress, + 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, + new XLAddress(_worksheet, + matchRange.RangeAddress. + LastAddress.RowNumber + + rowsShifted, + matchRange.RangeAddress. + LastAddress.ColumnLetter, + matchRange.RangeAddress. + LastAddress.FixedRow, + matchRange.RangeAddress. + LastAddress.FixedColumn))); + } + } } else sb.Append(matchString); @@ -1540,200 +1532,126 @@ sb.Append(matchString); } else - sb.Append(value.Substring(lastIndex, matchIndex - lastIndex + matchString.Length)); - lastIndex = matchIndex + matchString.Length; + sb.Append(matchString); } - - if (lastIndex < value.Length) - sb.Append(value.Substring(lastIndex)); - - string retVal = sb.ToString(); - _formulaA1 = retVal.Substring(1, retVal.Length - 2); + else + sb.Append(value.Substring(lastIndex, matchIndex - lastIndex + matchString.Length)); + lastIndex = matchIndex + matchString.Length; } + + if (lastIndex < value.Length) + sb.Append(value.Substring(lastIndex)); + + string retVal = sb.ToString(); + _formulaA1 = retVal.Substring(1, retVal.Length - 2); } internal void ShiftFormulaColumns(XLRange shiftedRange, int columnsShifted) { - if (!StringExtensions.IsNullOrWhiteSpace(FormulaA1)) + if (StringExtensions.IsNullOrWhiteSpace(FormulaA1)) return; + + string value = ">" + _formulaA1 + "<"; + + var regex = A1SimpleRegex; + + var sb = new StringBuilder(); + int lastIndex = 0; + + foreach (Match match in regex.Matches(value).Cast()) { - string value = ">" + _formulaA1 + "<"; - - var regex = A1SimpleRegex; - - var sb = new StringBuilder(); - int lastIndex = 0; - - foreach (Match match in regex.Matches(value).Cast()) + string matchString = match.Value; + int matchIndex = match.Index; + if (value.Substring(0, matchIndex).CharCount('"') % 2 == 0) { - string matchString = match.Value; - int matchIndex = match.Index; - if (value.Substring(0, matchIndex).CharCount('"') % 2 == 0) - { // Check that the match is not between quotes - sb.Append(value.Substring(lastIndex, matchIndex - lastIndex)); - string sheetName; - bool useSheetName = false; - if (matchString.Contains('!')) - { - sheetName = matchString.Substring(0, matchString.IndexOf('!')); - if (sheetName[0] == '\'') - sheetName = sheetName.Substring(1, sheetName.Length - 2); - useSheetName = true; - } - else - sheetName = _worksheet.Name; + sb.Append(value.Substring(lastIndex, matchIndex - lastIndex)); + string sheetName; + bool useSheetName = false; + if (matchString.Contains('!')) + { + sheetName = matchString.Substring(0, matchString.IndexOf('!')); + if (sheetName[0] == '\'') + sheetName = sheetName.Substring(1, sheetName.Length - 2); + useSheetName = true; + } + else + sheetName = _worksheet.Name; - if (sheetName.ToLower().Equals(shiftedRange.Worksheet.Name.ToLower())) + if (String.Compare(sheetName, shiftedRange.Worksheet.Name, true) == 0) + { + string rangeAddress = matchString.Substring(matchString.IndexOf('!') + 1); + if (!A1RowRegex.IsMatch(rangeAddress)) { - string rangeAddress = matchString.Substring(matchString.IndexOf('!') + 1); - if (!A1RowRegex.IsMatch(rangeAddress)) + var matchRange = _worksheet.Workbook.Worksheet(sheetName).Range(rangeAddress); + if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= + matchRange.RangeAddress.LastAddress.ColumnNumber + && + shiftedRange.RangeAddress.FirstAddress.RowNumber <= + matchRange.RangeAddress.FirstAddress.RowNumber + && + shiftedRange.RangeAddress.LastAddress.RowNumber >= + matchRange.RangeAddress.LastAddress.RowNumber) { - var matchRange = _worksheet.Workbook.Worksheet(sheetName).Range(rangeAddress); - if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= - matchRange.RangeAddress.LastAddress.ColumnNumber - && - shiftedRange.RangeAddress.FirstAddress.RowNumber <= - matchRange.RangeAddress.FirstAddress.RowNumber - && - shiftedRange.RangeAddress.LastAddress.RowNumber >= - matchRange.RangeAddress.LastAddress.RowNumber) + if (A1ColumnRegex.IsMatch(rangeAddress)) { - if (A1ColumnRegex.IsMatch(rangeAddress)) + var columns = rangeAddress.Split(':'); + string column1String = columns[0]; + string column2String = columns[1]; + string column1; + if (column1String[0] == '$') { - var columns = rangeAddress.Split(':'); - string column1String = columns[0]; - string column2String = columns[1]; - string column1; - if (column1String[0] == '$') - { - column1 = "$" + - ExcelHelper.GetColumnLetterFromNumber( - ExcelHelper.GetColumnNumberFromLetter( - column1String.Substring(1)) + columnsShifted); - } - else - { - column1 = - ExcelHelper.GetColumnLetterFromNumber( - ExcelHelper.GetColumnNumberFromLetter(column1String) + - columnsShifted); - } - - string column2; - if (column2String[0] == '$') - { - column2 = "$" + - ExcelHelper.GetColumnLetterFromNumber( - ExcelHelper.GetColumnNumberFromLetter( - column2String.Substring(1)) + columnsShifted); - } - else - { - column2 = - ExcelHelper.GetColumnLetterFromNumber( - ExcelHelper.GetColumnNumberFromLetter(column2String) + - columnsShifted); - } - - 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) - { - if (rangeAddress.Contains(':')) - { - if (useSheetName) - { - sb.Append(String.Format("'{0}'!{1}:{2}", - sheetName, - new XLAddress(_worksheet, - matchRange.RangeAddress. - FirstAddress.RowNumber, - matchRange.RangeAddress. - FirstAddress.ColumnNumber + - columnsShifted, - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn), - 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(_worksheet, - matchRange.RangeAddress. - FirstAddress.RowNumber, - matchRange.RangeAddress. - FirstAddress.ColumnNumber + - columnsShifted, - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn), - new XLAddress(_worksheet, - matchRange.RangeAddress. - LastAddress.RowNumber, - matchRange.RangeAddress. - LastAddress.ColumnNumber + - columnsShifted, - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - } - else - { - if (useSheetName) - { - sb.Append(String.Format("'{0}'!{1}", - sheetName, - 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(_worksheet, - matchRange.RangeAddress. - FirstAddress.RowNumber, - matchRange.RangeAddress. - FirstAddress.ColumnNumber + - columnsShifted, - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn))); - } - } + column1 = "$" + + ExcelHelper.GetColumnLetterFromNumber( + ExcelHelper.GetColumnNumberFromLetter( + column1String.Substring(1)) + columnsShifted); } else { + column1 = + ExcelHelper.GetColumnLetterFromNumber( + ExcelHelper.GetColumnNumberFromLetter(column1String) + + columnsShifted); + } + + string column2; + if (column2String[0] == '$') + { + column2 = "$" + + ExcelHelper.GetColumnLetterFromNumber( + ExcelHelper.GetColumnNumberFromLetter( + column2String.Substring(1)) + columnsShifted); + } + else + { + column2 = + ExcelHelper.GetColumnLetterFromNumber( + ExcelHelper.GetColumnNumberFromLetter(column2String) + + columnsShifted); + } + + 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) + { + if (rangeAddress.Contains(':')) + { if (useSheetName) { sb.Append(String.Format("'{0}'!{1}:{2}", sheetName, - matchRange.RangeAddress.FirstAddress, + new XLAddress(_worksheet, + matchRange.RangeAddress. + FirstAddress.RowNumber, + matchRange.RangeAddress. + FirstAddress.ColumnNumber + + columnsShifted, + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn), new XLAddress(_worksheet, matchRange.RangeAddress. LastAddress.RowNumber, @@ -1748,7 +1666,16 @@ else { sb.Append(String.Format("{0}:{1}", - matchRange.RangeAddress.FirstAddress, + new XLAddress(_worksheet, + matchRange.RangeAddress. + FirstAddress.RowNumber, + matchRange.RangeAddress. + FirstAddress.ColumnNumber + + columnsShifted, + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn), new XLAddress(_worksheet, matchRange.RangeAddress. LastAddress.RowNumber, @@ -1761,9 +1688,73 @@ LastAddress.FixedColumn))); } } + else + { + if (useSheetName) + { + sb.Append(String.Format("'{0}'!{1}", + sheetName, + 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(_worksheet, + matchRange.RangeAddress. + FirstAddress.RowNumber, + matchRange.RangeAddress. + FirstAddress.ColumnNumber + + columnsShifted, + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn))); + } + } } else - sb.Append(matchString); + { + if (useSheetName) + { + sb.Append(String.Format("'{0}'!{1}:{2}", + sheetName, + matchRange.RangeAddress.FirstAddress, + 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, + new XLAddress(_worksheet, + matchRange.RangeAddress. + LastAddress.RowNumber, + matchRange.RangeAddress. + LastAddress.ColumnNumber + + columnsShifted, + matchRange.RangeAddress. + LastAddress.FixedRow, + matchRange.RangeAddress. + LastAddress.FixedColumn))); + } + } } else sb.Append(matchString); @@ -1772,17 +1763,19 @@ sb.Append(matchString); } else - sb.Append(value.Substring(lastIndex, matchIndex - lastIndex + matchString.Length)); - lastIndex = matchIndex + matchString.Length; + sb.Append(matchString); } - - if (lastIndex < value.Length) - sb.Append(value.Substring(lastIndex)); - - string retVal = sb.ToString(); - - _formulaA1 = retVal.Substring(1, retVal.Length - 2); + else + sb.Append(value.Substring(lastIndex, matchIndex - lastIndex + matchString.Length)); + lastIndex = matchIndex + matchString.Length; } + + if (lastIndex < value.Length) + sb.Append(value.Substring(lastIndex)); + + string retVal = sb.ToString(); + + _formulaA1 = retVal.Substring(1, retVal.Length - 2); } // -- diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCellCollectionOLD.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCellCollectionOLD.cs deleted file mode 100644 index 53eec57..0000000 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCellCollectionOLD.cs +++ /dev/null @@ -1,144 +0,0 @@ -using System; -using System.Collections; -using System.Collections.Generic; -using System.Linq; - -namespace ClosedXML.Excel -{ - internal class XLCellCollectionOLD : IDictionary - { - #region Private fields - private readonly Dictionary m_dictionary = new Dictionary(); - private readonly Dictionary m_deleted = new Dictionary(); - #endregion - public Dictionary Deleted - { - get { return m_deleted; } - } - - public void Add(IXLAddress key, XLCell value) - { - if (m_deleted.ContainsKey(key)) - { - m_deleted.Remove(key); - } - - m_dictionary.Add(key, value); - } - - public bool ContainsKey(IXLAddress key) - { - return m_dictionary.ContainsKey(key); - } - - public ICollection Keys - { - get { return m_dictionary.Keys; } - } - - public bool Remove(IXLAddress key) - { - if (!m_deleted.ContainsKey(key)) - { - m_deleted.Add(key, m_dictionary[key]); - } - - return m_dictionary.Remove(key); - } - - public bool TryGetValue(IXLAddress key, out XLCell value) - { - return m_dictionary.TryGetValue(key, out value); - } - - public ICollection Values - { - get { return m_dictionary.Values; } - } - - public XLCell this[IXLAddress key] - { - get { return m_dictionary[key]; } - set { m_dictionary[key] = value; } - } - - public void Add(KeyValuePair item) - { - if (m_deleted.ContainsKey(item.Key)) - { - m_deleted.Remove(item.Key); - } - m_dictionary.Add(item.Key, item.Value); - } - - public void Clear() - { - foreach (var kp in m_dictionary) - { - if (!m_deleted.ContainsKey(kp.Key)) - { - m_deleted.Add(kp.Key, kp.Value); - } - } - m_dictionary.Clear(); - } - - public bool Contains(KeyValuePair item) - { - return m_dictionary.Contains(item); - } - - public void CopyTo(KeyValuePair[] array, int arrayIndex) - { - throw new NotImplementedException(); - } - - public int Count - { - get { return m_dictionary.Count; } - } - - public bool IsReadOnly - { - get { return false; } - } - - public bool Remove(KeyValuePair item) - { - if (!m_deleted.ContainsKey(item.Key)) - { - m_deleted.Add(item.Key, m_dictionary[item.Key]); - } - - return m_dictionary.Remove(item.Key); - } - - public IEnumerator> GetEnumerator() - { - return m_dictionary.GetEnumerator(); - } - - IEnumerator IEnumerable.GetEnumerator() - { - return m_dictionary.GetEnumerator(); - } - - public void RemoveAll() - { - RemoveAll(c => true); - } - - public void RemoveAll(Func predicate) - { - foreach (var kp in m_dictionary.Values.Where(predicate).Select(c => c)) - { - if (!m_deleted.ContainsKey(kp.Address)) - { - m_deleted.Add(kp.Address, kp); - } - } - - m_dictionary.RemoveAll(predicate); - } - } -} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs index db27ad7..fe531d7 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs @@ -4,6 +4,8 @@ namespace ClosedXML.Excel { + using System.Linq; + internal class XLCells : IXLCells, IXLStylized, IEnumerable { #region Fields @@ -51,10 +53,9 @@ tmpRange.LastAddress.RowNumber, tmpRange.LastAddress.ColumnNumber); - foreach (XLSheetPoint a in addressList) + foreach (XLSheetPoint a in addressList.Where(a => !hash.Contains(a))) { - if (!hash.Contains(a)) - hash.Add(a); + hash.Add(a); } } else @@ -83,14 +84,9 @@ if (_usedCellsOnly) { - foreach (var cir in cellsInRanges) + foreach (var cell in cellsInRanges.SelectMany(cir => cir.Value.Select(a => cir.Key.Internals.CellsCollection.GetCell(a)).Where(cell => cell != null && !cell.IsEmpty(_includeFormats)))) { - foreach (XLSheetPoint a in cir.Value) - { - var cell = cir.Key.Internals.CellsCollection.GetCell(a); - if (cell != null && cell.IsUsed(_includeFormats)) - yield return cell; - } + yield return cell; } } else diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs index 87a0496..4516fd6 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs @@ -28,7 +28,7 @@ IsReference = xlColumnParameters.IsReference; if (IsReference) - (Worksheet).RangeShiftedColumns += WorksheetRangeShiftedColumns; + Worksheet.RangeShiftedColumns += WorksheetRangeShiftedColumns; else { _style = new XLStyle(this, xlColumnParameters.DefaultStyle); @@ -67,9 +67,9 @@ if (Worksheet.Internals.CellsCollection.ColumnsUsed.ContainsKey(column)) maxRow = Worksheet.Internals.CellsCollection.MaxRowInColumn(column); - if ((Worksheet).Internals.RowsCollection.Count > 0) + if (Worksheet.Internals.RowsCollection.Count > 0) { - Int32 maxInCollection = (Worksheet).Internals.RowsCollection.Keys.Max(); + Int32 maxInCollection = Worksheet.Internals.RowsCollection.Keys.Max(); if (maxInCollection > maxRow) maxRow = maxInCollection; } @@ -93,7 +93,7 @@ set { if (IsReference) - (Worksheet).Internals.ColumnsCollection[ColumnNumber()].InnerStyle = value; + Worksheet.Internals.ColumnsCollection[ColumnNumber()].InnerStyle = value; else _style = new XLStyle(this, value); } @@ -101,11 +101,11 @@ public Boolean Collapsed { - get { return IsReference ? (Worksheet).Internals.ColumnsCollection[ColumnNumber()].Collapsed : _collapsed; } + get { return IsReference ? Worksheet.Internals.ColumnsCollection[ColumnNumber()].Collapsed : _collapsed; } set { if (IsReference) - (Worksheet).Internals.ColumnsCollection[ColumnNumber()].Collapsed = value; + Worksheet.Internals.ColumnsCollection[ColumnNumber()].Collapsed = value; else _collapsed = value; } @@ -122,7 +122,7 @@ set { if (IsReference) - (Worksheet).Internals.ColumnsCollection[ColumnNumber()].Width = value; + Worksheet.Internals.ColumnsCollection[ColumnNumber()].Width = value; else _width = value; } @@ -132,14 +132,14 @@ { int columnNumber = ColumnNumber(); AsRange().Delete(XLShiftDeletedCells.ShiftCellsLeft); - (Worksheet).Internals.ColumnsCollection.Remove(columnNumber); + Worksheet.Internals.ColumnsCollection.Remove(columnNumber); var columnsToMove = new List(); columnsToMove.AddRange( - (Worksheet).Internals.ColumnsCollection.Where(c => c.Key > columnNumber).Select(c => c.Key)); + Worksheet.Internals.ColumnsCollection.Where(c => c.Key > columnNumber).Select(c => c.Key)); foreach (int column in columnsToMove.OrderBy(c => c)) { - (Worksheet).Internals.ColumnsCollection.Add(column - 1, (Worksheet).Internals.ColumnsCollection[column]); - (Worksheet).Internals.ColumnsCollection.Remove(column); + Worksheet.Internals.ColumnsCollection.Add(column - 1, Worksheet.Internals.ColumnsCollection[column]); + Worksheet.Internals.ColumnsCollection.Remove(column); } } @@ -178,7 +178,7 @@ set { if (IsReference) - (Worksheet).Internals.ColumnsCollection[ColumnNumber()].Style = value; + Worksheet.Internals.ColumnsCollection[ColumnNumber()].Style = value; else { _style = new XLStyle(this, value); @@ -192,10 +192,10 @@ maxRow = Worksheet.Internals.CellsCollection.MaxRowInColumn(column); } - if ((Worksheet).Internals.RowsCollection.Count > 0) + if (Worksheet.Internals.RowsCollection.Count > 0) { - Int32 minInCollection = (Worksheet).Internals.RowsCollection.Keys.Min(); - Int32 maxInCollection = (Worksheet).Internals.RowsCollection.Keys.Max(); + Int32 minInCollection = Worksheet.Internals.RowsCollection.Keys.Min(); + Int32 maxInCollection = Worksheet.Internals.RowsCollection.Keys.Max(); if (minInCollection < minRow) minRow = minInCollection; if (maxInCollection > maxRow) @@ -214,7 +214,7 @@ public new IXLColumns InsertColumnsAfter(Int32 numberOfColumns) { int columnNum = ColumnNumber(); - (Worksheet).Internals.ColumnsCollection.ShiftColumnsRight(columnNum + 1, numberOfColumns); + Worksheet.Internals.ColumnsCollection.ShiftColumnsRight(columnNum + 1, numberOfColumns); var range = (XLRange)Worksheet.Column(columnNum).AsRange(); range.InsertColumnsAfter(true, numberOfColumns); return Worksheet.Columns(columnNum + 1, columnNum + numberOfColumns); @@ -223,7 +223,7 @@ public new IXLColumns InsertColumnsBefore(Int32 numberOfColumns) { int columnNum = ColumnNumber(); - (Worksheet).Internals.ColumnsCollection.ShiftColumnsRight(columnNum, numberOfColumns); + Worksheet.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. var range = (XLRange)Worksheet.Column(columnNum).AsRange(); @@ -444,7 +444,7 @@ set { if (IsReference) - (Worksheet).Internals.ColumnsCollection[ColumnNumber()].IsHidden = value; + Worksheet.Internals.ColumnsCollection[ColumnNumber()].IsHidden = value; else _isHidden = value; } @@ -452,18 +452,18 @@ public Int32 OutlineLevel { - get { return IsReference ? (Worksheet).Internals.ColumnsCollection[ColumnNumber()].OutlineLevel : _outlineLevel; } + get { return IsReference ? Worksheet.Internals.ColumnsCollection[ColumnNumber()].OutlineLevel : _outlineLevel; } set { if (value < 0 || value > 8) throw new ArgumentOutOfRangeException("value", "Outline level must be between 0 and 8."); if (IsReference) - (Worksheet).Internals.ColumnsCollection[ColumnNumber()].OutlineLevel = value; + Worksheet.Internals.ColumnsCollection[ColumnNumber()].OutlineLevel = value; else { - (Worksheet).IncrementColumnOutline(value); - (Worksheet).DecrementColumnOutline(_outlineLevel); + Worksheet.IncrementColumnOutline(value); + Worksheet.DecrementColumnOutline(_outlineLevel); _outlineLevel = value; } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumnCollection.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumnCollection.cs index 3eaff28..2959177 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumnCollection.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumnCollection.cs @@ -8,76 +8,76 @@ { public void ShiftColumnsRight(Int32 startingColumn, Int32 columnsToShift) { - foreach (var ro in dictionary.Keys.Where(k => k >= startingColumn).OrderByDescending(k => k)) + foreach (var ro in _dictionary.Keys.Where(k => k >= startingColumn).OrderByDescending(k => k)) { - var columnToMove = dictionary[ro]; + var columnToMove = _dictionary[ro]; Int32 newColumn = ro + columnsToShift; if (newColumn <= ExcelHelper.MaxColumnNumber) { - dictionary.Add(newColumn, new XLColumn(columnToMove)); + _dictionary.Add(newColumn, new XLColumn(columnToMove)); } - dictionary.Remove(ro); + _dictionary.Remove(ro); } } - private Dictionary dictionary = new Dictionary(); + private readonly Dictionary _dictionary = new Dictionary(); public void Add(int key, XLColumn value) { - dictionary.Add(key, value); + _dictionary.Add(key, value); } public bool ContainsKey(int key) { - return dictionary.ContainsKey(key); + return _dictionary.ContainsKey(key); } public ICollection Keys { - get { return dictionary.Keys; } + get { return _dictionary.Keys; } } public bool Remove(int key) { - return dictionary.Remove(key); + return _dictionary.Remove(key); } public bool TryGetValue(int key, out XLColumn value) { - return dictionary.TryGetValue(key, out value); + return _dictionary.TryGetValue(key, out value); } public ICollection Values { - get { return dictionary.Values; } + get { return _dictionary.Values; } } public XLColumn this[int key] { get { - return dictionary[key]; + return _dictionary[key]; } set { - dictionary[key] = value; + _dictionary[key] = value; } } public void Add(KeyValuePair item) { - dictionary.Add(item.Key, item.Value); + _dictionary.Add(item.Key, item.Value); } public void Clear() { - dictionary.Clear(); + _dictionary.Clear(); } public bool Contains(KeyValuePair item) { - return dictionary.Contains(item); + return _dictionary.Contains(item); } public void CopyTo(KeyValuePair[] array, int arrayIndex) @@ -87,7 +87,7 @@ public int Count { - get { return dictionary.Count; } + get { return _dictionary.Count; } } public bool IsReadOnly @@ -97,22 +97,22 @@ public bool Remove(KeyValuePair item) { - return dictionary.Remove(item.Key); + return _dictionary.Remove(item.Key); } public IEnumerator> GetEnumerator() { - return dictionary.GetEnumerator(); + return _dictionary.GetEnumerator(); } System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() { - return dictionary.GetEnumerator(); + return _dictionary.GetEnumerator(); } public void RemoveAll(Func predicate) { - dictionary.RemoveAll(predicate); + _dictionary.RemoveAll(predicate); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs index e0371c1..13b2ff1 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs @@ -8,13 +8,13 @@ internal class XLColumns : IXLColumns, IXLStylized { - private readonly List columns = new List(); - private readonly XLWorksheet worksheet; + private readonly List _columns = new List(); + private readonly XLWorksheet _worksheet; internal IXLStyle style; public XLColumns(XLWorksheet worksheet) { - this.worksheet = worksheet; + _worksheet = worksheet; style = new XLStyle(this, XLWorkbook.DefaultStyle); } @@ -23,7 +23,7 @@ public IEnumerator GetEnumerator() { var retList = new List(); - columns.ForEach(retList.Add); + _columns.ForEach(retList.Add); return retList.GetEnumerator(); } @@ -39,11 +39,11 @@ { style = new XLStyle(this, value); - if (worksheet != null) - worksheet.Style = value; + if (_worksheet != null) + _worksheet.Style = value; else { - foreach (XLColumn column in columns) + foreach (XLColumn column in _columns) column.Style = value; } } @@ -53,27 +53,26 @@ { set { - columns.ForEach(c => c.Width = value); + _columns.ForEach(c => c.Width = value); - if (worksheet != null) - { - worksheet.ColumnWidth = value; - worksheet.Internals.ColumnsCollection.ForEach(c => c.Value.Width = value); - } + if (_worksheet == null) return; + + _worksheet.ColumnWidth = value; + _worksheet.Internals.ColumnsCollection.ForEach(c => c.Value.Width = value); } } public void Delete() { - if (worksheet != null) + if (_worksheet != null) { - worksheet.Internals.ColumnsCollection.Clear(); - worksheet.Internals.CellsCollection.Clear(); + _worksheet.Internals.ColumnsCollection.Clear(); + _worksheet.Internals.CellsCollection.Clear(); } else { var toDelete = new Dictionary>(); - foreach (XLColumn c in columns) + foreach (XLColumn c in _columns) { if (!toDelete.ContainsKey(c.Worksheet)) toDelete.Add(c.Worksheet, new List()); @@ -91,48 +90,48 @@ public IXLColumns AdjustToContents() { - columns.ForEach(c => c.AdjustToContents()); + _columns.ForEach(c => c.AdjustToContents()); return this; } public IXLColumns AdjustToContents(Int32 startRow) { - columns.ForEach(c => c.AdjustToContents(startRow)); + _columns.ForEach(c => c.AdjustToContents(startRow)); return this; } public IXLColumns AdjustToContents(Int32 startRow, Int32 endRow) { - columns.ForEach(c => c.AdjustToContents(startRow, endRow)); + _columns.ForEach(c => c.AdjustToContents(startRow, endRow)); return this; } public IXLColumns AdjustToContents(Double minWidth, Double maxWidth) { - columns.ForEach(c => c.AdjustToContents(minWidth, maxWidth)); + _columns.ForEach(c => c.AdjustToContents(minWidth, maxWidth)); return this; } public IXLColumns AdjustToContents(Int32 startRow, Double minWidth, Double maxWidth) { - columns.ForEach(c => c.AdjustToContents(startRow, minWidth, maxWidth)); + _columns.ForEach(c => c.AdjustToContents(startRow, minWidth, maxWidth)); return this; } public IXLColumns AdjustToContents(Int32 startRow, Int32 endRow, Double minWidth, Double maxWidth) { - columns.ForEach(c => c.AdjustToContents(startRow, endRow, minWidth, maxWidth)); + _columns.ForEach(c => c.AdjustToContents(startRow, endRow, minWidth, maxWidth)); return this; } public void Hide() { - columns.ForEach(c => c.Hide()); + _columns.ForEach(c => c.Hide()); } public void Unhide() { - columns.ForEach(c => c.Unhide()); + _columns.ForEach(c => c.Unhide()); } public void Group() @@ -152,33 +151,33 @@ public void Group(Boolean collapse) { - columns.ForEach(c => c.Group(collapse)); + _columns.ForEach(c => c.Group(collapse)); } public void Group(Int32 outlineLevel, Boolean collapse) { - columns.ForEach(c => c.Group(outlineLevel, collapse)); + _columns.ForEach(c => c.Group(outlineLevel, collapse)); } public void Ungroup(Boolean ungroupFromAll) { - columns.ForEach(c => c.Ungroup(ungroupFromAll)); + _columns.ForEach(c => c.Ungroup(ungroupFromAll)); } public void Collapse() { - columns.ForEach(c => c.Collapse()); + _columns.ForEach(c => c.Collapse()); } public void Expand() { - columns.ForEach(c => c.Expand()); + _columns.ForEach(c => c.Expand()); } public IXLCells Cells() { var cells = new XLCells(false, false); - foreach (XLColumn container in columns) + foreach (XLColumn container in _columns) cells.Add(container.RangeAddress); return cells; } @@ -186,7 +185,7 @@ public IXLCells CellsUsed() { var cells = new XLCells(true, false); - foreach (XLColumn container in columns) + foreach (XLColumn container in _columns) cells.Add(container.RangeAddress); return cells; } @@ -194,7 +193,7 @@ public IXLCells CellsUsed(Boolean includeStyles) { var cells = new XLCells(true, includeStyles); - foreach (XLColumn container in columns) + foreach (XLColumn container in _columns) cells.Add(container.RangeAddress); return cells; } @@ -204,14 +203,14 @@ /// public IXLColumns AddVerticalPageBreaks() { - foreach (XLColumn col in columns) + foreach (XLColumn col in _columns) col.Worksheet.PageSetup.AddVerticalPageBreak(col.ColumnNumber()); return this; } public IXLColumns SetDataType(XLCellValues dataType) { - columns.ForEach(c => c.DataType = dataType); + _columns.ForEach(c => c.DataType = dataType); return this; } @@ -225,14 +224,13 @@ { UpdatingStyle = true; yield return style; - if (worksheet != null) - yield return worksheet.Style; + if (_worksheet != null) + yield return _worksheet.Style; else { - foreach (XLColumn col in columns) + foreach (IXLStyle s in _columns.SelectMany(col => col.Styles)) { - foreach (IXLStyle s in col.Styles) - yield return s; + yield return s; } } UpdatingStyle = false; @@ -261,12 +259,12 @@ public void Add(XLColumn column) { - columns.Add(column); + _columns.Add(column); } public void CollapseOnly() { - columns.ForEach(c => c.Collapsed = true); + _columns.ForEach(c => c.Collapsed = true); } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/SheetPoint.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/SheetPoint.cs deleted file mode 100644 index 4b0d1b7..0000000 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/SheetPoint.cs +++ /dev/null @@ -1,99 +0,0 @@ -using System; -using System.Diagnostics; - -namespace ClosedXML.Excel -{ - /// - /// Lightweight struct for work with sheet coordinates - /// - public struct SheetPoint : IEquatable - { - #region Static - /// - /// Singleton instance - /// -// ReSharper disable RedundantDefaultFieldInitializer - public static readonly SheetPoint Empty = new SheetPoint(); -// ReSharper restore RedundantDefaultFieldInitializer - #endregion - #region Private fields - [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private readonly int m_row; - [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private readonly int m_column; - #endregion - #region Constructor - public SheetPoint(int row, int column) - { - #region Check - if (row < 0) - { - throw new ArgumentOutOfRangeException("row", "Must be more than 0"); - } - if (column < 0) - { - throw new ArgumentOutOfRangeException("column", "Must be more than 0"); - } - #endregion - m_row = row; - m_column = column; - } - #endregion - #region Public properties - public int RowNumber - { - [DebuggerStepThrough] - get { return m_row; } - } - public int ColumnNumber - { - [DebuggerStepThrough] - get { return m_column; } - } - #endregion - #region Public methods - public bool Equals(SheetPoint other) - { - return other.m_row == m_row && other.m_column == m_column; - } - public override bool Equals(object obj) - { - if (ReferenceEquals(null, obj)) - { - return false; - } - if (obj.GetType() != typeof (SheetPoint)) - { - return false; - } - return Equals((SheetPoint) obj); - } - public override int GetHashCode() - { - unchecked - { - return (m_row*397) ^ m_column; - } - } - public override string ToString() - { - return ToStringA1(); - } - public string ToStringA1() - { - return string.Format("{0}{1}", ExcelHelper.GetColumnLetterFromNumber(m_column), m_row); - } - #endregion - #region Internal methods - internal bool Equals(XLAddress other) - { - if (ReferenceEquals(other,null)) - { - return false; - } - return m_row == other.RowNumber && m_column == other.ColumnNumber; - } - #endregion - } - -} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/SheetRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/SheetRange.cs deleted file mode 100644 index 689df68..0000000 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/SheetRange.cs +++ /dev/null @@ -1,162 +0,0 @@ -using System; -using System.Diagnostics; - -namespace ClosedXML.Excel -{ - /// - /// Lightweight struct for work with sheet coordinate range - /// - public struct SheetRange : IEquatable - { - #region Static - /// - /// Singleton instance - /// -// ReSharper disable RedundantDefaultFieldInitializer - public static readonly SheetRange Empty = new SheetRange(); -// ReSharper restore RedundantDefaultFieldInitializer - #endregion - #region Private fields - [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private readonly SheetPoint m_begin; - [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private readonly SheetPoint m_end; - #endregion - #region Constructor - public SheetRange(int beginRow, int beginColumn, int endRow, int endColumn) - : this(new SheetPoint(beginRow, beginColumn), new SheetPoint(endRow, endColumn)) - { - } - public SheetRange(SheetPoint begin, SheetPoint end) - { - #region Check - if (begin.RowNumber > end.RowNumber) - { - throw new ArgumentOutOfRangeException("begin", "Row part of begin coordinate must be less or equal Row part coordinate of end"); - } - if (begin.ColumnNumber > end.ColumnNumber) - { - throw new ArgumentOutOfRangeException("begin", "Column part of begin coordinate must be less or equal Column part coordinate of end"); - } - #endregion - m_begin = begin; - m_end = end; - } - #endregion - #region Public properties - public SheetPoint FirstAddress - { - [DebuggerStepThrough] - get { return m_begin; } - } - public SheetPoint LastAddress - { - [DebuggerStepThrough] - get { return m_end; } - } - - public bool IsOneCell - { - get { return m_begin.Equals(m_end); } - } - public int RowCount - { - [DebuggerStepThrough] - get { return m_end.RowNumber - m_begin.RowNumber + 1; } - } - public int ColumnCount - { - [DebuggerStepThrough] - get { return m_end.ColumnNumber - m_begin.ColumnNumber + 1; } - } - public int Count - { - [DebuggerStepThrough] - get { return RowCount*ColumnCount; } - } - #endregion - #region Public methods - public bool Equals(SheetRange other) - { - return other.m_begin.Equals(m_begin) && other.m_end.Equals(m_end); - } - public bool Intersects(SheetRange range) - { - return !(range.FirstAddress.ColumnNumber > LastAddress.ColumnNumber - || range.LastAddress.ColumnNumber < FirstAddress.ColumnNumber - || range.FirstAddress.RowNumber > LastAddress.RowNumber - || range.LastAddress.RowNumber < FirstAddress.RowNumber - ); - } - - public Boolean Contains(SheetRange range) - { - return Contains(range.FirstAddress, range.LastAddress); - } - public bool Contains(SheetPoint first, SheetPoint last) - { - return Contains(first) && Contains(last); - } - public bool Contains(SheetPoint point) - { - return FirstAddress.RowNumber <= point.RowNumber && point.RowNumber <= LastAddress.RowNumber && - FirstAddress.ColumnNumber <= point.ColumnNumber && point.ColumnNumber <= LastAddress.ColumnNumber; - } - - public override bool Equals(object obj) - { - if (ReferenceEquals(null, obj)) - { - return false; - } - if (obj.GetType() != typeof (SheetRange)) - { - return false; - } - return Equals((SheetRange) obj); - } - public override int GetHashCode() - { - unchecked - { - return (m_begin.GetHashCode()*397) ^ m_end.GetHashCode(); - } - } - - internal string ToStringA1() - { - return IsOneCell ? m_begin.ToStringA1() : string.Format("{0}:{1}", m_begin.ToStringA1(), m_end.ToStringA1()); - } - public override string ToString() - { - return ToStringA1(); - } - #endregion - #region Internal methods - internal bool Equals(XLRangeAddress other) - { - if (ReferenceEquals(other, null)) - { - return false; - } - return m_begin.Equals(other.FirstAddress) && m_end.Equals(other.LastAddress); - } - - internal bool Intersects(XLRangeAddress range) - { - if (ReferenceEquals(range, null)) - { - return false; - } - return !(range.FirstAddress.ColumnNumber > LastAddress.ColumnNumber - || range.LastAddress.ColumnNumber < FirstAddress.ColumnNumber - || range.FirstAddress.RowNumber > LastAddress.RowNumber - || range.LastAddress.RowNumber < FirstAddress.RowNumber - ); - } - - #endregion - - - } -} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/XLSheetPoint.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/XLSheetPoint.cs index a66ae8f..e1beef7 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/XLSheetPoint.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/XLSheetPoint.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; namespace ClosedXML.Excel { @@ -13,8 +10,8 @@ Column = column; } - public Int32 Row; - public Int32 Column; + public readonly Int32 Row; + public readonly Int32 Column; public bool Equals(XLSheetPoint other) { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/XLSheetRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/XLSheetRange.cs index 23ccd25..2845011 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/XLSheetRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/XLSheetRange.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; namespace ClosedXML.Excel { @@ -13,8 +10,8 @@ LastPoint = lastPoint; } - public XLSheetPoint FirstPoint; - public XLSheetPoint LastPoint; + public readonly XLSheetPoint FirstPoint; + public readonly XLSheetPoint LastPoint; public bool Equals(XLSheetRange other) { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidation.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidation.cs index 1bf37bc..5f64087 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidation.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidation.cs @@ -2,57 +2,29 @@ namespace ClosedXML.Excel { - internal class XLDataValidation: IXLDataValidation + internal class XLDataValidation : IXLDataValidation { - private XLWorksheet worksheet; - public XLDataValidation(IXLRanges ranges, XLWorksheet worksheet) + public XLDataValidation(IXLRanges ranges) { - this.Ranges = ranges; - this.AllowedValues = XLAllowedValues.AnyValue; - this.IgnoreBlanks = true; + Ranges = ranges; + AllowedValues = XLAllowedValues.AnyValue; + IgnoreBlanks = true; ShowErrorMessage = true; ShowInputMessage = true; InCellDropdown = true; Operator = XLOperator.Between; - this.worksheet = worksheet; } - public XLDataValidation(IXLDataValidation dataValidation, XLWorksheet worksheet) + public XLDataValidation(IXLDataValidation dataValidation) { - this.worksheet = worksheet; - this.CopyFrom(dataValidation); + CopyFrom(dataValidation); } + #region IXLDataValidation Members + public IXLRanges Ranges { get; set; } - - public void Delete() - { - throw new NotImplementedException(); - //foreach (var dv in worksheet.DataValidations) - //{ - //} - } - public void CopyFrom(IXLDataValidation dataValidation) - { - if (Ranges == null) - Ranges = new XLRanges(); - //dataValidation.Ranges.ForEach(r => Ranges.Add(r)); - IgnoreBlanks = dataValidation.IgnoreBlanks; - InCellDropdown = dataValidation.InCellDropdown; - ShowErrorMessage = dataValidation.ShowErrorMessage; - ShowInputMessage = dataValidation.ShowInputMessage; - InputTitle = dataValidation.InputTitle; - InputMessage = dataValidation.InputMessage; - ErrorTitle = dataValidation.ErrorTitle; - ErrorMessage = dataValidation.ErrorMessage; - ErrorStyle = dataValidation.ErrorStyle; - AllowedValues = dataValidation.AllowedValues; - Operator = dataValidation.Operator; - MinValue = dataValidation.MinValue; - MaxValue = dataValidation.MaxValue; - } public Boolean IgnoreBlanks { get; set; } public Boolean InCellDropdown { get; set; } public Boolean ShowInputMessage { get; set; } @@ -65,11 +37,12 @@ public XLAllowedValues AllowedValues { get; set; } public XLOperator Operator { get; set; } - public String Value + public String Value { get { return MinValue; } set { MinValue = value; } } + public String MinValue { get; set; } public String MaxValue { get; set; } @@ -81,6 +54,7 @@ return new XLWholeNumberCriteria(this); } } + public XLDecimalCriteria Decimal { get @@ -89,6 +63,7 @@ return new XLDecimalCriteria(this); } } + public XLDateCriteria Date { get @@ -120,19 +95,22 @@ { List(list, true); } + public void List(String list, Boolean inCellDropdown) { AllowedValues = XLAllowedValues.List; Value = list; } + public void List(IXLRange range) { List(range, true); } + public void List(IXLRange range, Boolean inCellDropdown) { AllowedValues = XLAllowedValues.List; - Value = String.Format("'{0}'!{1}", ((XLRange)range).Worksheet.Name, range.RangeAddress.ToString()); + Value = String.Format("'{0}'!{1}", ((XLRange)range).Worksheet.Name, range.RangeAddress); } public void Custom(String customValidation) @@ -140,5 +118,28 @@ AllowedValues = XLAllowedValues.Custom; Value = customValidation; } + + #endregion + + public void CopyFrom(IXLDataValidation dataValidation) + { + if (Ranges == null) + Ranges = new XLRanges(); + //dataValidation.Ranges.ForEach(r => Ranges.Add(r)); + + IgnoreBlanks = dataValidation.IgnoreBlanks; + InCellDropdown = dataValidation.InCellDropdown; + ShowErrorMessage = dataValidation.ShowErrorMessage; + ShowInputMessage = dataValidation.ShowInputMessage; + InputTitle = dataValidation.InputTitle; + InputMessage = dataValidation.InputMessage; + ErrorTitle = dataValidation.ErrorTitle; + ErrorMessage = dataValidation.ErrorMessage; + ErrorStyle = dataValidation.ErrorStyle; + AllowedValues = dataValidation.AllowedValues; + Operator = dataValidation.Operator; + MinValue = dataValidation.MinValue; + MaxValue = dataValidation.MaxValue; + } } -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidations.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidations.cs index bb9be8e..8fde067 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidations.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidations.cs @@ -3,39 +3,40 @@ namespace ClosedXML.Excel { - internal class XLDataValidations: IXLDataValidations + using System.Collections; + using System.Linq; + + internal class XLDataValidations : IXLDataValidations { - private List dataValidations = new List(); + private readonly List _dataValidations = new List(); + + #region IXLDataValidations Members + public void Add(IXLDataValidation dataValidation) { - dataValidations.Add(dataValidation); - } - - public void Delete(IXLDataValidation dataValidation) - { - dataValidations.RemoveAll(dv=>dv.Ranges.Equals(dataValidation.Ranges)); + _dataValidations.Add(dataValidation); } public IEnumerator GetEnumerator() { - return dataValidations.GetEnumerator(); + return _dataValidations.GetEnumerator(); } - System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() + IEnumerator IEnumerable.GetEnumerator() { return GetEnumerator(); } public Boolean ContainsSingle(IXLRange range) { - foreach (var dv in dataValidations) - { - if (dv.Ranges.Count == 1 && dv.Ranges.Contains(range)) - return true; - } - return false; + return _dataValidations.Any(dv => dv.Ranges.Count == 1 && dv.Ranges.Contains(range)); } - + #endregion + + public void Delete(IXLDataValidation dataValidation) + { + _dataValidations.RemoveAll(dv => dv.Ranges.Equals(dataValidation.Ranges)); + } } -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDateCriteria.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDateCriteria.cs index 35d88de..792e6b0 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDateCriteria.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDateCriteria.cs @@ -8,7 +8,6 @@ public XLDateCriteria(IXLDataValidation dataValidation) : base(dataValidation) { - } public void EqualTo(DateTime value) @@ -16,44 +15,49 @@ dataValidation.Value = value.ToOADate().ToString(CultureInfo.InvariantCulture); dataValidation.Operator = XLOperator.EqualTo; } + public void NotEqualTo(DateTime value) { dataValidation.Value = value.ToOADate().ToString(CultureInfo.InvariantCulture); dataValidation.Operator = XLOperator.NotEqualTo; } + public void GreaterThan(DateTime value) { dataValidation.Value = value.ToOADate().ToString(CultureInfo.InvariantCulture); dataValidation.Operator = XLOperator.GreaterThan; } + public void LessThan(DateTime value) { dataValidation.Value = value.ToOADate().ToString(CultureInfo.InvariantCulture); dataValidation.Operator = XLOperator.LessThan; } + public void EqualOrGreaterThan(DateTime value) { dataValidation.Value = value.ToOADate().ToString(CultureInfo.InvariantCulture); dataValidation.Operator = XLOperator.EqualOrGreaterThan; } + public void EqualOrLessThan(DateTime value) { dataValidation.Value = value.ToOADate().ToString(CultureInfo.InvariantCulture); dataValidation.Operator = XLOperator.EqualOrLessThan; } + public void Between(DateTime minValue, DateTime maxValue) { - dataValidation.MinValue = minValue.ToOADate().ToString(CultureInfo.InvariantCulture); + dataValidation.MinValue = minValue.ToOADate().ToString(CultureInfo.InvariantCulture); dataValidation.MaxValue = maxValue.ToOADate().ToString(CultureInfo.InvariantCulture); dataValidation.Operator = XLOperator.Between; } + public void NotBetween(DateTime minValue, DateTime maxValue) { dataValidation.MinValue = minValue.ToOADate().ToString(CultureInfo.InvariantCulture); dataValidation.MaxValue = maxValue.ToOADate().ToString(CultureInfo.InvariantCulture); dataValidation.Operator = XLOperator.NotBetween; } - - } -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDecimalCriteria.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDecimalCriteria.cs index 02f1c5e..93f190b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDecimalCriteria.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDecimalCriteria.cs @@ -7,7 +7,6 @@ public XLDecimalCriteria(IXLDataValidation dataValidation) : base(dataValidation) { - } public void EqualTo(Double value) @@ -15,44 +14,49 @@ dataValidation.Value = value.ToString(); dataValidation.Operator = XLOperator.EqualTo; } + public void NotEqualTo(Double value) { dataValidation.Value = value.ToString(); dataValidation.Operator = XLOperator.NotEqualTo; } + public void GreaterThan(Double value) { dataValidation.Value = value.ToString(); dataValidation.Operator = XLOperator.GreaterThan; } + public void LessThan(Double value) { dataValidation.Value = value.ToString(); dataValidation.Operator = XLOperator.LessThan; } + public void EqualOrGreaterThan(Double value) { dataValidation.Value = value.ToString(); dataValidation.Operator = XLOperator.EqualOrGreaterThan; } + public void EqualOrLessThan(Double value) { dataValidation.Value = value.ToString(); dataValidation.Operator = XLOperator.EqualOrLessThan; } + public void Between(Double minValue, Double maxValue) { dataValidation.MinValue = minValue.ToString(); dataValidation.MaxValue = maxValue.ToString(); dataValidation.Operator = XLOperator.Between; } + public void NotBetween(Double minValue, Double maxValue) { dataValidation.MinValue = minValue.ToString(); dataValidation.MaxValue = maxValue.ToString(); dataValidation.Operator = XLOperator.NotBetween; } - - } -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLTextLengthCriteria.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLTextLengthCriteria.cs index 6e35427..876b802 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLTextLengthCriteria.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLTextLengthCriteria.cs @@ -7,7 +7,6 @@ public XLTextLengthCriteria(IXLDataValidation dataValidation) : base(dataValidation) { - } public void EqualTo(Int32 value) @@ -15,44 +14,49 @@ dataValidation.Value = value.ToString(); dataValidation.Operator = XLOperator.EqualTo; } + public void NotEqualTo(Int32 value) { dataValidation.Value = value.ToString(); dataValidation.Operator = XLOperator.NotEqualTo; } + public void GreaterThan(Int32 value) { dataValidation.Value = value.ToString(); dataValidation.Operator = XLOperator.GreaterThan; } + public void LessThan(Int32 value) { dataValidation.Value = value.ToString(); dataValidation.Operator = XLOperator.LessThan; } + public void EqualOrGreaterThan(Int32 value) { dataValidation.Value = value.ToString(); dataValidation.Operator = XLOperator.EqualOrGreaterThan; } + public void EqualOrLessThan(Int32 value) { dataValidation.Value = value.ToString(); dataValidation.Operator = XLOperator.EqualOrLessThan; } + public void Between(Int32 minValue, Int32 maxValue) { dataValidation.MinValue = minValue.ToString(); dataValidation.MaxValue = maxValue.ToString(); dataValidation.Operator = XLOperator.Between; } + public void NotBetween(Int32 minValue, Int32 maxValue) { dataValidation.MinValue = minValue.ToString(); dataValidation.MaxValue = maxValue.ToString(); dataValidation.Operator = XLOperator.NotBetween; } - - } -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLTimeCriteria.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLTimeCriteria.cs index 10b88f2..117ee98 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLTimeCriteria.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLTimeCriteria.cs @@ -7,7 +7,6 @@ public XLTimeCriteria(IXLDataValidation dataValidation) : base(dataValidation) { - } private String GetXLTime(TimeSpan value) @@ -20,44 +19,49 @@ dataValidation.Value = GetXLTime(value); dataValidation.Operator = XLOperator.EqualTo; } + public void NotEqualTo(TimeSpan value) { dataValidation.Value = GetXLTime(value); dataValidation.Operator = XLOperator.NotEqualTo; } + public void GreaterThan(TimeSpan value) { dataValidation.Value = GetXLTime(value); dataValidation.Operator = XLOperator.GreaterThan; } + public void LessThan(TimeSpan value) { dataValidation.Value = GetXLTime(value); dataValidation.Operator = XLOperator.LessThan; } + public void EqualOrGreaterThan(TimeSpan value) { dataValidation.Value = GetXLTime(value); dataValidation.Operator = XLOperator.EqualOrGreaterThan; } + public void EqualOrLessThan(TimeSpan value) { dataValidation.Value = GetXLTime(value); dataValidation.Operator = XLOperator.EqualOrLessThan; } + public void Between(TimeSpan minValue, TimeSpan maxValue) { dataValidation.MinValue = GetXLTime(minValue); dataValidation.MaxValue = GetXLTime(maxValue); dataValidation.Operator = XLOperator.Between; } + public void NotBetween(TimeSpan minValue, TimeSpan maxValue) { dataValidation.MinValue = GetXLTime(minValue); dataValidation.MaxValue = GetXLTime(maxValue); dataValidation.Operator = XLOperator.NotBetween; } - - } -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLValidationCriteria.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLValidationCriteria.cs index 6564298..b4e9eb2 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLValidationCriteria.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLValidationCriteria.cs @@ -5,47 +5,57 @@ public abstract class XLValidationCriteria : IXLValidationCriteria { protected IXLDataValidation dataValidation; + internal XLValidationCriteria(IXLDataValidation dataValidation) { this.dataValidation = dataValidation; } + #region IXLValidationCriteria Members + public void EqualTo(String value) { dataValidation.Value = value; dataValidation.Operator = XLOperator.EqualTo; } + public void NotEqualTo(String value) { dataValidation.Value = value; dataValidation.Operator = XLOperator.NotEqualTo; } + public void GreaterThan(String value) { dataValidation.Value = value; dataValidation.Operator = XLOperator.GreaterThan; } + public void LessThan(String value) { dataValidation.Value = value; dataValidation.Operator = XLOperator.LessThan; } + public void EqualOrGreaterThan(String value) { dataValidation.Value = value; dataValidation.Operator = XLOperator.EqualOrGreaterThan; } + public void EqualOrLessThan(String value) { dataValidation.Value = value; dataValidation.Operator = XLOperator.EqualOrLessThan; } + public void Between(String minValue, String maxValue) { dataValidation.MinValue = minValue; dataValidation.MaxValue = maxValue; dataValidation.Operator = XLOperator.Between; } + public void NotBetween(String minValue, String maxValue) { dataValidation.MinValue = minValue; @@ -56,88 +66,108 @@ public void EqualTo(IXLRange range) { - dataValidation.Value = String.Format("'{0}'!{1}", ((XLRange)range).Worksheet.Name, range.RangeAddress.ToString()); + dataValidation.Value = String.Format("'{0}'!{1}", ((XLRange)range).Worksheet.Name, range.RangeAddress); dataValidation.Operator = XLOperator.EqualTo; } + public void NotEqualTo(IXLRange range) { - dataValidation.Value = String.Format("'{0}'!{1}", ((XLRange)range).Worksheet.Name, range.RangeAddress.ToString()); + dataValidation.Value = String.Format("'{0}'!{1}", ((XLRange)range).Worksheet.Name, range.RangeAddress); dataValidation.Operator = XLOperator.NotEqualTo; } + public void GreaterThan(IXLRange range) { - dataValidation.Value = String.Format("'{0}'!{1}", ((XLRange)range).Worksheet.Name, range.RangeAddress.ToString()); + dataValidation.Value = String.Format("'{0}'!{1}", ((XLRange)range).Worksheet.Name, range.RangeAddress); dataValidation.Operator = XLOperator.GreaterThan; } + public void LessThan(IXLRange range) { - dataValidation.Value = String.Format("'{0}'!{1}", ((XLRange)range).Worksheet.Name, range.RangeAddress.ToString()); + dataValidation.Value = String.Format("'{0}'!{1}", ((XLRange)range).Worksheet.Name, range.RangeAddress); dataValidation.Operator = XLOperator.LessThan; } + public void EqualOrGreaterThan(IXLRange range) { - dataValidation.Value = String.Format("'{0}'!{1}", ((XLRange)range).Worksheet.Name, range.RangeAddress.ToString()); + dataValidation.Value = String.Format("'{0}'!{1}", ((XLRange)range).Worksheet.Name, range.RangeAddress); dataValidation.Operator = XLOperator.EqualOrGreaterThan; } + public void EqualOrLessThan(IXLRange range) { - dataValidation.Value = String.Format("'{0}'!{1}", ((XLRange)range).Worksheet.Name, range.RangeAddress.ToString()); + dataValidation.Value = String.Format("'{0}'!{1}", ((XLRange)range).Worksheet.Name, range.RangeAddress); dataValidation.Operator = XLOperator.EqualOrLessThan; } + public void Between(IXLRange minValue, IXLRange maxValue) { - dataValidation.MinValue = String.Format("'{0}'!{1}", ((XLRange)minValue).Worksheet.Name, minValue.RangeAddress.ToString()); - dataValidation.MaxValue = String.Format("'{0}'!{1}", ((XLRange)maxValue).Worksheet.Name, maxValue.RangeAddress.ToString()); + dataValidation.MinValue = String.Format("'{0}'!{1}", ((XLRange)minValue).Worksheet.Name, + minValue.RangeAddress); + dataValidation.MaxValue = String.Format("'{0}'!{1}", ((XLRange)maxValue).Worksheet.Name, + maxValue.RangeAddress); dataValidation.Operator = XLOperator.Between; } + public void NotBetween(IXLRange minValue, IXLRange maxValue) { - dataValidation.MinValue = String.Format("'{0}'!{1}", ((XLRange)minValue).Worksheet.Name, minValue.RangeAddress.ToString()); - dataValidation.MaxValue = String.Format("'{0}'!{1}", ((XLRange)maxValue).Worksheet.Name, maxValue.RangeAddress.ToString()); + dataValidation.MinValue = String.Format("'{0}'!{1}", ((XLRange)minValue).Worksheet.Name, + minValue.RangeAddress); + dataValidation.MaxValue = String.Format("'{0}'!{1}", ((XLRange)maxValue).Worksheet.Name, + maxValue.RangeAddress); dataValidation.Operator = XLOperator.NotBetween; } public void EqualTo(IXLCell cell) { - dataValidation.Value = String.Format("'{0}'!{1}", cell.Worksheet.Name, cell.Address.ToString()); + dataValidation.Value = String.Format("'{0}'!{1}", cell.Worksheet.Name, cell.Address); dataValidation.Operator = XLOperator.EqualTo; } + public void NotEqualTo(IXLCell cell) { - dataValidation.Value = String.Format("'{0}'!{1}", cell.Worksheet.Name, cell.Address.ToString()); + dataValidation.Value = String.Format("'{0}'!{1}", cell.Worksheet.Name, cell.Address); dataValidation.Operator = XLOperator.NotEqualTo; } + public void GreaterThan(IXLCell cell) { - dataValidation.Value = String.Format("'{0}'!{1}", cell.Worksheet.Name, cell.Address.ToString()); + dataValidation.Value = String.Format("'{0}'!{1}", cell.Worksheet.Name, cell.Address); dataValidation.Operator = XLOperator.GreaterThan; } + public void LessThan(IXLCell cell) { - dataValidation.Value = String.Format("'{0}'!{1}", cell.Worksheet.Name, cell.Address.ToString()); + dataValidation.Value = String.Format("'{0}'!{1}", cell.Worksheet.Name, cell.Address); dataValidation.Operator = XLOperator.LessThan; } + public void EqualOrGreaterThan(IXLCell cell) { - dataValidation.Value = String.Format("'{0}'!{1}", cell.Worksheet.Name, cell.Address.ToString()); + dataValidation.Value = String.Format("'{0}'!{1}", cell.Worksheet.Name, cell.Address); dataValidation.Operator = XLOperator.EqualOrGreaterThan; } + public void EqualOrLessThan(IXLCell cell) { - dataValidation.Value = String.Format("'{0}'!{1}", cell.Worksheet.Name, cell.Address.ToString()); + dataValidation.Value = String.Format("'{0}'!{1}", cell.Worksheet.Name, cell.Address); dataValidation.Operator = XLOperator.EqualOrLessThan; } + public void Between(IXLCell minValue, IXLCell maxValue) { - dataValidation.MinValue = String.Format("'{0}'!{1}", minValue.Worksheet.Name, minValue.Address.ToString()); - dataValidation.MaxValue = String.Format("'{0}'!{1}", maxValue.Worksheet.Name, maxValue.Address.ToString()); + dataValidation.MinValue = String.Format("'{0}'!{1}", minValue.Worksheet.Name, minValue.Address); + dataValidation.MaxValue = String.Format("'{0}'!{1}", maxValue.Worksheet.Name, maxValue.Address); dataValidation.Operator = XLOperator.Between; } + public void NotBetween(IXLCell minValue, IXLCell maxValue) { - dataValidation.MinValue = String.Format("'{0}'!{1}", minValue.Worksheet.Name, minValue.Address.ToString()); - dataValidation.MaxValue = String.Format("'{0}'!{1}", maxValue.Worksheet.Name, maxValue.Address.ToString()); + dataValidation.MinValue = String.Format("'{0}'!{1}", minValue.Worksheet.Name, minValue.Address); + dataValidation.MaxValue = String.Format("'{0}'!{1}", maxValue.Worksheet.Name, maxValue.Address); dataValidation.Operator = XLOperator.NotBetween; } + + #endregion } -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs index 96850b8..06a7ebd 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -216,6 +216,10 @@ String ToStringRelative(); String ToStringFixed(); + Boolean IsMerged(); + Boolean IsEmpty(); + Boolean IsEmpty(Boolean includeFormats); + //IXLChart CreateChart(Int32 firstRow, Int32 firstColumn, Int32 lastRow, Int32 lastColumn); } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs index 6bf8e30..b781930 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs @@ -22,8 +22,8 @@ if (!xlRangeParameters.IgnoreEvents) { - (Worksheet).RangeShiftedRows += WorksheetRangeShiftedRows; - (Worksheet).RangeShiftedColumns += WorksheetRangeShiftedColumns; + Worksheet.RangeShiftedRows += WorksheetRangeShiftedRows; + Worksheet.RangeShiftedColumns += WorksheetRangeShiftedColumns; xlRangeParameters.IgnoreEvents = true; } DefaultStyle = new XLStyle(this, xlRangeParameters.DefaultStyle); @@ -138,17 +138,14 @@ { var retVal = new XLRangeColumns(); var columnPairs = columns.Split(','); - foreach (string pair in columnPairs) + foreach (string tPair in columnPairs.Select(pair => pair.Trim())) { - string tPair = pair.Trim(); String firstColumn; String lastColumn; if (tPair.Contains(':') || tPair.Contains('-')) { - if (tPair.Contains('-')) - tPair = tPair.Replace('-', ':'); + string[] columnRange = tPair.Contains('-') ? tPair.Replace('-', ':').Split(':') : tPair.Split(':'); - var columnRange = tPair.Split(':'); firstColumn = columnRange[0]; lastColumn = columnRange[1]; } @@ -245,17 +242,14 @@ { var retVal = new XLRangeRows(); var rowPairs = rows.Split(','); - foreach (string pair in rowPairs) + foreach (string tPair in rowPairs.Select(pair => pair.Trim())) { - string tPair = pair.Trim(); String firstRow; String lastRow; if (tPair.Contains(':') || tPair.Contains('-')) { - if (tPair.Contains('-')) - tPair = tPair.Replace('-', ':'); + string[] rowRange = tPair.Contains('-') ? tPair.Replace('-', ':').Split(':') : tPair.Split(':'); - var rowRange = tPair.Split(':'); firstRow = rowRange[0]; lastRow = rowRange[1]; } @@ -402,9 +396,8 @@ public IXLRange Sort(String columnsToSortBy) { SortColumns.Clear(); - foreach (String coPair in columnsToSortBy.Split(',')) + foreach (string coPairTrimmed in columnsToSortBy.Split(',').Select(coPair => coPair.Trim())) { - String coPairTrimmed = coPair.Trim(); String coString; String order; if (coPairTrimmed.Contains(' ')) @@ -423,7 +416,7 @@ if (!Int32.TryParse(coString, out co)) co = ExcelHelper.GetColumnNumberFromLetter(coString); - SortColumns.Add(co, order.ToUpper().Equals("ASC") ? XLSortOrder.Ascending : XLSortOrder.Descending); + SortColumns.Add(co, String.Compare(order, "ASC", true) == 0 ? XLSortOrder.Ascending : XLSortOrder.Descending); } SortRangeRows(); @@ -433,9 +426,8 @@ public IXLRange Sort(String columnsToSortBy, Boolean matchCase) { SortColumns.Clear(); - foreach (String coPair in columnsToSortBy.Split(',')) + foreach (string coPairTrimmed in columnsToSortBy.Split(',').Select(coPair => coPair.Trim())) { - String coPairTrimmed = coPair.Trim(); String coString; String order; if (coPairTrimmed.Contains(' ')) @@ -454,7 +446,7 @@ if (!Int32.TryParse(coString, out co)) co = ExcelHelper.GetColumnNumberFromLetter(coString); - SortColumns.Add(co, order.ToUpper().Equals("ASC") ? XLSortOrder.Ascending : XLSortOrder.Descending, true, + SortColumns.Add(co, String.Compare(order, "ASC", true) == 0 ? XLSortOrder.Ascending : XLSortOrder.Descending, true, matchCase); } @@ -525,9 +517,8 @@ if (sortOrientation == XLSortOrientation.TopToBottom) return Sort(elementsToSortBy); SortRows.Clear(); - foreach (String roPair in elementsToSortBy.Split(',')) + foreach (string roPairTrimmed in elementsToSortBy.Split(',').Select(roPair => roPair.Trim())) { - String roPairTrimmed = roPair.Trim(); String roString; String order; if (roPairTrimmed.Contains(' ')) @@ -544,7 +535,7 @@ Int32 ro = Int32.Parse(roString); - SortRows.Add(ro, order.ToUpper().Equals("ASC") ? XLSortOrder.Ascending : XLSortOrder.Descending); + SortRows.Add(ro, String.Compare(order, "ASC",true)==0 ? XLSortOrder.Ascending : XLSortOrder.Descending); } SortRangeColumns(); @@ -556,9 +547,8 @@ if (sortOrientation == XLSortOrientation.TopToBottom) return Sort(elementsToSortBy, matchCase); SortRows.Clear(); - foreach (String roPair in elementsToSortBy.Split(',')) + foreach (string roPairTrimmed in elementsToSortBy.Split(',').Select(roPair => roPair.Trim())) { - String roPairTrimmed = roPair.Trim(); String roString; String order; if (roPairTrimmed.Contains(' ')) @@ -575,7 +565,7 @@ Int32 ro = Int32.Parse(roString); - SortRows.Add(ro, order.ToUpper().Equals("ASC") ? XLSortOrder.Ascending : XLSortOrder.Descending, true, + SortRows.Add(ro, String.Compare(order, "ASC", true) == 0 ? XLSortOrder.Ascending : XLSortOrder.Descending, true, matchCase); } @@ -777,10 +767,7 @@ public XLRangeColumn FirstColumnUsed(bool includeFormats) { var firstCellUsed = FirstCellUsed(includeFormats); - if (firstCellUsed == null) - return null; - - return Column(firstCellUsed.Address.ColumnNumber); + return firstCellUsed == null ? null : Column(firstCellUsed.Address.ColumnNumber); } public XLRangeColumn LastColumnUsed() @@ -791,10 +778,7 @@ public XLRangeColumn LastColumnUsed(bool includeFormats) { var lastCellUsed = LastCellUsed(includeFormats); - if (lastCellUsed == null) - return null; - - return Column(lastCellUsed.Address.ColumnNumber); + return lastCellUsed == null ? null : Column(lastCellUsed.Address.ColumnNumber); } public XLRangeRow FirstRow() @@ -815,10 +799,7 @@ public XLRangeRow LastRowUsed(bool includeFormats) { var lastCellUsed = LastCellUsed(includeFormats); - if (lastCellUsed == null) - return null; - - return Row(lastCellUsed.Address.RowNumber); + return lastCellUsed == null ? null : Row(lastCellUsed.Address.RowNumber); } public XLRangeRow FirstRowUsed() @@ -829,10 +810,7 @@ public XLRangeRow FirstRowUsed(bool includeFormats) { var firstCellUsed = FirstCellUsed(includeFormats); - if (firstCellUsed == null) - return null; - - return Row(firstCellUsed.Address.RowNumber); + return firstCellUsed == null ? null : Row(firstCellUsed.Address.RowNumber); } public XLRangeRow Row(Int32 row) @@ -948,10 +926,9 @@ RangeAddress.FirstAddress.RowNumber + squareSide - 1, RangeAddress.FirstAddress.ColumnNumber + squareSide - 1); - foreach (IXLRange merge in Worksheet.Internals.MergedRanges) + foreach (IXLRange merge in Worksheet.Internals.MergedRanges.Where(Contains)) { - if (Contains(merge)) - merge.RangeAddress.LastAddress = rngToTranspose.Cell(merge.ColumnCount(), merge.RowCount()).Address; + merge.RangeAddress.LastAddress = rngToTranspose.Cell(merge.ColumnCount(), merge.RowCount()).Address; } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs index 1b4dc9c..28252f4 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs @@ -7,18 +7,18 @@ internal class XLRangeAddress : IXLRangeAddress { #region Private fields - [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private XLAddress m_firstAddress; - [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private XLAddress m_lastAddress; + + [DebuggerBrowsable(DebuggerBrowsableState.Never)] private XLAddress _firstAddress; + [DebuggerBrowsable(DebuggerBrowsableState.Never)] private XLAddress _lastAddress; + #endregion + #region Constructor + public XLRangeAddress(XLAddress firstAddress, XLAddress lastAddress) { if (firstAddress.Worksheet != lastAddress.Worksheet) - { throw new ArgumentException("First and last addresses must be in the same worksheet"); - } Worksheet = firstAddress.Worksheet; FirstAddress = firstAddress; @@ -27,23 +27,17 @@ public XLRangeAddress(XLWorksheet worksheet, String rangeAddress) { - String addressToUse; - if (rangeAddress.Contains("!")) - { - addressToUse = rangeAddress.Substring(rangeAddress.IndexOf("!") + 1); - } - else - { - addressToUse = rangeAddress; - } + string addressToUse = rangeAddress.Contains("!") + ? rangeAddress.Substring(rangeAddress.IndexOf("!") + 1) + : rangeAddress; XLAddress firstAddress; XLAddress lastAddress; if (addressToUse.Contains(':')) { - String[] arrRange = addressToUse.Split(':'); - var firstPart = arrRange[0]; - var secondPart = arrRange[1]; + var arrRange = addressToUse.Split(':'); + string firstPart = arrRange[0]; + string secondPart = arrRange[1]; firstAddress = XLAddress.Create(worksheet, firstPart); lastAddress = XLAddress.Create(worksheet, secondPart); } @@ -56,23 +50,40 @@ LastAddress = lastAddress; Worksheet = worksheet; } + #endregion + #region Public properties + public XLWorksheet Worksheet { get; internal set; } - IXLWorksheet IXLRangeAddress.Worksheet { get { return Worksheet; } } - + public XLAddress FirstAddress { get { if (IsInvalid) - { throw new Exception("Range is invalid."); - } - return m_firstAddress; + return _firstAddress; } - set { m_firstAddress = value; } + set { _firstAddress = value; } + } + + public XLAddress LastAddress + { + get + { + if (IsInvalid) + throw new Exception("Range is an invalid state."); + + return _lastAddress; + } + set { _lastAddress = value; } + } + + IXLWorksheet IXLRangeAddress.Worksheet + { + get { return Worksheet; } } IXLAddress IXLRangeAddress.FirstAddress @@ -82,19 +93,6 @@ set { FirstAddress = value as XLAddress; } } - public XLAddress LastAddress - { - get - { - if (IsInvalid) - { - throw new Exception("Range is an invalid state."); - } - - return m_lastAddress; - } - set { m_lastAddress = value; } - } IXLAddress IXLRangeAddress.LastAddress { [DebuggerStepThrough] @@ -104,38 +102,42 @@ public bool IsInvalid { get; set; } - #endregion - #region Public methods - public override string ToString() - { - return m_firstAddress + ":" + m_lastAddress; - } + #endregion + + #region Public methods public String ToStringRelative() { - return m_firstAddress.ToStringRelative() + ":" + m_lastAddress.ToStringRelative(); + return _firstAddress.ToStringRelative() + ":" + _lastAddress.ToStringRelative(); } + public String ToStringFixed() { - return m_firstAddress.ToStringFixed() + ":" + m_lastAddress.ToStringFixed(); + return _firstAddress.ToStringFixed() + ":" + _lastAddress.ToStringFixed(); + } + + public override string ToString() + { + return _firstAddress + ":" + _lastAddress; } public override bool Equals(object obj) { - var other = (XLRangeAddress) obj; + var other = (XLRangeAddress)obj; return Worksheet.Equals(other.Worksheet) - && FirstAddress.Equals(other.FirstAddress) - && LastAddress.Equals(other.LastAddress); + && FirstAddress.Equals(other.FirstAddress) + && LastAddress.Equals(other.LastAddress); } public override int GetHashCode() { return - Worksheet.GetHashCode() - ^ FirstAddress.GetHashCode() - ^ LastAddress.GetHashCode(); + Worksheet.GetHashCode() + ^ FirstAddress.GetHashCode() + ^ LastAddress.GetHashCode(); } + #endregion } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index 36dcf5e..030b982 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -36,32 +36,28 @@ { var thisRange = AsRange(); if (Worksheet.DataValidations.ContainsSingle(thisRange)) + { return Worksheet.DataValidations.Where(dv => dv.Ranges.Contains(thisRange)).Single() as XLDataValidation; + } var dvEmpty = new List(); foreach (IXLDataValidation dv in Worksheet.DataValidations) { - foreach (IXLRange dvRange in dv.Ranges) + foreach (IXLRange dvRange in dv.Ranges.Where(dvRange => dvRange.Intersects(this))) { - if (dvRange.Intersects(this)) - { - dv.Ranges.Remove(dvRange); - foreach (IXLCell c in dvRange.Cells()) - { - if (!Contains(c.Address.ToString())) - dv.Ranges.Add(c.AsRange()); - } - if (dv.Ranges.Count() == 0) - dvEmpty.Add(dv); - } + dv.Ranges.Remove(dvRange); + foreach (IXLCell c in dvRange.Cells().Where(c => !Contains(c.Address.ToString()))) + dv.Ranges.Add(c.AsRange()); + if (dv.Ranges.Count() == 0) + dvEmpty.Add(dv); } } dvEmpty.ForEach(dv => Worksheet.DataValidations.Delete(dv)); var newRanges = new XLRanges {AsRange()}; - var dataValidation = new XLDataValidation(newRanges, Worksheet); + var dataValidation = new XLDataValidation(newRanges); Worksheet.DataValidations.Add(dataValidation); return dataValidation; @@ -335,9 +331,9 @@ ? Worksheet.Workbook.NamedRanges : Worksheet.NamedRanges; - if (namedRanges.Any(nr => nr.Name.ToLower() == rangeName.ToLower())) + if (namedRanges.Any(nr => String.Compare(nr.Name, rangeName, true) == 0)) { - var namedRange = namedRanges.Where(nr => nr.Name.ToLower() == rangeName.ToLower()).Single(); + var namedRange = namedRanges.Where(nr => String.Compare(nr.Name, rangeName, true) == 0).Single(); namedRange.Add(Worksheet.Workbook, ToStringFixed()); } else @@ -351,6 +347,22 @@ return this; } + public Boolean IsMerged() + { + return CellsUsed().Any(c => c.IsMerged()); + } + + public Boolean IsEmpty() + { + return !CellsUsed().Any() || CellsUsed().Any(c => c.IsEmpty()); + } + + public Boolean IsEmpty(Boolean includeFormats) + { + return !CellsUsed(includeFormats).Any() || + CellsUsed(includeFormats).Any(c => c.IsEmpty(includeFormats)); + } + #endregion #region IXLStylized Members @@ -443,12 +455,16 @@ var absoluteAddress = cellAddressInRange + RangeAddress.FirstAddress - 1; if (absoluteAddress.RowNumber <= 0 || absoluteAddress.RowNumber > ExcelHelper.MaxRowNumber) + { throw new IndexOutOfRangeException(String.Format("Row number must be between 1 and {0}", ExcelHelper.MaxRowNumber)); + } if (absoluteAddress.ColumnNumber <= 0 || absoluteAddress.ColumnNumber > ExcelHelper.MaxColumnNumber) + { throw new IndexOutOfRangeException(String.Format("Column number must be between 1 and {0}", ExcelHelper.MaxColumnNumber)); + } var cell = Worksheet.Internals.CellsCollection.GetCell(absoluteAddress.RowNumber, absoluteAddress.ColumnNumber); @@ -931,19 +947,16 @@ RangeAddress.LastAddress.ColumnNumber); - foreach (IXLWorksheet ws in Worksheet.Workbook.Worksheets) + foreach ( + XLCell cell in + Worksheet.Workbook.Worksheets.Cast().SelectMany( + xlWorksheet => (xlWorksheet).Internals.CellsCollection.GetCells( + c => !StringExtensions.IsNullOrWhiteSpace(c.FormulaA1)))) { - var xlWorksheet = (XLWorksheet)ws; - foreach ( - XLCell cell in - (xlWorksheet).Internals.CellsCollection.GetCells( - c => !StringExtensions.IsNullOrWhiteSpace(c.FormulaA1))) - { - if (shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp) - cell.ShiftFormulaRows((XLRange)shiftedRangeFormula, numberOfRows * -1); - else - cell.ShiftFormulaColumns((XLRange)shiftedRangeFormula, numberOfColumns * -1); - } + if (shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp) + cell.ShiftFormulaRows((XLRange)shiftedRangeFormula, numberOfRows * -1); + else + cell.ShiftFormulaColumns((XLRange)shiftedRangeFormula, numberOfColumns * -1); } // Range to shift... @@ -1006,64 +1019,63 @@ protected void ShiftColumns(IXLRangeAddress thisRangeAddress, XLRange shiftedRange, int columnsShifted) { - if (!thisRangeAddress.IsInvalid && !shiftedRange.RangeAddress.IsInvalid) - { - if ((columnsShifted < 0 - // all columns - && - thisRangeAddress.FirstAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber - && - thisRangeAddress.LastAddress.ColumnNumber <= - shiftedRange.RangeAddress.FirstAddress.ColumnNumber - columnsShifted - // all rows - && thisRangeAddress.FirstAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber - && thisRangeAddress.LastAddress.RowNumber <= shiftedRange.RangeAddress.LastAddress.RowNumber - ) || ( - shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= - thisRangeAddress.FirstAddress.ColumnNumber - && - shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber - && - shiftedRange.RangeAddress.LastAddress.RowNumber >= thisRangeAddress.LastAddress.RowNumber - && shiftedRange.ColumnCount() > - (thisRangeAddress.LastAddress.ColumnNumber - thisRangeAddress.FirstAddress.ColumnNumber + 1) - + - (thisRangeAddress.FirstAddress.ColumnNumber - - shiftedRange.RangeAddress.FirstAddress.ColumnNumber))) - thisRangeAddress.IsInvalid = true; - else - { - if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber - && shiftedRange.RangeAddress.LastAddress.RowNumber >= thisRangeAddress.LastAddress.RowNumber) - { - if ( - (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= - thisRangeAddress.FirstAddress.ColumnNumber && - columnsShifted > 0) - || - (shiftedRange.RangeAddress.FirstAddress.ColumnNumber < - thisRangeAddress.FirstAddress.ColumnNumber && - columnsShifted < 0) - ) - { - thisRangeAddress.FirstAddress = new XLAddress(Worksheet, - thisRangeAddress.FirstAddress.RowNumber, - thisRangeAddress.FirstAddress.ColumnNumber + - columnsShifted, - thisRangeAddress.FirstAddress.FixedRow, - thisRangeAddress.FirstAddress.FixedColumn); - } + if (thisRangeAddress.IsInvalid || shiftedRange.RangeAddress.IsInvalid) return; - if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= - thisRangeAddress.LastAddress.ColumnNumber) - { - thisRangeAddress.LastAddress = new XLAddress(Worksheet, - thisRangeAddress.LastAddress.RowNumber, - thisRangeAddress.LastAddress.ColumnNumber + - columnsShifted, - thisRangeAddress.LastAddress.FixedRow, - thisRangeAddress.LastAddress.FixedColumn); - } + if ((columnsShifted < 0 + // all columns + && + thisRangeAddress.FirstAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber + && + thisRangeAddress.LastAddress.ColumnNumber <= + shiftedRange.RangeAddress.FirstAddress.ColumnNumber - columnsShifted + // all rows + && thisRangeAddress.FirstAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber + && thisRangeAddress.LastAddress.RowNumber <= shiftedRange.RangeAddress.LastAddress.RowNumber + ) || ( + shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= + thisRangeAddress.FirstAddress.ColumnNumber + && + shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber + && + shiftedRange.RangeAddress.LastAddress.RowNumber >= thisRangeAddress.LastAddress.RowNumber + && shiftedRange.ColumnCount() > + (thisRangeAddress.LastAddress.ColumnNumber - thisRangeAddress.FirstAddress.ColumnNumber + 1) + + + (thisRangeAddress.FirstAddress.ColumnNumber - + shiftedRange.RangeAddress.FirstAddress.ColumnNumber))) + thisRangeAddress.IsInvalid = true; + else + { + if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber + && shiftedRange.RangeAddress.LastAddress.RowNumber >= thisRangeAddress.LastAddress.RowNumber) + { + if ( + (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= + thisRangeAddress.FirstAddress.ColumnNumber && + columnsShifted > 0) + || + (shiftedRange.RangeAddress.FirstAddress.ColumnNumber < + thisRangeAddress.FirstAddress.ColumnNumber && + columnsShifted < 0) + ) + { + thisRangeAddress.FirstAddress = new XLAddress(Worksheet, + thisRangeAddress.FirstAddress.RowNumber, + thisRangeAddress.FirstAddress.ColumnNumber + + columnsShifted, + thisRangeAddress.FirstAddress.FixedRow, + thisRangeAddress.FirstAddress.FixedColumn); + } + + if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= + thisRangeAddress.LastAddress.ColumnNumber) + { + thisRangeAddress.LastAddress = new XLAddress(Worksheet, + thisRangeAddress.LastAddress.RowNumber, + thisRangeAddress.LastAddress.ColumnNumber + + columnsShifted, + thisRangeAddress.LastAddress.FixedRow, + thisRangeAddress.LastAddress.FixedColumn); } } } @@ -1071,63 +1083,62 @@ protected void ShiftRows(IXLRangeAddress thisRangeAddress, XLRange shiftedRange, int rowsShifted) { - if (!thisRangeAddress.IsInvalid && !shiftedRange.RangeAddress.IsInvalid) - { - if ((rowsShifted < 0 - // all columns - && - thisRangeAddress.FirstAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber - && thisRangeAddress.LastAddress.ColumnNumber <= shiftedRange.RangeAddress.FirstAddress.ColumnNumber - // all rows - && thisRangeAddress.FirstAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber - && - thisRangeAddress.LastAddress.RowNumber <= - shiftedRange.RangeAddress.LastAddress.RowNumber - rowsShifted - ) || ( - shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber - && - shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= - thisRangeAddress.FirstAddress.ColumnNumber - && - shiftedRange.RangeAddress.LastAddress.ColumnNumber >= - thisRangeAddress.LastAddress.ColumnNumber - && shiftedRange.RowCount() > - (thisRangeAddress.LastAddress.RowNumber - thisRangeAddress.FirstAddress.RowNumber + 1) - + - (thisRangeAddress.FirstAddress.RowNumber - shiftedRange.RangeAddress.FirstAddress.RowNumber))) - thisRangeAddress.IsInvalid = true; - else - { - if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= - thisRangeAddress.FirstAddress.ColumnNumber - && - shiftedRange.RangeAddress.LastAddress.ColumnNumber >= thisRangeAddress.LastAddress.ColumnNumber) - { - if ( - (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber && - rowsShifted > 0) - || - (shiftedRange.RangeAddress.FirstAddress.RowNumber < thisRangeAddress.FirstAddress.RowNumber && - rowsShifted < 0) - ) - { - thisRangeAddress.FirstAddress = new XLAddress(Worksheet, - thisRangeAddress.FirstAddress.RowNumber + - rowsShifted, - thisRangeAddress.FirstAddress.ColumnNumber, - thisRangeAddress.FirstAddress.FixedRow, - thisRangeAddress.FirstAddress.FixedColumn); - } + if (thisRangeAddress.IsInvalid || shiftedRange.RangeAddress.IsInvalid) return; - if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.LastAddress.RowNumber) - { - thisRangeAddress.LastAddress = new XLAddress(Worksheet, - thisRangeAddress.LastAddress.RowNumber + - rowsShifted, - thisRangeAddress.LastAddress.ColumnNumber, - thisRangeAddress.LastAddress.FixedRow, - thisRangeAddress.LastAddress.FixedColumn); - } + if ((rowsShifted < 0 + // all columns + && + thisRangeAddress.FirstAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber + && thisRangeAddress.LastAddress.ColumnNumber <= shiftedRange.RangeAddress.FirstAddress.ColumnNumber + // all rows + && thisRangeAddress.FirstAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber + && + thisRangeAddress.LastAddress.RowNumber <= + shiftedRange.RangeAddress.LastAddress.RowNumber - rowsShifted + ) || ( + shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber + && + shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= + thisRangeAddress.FirstAddress.ColumnNumber + && + shiftedRange.RangeAddress.LastAddress.ColumnNumber >= + thisRangeAddress.LastAddress.ColumnNumber + && shiftedRange.RowCount() > + (thisRangeAddress.LastAddress.RowNumber - thisRangeAddress.FirstAddress.RowNumber + 1) + + + (thisRangeAddress.FirstAddress.RowNumber - shiftedRange.RangeAddress.FirstAddress.RowNumber))) + thisRangeAddress.IsInvalid = true; + else + { + if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= + thisRangeAddress.FirstAddress.ColumnNumber + && + shiftedRange.RangeAddress.LastAddress.ColumnNumber >= thisRangeAddress.LastAddress.ColumnNumber) + { + if ( + (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber && + rowsShifted > 0) + || + (shiftedRange.RangeAddress.FirstAddress.RowNumber < thisRangeAddress.FirstAddress.RowNumber && + rowsShifted < 0) + ) + { + thisRangeAddress.FirstAddress = new XLAddress(Worksheet, + thisRangeAddress.FirstAddress.RowNumber + + rowsShifted, + thisRangeAddress.FirstAddress.ColumnNumber, + thisRangeAddress.FirstAddress.FixedRow, + thisRangeAddress.FirstAddress.FixedColumn); + } + + if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.LastAddress.RowNumber) + { + thisRangeAddress.LastAddress = new XLAddress(Worksheet, + thisRangeAddress.LastAddress.RowNumber + + rowsShifted, + thisRangeAddress.LastAddress.ColumnNumber, + thisRangeAddress.LastAddress.FixedRow, + thisRangeAddress.LastAddress.FixedColumn); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs index 601ca48..7179771 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs @@ -10,23 +10,17 @@ public XLRangeColumn(XLRangeParameters rangeParameters, bool quickLoad) : base(rangeParameters.RangeAddress) { - if (!quickLoad) - { - Worksheet.RangeShiftedRows += WorksheetRangeShiftedRows; - Worksheet.RangeShiftedColumns += WorksheetRangeShiftedColumns; - DefaultStyle = new XLStyle(this, rangeParameters.DefaultStyle); - } + if (quickLoad) return; + + Worksheet.RangeShiftedRows += WorksheetRangeShiftedRows; + Worksheet.RangeShiftedColumns += WorksheetRangeShiftedColumns; + DefaultStyle = new XLStyle(this, rangeParameters.DefaultStyle); } #endregion #region IXLRangeColumn Members - public XLCell Cell(int row) - { - return Cell(row, 1); - } - IXLCell IXLRangeColumn.Cell(int row) { return Cell(row); @@ -34,7 +28,7 @@ public new IXLCells Cells(string cellsInColumn) { - var retVal = new XLCells( false, false); + var retVal = new XLCells(false, false); var rangePairs = cellsInColumn.Split(','); foreach (string pair in rangePairs) retVal.Add(Range(pair.Trim()).RangeAddress); @@ -112,9 +106,9 @@ lastColumnNumber = ExcelHelper.MaxColumnNumber; return target.Worksheet.Range( - target.Address.RowNumber, - target.Address.ColumnNumber, - lastRowNumber, + target.Address.RowNumber, + target.Address.ColumnNumber, + lastRowNumber, lastColumnNumber) .Column(1); } @@ -131,9 +125,9 @@ lastColumnNumber = ExcelHelper.MaxColumnNumber; return target.Worksheet.Range( - target.RangeAddress.FirstAddress.RowNumber, - target.RangeAddress.FirstAddress.ColumnNumber, - lastRowNumber, + target.RangeAddress.FirstAddress.RowNumber, + target.RangeAddress.FirstAddress.ColumnNumber, + lastRowNumber, lastColumnNumber) .Column(1); } @@ -147,17 +141,16 @@ { var retVal = new XLRangeColumns(); var rowPairs = columns.Split(','); - foreach (string pair in rowPairs) + foreach (string trimmedPair in rowPairs.Select(pair => pair.Trim())) { - string trimmedPair = pair.Trim(); string firstRow; string lastRow; if (trimmedPair.Contains(':') || trimmedPair.Contains('-')) { - if (trimmedPair.Contains('-')) - trimmedPair = trimmedPair.Replace('-', ':'); + var rowRange = trimmedPair.Contains('-') + ? trimmedPair.Replace('-', ':').Split(':') + : trimmedPair.Split(':'); - var rowRange = trimmedPair.Split(':'); firstRow = rowRange[0]; lastRow = rowRange[1]; } @@ -179,8 +172,18 @@ return this; } + public IXLColumn WorksheetColumn() + { + return Worksheet.Column(RangeAddress.FirstAddress.ColumnNumber); + } + #endregion + public XLCell Cell(int row) + { + return Cell(row, 1); + } + private void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted) { ShiftColumns(RangeAddress, range, columnsShifted); @@ -223,8 +226,8 @@ var thisCell = Cell(e.ElementNumber); var otherCell = otherColumn.Cell(e.ElementNumber); int comparison; - bool thisCellIsBlank = !thisCell.IsUsed(); - bool otherCellIsBlank = !otherCell.IsUsed(); + bool thisCellIsBlank = thisCell.IsEmpty(); + bool otherCellIsBlank = otherCell.IsEmpty(); if (e.IgnoreBlanks && (thisCellIsBlank || otherCellIsBlank)) { if (thisCellIsBlank && otherCellIsBlank) @@ -243,7 +246,9 @@ { if (thisCell.DataType == XLCellValues.Text) { - comparison = e.MatchCase ? thisCell.InnerText.CompareTo(otherCell.InnerText) : thisCell.InnerText.ToLower().CompareTo(otherCell.InnerText.ToLower()); + comparison = e.MatchCase + ? thisCell.InnerText.CompareTo(otherCell.InnerText) + : String.Compare(thisCell.InnerText, otherCell.InnerText, true); } else if (thisCell.DataType == XLCellValues.TimeSpan) comparison = thisCell.GetTimeSpan().CompareTo(otherCell.GetTimeSpan()); @@ -251,7 +256,7 @@ comparison = Double.Parse(thisCell.InnerText).CompareTo(Double.Parse(otherCell.InnerText)); } else if (e.MatchCase) - comparison = thisCell.GetString().ToLower().CompareTo(otherCell.GetString().ToLower()); + comparison = String.Compare(thisCell.GetString(), otherCell.GetString(), true); else comparison = thisCell.GetString().CompareTo(otherCell.GetString()); } @@ -263,44 +268,6 @@ return 0; } - #region XLRangeColumn Left - public XLRangeColumn ColumnLeft() - { - return ColumnLeft(1); - } - IXLRangeColumn IXLRangeColumn.ColumnLeft() - { - return ColumnLeft(); - } - public XLRangeColumn ColumnLeft(Int32 step) - { - return ColumnShift(step * -1); - } - IXLRangeColumn IXLRangeColumn.ColumnLeft(Int32 step) - { - return ColumnLeft(step); - } - #endregion - - #region XLRangeColumn Right - public XLRangeColumn ColumnRight() - { - return ColumnRight(1); - } - IXLRangeColumn IXLRangeColumn.ColumnRight() - { - return ColumnRight(); - } - public XLRangeColumn ColumnRight(Int32 step) - { - return ColumnShift(step); - } - IXLRangeColumn IXLRangeColumn.ColumnRight(Int32 step) - { - return ColumnRight(step); - } - #endregion - private XLRangeColumn ColumnShift(Int32 columnsToShift) { Int32 columnNumber = ColumnNumber() + columnsToShift; @@ -311,10 +278,52 @@ columnNumber).FirstColumn(); } - public IXLColumn WorksheetColumn() + #region XLRangeColumn Left + + IXLRangeColumn IXLRangeColumn.ColumnLeft() { - return Worksheet.Column(RangeAddress.FirstAddress.ColumnNumber); + return ColumnLeft(); } + IXLRangeColumn IXLRangeColumn.ColumnLeft(Int32 step) + { + return ColumnLeft(step); + } + + public XLRangeColumn ColumnLeft() + { + return ColumnLeft(1); + } + + public XLRangeColumn ColumnLeft(Int32 step) + { + return ColumnShift(step * -1); + } + + #endregion + + #region XLRangeColumn Right + + IXLRangeColumn IXLRangeColumn.ColumnRight() + { + return ColumnRight(); + } + + IXLRangeColumn IXLRangeColumn.ColumnRight(Int32 step) + { + return ColumnRight(step); + } + + public XLRangeColumn ColumnRight() + { + return ColumnRight(1); + } + + public XLRangeColumn ColumnRight(Int32 step) + { + return ColumnShift(step); + } + + #endregion } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeParameters.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeParameters.cs index ab3bbf6..353a3fc 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeParameters.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeParameters.cs @@ -3,18 +3,23 @@ internal class XLRangeParameters { #region Constructor + public XLRangeParameters(XLRangeAddress rangeAddress, IXLStyle defaultStyle) { RangeAddress = rangeAddress; DefaultStyle = defaultStyle; } + #endregion + #region Properties + public XLRangeAddress RangeAddress { get; private set; } public IXLStyle DefaultStyle { get; private set; } public bool IgnoreEvents { get; set; } + #endregion } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs index 70d430e..a4cd964 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs @@ -11,12 +11,10 @@ : base(rangeParameters.RangeAddress) { RangeParameters = rangeParameters; - if (!quickLoad) - { - Worksheet.RangeShiftedRows += WorksheetRangeShiftedRows; - Worksheet.RangeShiftedColumns += WorksheetRangeShiftedColumns; - DefaultStyle = new XLStyle(this, rangeParameters.DefaultStyle); - } + if (quickLoad) return; + Worksheet.RangeShiftedRows += WorksheetRangeShiftedRows; + Worksheet.RangeShiftedColumns += WorksheetRangeShiftedColumns; + DefaultStyle = new XLStyle(this, rangeParameters.DefaultStyle); } #endregion @@ -62,7 +60,7 @@ public new IXLCells Cells(string cellsInRow) { - var retVal = new XLCells( false, false); + var retVal = new XLCells(false, false); var rangePairs = cellsInRow.Split(','); foreach (string pair in rangePairs) retVal.Add(Range(pair.Trim()).RangeAddress); @@ -121,9 +119,9 @@ lastColumnNumber = ExcelHelper.MaxColumnNumber; return target.Worksheet.Range( - target.Address.RowNumber, - target.Address.ColumnNumber, - lastRowNumber, + target.Address.RowNumber, + target.Address.ColumnNumber, + lastRowNumber, lastColumnNumber) .Row(1); } @@ -139,9 +137,9 @@ lastColumnNumber = ExcelHelper.MaxColumnNumber; return target.Worksheet.Range( - target.RangeAddress.FirstAddress.RowNumber, - target.RangeAddress.LastAddress.ColumnNumber, - lastRowNumber, + target.RangeAddress.FirstAddress.RowNumber, + target.RangeAddress.LastAddress.ColumnNumber, + lastRowNumber, lastColumnNumber) .Row(1); } @@ -155,17 +153,15 @@ { var retVal = new XLRangeRows(); var columnPairs = rows.Split(','); - foreach (string pair in columnPairs) + foreach (string trimmedPair in columnPairs.Select(pair => pair.Trim())) { - string trimmedPair = pair.Trim(); string firstColumn; string lastColumn; if (trimmedPair.Contains(':') || trimmedPair.Contains('-')) { - if (trimmedPair.Contains('-')) - trimmedPair = trimmedPair.Replace('-', ':'); - - var columnRange = trimmedPair.Split(':'); + var columnRange = trimmedPair.Contains('-') + ? trimmedPair.Replace('-', ':').Split(':') + : trimmedPair.Split(':'); firstColumn = columnRange[0]; lastColumn = columnRange[1]; } @@ -187,6 +183,11 @@ return this; } + public IXLRow WorksheetRow() + { + return Worksheet.Row(RangeAddress.FirstAddress.RowNumber); + } + #endregion private void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted) @@ -231,8 +232,8 @@ var thisCell = (XLCell)Cell(e.ElementNumber); var otherCell = (XLCell)otherRow.Cell(e.ElementNumber); int comparison; - bool thisCellIsBlank = StringExtensions.IsNullOrWhiteSpace(thisCell.InnerText); - bool otherCellIsBlank = StringExtensions.IsNullOrWhiteSpace(otherCell.InnerText); + bool thisCellIsBlank = thisCell.IsEmpty(); + bool otherCellIsBlank = otherCell.IsEmpty(); if (e.IgnoreBlanks && (thisCellIsBlank || otherCellIsBlank)) { if (thisCellIsBlank && otherCellIsBlank) @@ -251,7 +252,9 @@ { if (thisCell.DataType == XLCellValues.Text) { - comparison = e.MatchCase ? thisCell.InnerText.CompareTo(otherCell.InnerText) : thisCell.InnerText.ToLower().CompareTo(otherCell.InnerText.ToLower()); + comparison = e.MatchCase + ? thisCell.InnerText.CompareTo(otherCell.InnerText) + : String.Compare(thisCell.InnerText, otherCell.InnerText, true); } else if (thisCell.DataType == XLCellValues.TimeSpan) comparison = thisCell.GetTimeSpan().CompareTo(otherCell.GetTimeSpan()); @@ -259,7 +262,7 @@ comparison = Double.Parse(thisCell.InnerText).CompareTo(Double.Parse(otherCell.InnerText)); } else if (e.MatchCase) - comparison = thisCell.GetString().ToLower().CompareTo(otherCell.GetString().ToLower()); + comparison = String.Compare(thisCell.GetString(), otherCell.GetString(), true); else comparison = thisCell.GetString().CompareTo(otherCell.GetString()); } @@ -271,44 +274,6 @@ return 0; } - #region XLRangeRow Above - public XLRangeRow RowAbove() - { - return RowAbove(1); - } - IXLRangeRow IXLRangeRow.RowAbove() - { - return RowAbove(); - } - public XLRangeRow RowAbove(Int32 step) - { - return RowShift(step * -1); - } - IXLRangeRow IXLRangeRow.RowAbove(Int32 step) - { - return RowAbove(step); - } - #endregion - - #region XLRangeRow Below - public XLRangeRow RowBelow() - { - return RowBelow(1); - } - IXLRangeRow IXLRangeRow.RowBelow() - { - return RowBelow(); - } - public XLRangeRow RowBelow(Int32 step) - { - return RowShift(step); - } - IXLRangeRow IXLRangeRow.RowBelow(Int32 step) - { - return RowBelow(step); - } - #endregion - private XLRangeRow RowShift(Int32 rowsToShift) { Int32 rowNum = RowNumber() + rowsToShift; @@ -319,10 +284,52 @@ RangeAddress.LastAddress.ColumnNumber).FirstRow(); } - - public IXLRow WorksheetRow() + #region XLRangeRow Above + + IXLRangeRow IXLRangeRow.RowAbove() { - return Worksheet.Row(RangeAddress.FirstAddress.RowNumber); + return RowAbove(); } + + IXLRangeRow IXLRangeRow.RowAbove(Int32 step) + { + return RowAbove(step); + } + + public XLRangeRow RowAbove() + { + return RowAbove(1); + } + + public XLRangeRow RowAbove(Int32 step) + { + return RowShift(step * -1); + } + + #endregion + + #region XLRangeRow Below + + IXLRangeRow IXLRangeRow.RowBelow() + { + return RowBelow(); + } + + IXLRangeRow IXLRangeRow.RowBelow(Int32 step) + { + return RowBelow(step); + } + + public XLRangeRow RowBelow() + { + return RowBelow(1); + } + + public XLRangeRow RowBelow(Int32 step) + { + return RowShift(step); + } + + #endregion } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs index fdbdadc..9d7f8cf 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs @@ -86,21 +86,17 @@ { foreach (IXLDataValidation dv in range.Worksheet.DataValidations) { - foreach (IXLRange dvRange in dv.Ranges) + foreach (IXLRange dvRange in dv.Ranges.Where(dvRange => dvRange.Intersects(range))) { - if (dvRange.Intersects(range)) + dv.Ranges.Remove(dvRange); + foreach (IXLCell c in dvRange.Cells().Where(c => !range.Contains(c.Address.ToString()))) { - dv.Ranges.Remove(dvRange); - foreach (IXLCell c in dvRange.Cells()) - { - if (!range.Contains(c.Address.ToString())) - dv.Ranges.Add(c.AsRange()); - } + dv.Ranges.Add(c.AsRange()); } } } } - var dataValidation = new XLDataValidation(this, _ranges.First().Worksheet); + var dataValidation = new XLDataValidation(this); _ranges.First().Worksheet.DataValidations.Add(dataValidation); return dataValidation; @@ -214,10 +210,7 @@ { var other = (XLRanges)obj; - if (_ranges.Count != other._ranges.Count) - return false; - - return _ranges.Select(thisRange => Enumerable.Contains(other._ranges, thisRange)).All(foundOne => foundOne); + return _ranges.Count == other._ranges.Count && _ranges.Select(thisRange => Enumerable.Contains(other._ranges, thisRange)).All(foundOne => foundOne); } public override int GetHashCode() diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/RichText/XLRichString.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/RichText/XLRichString.cs index 99ad9b4..2615dff 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/RichText/XLRichString.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/RichText/XLRichString.cs @@ -40,22 +40,22 @@ { return Text == other.Text - && this.Bold.Equals(other.Bold) - && this.Italic.Equals(other.Italic) - && this.Underline.Equals(other.Underline) - && this.Strikethrough.Equals(other.Strikethrough) - && this.VerticalAlignment.Equals(other.VerticalAlignment) - && this.Shadow.Equals(other.Shadow) - && this.FontSize.Equals(other.FontSize) - && this.FontColor.Equals(other.FontColor) - && this.FontName.Equals(other.FontName) - && this.FontFamilyNumbering.Equals(other.FontFamilyNumbering) + && Bold.Equals(other.Bold) + && Italic.Equals(other.Italic) + && Underline.Equals(other.Underline) + && Strikethrough.Equals(other.Strikethrough) + && VerticalAlignment.Equals(other.VerticalAlignment) + && Shadow.Equals(other.Shadow) + && FontSize.Equals(other.FontSize) + && FontColor.Equals(other.FontColor) + && FontName.Equals(other.FontName) + && FontFamilyNumbering.Equals(other.FontFamilyNumbering) ; } public override bool Equals(object obj) { - return this.Equals((XLRichString)obj); + return Equals((XLRichString)obj); } public override int GetHashCode() diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/RichText/XLRichText.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/RichText/XLRichText.cs index 2e7287a..9418929 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/RichText/XLRichText.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/RichText/XLRichText.cs @@ -22,7 +22,7 @@ AddText(rt.Text, rt); if (defaultRichText.HasPhonetics) { - phonetics = new XLPhonetics(defaultRichText.Phonetics, defaultFont); + _phonetics = new XLPhonetics(defaultRichText.Phonetics, defaultFont); } } @@ -33,12 +33,12 @@ } public Int32 Count { get { return _richTexts.Count; } } - private Int32 m_length = 0; + private Int32 _length = 0; public Int32 Length { get { - return m_length; + return _length; } } public IXLRichString AddText(String text) @@ -54,13 +54,13 @@ public IXLRichString AddText(IXLRichString richText) { _richTexts.Add(richText); - m_length += richText.Text.Length; + _length += richText.Text.Length; return richText; } public IXLRichText ClearText() { _richTexts.Clear(); - m_length = 0; + _length = 0; return this; } public IXLRichText ClearFont() @@ -80,11 +80,11 @@ public IXLRichText Substring(Int32 index) { - return Substring(index, m_length - index); + return Substring(index, _length - index); } public IXLRichText Substring(Int32 index, Int32 length) { - if (index + 1 > m_length || (m_length - index + 1) < length || length <= 0) + if (index + 1 > _length || (_length - index + 1) < length || length <= 0) throw new IndexOutOfRangeException("Index and length must refer to a location within the string."); List newRichTexts = new List(); @@ -110,9 +110,9 @@ if (leftToTake > rt.Text.Length - startIndex) leftToTake = rt.Text.Length - startIndex; - XLRichString newRT = new XLRichString(rt.Text.Substring(startIndex, leftToTake), rt); - newRichTexts.Add(newRT); - retVal.AddText(newRT); + XLRichString newRt = new XLRichString(rt.Text.Substring(startIndex, leftToTake), rt); + newRichTexts.Add(newRt); + retVal.AddText(newRt); if (startIndex + leftToTake < rt.Text.Length) newRichTexts.Add(new XLRichString(rt.Text.Substring(startIndex + leftToTake), rt)); @@ -171,26 +171,17 @@ return false; } - if (phonetics == null) - return true; - else - return Phonetics.Equals(other.Phonetics); + return _phonetics == null || Phonetics.Equals(other.Phonetics); } public String Text { get { return ToString(); } } - private IXLPhonetics phonetics; + private IXLPhonetics _phonetics; public IXLPhonetics Phonetics { - get - { - if (phonetics == null) - phonetics = new XLPhonetics(_defaultFont); - - return phonetics; - } + get { return _phonetics ?? (_phonetics = new XLPhonetics(_defaultFont)); } } - public Boolean HasPhonetics { get { return phonetics != null; } } + public Boolean HasPhonetics { get { return _phonetics != null; } } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs index bffff3a..7e20390 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs @@ -66,12 +66,12 @@ int row = RowNumber(); Int32 maxColumn = 0; - if ((Worksheet).Internals.CellsCollection.RowsUsed.ContainsKey(row)) + if (Worksheet.Internals.CellsCollection.RowsUsed.ContainsKey(row)) maxColumn = Worksheet.Internals.CellsCollection.MaxColumnInRow(row); - if ((Worksheet).Internals.ColumnsCollection.Count > 0) + if (Worksheet.Internals.ColumnsCollection.Count > 0) { - Int32 maxInCollection = (Worksheet).Internals.ColumnsCollection.Keys.Max(); + Int32 maxInCollection = Worksheet.Internals.ColumnsCollection.Keys.Max(); if (maxInCollection > maxColumn) maxColumn = maxInCollection; } @@ -98,7 +98,7 @@ set { if (IsReference) - (Worksheet).Internals.RowsCollection[RowNumber()].InnerStyle = value; + Worksheet.Internals.RowsCollection[RowNumber()].InnerStyle = value; else _style = new XLStyle(this, value); } @@ -110,7 +110,7 @@ set { if (IsReference) - (Worksheet).Internals.RowsCollection[RowNumber()].Collapsed = value; + Worksheet.Internals.RowsCollection[RowNumber()].Collapsed = value; else _collapsed = value; } @@ -124,7 +124,7 @@ set { if (IsReference) - (Worksheet).Internals.RowsCollection[RowNumber()].Height = value; + Worksheet.Internals.RowsCollection[RowNumber()].Height = value; else _height = value; } @@ -134,20 +134,20 @@ { int rowNumber = RowNumber(); AsRange().Delete(XLShiftDeletedCells.ShiftCellsUp); - (Worksheet).Internals.RowsCollection.Remove(rowNumber); + Worksheet.Internals.RowsCollection.Remove(rowNumber); var rowsToMove = new List(); - rowsToMove.AddRange((Worksheet).Internals.RowsCollection.Where(c => c.Key > rowNumber).Select(c => c.Key)); + rowsToMove.AddRange(Worksheet.Internals.RowsCollection.Where(c => c.Key > rowNumber).Select(c => c.Key)); foreach (int row in rowsToMove.OrderBy(r => r)) { - (Worksheet).Internals.RowsCollection.Add(row - 1, (Worksheet).Internals.RowsCollection[row]); - (Worksheet).Internals.RowsCollection.Remove(row); + Worksheet.Internals.RowsCollection.Add(row - 1, Worksheet.Internals.RowsCollection[row]); + Worksheet.Internals.RowsCollection.Remove(row); } } public new IXLRows InsertRowsBelow(Int32 numberOfRows) { int rowNum = RowNumber(); - (Worksheet).Internals.RowsCollection.ShiftRowsDown(rowNum + 1, numberOfRows); + Worksheet.Internals.RowsCollection.ShiftRowsDown(rowNum + 1, numberOfRows); var range = (XLRange)Worksheet.Row(rowNum).AsRange(); range.InsertRowsBelow(true, numberOfRows); return Worksheet.Rows(rowNum + 1, rowNum + numberOfRows); @@ -156,7 +156,7 @@ public new IXLRows InsertRowsAbove(Int32 numberOfRows) { int rowNum = RowNumber(); - (Worksheet).Internals.RowsCollection.ShiftRowsDown(rowNum, numberOfRows); + Worksheet.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. var range = (XLRange)Worksheet.Row(rowNum).AsRange(); @@ -313,11 +313,11 @@ public Boolean IsHidden { - get { return IsReference ? (Worksheet).Internals.RowsCollection[RowNumber()].IsHidden : _isHidden; } + get { return IsReference ? Worksheet.Internals.RowsCollection[RowNumber()].IsHidden : _isHidden; } set { if (IsReference) - (Worksheet).Internals.RowsCollection[RowNumber()].IsHidden = value; + Worksheet.Internals.RowsCollection[RowNumber()].IsHidden = value; else _isHidden = value; } @@ -329,7 +329,7 @@ set { if (IsReference) - (Worksheet).Internals.RowsCollection[RowNumber()].Style = value; + Worksheet.Internals.RowsCollection[RowNumber()].Style = value; else { _style = new XLStyle(this, value); @@ -343,10 +343,10 @@ maxColumn = Worksheet.Internals.CellsCollection.MaxColumnInRow(row); } - if ((Worksheet).Internals.ColumnsCollection.Count > 0) + if (Worksheet.Internals.ColumnsCollection.Count > 0) { - Int32 minInCollection = (Worksheet).Internals.ColumnsCollection.Keys.Min(); - Int32 maxInCollection = (Worksheet).Internals.ColumnsCollection.Keys.Max(); + Int32 minInCollection = Worksheet.Internals.ColumnsCollection.Keys.Min(); + Int32 maxInCollection = Worksheet.Internals.ColumnsCollection.Keys.Max(); if (minInCollection < minColumn) minColumn = minInCollection; if (maxInCollection > maxColumn) @@ -565,7 +565,7 @@ internal void SetStyleNoColumns(IXLStyle value) { if (IsReference) - (Worksheet).Internals.RowsCollection[RowNumber()].SetStyleNoColumns(value); + Worksheet.Internals.RowsCollection[RowNumber()].SetStyleNoColumns(value); else { _style = new XLStyle(this, value); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRowCollection.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRowCollection.cs index 28e6ca0..2cca961 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRowCollection.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRowCollection.cs @@ -4,102 +4,83 @@ namespace ClosedXML.Excel { - internal class XLRowsCollection: IDictionary - { - public void ShiftRowsDown(Int32 startingRow, Int32 rowsToShift) - { - foreach (var ro in dictionary.Keys.Where(k => k >= startingRow).OrderByDescending(k => k)) - { - var rowToMove = dictionary[ro]; - Int32 newRow = ro + rowsToShift; - if (newRow <= ExcelHelper.MaxRowNumber) - { - dictionary.Add(newRow, new XLRow(rowToMove)); - } - dictionary.Remove(ro); - } - } + using System.Collections; - private Dictionary dictionary = new Dictionary(); - private Dictionary deleted = new Dictionary(); + internal class XLRowsCollection : IDictionary + { + private readonly Dictionary _deleted = new Dictionary(); + private readonly Dictionary _dictionary = new Dictionary(); + public Dictionary Deleted { - get - { - return deleted; - } + get { return _deleted; } } + #region IDictionary Members + public void Add(int key, XLRow value) { - if (deleted.ContainsKey(key)) - deleted.Remove(key); + if (_deleted.ContainsKey(key)) + _deleted.Remove(key); - dictionary.Add(key, value); + _dictionary.Add(key, value); } public bool ContainsKey(int key) { - return dictionary.ContainsKey(key); + return _dictionary.ContainsKey(key); } public ICollection Keys { - get { return dictionary.Keys; } + get { return _dictionary.Keys; } } public bool Remove(int key) { - if (!deleted.ContainsKey(key)) - deleted.Add(key, dictionary[key]); + if (!_deleted.ContainsKey(key)) + _deleted.Add(key, _dictionary[key]); - return dictionary.Remove(key); + return _dictionary.Remove(key); } public bool TryGetValue(int key, out XLRow value) { - return dictionary.TryGetValue(key, out value); + return _dictionary.TryGetValue(key, out value); } public ICollection Values { - get { return dictionary.Values; } + get { return _dictionary.Values; } } public XLRow this[int key] { - get - { - return dictionary[key]; - } - set - { - dictionary[key] = value; - } + get { return _dictionary[key]; } + set { _dictionary[key] = value; } } public void Add(KeyValuePair item) { - if (deleted.ContainsKey(item.Key)) - deleted.Remove(item.Key); + if (_deleted.ContainsKey(item.Key)) + _deleted.Remove(item.Key); - dictionary.Add(item.Key, item.Value); + _dictionary.Add(item.Key, item.Value); } public void Clear() { - foreach (var kp in dictionary) + foreach (KeyValuePair kp in _dictionary.Where(kp => !_deleted.ContainsKey(kp.Key))) { - if (!deleted.ContainsKey(kp.Key)) - deleted.Add(kp.Key, kp.Value); + _deleted.Add(kp.Key, kp.Value); } - dictionary.Clear(); + _dictionary.Clear(); } public bool Contains(KeyValuePair item) { - return dictionary.Contains(item); + return _dictionary.Contains(item); } public void CopyTo(KeyValuePair[] array, int arrayIndex) @@ -109,7 +90,7 @@ public int Count { - get { return dictionary.Count; } + get { return _dictionary.Count; } } public bool IsReadOnly @@ -119,31 +100,44 @@ public bool Remove(KeyValuePair item) { - if (!deleted.ContainsKey(item.Key)) - deleted.Add(item.Key, dictionary[item.Key]); + if (!_deleted.ContainsKey(item.Key)) + _deleted.Add(item.Key, _dictionary[item.Key]); - return dictionary.Remove(item.Key); + return _dictionary.Remove(item.Key); } public IEnumerator> GetEnumerator() { - return dictionary.GetEnumerator(); + return _dictionary.GetEnumerator(); } - System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() + IEnumerator IEnumerable.GetEnumerator() { - return dictionary.GetEnumerator(); + return _dictionary.GetEnumerator(); + } + + #endregion + + public void ShiftRowsDown(Int32 startingRow, Int32 rowsToShift) + { + foreach (int ro in _dictionary.Keys.Where(k => k >= startingRow).OrderByDescending(k => k)) + { + var rowToMove = _dictionary[ro]; + Int32 newRow = ro + rowsToShift; + if (newRow <= ExcelHelper.MaxRowNumber) + _dictionary.Add(newRow, new XLRow(rowToMove)); + _dictionary.Remove(ro); + } } public void RemoveAll(Func predicate) { - foreach (var kp in dictionary.Values.Where(predicate).Select(c=>c)) + foreach (XLRow kp in _dictionary.Values.Where(predicate).Select(c => c).Where(kp => !_deleted.ContainsKey(kp.RowNumber()))) { - if (!deleted.ContainsKey(kp.RowNumber())) - deleted.Add(kp.RowNumber(), kp); + _deleted.Add(kp.RowNumber(), kp); } - dictionary.RemoveAll(predicate); + _dictionary.RemoveAll(predicate); } } -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRowParameters.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRowParameters.cs index d62ae0e..0b2d13e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRowParameters.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRowParameters.cs @@ -11,8 +11,9 @@ DefaultStyle = defaultStyle; IsReference = isReference; } + public IXLStyle DefaultStyle { get; set; } public XLWorksheet Worksheet { get; private set; } public Boolean IsReference { get; private set; } } -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs index cc8c2aa..56f2b3f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs @@ -8,13 +8,13 @@ internal class XLRows : IXLRows, IXLStylized { - private readonly List rows = new List(); - private readonly XLWorksheet worksheet; + private readonly List _rows = new List(); + private readonly XLWorksheet _worksheet; internal IXLStyle style; public XLRows(XLWorksheet worksheet) { - this.worksheet = worksheet; + _worksheet = worksheet; style = new XLStyle(this, XLWorkbook.DefaultStyle); } @@ -23,7 +23,7 @@ public IEnumerator GetEnumerator() { var retList = new List(); - rows.ForEach(retList.Add); + _rows.ForEach(retList.Add); return retList.GetEnumerator(); } @@ -39,11 +39,11 @@ { style = new XLStyle(this, value); - if (worksheet != null) - worksheet.Style = value; + if (_worksheet != null) + _worksheet.Style = value; else { - foreach (XLRow row in rows) + foreach (XLRow row in _rows) row.Style = value; } } @@ -53,26 +53,24 @@ { set { - rows.ForEach(c => c.Height = value); - if (worksheet != null) - { - worksheet.RowHeight = value; - worksheet.Internals.RowsCollection.ForEach(r => r.Value.Height = value); - } + _rows.ForEach(c => c.Height = value); + if (_worksheet == null) return; + _worksheet.RowHeight = value; + _worksheet.Internals.RowsCollection.ForEach(r => r.Value.Height = value); } } public void Delete() { - if (worksheet != null) + if (_worksheet != null) { - worksheet.Internals.RowsCollection.Clear(); - worksheet.Internals.CellsCollection.Clear(); + _worksheet.Internals.RowsCollection.Clear(); + _worksheet.Internals.CellsCollection.Clear(); } else { var toDelete = new Dictionary>(); - foreach (XLRow r in rows) + foreach (XLRow r in _rows) { if (!toDelete.ContainsKey(r.Worksheet)) toDelete.Add(r.Worksheet, new List()); @@ -90,49 +88,49 @@ public IXLRows AdjustToContents() { - rows.ForEach(r => r.AdjustToContents()); + _rows.ForEach(r => r.AdjustToContents()); return this; } public IXLRows AdjustToContents(Int32 startColumn) { - rows.ForEach(r => r.AdjustToContents(startColumn)); + _rows.ForEach(r => r.AdjustToContents(startColumn)); return this; } public IXLRows AdjustToContents(Int32 startColumn, Int32 endColumn) { - rows.ForEach(r => r.AdjustToContents(startColumn, endColumn)); + _rows.ForEach(r => r.AdjustToContents(startColumn, endColumn)); return this; } public IXLRows AdjustToContents(Double minHeight, Double maxHeight) { - rows.ForEach(r => r.AdjustToContents(minHeight, maxHeight)); + _rows.ForEach(r => r.AdjustToContents(minHeight, maxHeight)); return this; } public IXLRows AdjustToContents(Int32 startColumn, Double minHeight, Double maxHeight) { - rows.ForEach(r => r.AdjustToContents(startColumn, minHeight, maxHeight)); + _rows.ForEach(r => r.AdjustToContents(startColumn, minHeight, maxHeight)); return this; } public IXLRows AdjustToContents(Int32 startColumn, Int32 endColumn, Double minHeight, Double maxHeight) { - rows.ForEach(r => r.AdjustToContents(startColumn, endColumn, minHeight, maxHeight)); + _rows.ForEach(r => r.AdjustToContents(startColumn, endColumn, minHeight, maxHeight)); return this; } public void Hide() { - rows.ForEach(r => r.Hide()); + _rows.ForEach(r => r.Hide()); } public void Unhide() { - rows.ForEach(r => r.Unhide()); + _rows.ForEach(r => r.Unhide()); } public void Group() @@ -152,33 +150,33 @@ public void Group(Boolean collapse) { - rows.ForEach(r => r.Group(collapse)); + _rows.ForEach(r => r.Group(collapse)); } public void Group(Int32 outlineLevel, Boolean collapse) { - rows.ForEach(r => r.Group(outlineLevel, collapse)); + _rows.ForEach(r => r.Group(outlineLevel, collapse)); } public void Ungroup(Boolean ungroupFromAll) { - rows.ForEach(r => r.Ungroup(ungroupFromAll)); + _rows.ForEach(r => r.Ungroup(ungroupFromAll)); } public void Collapse() { - rows.ForEach(r => r.Collapse()); + _rows.ForEach(r => r.Collapse()); } public void Expand() { - rows.ForEach(r => r.Expand()); + _rows.ForEach(r => r.Expand()); } public IXLCells Cells() { var cells = new XLCells(false, false); - foreach (XLRow container in rows) + foreach (XLRow container in _rows) cells.Add(container.RangeAddress); return cells; } @@ -186,7 +184,7 @@ public IXLCells CellsUsed() { var cells = new XLCells(true, false); - foreach (XLRow container in rows) + foreach (XLRow container in _rows) cells.Add(container.RangeAddress); return cells; } @@ -194,21 +192,21 @@ public IXLCells CellsUsed(Boolean includeStyles) { var cells = new XLCells(true, includeStyles); - foreach (XLRow container in rows) + foreach (XLRow container in _rows) cells.Add(container.RangeAddress); return cells; } public IXLRows AddHorizontalPageBreaks() { - foreach (XLRow row in rows) + foreach (XLRow row in _rows) row.Worksheet.PageSetup.AddHorizontalPageBreak(row.RowNumber()); return this; } public IXLRows SetDataType(XLCellValues dataType) { - rows.ForEach(c => c.DataType = dataType); + _rows.ForEach(c => c.DataType = dataType); return this; } @@ -222,14 +220,13 @@ { UpdatingStyle = true; yield return style; - if (worksheet != null) - yield return worksheet.Style; + if (_worksheet != null) + yield return _worksheet.Style; else { - foreach (XLRow row in rows) + foreach (IXLStyle s in _rows.SelectMany(row => row.Styles)) { - foreach (IXLStyle s in row.Styles) - yield return s; + yield return s; } } UpdatingStyle = false; @@ -258,7 +255,7 @@ public void Add(XLRow row) { - rows.Add(row); + _rows.Add(row); } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs index 3435fe6..1e18e3f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs @@ -312,7 +312,7 @@ } else if (m_loadSource == XLLoadSource.File) { - if (m_originalFile.Trim().ToLower() != file.Trim().ToLower()) + if (String.Compare(m_originalFile.Trim(), file.Trim(), true) != 0) { File.Copy(m_originalFile, file, true); } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index c41aba4..1498904 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -220,9 +220,8 @@ xlTable.ShowAutoFilter = dTable.AutoFilter != null; - foreach (OpenXmlElement column in dTable.TableColumns) + foreach (var tableColumn in dTable.TableColumns.Cast()) { - var tableColumn = (TableColumn) column; if (tableColumn.TotalsRowFunction != null) xlTable.Field(tableColumn.Name.Value).TotalsRowFunction = tableColumn.TotalsRowFunction.Value.ToClosedXml(); @@ -244,51 +243,50 @@ if (workbookView != null && workbookView.ActiveTab != null) Worksheet((Int32) (workbookView.ActiveTab.Value + 1)).SetTabActive(); - if (workbook.DefinedNames != null) + if (workbook.DefinedNames == null) return; + + foreach (DefinedName definedName in workbook.DefinedNames) { - foreach (DefinedName definedName in workbook.DefinedNames) + var name = definedName.Name; + if (name == "_xlnm.Print_Area") { - var name = definedName.Name; - if (name == "_xlnm.Print_Area") + foreach (string area in definedName.Text.Split(',')) { - foreach (string area in definedName.Text.Split(',')) - { - var sections = area.Trim().Split('!'); - string sheetName = sections[0].Replace("\'", ""); - string sheetArea = sections[1]; - if (!sheetArea.Equals("#REF")) - WorksheetsInternal.Worksheet(sheetName).PageSetup.PrintAreas.Add(sheetArea); - } + var sections = area.Trim().Split('!'); + string sheetName = sections[0].Replace("\'", ""); + string sheetArea = sections[1]; + if (!sheetArea.Equals("#REF")) + WorksheetsInternal.Worksheet(sheetName).PageSetup.PrintAreas.Add(sheetArea); } - else if (name == "_xlnm.Print_Titles") + } + else if (name == "_xlnm.Print_Titles") + { + var areas = definedName.Text.Split(','); + + var colSections = areas[0].Trim().Split('!'); + string sheetNameCol = colSections[0].Replace("\'", ""); + string sheetAreaCol = colSections[1]; + if (!sheetAreaCol.Equals("#REF")) + WorksheetsInternal.Worksheet(sheetNameCol).PageSetup.SetColumnsToRepeatAtLeft(sheetAreaCol); + + var rowSections = areas[1].Split('!'); + string sheetNameRow = rowSections[0].Replace("\'", ""); + string sheetAreaRow = rowSections[1]; + if (!sheetAreaRow.Equals("#REF")) + WorksheetsInternal.Worksheet(sheetNameRow).PageSetup.SetRowsToRepeatAtTop(sheetAreaRow); + } + else + { + string text = definedName.Text; + + if (!text.Equals("#REF")) { - var areas = definedName.Text.Split(','); - - var colSections = areas[0].Trim().Split('!'); - string sheetNameCol = colSections[0].Replace("\'", ""); - string sheetAreaCol = colSections[1]; - if (!sheetAreaCol.Equals("#REF")) - WorksheetsInternal.Worksheet(sheetNameCol).PageSetup.SetColumnsToRepeatAtLeft(sheetAreaCol); - - var rowSections = areas[1].Split('!'); - string sheetNameRow = rowSections[0].Replace("\'", ""); - string sheetAreaRow = rowSections[1]; - if (!sheetAreaRow.Equals("#REF")) - WorksheetsInternal.Worksheet(sheetNameRow).PageSetup.SetRowsToRepeatAtTop(sheetAreaRow); - } - else - { - string text = definedName.Text; - - if (!text.Equals("#REF")) - { - var localSheetId = definedName.LocalSheetId; - var comment = definedName.Comment; - if (localSheetId == null) - NamedRanges.Add(name, text, comment); - else - Worksheet(Int32.Parse(localSheetId) + 1).NamedRanges.Add(name, text, comment); - } + var localSheetId = definedName.LocalSheetId; + var comment = definedName.Comment; + if (localSheetId == null) + NamedRanges.Add(name, text, comment); + else + Worksheet(Int32.Parse(localSheetId) + 1).NamedRanges.Add(name, text, comment); } } } @@ -361,34 +359,31 @@ var runs = sharedString.Elements(); var phoneticRuns = sharedString.Elements(); var phoneticProperties = sharedString.Elements(); - if (runs.Any()) + Boolean hasRuns = false; + foreach (Run run in runs) { - #region Load Runs + var runProperties = run.RunProperties; + String text = run.Text.InnerText.Replace("\n", Environment.NewLine); - foreach (Run run in runs) + if (runProperties == null) + xlCell.RichText.AddText(text, xlCell.Style.Font); + else { - var runProperties = run.RunProperties; - String text = run.Text.InnerText.Replace("\n", Environment.NewLine); - - if (runProperties == null) - xlCell.RichText.AddText(text, xlCell.Style.Font); - else - { - var rt = xlCell.RichText.AddText(text); - LoadFont(runProperties, rt); - } + var rt = xlCell.RichText.AddText(text); + LoadFont(runProperties, rt); } - - #endregion + if (!hasRuns) + hasRuns = true; } - else + + if(!hasRuns) xlCell._cellValue = sharedString.Text.InnerText; #region Load PhoneticProperties - if (phoneticProperties.Any()) + var pp = phoneticProperties.FirstOrDefault(); + if (pp != null) { - var pp = phoneticProperties.First(); if (pp.Alignment != null) xlCell.RichText.Phonetics.Alignment = pp.Alignment.Value.ToClosedXml(); if (pp.Type != null) @@ -496,20 +491,14 @@ var underline = fontSource.Elements().FirstOrDefault(); if (underline != null) { - if (underline.Val != null) - fontBase.Underline = underline.Val.Value.ToClosedXml(); - else - fontBase.Underline = XLFontUnderlineValues.Single; + fontBase.Underline = underline.Val != null ? underline.Val.Value.ToClosedXml() : XLFontUnderlineValues.Single; } var verticalTextAlignment = fontSource.Elements().FirstOrDefault(); - if (verticalTextAlignment != null) - { - if (verticalTextAlignment.Val != null) - fontBase.VerticalAlignment = verticalTextAlignment.Val.Value.ToClosedXml(); - else - fontBase.VerticalAlignment = XLFontVerticalTextAlignmentValues.Baseline; - } + + if (verticalTextAlignment == null) return; + + fontBase.VerticalAlignment = verticalTextAlignment.Val != null ? verticalTextAlignment.Val.Value.ToClosedXml() : XLFontVerticalTextAlignmentValues.Baseline; } private void LoadRows(Stylesheet s, NumberingFormats numberingFormats, Fills fills, Borders borders, Fonts fonts, @@ -517,7 +506,7 @@ Dictionary sharedFormulasR1C1, Dictionary styleList, Row row) { - var xlRow = (XLRow) ws.Row((Int32) row.RowIndex.Value, false); + var xlRow = ws.Row((Int32) row.RowIndex.Value, false); if (row.Height != null) xlRow.Height = row.Height; else @@ -563,51 +552,52 @@ private void LoadColumns(Stylesheet s, NumberingFormats numberingFormats, Fills fills, Borders borders, Fonts fonts, XLWorksheet ws, Columns columns) { - if (columns != null) + if (columns == null) return; + + var wsDefaultColumn = + columns.Elements().Where(c => c.Max == ExcelHelper.MaxColumnNumber).FirstOrDefault(); + + if (wsDefaultColumn != null && wsDefaultColumn.Width != null) + ws.ColumnWidth = wsDefaultColumn.Width - ColumnWidthOffset; + + Int32 styleIndexDefault = wsDefaultColumn != null && wsDefaultColumn.Style != null + ? Int32.Parse(wsDefaultColumn.Style.InnerText) + : -1; + if (styleIndexDefault >= 0) + ApplyStyle(ws, styleIndexDefault, s, fills, borders, fonts, numberingFormats); + + foreach (Column col in columns.Elements()) { - var wsDefaultColumn = - columns.Elements().Where(c => c.Max == ExcelHelper.MaxColumnNumber).FirstOrDefault(); + //IXLStylized toApply; + if (col.Max == ExcelHelper.MaxColumnNumber) continue; - if (wsDefaultColumn != null && wsDefaultColumn.Width != null) - ws.ColumnWidth = wsDefaultColumn.Width - ColumnWidthOffset; + var xlColumns = (XLColumns) ws.Columns(col.Min, col.Max); + if (col.Width != null) + xlColumns.Width = col.Width - ColumnWidthOffset; + else + xlColumns.Width = ws.ColumnWidth; - Int32 styleIndexDefault = wsDefaultColumn != null && wsDefaultColumn.Style != null - ? Int32.Parse(wsDefaultColumn.Style.InnerText) - : -1; - if (styleIndexDefault >= 0) - ApplyStyle(ws, styleIndexDefault, s, fills, borders, fonts, numberingFormats); + if (col.Hidden != null && col.Hidden) + xlColumns.Hide(); - foreach (Column col in columns.Elements()) + if (col.Collapsed != null && col.Collapsed) + xlColumns.CollapseOnly(); + + if (col.OutlineLevel != null) { - //IXLStylized toApply; - if (col.Max != ExcelHelper.MaxColumnNumber) - { - var xlColumns = (XLColumns) ws.Columns(col.Min, col.Max); - if (col.Width != null) - xlColumns.Width = col.Width - ColumnWidthOffset; - else - xlColumns.Width = ws.ColumnWidth; - - if (col.Hidden != null && col.Hidden) - xlColumns.Hide(); - - if (col.Collapsed != null && col.Collapsed) - xlColumns.CollapseOnly(); - - if (col.OutlineLevel != null) - xlColumns.ForEach(c => c.OutlineLevel = col.OutlineLevel); - - Int32 styleIndex = col.Style != null ? Int32.Parse(col.Style.InnerText) : -1; - if (styleIndex > 0) - ApplyStyle(xlColumns, styleIndex, s, fills, borders, fonts, numberingFormats); - else - xlColumns.Style = DefaultStyle; - } + var outlineLevel = col.OutlineLevel; + xlColumns.ForEach(c => c.OutlineLevel = outlineLevel); } + + Int32 styleIndex = col.Style != null ? Int32.Parse(col.Style.InnerText) : -1; + if (styleIndex > 0) + ApplyStyle(xlColumns, styleIndex, s, fills, borders, fonts, numberingFormats); + else + xlColumns.Style = DefaultStyle; } } - private XLCellValues GetDataTypeFromFormat(String format) + private static XLCellValues GetDataTypeFromFormat(String format) { int length = format.Length; String f = format.ToLower(); @@ -624,269 +614,249 @@ return XLCellValues.Text; } - private void LoadAutoFilter(AutoFilter af, XLWorksheet ws) + private static void LoadAutoFilter(AutoFilter af, XLWorksheet ws) { if (af != null) ws.Range(af.Reference.Value).SetAutoFilter(); } - private void LoadSheetProtection(SheetProtection sp, XLWorksheet ws) + private static void LoadSheetProtection(SheetProtection sp, XLWorksheet ws) { - if (sp != null) - { - if (sp.Sheet != null) ws.Protection.Protected = sp.Sheet.Value; - if (sp.Password != null) (ws.Protection as XLSheetProtection).PasswordHash = sp.Password.Value; - if (sp.FormatCells != null) ws.Protection.FormatCells = sp.FormatCells.Value; - if (sp.FormatColumns != null) ws.Protection.FormatColumns = sp.FormatColumns.Value; - if (sp.FormatRows != null) ws.Protection.FormatRows = sp.FormatRows.Value; - if (sp.InsertColumns != null) ws.Protection.InsertColumns = sp.InsertColumns.Value; - if (sp.InsertHyperlinks != null) ws.Protection.InsertHyperlinks = sp.InsertHyperlinks.Value; - if (sp.InsertRows != null) ws.Protection.InsertRows = sp.InsertRows.Value; - if (sp.DeleteColumns != null) ws.Protection.DeleteColumns = sp.DeleteColumns.Value; - if (sp.DeleteRows != null) ws.Protection.DeleteRows = sp.DeleteRows.Value; - if (sp.AutoFilter != null) ws.Protection.AutoFilter = sp.AutoFilter.Value; - if (sp.PivotTables != null) ws.Protection.PivotTables = sp.PivotTables.Value; - if (sp.Sort != null) ws.Protection.Sort = sp.Sort.Value; - if (sp.SelectLockedCells != null) ws.Protection.SelectLockedCells = !sp.SelectLockedCells.Value; - if (sp.SelectUnlockedCells != null) ws.Protection.SelectUnlockedCells = !sp.SelectUnlockedCells.Value; - } + if (sp == null) return; + + if (sp.Sheet != null) ws.Protection.Protected = sp.Sheet.Value; + if (sp.Password != null) ws.Protection.PasswordHash = sp.Password.Value; + if (sp.FormatCells != null) ws.Protection.FormatCells = sp.FormatCells.Value; + if (sp.FormatColumns != null) ws.Protection.FormatColumns = sp.FormatColumns.Value; + if (sp.FormatRows != null) ws.Protection.FormatRows = sp.FormatRows.Value; + if (sp.InsertColumns != null) ws.Protection.InsertColumns = sp.InsertColumns.Value; + if (sp.InsertHyperlinks != null) ws.Protection.InsertHyperlinks = sp.InsertHyperlinks.Value; + if (sp.InsertRows != null) ws.Protection.InsertRows = sp.InsertRows.Value; + if (sp.DeleteColumns != null) ws.Protection.DeleteColumns = sp.DeleteColumns.Value; + if (sp.DeleteRows != null) ws.Protection.DeleteRows = sp.DeleteRows.Value; + if (sp.AutoFilter != null) ws.Protection.AutoFilter = sp.AutoFilter.Value; + if (sp.PivotTables != null) ws.Protection.PivotTables = sp.PivotTables.Value; + if (sp.Sort != null) ws.Protection.Sort = sp.Sort.Value; + if (sp.SelectLockedCells != null) ws.Protection.SelectLockedCells = !sp.SelectLockedCells.Value; + if (sp.SelectUnlockedCells != null) ws.Protection.SelectUnlockedCells = !sp.SelectUnlockedCells.Value; } - private void LoadDataValidations(DataValidations dataValidations, XLWorksheet ws) + private static void LoadDataValidations(DataValidations dataValidations, XLWorksheet ws) { - if (dataValidations != null) + if (dataValidations == null) return; + + foreach (DataValidation dvs in dataValidations.Elements()) { - foreach (DataValidation dvs in dataValidations.Elements()) + foreach (var dvt in dvs.SequenceOfReferences.InnerText.Split(' ').Select(rangeAddress => ws.Range(rangeAddress).DataValidation)) { - foreach (String rangeAddress in dvs.SequenceOfReferences.InnerText.Split(' ')) - { - var dvt = ws.Range(rangeAddress).DataValidation; - if (dvs.AllowBlank != null) dvt.IgnoreBlanks = dvs.AllowBlank; - if (dvs.ShowDropDown != null) dvt.InCellDropdown = !dvs.ShowDropDown.Value; - if (dvs.ShowErrorMessage != null) dvt.ShowErrorMessage = dvs.ShowErrorMessage; - if (dvs.ShowInputMessage != null) dvt.ShowInputMessage = dvs.ShowInputMessage; - if (dvs.PromptTitle != null) dvt.InputTitle = dvs.PromptTitle; - if (dvs.Prompt != null) dvt.InputMessage = dvs.Prompt; - if (dvs.ErrorTitle != null) dvt.ErrorTitle = dvs.ErrorTitle; - if (dvs.Error != null) dvt.ErrorMessage = dvs.Error; - if (dvs.ErrorStyle != null) dvt.ErrorStyle = dvs.ErrorStyle.Value.ToClosedXml(); - if (dvs.Type != null) dvt.AllowedValues = dvs.Type.Value.ToClosedXml(); - if (dvs.Operator != null) dvt.Operator = dvs.Operator.Value.ToClosedXml(); - if (dvs.Formula1 != null) dvt.MinValue = dvs.Formula1.Text; - if (dvs.Formula2 != null) dvt.MaxValue = dvs.Formula2.Text; - } + if (dvs.AllowBlank != null) dvt.IgnoreBlanks = dvs.AllowBlank; + if (dvs.ShowDropDown != null) dvt.InCellDropdown = !dvs.ShowDropDown.Value; + if (dvs.ShowErrorMessage != null) dvt.ShowErrorMessage = dvs.ShowErrorMessage; + if (dvs.ShowInputMessage != null) dvt.ShowInputMessage = dvs.ShowInputMessage; + if (dvs.PromptTitle != null) dvt.InputTitle = dvs.PromptTitle; + if (dvs.Prompt != null) dvt.InputMessage = dvs.Prompt; + if (dvs.ErrorTitle != null) dvt.ErrorTitle = dvs.ErrorTitle; + if (dvs.Error != null) dvt.ErrorMessage = dvs.Error; + if (dvs.ErrorStyle != null) dvt.ErrorStyle = dvs.ErrorStyle.Value.ToClosedXml(); + if (dvs.Type != null) dvt.AllowedValues = dvs.Type.Value.ToClosedXml(); + if (dvs.Operator != null) dvt.Operator = dvs.Operator.Value.ToClosedXml(); + if (dvs.Formula1 != null) dvt.MinValue = dvs.Formula1.Text; + if (dvs.Formula2 != null) dvt.MaxValue = dvs.Formula2.Text; } } } - private void LoadHyperlinks(Hyperlinks hyperlinks, WorksheetPart worksheetPart, XLWorksheet ws) + private static void LoadHyperlinks(Hyperlinks hyperlinks, WorksheetPart worksheetPart, XLWorksheet ws) { var hyperlinkDictionary = new Dictionary(); if (worksheetPart.HyperlinkRelationships != null) hyperlinkDictionary = worksheetPart.HyperlinkRelationships.ToDictionary(hr => hr.Id, hr => hr.Uri); - if (hyperlinks != null) + if (hyperlinks == null) return; + + foreach (Hyperlink hl in hyperlinks.Elements()) { - foreach (Hyperlink hl in hyperlinks.Elements()) + if (hl.Reference.Value.Equals("#REF")) continue; + String tooltip = hl.Tooltip != null ? hl.Tooltip.Value : String.Empty; + var xlRange = ws.Range(hl.Reference.Value); + foreach (XLCell xlCell in xlRange.Cells()) { - if (!hl.Reference.Value.Equals("#REF")) - { - String tooltip = hl.Tooltip != null ? hl.Tooltip.Value : String.Empty; - var xlRange = ws.Range(hl.Reference.Value); - foreach (XLCell xlCell in xlRange.Cells()) - { - xlCell.SettingHyperlink = true; - if (hl.Id != null) - xlCell.Hyperlink = new XLHyperlink(hyperlinkDictionary[hl.Id], tooltip); - else - xlCell.Hyperlink = new XLHyperlink(hl.Location.Value, tooltip); - xlCell.SettingHyperlink = false; - } - } + xlCell.SettingHyperlink = true; + xlCell.Hyperlink = hl.Id != null ? new XLHyperlink(hyperlinkDictionary[hl.Id], tooltip) : new XLHyperlink(hl.Location.Value, tooltip); + xlCell.SettingHyperlink = false; } } } - private void LoadColumnBreaks(ColumnBreaks columnBreaks, XLWorksheet ws) + private static void LoadColumnBreaks(ColumnBreaks columnBreaks, XLWorksheet ws) { - if (columnBreaks != null) + if (columnBreaks == null) return; + + foreach (Break columnBreak in columnBreaks.Elements().Where(columnBreak => columnBreak.Id != null)) { - foreach (Break columnBreak in columnBreaks.Elements()) - { - if (columnBreak.Id != null) - ws.PageSetup.ColumnBreaks.Add(Int32.Parse(columnBreak.Id.InnerText)); - } + ws.PageSetup.ColumnBreaks.Add(Int32.Parse(columnBreak.Id.InnerText)); } } - private void LoadRowBreaks(RowBreaks rowBreaks, XLWorksheet ws) + private static void LoadRowBreaks(RowBreaks rowBreaks, XLWorksheet ws) { - if (rowBreaks != null) - { - foreach (Break rowBreak in rowBreaks.Elements()) - ws.PageSetup.RowBreaks.Add(Int32.Parse(rowBreak.Id.InnerText)); - } + if (rowBreaks == null) return; + + foreach (Break rowBreak in rowBreaks.Elements()) + ws.PageSetup.RowBreaks.Add(Int32.Parse(rowBreak.Id.InnerText)); } private void LoadSheetProperties(SheetProperties sheetProperty, XLWorksheet ws) { - if (sheetProperty != null) + if (sheetProperty == null) return; + + if (sheetProperty.TabColor != null) + ws.TabColor = GetColor(sheetProperty.TabColor); + + if (sheetProperty.OutlineProperties == null) return; + + if (sheetProperty.OutlineProperties.SummaryBelow != null) { - if (sheetProperty.TabColor != null) - ws.TabColor = GetColor(sheetProperty.TabColor); + ws.Outline.SummaryVLocation = sheetProperty.OutlineProperties.SummaryBelow + ? XLOutlineSummaryVLocation.Bottom + : XLOutlineSummaryVLocation.Top; + } - if (sheetProperty.OutlineProperties != null) - { - if (sheetProperty.OutlineProperties.SummaryBelow != null) - { - ws.Outline.SummaryVLocation = sheetProperty.OutlineProperties.SummaryBelow - ? XLOutlineSummaryVLocation.Bottom - : XLOutlineSummaryVLocation.Top; - } - - if (sheetProperty.OutlineProperties.SummaryRight != null) - { - ws.Outline.SummaryHLocation = sheetProperty.OutlineProperties.SummaryRight - ? XLOutlineSummaryHLocation.Right - : XLOutlineSummaryHLocation.Left; - } - } + if (sheetProperty.OutlineProperties.SummaryRight != null) + { + ws.Outline.SummaryHLocation = sheetProperty.OutlineProperties.SummaryRight + ? XLOutlineSummaryHLocation.Right + : XLOutlineSummaryHLocation.Left; } } - private void LoadHeaderFooter(HeaderFooter headerFooter, XLWorksheet ws) + private static void LoadHeaderFooter(HeaderFooter headerFooter, XLWorksheet ws) { - if (headerFooter != null) - { - if (headerFooter.AlignWithMargins != null) - ws.PageSetup.AlignHFWithMargins = headerFooter.AlignWithMargins; - if (headerFooter.ScaleWithDoc != null) - ws.PageSetup.ScaleHFWithDocument = headerFooter.ScaleWithDoc; + if (headerFooter == null) return; - // Footers - var xlFooter = (XLHeaderFooter) ws.PageSetup.Footer; - var evenFooter = headerFooter.EvenFooter; - if (evenFooter != null) - xlFooter.SetInnerText(XLHFOccurrence.EvenPages, evenFooter.Text); - var oddFooter = headerFooter.OddFooter; - if (oddFooter != null) - xlFooter.SetInnerText(XLHFOccurrence.OddPages, oddFooter.Text); - var firstFooter = headerFooter.FirstFooter; - if (firstFooter != null) - xlFooter.SetInnerText(XLHFOccurrence.FirstPage, firstFooter.Text); - // Headers - var xlHeader = (XLHeaderFooter) ws.PageSetup.Header; - var evenHeader = headerFooter.EvenHeader; - if (evenHeader != null) - xlHeader.SetInnerText(XLHFOccurrence.EvenPages, evenHeader.Text); - var oddHeader = headerFooter.OddHeader; - if (oddHeader != null) - xlHeader.SetInnerText(XLHFOccurrence.OddPages, oddHeader.Text); - var firstHeader = headerFooter.FirstHeader; - if (firstHeader != null) - xlHeader.SetInnerText(XLHFOccurrence.FirstPage, firstHeader.Text); - } + if (headerFooter.AlignWithMargins != null) + ws.PageSetup.AlignHFWithMargins = headerFooter.AlignWithMargins; + if (headerFooter.ScaleWithDoc != null) + ws.PageSetup.ScaleHFWithDocument = headerFooter.ScaleWithDoc; + + // Footers + var xlFooter = (XLHeaderFooter) ws.PageSetup.Footer; + var evenFooter = headerFooter.EvenFooter; + if (evenFooter != null) + xlFooter.SetInnerText(XLHFOccurrence.EvenPages, evenFooter.Text); + var oddFooter = headerFooter.OddFooter; + if (oddFooter != null) + xlFooter.SetInnerText(XLHFOccurrence.OddPages, oddFooter.Text); + var firstFooter = headerFooter.FirstFooter; + if (firstFooter != null) + xlFooter.SetInnerText(XLHFOccurrence.FirstPage, firstFooter.Text); + // Headers + var xlHeader = (XLHeaderFooter) ws.PageSetup.Header; + var evenHeader = headerFooter.EvenHeader; + if (evenHeader != null) + xlHeader.SetInnerText(XLHFOccurrence.EvenPages, evenHeader.Text); + var oddHeader = headerFooter.OddHeader; + if (oddHeader != null) + xlHeader.SetInnerText(XLHFOccurrence.OddPages, oddHeader.Text); + var firstHeader = headerFooter.FirstHeader; + if (firstHeader != null) + xlHeader.SetInnerText(XLHFOccurrence.FirstPage, firstHeader.Text); } - private void LoadPageSetup(PageSetup pageSetup, XLWorksheet ws) + private static void LoadPageSetup(PageSetup pageSetup, XLWorksheet ws) { - if (pageSetup != null) + if (pageSetup == null) return; + + if (pageSetup.PaperSize != null) + ws.PageSetup.PaperSize = (XLPaperSize) Int32.Parse(pageSetup.PaperSize.InnerText); + if (pageSetup.Scale != null) + ws.PageSetup.Scale = Int32.Parse(pageSetup.Scale.InnerText); + else { - if (pageSetup.PaperSize != null) - ws.PageSetup.PaperSize = (XLPaperSize) Int32.Parse(pageSetup.PaperSize.InnerText); - if (pageSetup.Scale != null) - ws.PageSetup.Scale = Int32.Parse(pageSetup.Scale.InnerText); - else - { - if (pageSetup.FitToWidth != null) - ws.PageSetup.PagesWide = Int32.Parse(pageSetup.FitToWidth.InnerText); - if (pageSetup.FitToHeight != null) - ws.PageSetup.PagesTall = Int32.Parse(pageSetup.FitToHeight.InnerText); - } - if (pageSetup.PageOrder != null) - ws.PageSetup.PageOrder = pageSetup.PageOrder.Value.ToClosedXml(); - if (pageSetup.Orientation != null) - ws.PageSetup.PageOrientation = pageSetup.Orientation.Value.ToClosedXml(); - if (pageSetup.BlackAndWhite != null) - ws.PageSetup.BlackAndWhite = pageSetup.BlackAndWhite; - if (pageSetup.Draft != null) - ws.PageSetup.DraftQuality = pageSetup.Draft; - if (pageSetup.CellComments != null) - ws.PageSetup.ShowComments = pageSetup.CellComments.Value.ToClosedXml(); - if (pageSetup.Errors != null) - ws.PageSetup.PrintErrorValue = pageSetup.Errors.Value.ToClosedXml(); - if (pageSetup.HorizontalDpi != null) ws.PageSetup.HorizontalDpi = (Int32) pageSetup.HorizontalDpi.Value; - if (pageSetup.VerticalDpi != null) ws.PageSetup.VerticalDpi = (Int32) pageSetup.VerticalDpi.Value; - if (pageSetup.FirstPageNumber != null) - ws.PageSetup.FirstPageNumber = Int32.Parse(pageSetup.FirstPageNumber.InnerText); + if (pageSetup.FitToWidth != null) + ws.PageSetup.PagesWide = Int32.Parse(pageSetup.FitToWidth.InnerText); + if (pageSetup.FitToHeight != null) + ws.PageSetup.PagesTall = Int32.Parse(pageSetup.FitToHeight.InnerText); } + if (pageSetup.PageOrder != null) + ws.PageSetup.PageOrder = pageSetup.PageOrder.Value.ToClosedXml(); + if (pageSetup.Orientation != null) + ws.PageSetup.PageOrientation = pageSetup.Orientation.Value.ToClosedXml(); + if (pageSetup.BlackAndWhite != null) + ws.PageSetup.BlackAndWhite = pageSetup.BlackAndWhite; + if (pageSetup.Draft != null) + ws.PageSetup.DraftQuality = pageSetup.Draft; + if (pageSetup.CellComments != null) + ws.PageSetup.ShowComments = pageSetup.CellComments.Value.ToClosedXml(); + if (pageSetup.Errors != null) + ws.PageSetup.PrintErrorValue = pageSetup.Errors.Value.ToClosedXml(); + if (pageSetup.HorizontalDpi != null) ws.PageSetup.HorizontalDpi = (Int32) pageSetup.HorizontalDpi.Value; + if (pageSetup.VerticalDpi != null) ws.PageSetup.VerticalDpi = (Int32) pageSetup.VerticalDpi.Value; + if (pageSetup.FirstPageNumber != null) + ws.PageSetup.FirstPageNumber = Int32.Parse(pageSetup.FirstPageNumber.InnerText); } - private void LoadPageMargins(PageMargins pageMargins, XLWorksheet ws) + private static void LoadPageMargins(PageMargins pageMargins, XLWorksheet ws) { - if (pageMargins != null) - { - if (pageMargins.Bottom != null) - ws.PageSetup.Margins.Bottom = pageMargins.Bottom; - if (pageMargins.Footer != null) - ws.PageSetup.Margins.Footer = pageMargins.Footer; - if (pageMargins.Header != null) - ws.PageSetup.Margins.Header = pageMargins.Header; - if (pageMargins.Left != null) - ws.PageSetup.Margins.Left = pageMargins.Left; - if (pageMargins.Right != null) - ws.PageSetup.Margins.Right = pageMargins.Right; - if (pageMargins.Top != null) - ws.PageSetup.Margins.Top = pageMargins.Top; - } + if (pageMargins == null) return; + + if (pageMargins.Bottom != null) + ws.PageSetup.Margins.Bottom = pageMargins.Bottom; + if (pageMargins.Footer != null) + ws.PageSetup.Margins.Footer = pageMargins.Footer; + if (pageMargins.Header != null) + ws.PageSetup.Margins.Header = pageMargins.Header; + if (pageMargins.Left != null) + ws.PageSetup.Margins.Left = pageMargins.Left; + if (pageMargins.Right != null) + ws.PageSetup.Margins.Right = pageMargins.Right; + if (pageMargins.Top != null) + ws.PageSetup.Margins.Top = pageMargins.Top; } - private void LoadPrintOptions(PrintOptions printOptions, XLWorksheet ws) + private static void LoadPrintOptions(PrintOptions printOptions, XLWorksheet ws) { - if (printOptions != null) - { - if (printOptions.GridLines != null) - ws.PageSetup.ShowGridlines = printOptions.GridLines; - if (printOptions.HorizontalCentered != null) - ws.PageSetup.CenterHorizontally = printOptions.HorizontalCentered; - if (printOptions.VerticalCentered != null) - ws.PageSetup.CenterVertically = printOptions.VerticalCentered; - if (printOptions.Headings != null) - ws.PageSetup.ShowRowAndColumnHeadings = printOptions.Headings; - } + if (printOptions == null) return; + + if (printOptions.GridLines != null) + ws.PageSetup.ShowGridlines = printOptions.GridLines; + if (printOptions.HorizontalCentered != null) + ws.PageSetup.CenterHorizontally = printOptions.HorizontalCentered; + if (printOptions.VerticalCentered != null) + ws.PageSetup.CenterVertically = printOptions.VerticalCentered; + if (printOptions.Headings != null) + ws.PageSetup.ShowRowAndColumnHeadings = printOptions.Headings; } - private void LoadSheetViews(SheetViews sheetViews, XLWorksheet ws) + private static void LoadSheetViews(SheetViews sheetViews, XLWorksheet ws) { - if (sheetViews != null) - { - var sheetView = sheetViews.Elements().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; - if (sheetView.TabSelected != null) ws.TabSelected = sheetView.TabSelected.Value; + if (sheetViews == null) return; - var pane = sheetView.Elements().FirstOrDefault(); - if (pane != null) - { - if (pane.State != null && - (pane.State == PaneStateValues.FrozenSplit || pane.State == PaneStateValues.Frozen)) - { - if (pane.HorizontalSplit != null) - ws.SheetView.SplitColumn = (Int32) pane.HorizontalSplit.Value; - if (pane.VerticalSplit != null) - ws.SheetView.SplitRow = (Int32) pane.VerticalSplit.Value; - } - } - } - } + var sheetView = sheetViews.Elements().FirstOrDefault(); + + if (sheetView == null) return; + + 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; + if (sheetView.TabSelected != null) ws.TabSelected = sheetView.TabSelected.Value; + + var pane = sheetView.Elements().FirstOrDefault(); + + if (pane == null) return; + + if (pane.State == null || + (pane.State != PaneStateValues.FrozenSplit && pane.State != PaneStateValues.Frozen)) return; + + if (pane.HorizontalSplit != null) + ws.SheetView.SplitColumn = (Int32) pane.HorizontalSplit.Value; + if (pane.VerticalSplit != null) + ws.SheetView.SplitRow = (Int32) pane.VerticalSplit.Value; } private void SetProperties(SpreadsheetDocument dSpreadsheet) @@ -926,16 +896,10 @@ retVal = new XLColor((Int32) color.Indexed.Value); else if (color.Theme != null) { - if (color.Tint != null) - retVal = XLColor.FromTheme((XLThemeColor) color.Theme.Value, color.Tint.Value); - else - retVal = XLColor.FromTheme((XLThemeColor) color.Theme.Value); + retVal = color.Tint != null ? XLColor.FromTheme((XLThemeColor) color.Theme.Value, color.Tint.Value) : XLColor.FromTheme((XLThemeColor) color.Theme.Value); } } - if (retVal == null) - return new XLColor(); - else - return retVal; + return retVal ?? new XLColor(); } private void ApplyStyle(IXLStylized xlStylized, Int32 styleIndex, Stylesheet s, Fills fills, Borders borders, @@ -1101,59 +1065,47 @@ if (font.Underline != null) { - if (font.Underline.Val != null) - xlStylized.InnerStyle.Font.Underline = (font.Underline).Val.Value.ToClosedXml(); - else - xlStylized.InnerStyle.Font.Underline = XLFontUnderlineValues.Single; + xlStylized.InnerStyle.Font.Underline = font.Underline.Val != null ? (font.Underline).Val.Value.ToClosedXml() : XLFontUnderlineValues.Single; } if (font.VerticalTextAlignment != null) { - if (font.VerticalTextAlignment.Val != null) - xlStylized.InnerStyle.Font.VerticalAlignment = - (font.VerticalTextAlignment).Val.Value.ToClosedXml(); - else - xlStylized.InnerStyle.Font.VerticalAlignment = XLFontVerticalTextAlignmentValues.Baseline; + xlStylized.InnerStyle.Font.VerticalAlignment = font.VerticalTextAlignment.Val != null ? (font.VerticalTextAlignment).Val.Value.ToClosedXml() : XLFontVerticalTextAlignmentValues.Baseline; } } var numberFormatId = cellFormat.NumberFormatId; - if (numberFormatId != null) - { - string formatCode = String.Empty; - if (numberingFormats != null) - { - var numberFormatList = - numberingFormats.Where( - nf => - ((NumberingFormat) nf).NumberFormatId != null && - ((NumberingFormat) nf).NumberFormatId.Value == numberFormatId); - if (numberFormatList.Count() > 0) - { - var numberingFormat = (NumberingFormat) numberFormatList.First(); - if (numberingFormat.FormatCode != null) - formatCode = numberingFormat.FormatCode.Value; - } - } - if (formatCode.Length > 0) - xlStylized.InnerStyle.NumberFormat.Format = formatCode; - else - xlStylized.InnerStyle.NumberFormat.NumberFormatId = (Int32) numberFormatId.Value; + if (numberFormatId == null) return; + + string formatCode = String.Empty; + if (numberingFormats != null) + { + var numberingFormat = + numberingFormats.FirstOrDefault( + nf => + ((NumberingFormat) nf).NumberFormatId != null && + ((NumberingFormat) nf).NumberFormatId.Value == numberFormatId) as NumberingFormat; + + if (numberingFormat != null && numberingFormat.FormatCode != null) + formatCode = numberingFormat.FormatCode.Value; } + if (formatCode.Length > 0) + xlStylized.InnerStyle.NumberFormat.Format = formatCode; + else + xlStylized.InnerStyle.NumberFormat.NumberFormatId = (Int32) numberFormatId.Value; } - private Boolean GetBoolean(BooleanPropertyType property) + private static Boolean GetBoolean(BooleanPropertyType property) { if (property != null) { if (property.Val != null) return property.Val; - else - return true; + return true; } - else - return false; + + return false; } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 0ed2618..2dde39b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -174,16 +174,16 @@ private static void GenerateTables(XLWorksheet worksheet, WorksheetPart worksheetPart, SaveContext context) { worksheetPart.Worksheet.RemoveAllChildren(); - if (worksheet.Tables.Any()) + + if (!worksheet.Tables.Any()) return; + + foreach (IXLTable table in worksheet.Tables) { - foreach (IXLTable table in worksheet.Tables) - { - string tableRelId = context.RelIdGenerator.GetNext(RelType.Workbook); - var xlTable = (XLTable)table; - xlTable.RelId = tableRelId; - var tableDefinitionPart = worksheetPart.AddNewPart(tableRelId); - GenerateTableDefinitionPartContent(tableDefinitionPart, xlTable, context); - } + string tableRelId = context.RelIdGenerator.GetNext(RelType.Workbook); + var xlTable = (XLTable)table; + xlTable.RelId = tableRelId; + var tableDefinitionPart = worksheetPart.AddNewPart(tableRelId); + GenerateTableDefinitionPartContent(tableDefinitionPart, xlTable, context); } } @@ -227,25 +227,20 @@ var modifiedWorksheets = ((IEnumerable)WorksheetsInternal).Select(w => new {w.Name, Order = w.Position}).ToList(); var modifiedNamedRanges = GetModifiedNamedRanges(); - int modifiedWorksheetsCount = modifiedWorksheets.Count(); - int modifiedNamedRangesCount = modifiedNamedRanges.Count(); + int modifiedWorksheetsCount = modifiedWorksheets.Count; + int modifiedNamedRangesCount = modifiedNamedRanges.Count; InsertOnVtVector(vTVectorOne, "Worksheets", 0, modifiedWorksheetsCount.ToString()); InsertOnVtVector(vTVectorOne, "Named Ranges", 2, modifiedNamedRangesCount.ToString()); vTVectorTwo.Size = (UInt32)(modifiedNamedRangesCount + modifiedWorksheetsCount); - foreach (var w in modifiedWorksheets.OrderBy(w => w.Order)) - { - var vTlpstr3 = new VTLPSTR {Text = w.Name}; + foreach ( + VTLPSTR vTlpstr3 in modifiedWorksheets.OrderBy(w => w.Order).Select(w => new VTLPSTR {Text = w.Name})) vTVectorTwo.AppendChild(vTlpstr3); - } - foreach (string nr in modifiedNamedRanges) - { - var vTlpstr7 = new VTLPSTR {Text = nr}; + foreach (VTLPSTR vTlpstr7 in modifiedNamedRanges.Select(nr => new VTLPSTR {Text = nr})) vTVectorTwo.AppendChild(vTlpstr7); - } if (Properties.Manager != null) { @@ -260,18 +255,17 @@ properties.Manager = null; } - if (Properties.Company != null) - { - if (!StringExtensions.IsNullOrWhiteSpace(Properties.Company)) - { - if (properties.Company == null) - properties.Company = new Company(); + if (Properties.Company == null) return; - properties.Company.Text = Properties.Company; - } - else - properties.Company = null; + if (!StringExtensions.IsNullOrWhiteSpace(Properties.Company)) + { + if (properties.Company == null) + properties.Company = new Company(); + + properties.Company.Text = Properties.Company; } + else + properties.Company = null; } private static void InsertOnVtVector(VTVector vTVector, String property, Int32 index, String text) @@ -308,7 +302,7 @@ } } - private IEnumerable GetModifiedNamedRanges() + private List GetModifiedNamedRanges() { var namedRanges = new List(); foreach (XLWorksheet w in WorksheetsInternal) @@ -356,18 +350,19 @@ workbook.Sheets = new Sheets(); var worksheets = WorksheetsInternal; - workbook.Sheets.Elements().Where(s => worksheets.Deleted.Contains(s.Id)).ToList().ForEach(s => s.Remove()); + workbook.Sheets.Elements().Where(s => worksheets.Deleted.Contains(s.Id)).ToList().ForEach( + s => s.Remove()); foreach (Sheet sheet in workbook.Sheets.Elements()) { - var sheetId = (Int32)sheet.SheetId.Value; - if (WorksheetsInternal.Any(w => (w).SheetId == sheetId)) - { - var wks = - WorksheetsInternal.Where(w => (w).SheetId == sheetId).Single(); - wks.RelId = sheet.Id; - sheet.Name = wks.Name; - } + int sheetId = (Int32)sheet.SheetId.Value; + + if (!WorksheetsInternal.Any(w => (w).SheetId == sheetId)) continue; + + var wks = + WorksheetsInternal.Where(w => (w).SheetId == sheetId).Single(); + wks.RelId = sheet.Id; + sheet.Name = wks.Name; } foreach ( @@ -407,13 +402,12 @@ workbook.Sheets.RemoveChild(sheet); workbook.Sheets.AppendChild(sheet); - if (!foundVisible) - { - if (sheet.State == null || sheet.State == SheetStateValues.Visible) - foundVisible = true; - else - firstSheetVisible++; - } + if (foundVisible) continue; + + if (sheet.State == null || sheet.State == SheetStateValues.Visible) + foundVisible = true; + else + firstSheetVisible++; } var workbookView = workbook.BookViews.Elements().FirstOrDefault(); @@ -421,11 +415,10 @@ UInt32 activeTab = firstSheetVisible; foreach (XLWorksheet ws in worksheets) { - if (ws.TabActive) - { - activeTab = (UInt32)(ws.Position - 1); - break; - } + if (!ws.TabActive) continue; + + activeTab = (UInt32)(ws.Position - 1); + break; } if (workbookView == null) @@ -442,11 +435,10 @@ var definedNames = new DefinedNames(); foreach (XLWorksheet worksheet in WorksheetsInternal) { + uint wsSheetId = (UInt32)worksheet.SheetId; UInt32 sheetId = 0; - foreach (Sheet s in workbook.Sheets.Elements()) + foreach (Sheet s in workbook.Sheets.Elements().TakeWhile(s => s.SheetId != wsSheetId)) { - if (s.SheetId == (UInt32)worksheet.SheetId) - break; sheetId++; } @@ -508,16 +500,16 @@ else titles = definedNameTextRow; - if (titles.Length > 0) - { - var definedName = new DefinedName - { - Name = "_xlnm.Print_Titles", - LocalSheetId = sheetId, - Text = titles - }; - definedNames.AppendChild(definedName); - } + if (titles.Length <= 0) continue; + + var definedName2 = new DefinedName + { + Name = "_xlnm.Print_Titles", + LocalSheetId = sheetId, + Text = titles + }; + + definedNames.AppendChild(definedName2); } foreach (IXLNamedRange nr in NamedRanges) @@ -537,36 +529,20 @@ foreach (DefinedName dn in definedNames) { - String dnName = dn.Name.Value.ToLower(); + String dnName = dn.Name.Value; var dnLocalSheetId = dn.LocalSheetId; - if (workbook.DefinedNames.Elements().Any(d => - d.Name.Value.ToLower() == dnName - && ( - (d.LocalSheetId != null && - dnLocalSheetId != null && - d.LocalSheetId.InnerText == - dnLocalSheetId.InnerText) - || d.LocalSheetId == null || - dnLocalSheetId == null) - )) + var existingDefinedName = workbook.DefinedNames + .Elements() + .FirstOrDefault(d => + String.Compare(d.Name.Value, dnName, true) == 0 + && ( + (d.LocalSheetId != null && dnLocalSheetId != null && + d.LocalSheetId.InnerText == dnLocalSheetId.InnerText) + || d.LocalSheetId == null + || dnLocalSheetId == null) + ); + if (existingDefinedName != null) { - var existingDefinedName = (DefinedName)workbook.DefinedNames.Where(d => - ((DefinedName)d).Name.Value. - ToLower() == - dnName - && ( - (((DefinedName)d). - LocalSheetId != null && - dnLocalSheetId != null && - ((DefinedName)d). - LocalSheetId. - InnerText == - dnLocalSheetId.InnerText) - || - ((DefinedName)d). - LocalSheetId == null || - dnLocalSheetId == null) - ).First(); existingDefinedName.Text = dn.Text; existingDefinedName.LocalSheetId = dn.LocalSheetId; existingDefinedName.Comment = dn.Comment; @@ -598,132 +574,125 @@ var newStrings = new Dictionary(); var newRichStrings = new Dictionary(); - foreach (XLWorksheet w in Worksheets.Cast()) + foreach (XLCell c in Worksheets.Cast().SelectMany(w => w.Internals.CellsCollection.GetCells().Where(c => c.DataType == XLCellValues.Text + && c.ShareString + && c.InnerText.Length > 0))) { - foreach (XLCell c in w.Internals.CellsCollection.GetCells()) + if (c.HasRichText) { - if ( - c.DataType == XLCellValues.Text - && c.ShareString - && c.InnerText.Length > 0) + if (newRichStrings.ContainsKey(c.RichText)) + c.SharedStringId = newRichStrings[c.RichText]; + else { - if (c.HasRichText) + var sharedStringItem = new SharedStringItem(); + foreach (IXLRichString rt in c.RichText) { - if (newRichStrings.ContainsKey(c.RichText)) - c.SharedStringId = newRichStrings[c.RichText]; - else - { - var sharedStringItem = new SharedStringItem(); - foreach (IXLRichString rt in c.RichText) - { - var run = new DocumentFormat.OpenXml.Spreadsheet.Run(); + var run = new DocumentFormat.OpenXml.Spreadsheet.Run(); - var runProperties = new DocumentFormat.OpenXml.Spreadsheet.RunProperties(); + var runProperties = new DocumentFormat.OpenXml.Spreadsheet.RunProperties(); - var bold = rt.Bold ? new Bold() : null; - var italic = rt.Italic ? new Italic() : null; - var underline = rt.Underline != XLFontUnderlineValues.None - ? new Underline {Val = rt.Underline.ToOpenXml()} - : null; - var strike = rt.Strikethrough ? new Strike() : null; - var verticalAlignment = new VerticalTextAlignment - {Val = rt.VerticalAlignment.ToOpenXml()}; - var shadow = rt.Shadow ? new Shadow() : null; - var fontSize = new FontSize {Val = rt.FontSize}; - var color = GetNewColor(rt.FontColor); - var fontName = new RunFont {Val = rt.FontName}; - var fontFamilyNumbering = new FontFamily {Val = (Int32)rt.FontFamilyNumbering}; + var bold = rt.Bold ? new Bold() : null; + var italic = rt.Italic ? new Italic() : null; + var underline = rt.Underline != XLFontUnderlineValues.None + ? new Underline {Val = rt.Underline.ToOpenXml()} + : null; + var strike = rt.Strikethrough ? new Strike() : null; + var verticalAlignment = new VerticalTextAlignment + {Val = rt.VerticalAlignment.ToOpenXml()}; + var shadow = rt.Shadow ? new Shadow() : null; + var fontSize = new FontSize {Val = rt.FontSize}; + var color = GetNewColor(rt.FontColor); + var fontName = new RunFont {Val = rt.FontName}; + var fontFamilyNumbering = new FontFamily {Val = (Int32)rt.FontFamilyNumbering}; - if (bold != null) runProperties.Append(bold); - if (italic != null) runProperties.Append(italic); + if (bold != null) runProperties.Append(bold); + if (italic != null) runProperties.Append(italic); - if (strike != null) runProperties.Append(strike); - if (shadow != null) runProperties.Append(shadow); - if (underline != null) runProperties.Append(underline); - runProperties.Append(verticalAlignment); + if (strike != null) runProperties.Append(strike); + if (shadow != null) runProperties.Append(shadow); + if (underline != null) runProperties.Append(underline); + runProperties.Append(verticalAlignment); - runProperties.Append(fontSize); - runProperties.Append(color); - runProperties.Append(fontName); - runProperties.Append(fontFamilyNumbering); + runProperties.Append(fontSize); + runProperties.Append(color); + runProperties.Append(fontName); + runProperties.Append(fontFamilyNumbering); - var text = new Text {Text = rt.Text}; - if (rt.Text.StartsWith(" ") || rt.Text.EndsWith(" ") || - rt.Text.Contains(Environment.NewLine)) - text.Space = SpaceProcessingModeValues.Preserve; + var text = new Text {Text = rt.Text}; + if (rt.Text.StartsWith(" ") || rt.Text.EndsWith(" ") || + rt.Text.Contains(Environment.NewLine)) + text.Space = SpaceProcessingModeValues.Preserve; - run.Append(runProperties); - run.Append(text); + run.Append(runProperties); + run.Append(text); - sharedStringItem.Append(run); - } - - if (c.RichText.HasPhonetics) - { - foreach (IXLPhonetic p in c.RichText.Phonetics) - { - var phoneticRun = new PhoneticRun - { - BaseTextStartIndex = (UInt32)p.Start, - EndingBaseIndex = (UInt32)p.End - }; - - var text = new Text {Text = p.Text}; - - phoneticRun.Append(text); - sharedStringItem.Append(phoneticRun); - } - var f = new XLFont(null, c.RichText.Phonetics); - if (!context.SharedFonts.ContainsKey(f)) - context.SharedFonts.Add(f, new FontInfo {Font = f}); - - var phoneticProperties = new PhoneticProperties - { - FontId = - context.SharedFonts[ - new XLFont(null, c.RichText.Phonetics)]. - FontId - }; - if (c.RichText.Phonetics.Alignment != XLPhoneticAlignment.Left) - phoneticProperties.Alignment = c.RichText.Phonetics.Alignment.ToOpenXml(); - if (c.RichText.Phonetics.Type != XLPhoneticType.FullWidthKatakana) - phoneticProperties.Type = c.RichText.Phonetics.Type.ToOpenXml(); - - sharedStringItem.Append(phoneticProperties); - } - - sharedStringTablePart.SharedStringTable.Append(sharedStringItem); - sharedStringTablePart.SharedStringTable.Count += 1; - sharedStringTablePart.SharedStringTable.UniqueCount += 1; - - newRichStrings.Add(c.RichText, stringId); - c.SharedStringId = stringId; - - stringId++; - } + sharedStringItem.Append(run); } - else + + if (c.RichText.HasPhonetics) { - if (newStrings.ContainsKey(c.Value.ToString())) - c.SharedStringId = newStrings[c.Value.ToString()]; - else + foreach (IXLPhonetic p in c.RichText.Phonetics) { - String s = c.Value.ToString(); - var sharedStringItem = new SharedStringItem(); - var text = new Text {Text = s}; - if (s.StartsWith(" ") || s.EndsWith(" ")) - text.Space = SpaceProcessingModeValues.Preserve; - sharedStringItem.Append(text); - sharedStringTablePart.SharedStringTable.Append(sharedStringItem); - sharedStringTablePart.SharedStringTable.Count += 1; - sharedStringTablePart.SharedStringTable.UniqueCount += 1; + var phoneticRun = new PhoneticRun + { + BaseTextStartIndex = (UInt32)p.Start, + EndingBaseIndex = (UInt32)p.End + }; - newStrings.Add(c.Value.ToString(), stringId); - c.SharedStringId = stringId; + var text = new Text {Text = p.Text}; - stringId++; + phoneticRun.Append(text); + sharedStringItem.Append(phoneticRun); } + var f = new XLFont(null, c.RichText.Phonetics); + if (!context.SharedFonts.ContainsKey(f)) + context.SharedFonts.Add(f, new FontInfo {Font = f}); + + var phoneticProperties = new PhoneticProperties + { + FontId = + context.SharedFonts[ + new XLFont(null, c.RichText.Phonetics)]. + FontId + }; + if (c.RichText.Phonetics.Alignment != XLPhoneticAlignment.Left) + phoneticProperties.Alignment = c.RichText.Phonetics.Alignment.ToOpenXml(); + if (c.RichText.Phonetics.Type != XLPhoneticType.FullWidthKatakana) + phoneticProperties.Type = c.RichText.Phonetics.Type.ToOpenXml(); + + sharedStringItem.Append(phoneticProperties); } + + sharedStringTablePart.SharedStringTable.Append(sharedStringItem); + sharedStringTablePart.SharedStringTable.Count += 1; + sharedStringTablePart.SharedStringTable.UniqueCount += 1; + + newRichStrings.Add(c.RichText, stringId); + c.SharedStringId = stringId; + + stringId++; + } + } + else + { + if (newStrings.ContainsKey(c.Value.ToString())) + c.SharedStringId = newStrings[c.Value.ToString()]; + else + { + String s = c.Value.ToString(); + var sharedStringItem = new SharedStringItem(); + var text = new Text {Text = s}; + if (s.StartsWith(" ") || s.EndsWith(" ")) + text.Space = SpaceProcessingModeValues.Preserve; + sharedStringItem.Append(text); + sharedStringTablePart.SharedStringTable.Append(sharedStringItem); + sharedStringTablePart.SharedStringTable.Count += 1; + sharedStringTablePart.SharedStringTable.UniqueCount += 1; + + newStrings.Add(c.Value.ToString(), stringId); + c.SharedStringId = stringId; + + stringId++; } } } @@ -1645,27 +1614,20 @@ foreach (XLWorksheet worksheet in WorksheetsInternal) { - foreach (IXLStyle s in worksheet.Styles) - { - if (!xlStyles.Contains(s)) - xlStyles.Add(s); - } + foreach (IXLStyle s in worksheet.Styles.Where(s => !xlStyles.Contains(s))) + xlStyles.Add(s); - foreach (IXLStyle s in worksheet.Internals.ColumnsCollection.Select(kp => kp.Value.Style)) - { - if (!xlStyles.Contains(s)) - xlStyles.Add(s); - } + foreach ( + IXLStyle s in + worksheet.Internals.ColumnsCollection.Select(kp => kp.Value.Style).Where( + s => !xlStyles.Contains(s))) + xlStyles.Add(s); - foreach (IXLStyle s in worksheet.Internals.RowsCollection.Select(kp => kp.Value.Style)) - { - if (!xlStyles.Contains(s)) - xlStyles.Add(s); - } - - //xlStyles.AddRange(worksheet.Styles); - //worksheet.Internals.ColumnsCollection.Values.ForEach(c => xlStyles.Add(c.Style)); - //worksheet.Internals.RowsCollection.Values.ForEach(c => xlStyles.Add(c.Style)); + foreach ( + IXLStyle s in + worksheet.Internals.RowsCollection.Select(kp => kp.Value.Style).Where(s => !xlStyles.Contains(s)) + ) + xlStyles.Add(s); } foreach (IXLStyle xlStyle in xlStyles) @@ -1679,16 +1641,17 @@ if (!sharedBorders.ContainsKey(xlStyle.Border)) sharedBorders.Add(xlStyle.Border, new BorderInfo {BorderId = borderCount++, Border = xlStyle.Border}); - if (xlStyle.NumberFormat.NumberFormatId == -1 && !sharedNumberFormats.ContainsKey(xlStyle.NumberFormat)) - { - sharedNumberFormats.Add(xlStyle.NumberFormat, - new NumberFormatInfo - { - NumberFormatId = numberFormatCount + 164, - NumberFormat = xlStyle.NumberFormat - }); - numberFormatCount++; - } + if ( xlStyle.NumberFormat.NumberFormatId != -1 + || sharedNumberFormats.ContainsKey(xlStyle.NumberFormat)) + continue; + + sharedNumberFormats.Add(xlStyle.NumberFormat, + new NumberFormatInfo + { + NumberFormatId = numberFormatCount + 164, + NumberFormat = xlStyle.NumberFormat + }); + numberFormatCount++; } var allSharedNumberFormats = ResolveNumberFormats(workbookStylesPart, sharedNumberFormats); @@ -1698,23 +1661,22 @@ foreach (IXLStyle xlStyle in xlStyles) { - if (!context.SharedStyles.ContainsKey(xlStyle)) - { - int numberFormatId = xlStyle.NumberFormat.NumberFormatId >= 0 - ? xlStyle.NumberFormat.NumberFormatId - : allSharedNumberFormats[xlStyle.NumberFormat].NumberFormatId; + if (context.SharedStyles.ContainsKey(xlStyle)) continue; - context.SharedStyles.Add(xlStyle, - new StyleInfo - { - StyleId = styleCount++, - Style = xlStyle, - FontId = context.SharedFonts[xlStyle.Font].FontId, - FillId = allSharedFills[xlStyle.Fill].FillId, - BorderId = allSharedBorders[xlStyle.Border].BorderId, - NumberFormatId = numberFormatId - }); - } + int numberFormatId = xlStyle.NumberFormat.NumberFormatId >= 0 + ? xlStyle.NumberFormat.NumberFormatId + : allSharedNumberFormats[xlStyle.NumberFormat].NumberFormatId; + + context.SharedStyles.Add(xlStyle, + new StyleInfo + { + StyleId = styleCount++, + Style = xlStyle, + FontId = context.SharedFonts[xlStyle.Font].FontId, + FillId = allSharedFills[xlStyle.Fill].FillId, + BorderId = allSharedBorders[xlStyle.Border].BorderId, + NumberFormatId = numberFormatId + }); } ResolveCellStyleFormats(workbookStylesPart, context); @@ -1762,29 +1724,29 @@ var info = styleInfo; Boolean foundOne = workbookStylesPart.Stylesheet.CellFormats.Cast().Any(f => CellFormatsAreEqual(f, info)); - if (!foundOne) - { - var cellFormat = GetCellFormat(styleInfo); - cellFormat.FormatId = 0; - var alignment = new Alignment - { - Horizontal = styleInfo.Style.Alignment.Horizontal.ToOpenXml(), - Vertical = styleInfo.Style.Alignment.Vertical.ToOpenXml(), - Indent = (UInt32)styleInfo.Style.Alignment.Indent, - ReadingOrder = (UInt32)styleInfo.Style.Alignment.ReadingOrder, - WrapText = styleInfo.Style.Alignment.WrapText, - TextRotation = (UInt32)styleInfo.Style.Alignment.TextRotation, - ShrinkToFit = styleInfo.Style.Alignment.ShrinkToFit, - RelativeIndent = styleInfo.Style.Alignment.RelativeIndent, - JustifyLastLine = styleInfo.Style.Alignment.JustifyLastLine - }; - cellFormat.AppendChild(alignment); + + if (foundOne) continue; - if (cellFormat.ApplyProtection.Value) - cellFormat.AppendChild(GetProtection(styleInfo)); + var cellFormat = GetCellFormat(styleInfo); + cellFormat.FormatId = 0; + var alignment = new Alignment + { + Horizontal = styleInfo.Style.Alignment.Horizontal.ToOpenXml(), + Vertical = styleInfo.Style.Alignment.Vertical.ToOpenXml(), + Indent = (UInt32)styleInfo.Style.Alignment.Indent, + ReadingOrder = (UInt32)styleInfo.Style.Alignment.ReadingOrder, + WrapText = styleInfo.Style.Alignment.WrapText, + TextRotation = (UInt32)styleInfo.Style.Alignment.TextRotation, + ShrinkToFit = styleInfo.Style.Alignment.ShrinkToFit, + RelativeIndent = styleInfo.Style.Alignment.RelativeIndent, + JustifyLastLine = styleInfo.Style.Alignment.JustifyLastLine + }; + cellFormat.AppendChild(alignment); - workbookStylesPart.Stylesheet.CellFormats.AppendChild(cellFormat); - } + if (cellFormat.ApplyProtection.Value) + cellFormat.AppendChild(GetProtection(styleInfo)); + + workbookStylesPart.Stylesheet.CellFormats.AppendChild(cellFormat); } workbookStylesPart.Stylesheet.CellFormats.Count = (UInt32)workbookStylesPart.Stylesheet.CellFormats.Count(); } @@ -1801,15 +1763,15 @@ Boolean foundOne = workbookStylesPart.Stylesheet.CellStyleFormats.Cast().Any( f => CellFormatsAreEqual(f, info)); - if (!foundOne) - { - var cellStyleFormat = GetCellFormat(styleInfo); - if (cellStyleFormat.ApplyProtection.Value) - cellStyleFormat.AppendChild(GetProtection(styleInfo)); + if (foundOne) continue; - workbookStylesPart.Stylesheet.CellStyleFormats.AppendChild(cellStyleFormat); - } + var cellStyleFormat = GetCellFormat(styleInfo); + + if (cellStyleFormat.ApplyProtection.Value) + cellStyleFormat.AppendChild(GetProtection(styleInfo)); + + workbookStylesPart.Stylesheet.CellStyleFormats.AppendChild(cellStyleFormat); } workbookStylesPart.Stylesheet.CellStyleFormats.Count = (UInt32)workbookStylesPart.Stylesheet.CellStyleFormats.Count(); @@ -2069,17 +2031,16 @@ private static void ResolveFillWithPattern(Fills fills, PatternValues patternValues) { - if (!fills.Elements().Any(f => - f.PatternFill.PatternType == patternValues - && f.PatternFill.ForegroundColor == null - && f.PatternFill.BackgroundColor == null - )) - { - var fill1 = new Fill(); - var patternFill1 = new PatternFill {PatternType = patternValues}; - fill1.AppendChild(patternFill1); - fills.AppendChild(fill1); - } + if (fills.Elements().Any(f => + f.PatternFill.PatternType == patternValues + && f.PatternFill.ForegroundColor == null + && f.PatternFill.BackgroundColor == null + )) return; + + var fill1 = new Fill(); + var patternFill1 = new PatternFill {PatternType = patternValues}; + fill1.AppendChild(patternFill1); + fills.AppendChild(fill1); } private static Fill GetNewFill(FillInfo fillInfo) @@ -2502,7 +2463,7 @@ worksheetPart.Worksheet.SheetFormatProperties.DefaultColumnWidth = worksheetColumnWidth; else worksheetPart.Worksheet.SheetFormatProperties.DefaultColumnWidth = null; - + if (maxOutlineColumn > 0) worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelColumn = (byte)maxOutlineColumn; @@ -2523,8 +2484,6 @@ worksheetPart.Worksheet.RemoveAllChildren(); else { - - if (!worksheetPart.Worksheet.Elements().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Columns); @@ -2555,7 +2514,7 @@ { UInt32Value min = 1; UInt32Value max = (UInt32)(minInColumnsCollection - 1); - + for (var co = min; co <= max; co++) { @@ -2619,9 +2578,9 @@ c => c.Min.Value)) { col.Style = worksheetStyleId; - col.Width = worksheetColumnWidth; - col.CustomWidth = true; - + col.Width = worksheetColumnWidth; + col.CustomWidth = true; + if ((Int32)col.Max.Value > maxInColumnsCollection) maxInColumnsCollection = (Int32)col.Max.Value; } @@ -2644,7 +2603,7 @@ if (!columns.Any()) { worksheetPart.Worksheet.RemoveAllChildren(); - cm.SetElement( XLWSContentManager.XLWSContents.Columns, null); + cm.SetElement(XLWSContentManager.XLWSContents.Columns, null); } } @@ -2672,13 +2631,12 @@ } var sheetDataRows = sheetData.Elements().ToDictionary(r => (Int32)r.RowIndex.Value, r => r); - foreach (KeyValuePair r in xlWorksheet.Internals.RowsCollection.Deleted) + foreach ( + KeyValuePair r in + xlWorksheet.Internals.RowsCollection.Deleted.Where(r => sheetDataRows.ContainsKey(r.Key))) { - if (sheetDataRows.ContainsKey(r.Key)) - { - sheetData.RemoveChild(sheetDataRows[r.Key]); - sheetDataRows.Remove(r.Key); - } + sheetData.RemoveChild(sheetDataRows[r.Key]); + sheetDataRows.Remove(r.Key); } var distinctRows = cellsByRow.Keys.Union(xlWorksheet.Internals.RowsCollection.Keys); @@ -2742,115 +2700,114 @@ var cellsByReference = row.Elements().ToDictionary(c => c.CellReference.Value, c => c); - foreach (var c in xlWorksheet.Internals.CellsCollection.Deleted) + foreach (XLSheetPoint c in xlWorksheet.Internals.CellsCollection.Deleted) { String key = ExcelHelper.GetColumnLetterFromNumber(c.Column) + c.Row.ToStringLookup(); if (cellsByReference.ContainsKey(key)) row.RemoveChild(cellsByReference[key]); } - if (cellsByRow.ContainsKey(distinctRow)) + if (!cellsByRow.ContainsKey(distinctRow)) continue; + + Boolean isNewRow = !row.Elements().Any(); + foreach (XLCell opCell in cellsByRow[distinctRow] + .OrderBy(c => c.Address.ColumnNumber) + .Select(c => (XLCell)c)) { - Boolean isNewRow = !row.Elements().Any(); - foreach (XLCell opCell in cellsByRow[distinctRow] - .OrderBy(c => c.Address.ColumnNumber) - .Select(c => (XLCell)c)) + uint styleId = context.SharedStyles[opCell.Style].StyleId; + + var dataType = opCell.DataType; + string cellReference = (opCell.Address).GetTrimmedAddress(); + + Cell cell; + if (cellsByReference.ContainsKey(cellReference)) + cell = cellsByReference[cellReference]; + else { - uint styleId = context.SharedStyles[opCell.Style].StyleId; - - var dataType = opCell.DataType; - string cellReference = (opCell.Address).GetTrimmedAddress(); - - Cell cell; - if (cellsByReference.ContainsKey(cellReference)) - cell = cellsByReference[cellReference]; + cell = new Cell {CellReference = new StringValue(cellReference)}; + if (isNewRow) + row.AppendChild(cell); else { - cell = new Cell {CellReference = new StringValue(cellReference)}; - if (isNewRow) + Int32 newColumn = ExcelHelper.GetColumnNumberFromAddress1(cellReference); + + Cell cellBeforeInsert = null; + Int32 lastCo = Int32.MaxValue; + foreach ( + Cell c in + row.Elements().Where( + c => + ExcelHelper.GetColumnNumberFromAddress1(c.CellReference.Value) > newColumn)) + { + int thidCo = ExcelHelper.GetColumnNumberFromAddress1(c.CellReference.Value); + + if (lastCo <= thidCo) continue; + + cellBeforeInsert = c; + lastCo = thidCo; + } + if (cellBeforeInsert == null) row.AppendChild(cell); else - { - Int32 newColumn = ExcelHelper.GetColumnNumberFromAddress1(cellReference); + row.InsertBefore(cell, cellBeforeInsert); + } + } - Cell cellBeforeInsert = null; - Int32 lastCo = Int32.MaxValue; - foreach ( - Cell c in - row.Elements().Where( - c => - ExcelHelper.GetColumnNumberFromAddress1(c.CellReference.Value) > newColumn)) + cell.StyleIndex = styleId; + if (!StringExtensions.IsNullOrWhiteSpace(opCell.FormulaA1)) + { + String formula = opCell.FormulaA1; + if (formula.StartsWith("{")) + { + formula = formula.Substring(1, formula.Length - 2); + cell.CellFormula = new CellFormula(formula) + { + FormulaType = CellFormulaValues.Array, + Reference = cellReference + }; + } + else + cell.CellFormula = new CellFormula(formula); + cell.CellValue = null; + } + else + { + cell.CellFormula = null; + + cell.DataType = opCell.DataType == XLCellValues.DateTime ? null : GetCellValue(opCell); + + var cellValue = new CellValue(); + if (dataType == XLCellValues.Text) + { + if (opCell.InnerText.Length == 0) + cell.CellValue = null; + else + { + if (opCell.ShareString) { - int thidCo = ExcelHelper.GetColumnNumberFromAddress1(c.CellReference.Value); - if (lastCo > thidCo) - { - cellBeforeInsert = c; - lastCo = thidCo; - } + cellValue.Text = opCell.SharedStringId.ToString(); + cell.CellValue = cellValue; } - if (cellBeforeInsert == null) - row.AppendChild(cell); else - row.InsertBefore(cell, cellBeforeInsert); + cell.InlineString = new InlineString {Text = new Text(opCell.GetString())}; } } - - cell.StyleIndex = styleId; - if (!StringExtensions.IsNullOrWhiteSpace(opCell.FormulaA1)) + else if (dataType == XLCellValues.TimeSpan) { - String formula = opCell.FormulaA1; - if (formula.StartsWith("{")) - { - formula = formula.Substring(1, formula.Length - 2); - cell.CellFormula = new CellFormula(formula) - { - FormulaType = CellFormulaValues.Array, - Reference = cellReference - }; - } - else - cell.CellFormula = new CellFormula(formula); - cell.CellValue = null; + var timeSpan = opCell.GetTimeSpan(); + cellValue.Text = + XLCell.BaseDate.Add(timeSpan).ToOADate().ToString(CultureInfo.InvariantCulture); + cell.CellValue = cellValue; + } + else if (dataType == XLCellValues.DateTime || dataType == XLCellValues.Number) + { + cellValue.Text = Double.Parse(opCell.InnerText).ToString(CultureInfo.InvariantCulture); + cell.CellValue = cellValue; } else { - cell.CellFormula = null; - - cell.DataType = opCell.DataType == XLCellValues.DateTime ? null : GetCellValue(opCell); - - var cellValue = new CellValue(); - if (dataType == XLCellValues.Text) - { - if (opCell.InnerText.Length == 0 ) - cell.CellValue = null; - else - { - if (opCell.ShareString) - { - cellValue.Text = opCell.SharedStringId.ToString(); - cell.CellValue = cellValue; - } - else - cell.InlineString = new InlineString {Text = new Text(opCell.GetString())}; - } - } - else if (dataType == XLCellValues.TimeSpan) - { - var timeSpan = opCell.GetTimeSpan(); - cellValue.Text = - XLCell.BaseDate.Add(timeSpan).ToOADate().ToString(CultureInfo.InvariantCulture); - cell.CellValue = cellValue; - } - else if (dataType == XLCellValues.DateTime || dataType == XLCellValues.Number) - { - cellValue.Text = Double.Parse(opCell.InnerText).ToString(CultureInfo.InvariantCulture); - cell.CellValue = cellValue; - } - else - { - cellValue.Text = opCell.InnerText; - cell.CellValue = cellValue; - } + cellValue.Text = opCell.InnerText; + cell.CellValue = cellValue; } } } @@ -2871,7 +2828,7 @@ var sheetProtection = worksheetPart.Worksheet.Elements().First(); cm.SetElement(XLWSContentManager.XLWSContents.SheetProtection, sheetProtection); - var protection = (XLSheetProtection)xlWorksheet.Protection; + var protection = xlWorksheet.Protection; sheetProtection.Sheet = protection.Protected; if (!StringExtensions.IsNullOrWhiteSpace(protection.PasswordHash)) sheetProtection.Password = protection.PasswordHash; @@ -2934,14 +2891,10 @@ cm.SetElement(XLWSContentManager.XLWSContents.MergeCells, mergeCells); mergeCells.RemoveAllChildren(); - foreach ( - string merged in - (xlWorksheet).Internals.MergedRanges.Select( - m => m.RangeAddress.FirstAddress.ToString() + ":" + m.RangeAddress.LastAddress.ToString())) - { - var mergeCell = new MergeCell {Reference = merged}; + foreach (MergeCell mergeCell in (xlWorksheet).Internals.MergedRanges.Select( + m => m.RangeAddress.FirstAddress.ToString() + ":" + m.RangeAddress.LastAddress.ToString()).Select( + merged => new MergeCell {Reference = merged})) mergeCells.AppendChild(mergeCell); - } mergeCells.Count = (UInt32)mergeCells.Count(); } @@ -2962,7 +2915,6 @@ } else { - worksheetPart.Worksheet.Elements().FirstOrDefault(); if (!worksheetPart.Worksheet.Elements().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.DataValidations); @@ -3016,7 +2968,6 @@ } else { - worksheetPart.Worksheet.Elements().FirstOrDefault(); if (!worksheetPart.Worksheet.Elements().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Hyperlinks); @@ -3204,16 +3155,14 @@ { rowBreaks.Count = (UInt32)rowBreakCount; rowBreaks.ManualBreakCount = (UInt32)rowBreakCount; - foreach (int rb in xlWorksheet.PageSetup.RowBreaks) - { - var break1 = new Break - { - Id = (UInt32)rb, - Max = (UInt32)xlWorksheet.RangeAddress.LastAddress.RowNumber, - ManualPageBreak = true - }; + uint lastRowNum = (UInt32)xlWorksheet.RangeAddress.LastAddress.RowNumber; + foreach (Break break1 in xlWorksheet.PageSetup.RowBreaks.Select(rb => new Break + { + Id = (UInt32)rb, + Max = lastRowNum, + ManualPageBreak = true + })) rowBreaks.AppendChild(break1); - } cm.SetElement(XLWSContentManager.XLWSContents.RowBreaks, rowBreaks); } else @@ -3239,16 +3188,14 @@ { columnBreaks.Count = (UInt32)columnBreakCount; columnBreaks.ManualBreakCount = (UInt32)columnBreakCount; - foreach (int cb in xlWorksheet.PageSetup.ColumnBreaks) - { - var break1 = new Break - { - Id = (UInt32)cb, - Max = (UInt32)xlWorksheet.RangeAddress.LastAddress.ColumnNumber, - ManualPageBreak = true - }; + uint maxColumnNumber = (UInt32)xlWorksheet.RangeAddress.LastAddress.ColumnNumber; + foreach (Break break1 in xlWorksheet.PageSetup.ColumnBreaks.Select(cb => new Break + { + Id = (UInt32)cb, + Max = maxColumnNumber, + ManualPageBreak = true + })) columnBreaks.AppendChild(break1); - } cm.SetElement(XLWSContentManager.XLWSContents.ColumnBreaks, columnBreaks); } else @@ -3284,12 +3231,10 @@ cm.SetElement(XLWSContentManager.XLWSContents.TableParts, tableParts); tableParts.Count = (UInt32)xlWorksheet.Tables.Count(); - foreach (IXLTable table in xlWorksheet.Tables) - { - var xlTable = (XLTable)table; - var tablePart = new TablePart {Id = xlTable.RelId}; + foreach ( + TablePart tablePart in + from XLTable xlTable in xlWorksheet.Tables select new TablePart {Id = xlTable.RelId}) tableParts.AppendChild(tablePart); - } #endregion } @@ -3305,19 +3250,19 @@ Int32 count = sheetColumns.Count; foreach (KeyValuePair kp in sheetColumns.OrderBy(kp => kp.Key)) { - if (!(kp.Key < count && ColumnsAreEqual(kp.Value, sheetColumns[kp.Key + 1]))) - { - var newColumn = (Column)kp.Value.CloneNode(true); - newColumn.Min = lastMin; - var columnsToRemove = - columns.Elements().Where(co => co.Min >= newColumn.Min && co.Max <= newColumn.Max). - Select(co => co).ToList(); - columnsToRemove.ForEach(c => columns.RemoveChild(c)); + if ((kp.Key < count && ColumnsAreEqual(kp.Value, sheetColumns[kp.Key + 1]))) continue; - columns.AppendChild(newColumn); + var newColumn = (Column)kp.Value.CloneNode(true); + newColumn.Min = lastMin; + uint newColumnMax = newColumn.Max.Value; + var columnsToRemove = + columns.Elements().Where(co => co.Min.Value >= lastMin && co.Max.Value <= newColumnMax). + Select(co => co).ToList(); + columnsToRemove.ForEach(c => columns.RemoveChild(c)); - lastMin = kp.Key + 1; - } + columns.AppendChild(newColumn); + + lastMin = kp.Key + 1; } } @@ -3329,40 +3274,19 @@ } private static void UpdateColumn(Column column, Columns columns, Dictionary sheetColumnsByMin) - //, Dictionary sheetColumnsByMax) { UInt32 co = column.Min.Value; Column newColumn; - Column existingColumn; // = columns.Elements().FirstOrDefault(c => c.Min.Value == column.Min.Value); if (!sheetColumnsByMin.ContainsKey(co)) { - //if (sheetColumnsByMin.ContainsKey(co + 1) && ColumnsAreEqual(column, sheetColumnsByMin[co + 1])) - //{ - // var thisColumn = sheetColumnsByMin[co + 1]; - // thisColumn.Min -= 1; - // sheetColumnsByMin.Remove(co + 1); - // sheetColumnsByMin.Add(co, thisColumn); - //} - //else if (sheetColumnsByMax.ContainsKey(co - 1) && ColumnsAreEqual(column, sheetColumnsByMin[co - 1])) - //{ - // var thisColumn = sheetColumnsByMin[co - 1]; - // thisColumn.Max += 1; - // sheetColumnsByMax.Remove(co - 1); - // sheetColumnsByMax.Add(co, thisColumn); - //} - //else - //{ newColumn = (Column)column.CloneNode(true); columns.AppendChild(newColumn); sheetColumnsByMin.Add(co, newColumn); - // sheetColumnsByMax.Add(co, newColumn); - //} } else { - existingColumn = sheetColumnsByMin[column.Min.Value]; + var existingColumn = sheetColumnsByMin[column.Min.Value]; newColumn = (Column)existingColumn.CloneNode(true); - //newColumn = new Column() { InnerXml = existingColumn.InnerXml }; newColumn.Min = column.Min; newColumn.Max = column.Max; newColumn.Style = column.Style; @@ -3408,8 +3332,8 @@ private static bool ColumnsAreEqual(Column left, Column right) { return - ((left.Style == null && right.Style == null) - || (left.Style != null && right.Style != null && left.Style.Value == right.Style.Value)) + ((left.Style == null && right.Style == null) + || (left.Style != null && right.Style != null && left.Style.Value == right.Style.Value)) && ((left.Width == null && right.Width == null) || (left.Width != null && right.Width != null && left.Width.Value == right.Width.Value)) && ((left.Hidden == null && right.Hidden == null) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index 3464d9c..02d1158 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -641,7 +641,7 @@ var targetSheet = (XLWorksheet)workbook.WorksheetsInternal.Add(newSheetName, position); Internals.CellsCollection.GetCells().ForEach(c => targetSheet.Cell(c.Address).CopyFrom(c)); - DataValidations.ForEach(dv => targetSheet.DataValidations.Add(new XLDataValidation(dv, targetSheet))); + DataValidations.ForEach(dv => targetSheet.DataValidations.Add(new XLDataValidation(dv))); Internals.ColumnsCollection.ForEach( kp => targetSheet.Internals.ColumnsCollection.Add(kp.Key, new XLColumn(kp.Value))); Internals.RowsCollection.ForEach(kp => targetSheet.Internals.RowsCollection.Add(kp.Key, new XLRow(kp.Value))); @@ -719,7 +719,9 @@ return this; } - public IXLSheetProtection Protection { get; private set; } + public XLSheetProtection Protection { get; private set; } + + IXLSheetProtection IXLWorksheet.Protection { get { return Protection; } } public IXLSheetProtection Protect() { diff --git a/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs b/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs index 0087778..6e084f0 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs +++ b/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs @@ -5,8 +5,10 @@ namespace ClosedXML { + using System.Linq; + /// - /// Common methods + /// Common methods /// public static class ExcelHelper { @@ -15,30 +17,27 @@ public const int MaxRowNumber = 1048576; public const int MaxColumnNumber = 16384; - internal static readonly NumberFormatInfo NumberFormatForParse = CultureInfo.InvariantCulture.NumberFormat; private const Int32 TwoT26 = 26 * 26; + internal static readonly NumberFormatInfo NumberFormatForParse = CultureInfo.InvariantCulture.NumberFormat; + /// - /// Gets the column number of a given column letter. + /// Gets the column number of a given column letter. /// /// The column letter to translate into a column number. public static int GetColumnNumberFromLetter(string columnLetter) { if (columnLetter[0] <= '9') - { return Int32.Parse(columnLetter, NumberFormatForParse); - } columnLetter = columnLetter.ToUpper(); - var length = columnLetter.Length; + int length = columnLetter.Length; if (length == 1) - { return Convert.ToByte(columnLetter[0]) - 64; - } if (length == 2) { return - ((Convert.ToByte(columnLetter[0]) - 64) * 26) + - (Convert.ToByte(columnLetter[1]) - 64); + ((Convert.ToByte(columnLetter[0]) - 64) * 26) + + (Convert.ToByte(columnLetter[1]) - 64); } if (length == 3) { @@ -48,18 +47,20 @@ } throw new ApplicationException("Column Length must be between 1 and 3."); } + /// - /// Gets the column letter of a given column number. + /// Gets the column letter of a given column number. /// /// The column number to translate into a column letter. public static string GetColumnLetterFromNumber(int column) { #region Check + if (column <= 0) - { throw new ArgumentOutOfRangeException("column", "Must be more than 0"); - } + #endregion + var value = new StringBuilder(6); while (column > 0) { @@ -78,30 +79,18 @@ public static bool IsValidColumn(string column) { if (StringExtensions.IsNullOrWhiteSpace(column) || column.Length > 3) - { return false; - } - Boolean retVal = true; String theColumn = column.ToUpper(); - for (Int32 i = 0; i < column.Length; i++) - { - if (theColumn[i] < 'A' || theColumn[i] > 'Z' || (i == 2 && theColumn[i] > 'D')) - { - retVal = false; - break; - } - } - return retVal; + return + !column.Where((t, i) => theColumn[i] < 'A' || theColumn[i] > 'Z' || (i == 2 && theColumn[i] > 'D')).Any(); } public static bool IsValidRow(string rowString) { Int32 row; if (Int32.TryParse(rowString, out row)) - { return row > 0 && row <= MaxRowNumber; - } return false; } @@ -111,23 +100,19 @@ Int32 rowPos = 0; Int32 addressLength = address.Length; while (rowPos < addressLength && (address[rowPos] > '9' || address[rowPos] < '0')) - { rowPos++; - } return - rowPos < addressLength - && IsValidRow(address.Substring(rowPos)) - && IsValidColumn(address.Substring(0, rowPos)); + rowPos < addressLength + && IsValidRow(address.Substring(rowPos)) + && IsValidColumn(address.Substring(0, rowPos)); } public static int GetRowFromAddress1(string cellAddressString) { Int32 rowPos = 1; while (cellAddressString[rowPos] > '9') - { rowPos++; - } return Int32.Parse(cellAddressString.Substring(rowPos), NumberFormatForParse); } @@ -136,9 +121,7 @@ { Int32 rowPos = 0; while (cellAddressString[rowPos] > '9') - { rowPos++; - } return GetColumnNumberFromLetter(cellAddressString.Substring(0, rowPos)); } @@ -147,42 +130,27 @@ { Int32 rowPos = 1; while (cellAddressString[rowPos] > '9') - { rowPos++; - } - if (cellAddressString[rowPos] == '$') - { - return Int32.Parse(cellAddressString.Substring(rowPos + 1), NumberFormatForParse); - } - return Int32.Parse(cellAddressString.Substring(rowPos), NumberFormatForParse); + return + Int32.Parse( + cellAddressString[rowPos] == '$' + ? cellAddressString.Substring(rowPos + 1) + : cellAddressString.Substring(rowPos), NumberFormatForParse); } public static int GetColumnNumberFromAddress2(string cellAddressString) { - Int32 startPos; - if (cellAddressString[0] == '$') - { - startPos = 1; - } - else - { - startPos = 0; - } + int startPos = cellAddressString[0] == '$' ? 1 : 0; Int32 rowPos = startPos; while (cellAddressString[rowPos] > '9') - { rowPos++; - } - if (cellAddressString[rowPos] == '$') - { - return GetColumnNumberFromLetter(cellAddressString.Substring(startPos, rowPos - 1)); - } - return GetColumnNumberFromLetter(cellAddressString.Substring(startPos, rowPos)); + return + GetColumnNumberFromLetter(cellAddressString[rowPos] == '$' + ? cellAddressString.Substring(startPos, rowPos - 1) + : cellAddressString.Substring(startPos, rowPos)); } - - } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/PathHelper.cs b/ClosedXML/ClosedXML/ClosedXML/PathHelper.cs index 702d955..696e074 100644 --- a/ClosedXML/ClosedXML/ClosedXML/PathHelper.cs +++ b/ClosedXML/ClosedXML/ClosedXML/PathHelper.cs @@ -136,7 +136,7 @@ int partsCount = Math.Min(firstPathParts.Length, secondPathParts.Length); for (int i = 0; i < partsCount; i++) { - if (!firstPathParts[i].ToLower().Equals(secondPathParts[i].ToLower())) + if (String.Compare(firstPathParts[i], secondPathParts[i], true) != 0) { break; } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/SortExample.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/SortExample.cs index 1f2ecea..575b653 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/SortExample.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/SortExample.cs @@ -200,12 +200,12 @@ ws.Cell("A2").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise); ws.Cell("A3").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.BurlyWood); ws.Cell("A4").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkGray); - ws.Cell("A5").SetValue(" ").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon); + ws.Cell("A5").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon); ws.Cell("A6").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DodgerBlue); ws.Cell("A7").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.IndianRed); ws.Cell("A8").SetValue("B").Style.Fill.SetBackgroundColor(XLColor.DeepPink); - ws.Cell("B1").SetValue(" ").Style.Fill.SetBackgroundColor(XLColor.LightGreen); + ws.Cell("B1").SetValue("").Style.Fill.SetBackgroundColor(XLColor.LightGreen); ws.Cell("B2").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise); ws.Cell("B3").SetValue("B").Style.Fill.SetBackgroundColor(XLColor.BurlyWood); ws.Cell("B4").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkGray); @@ -215,13 +215,13 @@ ws.Cell("B8").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.DeepPink); ws.Cell("C1").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.LightGreen); - ws.Cell("C2").SetValue(" ").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise); + ws.Cell("C2").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise); ws.Cell("C3").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.BurlyWood); ws.Cell("C4").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.DarkGray); ws.Cell("C5").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon); ws.Cell("C6").SetValue("b").Style.Fill.SetBackgroundColor(XLColor.DodgerBlue); ws.Cell("C7").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.IndianRed); - ws.Cell("C8").SetValue(" ").Style.Fill.SetBackgroundColor(XLColor.DeepPink); + ws.Cell("C8").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DeepPink); } // Private diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/Sorting.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/Sorting.cs index 2f1c1b0..0fa398c 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/Sorting.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/Sorting.cs @@ -224,12 +224,12 @@ ws.Cell("A2").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise); ws.Cell("A3").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.BurlyWood); ws.Cell("A4").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkGray); - ws.Cell("A5").SetValue(" ").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon); + ws.Cell("A5").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon); ws.Cell("A6").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DodgerBlue); ws.Cell("A7").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.IndianRed); ws.Cell("A8").SetValue("B").Style.Fill.SetBackgroundColor(XLColor.DeepPink); - ws.Cell("B1").SetValue(" ").Style.Fill.SetBackgroundColor(XLColor.LightGreen); + ws.Cell("B1").SetValue("").Style.Fill.SetBackgroundColor(XLColor.LightGreen); ws.Cell("B2").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise); ws.Cell("B3").SetValue("B").Style.Fill.SetBackgroundColor(XLColor.BurlyWood); ws.Cell("B4").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkGray); @@ -239,13 +239,13 @@ ws.Cell("B8").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.DeepPink); ws.Cell("C1").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.LightGreen); - ws.Cell("C2").SetValue(" ").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise); + ws.Cell("C2").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise); ws.Cell("C3").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.BurlyWood); ws.Cell("C4").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.DarkGray); ws.Cell("C5").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon); ws.Cell("C6").SetValue("b").Style.Fill.SetBackgroundColor(XLColor.DodgerBlue); ws.Cell("C7").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.IndianRed); - ws.Cell("C8").SetValue(" ").Style.Fill.SetBackgroundColor(XLColor.DeepPink); + ws.Cell("C8").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DeepPink); } // Private diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRangesPlus.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRangesPlus.cs index 13aaf7e..f3b37c8 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRangesPlus.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRangesPlus.cs @@ -21,9 +21,9 @@ // Place some markers var cellNextRow = ws.Cell(rngTable.RangeAddress.LastAddress.RowNumber + 1, rngTable.RangeAddress.LastAddress.ColumnNumber); - cellNextRow.Value = "Next Row"; + cellNextRow.Value = "ColumnRight Row"; var cellNextColumn = ws.Cell(rngTable.RangeAddress.LastAddress.RowNumber, rngTable.RangeAddress.LastAddress.ColumnNumber + 1); - cellNextColumn.Value = "Next Column"; + cellNextColumn.Value = "ColumnRight Column"; rngTable.Transpose(XLTransposeOptions.MoveCells); rngTable.Transpose(XLTransposeOptions.MoveCells); diff --git a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj index f876d13..7ff3d6b 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj @@ -127,9 +127,6 @@ Excel\Cells\XLCell.cs - - Excel\Cells\XLCellCollectionOLD.cs - Excel\Cells\XLCells.cs diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj index c7fc1f4..31f2a4c 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -63,6 +63,8 @@ + + diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Cells/XLCellTest.cs b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Cells/XLCellTest.cs new file mode 100644 index 0000000..8ab8b17 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Cells/XLCellTest.cs @@ -0,0 +1,99 @@ +using ClosedXML.Excel; +using Microsoft.VisualStudio.TestTools.UnitTesting; +using System.Linq; +using System; + +namespace ClosedXML_Tests +{ + [TestClass()] + public class XLCellTest + { + [TestMethod()] + public void IsEmpty1() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + var cell = ws.Cell(1, 1); + var actual = cell.IsEmpty(); + var expected = true; + Assert.AreEqual(expected, actual); + } + + [TestMethod()] + public void IsEmpty2() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + var cell = ws.Cell(1, 1); + var actual = cell.IsEmpty(true); + var expected = true; + Assert.AreEqual(expected, actual); + } + + [TestMethod()] + public void IsEmpty3() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + var cell = ws.Cell(1, 1); + cell.Style.Fill.BackgroundColor = XLColor.Red; + var actual = cell.IsEmpty(); + var expected = true; + Assert.AreEqual(expected, actual); + } + + [TestMethod()] + public void IsEmpty4() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + var cell = ws.Cell(1, 1); + cell.Style.Fill.BackgroundColor = XLColor.Red; + var actual = cell.IsEmpty(false); + var expected = true; + Assert.AreEqual(expected, actual); + } + + [TestMethod()] + public void IsEmpty5() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + var cell = ws.Cell(1, 1); + cell.Style.Fill.BackgroundColor = XLColor.Red; + var actual = cell.IsEmpty(true); + var expected = false; + Assert.AreEqual(expected, actual); + } + + [TestMethod()] + public void IsEmpty6() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + var cell = ws.Cell(1, 1); + cell.Value = "X"; + var actual = cell.IsEmpty(); + var expected = false; + Assert.AreEqual(expected, actual); + } + + [TestMethod()] + public void ValueSetToEmptyString() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + var cell = ws.Cell(1, 1); + cell.Value = new DateTime(2000, 1, 2); + cell.Value = String.Empty; + var actual = cell.GetString(); + var expected = String.Empty; + Assert.AreEqual(expected, actual); + } + + [TestMethod()] + public void ValueSetToNull() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + var cell = ws.Cell(1, 1); + cell.Value = new DateTime(2000, 1, 2); + cell.Value = null; + var actual = cell.GetString(); + var expected = String.Empty; + Assert.AreEqual(expected, actual); + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTest.cs b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTest.cs new file mode 100644 index 0000000..403cc73 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTest.cs @@ -0,0 +1,82 @@ +using ClosedXML.Excel; +using Microsoft.VisualStudio.TestTools.UnitTesting; +using System.Linq; +using System; + +namespace ClosedXML_Tests +{ + [TestClass()] + public class XLRangeBaseTest + { + [TestMethod()] + public void IsEmpty1() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + var cell = ws.Cell(1, 1); + var range = ws.Range("A1:B2"); + var actual = range.IsEmpty(); + var expected = true; + Assert.AreEqual(expected, actual); + } + + [TestMethod()] + public void IsEmpty2() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + var cell = ws.Cell(1, 1); + var range = ws.Range("A1:B2"); + var actual = range.IsEmpty(true); + var expected = true; + Assert.AreEqual(expected, actual); + } + + [TestMethod()] + public void IsEmpty3() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + var cell = ws.Cell(1, 1); + cell.Style.Fill.BackgroundColor = XLColor.Red; + var range = ws.Range("A1:B2"); + var actual = range.IsEmpty(); + var expected = true; + Assert.AreEqual(expected, actual); + } + + [TestMethod()] + public void IsEmpty4() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + var cell = ws.Cell(1, 1); + cell.Style.Fill.BackgroundColor = XLColor.Red; + var range = ws.Range("A1:B2"); + var actual = range.IsEmpty(false); + var expected = true; + Assert.AreEqual(expected, actual); + } + + [TestMethod()] + public void IsEmpty5() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + var cell = ws.Cell(1, 1); + cell.Style.Fill.BackgroundColor = XLColor.Red; + var range = ws.Range("A1:B2"); + var actual = range.IsEmpty(true); + var expected = false; + Assert.AreEqual(expected, actual); + } + + [TestMethod()] + public void IsEmpty6() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + var cell = ws.Cell(1, 1); + cell.Value = "X"; + var range = ws.Range("A1:B2"); + var actual = range.IsEmpty(); + var expected = false; + Assert.AreEqual(expected, actual); + } + + } +}