diff --git a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj index f8c5fc6..09e95a6 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj @@ -53,6 +53,10 @@ + + + + diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index 85e36dd..89cd937 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -3,6 +3,8 @@ using System.Linq; using System.Text; using System.Text.RegularExpressions; +using System.Collections; +using System.Data; namespace ClosedXML.Excel @@ -120,73 +122,212 @@ } set { - FormulaA1 = String.Empty; - String val = value.ToString(); - Double dTest; - DateTime dtTest; - Boolean bTest; - if (initialized) + if (!SetEnumerable(value)) + if (!SetRange(value)) + SetValue(value); + } + } + + private Boolean SetRange(Object rangeObject) + { + var asRange = rangeObject as XLRangeBase; + if (asRange != null) + { + worksheet.Range(Address.RowNumber, Address.ColumnNumber, asRange.RowCount(), asRange.ColumnCount()).Clear(); + for (var ro = 1; ro <= asRange.RowCount(); ro++) { - if (dataType == XLCellValues.Boolean) + for (var co = 1; co <= asRange.RowCount(); co++) { - if (Boolean.TryParse(val, out bTest)) - val = bTest ? "1" : "0"; - else if (!(val == "1" || val == "0")) - throw new ArgumentException("'" + val + "' is not a Boolean type."); - } - else if (dataType == XLCellValues.DateTime) - { - if (DateTime.TryParse(val, out dtTest)) - { - - val = dtTest.ToOADate().ToString(); - } - else if (!Double.TryParse(val, out dTest)) - { - throw new ArgumentException("'" + val + "' is not a DateTime type."); - } - - if (Style.NumberFormat.Format == String.Empty && Style.NumberFormat.NumberFormatId == 0) - Style.NumberFormat.NumberFormatId = 14; - } - else if (dataType == XLCellValues.Number) - { - if (!Double.TryParse(val, out dTest)) - throw new ArgumentException("'" + val + "' is not a Numeric type."); - + var sourceCell = asRange.Cell(ro, co); + var targetCell = worksheet.Cell(Address.RowNumber + ro - 1, Address.ColumnNumber + co - 1); + targetCell.Style = sourceCell.Style; + targetCell.DataType = sourceCell.DataType; + targetCell.Value = sourceCell.Value; + targetCell.FormulaA1 = sourceCell.FormulaA1; } } - else + var rangesToMerge = new List(); + foreach (var merge in asRange.Worksheet.Internals.MergedCells) { - if (val.Length > 0 && val.Substring(0, 1) == "'") + if (asRange.ContainsRange(merge)) { - val = val.Substring(1, val.Length - 1); - dataType = XLCellValues.Text; + var mergedRange = worksheet.Range(merge); + var initialRo = Address.RowNumber + (mergedRange.RangeAddress.FirstAddress.RowNumber - asRange.RangeAddress.FirstAddress.RowNumber); + var initialCo = Address.ColumnNumber + (mergedRange.RangeAddress.FirstAddress.ColumnNumber - asRange.RangeAddress.FirstAddress.ColumnNumber); + rangesToMerge.Add(worksheet.Range(initialRo, initialCo, initialRo + mergedRange.RowCount() - 1, initialCo + mergedRange.ColumnCount() - 1)); } - else if (Double.TryParse(val, out dTest)) + } + rangesToMerge.ForEach(r => r.Merge()); + + return true; + } + else + { + return false; + } + + } + + private Boolean SetEnumerable(Object collectionObject) + { + var asEnumerable = collectionObject as IEnumerable; + if (asEnumerable != null && collectionObject.GetType() != typeof(String)) + { + Int32 ro = Address.RowNumber; + Int32 maxCo = 0; + foreach (var m in asEnumerable) + { + Int32 co = Address.ColumnNumber; + + if (m.GetType().IsPrimitive || m.GetType() == typeof(String) || m.GetType() == typeof(DateTime)) { - dataType = XLCellValues.Number; + SetValue(m, ro, co); } - else if (DateTime.TryParse(val, out dtTest)) + else if (m.GetType().IsArray) { - dataType = XLCellValues.DateTime; - Style.NumberFormat.NumberFormatId = 14; - val = dtTest.ToOADate().ToString(); + dynamic arr = m; + foreach (var item in arr) + { + SetValue(item, ro, co); + co++; + } } - else if (Boolean.TryParse(val, out bTest)) + else if ((m as DataRow) != null) { - dataType = XLCellValues.Boolean; - val = bTest ? "1" : "0"; + foreach (var item in (m as DataRow).ItemArray) + { + SetValue(item, ro, co); + co++; + } } else { - dataType = XLCellValues.Text; + var fieldInfo = m.GetType().GetFields(); + foreach (var info in fieldInfo) + { + SetValue(info.GetValue(m), ro, co); + co++; + } + var propertyInfo = m.GetType().GetProperties(); + foreach (var info in propertyInfo) + { + if ((info as IEnumerable) == null) + SetValue(info.GetValue(m, null), ro, co); + co++; + } } + + if (co > maxCo) + maxCo = co; + + ro++; } - cellValue = val; + ClearMerged(ro - 1, maxCo - 1); + return true; + } + else + { + return false; } } + private void ClearMerged(Int32 rowCount, Int32 columnCount) + { + List mergeToDelete = new List(); + foreach (var merge in worksheet.Internals.MergedCells) + { + var ma = new XLRangeAddress(merge); + + if (!( // See if the two ranges intersect... + ma.FirstAddress.ColumnNumber > Address.ColumnNumber + columnCount + || ma.LastAddress.ColumnNumber < Address.ColumnNumber + || ma.FirstAddress.RowNumber > Address.RowNumber + rowCount + || ma.LastAddress.RowNumber < Address.RowNumber + )) + { + mergeToDelete.Add(merge); + } + } + mergeToDelete.ForEach(m => worksheet.Internals.MergedCells.Remove(m)); + } + + private void SetValue(object objWithValue, int ro, int co) + { + String str = String.Empty; + if (objWithValue != null) + str = objWithValue.ToString(); + + worksheet.Cell(ro, co).Value = str; + } + + private void SetValue(Object value) + { + FormulaA1 = String.Empty; + String val = value.ToString(); + Double dTest; + DateTime dtTest; + Boolean bTest; + if (initialized) + { + if (dataType == XLCellValues.Boolean) + { + if (Boolean.TryParse(val, out bTest)) + val = bTest ? "1" : "0"; + else if (!(val == "1" || val == "0")) + throw new ArgumentException("'" + val + "' is not a Boolean type."); + } + else if (dataType == XLCellValues.DateTime) + { + if (DateTime.TryParse(val, out dtTest)) + { + + val = dtTest.ToOADate().ToString(); + } + else if (!Double.TryParse(val, out dTest)) + { + throw new ArgumentException("'" + val + "' is not a DateTime type."); + } + + if (Style.NumberFormat.Format == String.Empty && Style.NumberFormat.NumberFormatId == 0) + Style.NumberFormat.NumberFormatId = 14; + } + else if (dataType == XLCellValues.Number) + { + if (!Double.TryParse(val, out dTest)) + throw new ArgumentException("'" + val + "' is not a Numeric type."); + + } + } + else + { + if (val.Length > 0 && val.Substring(0, 1) == "'") + { + val = val.Substring(1, val.Length - 1); + dataType = XLCellValues.Text; + } + else if (Double.TryParse(val, out dTest)) + { + dataType = XLCellValues.Number; + } + else if (DateTime.TryParse(val, out dtTest)) + { + dataType = XLCellValues.DateTime; + Style.NumberFormat.NumberFormatId = 14; + val = dtTest.ToOADate().ToString(); + } + else if (Boolean.TryParse(val, out bTest)) + { + dataType = XLCellValues.Boolean; + val = bTest ? "1" : "0"; + } + else + { + dataType = XLCellValues.Text; + } + } + cellValue = val; + } + #region IXLStylized Members private IXLStyle style; @@ -369,42 +510,141 @@ } private enum FormulaConversionType { A1toR1C1, R1C1toA1 }; - private static Regex a1Regex = new Regex(@"\$?[a-zA-Z]{1,3}\$?\d+"); - private static Regex r1c1Regex = new Regex(@"[Rr]\[?-?\d*\]?[Cc]\[?-?\d*\]?"); - private String GetFormula(String value, FormulaConversionType conversionType) + private static Regex a1Regex = new Regex(@"\W(\$?[a-zA-Z]{1,3}\$?\d{1,7})\W|\W(\d{1,7}:\d{1,7})\W|\W([a-zA-Z]{1,3}:[a-zA-Z]{1,3})\W"); + private static Regex r1c1Regex = new Regex( + @"\W([Rr]\[?-?\d{0,7}\]?[Cc]\[?-?\d{0,7}\]?)\W" // R1C1 + + @"|\W([Rr]\[?-?\d{0,7}\]?:[Rr]\[?-?\d{0,7}\]?)\W" // R:R + + @"|\W([Cc]\[?-?\d{0,3}\]?:[Cc]\[?-?\d{0,3}\]?)\W"); // C:C + private String GetFormula(String strValue, FormulaConversionType conversionType) { - if (String.IsNullOrWhiteSpace(value)) + if (String.IsNullOrWhiteSpace(strValue)) return String.Empty; + var value = ">" + strValue + "<"; + Regex regex = conversionType == FormulaConversionType.A1toR1C1 ? a1Regex : r1c1Regex; var sb = new StringBuilder(); var lastIndex = 0; - var matches = regex.Matches(value); - foreach (var i in Enumerable.Range(0, matches.Count)) + var matchList = new List>(); + PopulateMatchList(value, 0, matchList, regex); + foreach (var kp in matchList) { - var m = matches[i]; - sb.Append(value.Substring(lastIndex, m.Index - lastIndex)); - - if (conversionType == FormulaConversionType.A1toR1C1) - sb.Append(GetR1C1Address(m.Value)); - else - sb.Append(GetA1Address(m.Value)); - lastIndex = m.Index + m.Value.Length; + var matchString = kp.Key; + var matchIndex = kp.Value; + if (value.Substring(0, matchIndex).CharCount('"') % 2 == 0) // Check if the match is in between quotes + { + sb.Append(value.Substring(lastIndex, matchIndex - lastIndex)); + if (conversionType == FormulaConversionType.A1toR1C1) + sb.Append(GetR1C1Address(matchString)); + else + sb.Append(GetA1Address(matchString)); + } + else + { + sb.Append(value.Substring(lastIndex, matchIndex - lastIndex + matchString.Length)); + } + lastIndex = matchIndex + matchString.Length; } if (lastIndex < value.Length) sb.Append(value.Substring(lastIndex)); var retVal = sb.ToString(); - return retVal; + return retVal.Substring(1, retVal.Length - 2); + } + + private void PopulateMatchList(string value, Int32 startIndex, List> matchList, Regex regex) + { + var match = regex.Match(value, startIndex); + if (match.Success) + { + //var groups = from g in match.Groups.Cast() where g.Success select g; + var matchGroup = (from g in match.Groups.Cast() where g.Success select g).ElementAt(1); + matchList.Add(new KeyValuePair(matchGroup.Value, matchGroup.Index)); + if (matchGroup.Index + matchGroup.Value.Length < value.Length) + { + //var newValue = value.Substring(matchGroup.Index + matchGroup.Value.Length); + PopulateMatchList(value, matchGroup.Index + matchGroup.Value.Length, matchList, regex); + } + } } private String GetA1Address(String r1c1Address) { var addressToUse = r1c1Address.ToUpper(); - var rowPart = addressToUse.Substring(0, addressToUse.IndexOf("C")); + if (addressToUse.Contains(':')) + { + var parts = addressToUse.Split(':'); + var p1 = parts[0]; + var p2 = parts[1]; + String leftPart; + String rightPart; + if (p1.StartsWith("R")) + { + leftPart = GetA1Row(p1); + rightPart = GetA1Row(p2); + } + else + { + leftPart = GetA1Column(p1); + rightPart = GetA1Column(p2); + } + return leftPart + ":" + rightPart; + } + else + { + + var rowPart = addressToUse.Substring(0, addressToUse.IndexOf("C")); + String rowToReturn = GetA1Row(rowPart); + + var columnPart = addressToUse.Substring(addressToUse.IndexOf("C")); + String columnToReturn = GetA1Column(columnPart); + //var cIndex = addressToUse.IndexOf("C"); + //String columnToReturn; + //if (cIndex == addressToUse.Length - 1) + //{ + // columnToReturn = Address.ColumnLetter; + //} + //else + //{ + // var columnPart = addressToUse.Substring(cIndex); + // var bIndex = columnPart.IndexOf("["); + // if (bIndex >= 0) + // columnToReturn = XLAddress.GetColumnLetterFromNumber( + // Address.ColumnNumber + Int32.Parse(columnPart.Substring(bIndex + 1, columnPart.Length - bIndex - 2))); + // else + // columnToReturn = "$" + XLAddress.GetColumnLetterFromNumber(Int32.Parse(columnPart.Substring(1))); + //} + + var retAddress = columnToReturn + rowToReturn; + return retAddress; + } + } + + private String GetA1Column(String columnPart) + { + String columnToReturn; + if (columnPart == "C") + { + columnToReturn = Address.ColumnLetter; + } + else + { + var bIndex = columnPart.IndexOf("["); + if (bIndex >= 0) + columnToReturn = XLAddress.GetColumnLetterFromNumber( + Address.ColumnNumber + Int32.Parse(columnPart.Substring(bIndex + 1, columnPart.Length - bIndex - 2)) + ); + else + columnToReturn = "$" + XLAddress.GetColumnLetterFromNumber(Int32.Parse(columnPart.Substring(1))); + } + return columnToReturn; + } + + private String GetA1Row(String rowPart) + { String rowToReturn; if (rowPart == "R") { @@ -414,61 +654,81 @@ { var bIndex = rowPart.IndexOf("["); if (bIndex >= 0) - rowToReturn = (Address.RowNumber + Int32.Parse(rowPart.Substring(bIndex + 1, rowPart.Length - bIndex - 1))).ToString(); + rowToReturn = (Address.RowNumber + Int32.Parse(rowPart.Substring(bIndex + 1, rowPart.Length - bIndex - 2))).ToString(); else rowToReturn = "$" + rowPart.Substring(1); } - - var cIndex = addressToUse.IndexOf("C"); - String columnToReturn; - if (cIndex == addressToUse.Length - 1) - { - columnToReturn = Address.ColumnLetter; - } - else - { - var columnPart = addressToUse.Substring(cIndex); - var bIndex = columnPart.IndexOf("["); - if (bIndex >= 0) - columnToReturn = XLAddress.GetColumnLetterFromNumber( - Address.ColumnNumber + Int32.Parse(columnPart.Substring(bIndex + 1, columnPart.Length - bIndex - 2))); - else - columnToReturn = "$" + XLAddress.GetColumnLetterFromNumber(Int32.Parse(columnPart.Substring(1))); - } - - var retAddress = columnToReturn + rowToReturn; - return retAddress; + return rowToReturn; } private String GetR1C1Address(String a1Address) { - var address = new XLAddress(a1Address); - - String rowPart; - var rowDiff = address.RowNumber - Address.RowNumber; - if (rowDiff != 0 || address.FixedRow) + if (a1Address.Contains(':')) { - if (address.FixedRow) - rowPart = String.Format("R{0}", address.RowNumber); + var parts = a1Address.Split(':'); + var p1 = parts[0]; + var p2 = parts[1]; + Int32 row1; + if (Int32.TryParse(p1.Replace("$", ""), out row1)) + { + var row2 = Int32.Parse(p2.Replace("$", "")); + var leftPart = GetR1C1Row(row1, p1.Contains('$')); + var rightPart = GetR1C1Row(row2, p2.Contains('$')); + return leftPart + ":" + rightPart; + } + else + { + var column1 = XLAddress.GetColumnNumberFromLetter(p1.Replace("$", "")); + var column2 = XLAddress.GetColumnNumberFromLetter(p2.Replace("$", "")); + var leftPart = GetR1C1Column(column1, p1.Contains('$')); + var rightPart = GetR1C1Column(column2, p2.Contains('$')); + return leftPart + ":" + rightPart; + } + } + else + { + var address = new XLAddress(a1Address); + + String rowPart = GetR1C1Row(address.RowNumber, address.FixedRow); + String columnPart = GetR1C1Column(address.ColumnNumber, address.FixedRow); + + return rowPart + columnPart; + } + } + + private String GetR1C1Row(Int32 rowNumber, Boolean fixedRow) + { + String rowPart; + var rowDiff = rowNumber - Address.RowNumber; + if (rowDiff != 0 || fixedRow) + { + if (fixedRow) + rowPart = String.Format("R{0}", rowNumber); else rowPart = String.Format("R[{0}]", rowDiff); } else rowPart = "R"; + return rowPart; + } + + private String GetR1C1Column(Int32 columnNumber, Boolean fixedColumn) + { String columnPart; - var columnDiff = address.ColumnNumber - Address.ColumnNumber; - if (columnDiff != 0 || address.FixedColumn) + var columnDiff = columnNumber - Address.ColumnNumber; + if (columnDiff != 0 || fixedColumn) { - if(address.FixedColumn) - columnPart = String.Format("C{0}", address.ColumnNumber); + if (fixedColumn) + columnPart = String.Format("C{0}", columnNumber); else columnPart = String.Format("C[{0}]", columnDiff); } else columnPart = "C"; - return rowPart + columnPart; + return columnPart; } + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs index 89d3ef0..af7ee5d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs @@ -47,5 +47,9 @@ void CollapseColumns(Int32 outlineLevel); void ExpandRows(Int32 outlineLevel); void ExpandColumns(Int32 outlineLevel); + + void Delete(); + + IXLNamedRanges NamedRanges { get; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheetInternals.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheetInternals.cs index c3c4a59..9bd19c6 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheetInternals.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheetInternals.cs @@ -11,5 +11,6 @@ XLColumnsCollection ColumnsCollection { get; } XLRowsCollection RowsCollection { get; } List MergedCells { get; } + XLWorkbook Workbook { get; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheets.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheets.cs index e3826b9..d312566 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheets.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheets.cs @@ -7,9 +7,10 @@ { public interface IXLWorksheets: IEnumerable { - IXLWorksheet GetWorksheet(String sheetName); - IXLWorksheet GetWorksheet(Int32 sheetIndex); + IXLWorksheet Worksheet(String sheetName); + IXLWorksheet Worksheet(Int32 sheetIndex); IXLWorksheet Add(String sheetName); void Delete(String sheetName); + void Delete(Int32 sheetIndex); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs new file mode 100644 index 0000000..b92ca17 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs @@ -0,0 +1,15 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public interface IXLNamedRange + { + String Name { get; set; } + IXLRanges Ranges { get; } + IXLRange Range { get; } + String Comment { get; set; } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/IXLNamedRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/IXLNamedRanges.cs new file mode 100644 index 0000000..221d369 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/IXLNamedRanges.cs @@ -0,0 +1,18 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public interface IXLNamedRanges: IEnumerable + { + IXLNamedRange NamedRange(String rangeName); + IXLNamedRange NamedRange(Int32 rangeIndex); + IXLNamedRange Add(String rangeName, String rangeAddress, String comment = null); + IXLNamedRange Add(String rangeName, IXLRange range, String comment = null); + IXLNamedRange Add(String rangeName, IXLRanges ranges, String comment = null); + void Delete(String rangeName); + void Delete(Int32 rangeIndex); + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs new file mode 100644 index 0000000..c6b9e40 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs @@ -0,0 +1,61 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal class XLNamedRange: IXLNamedRange + { + private List rangeList = new List(); + private XLWorkbook workbook; + public XLNamedRange(XLWorkbook workbook, String rangeName, String range, String comment = null) + { + Name = rangeName; + rangeList.Add(range); + Comment = comment; + this.workbook = workbook; + } + + public XLNamedRange(XLWorkbook workbook, String rangeName, IXLRanges ranges, String comment = null) + { + Name = rangeName; + ranges.ForEach(r => rangeList.Add(r.ToString())); + Comment = comment; + this.workbook = workbook; + } + + public String Name { get; set; } + public IXLRanges Ranges + { + get + { + var ranges = new XLRanges(workbook.Style); + foreach (var rangeAddress in rangeList) + { + var byExclamation = rangeAddress.Split('!'); + var wsName = byExclamation[0].Replace("'", ""); + var rng = byExclamation[1]; + var rangeToAdd = workbook.Worksheets.Worksheet(wsName).Range(rng); + ranges.Add(rangeToAdd); + } + return ranges; + } + } + public IXLRange Range + { + get + { + return Ranges.Single(); + } + } + public String Comment { get; set; } + + public override string ToString() + { + String retVal = rangeList.Aggregate(String.Empty, (agg, r) => agg += r + ","); + if (retVal.Length > 0) retVal = retVal.Substring(0, retVal.Length - 1); + return retVal; + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs new file mode 100644 index 0000000..5e82a1e --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs @@ -0,0 +1,81 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal class XLNamedRanges: IXLNamedRanges + { + Dictionary namedRanges = new Dictionary(); + private XLWorkbook workbook; + public XLNamedRanges(XLWorkbook workbook) + { + this.workbook = workbook; + } + + #region IXLNamedRanges Members + + public IXLNamedRange NamedRange(String rangeName) + { + return namedRanges[rangeName]; + } + + public IXLNamedRange NamedRange(Int32 rangeIndex) + { + return namedRanges.ElementAt(rangeIndex).Value; + } + + public IXLNamedRange Add(String rangeName, String rangeAddress, String comment = null) + { + var namedRange = new XLNamedRange(workbook, rangeName, rangeAddress, comment); + namedRanges.Add(rangeName, namedRange); + return namedRange; + } + + public IXLNamedRange Add(String rangeName, IXLRange range, String comment = null) + { + var ranges = new XLRanges(range.Style); + ranges.Add(range); + return Add(rangeName, ranges, comment); + } + + public IXLNamedRange Add(String rangeName, IXLRanges ranges, String comment = null) + { + var namedRange = new XLNamedRange(workbook, rangeName, ranges, comment); + namedRanges.Add(rangeName, namedRange); + return namedRange; + } + + public void Delete(String rangeName) + { + namedRanges.Remove(rangeName); + } + + public void Delete(Int32 rangeIndex) + { + namedRanges.Remove(namedRanges.ElementAt(rangeIndex).Key); + } + + #endregion + + #region IEnumerable Members + + public IEnumerator GetEnumerator() + { + return namedRanges.Values.GetEnumerator(); + } + + #endregion + + #region IEnumerable Members + + System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() + { + return GetEnumerator(); + } + + #endregion + + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeAddress.cs index 6c720af..7edd278 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeAddress.cs @@ -7,6 +7,7 @@ { public interface IXLRangeAddress { + //IXLWorksheet Worksheet { get; set; } IXLAddress FirstAddress { get; set; } IXLAddress LastAddress { get; set; } Boolean IsInvalid { get; set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs index dc4b760..e5ac900 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -5,6 +5,7 @@ namespace ClosedXML.Excel { + public enum XLScope { Workbook, Worksheet }; public interface IXLRangeBase: IXLStylized { IEnumerable Cells(); @@ -24,5 +25,6 @@ void Merge(); IXLRange AsRange(); Boolean ContainsRange(String rangeAddress); + void CreateNamedRange(String rangeName, XLScope scope = XLScope.Workbook, String comment = null); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs index 397a7d4..28c627e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs @@ -9,5 +9,6 @@ { void Clear(); void Add(IXLRange range); + void Remove(IXLRange range); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs index 8de081c..61a7a72 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs @@ -391,8 +391,14 @@ { if (this.ContainsRange(merge)) { + String addressToUse; + if (merge.Contains("!")) + addressToUse = merge.Substring(merge.IndexOf("!") + 1); + else + addressToUse = merge; + mergeToDelete.Add(merge); - String[] arrRange = merge.Split(':'); + String[] arrRange = addressToUse.Split(':'); var firstAddress = new XLAddress(arrRange[0]); var lastAddress = new XLAddress(arrRange[1]); var newLastAddress = new XLAddress(lastAddress.ColumnNumber, lastAddress.RowNumber); @@ -442,6 +448,5 @@ } #endregion - } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs index 9ae76d5..9b12026 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs @@ -7,6 +7,8 @@ { internal class XLRangeAddress: IXLRangeAddress { + //public IXLWorksheet Worksheet { get; set; } + private IXLAddress firstAddress; public IXLAddress FirstAddress { @@ -55,11 +57,17 @@ public XLRangeAddress(String rangeAddress) { + String addressToUse; + if (rangeAddress.Contains("!")) + addressToUse = rangeAddress.Substring(rangeAddress.IndexOf("!") + 1); + else + addressToUse = rangeAddress; + XLAddress firstAddress; XLAddress lastAddress; - if (rangeAddress.Contains(':')) + if (addressToUse.Contains(':')) { - String[] arrRange = rangeAddress.Split(':'); + String[] arrRange = addressToUse.Split(':'); var firstPart = arrRange[0]; var secondPart = arrRange[1]; firstAddress = new XLAddress(firstPart); @@ -67,8 +75,8 @@ } else { - firstAddress = new XLAddress(rangeAddress); - lastAddress = new XLAddress(rangeAddress); + firstAddress = new XLAddress(addressToUse); + lastAddress = new XLAddress(addressToUse); } FirstAddress = firstAddress; LastAddress = lastAddress; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index dfe99a4..d76eb7f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -139,7 +139,7 @@ public IXLRanges Ranges( String ranges) { - var retVal = new XLRanges(Worksheet); + var retVal = new XLRanges(Worksheet.Style); var rangePairs = ranges.Split(','); foreach (var pair in rangePairs) { @@ -149,7 +149,7 @@ } public IXLRanges Ranges( params String[] ranges) { - var retVal = new XLRanges(Worksheet); + var retVal = new XLRanges(Worksheet.Style); foreach (var pair in ranges) { retVal.Add(this.Range(pair)); @@ -411,18 +411,24 @@ public Boolean ContainsRange(String rangeAddress) { + String addressToUse; + if (rangeAddress.Contains("!")) + addressToUse = rangeAddress.Substring(rangeAddress.IndexOf("!") + 1); + else + addressToUse = rangeAddress; + XLAddress firstAddress; XLAddress lastAddress; - if (rangeAddress.Contains(':')) + if (addressToUse.Contains(':')) { - String[] arrRange = rangeAddress.Split(':'); + String[] arrRange = addressToUse.Split(':'); firstAddress = new XLAddress(arrRange[0]); lastAddress = new XLAddress(arrRange[1]); } else { - firstAddress = new XLAddress(rangeAddress); - lastAddress = new XLAddress(rangeAddress); + firstAddress = new XLAddress(addressToUse); + lastAddress = new XLAddress(addressToUse); } return firstAddress >= (XLAddress)this.RangeAddress.FirstAddress @@ -517,7 +523,20 @@ public override string ToString() { - return RangeAddress.FirstAddress.ToString() + ":" + RangeAddress.LastAddress.ToString(); + var sb = new StringBuilder(); + sb.Append("'"); + sb.Append(Worksheet.Name); + sb.Append("'!"); + var firstAddress = new XLAddress(RangeAddress.FirstAddress.ToString()); + firstAddress.FixedColumn = true; + firstAddress.FixedRow = true; + sb.Append(firstAddress.ToString()); + sb.Append(":"); + var lastAddress = new XLAddress(RangeAddress.LastAddress.ToString()); + lastAddress.FixedColumn = true; + lastAddress.FixedRow = true; + sb.Append(lastAddress.ToString()); + return sb.ToString(); } public String FormulaA1 @@ -535,5 +554,16 @@ } } + public void CreateNamedRange(String rangeName, XLScope scope = XLScope.Workbook, String comment = null) + { + if (scope == XLScope.Workbook) + { + Worksheet.Internals.Workbook.NamedRanges.Add(rangeName, this.AsRange(), comment); + } + else + { + Worksheet.NamedRanges.Add(rangeName, this.AsRange(), comment); + } + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs index 856ba10..4cd6cc2 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs @@ -7,9 +7,9 @@ { internal class XLRanges : IXLRanges { - public XLRanges(XLWorksheet worksheet) + public XLRanges(IXLStyle defaultStyle) { - Style = worksheet.Style; + Style = defaultStyle; } List ranges = new List(); @@ -24,6 +24,11 @@ ranges.Add((XLRange)range); } + public void Remove(IXLRange range) + { + ranges.RemoveAll(r => r.ToString() == range.ToString()); + } + public IEnumerator GetEnumerator() { return ranges.ToList().GetEnumerator(); @@ -76,5 +81,12 @@ public Boolean UpdatingStyle { get; set; } #endregion + + public override string ToString() + { + String retVal = ranges.Aggregate(String.Empty, (agg, r)=> agg += r.ToString() + ","); + if (retVal.Length > 0) retVal = retVal.Substring(0, retVal.Length - 1); + return retVal; + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs index a862ae4..fc38114 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs @@ -249,8 +249,8 @@ // This formula is based on this article plus a nudge ( + 0.2M ) // http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.column.width.aspx // Truncate(((256 * Solve_For_This + Truncate(128 / 7)) / 256) * 7) = DeterminePixelsOfString - - Size textSize = TextRenderer.MeasureText(text, stringFont); + String textToUse = new String('X', text.Length); + Size textSize = TextRenderer.MeasureText(textToUse, stringFont); double width = (double)(((textSize.Width / (double)7) * 256) - (128 / 7)) / 256; width = (double)decimal.Round((decimal)width + 0.2M, 2); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs index abbec39..e979b5f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs @@ -176,7 +176,12 @@ #region Overrides public override string ToString() { - return this.columnLetter + this.rowNumber.ToString(); + var sb = new StringBuilder(); + if (fixedColumn) sb.Append("$"); + sb.Append(this.columnLetter); + if (fixedRow) sb.Append("$"); + sb.Append(this.rowNumber.ToString()); + return sb.ToString(); } #endregion diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs index ed7c534..cda483a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs @@ -17,6 +17,7 @@ DefaultRowHeight = 15; DefaultColumnWidth = 9.140625; Worksheets = new XLWorksheets(this); + NamedRanges = new XLNamedRanges(this); PopulateEnums(); Style = DefaultStyle; RowHeight = DefaultRowHeight; @@ -36,6 +37,7 @@ #region IXLWorkbook Members public IXLWorksheets Worksheets { get; private set; } + public IXLNamedRanges NamedRanges { get; private set; } /// /// Gets the file name of the workbook. diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index d7f3f1b..0d19b03 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -64,12 +64,8 @@ Borders borders = (Borders)s.Borders; Fonts fonts = (Fonts)s.Fonts; - - //return items[int.Parse(headCell.CellValue.Text)].InnerText; - var sheets = dSpreadsheet.WorkbookPart.Workbook.Sheets; - - // For each sheet, display the sheet information. + foreach (var sheet in sheets) { var dSheet = ((Sheet)sheet); @@ -319,31 +315,45 @@ var workbook = (Workbook)dSpreadsheet.WorkbookPart.Workbook; foreach (var definedName in workbook.Descendants()) { - if (definedName.Name == "_xlnm.Print_Area") + var name = definedName.Name; + if (name == "_xlnm.Print_Area") { foreach (var area in definedName.Text.Split(',')) { var sections = area.Split('!'); var sheetName = sections[0].Replace("\'", ""); var sheetArea = sections[1]; - Worksheets.GetWorksheet(sheetName).PageSetup.PrintAreas.Add(sheetArea); + Worksheets.Worksheet(sheetName).PageSetup.PrintAreas.Add(sheetArea); } } - else if (definedName.Name == "_xlnm.Print_Titles") + else if (name == "_xlnm.Print_Titles") { var areas = definedName.Text.Split(','); var colSections = areas[0].Split('!'); var sheetNameCol = colSections[0].Replace("\'", ""); var sheetAreaCol = colSections[1]; - Worksheets.GetWorksheet(sheetNameCol).PageSetup.SetColumnsToRepeatAtLeft(sheetAreaCol); + Worksheets.Worksheet(sheetNameCol).PageSetup.SetColumnsToRepeatAtLeft(sheetAreaCol); var rowSections = areas[1].Split('!'); var sheetNameRow = rowSections[0].Replace("\'", ""); var sheetAreaRow = rowSections[1]; - Worksheets.GetWorksheet(sheetNameRow).PageSetup.SetRowsToRepeatAtTop(sheetAreaRow); + Worksheets.Worksheet(sheetNameRow).PageSetup.SetRowsToRepeatAtTop(sheetAreaRow); } - //ws.PageSetup.PrintAreas. + else + { + var localSheetId = definedName.LocalSheetId; + var comment = definedName.Comment; + var text = definedName.Text; + if (localSheetId == null) + { + NamedRanges.Add(name, text, comment); + } + else + { + Worksheets.Worksheet(Int32.Parse(localSheetId)).NamedRanges.Add(name, text, comment); + } + } } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index d2bb917..7692c62 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -290,9 +290,13 @@ variant3.Append(vTLPSTR2); + var namedCount = NamedRanges.Count() + Worksheets.Aggregate(0, (counter, ws) => counter += ws.NamedRanges.Count()); Vt.Variant variant4 = new Vt.Variant(); Vt.VTInt32 vTInt322 = new Vt.VTInt32(); - vTInt322.Text = (Worksheets.Count() * 2).ToString(); + vTInt322.Text = ( + Worksheets.Count() * 2 // for the worksheets print area and titles + + namedCount + ).ToString(); variant4.Append(vTInt322); @@ -306,7 +310,7 @@ Ap.TitlesOfParts titlesOfParts1 = new Ap.TitlesOfParts(); UInt32 sheetCount = (UInt32)Worksheets.Count(); - Vt.VTVector vTVector2 = new Vt.VTVector() { BaseType = Vt.VectorBaseValues.Lpstr, Size = (UInt32Value)sheetCount * 3 }; + Vt.VTVector vTVector2 = new Vt.VTVector() { BaseType = Vt.VectorBaseValues.Lpstr, Size = (UInt32Value)(sheetCount * 3 + namedCount) }; foreach (var worksheet in Worksheets) { Vt.VTLPSTR vTLPSTR3 = new Vt.VTLPSTR(); @@ -320,6 +324,20 @@ Vt.VTLPSTR vTLPSTR5 = new Vt.VTLPSTR(); vTLPSTR5.Text = worksheet.Name + "!Print_Titles"; vTVector2.Append(vTLPSTR5); + + foreach (var nr in worksheet.NamedRanges) + { + Vt.VTLPSTR vTLPSTR6 = new Vt.VTLPSTR(); + vTLPSTR6.Text = worksheet.Name + "!" + nr.Name; + vTVector2.Append(vTLPSTR6); + } + } + + foreach (var nr in NamedRanges) + { + Vt.VTLPSTR vTLPSTR7 = new Vt.VTLPSTR(); + vTLPSTR7.Text = nr.Name; + vTVector2.Append(vTLPSTR7); } titlesOfParts1.Append(vTVector2); @@ -390,11 +408,21 @@ + printArea.RangeAddress.FirstAddress.ToString() + ":" + printArea.RangeAddress.LastAddress.ToString() + ","; } - definedName.Text = definedNameText.Substring(0, definedNameText.Length - 1); definedNames.Append(definedName); } + foreach (var nr in worksheet.NamedRanges) + { + DefinedName definedName = new DefinedName() { + Name = nr.Name, + LocalSheetId = (UInt32Value)sheetId - 1, + Text = nr.ToString() + }; + if (!String.IsNullOrWhiteSpace(nr.Comment)) definedName.Comment = nr.Comment; + definedNames.Append(definedName); + } + var titles = String.Empty; var definedNameTextRow = String.Empty; var definedNameTextColumn = String.Empty; @@ -430,6 +458,17 @@ } } + foreach (var nr in NamedRanges) + { + DefinedName definedName = new DefinedName() + { + Name = nr.Name, + Text = nr.ToString() + }; + if (!String.IsNullOrWhiteSpace(nr.Comment)) definedName.Comment = nr.Comment; + definedNames.Append(definedName); + } + CalculationProperties calculationProperties = new CalculationProperties() { CalculationId = (UInt32Value)125725U }; if (CalculateMode != XLCalculateMode.Default) calculationProperties.CalculationMode = calculateModeValues.Single(p => p.Key == CalculateMode).Value; @@ -774,7 +813,7 @@ SheetDimension sheetDimension = new SheetDimension() { Reference = sheetDimensionReference }; - Boolean tabSelected = xlWorksheet.Name == Worksheets.GetWorksheet(0).Name; + Boolean tabSelected = xlWorksheet.Name == Worksheets.Worksheet(0).Name; SheetViews sheetViews = new SheetViews(); SheetView sheetView = new SheetView() { TabSelected = tabSelected, WorkbookViewId = (UInt32Value)0U }; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index c40cc56..ff538d1 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -21,12 +21,15 @@ #endregion + private XLWorkbook workbook; public XLWorksheet(String sheetName, XLWorkbook workbook) : base((IXLRangeAddress)new XLRangeAddress(new XLAddress(1, 1), new XLAddress(MaxNumberOfRows, MaxNumberOfColumns))) { Worksheet = this; + NamedRanges = new XLNamedRanges(workbook); + this.workbook = workbook; Style = workbook.Style; - Internals = new XLWorksheetInternals(new Dictionary(), new XLColumnsCollection(), new XLRowsCollection(), new List()); + Internals = new XLWorksheetInternals(new Dictionary(), new XLColumnsCollection(), new XLRowsCollection(), new List(), workbook); PageSetup = new XLPageSetup(workbook.PageOptions, this); Outline = new XLOutline(workbook.Outline); ColumnWidth = workbook.ColumnWidth; @@ -51,13 +54,13 @@ rngMerged.RangeAddress.FirstAddress.ColumnNumber + columnsShifted, rngMerged.RangeAddress.LastAddress.RowNumber, rngMerged.RangeAddress.LastAddress.ColumnNumber + columnsShifted); - newMerge.Add(newRng.ToString()); + newMerge.Add(GetRangeLocation(newRng.ToString())); } else if ( !(range.RangeAddress.FirstAddress.ColumnNumber <= rngMerged.RangeAddress.FirstAddress.ColumnNumber && range.RangeAddress.FirstAddress.RowNumber <= rngMerged.RangeAddress.LastAddress.RowNumber)) { - newMerge.Add(rngMerged.ToString()); + newMerge.Add(GetRangeLocation(rngMerged.ToString())); } } Internals.MergedCells = newMerge; @@ -78,17 +81,25 @@ rngMerged.RangeAddress.FirstAddress.ColumnNumber, rngMerged.RangeAddress.LastAddress.RowNumber + rowsShifted, rngMerged.RangeAddress.LastAddress.ColumnNumber); - newMerge.Add(newRng.ToString()); + newMerge.Add(GetRangeLocation(newRng.ToString())); } else if (!(range.RangeAddress.FirstAddress.RowNumber <= rngMerged.RangeAddress.FirstAddress.RowNumber && range.RangeAddress.FirstAddress.ColumnNumber <= rngMerged.RangeAddress.LastAddress.ColumnNumber)) { - newMerge.Add(rngMerged.ToString()); + newMerge.Add(GetRangeLocation(rngMerged.ToString())); } } Internals.MergedCells = newMerge; } + private String GetRangeLocation(String rangeAddress) + { + if (rangeAddress.Contains("!")) + return rangeAddress.Substring(rangeAddress.IndexOf("!") + 1); + else + return rangeAddress; + } + public void NotifyRangeShiftedRows(XLRange range, Int32 rowsShifted) { if (RangeShiftedRows != null) @@ -406,5 +417,12 @@ Internals.ColumnsCollection.Values.Where(c => c.OutlineLevel == outlineLevel).ForEach(c => c.Expand()); } + + public void Delete() + { + workbook.Worksheets.Delete(Name); + } + + public IXLNamedRanges NamedRanges { get; private set; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs index fd888e7..7a304f7 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs @@ -11,17 +11,21 @@ Dictionary cellsCollection , XLColumnsCollection columnsCollection, XLRowsCollection rowsCollection, - List mergedCells) + List mergedCells, + XLWorkbook workbook + ) { CellsCollection = cellsCollection; ColumnsCollection = columnsCollection; RowsCollection = rowsCollection; MergedCells = mergedCells; + Workbook = workbook; } public Dictionary CellsCollection { get; private set; } public XLColumnsCollection ColumnsCollection { get; private set; } public XLRowsCollection RowsCollection { get; private set; } public List MergedCells { get; internal set; } + public XLWorkbook Workbook { get; internal set; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs index 60a3fad..dfe2573 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs @@ -17,12 +17,12 @@ #region IXLWorksheets Members - public IXLWorksheet GetWorksheet(string sheetName) + public IXLWorksheet Worksheet(string sheetName) { return worksheets[sheetName]; } - public IXLWorksheet GetWorksheet(int sheetIndex) + public IXLWorksheet Worksheet(int sheetIndex) { return worksheets.ElementAt(sheetIndex).Value; } @@ -39,6 +39,11 @@ worksheets.Remove(sheetName); } + public void Delete(Int32 sheetIndex) + { + worksheets.Remove(worksheets.ElementAt(sheetIndex).Key); + } + #endregion #region IEnumerable Members diff --git a/ClosedXML/ClosedXML/ClosedXML/Extensions.cs b/ClosedXML/ClosedXML/ClosedXML/Extensions.cs index 6522dbd..1d11c76 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Extensions.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Extensions.cs @@ -61,6 +61,10 @@ return sb.ToString(); } + public static Int32 CharCount(this String instance, Char c) + { + return instance.Length - instance.Replace(c.ToString(), "").Length; + } } public static class DictionaryExtensions diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj index 5e23fdf..e74906e 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -55,6 +55,9 @@ + + + diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs index aa53662..2fe454a 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs @@ -54,6 +54,9 @@ new HideUnhide().Create(@"C:\Excel Files\Created\HideUnhide.xlsx"); new Outline().Create(@"C:\Excel Files\Created\Outline.xlsx"); new Formulas().Create(@"C:\Excel Files\Created\Formulas.xlsx"); + new Collections().Create(@"C:\Excel Files\Created\Collections.xlsx"); + new NamedRanges().Create(@"C:\Excel Files\Created\NamedRanges.xlsx"); + new CopyingRanges().Create(); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/ChangingBasicTable.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/ChangingBasicTable.cs index 649075d..b2fd4d8 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/ChangingBasicTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/ChangingBasicTable.cs @@ -13,7 +13,7 @@ public void Create() { var workbook = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); - var ws = workbook.Worksheets.GetWorksheet(0); + var ws = workbook.Worksheets.Worksheet(0); // Change the background color of the headers var rngHeaders = ws.Range("B3:F3"); diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/Collections.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/Collections.cs new file mode 100644 index 0000000..fc271e7 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/Collections.cs @@ -0,0 +1,129 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; + +using System.Drawing; +using System.Data; + +namespace ClosedXML_Examples.Misc +{ + public class Collections + { + #region Variables + + // Public + + // Private + + + #endregion + + #region Properties + + // Public + + // Private + + // Override + + + #endregion + + #region Events + + // Public + + // Private + + // Override + + + #endregion + + #region Methods + + // Public + public void Create(String filePath) + { + var wb = new XLWorkbook(); + var ws = wb.Worksheets.Add("Collections"); + + // From a list of strings + var listOfStrings = new List(); + listOfStrings.Add("House"); + listOfStrings.Add("Car"); + ws.Cell(1, 1).Value = "Strings"; + ws.Cell(1, 1).Style.Fill.BackgroundColor = Color.Cyan; + ws.Cell(2, 1).Value = listOfStrings; + + // From a list of arrays + var listOfArr = new List(); + listOfArr.Add(new Int32[] { 1, 2, 3 }); + listOfArr.Add(new Int32[] { 1 }); + listOfArr.Add(new Int32[] { 1, 2, 3, 4, 5, 6 }); + ws.Cell(1, 3).Value = "Arrays"; + ws.Cell(1, 3).Style.Fill.BackgroundColor = Color.Cyan; + ws.Range(1, 3, 1, 8).Merge(); + ws.Cell(2, 3).Value = listOfArr; + + // From a DataTable + var dataTable = GetTable(); + ws.Cell(6, 1).Value = "DataTable"; + ws.Cell(6, 1).Style.Fill.BackgroundColor = Color.Cyan; + ws.Range(6, 1, 6, 4).Merge(); + ws.Cell(7, 1).Value = dataTable.AsEnumerable(); + + // From a query + var list = new List(); + list.Add(new Person() { Name = "John", Age = 30, House = "On Elm St." }); + list.Add(new Person() { Name = "Mary", Age = 15, House = "On Main St." }); + list.Add(new Person() { Name = "Luis", Age = 21, House = "On 23rd St." }); + list.Add(new Person() { Name = "Henry", Age = 45, House = "On 5th Ave." }); + + var people = from p in list + where p.Age >= 21 + select new { p.Name, p.House, p.Age }; + + ws.Cell(6, 6).Value = "Query"; + ws.Cell(6, 6).Style.Fill.BackgroundColor = Color.Cyan; + ws.Range(6, 6, 6, 8).Merge(); + ws.Cell(7, 6).Value = people.AsEnumerable(); + + + ws.Columns().AdjustToContents(); + + wb.SaveAs(filePath); + } + + class Person + { + public String House { get; set; } + public String Name { get; set; } + public Int32 Age { get; set; } + } + + // Private + private DataTable GetTable() + { + + DataTable table = new DataTable(); + table.Columns.Add("Dosage", typeof(int)); + table.Columns.Add("Drug", typeof(string)); + table.Columns.Add("Patient", typeof(string)); + table.Columns.Add("Date", typeof(DateTime)); + + table.Rows.Add(25, "Indocin", "David", DateTime.Now); + table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now); + table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now); + table.Rows.Add(21, "Combivent", "Janet", DateTime.Now); + table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now); + return table; + } + // Override + + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/Formulas.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/Formulas.cs index 4afb12b..4a6cafc 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/Formulas.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/Formulas.cs @@ -75,8 +75,17 @@ ws.Cell(3, 5).Value = cellWithFormulaR1C1.FormulaR1C1; ws.Cell(3, 6).Value = cellWithFormulaR1C1.Value; + ws.Cell(4, 1).Value = "A"; + ws.Cell(4, 2).Value = "B"; + var cellWithStringFormula = ws.Cell(4, 3); + // Use R1C1 notation + cellWithStringFormula.FormulaR1C1 = "=\"Test\" & RC[-2] & \"R3C2\""; + ws.Cell(4, 4).Value = cellWithStringFormula.FormulaA1; + ws.Cell(4, 5).Value = cellWithStringFormula.FormulaR1C1; + ws.Cell(4, 6).Value = cellWithStringFormula.Value; + // Setting the formula of a range - var rngData = ws.Range(2, 1, 3, 7); + var rngData = ws.Range(2, 1, 4, 7); rngData.LastColumn().FormulaR1C1 = "=IF(RC[-3]=RC[-1],\"Yes\", \"No\")"; ws.Range(1, 1, 1, 7).Style.Fill.BackgroundColor = Color.Cyan; @@ -84,10 +93,17 @@ ws.Columns().AdjustToContents(); // You can also change the reference notation: - // wb.ReferenceStyle = XLReferenceStyle.R1C1; + wb.ReferenceStyle = XLReferenceStyle.R1C1; // And the workbook calculation mode: - // wb.CalculateMode = XLCalculateMode.Auto; + wb.CalculateMode = XLCalculateMode.Auto; + + ws.Range("A10").CreateNamedRange("A10_R1C1_A10_R1C1"); + ws.Cell("A10").Value = 0; + ws.Cell("A11").FormulaA1 = "A2 + A10_R1C1_A10_R1C1"; + ws.Cell("A12").FormulaR1C1 = "R2C1 + A10_R1C1_A10_R1C1"; + ws.Cell("A13").FormulaR1C1 = "=SUM(R[-5]:R[-4])"; + ws.Cell("A14").FormulaA1 = "=SUM(8:9)"; wb.SaveAs(filePath); } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/LambdaExpressions.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/LambdaExpressions.cs index 15202d0..2b3fe81 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/LambdaExpressions.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/LambdaExpressions.cs @@ -13,7 +13,7 @@ public void Create() { var workbook = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); - var ws = workbook.Worksheets.GetWorksheet(0); + var ws = workbook.Worksheets.Worksheet(0); // Define a range with the data var firstDataCell = ws.Cell("B4"); @@ -24,7 +24,7 @@ rngData.Rows() // From all rows .Where(r => !r.Cell(3).GetBoolean()) // where the 3rd cell of each row is false .ForEach(r => r.Delete()); // delete the row and shift the cells up (the default for rows in a range) - + // Put a light gray background to all text cells rngData.Cells() // From all cells .Where(c => c.DataType == XLCellValues.Text) // where the data type is Text diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MergeMoves.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MergeMoves.cs index 78c9d47..7e57a18 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MergeMoves.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MergeMoves.cs @@ -57,7 +57,7 @@ public void Create() { var workbook = new XLWorkbook(@"C:\Excel Files\Created\MergedCells.xlsx"); - var ws = workbook.Worksheets.GetWorksheet(0); + var ws = workbook.Worksheets.Worksheet(0); ws.Range("B1:F1").InsertRowsBelow(1); ws.Range("A3:A9").InsertColumnsAfter(1); diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/CopyingRanges.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/CopyingRanges.cs new file mode 100644 index 0000000..89b1962 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/CopyingRanges.cs @@ -0,0 +1,30 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; +using System.Drawing; +using ClosedXML; + +namespace ClosedXML_Examples +{ + public class CopyingRanges + { + public void Create() + { + var workbook = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); + var ws = workbook.Worksheets.Worksheet(0); + + // Define a range with the data + var firstTableCell = ws.FirstCellUsed(); + var lastTableCell = ws.LastCellUsed(); + var rngData = ws.Range(firstTableCell.Address, lastTableCell.Address); + + // Copy the table to another worksheet + var wsCopy = workbook.Worksheets.Add("Contacts Copy"); + wsCopy.Cell(1,1).Value = rngData; + + workbook.SaveAs(@"C:\Excel Files\Created\CopyingRanges.xlsx"); + } + } +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/NamedRanges.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/NamedRanges.cs new file mode 100644 index 0000000..7be8a15 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/NamedRanges.cs @@ -0,0 +1,86 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; + +using System.Drawing; + +namespace ClosedXML_Examples.Misc +{ + public class NamedRanges + { + #region Methods + + // Public + public void Create(String filePath) + { + var wb = new XLWorkbook(); + var wsPresentation = wb.Worksheets.Add("Presentation"); + var wsData = wb.Worksheets.Add("Data"); + + // Fill up some data + wsData.Cell(1, 1).Value = "Name"; + wsData.Cell(1, 2).Value = "Age"; + wsData.Cell(2, 1).Value = "Tom"; + wsData.Cell(2, 2).Value = 30; + wsData.Cell(3, 1).Value = "Dick"; + wsData.Cell(3, 2).Value = 25; + wsData.Cell(4, 1).Value = "Harry"; + wsData.Cell(4, 2).Value = 29; + + // Create a named range with the data: + wsData.Range("A2:B4").CreateNamedRange("PeopleData"); // Default named range scope is Workbook + + // Let's use the named range in a formula: + wsPresentation.Cell(1, 1).Value = "People Count:"; + wsPresentation.Cell(1, 2).FormulaA1 = "COUNT(PeopleData)"; + + // Create a named range with worksheet scope: + wsPresentation.Range("B1").CreateNamedRange("PeopleCount", XLScope.Worksheet); + + // Let's use the named range: + wsPresentation.Cell(2, 1).Value = "Total:"; + wsPresentation.Cell(2, 2).FormulaA1 = "PeopleCount"; + + // Copy the data in a named range: + wsPresentation.Cell(4, 1).Value = "People Data:"; + wsPresentation.Cell(5, 1).Value = wb.NamedRanges.NamedRange("PeopleData").Range; + + + ///////////////////////////////////////////////////////////////////////// + // For the Excel geeks out there who actually know about + // named ranges with relative addresses, you can + // create such a thing with the following methods: + + // The following creates a relative named range pointing to the same row + // and one column to the right. For example if the current cell is B4 + // relativeRange1 will point to C4. + wsPresentation.NamedRanges.Add("relativeRange1", "Presentation!B1"); + + // The following creates a ralative named range pointing to the same row + // and one column to the left. For example if the current cell is D2 + // relativeRange2 will point to C2. + wb.NamedRanges.Add("relativeRange2", "Presentation!XFD1"); + + // Explanation: The address of a relative range always starts at A1 + // and moves from then on. To get the desired relative range just + // add or subtract the required rows and/or columns from A1. + // Column -1 = XFD, Column -2 = XFC, etc. + // Row -1 = 1048576, Row -2 = 1048575, etc. + ///////////////////////////////////////////////////////////////////////// + + wsData.Columns().AdjustToContents(); + wsPresentation.Columns().AdjustToContents(); + + wb.SaveAs(filePath); + } + + // Private + + // Override + + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/ShiftingRanges.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/ShiftingRanges.cs index 3877268..a8570ec 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/ShiftingRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/ShiftingRanges.cs @@ -12,7 +12,7 @@ public void Create() { var workbook = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); - var ws = workbook.Worksheets.GetWorksheet(0); + var ws = workbook.Worksheets.Worksheet(0); // Get a range object var rngHeaders = ws.Range("B3:F3"); diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRanges.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRanges.cs index 5bffe8a..0b9b32b 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRanges.cs @@ -13,7 +13,7 @@ public void Create() { var workbook = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); - var ws = workbook.Worksheets.GetWorksheet(0); + var ws = workbook.Worksheets.Worksheet(0); var rngTable = ws.Range("B2:F6"); diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRangesPlus.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRangesPlus.cs index 0343d1e..704d99d 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRangesPlus.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRangesPlus.cs @@ -13,7 +13,7 @@ public void Create() { var workbook = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); - var ws = workbook.Worksheets.GetWorksheet(0); + var ws = workbook.Worksheets.Worksheet(0); var rngTable = ws.Range("B2:F6"); diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index 1f507c9..e98d334 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -14,20 +14,24 @@ { var wb = new XLWorkbook(); var ws = wb.Worksheets.Add("Test"); - var rng = ws.Range("B2:B2"); - ws.Column(2).Delete(); - rng.Style.Fill.BackgroundColor = Color.Blue; + var lst = new List(); + lst.Add(new Person(){ Name = "Manuel", Age = 33}); + lst.Add(new Person() { Name = "Carlos", Age = 32 }); + + ws.Cell(1, 1).Value = lst; + //wb.Load(@"c:\Initial.xlsx"); wb.SaveAs(@"C:\Excel Files\ForTesting\Sandbox.xlsx"); //Console.ReadKey(); } - - // Invalidate range references when they point to a deleted range. - // Implement formulas - // Implement grouping of rows and columns - // Autosize rows/columns + class Person + { + public String Name { get; set; } + public Int32 Age { get; set; } + } + // Save defaults to a .config file // Add/Copy/Paste (maybe another name?) rows, columns, ranges into an area.