diff --git a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj
index d382ef7..f8c5fc6 100644
--- a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj
+++ b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj
@@ -53,6 +53,8 @@
+
+
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs
index 9483aea..f432c60 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs
@@ -21,5 +21,7 @@
DateTime GetDateTime();
void Clear();
void Delete(XLShiftDeletedCells shiftDeleteCells);
+ String FormulaA1 { get; set; }
+ String FormulaR1C1 { get; set; }
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs
index 5518bba..85e36dd 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs
@@ -2,6 +2,7 @@
using System.Collections.Generic;
using System.Linq;
using System.Text;
+using System.Text.RegularExpressions;
namespace ClosedXML.Excel
@@ -96,6 +97,10 @@
{
get
{
+ var fA1 = FormulaA1;
+ if (!String.IsNullOrWhiteSpace(fA1))
+ return fA1;
+
if (dataType == XLCellValues.Boolean)
{
return cellValue != "0";
@@ -115,6 +120,7 @@
}
set
{
+ FormulaA1 = String.Empty;
String val = value.ToString();
Double dTest;
DateTime dtTest;
@@ -323,5 +329,146 @@
}
return formatCodes;
}
+
+ private String formulaA1;
+ public String FormulaA1
+ {
+ get { return formulaA1; }
+ set
+ {
+ formulaA1 = value;
+ formulaR1C1 = String.Empty;
+ }
+ }
+
+ private String formulaR1C1;
+ public String FormulaR1C1
+ {
+ get
+ {
+ if (String.IsNullOrWhiteSpace(formulaR1C1))
+ formulaR1C1 = GetFormulaR1C1(FormulaA1);
+
+ return formulaR1C1;
+ }
+ set
+ {
+ formulaR1C1 = value;
+ FormulaA1 = GetFormulaA1(value);
+ }
+ }
+
+ private String GetFormulaR1C1(String value)
+ {
+ return GetFormula(value, FormulaConversionType.A1toR1C1);
+ }
+
+ private String GetFormulaA1(String value)
+ {
+ return GetFormula(value, FormulaConversionType.R1C1toA1);
+ }
+
+ 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)
+ {
+ if (String.IsNullOrWhiteSpace(value))
+ return String.Empty;
+
+ 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 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;
+ }
+ if (lastIndex < value.Length)
+ sb.Append(value.Substring(lastIndex));
+
+ var retVal = sb.ToString();
+ return retVal;
+ }
+
+ private String GetA1Address(String r1c1Address)
+ {
+ var addressToUse = r1c1Address.ToUpper();
+
+ var rowPart = addressToUse.Substring(0, addressToUse.IndexOf("C"));
+ String rowToReturn;
+ if (rowPart == "R")
+ {
+ rowToReturn = Address.RowNumber.ToString();
+ }
+ else
+ {
+ var bIndex = rowPart.IndexOf("[");
+ if (bIndex >= 0)
+ rowToReturn = (Address.RowNumber + Int32.Parse(rowPart.Substring(bIndex + 1, rowPart.Length - bIndex - 1))).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;
+ }
+
+ private String GetR1C1Address(String a1Address)
+ {
+ var address = new XLAddress(a1Address);
+
+ String rowPart;
+ var rowDiff = address.RowNumber - Address.RowNumber;
+ if (rowDiff != 0 || address.FixedRow)
+ {
+ if (address.FixedRow)
+ rowPart = String.Format("R{0}", address.RowNumber);
+ else
+ rowPart = String.Format("R[{0}]", rowDiff);
+ }
+ else
+ rowPart = "R";
+
+ String columnPart;
+ var columnDiff = address.ColumnNumber - Address.ColumnNumber;
+ if (columnDiff != 0 || address.FixedColumn)
+ {
+ if(address.FixedColumn)
+ columnPart = String.Format("C{0}", address.ColumnNumber);
+ else
+ columnPart = String.Format("C[{0}]", columnDiff);
+ }
+ else
+ columnPart = "C";
+
+ return rowPart + columnPart;
+ }
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs
index 4dea625..e4b8e69 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs
@@ -17,5 +17,15 @@
IXLCell Cell(int row);
void AdjustToContents();
+ void Hide();
+ void Unhide();
+ Boolean IsHidden { get; }
+ Int32 OutlineLevel { get; set; }
+ void Group(Boolean collapse = false);
+ void Group(Int32 outlineLevel, Boolean collapse = false);
+ void Ungroup(Boolean fromAll = false);
+ void Collapse();
+ void Expand();
+
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs
index fdd373a..480a753 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs
@@ -10,5 +10,12 @@
Double Width { set; }
void Delete();
void AdjustToContents();
+ void Hide();
+ void Unhide();
+ void Group(Boolean collapse = false);
+ void Group(Int32 outlineLevel, Boolean collapse = false);
+ void Ungroup(Boolean fromAll = false);
+ void Collapse();
+ void Expand();
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs
index 130b047..3a543df 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs
@@ -208,5 +208,132 @@
Width = maxWidth;
}
+
+ public void Hide()
+ {
+ IsHidden = true;
+ }
+ public void Unhide()
+ {
+ IsHidden = false;
+ }
+ private Boolean isHidden;
+ public Boolean IsHidden
+ {
+ get
+ {
+ if (IsReference)
+ {
+ return Worksheet.Internals.ColumnsCollection[this.ColumnNumber()].IsHidden;
+ }
+ else
+ {
+ return isHidden;
+ }
+ }
+ set
+ {
+ if (IsReference)
+ {
+ Worksheet.Internals.ColumnsCollection[this.ColumnNumber()].IsHidden = value;
+ }
+ else
+ {
+ isHidden = value;
+ }
+ }
+ }
+
+
+ private Boolean collapsed;
+ public Boolean Collapsed
+ {
+ get
+ {
+ if (IsReference)
+ {
+ return Worksheet.Internals.ColumnsCollection[this.ColumnNumber()].Collapsed;
+ }
+ else
+ {
+ return collapsed;
+ }
+ }
+ set
+ {
+ if (IsReference)
+ {
+ Worksheet.Internals.ColumnsCollection[this.ColumnNumber()].Collapsed = value;
+ }
+ else
+ {
+ collapsed = value;
+ }
+ }
+ }
+ private Int32 outlineLevel;
+ public Int32 OutlineLevel
+ {
+ get
+ {
+ if (IsReference)
+ {
+ return Worksheet.Internals.ColumnsCollection[this.ColumnNumber()].OutlineLevel;
+ }
+ else
+ {
+ return outlineLevel;
+ }
+ }
+ set
+ {
+ if (value < 0 || value > 8)
+ throw new ArgumentOutOfRangeException("Outline level must be between 0 and 8.");
+
+ if (IsReference)
+ {
+ Worksheet.Internals.ColumnsCollection[this.ColumnNumber()].OutlineLevel = value;
+ }
+ else
+ {
+ outlineLevel = value;
+ }
+ }
+ }
+
+ public void Group(Boolean collapse = false)
+ {
+ if (OutlineLevel < 8)
+ OutlineLevel += 1;
+
+ Collapsed = collapse;
+ }
+ public void Group(Int32 outlineLevel, Boolean collapse = false)
+ {
+ OutlineLevel = outlineLevel;
+ Collapsed = collapse;
+ }
+ public void Ungroup(Boolean ungroupFromAll = false)
+ {
+ if (ungroupFromAll)
+ {
+ OutlineLevel = 0;
+ }
+ else
+ {
+ if (OutlineLevel > 0)
+ OutlineLevel -= 1;
+ }
+ }
+ public void Collapse()
+ {
+ Collapsed = true;
+ Hide();
+ }
+ public void Expand()
+ {
+ Collapsed = false;
+ Unhide();
+ }
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs
index 15733c6..f2c0e44 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs
@@ -145,5 +145,35 @@
{
columns.ForEach(c => c.AdjustToContents());
}
+
+ public void Hide()
+ {
+ columns.ForEach(c => c.Hide());
+ }
+ public void Unhide()
+ {
+ columns.ForEach(c => c.Unhide());
+ }
+
+ public void Group(Boolean collapse = false)
+ {
+ columns.ForEach(c => c.Group(collapse));
+ }
+ public void Group(Int32 outlineLevel, Boolean collapse = false)
+ {
+ columns.ForEach(c => c.Group(outlineLevel, collapse));
+ }
+ public void Ungroup(Boolean ungroupFromAll = false)
+ {
+ columns.ForEach(c => c.Ungroup(ungroupFromAll));
+ }
+ public void Collapse()
+ {
+ columns.ForEach(c => c.Collapse());
+ }
+ public void Expand()
+ {
+ columns.ForEach(c => c.Expand());
+ }
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLOutline.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLOutline.cs
new file mode 100644
index 0000000..244929c
--- /dev/null
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLOutline.cs
@@ -0,0 +1,15 @@
+using System;
+using System.Collections.Generic;
+using System.Linq;
+using System.Text;
+
+namespace ClosedXML.Excel
+{
+ public enum XLOutlineSummaryVLocation { Top, Bottom };
+ public enum XLOutlineSummaryHLocation { Left, Right };
+ public interface IXLOutline
+ {
+ XLOutlineSummaryVLocation SummaryVLocation { get; set; }
+ XLOutlineSummaryHLocation SummaryHLocation { get; set; }
+ }
+}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs
index bd93bfd..89d3ef0 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs
@@ -12,6 +12,7 @@
String Name { get; set; }
IXLPageSetup PageSetup { get; }
+ IXLOutline Outline { get; }
IXLRow FirstRowUsed();
IXLRow LastRowUsed();
@@ -36,5 +37,15 @@
int RowCount();
int ColumnCount();
+
+ void CollapseRows();
+ void CollapseColumns();
+ void ExpandRows();
+ void ExpandColumns();
+
+ void CollapseRows(Int32 outlineLevel);
+ void CollapseColumns(Int32 outlineLevel);
+ void ExpandRows(Int32 outlineLevel);
+ void ExpandColumns(Int32 outlineLevel);
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs
index affe7bd..72fdb41 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs
@@ -48,6 +48,8 @@
void Clear();
void Transpose(XLTransposeOptions transposeOption);
+ String FormulaA1 { set; }
+ String FormulaR1C1 { set; }
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs
index e9a215d..164046c 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs
@@ -22,6 +22,8 @@
void Delete(XLShiftDeletedCells shiftDeleteCells = XLShiftDeletedCells.ShiftCellsLeft);
void Clear();
+ String FormulaA1 { set; }
+ String FormulaR1C1 { set; }
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs
index c48e535..c5745f0 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs
@@ -9,5 +9,7 @@
{
void Clear();
void Add(IXLRangeColumn range);
+ String FormulaA1 { set; }
+ String FormulaR1C1 { set; }
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs
index 5371f82..338a4ea 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs
@@ -24,6 +24,8 @@
void Delete(XLShiftDeletedCells shiftDeleteCells = XLShiftDeletedCells.ShiftCellsUp);
void Clear();
+ String FormulaA1 { set; }
+ String FormulaR1C1 { set; }
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs
index 81b0ab3..dc54bf7 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs
@@ -9,5 +9,7 @@
{
void Clear();
void Add(IXLRangeRow range);
+ String FormulaA1 { set; }
+ String FormulaR1C1 { set; }
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs
index 1d5e3d9..8de081c 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs
@@ -440,6 +440,7 @@
}
}
}
+
#endregion
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs
index 12cf0a1..dfe99a4 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs
@@ -520,5 +520,20 @@
return RangeAddress.FirstAddress.ToString() + ":" + RangeAddress.LastAddress.ToString();
}
+ public String FormulaA1
+ {
+ set
+ {
+ Cells().ForEach(c => c.FormulaA1 = value);
+ }
+ }
+ public String FormulaR1C1
+ {
+ set
+ {
+ Cells().ForEach(c => c.FormulaR1C1 = value);
+ }
+ }
+
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs
index 273f634..40a6b76 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs
@@ -76,5 +76,20 @@
public Boolean UpdatingStyle { get; set; }
#endregion
+
+ public String FormulaA1
+ {
+ set
+ {
+ ranges.ForEach(r => r.FormulaA1 = value);
+ }
+ }
+ public String FormulaR1C1
+ {
+ set
+ {
+ ranges.ForEach(r => r.FormulaR1C1 = value);
+ }
+ }
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs
index e0e71b5..ce0d6e4 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs
@@ -76,5 +76,20 @@
public Boolean UpdatingStyle { get; set; }
#endregion
+
+ public String FormulaA1
+ {
+ set
+ {
+ ranges.ForEach(r => r.FormulaA1 = value);
+ }
+ }
+ public String FormulaR1C1
+ {
+ set
+ {
+ ranges.ForEach(r => r.FormulaR1C1 = value);
+ }
+ }
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs
index e75cfe3..1f9ea2b 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs
@@ -18,5 +18,14 @@
IXLCell Cell(String column);
void AdjustToContents();
+ void Hide();
+ void Unhide();
+ Boolean IsHidden { get; }
+ Int32 OutlineLevel { get; set; }
+ void Group(Boolean collapse = false);
+ void Group(Int32 outlineLevel, Boolean collapse = false);
+ void Ungroup(Boolean fromAll = false);
+ void Collapse();
+ void Expand();
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs
index 96f45c5..6f87a9b 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs
@@ -10,5 +10,12 @@
Double Height { set; }
void Delete();
void AdjustToContents();
+ void Hide();
+ void Unhide();
+ void Group(Boolean collapse = false);
+ void Group(Int32 outlineLevel, Boolean collapse = false);
+ void Ungroup(Boolean fromAll = false);
+ void Collapse();
+ void Expand();
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs
index 7cc9a1c..a5bb7c3 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs
@@ -135,6 +135,41 @@
Height = maxHeight;
}
+ public void Hide()
+ {
+ IsHidden = true;
+ }
+ public void Unhide()
+ {
+ IsHidden = false;
+ }
+ private Boolean isHidden;
+ public Boolean IsHidden
+ {
+ get
+ {
+ if (IsReference)
+ {
+ return Worksheet.Internals.RowsCollection[this.RowNumber()].IsHidden;
+ }
+ else
+ {
+ return isHidden;
+ }
+ }
+ set
+ {
+ if (IsReference)
+ {
+ Worksheet.Internals.RowsCollection[this.RowNumber()].IsHidden = value;
+ }
+ else
+ {
+ isHidden = value;
+ }
+ }
+ }
+
#endregion
#region IXLStylized Members
@@ -214,5 +249,96 @@
}
#endregion
+
+ private Boolean collapsed;
+ public Boolean Collapsed
+ {
+ get
+ {
+ if (IsReference)
+ {
+ return Worksheet.Internals.RowsCollection[this.RowNumber()].Collapsed;
+ }
+ else
+ {
+ return collapsed;
+ }
+ }
+ set
+ {
+ if (IsReference)
+ {
+ Worksheet.Internals.RowsCollection[this.RowNumber()].Collapsed = value;
+ }
+ else
+ {
+ collapsed = value;
+ }
+ }
+ }
+ private Int32 outlineLevel;
+ public Int32 OutlineLevel
+ {
+ get
+ {
+ if (IsReference)
+ {
+ return Worksheet.Internals.RowsCollection[this.RowNumber()].OutlineLevel;
+ }
+ else
+ {
+ return outlineLevel;
+ }
+ }
+ set
+ {
+ if (value < 1 || value > 8)
+ throw new ArgumentOutOfRangeException("Outline level must be between 1 and 8.");
+
+ if (IsReference)
+ {
+ Worksheet.Internals.RowsCollection[this.RowNumber()].OutlineLevel = value;
+ }
+ else
+ {
+ outlineLevel = value;
+ }
+ }
+ }
+
+ public void Group(Boolean collapse = false)
+ {
+ if (OutlineLevel < 8)
+ OutlineLevel += 1;
+
+ Collapsed = collapse;
+ }
+ public void Group(Int32 outlineLevel, Boolean collapse = false)
+ {
+ OutlineLevel = outlineLevel;
+ Collapsed = collapse;
+ }
+ public void Ungroup(Boolean ungroupFromAll = false)
+ {
+ if (ungroupFromAll)
+ {
+ OutlineLevel = 0;
+ }
+ else
+ {
+ if (OutlineLevel > 0)
+ OutlineLevel -= 1;
+ }
+ }
+ public void Collapse()
+ {
+ Collapsed = true;
+ Hide();
+ }
+ public void Expand()
+ {
+ Collapsed = false;
+ Unhide();
+ }
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs
index acdc9d7..b46050d 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs
@@ -142,5 +142,35 @@
{
rows.ForEach(r => r.AdjustToContents());
}
+
+ public void Hide()
+ {
+ rows.ForEach(r => r.Hide());
+ }
+ public void Unhide()
+ {
+ rows.ForEach(r => r.Unhide());
+ }
+
+ public void Group(Boolean collapse = false)
+ {
+ rows.ForEach(r => r.Group(collapse));
+ }
+ public void Group(Int32 outlineLevel, Boolean collapse = false)
+ {
+ rows.ForEach(r => r.Group(outlineLevel, collapse));
+ }
+ public void Ungroup(Boolean ungroupFromAll = false)
+ {
+ rows.ForEach(r => r.Ungroup(ungroupFromAll));
+ }
+ public void Collapse()
+ {
+ rows.ForEach(r => r.Collapse());
+ }
+ public void Expand()
+ {
+ rows.ForEach(r => r.Expand());
+ }
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs
index 34f157a..abbec39 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs
@@ -8,6 +8,7 @@
{
internal struct XLAddress: IXLAddress
{
+ private static Regex a1Regex = new Regex(@"^(\$?[a-zA-Z]{1,3})(\$?\d+)$");
#region Constructors
///
/// Initializes a new struct using R1C1 notation.
@@ -19,6 +20,8 @@
this.rowNumber = rowNumber;
this.columnNumber = columnNumber;
this.columnLetter = GetColumnLetterFromNumber(columnNumber);
+ fixedColumn = false;
+ fixedRow = false;
}
///
@@ -31,6 +34,8 @@
this.rowNumber = rowNumber;
this.columnNumber = GetColumnNumberFromLetter(columnLetter);
this.columnLetter = columnLetter;
+ fixedColumn = false;
+ fixedRow = false;
}
@@ -40,10 +45,12 @@
/// The cell address.
public XLAddress(String cellAddressString)
{
- Match m = Regex.Match(cellAddressString, @"^([a-zA-Z]+)(\d+)$");
- columnLetter = m.Groups[1].Value;
- this.rowNumber = Int32.Parse(m.Groups[2].Value);
- this.columnNumber = GetColumnNumberFromLetter(columnLetter);
+ Match m = a1Regex.Match(cellAddressString);
+ fixedColumn = m.Groups[1].Value.StartsWith("$");
+ columnLetter = m.Groups[1].Value.Replace("$", "");
+ fixedRow = m.Groups[1].Value.StartsWith("$");
+ rowNumber = Int32.Parse(m.Groups[2].Value.Replace("$", ""));
+ columnNumber = GetColumnNumberFromLetter(columnLetter);
}
#endregion
@@ -120,6 +127,20 @@
#region Properties
+ private Boolean fixedRow;
+ public Boolean FixedRow
+ {
+ get { return fixedRow; }
+ set { fixedRow = value; }
+ }
+
+ private Boolean fixedColumn;
+ public Boolean FixedColumn
+ {
+ get { return fixedColumn; }
+ set { fixedColumn = value; }
+ }
+
private Int32 rowNumber;
///
/// Gets the row number of this address.
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLOutline.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLOutline.cs
new file mode 100644
index 0000000..7bc72ac
--- /dev/null
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLOutline.cs
@@ -0,0 +1,21 @@
+using System;
+using System.Collections.Generic;
+using System.Linq;
+using System.Text;
+
+namespace ClosedXML.Excel
+{
+ internal class XLOutline:IXLOutline
+ {
+ public XLOutline(IXLOutline outline)
+ {
+ if (outline != null)
+ {
+ SummaryHLocation = outline.SummaryHLocation;
+ SummaryVLocation = outline.SummaryVLocation;
+ }
+ }
+ public XLOutlineSummaryVLocation SummaryVLocation { get; set; }
+ public XLOutlineSummaryHLocation SummaryHLocation { get; set; }
+ }
+}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs
index 2abc97c..ed7c534 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs
@@ -8,6 +8,8 @@
namespace ClosedXML.Excel
{
+ public enum XLCalculateMode { Auto, AutoNoTable, Manual, Default };
+ public enum XLReferenceStyle { R1C1, A1, Default };
public partial class XLWorkbook
{
public XLWorkbook()
@@ -20,7 +22,10 @@
RowHeight = DefaultRowHeight;
ColumnWidth = DefaultColumnWidth;
PageOptions = DefaultPageOptions;
+ Outline = DefaultOutline;
Properties = new XLWorkbookProperties();
+ CalculateMode = XLCalculateMode.Default;
+ ReferenceStyle = XLReferenceStyle.Default;
}
public XLWorkbook(String file): this()
@@ -55,7 +60,10 @@
public Double RowHeight { get; set; }
public Double ColumnWidth { get; set; }
public IXLPageSetup PageOptions { get; set; }
+ public IXLOutline Outline { get; set; }
public XLWorkbookProperties Properties { get; set; }
+ public XLCalculateMode CalculateMode { get; set; }
+ public XLReferenceStyle ReferenceStyle { get; set; }
#endregion
@@ -150,6 +158,16 @@
}
}
+ public static IXLOutline DefaultOutline
+ {
+ get
+ {
+ return new XLOutline(null) {
+ SummaryHLocation = XLOutlineSummaryHLocation.Right,
+ SummaryVLocation= XLOutlineSummaryVLocation.Bottom };
+ }
+ }
+
public static IXLFont GetXLFont()
{
return new XLFont();
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs
index 1860352..d7f3f1b 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs
@@ -38,12 +38,25 @@
sharedStrings = shareStringPart.SharedStringTable.Elements().ToArray();
}
+ var referenceMode = dSpreadsheet.WorkbookPart.Workbook.CalculationProperties.ReferenceMode;
+ if (referenceMode != null)
+ {
+ ReferenceStyle = referenceModeValues.Single(p => p.Value == referenceMode.Value).Key;
+ }
+
+ var calculateMode = dSpreadsheet.WorkbookPart.Workbook.CalculationProperties.CalculationMode;
+ if (calculateMode != null)
+ {
+ CalculateMode = calculateModeValues.Single(p => p.Value == calculateMode.Value).Key;
+ }
+
if (dSpreadsheet.ExtendedFilePropertiesPart.Properties.Elements().Count() > 0)
Properties.Company = dSpreadsheet.ExtendedFilePropertiesPart.Properties.GetFirstChild().Text;
if (dSpreadsheet.ExtendedFilePropertiesPart.Properties.Elements().Count() > 0)
Properties.Manager = dSpreadsheet.ExtendedFilePropertiesPart.Properties.GetFirstChild().Text;
+
var workbookStylesPart = (WorkbookStylesPart)dSpreadsheet.WorkbookPart.WorkbookStylesPart;
var s = (Stylesheet)workbookStylesPart.Stylesheet;
var numberingFormats = (NumberingFormats)s.NumberingFormats;
@@ -94,10 +107,20 @@
if (col.Max != XLWorksheet.MaxNumberOfColumns)
{
toApply = ws.Columns(col.Min, col.Max);
- if (col.Width != null)
- ((XLColumns)toApply).Width = col.Width;
+ var xlColumns = (XLColumns)toApply;
+ if (col.Width != null)
+ xlColumns.Width = col.Width;
else
- ((XLColumns)toApply).Width = ws.ColumnWidth;
+ xlColumns.Width = ws.ColumnWidth;
+
+ if (col.Hidden != null && col.Hidden)
+ xlColumns.Hide();
+
+ if (col.Collapsed != null && col.Collapsed)
+ xlColumns.Collapse();
+
+ 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)
@@ -120,6 +143,15 @@
else
xlRow.Height = ws.RowHeight;
+ if (row.Hidden != null && row.Hidden)
+ xlRow.Hide();
+
+ if (row.Collapsed != null && row.Collapsed)
+ xlRow.Collapse();
+
+ if (row.OutlineLevel != null)
+ xlRow.OutlineLevel = row.OutlineLevel;
+
Int32 styleIndex = row.StyleIndex != null ? Int32.Parse(row.StyleIndex.InnerText) : -1;
if (styleIndex > 0)
{
@@ -147,12 +179,19 @@
xlCell.Style = DefaultStyle;
}
- if (dCell.DataType != null)
+ if(dCell.CellFormula != null)
+ {
+ xlCell.FormulaA1 = dCell.CellFormula.Text;
+ }
+ else if (dCell.DataType != null)
{
if (dCell.DataType == CellValues.SharedString)
{
xlCell.DataType = XLCellValues.Text;
- xlCell.Value = sharedStrings[Int32.Parse(dCell.CellValue.Text)].InnerText;
+ if (!String.IsNullOrWhiteSpace(dCell.CellValue.Text))
+ xlCell.Value = sharedStrings[Int32.Parse(dCell.CellValue.Text)].InnerText;
+ else
+ xlCell.Value = dCell.CellValue.Text;
}
else if (dCell.DataType == CellValues.Date)
{
@@ -213,24 +252,48 @@
if (pageSetup.VerticalDpi != null) ws.PageSetup.VerticalDpi = Int32.Parse(pageSetup.VerticalDpi.InnerText);
if (pageSetup.FirstPageNumber != null) ws.PageSetup.FirstPageNumber = Int32.Parse(pageSetup.FirstPageNumber.InnerText);
- var headerFooter = (HeaderFooter)worksheetPart.Worksheet.Descendants().First();
- ws.PageSetup.AlignHFWithMargins = headerFooter.AlignWithMargins;
- // Footers
- var xlFooter = (XLHeaderFooter)ws.PageSetup.Footer;
- var evenFooter = (EvenFooter)headerFooter.EvenFooter;
- xlFooter.SetInnerText(XLHFOccurrence.EvenPages, evenFooter.Text);
- var oddFooter = (OddFooter)headerFooter.OddFooter;
- xlFooter.SetInnerText(XLHFOccurrence.OddPages, oddFooter.Text);
- var firstFooter = (FirstFooter)headerFooter.FirstFooter;
- xlFooter.SetInnerText(XLHFOccurrence.FirstPage, firstFooter.Text);
- // Headers
- var xlHeader = (XLHeaderFooter)ws.PageSetup.Header;
- var evenHeader = (EvenHeader)headerFooter.EvenHeader;
- xlHeader.SetInnerText(XLHFOccurrence.EvenPages, evenHeader.Text);
- var oddHeader = (OddHeader)headerFooter.OddHeader;
- xlHeader.SetInnerText(XLHFOccurrence.OddPages, oddHeader.Text);
- var firstHeader = (FirstHeader)headerFooter.FirstHeader;
- xlHeader.SetInnerText(XLHFOccurrence.FirstPage, firstHeader.Text);
+ var headerFooters = worksheetPart.Worksheet.Descendants();
+ if (headerFooters.Count() > 0)
+ {
+ var headerFooter = (HeaderFooter)headerFooters.First();
+ ws.PageSetup.AlignHFWithMargins = headerFooter.AlignWithMargins;
+
+ // Footers
+ var xlFooter = (XLHeaderFooter)ws.PageSetup.Footer;
+ var evenFooter = (EvenFooter)headerFooter.EvenFooter;
+ xlFooter.SetInnerText(XLHFOccurrence.EvenPages, evenFooter.Text);
+ var oddFooter = (OddFooter)headerFooter.OddFooter;
+ xlFooter.SetInnerText(XLHFOccurrence.OddPages, oddFooter.Text);
+ var firstFooter = (FirstFooter)headerFooter.FirstFooter;
+ xlFooter.SetInnerText(XLHFOccurrence.FirstPage, firstFooter.Text);
+ // Headers
+ var xlHeader = (XLHeaderFooter)ws.PageSetup.Header;
+ var evenHeader = (EvenHeader)headerFooter.EvenHeader;
+ xlHeader.SetInnerText(XLHFOccurrence.EvenPages, evenHeader.Text);
+ var oddHeader = (OddHeader)headerFooter.OddHeader;
+ xlHeader.SetInnerText(XLHFOccurrence.OddPages, oddHeader.Text);
+ var firstHeader = (FirstHeader)headerFooter.FirstHeader;
+ xlHeader.SetInnerText(XLHFOccurrence.FirstPage, firstHeader.Text);
+ }
+ var sheetProperties = worksheetPart.Worksheet.Descendants();
+ if (sheetProperties.Count() > 0)
+ {
+ var sheetProperty = (SheetProperties)sheetProperties.First();
+ 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;
+ }
+ }
+ }
var rowBreaksList = worksheetPart.Worksheet.Descendants();
if (rowBreaksList.Count() > 0)
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs
index bfc28c8..d2bb917 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs
@@ -29,6 +29,8 @@
private List> pageOrientationValues = new List>();
private List> showCommentsValues = new List>();
private List> printErrorValues = new List>();
+ private List> calculateModeValues = new List>();
+ private List> referenceModeValues = new List>();
private void PopulateEnums()
{
PopulateFillPatternValues();
@@ -41,6 +43,8 @@
PopulatePageOrientationValues();
PopulateShowCommentsValues();
PopulatePrintErrorValues();
+ PopulateCalculateModeValues();
+ PoulateReferenceModeValues();
}
private enum RelType { General, Workbook, Worksheet }
@@ -198,6 +202,18 @@
printErrorValues.Add(new KeyValuePair(XLPrintErrorValues.NA, PrintErrorValues.NA));
}
+ private void PopulateCalculateModeValues()
+ {
+ calculateModeValues.Add(new KeyValuePair(XLCalculateMode.Auto, CalculateModeValues.Auto)) ;
+ calculateModeValues.Add(new KeyValuePair(XLCalculateMode.AutoNoTable, CalculateModeValues.AutoNoTable));
+ calculateModeValues.Add(new KeyValuePair(XLCalculateMode.Manual, CalculateModeValues.Manual));
+ }
+
+ private void PoulateReferenceModeValues()
+ {
+ referenceModeValues.Add(new KeyValuePair(XLReferenceStyle.R1C1, ReferenceModeValues.R1C1));
+ referenceModeValues.Add(new KeyValuePair(XLReferenceStyle.A1, ReferenceModeValues.A1));
+ }
// Creates a SpreadsheetDocument.
private void CreatePackage(String filePath)
@@ -233,6 +249,8 @@
GenerateWorksheetPartContent(worksheetPart, (XLWorksheet)worksheet);
}
+ GenerateCalculationChainPartContent(workbookPart, "rId" + (startId + 4));
+
ThemePart themePart1 = workbookPart.AddNewPart("rId" + (startId + 1));
GenerateThemePartContent(themePart1);
@@ -404,7 +422,6 @@
titles = definedNameTextRow;
}
-
if (titles.Length > 0)
{
DefinedName definedName = new DefinedName() { Name = "_xlnm.Print_Titles", LocalSheetId = (UInt32Value)sheetId - 1 };
@@ -413,14 +430,20 @@
}
}
- CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)125725U, CalculationMode = CalculateModeValues.Manual };
+ CalculationProperties calculationProperties = new CalculationProperties() { CalculationId = (UInt32Value)125725U };
+ if (CalculateMode != XLCalculateMode.Default)
+ calculationProperties.CalculationMode = calculateModeValues.Single(p => p.Key == CalculateMode).Value;
+
+ if (ReferenceStyle != XLReferenceStyle.Default)
+ calculationProperties.ReferenceMode = referenceModeValues.Single(p=>p.Key==ReferenceStyle).Value;
+
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(bookViews1);
workbook1.Append(sheets);
if (definedNames.Count() > 0) workbook1.Append(definedNames);
- workbook1.Append(calculationProperties1);
+ workbook1.Append(calculationProperties);
workbookPart.Workbook = workbook1;
}
@@ -708,18 +731,23 @@
workbookStylesPart.Stylesheet = stylesheet1;
}
-
-
private void GenerateWorksheetPartContent(WorksheetPart worksheetPart, XLWorksheet xlWorksheet)
{
Worksheet worksheet = new Worksheet();
worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
SheetProperties sheetProperties = new SheetProperties() { CodeName = xlWorksheet.Name.RemoveSpecialCharacters() };
+ OutlineProperties outlineProperties = new OutlineProperties() {
+ SummaryBelow = (xlWorksheet.Outline.SummaryVLocation == XLOutlineSummaryVLocation.Bottom),
+ SummaryRight = (xlWorksheet.Outline.SummaryHLocation == XLOutlineSummaryHLocation.Right)
+ };
+ sheetProperties.Append(outlineProperties);
+
if (xlWorksheet.PageSetup.PagesTall > 0 || xlWorksheet.PageSetup.PagesWide > 0)
{
PageSetupProperties pageSetupProperties = new PageSetupProperties() { FitToPage = true };
sheetProperties.Append(pageSetupProperties);
}
+
UInt32 maxColumn = 0;
UInt32 maxRow = 0;
@@ -752,7 +780,20 @@
SheetView sheetView = new SheetView() { TabSelected = tabSelected, WorkbookViewId = (UInt32Value)0U };
sheetViews.Append(sheetView);
+
+ var maxOutlineColumn = 0;
+ if (xlWorksheet.Columns().Count() > 0)
+ maxOutlineColumn = xlWorksheet.Columns().Cast().Max(c => c.OutlineLevel);
+
+ var maxOutlineRow = 0;
+ if (xlWorksheet.Rows().Count() > 0)
+ maxOutlineRow = xlWorksheet.Rows().Cast().Max(c => c.OutlineLevel);
+
SheetFormatProperties sheetFormatProperties3 = new SheetFormatProperties() { DefaultRowHeight = xlWorksheet.RowHeight, DefaultColumnWidth = xlWorksheet.ColumnWidth , CustomHeight = true };
+ if (maxOutlineColumn > 0)
+ sheetFormatProperties3.OutlineLevelColumn = (byte)maxOutlineColumn;
+ if (maxOutlineRow > 0)
+ sheetFormatProperties3.OutlineLevelRow = (byte)maxOutlineRow;
Columns columns = new Columns();
@@ -787,10 +828,16 @@
{
UInt32 styleId;
Double columnWidth;
+ Boolean isHidden = false;
+ Boolean collapsed = false;
+ Int32 outlineLevel = 0;
if (xlWorksheet.Internals.ColumnsCollection.ContainsKey(co))
{
styleId = sharedStyles[xlWorksheet.Internals.ColumnsCollection[co].Style.ToString()].StyleId;
columnWidth = xlWorksheet.Internals.ColumnsCollection[co].Width;
+ isHidden = xlWorksheet.Internals.ColumnsCollection[co].IsHidden;
+ collapsed = xlWorksheet.Internals.ColumnsCollection[co].Collapsed;
+ outlineLevel = xlWorksheet.Internals.ColumnsCollection[co].OutlineLevel;
}
else
{
@@ -806,6 +853,9 @@
Width = columnWidth,
CustomWidth = true
};
+ if (isHidden) column.Hidden = true;
+ if (collapsed) column.Collapsed = true;
+ if (outlineLevel > 0) column.OutlineLevel = (byte)outlineLevel;
columns.Append(column);
}
@@ -843,11 +893,15 @@
row.CustomHeight = true;
row.StyleIndex = sharedStyles[thisRowStyleString].StyleId;
row.CustomFormat = true;
+ if (thisRow.IsHidden) row.Hidden = true;
+ if (thisRow.Collapsed) row.Collapsed = true;
+ if (thisRow.OutlineLevel > 0) row.OutlineLevel = (byte)thisRow.OutlineLevel;
}
else
{
row.Height = xlWorksheet.RowHeight;
row.CustomHeight = true;
+ row.Hidden = false;
}
foreach (var opCell in xlWorksheet.Internals.CellsCollection
@@ -859,42 +913,50 @@
Cell cell;
var dataType = opCell.Value.DataType;
var cellReference = opCell.Key.ToString();
- if (opCell.Value.DataType == XLCellValues.DateTime)
+ if (!String.IsNullOrWhiteSpace(opCell.Value.FormulaA1))
{
- cell = new Cell()
- {
- CellReference = cellReference,
- StyleIndex = styleId
- };
- }
- else if (styleId == 0)
- {
- cell = new Cell()
- {
- CellReference = cellReference,
- DataType = GetCellValue(dataType)
- };
+ cell = new Cell() { CellReference = cellReference, StyleIndex = styleId };
+ cell.Append(new CellFormula(opCell.Value.FormulaA1));
}
else
{
- cell = new Cell()
+ if (opCell.Value.DataType == XLCellValues.DateTime)
{
- CellReference = cellReference,
- DataType = GetCellValue(dataType),
- StyleIndex = styleId
- };
+ cell = new Cell()
+ {
+ CellReference = cellReference,
+ StyleIndex = styleId
+ };
+ }
+ else if (styleId == 0)
+ {
+ cell = new Cell()
+ {
+ CellReference = cellReference,
+ DataType = GetCellValue(dataType)
+ };
+ }
+ else
+ {
+ cell = new Cell()
+ {
+ CellReference = cellReference,
+ DataType = GetCellValue(dataType),
+ StyleIndex = styleId
+ };
+ }
+ CellValue cellValue = new CellValue();
+ if (dataType == XLCellValues.Text && !String.IsNullOrWhiteSpace(opCell.Value.InnerText))
+ {
+ cellValue.Text = sharedStrings[opCell.Value.InnerText].ToString();
+ }
+ else
+ {
+ cellValue.Text = opCell.Value.InnerText;
+ }
+ cell.Append(cellValue);
}
- CellValue cellValue = new CellValue();
- if (dataType == XLCellValues.Text)
- {
- cellValue.Text = sharedStrings[opCell.Value.InnerText].ToString();
- }
- else
- {
- cellValue.Text = opCell.Value.InnerText;
- }
-
- cell.Append(cellValue);
+
row.Append(cell);
}
sheetData.Append(row);
@@ -1014,7 +1076,6 @@
Break break1 = new Break() { Id = (UInt32Value)(UInt32)cb, Max = (UInt32Value)(UInt32)xlWorksheet.RangeAddress.LastAddress.ColumnNumber, ManualPageBreak = true };
columnBreaks.Append(break1);
}
-
}
worksheet.Append(sheetProperties);
@@ -1027,7 +1088,8 @@
worksheet.Append(printOptions);
worksheet.Append(pageMargins);
worksheet.Append(pageSetup1);
- worksheet.Append(headerFooter);
+ if (headerFooter.Any(hf=>hf.InnerText.Length > 0))
+ worksheet.Append(headerFooter);
if (rowBreaks != null) worksheet.Append(rowBreaks);
if (columnBreaks != null) worksheet.Append(columnBreaks);
//worksheet.Append(drawing1);
@@ -1035,6 +1097,28 @@
worksheetPart.Worksheet = worksheet;
}
+ private void GenerateCalculationChainPartContent(WorkbookPart workbookPart, String rId)
+ {
+ Boolean foundOne = false;
+ CalculationChain calculationChain = new CalculationChain();
+ Int32 sheetId = 0;
+ foreach (var worksheet in Worksheets.Cast())
+ {
+ sheetId++;
+ foreach (var c in worksheet.Internals.CellsCollection.Values.Where(c => !String.IsNullOrWhiteSpace(c.FormulaA1)))
+ {
+ CalculationCell calculationCell = new CalculationCell() { CellReference = c.Address.ToString(), SheetId = sheetId };
+ calculationChain.Append(calculationCell);
+ if (!foundOne) foundOne = true;
+ }
+ }
+ if (foundOne)
+ {
+ CalculationChainPart calculationChainPart = workbookPart.AddNewPart(rId);
+ calculationChainPart.CalculationChain = calculationChain;
+ }
+ }
+
private void GenerateThemePartContent(ThemePart themePart)
{
A.Theme theme1 = new A.Theme() { Name = "Office Theme" };
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs
index f41d226..c40cc56 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs
@@ -28,6 +28,7 @@
Style = workbook.Style;
Internals = new XLWorksheetInternals(new Dictionary(), new XLColumnsCollection(), new XLRowsCollection(), new List());
PageSetup = new XLPageSetup(workbook.PageOptions, this);
+ Outline = new XLOutline(workbook.Outline);
ColumnWidth = workbook.ColumnWidth;
RowHeight = workbook.RowHeight;
this.Name = sheetName;
@@ -141,6 +142,7 @@
public String Name { get; set; }
public IXLPageSetup PageSetup { get; private set; }
+ public IXLOutline Outline { get; private set; }
public IXLRow FirstRowUsed()
{
@@ -322,6 +324,9 @@
}
else
{
+ // This is a new row so we're going to reference all
+ // cells in this row to preserve their formatting
+ this.Internals.ColumnsCollection.Keys.ForEach(c => Cell(row, c));
styleToUse = this.Style;
this.Internals.RowsCollection.Add(row, new XLRow(row, new XLRowParameters(this, styleToUse, false)));
}
@@ -337,6 +342,9 @@
}
else
{
+ // This is a new row so we're going to reference all
+ // cells in this row to preserve their formatting
+ this.Internals.RowsCollection.Keys.ForEach(r => Cell(r, column));
styleToUse = this.Style;
this.Internals.ColumnsCollection.Add(column, new XLColumn(column, new XLColumnParameters(this, this.Style, false)));
}
@@ -353,5 +361,50 @@
return Range(1, 1, XLWorksheet.MaxNumberOfRows, XLWorksheet.MaxNumberOfColumns);
}
+ public void CollapseRows()
+ {
+ Enumerable.Range(1, 8).ForEach(i => CollapseRows(i));
+ }
+ public void CollapseColumns()
+ {
+ Enumerable.Range(1, 8).ForEach(i => CollapseColumns(i));
+ }
+ public void ExpandRows()
+ {
+ Enumerable.Range(1, 8).ForEach(i => ExpandRows(i));
+ }
+ public void ExpandColumns()
+ {
+ Enumerable.Range(1, 8).ForEach(i => ExpandRows(i));
+ }
+
+ public void CollapseRows(Int32 outlineLevel)
+ {
+ if (outlineLevel < 1 || outlineLevel > 8)
+ throw new ArgumentOutOfRangeException("Outline level must be between 1 and 8.");
+
+ Internals.RowsCollection.Values.Where(r => r.OutlineLevel == outlineLevel).ForEach(r => r.Collapse());
+ }
+ public void CollapseColumns(Int32 outlineLevel)
+ {
+ if (outlineLevel < 1 || outlineLevel > 8)
+ throw new ArgumentOutOfRangeException("Outline level must be between 1 and 8.");
+
+ Internals.ColumnsCollection.Values.Where(c => c.OutlineLevel == outlineLevel).ForEach(c => c.Collapse());
+ }
+ public void ExpandRows(Int32 outlineLevel)
+ {
+ if (outlineLevel < 1 || outlineLevel > 8)
+ throw new ArgumentOutOfRangeException("Outline level must be between 1 and 8.");
+
+ Internals.RowsCollection.Values.Where(r => r.OutlineLevel == outlineLevel).ForEach(r => r.Expand());
+ }
+ public void ExpandColumns(Int32 outlineLevel)
+ {
+ if (outlineLevel < 1 || outlineLevel > 8)
+ throw new ArgumentOutOfRangeException("Outline level must be between 1 and 8.");
+
+ Internals.ColumnsCollection.Values.Where(c => c.OutlineLevel == outlineLevel).ForEach(c => c.Expand());
+ }
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj
index 293404c..5e23fdf 100644
--- a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj
+++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj
@@ -55,10 +55,13 @@
+
+
+
-
+
diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs
index 16e28bd..aa53662 100644
--- a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs
+++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs
@@ -51,6 +51,9 @@
new MergeMoves().Create();
new WorkbookProperties().Create(@"C:\Excel Files\Created\WorkbookProperties.xlsx");
new AdjustToContents().Create(@"C:\Excel Files\Created\AdjustToContents.xlsx");
+ 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");
}
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/AdjustToContents.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/AdjustToContents.cs
index 309b8a7..406eba4 100644
--- a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/AdjustToContents.cs
+++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/AdjustToContents.cs
@@ -57,15 +57,17 @@
ws.Cell(5, 2).Value = "really long text";
ws.Cell(5, 2).Style.Font.FontSize = 20;
+ // Adjust all rows/columns in one shot
+ ws.Rows().AdjustToContents();
+ ws.Columns().AdjustToContents();
+
+ // You can also adjust specific rows/columns
+
// Adjust the width of column 2 to its contents
- ws.Column(2).AdjustToContents();
+ //ws.Column(2).AdjustToContents();
- // Adjust the height of row 5 to its contents
- ws.Row(5).AdjustToContents();
-
- // You can also adjust all rows/columns in one shot
- //ws.Rows().AdjustToContents();
- //ws.Columns().AdjustToContents();
+ // Adjust the height of rows 2,3,4,5 to their contents
+ //ws.Rows(2, 5).AdjustToContents();
wb.SaveAs(filePath);
}
diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/Formulas.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/Formulas.cs
new file mode 100644
index 0000000..4afb12b
--- /dev/null
+++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/Formulas.cs
@@ -0,0 +1,102 @@
+using System;
+using System.Collections.Generic;
+using System.Linq;
+using System.Text;
+using ClosedXML.Excel;
+
+using System.Drawing;
+
+namespace ClosedXML_Examples.Misc
+{
+ public class Formulas
+ {
+ #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("Formulas");
+
+ ws.Cell(1, 1).Value = "Num1";
+ ws.Cell(1, 2).Value = "Num2";
+ ws.Cell(1, 3).Value = "Total";
+ ws.Cell(1, 4).Value = "cell.FormulaA1";
+ ws.Cell(1, 5).Value = "cell.FormulaR1C1";
+ ws.Cell(1, 6).Value = "cell.Value";
+ ws.Cell(1, 7).Value = "Are Equal?";
+
+ ws.Cell(2, 1).Value = 1;
+ ws.Cell(2, 2).Value = 2;
+ var cellWithFormulaA1 = ws.Cell(2, 3);
+ // Use A1 notation
+ cellWithFormulaA1.FormulaA1 = "=A2+$B$2"; // The equal sign (=) in a formula is optional
+ ws.Cell(2, 4).Value = cellWithFormulaA1.FormulaA1;
+ ws.Cell(2, 5).Value = cellWithFormulaA1.FormulaR1C1;
+ ws.Cell(2, 6).Value = cellWithFormulaA1.Value;
+
+ ws.Cell(3, 1).Value = 1;
+ ws.Cell(3, 2).Value = 2;
+ var cellWithFormulaR1C1 = ws.Cell(3, 3);
+ // Use R1C1 notation
+ cellWithFormulaR1C1.FormulaR1C1 = "RC[-2]+R3C2"; // The equal sign (=) in a formula is optional
+ ws.Cell(3, 4).Value = cellWithFormulaR1C1.FormulaA1;
+ ws.Cell(3, 5).Value = cellWithFormulaR1C1.FormulaR1C1;
+ ws.Cell(3, 6).Value = cellWithFormulaR1C1.Value;
+
+ // Setting the formula of a range
+ var rngData = ws.Range(2, 1, 3, 7);
+ rngData.LastColumn().FormulaR1C1 = "=IF(RC[-3]=RC[-1],\"Yes\", \"No\")";
+
+ ws.Range(1, 1, 1, 7).Style.Fill.BackgroundColor = Color.Cyan;
+ ws.Range(1, 1, 1, 7).Style.Font.Bold = true;
+ ws.Columns().AdjustToContents();
+
+ // You can also change the reference notation:
+ // wb.ReferenceStyle = XLReferenceStyle.R1C1;
+
+ // And the workbook calculation mode:
+ // wb.CalculateMode = XLCalculateMode.Auto;
+
+ wb.SaveAs(filePath);
+ }
+
+ // Private
+
+ // Override
+
+
+ #endregion
+ }
+}
diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/HideUnhide.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/HideUnhide.cs
new file mode 100644
index 0000000..274aab3
--- /dev/null
+++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/HideUnhide.cs
@@ -0,0 +1,68 @@
+using System;
+using System.Collections.Generic;
+using System.Linq;
+using System.Text;
+using ClosedXML.Excel;
+
+using System.Drawing;
+
+namespace ClosedXML_Examples.Misc
+{
+ public class HideUnhide
+ {
+ #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("Hide Unhide");
+
+ ws.Columns(1, 3).Hide();
+ ws.Rows(1, 3).Hide();
+
+ ws.Column(2).Unhide();
+ ws.Row(2).Unhide();
+
+ wb.SaveAs(filePath);
+ }
+
+ // Private
+
+ // Override
+
+
+ #endregion
+ }
+}
diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/Outline.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/Outline.cs
new file mode 100644
index 0000000..48a6230
--- /dev/null
+++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/Outline.cs
@@ -0,0 +1,90 @@
+using System;
+using System.Collections.Generic;
+using System.Linq;
+using System.Text;
+using ClosedXML.Excel;
+
+using System.Drawing;
+
+namespace ClosedXML_Examples.Misc
+{
+ public class Outline
+ {
+ #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("Outline");
+
+ ws.Outline.SummaryHLocation = XLOutlineSummaryHLocation.Right;
+ ws.Columns(2, 6).Group(); // Create an outline (level 1) for columns 2-6
+ ws.Columns(2, 4).Group(); // Create an outline (level 2) for columns 2-4
+ ws.Column(2).Ungroup(true); // Remove column 2 from all outlines
+
+ ws.Outline.SummaryVLocation = XLOutlineSummaryVLocation.Bottom;
+ ws.Rows(1, 5).Group(); // Create an outline (level 1) for rows 1-5
+ ws.Rows(1, 4).Group(); // Create an outline (level 2) for rows 1-4
+ ws.Rows(1, 4).Collapse(); // Collapse rows 1-4
+ ws.Rows(1, 2).Group(); // Create an outline (level 3) for rows 1-2
+ ws.Rows(1, 2).Ungroup(); // Ungroup rows 1-2 from their last outline
+
+ // You can also Collapse/Expand specific outline levels
+ //
+ // ws.CollapseRows(Int32 outlineLevel)
+ // ws.CollapseColumns(Int32 outlineLevel)
+ //
+ // ws.ExpandRows(Int32 outlineLevel)
+ // ws.ExpandColumns(Int32 outlineLevel)
+
+ // And you can also Collapse/Expand ALL outline levels in one shot
+ //
+ // ws.CollapseRows()
+ // ws.CollapseColumns()
+ //
+ // ws.ExpandRows()
+ // ws.ExpandColumns()
+
+ wb.SaveAs(filePath);
+ }
+
+ // Private
+
+ // Override
+
+
+ #endregion
+ }
+}
diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/PageSetup/Page.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/PageSetup/Page.cs
index 96ce55c..d81e131 100644
--- a/ClosedXML/ClosedXML/ClosedXML_Examples/PageSetup/Page.cs
+++ b/ClosedXML/ClosedXML/ClosedXML_Examples/PageSetup/Page.cs
@@ -25,9 +25,9 @@
var ws2 = workbook.Worksheets.Add("Page Setup - Page2");
ws2.PageSetup.PageOrientation = XLPageOrientation.Portrait;
- ws2.PageSetup.FitToPages(2, 2); // Alternatively you can use
- // ws2.PageSetup.PagesTall = #
- // and/or ws2.PageSetup.PagesWide = #
+ ws2.PageSetup.FitToPages(2, 2); // Alternatively you can use
+ // ws2.PageSetup.PagesTall = #
+ // and/or ws2.PageSetup.PagesWide = #
ws2.PageSetup.PaperSize = XLPaperSize.LetterPaper;
ws2.PageSetup.VerticalDpi = 600;
diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRanges.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRanges.cs
new file mode 100644
index 0000000..5bffe8a
--- /dev/null
+++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRanges.cs
@@ -0,0 +1,27 @@
+using System;
+using System.Collections.Generic;
+using System.Linq;
+using System.Text;
+using ClosedXML.Excel;
+using System.Drawing;
+
+
+namespace ClosedXML_Examples
+{
+ public class TransposeRanges
+ {
+ public void Create()
+ {
+ var workbook = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx");
+ var ws = workbook.Worksheets.GetWorksheet(0);
+
+ var rngTable = ws.Range("B2:F6");
+
+ rngTable.Transpose(XLTransposeOptions.MoveCells);
+
+ ws.Columns().AdjustToContents();
+
+ workbook.SaveAs(@"C:\Excel Files\Created\TransposeRanges.xlsx");
+ }
+ }
+}
\ No newline at end of file
diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleNumberFormat.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleNumberFormat.cs
index 6976e96..b0fcf26 100644
--- a/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleNumberFormat.cs
+++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleNumberFormat.cs
@@ -73,7 +73,7 @@
ws.Cell(++ro, co).Value = "12.345";
ws.Cell(ro, co).Style.NumberFormat.NumberFormatId = 3;
- ws.Column(2).AdjustToContents();
+ ws.Column(co).AdjustToContents();
workbook.SaveAs(filePath);
}