diff --git a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj index 5e41e2a..9fc6cdb 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj @@ -66,6 +66,16 @@ + + + + + + + + + + diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs index 99c5a3e..e339fb6 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs @@ -90,6 +90,11 @@ void Clear(); /// + /// Clears the styles of this cell (preserving number formats). + /// + void ClearStyles(); + + /// /// Deletes the current cell and shifts the surrounding cells according to the shiftDeleteCells parameter. /// /// How to shift the surrounding cells. diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCells.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCells.cs index e36b7ce..2c3a0a1 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCells.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCells.cs @@ -7,6 +7,49 @@ { public interface IXLCells : IEnumerable, IXLStylized { + /// + /// Sets the cells' value. + /// If the object is an IEnumerable ClosedXML will copy the collection's data into a table starting from each cell. + /// If the object is a range ClosedXML will copy the range starting from each cell. + /// Setting the value to an object (not IEnumerable/range) will call the object's ToString() method. + /// ClosedXML will try to translate it to the corresponding type, if it can't then the value will be left as a string. + /// + /// + /// The object containing the value(s) to set. + /// + Object Value { set; } + /// + /// Sets the type of the cells' data. + /// Changing the data type will cause ClosedXML to covert the current value to the new data type. + /// An exception will be thrown if the current value cannot be converted to the new data type. + /// + /// + /// The type of the cell's data. + /// + /// + XLCellValues DataType { set; } + + /// + /// Clears the contents of these cells (including styles). + /// + void Clear(); + + /// + /// Clears the styles of this range (preserving number formats). + /// + void ClearStyles(); + + /// + /// Sets the cells' formula with A1 references. + /// + /// The formula with A1 references. + String FormulaA1 { set; } + + /// + /// Sets the cells' formula with R1C1 references. + /// + /// The formula with R1C1 references. + String FormulaR1C1 { set; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index 8b1b934..2160735 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -78,13 +78,29 @@ } else if (dataType == XLCellValues.DateTime || IsDateFormat()) { - String format = GetFormat(); - return DateTime.FromOADate(Double.Parse(cellValue)).ToString(format); + Double dTest; + if (Double.TryParse(cellValue, out dTest)) + { + String format = GetFormat(); + return DateTime.FromOADate(dTest).ToString(format); + } + else + { + return cellValue; + } } else if (dataType == XLCellValues.Number) { - String format = GetFormat(); - return Double.Parse(cellValue).ToString(format); + Double dTest; + if (Double.TryParse(cellValue, out dTest)) + { + String format = GetFormat(); + return dTest.ToString(format); + } + else + { + return cellValue; + } } else { @@ -503,6 +519,12 @@ { worksheet.Range(Address, Address).Clear(); } + public void ClearStyles() + { + var newStyle = new XLStyle(this, worksheet.Style); + newStyle.NumberFormat = this.Style.NumberFormat; + this.Style = newStyle; + } public void Delete(XLShiftDeletedCells shiftDeleteCells) { worksheet.Range(Address, Address).Delete(shiftDeleteCells); @@ -550,7 +572,15 @@ private String formulaA1; public String FormulaA1 { - get { return formulaA1; } + get + { + if (StringExtensions.IsNullOrWhiteSpace(formulaA1)) + return String.Empty; + else if (formulaA1.Trim()[0] == '=') + return formulaA1.Substring(1); + else + return formulaA1; + } set { formulaA1 = value; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs index 0220ad7..08f719d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs @@ -94,5 +94,47 @@ public Boolean UpdatingStyle { get; set; } #endregion + + public Object Value + { + set + { + cells.ForEach(c => c.Value = value); + } + } + + public XLCellValues DataType + { + set + { + cells.ForEach(c => c.DataType = value); + } + } + + public void Clear() + { + cells.ForEach(c => c.Clear()); + } + + public void ClearStyles() + { + cells.ForEach(c => c.ClearStyles()); + } + + 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/Columns/IXLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs index c3887a4..3b9e784 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs @@ -45,11 +45,6 @@ void InsertColumnsBefore(Int32 numberOfColumns); /// - /// Clears the contents of this column (including styles). - /// - void Clear(); - - /// /// Gets the cell in the specified row. /// /// The cell's row. @@ -70,12 +65,6 @@ IXLCells Cells(Int32 firstRow, Int32 lastRow); /// - /// Converts this column to a range object. - /// - IXLRange AsRange(); - - - /// /// Adjusts the width of the column based on its contents. /// void AdjustToContents(); @@ -159,5 +148,6 @@ /// Expands this column (if it's collapsed). void Expand(); + Int32 CellCount(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs index f4367b9..e439724 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs @@ -82,6 +82,13 @@ var columnNumber = this.ColumnNumber(); this.AsRange().Delete(XLShiftDeletedCells.ShiftCellsLeft); Worksheet.Internals.ColumnsCollection.Remove(columnNumber); + List columnsToMove = new List(); + columnsToMove.AddRange(Worksheet.Internals.ColumnsCollection.Where(c => c.Key > columnNumber).Select(c => c.Key)); + foreach (var column in columnsToMove.OrderBy(c=>c)) + { + Worksheet.Internals.ColumnsCollection.Add(column - 1, Worksheet.Internals.ColumnsCollection[column]); + Worksheet.Internals.ColumnsCollection.Remove(column); + } } public new void Clear() @@ -189,30 +196,21 @@ #endregion - public Int32 ColumnNumber() - { - return this.RangeAddress.FirstAddress.ColumnNumber; - } - public String ColumnLetter() - { - return this.RangeAddress.FirstAddress.ColumnLetter; - } - - public new void InsertColumnsAfter( Int32 numberOfColumns) + public new void InsertColumnsAfter(Int32 numberOfColumns) { var columnNum = this.ColumnNumber(); this.Worksheet.Internals.ColumnsCollection.ShiftColumnsRight(columnNum + 1, numberOfColumns); XLRange range = (XLRange)this.Worksheet.Column(columnNum).AsRange(); - range.InsertColumnsAfter(numberOfColumns, true); + range.InsertColumnsAfter(true, numberOfColumns); } - public new void InsertColumnsBefore( Int32 numberOfColumns) + public new void InsertColumnsBefore(Int32 numberOfColumns) { var columnNum = this.ColumnNumber(); this.Worksheet.Internals.ColumnsCollection.ShiftColumnsRight(columnNum, numberOfColumns); // We can't use this.AsRange() because we've shifted the columns // and we want to use the old columnNum. XLRange range = (XLRange)this.Worksheet.Column(columnNum).AsRange(); - range.InsertColumnsBefore(numberOfColumns, true); + range.InsertColumnsBefore(true, numberOfColumns); } public override IXLRange AsRange() @@ -222,8 +220,11 @@ public override IXLRange Range(String rangeAddressStr) { String rangeAddressToUse; - if (rangeAddressStr.Contains(":")) + if (rangeAddressStr.Contains(':') || rangeAddressStr.Contains('-')) { + if (rangeAddressStr.Contains('-')) + rangeAddressStr = rangeAddressStr.Replace('-', ':'); + String[] arrRange = rangeAddressStr.Split(':'); var firstPart = arrRange[0]; var secondPart = arrRange[1]; @@ -366,6 +367,8 @@ } else { + Worksheet.IncrementColumnOutline(value); + Worksheet.DecrementColumnOutline(outlineLevel); outlineLevel = value; } } @@ -416,5 +419,10 @@ Collapsed = false; Unhide(); } + + public Int32 CellCount() + { + return this.RangeAddress.LastAddress.ColumnNumber - this.RangeAddress.FirstAddress.ColumnNumber + 1; + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs index f464b10..5761f7d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs @@ -31,7 +31,7 @@ #region IXLStylized Members - private IXLStyle style; + internal IXLStyle style; public IXLStyle Style { get diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs index e9efaea..17fbe26 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs @@ -151,6 +151,11 @@ IXLRange Range(string rangeAddress); /// Returns the specified range. + /// The first cell in the range. + /// The last cell in the range. + IXLRange Range(IXLCell firstCell, IXLCell lastCell); + + /// Returns the specified range. /// The first cell address in the worksheet. /// The last cell address in the worksheet. IXLRange Range(string firstCellAddress, string lastCellAddress); @@ -221,10 +226,7 @@ /// Deletes this worksheet. /// void Delete(); - /// - /// Clears the contents of this worksheet (including styles). - /// - void Clear(); + /// /// Gets an object to manage this worksheet's named ranges. @@ -235,5 +237,7 @@ /// Gets an object to manage how the worksheet is going to displayed by Excel. /// IXLSheetView SheetView { get; } + + IXLTables Tables { get; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs index 4fa7b2b..5b3a178 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs @@ -135,6 +135,11 @@ IXLRange Range(string rangeAddress); /// Returns the specified range. + /// The first cell in the range. + /// The last cell in the range. + IXLRange Range(IXLCell firstCell, IXLCell lastCell); + + /// Returns the specified range. /// The first cell address in the range. /// The last cell address in the range. IXLRange Range(string firstCellAddress, string lastCellAddress); @@ -169,54 +174,45 @@ /// /// Number of columns to insert. void InsertColumnsAfter(int numberOfColumns); + void InsertColumnsAfter(int numberOfColumns, Boolean expandRange); /// /// Inserts X number of columns to the left of this range. /// This range and all cells to the right of this range will be shifted X number of columns. /// /// Number of columns to insert. void InsertColumnsBefore(int numberOfColumns); + void InsertColumnsBefore(int numberOfColumns, Boolean expandRange); /// /// Inserts X number of rows on top of this range. /// This range and all cells below this range will be shifted X number of rows. /// /// Number of rows to insert. void InsertRowsAbove(int numberOfRows); + void InsertRowsAbove(int numberOfRows, Boolean expandRange); /// /// Inserts X number of rows below this range. /// All cells below this range will be shifted X number of rows. /// /// Number of rows to insert. void InsertRowsBelow(int numberOfRows); + void InsertRowsBelow(int numberOfRows, Boolean expandRange); /// /// Deletes this range and shifts the surrounding cells accordingly. /// /// How to shift the surrounding cells. void Delete(XLShiftDeletedCells shiftDeleteCells); - /// - /// Clears the contents of this range (including styles). - /// - void Clear(); /// /// Transposes the contents and styles of all cells in this range. /// /// How to handle the surrounding cells when transposing the range. void Transpose(XLTransposeOptions transposeOption); - /// - /// Sets the formula for all cells in the range in A1 notation. - /// - /// - /// The formula A1. - /// - String FormulaA1 { set; } - /// - /// Sets the formula for all cells in the range in R1C1 notation. - /// - /// - /// The formula R1C1. - /// - String FormulaR1C1 { set; } + + IXLTable AsTable(); + IXLTable AsTable(String name); + IXLTable CreateTable(); + IXLTable CreateTable(String name); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs index 6f16848..be60613 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -129,5 +129,20 @@ /// The scope for the named range. /// The comments for the named range. IXLRange AddToNamed(String rangeName, XLScope scope, String comment); + + /// + /// Clears the contents of this range (including styles). + /// + void Clear(); + + /// + /// Clears the styles of this range (preserving number formats). + /// + void ClearStyles(); + + /// + /// Converts this object to a range. + /// + IXLRange AsRange(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs index 876e9a4..61865df 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs @@ -28,34 +28,33 @@ IXLCells Cells(Int32 firstRow, Int32 lastRow); /// - /// Converts this column to a range object. - /// - IXLRange AsRange(); - - /// /// Inserts X number of columns to the right of this range. /// All cells to the right of this range will be shifted X number of columns. /// /// Number of columns to insert. void InsertColumnsAfter(int numberOfColumns); + void InsertColumnsAfter(int numberOfColumns, Boolean expandRange); /// /// Inserts X number of columns to the left of this range. /// This range and all cells to the right of this range will be shifted X number of columns. /// /// Number of columns to insert. void InsertColumnsBefore(int numberOfColumns); + void InsertColumnsBefore(int numberOfColumns, Boolean expandRange); /// /// Inserts X number of cells on top of this column. /// This column and all cells below it will be shifted X number of rows. /// /// Number of cells to insert. void InsertCellsAbove(int numberOfRows); + void InsertCellsAbove(int numberOfRows, Boolean expandRange); /// /// Inserts X number of cells below this range. /// All cells below this column will be shifted X number of rows. /// /// Number of cells to insert. void InsertCellsBelow(int numberOfRows); + void InsertCellsBelow(int numberOfRows, Boolean expandRange); /// /// Deletes this range and shifts the cells at the right. @@ -66,24 +65,18 @@ /// /// How to shift the surrounding cells. void Delete(XLShiftDeletedCells shiftDeleteCells); + /// - /// Clears the contents of the column (including styles). + /// Gets this column's number in the range /// - void Clear(); + Int32 ColumnNumber(); + /// - /// Sets the formula for all cells in the column in A1 notation. + /// Gets this column's letter in the range /// - /// - /// The formula A1. - /// - String FormulaA1 { set; } - /// - /// Sets the formula for all cells in the column in R1C1 notation. - /// - /// - /// The formula R1C1. - /// - String FormulaR1C1 { set; } + String ColumnLetter(); + + Int32 CellCount(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs index 2352f50..80babb1 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs @@ -17,20 +17,6 @@ /// /// The column range to add. void Add(IXLRangeColumn columRange); - /// - /// Sets the formula for all cells in the columns in A1 notation. - /// - /// - /// The formula A1. - /// - String FormulaA1 { set; } - /// - /// Sets the formula for all cells in the columns in R1C1 notation. - /// - /// - /// The formula R1C1. - /// - String FormulaR1C1 { set; } /// /// Returns the collection of cells in this column. @@ -47,5 +33,10 @@ /// /// if set to true will return all cells with a value or a style different than the default. IXLCells CellsUsed(Boolean includeStyles); + + /// + /// Deletes all columns and shifts the columns at the right of them accordingly. + /// + void Delete(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs index ee1ec53..a9033f4 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs @@ -40,34 +40,33 @@ IXLCells Cells(String firstColumn, String lastColumn); /// - /// Converts this row to a range object. - /// - IXLRange AsRange(); - - /// /// Inserts X number of cells to the right of this row. /// All cells to the right of this row will be shifted X number of columns. /// /// Number of cells to insert. void InsertCellsAfter(int numberOfColumns); + void InsertCellsAfter(int numberOfColumns, Boolean expandRange); /// /// Inserts X number of cells to the left of this row. /// This row and all cells to the right of it will be shifted X number of columns. /// /// Number of cells to insert. void InsertCellsBefore(int numberOfColumns); + void InsertCellsBefore(int numberOfColumns, Boolean expandRange); /// /// Inserts X number of rows on top of this row. /// This row and all cells below it will be shifted X number of rows. /// /// Number of rows to insert. void InsertRowsAbove(int numberOfRows); + void InsertRowsAbove(int numberOfRows, Boolean expandRange); /// /// Inserts X number of rows below this row. /// All cells below this row will be shifted X number of rows. /// /// Number of rows to insert. void InsertRowsBelow(int numberOfRows); + void InsertRowsBelow(int numberOfRows, Boolean expandRange); /// /// Deletes this range and shifts the cells below. @@ -78,24 +77,13 @@ /// /// How to shift the surrounding cells. void Delete(XLShiftDeletedCells shiftDeleteCells); + /// - /// Clears the contents of the row (including styles). + /// Gets this row's number in the range /// - void Clear(); - /// - /// Sets the formula for all cells in the row in A1 notation. - /// - /// - /// The formula A1. - /// - String FormulaA1 { set; } - /// - /// Sets the formula for all cells in the row in R1C1 notation. - /// - /// - /// The formula R1C1. - /// - String FormulaR1C1 { set; } + Int32 RowNumber(); + + Int32 CellCount(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs index 050fdf9..33ebcb1 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs @@ -16,20 +16,6 @@ /// Clears the contents of the rows (including styles). /// void Clear(); - /// - /// Sets the formula for all cells in the rows in A1 notation. - /// - /// - /// The formula A1. - /// - String FormulaA1 { set; } - /// - /// Sets the formula for all cells in the rows in R1C1 notation. - /// - /// - /// The formula R1C1. - /// - String FormulaR1C1 { set; } /// /// Returns the collection of cells. @@ -46,5 +32,10 @@ /// /// if set to true will return all cells with a value or a style different than the default. IXLCells CellsUsed(Boolean includeStyles); + + /// + /// Deletes all rows and shifts the rows below them accordingly. + /// + void Delete(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs index a2e8555..8ecf094 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs @@ -8,8 +8,10 @@ { internal class XLRange: XLRangeBase, IXLRange { + public XLRangeParameters RangeParameters { get; private set; } public XLRange(XLRangeParameters xlRangeParameters): base(xlRangeParameters.RangeAddress) { + this.RangeParameters = xlRangeParameters; Worksheet = xlRangeParameters.Worksheet; Worksheet.RangeShiftedRows += new RangeShiftedRowsDelegate(Worksheet_RangeShiftedRows); Worksheet.RangeShiftedColumns += new RangeShiftedColumnsDelegate(Worksheet_RangeShiftedColumns); @@ -188,18 +190,22 @@ var columnPairs = columns.Split(','); foreach (var pair in columnPairs) { + var tPair = pair.Trim(); String firstColumn; String lastColumn; - if (pair.Trim().Contains(':')) + if (tPair.Contains(':') || tPair.Contains('-')) { - var columnRange = pair.Trim().Split(':'); + if (tPair.Contains('-')) + tPair = tPair.Replace('-', ':'); + + var columnRange = tPair.Split(':'); firstColumn = columnRange[0]; lastColumn = columnRange[1]; } else { - firstColumn = pair.Trim(); - lastColumn = pair.Trim(); + firstColumn = tPair; + lastColumn = tPair; } Int32 tmp; @@ -242,18 +248,22 @@ var rowPairs = rows.Split(','); foreach (var pair in rowPairs) { + var tPair = pair.Trim(); String firstRow; String lastRow; - if (pair.Trim().Contains(':')) + if (tPair.Contains(':') || tPair.Contains('-')) { - var rowRange = pair.Trim().Split(':'); + if (tPair.Contains('-')) + tPair = tPair.Replace('-', ':'); + + var rowRange = tPair.Split(':'); firstRow = rowRange[0]; lastRow = rowRange[1]; } else { - firstRow = pair.Trim(); - lastRow = pair.Trim(); + firstRow = tPair; + lastRow = tPair; } foreach (var row in this.Rows(Int32.Parse(firstRow), Int32.Parse(lastRow))) { @@ -273,7 +283,7 @@ var lastCell = LastCell(); MoveOrClearForTranspose(transposeOption, rowCount, columnCount); - TransposeMerged(); + TransposeMerged(squareSide); TransposeRange(squareSide); this.RangeAddress.LastAddress = new XLAddress( firstCell.Address.RowNumber + columnCount - 1, @@ -318,33 +328,54 @@ XLRange rngToTranspose = (XLRange)Worksheet.Range( this.RangeAddress.FirstAddress.RowNumber, this.RangeAddress.FirstAddress.ColumnNumber, - this.RangeAddress.FirstAddress.RowNumber + squareSide, - this.RangeAddress.FirstAddress.ColumnNumber + squareSide); + this.RangeAddress.FirstAddress.RowNumber + squareSide - 1, + this.RangeAddress.FirstAddress.ColumnNumber + squareSide - 1); - foreach (var c in rngToTranspose.Cells()) + Int32 roInitial = rngToTranspose.RangeAddress.FirstAddress.RowNumber; + Int32 coInitial = rngToTranspose.RangeAddress.FirstAddress.ColumnNumber; + Int32 roCount = rngToTranspose.RowCount(); + Int32 coCount = rngToTranspose.ColumnCount(); + for (Int32 ro = 1; ro <= roCount; ro++) { - var newKey = new XLAddress(c.Address.ColumnNumber, c.Address.RowNumber); - var newCell = new XLCell(newKey, c.Style, Worksheet); - newCell.Value = c.Value; - newCell.DataType = c.DataType; - cellsToInsert.Add(newKey, newCell); - cellsToDelete.Add(c.Address); + for (Int32 co = 1; co <= coCount; co++) + { + var oldCell = rngToTranspose.Cell(ro, co); + var newKey = rngToTranspose.Cell(co, ro).Address; // new XLAddress(c.Address.ColumnNumber, c.Address.RowNumber); + var newCell = new XLCell(newKey, oldCell.Style, Worksheet); + newCell.Value = oldCell.Value; + newCell.DataType = oldCell.DataType; + cellsToInsert.Add(newKey, newCell); + cellsToDelete.Add(oldCell.Address); + } } + //foreach (var c in rngToTranspose.Cells()) + //{ + // var newKey = new XLAddress(c.Address.ColumnNumber, c.Address.RowNumber); + // var newCell = new XLCell(newKey, c.Style, Worksheet); + // newCell.Value = c.Value; + // newCell.DataType = c.DataType; + // cellsToInsert.Add(newKey, newCell); + // cellsToDelete.Add(c.Address); + //} cellsToDelete.ForEach(c => this.Worksheet.Internals.CellsCollection.Remove(c)); cellsToInsert.ForEach(c => this.Worksheet.Internals.CellsCollection.Add(c.Key, c.Value)); } - private void TransposeMerged() + private void TransposeMerged(Int32 squareSide) { + XLRange rngToTranspose = (XLRange)Worksheet.Range( + this.RangeAddress.FirstAddress.RowNumber, + this.RangeAddress.FirstAddress.ColumnNumber, + this.RangeAddress.FirstAddress.RowNumber + squareSide - 1, + this.RangeAddress.FirstAddress.ColumnNumber + squareSide - 1); + List mergeToDelete = new List(); List mergeToInsert = new List(); foreach (var merge in Worksheet.Internals.MergedRanges) { if (this.Contains(merge)) { - var lastAddress = merge.RangeAddress.LastAddress; - var newLastAddress = new XLAddress(lastAddress.ColumnNumber, lastAddress.RowNumber); - merge.RangeAddress.LastAddress = newLastAddress; + merge.RangeAddress.LastAddress = rngToTranspose.Cell(merge.ColumnCount(), merge.RowCount()).Address; } } mergeToDelete.ForEach(m => this.Worksheet.Internals.MergedRanges.Remove(m)); @@ -357,38 +388,56 @@ { if (rowCount > columnCount) { - this.InsertColumnsAfter(rowCount - columnCount); + this.InsertColumnsAfter(rowCount - columnCount, false); } else if (columnCount > rowCount) { - this.InsertRowsBelow(columnCount - rowCount); + this.InsertRowsBelow(columnCount - rowCount, false); } } else { if (rowCount > columnCount) { - var toMove = columnCount - rowCount; + var toMove = rowCount - columnCount; var rngToClear = Worksheet.Range( this.RangeAddress.FirstAddress.RowNumber, - columnCount + 1, + this.RangeAddress.LastAddress.ColumnNumber + 1, this.RangeAddress.LastAddress.RowNumber, - columnCount + toMove); + this.RangeAddress.LastAddress.ColumnNumber + toMove); rngToClear.Clear(); } else if (columnCount > rowCount) { - var toMove = rowCount - columnCount; + var toMove = columnCount - rowCount; var rngToClear = Worksheet.Range( - rowCount + 1, + this.RangeAddress.LastAddress.RowNumber + 1, this.RangeAddress.FirstAddress.ColumnNumber, - rowCount + toMove, + this.RangeAddress.LastAddress.RowNumber + toMove, this.RangeAddress.LastAddress.ColumnNumber); rngToClear.Clear(); } } } + public IXLTable AsTable() + { + return new XLTable(this, false); + } + public IXLTable AsTable(String name) + { + return new XLTable(this, name, false); + } + + public IXLTable CreateTable() + { + return new XLTable(this, true); + } + public IXLTable CreateTable(String name) + { + return new XLTable(this, name, true); + } + #endregion } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs index ca413d4..386d15f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs @@ -28,7 +28,7 @@ get { if (IsInvalid) - throw new Exception("Range is invalid."); + throw new Exception("Range is an invalid state."); return lastAddress; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index ab14177..bc1c648 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -27,7 +27,7 @@ public IXLCell FirstCellUsed() { - return FirstCellUsed(true); + return FirstCellUsed(false); } public IXLCell FirstCellUsed(Boolean includeStyles) { @@ -47,7 +47,7 @@ public IXLCell LastCellUsed() { - return LastCellUsed(true); + return LastCellUsed(false); } public IXLCell LastCellUsed(Boolean includeStyles) { @@ -124,16 +124,33 @@ { return this.RangeAddress.LastAddress.RowNumber - this.RangeAddress.FirstAddress.RowNumber + 1; } + public Int32 RowNumber() + { + return this.RangeAddress.FirstAddress.RowNumber; + } public Int32 ColumnCount() { return this.RangeAddress.LastAddress.ColumnNumber - this.RangeAddress.FirstAddress.ColumnNumber + 1; } + public Int32 ColumnNumber() + { + return this.RangeAddress.FirstAddress.ColumnNumber; + } + public String ColumnLetter() + { + return this.RangeAddress.FirstAddress.ColumnLetter; + } public virtual IXLRange Range(String rangeAddressStr) { var rangeAddress = new XLRangeAddress(rangeAddressStr); return Range(rangeAddress); } + + public IXLRange Range(IXLCell firstCell, IXLCell lastCell) + { + return Range(firstCell.Address, lastCell.Address); + } public IXLRange Range(String firstCellAddress, String lastCellAddress) { var rangeAddress = new XLRangeAddress(firstCellAddress, lastCellAddress); @@ -276,12 +293,24 @@ return AsRange(); } - public void InsertColumnsAfter(Int32 numberOfColumns) { - this.InsertColumnsAfter(numberOfColumns, false); + InsertColumnsAfter(numberOfColumns, true); } - public void InsertColumnsAfter(Int32 numberOfColumns, Boolean onlyUsedCells) + public void InsertColumnsAfter(Int32 numberOfColumns, Boolean expandRange) + { + this.InsertColumnsAfter(false, numberOfColumns); + // Adjust the range + if (expandRange) + { + this.RangeAddress = new XLRangeAddress( + this.RangeAddress.FirstAddress.RowNumber, + this.RangeAddress.FirstAddress.ColumnNumber, + this.RangeAddress.LastAddress.RowNumber, + this.RangeAddress.LastAddress.ColumnNumber + numberOfColumns); + } + } + public void InsertColumnsAfter(Boolean onlyUsedCells, Int32 numberOfColumns) { var columnCount = this.ColumnCount(); var firstColumn = this.RangeAddress.FirstAddress.ColumnNumber + columnCount; @@ -294,13 +323,26 @@ if (lastRow > XLWorksheet.MaxNumberOfRows) lastRow = XLWorksheet.MaxNumberOfRows; var newRange = (XLRange)this.Worksheet.Range(firstRow, firstColumn, lastRow, lastColumn); - newRange.InsertColumnsBefore(numberOfColumns, onlyUsedCells); + newRange.InsertColumnsBefore(onlyUsedCells, numberOfColumns); } public void InsertColumnsBefore(Int32 numberOfColumns) { - this.InsertColumnsBefore(numberOfColumns, false); + InsertColumnsBefore(numberOfColumns, false); } - public void InsertColumnsBefore(Int32 numberOfColumns, Boolean onlyUsedCells) + public void InsertColumnsBefore(Int32 numberOfColumns, Boolean expandRange) + { + this.InsertColumnsBefore(false, numberOfColumns); + // Adjust the range + if (expandRange) + { + this.RangeAddress = new XLRangeAddress( + this.RangeAddress.FirstAddress.RowNumber, + this.RangeAddress.FirstAddress.ColumnNumber - numberOfColumns, + this.RangeAddress.LastAddress.RowNumber, + this.RangeAddress.LastAddress.ColumnNumber); + } + } + public void InsertColumnsBefore(Boolean onlyUsedCells, Int32 numberOfColumns) { var cellsToInsert = new Dictionary(); var cellsToDelete = new List(); @@ -312,6 +354,7 @@ if (!onlyUsedCells) { var lastColumn = this.Worksheet.LastColumnUsed().ColumnNumber(); + for (var co = lastColumn; co >= firstColumn; co--) { for (var ro = lastRow; ro >= firstRow; ro--) @@ -369,13 +412,27 @@ } Worksheet.NotifyRangeShiftedColumns((XLRange)this.AsRange(), numberOfColumns); + } public void InsertRowsBelow(Int32 numberOfRows) { - this.InsertRowsBelow(numberOfRows, false); + InsertRowsBelow(numberOfRows, true); } - public void InsertRowsBelow(Int32 numberOfRows, Boolean onlyUsedCells) + public void InsertRowsBelow(Int32 numberOfRows, Boolean expandRange) + { + this.InsertRowsBelow(false,numberOfRows); + // Adjust the range + if (expandRange) + { + this.RangeAddress = new XLRangeAddress( + this.RangeAddress.FirstAddress.RowNumber, + this.RangeAddress.FirstAddress.ColumnNumber, + this.RangeAddress.LastAddress.RowNumber + numberOfRows, + this.RangeAddress.LastAddress.ColumnNumber); + } + } + public void InsertRowsBelow(Boolean onlyUsedCells, Int32 numberOfRows) { var rowCount = this.RowCount(); var firstRow = this.RangeAddress.FirstAddress.RowNumber + rowCount; @@ -388,13 +445,26 @@ if (lastColumn > XLWorksheet.MaxNumberOfColumns) lastColumn = XLWorksheet.MaxNumberOfColumns; var newRange = (XLRange)this.Worksheet.Range(firstRow, firstColumn, lastRow, lastColumn); - newRange.InsertRowsAbove(numberOfRows, onlyUsedCells); + newRange.InsertRowsAbove(onlyUsedCells, numberOfRows); } public void InsertRowsAbove(Int32 numberOfRows) { - this.InsertRowsAbove(numberOfRows, false); + InsertRowsAbove(numberOfRows, false); } - public void InsertRowsAbove(Int32 numberOfRows, Boolean onlyUsedCells) + public void InsertRowsAbove(Int32 numberOfRows, Boolean expandRange) + { + this.InsertRowsAbove(false, numberOfRows); + // Adjust the range + if (expandRange) + { + this.RangeAddress = new XLRangeAddress( + this.RangeAddress.FirstAddress.RowNumber - numberOfRows, + this.RangeAddress.FirstAddress.ColumnNumber, + this.RangeAddress.LastAddress.RowNumber, + this.RangeAddress.LastAddress.ColumnNumber); + } + } + public void InsertRowsAbove(Boolean onlyUsedCells, Int32 numberOfRows ) { var cellsToInsert = new Dictionary(); var cellsToDelete = new List(); @@ -406,6 +476,7 @@ if (!onlyUsedCells) { var lastRow = this.Worksheet.LastRowUsed().RowNumber(); + for (var ro = lastRow; ro >= firstRow; ro--) { for (var co = lastColumn; co >= firstColumn; co--) @@ -477,6 +548,16 @@ ClearMerged(); } + public void ClearStyles() + { + foreach (var cell in CellsUsed(true)) + { + var newStyle = new XLStyle(cell, Worksheet.Style); + newStyle.NumberFormat = cell.Style.NumberFormat; + cell.Style = newStyle; + } + } + private void ClearMerged() { List mergeToDelete = new List(); @@ -580,6 +661,15 @@ } cellsToDelete.ForEach(c => this.Worksheet.Internals.CellsCollection.Remove(c)); cellsToInsert.ForEach(c => this.Worksheet.Internals.CellsCollection.Add(c.Key, c.Value)); + + List mergesToRemove = new List(); + foreach (var merge in Worksheet.Internals.MergedRanges) + { + if (this.Contains(merge)) + mergesToRemove.Add(merge); + } + mergesToRemove.ForEach(r => Worksheet.Internals.MergedRanges.Remove(r)); + var shiftedRange = (XLRange)this.AsRange(); if (shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp) { @@ -703,9 +793,13 @@ // all rows && thisRangeAddress.FirstAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber && thisRangeAddress.LastAddress.RowNumber <= shiftedRange.RangeAddress.LastAddress.RowNumber - ) || (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber + ) || ( + shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= thisRangeAddress.FirstAddress.ColumnNumber + && shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber && shiftedRange.RangeAddress.LastAddress.RowNumber >= thisRangeAddress.LastAddress.RowNumber - && thisRangeAddress.FirstAddress.ColumnNumber + columnsShifted <= 0)) + && shiftedRange.ColumnCount() > + (thisRangeAddress.LastAddress.ColumnNumber - thisRangeAddress.FirstAddress.ColumnNumber + 1) + + (thisRangeAddress.FirstAddress.ColumnNumber - shiftedRange.RangeAddress.FirstAddress.ColumnNumber))) { thisRangeAddress.IsInvalid = true; } @@ -714,7 +808,10 @@ if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber && shiftedRange.RangeAddress.LastAddress.RowNumber >= thisRangeAddress.LastAddress.RowNumber) { - if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= thisRangeAddress.FirstAddress.ColumnNumber) + if ( + (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= thisRangeAddress.FirstAddress.ColumnNumber && columnsShifted > 0) + || (shiftedRange.RangeAddress.FirstAddress.ColumnNumber < thisRangeAddress.FirstAddress.ColumnNumber && columnsShifted < 0) + ) thisRangeAddress.FirstAddress = new XLAddress(thisRangeAddress.FirstAddress.RowNumber, thisRangeAddress.FirstAddress.ColumnNumber + columnsShifted); if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= thisRangeAddress.LastAddress.ColumnNumber) @@ -735,9 +832,13 @@ // all rows && thisRangeAddress.FirstAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber && thisRangeAddress.LastAddress.RowNumber <= shiftedRange.RangeAddress.LastAddress.RowNumber - rowsShifted - ) || (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= thisRangeAddress.FirstAddress.ColumnNumber + ) || ( + shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber + && shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= thisRangeAddress.FirstAddress.ColumnNumber && shiftedRange.RangeAddress.LastAddress.ColumnNumber >= thisRangeAddress.LastAddress.ColumnNumber - && thisRangeAddress.FirstAddress.RowNumber + rowsShifted <= 0)) + && shiftedRange.RowCount() > + (thisRangeAddress.LastAddress.RowNumber - thisRangeAddress.FirstAddress.RowNumber + 1) + + (thisRangeAddress.FirstAddress.RowNumber - shiftedRange.RangeAddress.FirstAddress.RowNumber))) { thisRangeAddress.IsInvalid = true; } @@ -746,7 +847,10 @@ if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= thisRangeAddress.FirstAddress.ColumnNumber && shiftedRange.RangeAddress.LastAddress.ColumnNumber >= thisRangeAddress.LastAddress.ColumnNumber) { - if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber) + if ( + (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber && rowsShifted > 0) + || (shiftedRange.RangeAddress.FirstAddress.RowNumber < thisRangeAddress.FirstAddress.RowNumber && rowsShifted < 0) + ) thisRangeAddress.FirstAddress = new XLAddress(thisRangeAddress.FirstAddress.RowNumber + rowsShifted, thisRangeAddress.FirstAddress.ColumnNumber); if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.LastAddress.RowNumber) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs index aa51730..451a9be 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs @@ -54,8 +54,11 @@ public override IXLRange Range(String rangeAddressStr) { String rangeAddressToUse; - if (rangeAddressStr.Contains(":")) + if (rangeAddressStr.Contains(':') || rangeAddressStr.Contains('-')) { + if (rangeAddressStr.Contains('-')) + rangeAddressStr = rangeAddressStr.Replace('-', ':'); + String[] arrRange = rangeAddressStr.Split(':'); var firstPart = arrRange[0]; var secondPart = arrRange[1]; @@ -74,15 +77,27 @@ { Delete(XLShiftDeletedCells.ShiftCellsLeft); } - public void InsertCellsAbove(int numberOfRows) { - InsertRowsAbove(numberOfRows); + InsertCellsAbove(numberOfRows, false); + } + public void InsertCellsAbove(int numberOfRows, Boolean expandRange) + { + InsertRowsAbove(numberOfRows, expandRange); } public void InsertCellsBelow(int numberOfRows) { - InsertRowsBelow(numberOfRows); + InsertCellsBelow(numberOfRows, true); + } + public void InsertCellsBelow(int numberOfRows, Boolean expandRange) + { + InsertRowsBelow(numberOfRows, expandRange); + } + + public Int32 CellCount() + { + return this.RangeAddress.LastAddress.ColumnNumber - this.RangeAddress.FirstAddress.ColumnNumber + 1; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs index 5beb797..1886aca 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs @@ -21,6 +21,12 @@ ranges.ForEach(r => r.Clear()); } + public void Delete() + { + ranges.OrderByDescending(c => c.ColumnNumber()).ForEach(r => r.Delete()); + ranges.Clear(); + } + public void Add(IXLRangeColumn range) { ranges.Add((XLRangeColumn)range); @@ -50,7 +56,7 @@ set { style = new XLStyle(this, value); - + ranges.ForEach(r => r.Style = value); } } @@ -81,20 +87,6 @@ #endregion - public String FormulaA1 - { - set - { - ranges.ForEach(r => r.FormulaA1 = value); - } - } - public String FormulaR1C1 - { - set - { - ranges.ForEach(r => r.FormulaR1C1 = value); - } - } public IXLCells Cells() { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs index d7b1cad..965e90f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs @@ -8,8 +8,10 @@ { internal class XLRangeRow: XLRangeBase, IXLRangeRow { + public XLRangeParameters RangeParameters { get; private set; } public XLRangeRow(XLRangeParameters xlRangeParameters): base(xlRangeParameters.RangeAddress) { + this.RangeParameters = xlRangeParameters; Worksheet = xlRangeParameters.Worksheet; Worksheet.RangeShiftedRows += new RangeShiftedRowsDelegate(Worksheet_RangeShiftedRows); Worksheet.RangeShiftedColumns += new RangeShiftedColumnsDelegate(Worksheet_RangeShiftedColumns); @@ -44,14 +46,22 @@ Delete(XLShiftDeletedCells.ShiftCellsUp); } - public void InsertCellsAfter(int numberOfColumns) + public void InsertCellsAfter(int numberOfColumns) { - InsertColumnsAfter(numberOfColumns); + InsertCellsAfter(numberOfColumns, true); + } + public void InsertCellsAfter(int numberOfColumns, Boolean expandRange) + { + InsertColumnsAfter(numberOfColumns, expandRange); } public void InsertCellsBefore(int numberOfColumns) { - InsertColumnsBefore(numberOfColumns); + InsertCellsBefore(numberOfColumns, false); + } + public void InsertCellsBefore(int numberOfColumns, Boolean expandRange) + { + InsertColumnsBefore(numberOfColumns, expandRange); } public IXLCells Cells(String cellsInRow) @@ -68,8 +78,11 @@ public override IXLRange Range(String rangeAddressStr) { String rangeAddressToUse; - if (rangeAddressStr.Contains(":")) + if (rangeAddressStr.Contains(':') || rangeAddressStr.Contains('-')) { + if (rangeAddressStr.Contains('-')) + rangeAddressStr = rangeAddressStr.Replace('-', ':'); + String[] arrRange = rangeAddressStr.Split(':'); var firstPart = arrRange[0]; var secondPart = arrRange[1]; @@ -94,6 +107,11 @@ return Cells(XLAddress.GetColumnNumberFromLetter(firstColumn) + ":" + XLAddress.GetColumnNumberFromLetter(lastColumn)); } + + public Int32 CellCount() + { + return this.RangeAddress.LastAddress.ColumnNumber - this.RangeAddress.FirstAddress.ColumnNumber + 1; + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs index 3322ddc..2149332 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs @@ -21,6 +21,12 @@ ranges.ForEach(r => r.Clear()); } + public void Delete() + { + ranges.OrderByDescending(r => r.RowNumber()).ForEach(r => r.Delete()); + ranges.Clear(); + } + public void Add(IXLRangeRow range) { ranges.Add((XLRangeRow)range); @@ -50,7 +56,7 @@ set { style = new XLStyle(this, value); - + ranges.ForEach(r => r.Style = value); } } @@ -81,21 +87,6 @@ #endregion - public String FormulaA1 - { - set - { - ranges.ForEach(r => r.FormulaA1 = value); - } - } - public String FormulaR1C1 - { - set - { - ranges.ForEach(r => r.FormulaR1C1 = value); - } - } - public IXLCells Cells() { var cellHash = new HashSet(); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs index b2ee030..d22723c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs @@ -40,15 +40,6 @@ void InsertRowsAbove(Int32 numberOfRows); /// - /// Clears the contents of this row (including styles). - /// - void Clear(); - - /// - /// Adjusts the height of the row based on its contents. - /// - void AdjustToContents(); - /// /// Adjusts the height of the row based on its contents, starting from the startColumn. /// /// The column to start calculating the row height. @@ -154,13 +145,9 @@ /// The last column in the group of cells to return. IXLCells Cells(String firstColumn, String lastColumn); - /// - /// Converts this row to a range object. - /// - IXLRange AsRange(); - /// Expands this row (if it's collapsed). void Expand(); + Int32 CellCount(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs index e5fbeaa..1befc4d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs @@ -87,19 +87,22 @@ var rowNumber = this.RowNumber(); this.AsRange().Delete(XLShiftDeletedCells.ShiftCellsUp); Worksheet.Internals.RowsCollection.Remove(rowNumber); + List rowsToMove = new List(); + rowsToMove.AddRange(Worksheet.Internals.RowsCollection.Where(c => c.Key > rowNumber).Select(c => c.Key)); + foreach (var row in rowsToMove.OrderBy(r=>r)) + { + Worksheet.Internals.RowsCollection.Add(row - 1, Worksheet.Internals.RowsCollection[row]); + Worksheet.Internals.RowsCollection.Remove(row); + } } - public Int32 RowNumber() - { - return this.RangeAddress.FirstAddress.RowNumber; - } public new void InsertRowsBelow(Int32 numberOfRows) { var rowNum = this.RowNumber(); this.Worksheet.Internals.RowsCollection.ShiftRowsDown(rowNum + 1, numberOfRows); XLRange range = (XLRange)this.Worksheet.Row(rowNum).AsRange(); - range.InsertRowsBelow(numberOfRows, true); + range.InsertRowsBelow(true, numberOfRows); } public new void InsertRowsAbove(Int32 numberOfRows) @@ -109,7 +112,7 @@ // We can't use this.AsRange() because we've shifted the rows // and we want to use the old rowNum. XLRange range = (XLRange)this.Worksheet.Row(rowNum).AsRange(); - range.InsertRowsAbove(numberOfRows, true); + range.InsertRowsAbove(true, numberOfRows); } public new void Clear() @@ -142,8 +145,11 @@ public override IXLRange Range(String rangeAddressStr) { String rangeAddressToUse; - if (rangeAddressStr.Contains(":")) + if (rangeAddressStr.Contains(':') || rangeAddressStr.Contains('-')) { + if (rangeAddressStr.Contains('-')) + rangeAddressStr = rangeAddressStr.Replace('-', ':'); + String[] arrRange = rangeAddressStr.Split(':'); var firstPart = arrRange[0]; var secondPart = arrRange[1]; @@ -244,7 +250,25 @@ #region IXLStylized Members - private IXLStyle style; + internal void SetStyleNoColumns(IXLStyle value) + { + if (IsReference) + { + Worksheet.Internals.RowsCollection[this.RowNumber()].SetStyleNoColumns(value); + } + else + { + style = new XLStyle(this, value); + + var row = this.RowNumber(); + foreach (var c in Worksheet.Internals.CellsCollection.Values.Where(c => c.Address.RowNumber == row)) + { + c.Style = value; + } + } + } + + internal IXLStyle style; public override IXLStyle Style { get @@ -375,6 +399,8 @@ } else { + Worksheet.IncrementColumnOutline(value); + Worksheet.DecrementColumnOutline(outlineLevel); outlineLevel = value; } } @@ -426,5 +452,10 @@ Collapsed = false; Unhide(); } + + public Int32 CellCount() + { + return this.RangeAddress.LastAddress.ColumnNumber - this.RangeAddress.FirstAddress.ColumnNumber + 1; + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs index a284ba2..482bb91 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs @@ -32,7 +32,7 @@ #region IXLStylized Members - private IXLStyle style; + internal IXLStyle style; public IXLStyle Style { get @@ -126,12 +126,17 @@ { if (entireWorksheet) { - worksheet.Internals.ColumnsCollection.Clear(); + worksheet.Internals.RowsCollection.Clear(); worksheet.Internals.CellsCollection.Clear(); } else { - rows.ForEach(r => r.Delete()); + var toDelete = new List(); + foreach (var r in rows) + toDelete.Add(r.RowNumber()); + + foreach (var r in toDelete.OrderByDescending(r => r)) + worksheet.Row(r).Delete(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs new file mode 100644 index 0000000..a00f8d6 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs @@ -0,0 +1,125 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public enum XLTableTheme + { + TableStyleMedium28, + TableStyleMedium27, + TableStyleMedium26, + TableStyleMedium25, + TableStyleMedium24, + TableStyleMedium23, + TableStyleMedium22, + TableStyleMedium21, + TableStyleMedium20, + TableStyleMedium19, + TableStyleMedium18, + TableStyleMedium17, + TableStyleMedium16, + TableStyleMedium15, + TableStyleMedium14, + TableStyleMedium13, + TableStyleMedium12, + TableStyleMedium11, + TableStyleMedium10, + TableStyleMedium9, + TableStyleMedium8, + TableStyleMedium7, + TableStyleMedium6, + TableStyleMedium5, + TableStyleMedium4, + TableStyleMedium3, + TableStyleMedium2, + TableStyleMedium1, + TableStyleLight21, + TableStyleLight20, + TableStyleLight19, + TableStyleLight18, + TableStyleLight17, + TableStyleLight16, + TableStyleLight15, + TableStyleLight14, + TableStyleLight13, + TableStyleLight12, + TableStyleLight11, + TableStyleLight10, + TableStyleLight9, + TableStyleLight8, + TableStyleLight7, + TableStyleLight6, + TableStyleLight5, + TableStyleLight4, + TableStyleLight3, + TableStyleLight2, + TableStyleLight1, + TableStyleDark11, + TableStyleDark10, + TableStyleDark9, + TableStyleDark8, + TableStyleDark7, + TableStyleDark6, + TableStyleDark5, + TableStyleDark4, + TableStyleDark3, + TableStyleDark2, + TableStyleDark1 + } + public interface IXLTable: IXLRange + { + String Name { get; set; } + Boolean EmphasizeFirstColumn { get; set; } + Boolean EmphasizeLastColumn { get; set; } + Boolean ShowRowStripes { get; set; } + Boolean ShowColumnStripes { get; set; } + Boolean ShowTotalsRow { get; set; } + Boolean ShowAutoFilter { get; set; } + XLTableTheme Theme { get; set; } + IXLRangeRow HeadersRow(); + IXLRangeRow TotalsRow(); + IXLTableField Field(String fieldName); + IXLTableField Field(Int32 fieldIndex); + + /// + /// Gets the first data row of the table. + /// + new IXLTableRow FirstRow(); + /// + /// Gets the first data row of the table that contains a cell with a value. + /// + new IXLTableRow FirstRowUsed(); + /// + /// Gets the last data row of the table. + /// + new IXLTableRow LastRow(); + /// + /// Gets the last data row of the table that contains a cell with a value. + /// + new IXLTableRow LastRowUsed(); + /// + /// Gets the specified row of the table data. + /// + /// The table row. + new IXLTableRow Row(int row); + /// + /// Gets a collection of all data rows in this table. + /// + new IXLTableRows Rows(); + /// + /// Gets a collection of the specified data rows in this table. + /// + /// The first row to return. + /// The last row to return. + /// + new IXLTableRows Rows(int firstRow, int lastRow); + /// + /// Gets a collection of the specified data rows in this table, separated by commas. + /// e.g. Rows("4:5"), Rows("7:8,10:11"), Rows("13") + /// + /// The rows to return. + new IXLTableRows Rows(string rows); + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableField.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableField.cs new file mode 100644 index 0000000..b7462e7 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableField.cs @@ -0,0 +1,31 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public enum XLTotalsRowFunction + { + None, + Sum, + Minimum, + Maximum, + Average, + Count, + CountNumbers, + StandardDeviation, + Variance, + Custom + } + + public interface IXLTableField + { + Int32 Index { get; set; } + String Name { get; set; } + String TotalsRowLabel { get; set; } + String TotalsRowFormulaA1 { get; set; } + String TotalsRowFormulaR1C1 { get; set; } + XLTotalsRowFunction TotalsRowFunction { get; set; } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRow.cs new file mode 100644 index 0000000..18d738e --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRow.cs @@ -0,0 +1,13 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public interface IXLTableRow: IXLRangeRow + { + IXLCell Field(Int32 index); + IXLCell Field(String name); + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRows.cs new file mode 100644 index 0000000..678574b --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRows.cs @@ -0,0 +1,37 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public interface IXLTableRows: IEnumerable, IXLStylized + { + /// + /// Adds a table row to this group. + /// + /// The row table to add. + void Add(IXLTableRow tableRow); + + /// + /// Clears the contents of the rows (including styles). + /// + void Clear(); + + /// + /// Returns the collection of cells. + /// + IXLCells Cells(); + + /// + /// Returns the collection of cells that have a value. + /// + IXLCells CellsUsed(); + + /// + /// Returns the collection of cells that have a value. + /// + /// if set to true will return all cells with a value or a style different than the default. + IXLCells CellsUsed(Boolean includeStyles); + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTables.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTables.cs new file mode 100644 index 0000000..8ab010a --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTables.cs @@ -0,0 +1,14 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public interface IXLTables: IEnumerable + { + void Add(IXLTable table); + IXLTable Table(Int32 index); + IXLTable Table(String name); + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs new file mode 100644 index 0000000..7025909 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs @@ -0,0 +1,231 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal class XLTable : XLRange, IXLTable + { + public String RelId { get; set; } + public String Name { get; set; } + public Boolean EmphasizeFirstColumn { get; set; } + public Boolean EmphasizeLastColumn { get; set; } + public Boolean ShowRowStripes { get; set; } + public Boolean ShowColumnStripes { get; set; } + public Boolean ShowAutoFilter { get; set; } + public XLTableTheme Theme { get; set; } + + internal Boolean showTotalsRow; + public Boolean ShowTotalsRow + { + get { return showTotalsRow; } + set + { + if (value && !showTotalsRow) + this.InsertRowsBelow(1); + else if (!value && showTotalsRow) + this.TotalsRow().Delete(); + + showTotalsRow = value; + } + } + + public IXLRange DataRange + { + get + { + if (showTotalsRow) + return base.Range(2,1, RowCount() - 1, ColumnCount()); + else + return base.Range(2, 1, RowCount(), ColumnCount()); + } + } + public XLTable(XLRange range, Boolean addToTables) + : base(range.RangeParameters) + { + InitializeValues(); + + Int32 id = 1; + while (true) + { + String tableName = String.Format("Table{0}", id); + if (!Worksheet.Tables.Where(t=>t.Name == tableName).Any()) + { + Name = tableName; + if (addToTables) + Worksheet.Tables.Add(this); + break; + } + id++; + } + } + + private void InitializeValues() + { + ShowRowStripes = true; + ShowAutoFilter = true; + Theme = XLTableTheme.TableStyleLight9; + } + + public XLTable(XLRange range, String name, Boolean addToTables) + : base(range.RangeParameters) + { + InitializeValues(); + + this.Name = name; + if (addToTables) + Worksheet.Tables.Add(this); + } + + + public IXLRangeRow HeadersRow() + { + return new XLTableRow(this, (XLRangeRow)base.FirstRow()); + } + + public IXLRangeRow TotalsRow() + { + if (ShowTotalsRow) + return new XLTableRow(this, (XLRangeRow)base.LastRow()); + else + throw new InvalidOperationException("Cannot access TotalsRow if ShowTotals property is false"); + } + + public new IXLTableRow FirstRow() + { + return Row(1); + } + + public new IXLTableRow FirstRowUsed() + { + return new XLTableRow(this, (XLRangeRow)(DataRange.FirstRowUsed())); + } + + public new IXLTableRow LastRow() + { + if (ShowTotalsRow) + return new XLTableRow(this, (XLRangeRow)base.Row(RowCount() - 1)); + else + return new XLTableRow(this, (XLRangeRow)base.Row(RowCount())); + } + + public new IXLTableRow LastRowUsed() + { + return new XLTableRow(this, (XLRangeRow)(DataRange.LastRowUsed())); + } + + public new IXLTableRow Row(int row) + { + return new XLTableRow(this, (XLRangeRow)base.Row(row + 1)); + } + + public new IXLTableRows Rows() + { + var retVal = new XLTableRows(Worksheet); + foreach (var r in Enumerable.Range(1, DataRange.RowCount())) + { + retVal.Add(this.Row(r)); + } + return retVal; + } + + public new IXLTableRows Rows(int firstRow, int lastRow) + { + var retVal = new XLTableRows(Worksheet); + + for (var ro = firstRow; ro <= lastRow; ro++) + { + retVal.Add(this.Row(ro)); + } + return retVal; + } + + public new IXLTableRows Rows(string rows) + { + var retVal = new XLTableRows(Worksheet); + var rowPairs = rows.Split(','); + foreach (var pair in rowPairs) + { + var tPair = pair.Trim(); + String firstRow; + String lastRow; + if (tPair.Contains(':') || tPair.Contains('-')) + { + if (tPair.Contains('-')) + tPair = tPair.Replace('-', ':'); + + var rowRange = tPair.Split(':'); + firstRow = rowRange[0]; + lastRow = rowRange[1]; + } + else + { + firstRow = tPair; + lastRow = tPair; + } + foreach (var row in this.Rows(Int32.Parse(firstRow), Int32.Parse(lastRow))) + { + retVal.Add(row); + } + } + return retVal; + } + + public IXLTableField Field(String fieldName) + { + return Field(GetFieldIndex(fieldName)); + } + + private Dictionary fields = new Dictionary(); + public IXLTableField Field(Int32 fieldIndex) + { + if (!fields.ContainsKey(fieldIndex)) + { + if (fieldIndex >= HeadersRow().CellCount()) + throw new ArgumentOutOfRangeException(); + + var newField = new XLTableField(this) { Index = fieldIndex, Name = HeadersRow().Cell(fieldIndex + 1).GetString() }; + fields.Add(fieldIndex, newField); + } + + return fields[fieldIndex]; + } + + private Dictionary fieldNames = new Dictionary(); + public Int32 GetFieldIndex(String name) + { + if (fieldNames.ContainsKey(name)) + { + return fieldNames[name].Index; + } + else + { + var headersRow = HeadersRow(); + Int32 cellCount = headersRow.CellCount(); + for (Int32 cellPos = 1; cellPos <= cellCount; cellPos++) + { + if (headersRow.Cell(cellPos).GetString().Equals(name)) + { + if (fieldNames.ContainsKey(name)) + { + throw new ArgumentException("The header row contains more than one field name '" + name + "'."); + } + else + { + fieldNames.Add(name, Field(cellPos - 1)); + } + } + } + if (fieldNames.ContainsKey(name)) + { + return fieldNames[name].Index; + } + else + { + throw new ArgumentOutOfRangeException("The header row doesn't contain field name '" + name + "'."); + } + } + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableField.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableField.cs new file mode 100644 index 0000000..db7e7fd --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableField.cs @@ -0,0 +1,84 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal class XLTableField: IXLTableField + { + private XLTable table; + public XLTableField(XLTable table) + { + this.table = table; + } + + public Int32 Index { get; set; } + public String Name { get; set; } + + private String totalsRowLabel; + public String TotalsRowLabel + { + get { return totalsRowLabel; } + set + { + totalsRowFunction = XLTotalsRowFunction.None; + table.TotalsRow().Cell(Index + 1).Value = value; + totalsRowLabel = value; + } + } + + public String TotalsRowFormulaA1 + { + get { return table.TotalsRow().Cell(Index + 1).FormulaA1; } + set + { + totalsRowFunction = XLTotalsRowFunction.Custom; + table.TotalsRow().Cell(Index + 1).FormulaA1 = value; + } + } + public String TotalsRowFormulaR1C1 + { + get { return table.TotalsRow().Cell(Index + 1).FormulaR1C1; } + set + { + totalsRowFunction = XLTotalsRowFunction.Custom; + table.TotalsRow().Cell(Index + 1).FormulaR1C1 = value; + } + } + + private XLTotalsRowFunction totalsRowFunction; + public XLTotalsRowFunction TotalsRowFunction + { + get { return totalsRowFunction; } + set + { + if (value != XLTotalsRowFunction.None && value != XLTotalsRowFunction.Custom) + { + var cell = table.TotalsRow().Cell(Index + 1); + String formula = String.Empty; + switch (value) + { + case XLTotalsRowFunction.Sum: formula = "109"; break; + case XLTotalsRowFunction.Minimum: formula = "105"; break; + case XLTotalsRowFunction.Maximum: formula = "104"; break; + case XLTotalsRowFunction.Average: formula = "101"; break; + case XLTotalsRowFunction.Count: formula = "103"; break; + case XLTotalsRowFunction.CountNumbers: formula = "102"; break; + case XLTotalsRowFunction.StandardDeviation: formula = "107"; break; + case XLTotalsRowFunction.Variance: formula = "110"; break; + } + + cell.FormulaA1 = "SUBTOTAL(" + formula + ",[" + Name + "])"; + var lastCell = table.LastRow().Cell(Index + 1); + if (lastCell.DataType != XLCellValues.Text) + { + cell.DataType = lastCell.DataType; + cell.Style.NumberFormat = lastCell.Style.NumberFormat; + } + } + totalsRowFunction = value; + } + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRow.cs new file mode 100644 index 0000000..82aa691 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRow.cs @@ -0,0 +1,28 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal class XLTableRow: XLRangeRow, IXLTableRow + { + private XLTable table; + public XLTableRow(XLTable table, XLRangeRow rangeRow) + : base(rangeRow.RangeParameters) + { + this.table = table; + } + + public IXLCell Field(Int32 index) + { + return Cell(index + 1); + } + + public IXLCell Field(String name) + { + Int32 fieldIndex = table.GetFieldIndex(name); + return Cell(fieldIndex + 1); + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs new file mode 100644 index 0000000..067ac83 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs @@ -0,0 +1,138 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal class XLTableRows: IXLTableRows + { + XLWorksheet worksheet; + public XLTableRows(XLWorksheet worksheet) + { + Style = worksheet.Style; + this.worksheet = worksheet; + } + + List ranges = new List(); + + public void Clear() + { + ranges.ForEach(r => r.Clear()); + } + + public void Add(IXLTableRow range) + { + ranges.Add((XLTableRow)range); + } + + public IEnumerator GetEnumerator() + { + var retList = new List(); + ranges.ForEach(c => retList.Add(c)); + return retList.GetEnumerator(); + } + + System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() + { + return GetEnumerator(); + } + + #region IXLStylized Members + + private IXLStyle style; + public IXLStyle Style + { + get + { + return style; + } + set + { + style = new XLStyle(this, value); + ranges.ForEach(r => r.Style = value); + } + } + + public IEnumerable Styles + { + get + { + UpdatingStyle = true; + yield return style; + foreach (var rng in ranges) + { + yield return rng.Style; + foreach (var r in rng.Worksheet.Internals.CellsCollection.Values.Where(c => + c.Address.RowNumber >= rng.RangeAddress.FirstAddress.RowNumber + && c.Address.RowNumber <= rng.RangeAddress.LastAddress.RowNumber + && c.Address.ColumnNumber >= rng.RangeAddress.FirstAddress.ColumnNumber + && c.Address.ColumnNumber <= rng.RangeAddress.LastAddress.ColumnNumber + )) + { + yield return r.Style; + } + } + UpdatingStyle = false; + } + } + + public Boolean UpdatingStyle { get; set; } + + #endregion + + public IXLCells Cells() + { + var cellHash = new HashSet(); + foreach (var container in ranges) + { + foreach (var cell in container.Cells()) + { + if (!cellHash.Contains(cell)) + { + cellHash.Add(cell); + } + } + } + var cells = new XLCells(worksheet); + cells.AddRange(cellHash); + return (IXLCells)cells; + } + + public IXLCells CellsUsed() + { + var cellHash = new HashSet(); + foreach (var container in ranges) + { + foreach (var cell in container.CellsUsed()) + { + if (!cellHash.Contains(cell)) + { + cellHash.Add(cell); + } + } + } + var cells = new XLCells(worksheet); + cells.AddRange(cellHash); + return (IXLCells)cells; + } + + public IXLCells CellsUsed(Boolean includeStyles) + { + var cellHash = new HashSet(); + foreach (var container in ranges) + { + foreach (var cell in container.CellsUsed(includeStyles)) + { + if (!cellHash.Contains(cell)) + { + cellHash.Add(cell); + } + } + } + var cells = new XLCells(worksheet); + cells.AddRange(cellHash); + return (IXLCells)cells; + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTables.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTables.cs new file mode 100644 index 0000000..17eddcb --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTables.cs @@ -0,0 +1,36 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public class XLTables: IXLTables + { + private List tables = new List(); + public IEnumerator GetEnumerator() + { + return tables.GetEnumerator(); + } + + System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() + { + return GetEnumerator(); + } + + public void Add(IXLTable table) + { + tables.Add(table); + } + + public IXLTable Table(Int32 index) + { + return tables[index]; + } + + public IXLTable Table(String name) + { + return tables.Where(t => t.Name == name).Single(); + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index f316249..c75d095 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -134,10 +134,10 @@ foreach (var col in worksheetPart.Worksheet.Descendants()) { - IXLStylized toApply; + //IXLStylized toApply; if (col.Max != XLWorksheet.MaxNumberOfColumns) { - toApply = ws.Columns(col.Min, col.Max); + var toApply = ws.Columns(col.Min, col.Max); var xlColumns = (XLColumns)toApply; if (col.Width != null) xlColumns.Width = col.Width; @@ -160,7 +160,7 @@ } else { - toApply.Style = DefaultStyle; + xlColumns.Style = DefaultStyle; } } } @@ -182,14 +182,20 @@ if (row.OutlineLevel != null) xlRow.OutlineLevel = row.OutlineLevel; - Int32 styleIndex = row.StyleIndex != null ? Int32.Parse(row.StyleIndex.InnerText) : -1; - if (styleIndex > 0) + if (row.CustomFormat != null) { - ApplyStyle(xlRow, styleIndex, s, fills, borders, fonts, numberingFormats); - } - else - { - xlRow.Style = DefaultStyle; + Int32 styleIndex = row.StyleIndex != null ? Int32.Parse(row.StyleIndex.InnerText) : -1; + if (styleIndex > 0) + { + ApplyStyle(xlRow, styleIndex, s, fills, borders, fonts, numberingFormats); + } + else + { + //((XLRow)xlRow).style = ws.Style; + //((XLRow)xlRow).SetStyleNoColumns(ws.Style); + xlRow.Style = DefaultStyle; + //xlRow.Style = ws.Style; + } } } @@ -261,8 +267,46 @@ ws.Cell(dCell.CellReference).Value = dCell.CellValue.Text; ws.Cell(dCell.CellReference).Style.NumberFormat.NumberFormatId = Int32.Parse(numberFormatId); } - } + + } + foreach (var tablePart in worksheetPart.TableDefinitionParts) + { + var dTable = (Table)tablePart.Table; + var reference = dTable.Reference.Value; + var xlTable = ws.Range(reference).CreateTable(dTable.Name); + if (dTable.TotalsRowCount != null && dTable.TotalsRowCount.Value > 0) + ((XLTable)xlTable).showTotalsRow = true; + + if (dTable.TableStyleInfo != null) + { + if (dTable.TableStyleInfo.ShowFirstColumn != null) + xlTable.EmphasizeFirstColumn = dTable.TableStyleInfo.ShowFirstColumn.Value; + if (dTable.TableStyleInfo.ShowLastColumn != null) + xlTable.EmphasizeLastColumn = dTable.TableStyleInfo.ShowLastColumn.Value; + if (dTable.TableStyleInfo.ShowRowStripes != null) + xlTable.ShowRowStripes = dTable.TableStyleInfo.ShowRowStripes.Value; + if (dTable.TableStyleInfo.ShowColumnStripes != null) + xlTable.ShowColumnStripes = dTable.TableStyleInfo.ShowColumnStripes.Value; + if (dTable.TableStyleInfo.Name != null) + xlTable.Theme = (XLTableTheme)Enum.Parse(typeof(XLTableTheme), dTable.TableStyleInfo.Name.Value); + } + + xlTable.ShowAutoFilter = dTable.AutoFilter != null; + + foreach (var column in dTable.TableColumns) + { + var tableColumn = (TableColumn)column; + if (tableColumn.TotalsRowFunction != null) + xlTable.Field(tableColumn.Name.Value).TotalsRowFunction = totalsRowFunctionValues.Single(p => p.Value == tableColumn.TotalsRowFunction.Value).Key; + + if (tableColumn.TotalsRowFormula != null) + xlTable.Field(tableColumn.Name.Value).TotalsRowFormulaA1 = tableColumn.TotalsRowFormula.Text; + + if (tableColumn.TotalsRowLabel != null) + xlTable.Field(tableColumn.Name.Value).TotalsRowLabel = tableColumn.TotalsRowLabel.Value; + } + } var printOptionsQuery = worksheetPart.Worksheet.Descendants(); if (printOptionsQuery.Count() > 0) { @@ -448,6 +492,7 @@ } } + } private void SetProperties(SpreadsheetDocument dSpreadsheet) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index cf8fe34..07c9da1 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -35,6 +35,7 @@ private List> calculateModeValues = new List>(); private List> referenceModeValues = new List>(); private List> alignmentReadingOrderValues = new List>(); + private List> totalsRowFunctionValues = new List>(); private Boolean populated = false; private void PopulateEnums() @@ -54,6 +55,7 @@ PopulateCalculateModeValues(); PopulateReferenceModeValues(); PopulateAlignmentReadingOrderValues(); + PopulateTotalsRowFunctionValues(); populated = true; } } @@ -118,6 +120,7 @@ } } + #region Pouplate Values private void PopulateUnderlineValues() { @@ -247,7 +250,23 @@ alignmentReadingOrderValues.Add(new KeyValuePair(XLAlignmentReadingOrderValues.LeftToRight, 1)); alignmentReadingOrderValues.Add(new KeyValuePair(XLAlignmentReadingOrderValues.RightToLeft, 2)); } - + + private void PopulateTotalsRowFunctionValues() + { + totalsRowFunctionValues.Add(new KeyValuePair(XLTotalsRowFunction.None, TotalsRowFunctionValues.None)); + totalsRowFunctionValues.Add(new KeyValuePair(XLTotalsRowFunction.Sum, TotalsRowFunctionValues.Sum)); + totalsRowFunctionValues.Add(new KeyValuePair(XLTotalsRowFunction.Minimum, TotalsRowFunctionValues.Minimum)); + totalsRowFunctionValues.Add(new KeyValuePair(XLTotalsRowFunction.Maximum, TotalsRowFunctionValues.Maximum)); + totalsRowFunctionValues.Add(new KeyValuePair(XLTotalsRowFunction.Average, TotalsRowFunctionValues.Average)); + totalsRowFunctionValues.Add(new KeyValuePair(XLTotalsRowFunction.Count, TotalsRowFunctionValues.Count)); + totalsRowFunctionValues.Add(new KeyValuePair(XLTotalsRowFunction.CountNumbers, TotalsRowFunctionValues.CountNumbers)); + totalsRowFunctionValues.Add(new KeyValuePair(XLTotalsRowFunction.StandardDeviation, TotalsRowFunctionValues.StandardDeviation)); + totalsRowFunctionValues.Add(new KeyValuePair(XLTotalsRowFunction.Variance, TotalsRowFunctionValues.Variance)); + totalsRowFunctionValues.Add(new KeyValuePair(XLTotalsRowFunction.Custom, TotalsRowFunctionValues.Custom)); + } + + #endregion + private void CreatePackage(String filePath) { SpreadsheetDocument package; @@ -278,6 +297,7 @@ relId = new RelIdGenerator(); sharedStrings = new Dictionary(); sharedStyles = new Dictionary(); + tableId = 0; WorkbookPart workbookPart; if (document.WorkbookPart == null) @@ -331,12 +351,29 @@ WorksheetPart worksheetPart; var sheets = workbookPart.Workbook.Sheets.Elements(); if (workbookPart.Parts.Where(p => p.RelationshipId == worksheet.RelId).Any()) + { worksheetPart = (WorksheetPart)workbookPart.GetPartById(worksheet.RelId); + var wsPartsToRemove = worksheetPart.TableDefinitionParts.ToList(); + wsPartsToRemove.ForEach(tdp=>worksheetPart.DeletePart(tdp)); + //foreach (var tdp in worksheetPart.TableDefinitionParts) + //{ + // worksheetPart.DeletePart(tdp); + // var w = workbookPart.Parts.Where(s => s.RelationshipId == tdp.RelationshipType); + // if (w.Count() > 0) + // { + // var wsPartToRemove = w.Single(); + // workbookPart.DeletePart(wsPartToRemove.OpenXmlPart); + // } + //} + } else + { worksheetPart = workbookPart.AddNewPart(worksheet.RelId); - + } + GenerateWorksheetPartContent(worksheetPart, worksheet); } + GenerateCalculationChainPartContent(workbookPart); @@ -349,6 +386,22 @@ SetPackageProperties(document); } + private void GenerateTables(XLWorksheet worksheet, WorksheetPart worksheetPart) + { + worksheetPart.Worksheet.RemoveAllChildren(); + if (worksheet.Tables.Count() > 0) + { + foreach (var table in worksheet.Tables) + { + String tableRelId = relId.GetNext(RelType.Workbook); + var xlTable = (XLTable)table; + xlTable.RelId = tableRelId; + TableDefinitionPart tableDefinitionPart = worksheetPart.AddNewPart(tableRelId); + GenerateTableDefinitionPartContent(tableDefinitionPart, xlTable); + } + } + } + private void GenerateExtendedFilePropertiesPartContent(ExtendedFilePropertiesPart extendedFilePropertiesPart, WorkbookPart workbookPart) { //if (extendedFilePropertiesPart.Properties.NamespaceDeclarations.Contains(new KeyValuePair( @@ -730,6 +783,7 @@ } } + #region GenerateWorkbookStylesPartContent private void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart) { var defaultStyle = DefaultStyle; @@ -865,6 +919,9 @@ } sharedStyles.Clear(); newSharedStyles.ForEach(kp => sharedStyles.Add(kp.Key, kp.Value)); + + //TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium9", DefaultPivotStyle = "PivotStyleLight16" }; + //workbookStylesPart.Stylesheet.Append(tableStyles1); } private void ResolveAlignments(WorkbookStylesPart workbookStylesPart) @@ -1366,13 +1423,17 @@ return newXLNumberFormat.Equals(xlNumberFormat); } +#endregion + #region GenerateWorksheetPartContent private void GenerateWorksheetPartContent(WorksheetPart worksheetPart, XLWorksheet xlWorksheet) { #region Worksheet if (worksheetPart.Worksheet == null) worksheetPart.Worksheet = new Worksheet(); + GenerateTables(xlWorksheet, worksheetPart); + if (!worksheetPart.Worksheet.NamespaceDeclarations.Contains(new KeyValuePair("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"))) worksheetPart.Worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); #endregion @@ -1392,7 +1453,7 @@ if (xlWorksheet.PageSetup.PagesTall > 0 || xlWorksheet.PageSetup.PagesWide > 0) worksheetPart.Worksheet.SheetProperties.PageSetupProperties.FitToPage = true; - + #endregion @@ -1445,9 +1506,9 @@ Double ySplit = 0; //if (xlWorksheet.SheetView.FreezePanes) //{ - pane.State = PaneStateValues.FrozenSplit; - hSplit = xlWorksheet.SheetView.SplitColumn; - ySplit = xlWorksheet.SheetView.SplitRow; + pane.State = PaneStateValues.FrozenSplit; + hSplit = xlWorksheet.SheetView.SplitColumn; + ySplit = xlWorksheet.SheetView.SplitRow; //} //else //{ @@ -1474,12 +1535,12 @@ #endregion var maxOutlineColumn = 0; - if (xlWorksheet.Columns().Count() > 0) - maxOutlineColumn = xlWorksheet.Columns().Cast().Max(c => c.OutlineLevel); + if (xlWorksheet.ColumnCount() > 0) + maxOutlineColumn = xlWorksheet.GetMaxColumnOutline(); var maxOutlineRow = 0; - if (xlWorksheet.Rows().Count() > 0) - maxOutlineRow = xlWorksheet.Rows().Cast().Max(c => c.OutlineLevel); + if (xlWorksheet.RowCount() > 0) + maxOutlineRow = xlWorksheet.GetMaxRowOutline(); #region SheetFormatProperties if (worksheetPart.Worksheet.SheetFormatProperties == null) @@ -1593,7 +1654,7 @@ if ((Int32)col.Max.Value > maxInColumnsCollection) maxInColumnsCollection = (Int32)col.Max.Value; } - + if (maxInColumnsCollection < XLWorksheet.MaxNumberOfColumns) { Column column = new Column() @@ -1607,7 +1668,7 @@ columns.Append(column); } } -#endregion + #endregion #region SheetData SheetData sheetData; @@ -1623,32 +1684,46 @@ sheetData = worksheetPart.Worksheet.Elements().First(); - var rowsFromCells = xlWorksheet.Internals.CellsCollection.Where(c => c.Key.ColumnNumber > 0 && c.Key.RowNumber > 0).Select(c => c.Key.RowNumber).Distinct(); - var rowsFromCollection = xlWorksheet.Internals.RowsCollection.Keys; - var allRows = rowsFromCells.ToList(); - allRows.AddRange(rowsFromCollection); - var distinctRows = allRows.Distinct(); + var cellsByRow = new Dictionary>(); + foreach (var c in xlWorksheet.Internals.CellsCollection.Values) + { + Int32 rowNum = c.Address.RowNumber; + if (!cellsByRow.ContainsKey(rowNum)) + cellsByRow.Add(rowNum, new List()); + cellsByRow[rowNum].Add(c); + } + + var distinctRows = cellsByRow.Keys.Union(xlWorksheet.Internals.RowsCollection.Keys); + Boolean noRows = (sheetData.Elements().FirstOrDefault() == null); + var sheetDataRows = sheetData.Elements().ToDictionary(r => (Int32)r.RowIndex.Value, r => r); foreach (var distinctRow in distinctRows.OrderBy(r => r)) { - Row row = sheetData.Elements().FirstOrDefault(r=>r.RowIndex.Value == (UInt32)distinctRow); - if (row == null) + Row row; // = sheetData.Elements().FirstOrDefault(r => r.RowIndex.Value == (UInt32)distinctRow); + if (sheetDataRows.ContainsKey(distinctRow)) + { + row = sheetDataRows[distinctRow]; + } + else { row = new Row() { RowIndex = (UInt32)distinctRow }; - if (sheetData.Elements().Count() == 0) + if (noRows) { sheetData.Append(row); + noRows = false; } else { - Row rowBeforeInsert = sheetData.Elements() - .Where(c => c.RowIndex.Value > row.RowIndex.Value) - .OrderBy(c => c.RowIndex.Value) - .FirstOrDefault(); - if (rowBeforeInsert == null) - sheetData.Append(row); - else + if (sheetDataRows.Where(r => r.Key > row.RowIndex.Value).Any()) + { + var minRow = sheetDataRows.Where(r => r.Key > (Int32)row.RowIndex.Value).Min(r=>r.Key); + Row rowBeforeInsert = sheetDataRows[minRow]; sheetData.InsertBefore(row, rowBeforeInsert); + } + else + { + sheetData.Append(row); + } } } @@ -1662,7 +1737,7 @@ row.Height = thisRow.Height; row.CustomHeight = true; row.StyleIndex = sharedStyles[thisRow.Style].StyleId; - row.CustomFormat = true; + row.CustomFormat = !thisRow.Style.Equals(xlWorksheet.Style); if (thisRow.IsHidden) row.Hidden = true; if (thisRow.Collapsed) row.Collapsed = true; if (thisRow.OutlineLevel > 0) row.OutlineLevel = (byte)thisRow.OutlineLevel; @@ -1683,93 +1758,98 @@ } cellsToRemove.ForEach(cell => row.RemoveChild(cell)); - foreach (var opCell in xlWorksheet.Internals.CellsCollection - .Where(c => c.Key.RowNumber == distinctRow) - .OrderBy(c => c.Key.ColumnNumber) - .Select(c => c)) + + if (cellsByRow.ContainsKey(distinctRow)) { - var styleId = sharedStyles[opCell.Value.Style].StyleId; - - var dataType = opCell.Value.DataType; - var cellReference = ((XLAddress)opCell.Key).GetTrimmedAddress(); - Boolean isNewCell = false; - Cell cell = row.Elements().FirstOrDefault(c => c.CellReference.Value == cellReference); - if (cell == null) - { - isNewCell = true; - cell = new Cell() { CellReference = cellReference }; - if (row.Elements().Count() == 0) - { - row.Append(cell); - } - else - { - Int32 newColumn = XLAddress.GetColumnNumberFromAddress1(cellReference); - Cell cellBeforeInsert = null; - Int32 lastCo = Int32.MaxValue; - foreach (var c in row.Elements().Where(c => XLAddress.GetColumnNumberFromAddress1(c.CellReference.Value) > newColumn)) + foreach (var opCell in cellsByRow[distinctRow] + .OrderBy(c => c.Address.ColumnNumber) + .Select(c => (XLCell)c)) + { + var styleId = sharedStyles[opCell.Style].StyleId; + + var dataType = opCell.DataType; + var cellReference = ((XLAddress)opCell.Address).GetTrimmedAddress(); + + Boolean isNewCell = false; + Cell cell = row.Elements().FirstOrDefault(c => c.CellReference.Value == cellReference); + if (cell == null) + { + isNewCell = true; + cell = new Cell() { CellReference = cellReference }; + if (row.Elements().Count() == 0) { - var thidCo = XLAddress.GetColumnNumberFromAddress1(c.CellReference.Value); - if (lastCo > thidCo) - { - cellBeforeInsert = c; - lastCo = thidCo; - } - } - if (cellBeforeInsert == null) row.Append(cell); + } else - row.InsertBefore(cell, cellBeforeInsert); - } - } - - cell.StyleIndex = styleId; - if (!StringExtensions.IsNullOrWhiteSpace(opCell.Value.FormulaA1)) - { - cell.CellFormula = new CellFormula(opCell.Value.FormulaA1); - cell.CellValue = null; - } - else - { - cell.CellFormula = null; - - if (opCell.Value.DataType == XLCellValues.DateTime) - cell.DataType = null; - else - cell.DataType = GetCellValue(dataType); - - CellValue cellValue = new CellValue(); - if (dataType == XLCellValues.Text) - { - if (StringExtensions.IsNullOrWhiteSpace(opCell.Value.InnerText)) { - if (isNewCell) - cellValue = null; + Int32 newColumn = XLAddress.GetColumnNumberFromAddress1(cellReference); + + Cell cellBeforeInsert = null; + Int32 lastCo = Int32.MaxValue; + foreach (var c in row.Elements().Where(c => XLAddress.GetColumnNumberFromAddress1(c.CellReference.Value) > newColumn)) + { + var thidCo = XLAddress.GetColumnNumberFromAddress1(c.CellReference.Value); + if (lastCo > thidCo) + { + cellBeforeInsert = c; + lastCo = thidCo; + } + } + if (cellBeforeInsert == null) + row.Append(cell); else - cellValue.Text = String.Empty; + row.InsertBefore(cell, cellBeforeInsert); } - else - { - cellValue.Text = sharedStrings[opCell.Value.InnerText].ToString(); - } - cell.CellValue = cellValue; } - else if (dataType == XLCellValues.TimeSpan) + + cell.StyleIndex = styleId; + if (!StringExtensions.IsNullOrWhiteSpace(opCell.FormulaA1)) { - TimeSpan timeSpan = opCell.Value.GetTimeSpan(); - cellValue.Text = XLCell.baseDate.Add(timeSpan).ToOADate().ToString(CultureInfo.InvariantCulture); - cell.CellValue = cellValue; - } - else if (dataType == XLCellValues.DateTime || dataType == XLCellValues.Number) - { - cellValue.Text = Double.Parse(opCell.Value.InnerText).ToString(CultureInfo.InvariantCulture); - cell.CellValue = cellValue; + cell.CellFormula = new CellFormula(opCell.FormulaA1); + cell.CellValue = null; } else { - cellValue.Text = opCell.Value.InnerText; - cell.CellValue = cellValue; + cell.CellFormula = null; + + if (opCell.DataType == XLCellValues.DateTime) + cell.DataType = null; + else + cell.DataType = GetCellValue(dataType); + + CellValue cellValue = new CellValue(); + if (dataType == XLCellValues.Text) + { + if (StringExtensions.IsNullOrWhiteSpace(opCell.InnerText)) + { + if (isNewCell) + cellValue = null; + else + cellValue.Text = String.Empty; + } + else + { + cellValue.Text = sharedStrings[opCell.InnerText].ToString(); + } + cell.CellValue = cellValue; + } + else if (dataType == XLCellValues.TimeSpan) + { + TimeSpan timeSpan = opCell.GetTimeSpan(); + cellValue.Text = XLCell.baseDate.Add(timeSpan).ToOADate().ToString(CultureInfo.InvariantCulture); + cell.CellValue = cellValue; + } + else if (dataType == XLCellValues.DateTime || dataType == XLCellValues.Number) + { + cellValue.Text = Double.Parse(opCell.InnerText).ToString(CultureInfo.InvariantCulture); + cell.CellValue = cellValue; + } + else + { + cellValue.Text = opCell.InnerText; + cell.CellValue = cellValue; + } } } } @@ -1800,7 +1880,7 @@ mergeCells = worksheetPart.Worksheet.Elements().First(); mergeCells.RemoveAllChildren(); - foreach (var merged in xlWorksheet.Internals.MergedRanges.Select(m=>m.RangeAddress.FirstAddress.ToString() + ":" + m.RangeAddress.LastAddress.ToString())) + foreach (var merged in xlWorksheet.Internals.MergedRanges.Select(m => m.RangeAddress.FirstAddress.ToString() + ":" + m.RangeAddress.LastAddress.ToString())) { MergeCell mergeCell = new MergeCell() { Reference = merged }; mergeCells.Append(mergeCell); @@ -1818,31 +1898,31 @@ #region PrintOptions PrintOptions printOptions = null; - if (worksheetPart.Worksheet.Elements().Count() == 0) - { - OpenXmlElement previousElement; - if (hyperlinks != null) - previousElement = hyperlinks; - else if (mergeCells != null) - previousElement = mergeCells; - else if (phoneticProperties != null) - previousElement = phoneticProperties; - else if (sheetData != null) - previousElement = sheetData; - else if (columns != null) - previousElement = columns; - else - previousElement = worksheetPart.Worksheet.SheetFormatProperties; + if (worksheetPart.Worksheet.Elements().Count() == 0) + { + OpenXmlElement previousElement; + if (hyperlinks != null) + previousElement = hyperlinks; + else if (mergeCells != null) + previousElement = mergeCells; + else if (phoneticProperties != null) + previousElement = phoneticProperties; + else if (sheetData != null) + previousElement = sheetData; + else if (columns != null) + previousElement = columns; + else + previousElement = worksheetPart.Worksheet.SheetFormatProperties; - worksheetPart.Worksheet.InsertAfter(new PrintOptions(), previousElement); - } + worksheetPart.Worksheet.InsertAfter(new PrintOptions(), previousElement); + } - printOptions = worksheetPart.Worksheet.Elements().First(); + printOptions = worksheetPart.Worksheet.Elements().First(); - printOptions.HorizontalCentered = xlWorksheet.PageSetup.CenterHorizontally; - printOptions.VerticalCentered = xlWorksheet.PageSetup.CenterVertically; - printOptions.Headings = xlWorksheet.PageSetup.ShowRowAndColumnHeadings; - printOptions.GridLines = xlWorksheet.PageSetup.ShowGridlines; + printOptions.HorizontalCentered = xlWorksheet.PageSetup.CenterHorizontally; + printOptions.VerticalCentered = xlWorksheet.PageSetup.CenterVertically; + printOptions.Headings = xlWorksheet.PageSetup.ShowRowAndColumnHeadings; + printOptions.GridLines = xlWorksheet.PageSetup.ShowGridlines; #endregion #region PageMargins @@ -1879,19 +1959,17 @@ #region PageSetup if (worksheetPart.Worksheet.Elements().Count() == 0) { - var nps = new PageSetup(); - nps.Id = relId.GetNext(RelType.Workbook); worksheetPart.Worksheet.InsertAfter(new PageSetup(), pageMargins); } PageSetup pageSetup = worksheetPart.Worksheet.Elements().First(); - pageSetup.Orientation = pageOrientationValues.Single(p=>p.Key == xlWorksheet.PageSetup.PageOrientation).Value; + pageSetup.Orientation = pageOrientationValues.Single(p => p.Key == xlWorksheet.PageSetup.PageOrientation).Value; pageSetup.PaperSize = (UInt32)xlWorksheet.PageSetup.PaperSize; pageSetup.BlackAndWhite = xlWorksheet.PageSetup.BlackAndWhite; pageSetup.Draft = xlWorksheet.PageSetup.DraftQuality; - pageSetup.PageOrder = pageOrderValues.Single(p=>p.Key == xlWorksheet.PageSetup.PageOrder).Value; - pageSetup.CellComments = showCommentsValues.Single(s=>s.Key == xlWorksheet.PageSetup.ShowComments).Value; + pageSetup.PageOrder = pageOrderValues.Single(p => p.Key == xlWorksheet.PageSetup.PageOrder).Value; + pageSetup.CellComments = showCommentsValues.Single(s => s.Key == xlWorksheet.PageSetup.ShowComments).Value; pageSetup.Errors = printErrorValues.Single(p => p.Key == xlWorksheet.PageSetup.PrintErrorValue).Value; if (xlWorksheet.PageSetup.FirstPageNumber > 0) @@ -1948,7 +2026,7 @@ headerFooter.AlignWithMargins = xlWorksheet.PageSetup.AlignHFWithMargins; headerFooter.DifferentFirst = true; headerFooter.DifferentOddEven = true; - + OddHeader oddHeader = new OddHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.OddPages)); headerFooter.Append(oddHeader); OddFooter oddFooter = new OddFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.OddPages)); @@ -1974,7 +2052,7 @@ OpenXmlElement previousElement; if (worksheetPart.Worksheet.Elements().Count() > 0) previousElement = headerFooter; - else + else previousElement = pageSetup; worksheetPart.Worksheet.InsertAfter(new RowBreaks(), previousElement); @@ -2033,6 +2111,49 @@ worksheetPart.Worksheet.RemoveAllChildren(); } #endregion + + #region Drawings & OleObjects + Drawing drawing = worksheetPart.Worksheet.Elements().FirstOrDefault(); + LegacyDrawing legacyDrawing = worksheetPart.Worksheet.Elements().FirstOrDefault(); + LegacyDrawingHeaderFooter legacyDrawingHF = worksheetPart.Worksheet.Elements().FirstOrDefault(); + OleObjects oleObjects = worksheetPart.Worksheet.Elements().FirstOrDefault(); + #endregion + + #region Tables + worksheetPart.Worksheet.RemoveAllChildren(); + { + OpenXmlElement previousElement; + if (oleObjects != null) + previousElement = oleObjects; + else if (legacyDrawingHF != null) + previousElement = legacyDrawingHF; + else if (legacyDrawing != null) + previousElement = legacyDrawing; + else if (drawing != null) + previousElement = drawing; + else if (worksheetPart.Worksheet.Elements().Count() > 0) + previousElement = columnBreaks; + else if (worksheetPart.Worksheet.Elements().Count() > 0) + previousElement = rowBreaks; + else if (worksheetPart.Worksheet.Elements().Count() > 0) + previousElement = headerFooter; + else + previousElement = pageSetup; + + worksheetPart.Worksheet.InsertAfter(new TableParts(), previousElement); + } + + TableParts tableParts = worksheetPart.Worksheet.Elements().First(); + + tableParts.Count = (UInt32)xlWorksheet.Tables.Count(); + foreach (var table in xlWorksheet.Tables) + { + var xlTable = (XLTable)table; + TablePart tablePart = new TablePart() { Id = xlTable.RelId }; + tableParts.Append(tablePart); + } + + #endregion } private Double GetColumnWidth(Double columnWidth) @@ -2092,6 +2213,7 @@ } } + #endregion private void GenerateCalculationChainPartContent(WorkbookPart workbookPart) { @@ -2157,52 +2279,52 @@ A.ColorScheme colorScheme1 = new A.ColorScheme() { Name = "Office" }; A.Dark1Color dark1Color1 = new A.Dark1Color(); - A.SystemColor systemColor1 = new A.SystemColor() { Val = A.SystemColorValues.WindowText, LastColor = Theme.Text1.Color.ToHex().Substring(3)}; + A.SystemColor systemColor1 = new A.SystemColor() { Val = A.SystemColorValues.WindowText, LastColor = Theme.Text1.Color.ToHex().Substring(2)}; dark1Color1.Append(systemColor1); A.Light1Color light1Color1 = new A.Light1Color(); - A.SystemColor systemColor2 = new A.SystemColor() { Val = A.SystemColorValues.Window, LastColor = Theme.Background1.Color.ToHex().Substring(3) }; + A.SystemColor systemColor2 = new A.SystemColor() { Val = A.SystemColorValues.Window, LastColor = Theme.Background1.Color.ToHex().Substring(2) }; light1Color1.Append(systemColor2); A.Dark2Color dark2Color1 = new A.Dark2Color(); - A.RgbColorModelHex rgbColorModelHex1 = new A.RgbColorModelHex() { Val = Theme.Text2.Color.ToHex().Substring(3) }; + A.RgbColorModelHex rgbColorModelHex1 = new A.RgbColorModelHex() { Val = Theme.Text2.Color.ToHex().Substring(2) }; dark2Color1.Append(rgbColorModelHex1); A.Light2Color light2Color1 = new A.Light2Color(); - A.RgbColorModelHex rgbColorModelHex2 = new A.RgbColorModelHex() { Val = Theme.Background2.Color.ToHex().Substring(3) }; + A.RgbColorModelHex rgbColorModelHex2 = new A.RgbColorModelHex() { Val = Theme.Background2.Color.ToHex().Substring(2) }; light2Color1.Append(rgbColorModelHex2); A.Accent1Color accent1Color1 = new A.Accent1Color(); - A.RgbColorModelHex rgbColorModelHex3 = new A.RgbColorModelHex() { Val = Theme.Accent1.Color.ToHex().Substring(3) }; + A.RgbColorModelHex rgbColorModelHex3 = new A.RgbColorModelHex() { Val = Theme.Accent1.Color.ToHex().Substring(2) }; accent1Color1.Append(rgbColorModelHex3); A.Accent2Color accent2Color1 = new A.Accent2Color(); - A.RgbColorModelHex rgbColorModelHex4 = new A.RgbColorModelHex() { Val = Theme.Accent2.Color.ToHex().Substring(3) }; + A.RgbColorModelHex rgbColorModelHex4 = new A.RgbColorModelHex() { Val = Theme.Accent2.Color.ToHex().Substring(2) }; accent2Color1.Append(rgbColorModelHex4); A.Accent3Color accent3Color1 = new A.Accent3Color(); - A.RgbColorModelHex rgbColorModelHex5 = new A.RgbColorModelHex() { Val = Theme.Accent3.Color.ToHex().Substring(3) }; + A.RgbColorModelHex rgbColorModelHex5 = new A.RgbColorModelHex() { Val = Theme.Accent3.Color.ToHex().Substring(2) }; accent3Color1.Append(rgbColorModelHex5); A.Accent4Color accent4Color1 = new A.Accent4Color(); - A.RgbColorModelHex rgbColorModelHex6 = new A.RgbColorModelHex() { Val = Theme.Accent4.Color.ToHex().Substring(3) }; + A.RgbColorModelHex rgbColorModelHex6 = new A.RgbColorModelHex() { Val = Theme.Accent4.Color.ToHex().Substring(2) }; accent4Color1.Append(rgbColorModelHex6); A.Accent5Color accent5Color1 = new A.Accent5Color(); - A.RgbColorModelHex rgbColorModelHex7 = new A.RgbColorModelHex() { Val = Theme.Accent5.Color.ToHex().Substring(3) }; + A.RgbColorModelHex rgbColorModelHex7 = new A.RgbColorModelHex() { Val = Theme.Accent5.Color.ToHex().Substring(2) }; accent5Color1.Append(rgbColorModelHex7); A.Accent6Color accent6Color1 = new A.Accent6Color(); - A.RgbColorModelHex rgbColorModelHex8 = new A.RgbColorModelHex() { Val = Theme.Accent6.Color.ToHex().Substring(3) }; + A.RgbColorModelHex rgbColorModelHex8 = new A.RgbColorModelHex() { Val = Theme.Accent6.Color.ToHex().Substring(2) }; accent6Color1.Append(rgbColorModelHex8); @@ -2729,5 +2851,76 @@ document.PackageProperties.ContentStatus = Properties.Status; } + UInt32 tableId = 0; + private void GenerateTableDefinitionPartContent(TableDefinitionPart tableDefinitionPart, XLTable xlTable) + { + tableId++; + String reference; + reference = xlTable.RangeAddress.FirstAddress.ToString() + ":" + xlTable.RangeAddress.LastAddress.ToString(); + + Table table = new Table() { + Id = tableId, + Name = xlTable.Name.RemoveSpecialCharacters(), + DisplayName = xlTable.Name, + Reference = reference }; + + if (xlTable.ShowTotalsRow) + table.TotalsRowCount = 1; + else + table.TotalsRowShown = false; + + TableColumns tableColumns1 = new TableColumns() { Count = (UInt32)xlTable.ColumnCount() }; + UInt32 columnId = 0; + foreach (var cell in xlTable.HeadersRow().Cells()) + { + columnId++; + String fieldName = cell.GetString(); + var xlField = xlTable.Field(fieldName); + TableColumn tableColumn1 = new TableColumn() { + Id = (UInt32)columnId, + Name = fieldName + }; + if (xlTable.ShowTotalsRow) + { + if (xlField.TotalsRowFunction != XLTotalsRowFunction.None) + { + tableColumn1.TotalsRowFunction = totalsRowFunctionValues.Single(p => p.Key == xlField.TotalsRowFunction).Value; + + if (xlField.TotalsRowFunction == XLTotalsRowFunction.Custom) + tableColumn1.TotalsRowFormula = new TotalsRowFormula(xlField.TotalsRowFormulaA1); + } + + if (!StringExtensions.IsNullOrWhiteSpace(xlField.TotalsRowLabel)) + tableColumn1.TotalsRowLabel = xlField.TotalsRowLabel; + } + tableColumns1.Append(tableColumn1); + } + + TableStyleInfo tableStyleInfo1 = new TableStyleInfo() { + Name = Enum.GetName(typeof(XLTableTheme), xlTable.Theme), + ShowFirstColumn = xlTable.EmphasizeFirstColumn, + ShowLastColumn = xlTable.EmphasizeLastColumn, + ShowRowStripes = xlTable.ShowRowStripes, + ShowColumnStripes = xlTable.ShowColumnStripes + }; + + if (xlTable.ShowAutoFilter) + { + AutoFilter autoFilter1 = new AutoFilter(); + + if (xlTable.ShowTotalsRow) + autoFilter1.Reference = xlTable.RangeAddress.FirstAddress.ToString() + ":" + + new XLAddress(xlTable.RangeAddress.LastAddress.RowNumber - 1, xlTable.RangeAddress.LastAddress.ColumnNumber).ToString(); + else + autoFilter1.Reference = reference; + + table.Append(autoFilter1); + } + + table.Append(tableColumns1); + table.Append(tableStyleInfo1); + + tableDefinitionPart.Table = table; + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index 433c484..dfd5cd1 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -28,6 +28,7 @@ Worksheet = this; NamedRanges = new XLNamedRanges(workbook); SheetView = new XLSheetView(); + Tables = new XLTables(); this.workbook = workbook; Style = workbook.Style; Internals = new XLWorksheetInternals(new XLCellCollection(), new XLColumnsCollection(), new XLRowsCollection(), new XLRanges(workbook, workbook.Style) , workbook); @@ -144,8 +145,7 @@ public String Name { get; set; } public Int32 SheetId { get; set; } public String RelId { get; set; } - - + internal Int32 sheetIndex; public Int32 SheetIndex { @@ -255,24 +255,28 @@ return retVal; } - public IXLColumns Columns( String columns) + public IXLColumns Columns(String columns) { var retVal = new XLColumns(this); var columnPairs = columns.Split(','); foreach (var pair in columnPairs) { + var tPair = pair.Trim(); String firstColumn; String lastColumn; - if (pair.Trim().Contains(':')) + if (tPair.Contains(':') || tPair.Contains('-')) { - var columnRange = pair.Trim().Split(':'); + if (tPair.Contains('-')) + tPair = tPair.Replace('-', ':'); + + var columnRange = tPair.Split(':'); firstColumn = columnRange[0]; lastColumn = columnRange[1]; } else { - firstColumn = pair.Trim(); - lastColumn = pair.Trim(); + firstColumn = tPair; + lastColumn = tPair; } Int32 tmp; @@ -328,18 +332,22 @@ var rowPairs = rows.Split(','); foreach (var pair in rowPairs) { + var tPair = pair.Trim(); String firstRow; String lastRow; - if (pair.Trim().Contains(':')) + if (tPair.Contains(':') || tPair.Contains('-')) { - var rowRange = pair.Trim().Split(':'); + if (tPair.Contains('-')) + tPair = tPair.Replace('-', ':'); + + var rowRange = tPair.Split(':'); firstRow = rowRange[0]; lastRow = rowRange[1]; } else { - firstRow = pair.Trim(); - lastRow = pair.Trim(); + firstRow = tPair; + lastRow = tPair; } foreach (var row in this.Rows(Int32.Parse(firstRow), Int32.Parse(lastRow))) { @@ -484,5 +492,68 @@ } public IXLNamedRanges NamedRanges { get; private set; } public IXLSheetView SheetView { get; private set; } + public IXLTables Tables { get; private set; } + + #region Outlines + private Dictionary columnOutlineCount = new Dictionary(); + public void IncrementColumnOutline(Int32 level) + { + if (level > 0) + { + if (!columnOutlineCount.ContainsKey(level)) + columnOutlineCount.Add(level, 0); + + columnOutlineCount[level]++; + } + } + public void DecrementColumnOutline(Int32 level) + { + if (level > 0) + { + if (!columnOutlineCount.ContainsKey(level)) + columnOutlineCount.Add(level, 0); + + if (columnOutlineCount[level] > 0) + columnOutlineCount[level]--; + } + } + public Int32 GetMaxColumnOutline() + { + if (columnOutlineCount.Count == 0) + return 0; + else + return columnOutlineCount.Where(kp => kp.Value > 0).Max(kp => kp.Key); + } + + private Dictionary rowOutlineCount = new Dictionary(); + public void IncrementRowOutline(Int32 level) + { + if (level > 0) + { + if (!rowOutlineCount.ContainsKey(level)) + rowOutlineCount.Add(level, 0); + + rowOutlineCount[level]++; + } + } + public void DecrementRowOutline(Int32 level) + { + if (level > 0) + { + if (!rowOutlineCount.ContainsKey(level)) + rowOutlineCount.Add(level, 0); + + if (rowOutlineCount[level] > 0) + rowOutlineCount[level]--; + } + } + public Int32 GetMaxRowOutline() + { + if (rowOutlineCount.Count == 0) + return 0; + else + return rowOutlineCount.Where(kp => kp.Value > 0).Max(kp => kp.Key); + } + #endregion } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj index 8cdfb70..f658c7d 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -60,6 +60,8 @@ + + diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs index c25b7ac..97d387f 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs @@ -63,6 +63,8 @@ new ColumnCells().Create(@"C:\Excel Files\Created\ColumnCells.xlsx"); new RowCells().Create(@"C:\Excel Files\Created\RowCells.xlsx"); new FreezePanes().Create(@"C:\Excel Files\Created\FreezePanes.xlsx"); + new UsingTables().Create(@"C:\Excel Files\Created\UsingTables.xlsx"); + new ShowCase().Create(@"C:\Excel Files\Created\ShowCase.xlsx"); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/LoadFiles.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/LoadFiles.cs index 95ade51..d3aab18 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/LoadFiles.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/LoadFiles.cs @@ -21,7 +21,7 @@ LoadAndSaveFile(forLoadingFolder + @"\" + fileName, forSavingFolder + @"\" + fileName); } - //LoadAndSaveFile(forLoadingFolder + @"\HeaderFooters.xlsx", forSavingFolder + @"\HeaderFooters.xlsx"); + //LoadAndSaveFile(forLoadingFolder + @"\StyleWorksheet.xlsx", forSavingFolder + @"\StyleWorksheet.xlsx"); } private static void LoadAndSaveFile(String input, String output) diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/Formulas.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/Formulas.cs index ffc77b1..d5ddd15 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/Formulas.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/Formulas.cs @@ -86,7 +86,7 @@ // Setting the formula of a range var rngData = ws.Range(2, 1, 4, 7); - rngData.LastColumn().FormulaR1C1 = "=IF(RC[-3]=RC[-1],\"Yes\", \"No\")"; + rngData.LastColumn().Cells().FormulaR1C1 = "=IF(RC[-3]=RC[-1],\"Yes\", \"No\")"; ws.Range(1, 1, 1, 7).Style.Fill.BackgroundColor = XLColor.Cyan; ws.Range(1, 1, 1, 7).Style.Font.Bold = true; diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/ShowCase.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/ShowCase.cs new file mode 100644 index 0000000..36a6459 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/ShowCase.cs @@ -0,0 +1,114 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; + +using System.Drawing; + +namespace ClosedXML_Examples +{ + public class ShowCase + { + public void Create(String filePath) + { + // Creating a new workbook + var wb = new XLWorkbook(); + + //Adding a worksheet + var ws = wb.Worksheets.Add("Contacts"); + + //Adding text + //Title + ws.Cell("B2").Value = "Contacts"; + //First Names + ws.Cell("B3").Value = "FName"; + ws.Cell("B4").Value = "John"; + ws.Cell("B5").Value = "Hank"; + ws.Cell("B6").Value = "Dagny"; + //Last Names + ws.Cell("C3").Value = "LName"; + ws.Cell("C4").Value = "Galt"; + ws.Cell("C5").Value = "Rearden"; + ws.Cell("C6").Value = "Taggart"; + + //Adding more data types + //Is an outcast? + ws.Cell("D3").Value = "Outcast"; + ws.Cell("D4").Value = true; + ws.Cell("D5").Value = false; + ws.Cell("D6").Value = false; + //Date of Birth + ws.Cell("E3").Value = "DOB"; + ws.Cell("E4").Value = new DateTime(1919, 1, 21); + ws.Cell("E5").Value = new DateTime(1907, 3, 4); + ws.Cell("E6").Value = new DateTime(1921, 12, 15); + //Income + ws.Cell("F3").Value = "Income"; + ws.Cell("F4").Value = 2000; + ws.Cell("F5").Value = 40000; + ws.Cell("F6").Value = 10000; + + //Defining ranges + //From worksheet + var rngTable = ws.Range("B2:F6"); + //From another range + var rngDates = rngTable.Range("D3:D5"); // The address is relative to rngTable (NOT the worksheet) + var rngNumbers = rngTable.Range("E3:E5"); // The address is relative to rngTable (NOT the worksheet) + + //Formatting dates and numbers + //Using a OpenXML's predefined formats + rngDates.Style.NumberFormat.NumberFormatId = 15; + //Using a custom format + rngNumbers.Style.NumberFormat.Format = "$ #,##0"; + + //Format title cell + rngTable.Cell(1, 1).Style.Font.Bold = true; + rngTable.Cell(1, 1).Style.Fill.BackgroundColor = XLColor.CornflowerBlue; + rngTable.Cell(1, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; + + //Merge title cells + rngTable.Row(1).Merge(); // We could've also used: rngTable.Range("A1:E1").Merge() + + //Formatting headers + var rngHeaders = rngTable.Range("A2:E2"); // The address is relative to rngTable (NOT the worksheet) + rngHeaders.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; + rngHeaders.Style.Font.Bold = true; + rngHeaders.Style.Font.FontColor = XLColor.DarkBlue; + rngHeaders.Style.Fill.BackgroundColor = XLColor.Aqua; + + // Create an Excel table with the data portion + var rngData = ws.Range("B3:F6"); + var excelTable = rngData.CreateTable(); + + // Add the totals row + excelTable.ShowTotalsRow = true; + // Put the average on the field "Income" + // Notice how we're calling the cell by the column name + excelTable.Field("Income").TotalsRowFunction = XLTotalsRowFunction.Average; + // Put a label on the totals cell of the field "DOB" + excelTable.Field("DOB").TotalsRowLabel = "Average:"; + + //Add thick borders + // This range will contain the entire contents of our spreadsheet: + var firstCell = ws.FirstCellUsed(); + var lastCell = ws.LastCellUsed(); + var contents = ws.Range(firstCell.Address, lastCell.Address); + + //Left border + contents.FirstColumn().Style.Border.LeftBorder = XLBorderStyleValues.Thick; + //Right border + contents.LastColumn().Style.Border.RightBorder = XLBorderStyleValues.Thick; + //Top border + contents.FirstRow().Style.Border.TopBorder = XLBorderStyleValues.Thick; + //Bottom border + contents.LastRow().Style.Border.BottomBorder = XLBorderStyleValues.Thick; + + // Adjust column widths to their content + ws.Columns(2, 6).AdjustToContents(); + + //Saving the workbook + wb.SaveAs(filePath); + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/DeletingRanges.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/DeletingRanges.cs index 5f569fd..2a06dc3 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/DeletingRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/DeletingRanges.cs @@ -16,29 +16,49 @@ public void Create(String filePath) { var workbook = new XLWorkbook(); - var ws = workbook.Worksheets.Add("Deleting Ranges"); - foreach (var ro in Enumerable.Range(1, 10)) - foreach (var co in Enumerable.Range(1, 10)) - ws.Cell(ro, co).Value = ws.Cell(ro, co).Address.ToString(); - - // Delete range and shift cells up - ws.Range("B4:C5").Delete(XLShiftDeletedCells.ShiftCellsUp); - // Delete range and shift cells left - ws.Range("D1:E3").Delete(XLShiftDeletedCells.ShiftCellsLeft); + // Deleting Columns + // Setup test values + ws.Columns("1-3, 5, 7").Style.Fill.BackgroundColor = XLColor.Gray; + ws.Columns("4, 6").Style.Fill.BackgroundColor = XLColor.GreenPigment; + ws.Row(1).Cells("1-3, 5, 7").Value = "FAIL"; - // Delete an entire row - ws.Row(5).Delete(); + ws.Column(7).Delete(); + ws.Column(1).Delete(); + ws.Columns(1,2).Delete(); + ws.Column(2).Delete(); - // Delete a row in a range, shift cells up - ws.Range("A1:C4").Row(2).Delete(XLShiftDeletedCells.ShiftCellsUp); + // Deleting Rows + ws.Rows("1,5,7").Style.Fill.BackgroundColor = XLColor.GreenPigment; + ws.Rows("2-4,6, 8").Style.Fill.BackgroundColor = XLColor.Gray; + ws.Column(1).Cells("2-4,6, 8").Value = "FAIL"; - // Delete an entire column - ws.Column(5).Delete(); + ws.Row(8).Delete(); + ws.Row(2).Delete(); + ws.Rows(2, 3).Delete(); + ws.Rows(3, 4).Delete(); - // Delete a column in a range, shift cells up - ws.Range("A1:C4").Column(2).Delete(XLShiftDeletedCells.ShiftCellsLeft); + // Deleting Ranges (Shifting Left) + var rng1 = ws.Range(2, 2, 8, 8); + rng1.Columns("1-3, 5, 7").Style.Fill.BackgroundColor = XLColor.Gray; + rng1.Columns("4, 6").Style.Fill.BackgroundColor = XLColor.Orange; + rng1.Row(1).Cells("1-3, 5, 7").Value = "FAIL"; + + rng1.Column(7).Delete(); + rng1.Column(1).Delete(); + rng1.Range(1, 1, rng1.RowCount(), 2).Delete(XLShiftDeletedCells.ShiftCellsLeft); + rng1.Column(2).Delete(); + + // Deleting Ranges (Shifting Up) + rng1.Rows("4, 6").Style.Fill.BackgroundColor = XLColor.Orange; + rng1.Rows("1-3, 5, 7").Style.Fill.BackgroundColor = XLColor.Gray; + rng1.Column(1).Cells("1-3, 5, 7").Value = "FAIL"; + + rng1.Row(7).Delete(); + rng1.Row(1).Delete(); + rng1.Range(1, 1, 2, rng1.ColumnCount()).Delete(XLShiftDeletedCells.ShiftCellsUp); + rng1.Row(2).Delete(); workbook.SaveAs(filePath); } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/ShiftingRanges.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/ShiftingRanges.cs index 16d2f8f..3a5ed6f 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/ShiftingRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/ShiftingRanges.cs @@ -25,7 +25,7 @@ rngHeaders.Style.Fill.BackgroundColor = XLColor.LightSalmon; ws.Columns().AdjustToContents(); - + workbook.SaveAs(@"C:\Excel Files\Created\ShiftingRanges.xlsx"); } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/UsingTables.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/UsingTables.cs new file mode 100644 index 0000000..6817e0c --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/UsingTables.cs @@ -0,0 +1,91 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; + +using System.Drawing; + +namespace ClosedXML_Examples.Ranges +{ + public class UsingTables + { + #region Methods + + // Public + public void Create(String filePath) + { + var wb = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); + var ws = wb.Worksheets.Worksheet(0); + var firstCell = ws.FirstCellUsed(); + var lastCell = ws.LastCellUsed(); + var range = ws.Range(firstCell.Address, lastCell.Address); + range.Row(1).Delete(); // Deleting the "Contacts" header (we don't need it for our purposes) + range.ClearStyles(); // We want to use a theme for table, not the hard coded format of the BasicTable + + var table = range.CreateTable(); // You can also use range.AsTable() if you want to + // manipulate the range as a table but don't want + // to create the table in the worksheet. + + // Let's activate the Totals row and add the sum of Income + table.ShowTotalsRow = true; + table.Field("Income").TotalsRowFunction = XLTotalsRowFunction.Sum; + // Just for fun let's add the text "Sum Of Income" to the totals row + table.Field(0).TotalsRowLabel = "Sum Of Income"; + + // Copy all the headers + Int32 columnWithHeaders = lastCell.Address.ColumnNumber + 2; + Int32 currentRow = table.RangeAddress.FirstAddress.RowNumber; + ws.Cell(currentRow, columnWithHeaders).Value = "Table Headers"; + foreach (var cell in table.HeadersRow().Cells()) + { + currentRow++; + ws.Cell(currentRow, columnWithHeaders).Value = cell.Value; + } + + // Format the headers as a table with a different style and no autofilters + var htFirstCell = ws.Cell(table.RangeAddress.FirstAddress.RowNumber, columnWithHeaders); + var htLastCell = ws.Cell(currentRow, columnWithHeaders); + var headersTable = ws.Range(htFirstCell, htLastCell).CreateTable("Headers"); + headersTable.Theme = XLTableTheme.TableStyleLight10; + headersTable.ShowAutoFilter = false; + + // Add a custom formula to the headersTable + headersTable.ShowTotalsRow = true; + headersTable.Field(0).TotalsRowFormulaA1 = "CONCATENATE(\"Count: \", CountA(Headers[Table Headers]))"; + + + // Copy the names + Int32 columnWithNames = columnWithHeaders + 2; + currentRow = table.RangeAddress.FirstAddress.RowNumber; // reset the currentRow + ws.Cell(currentRow, columnWithNames).Value = "Names"; + foreach (var row in table.Rows()) + { + currentRow++; + var fName = row.Field("FName").GetString(); // Notice how we're calling the cell by field name + var lName = row.Field("LName").GetString(); // Notice how we're calling the cell by field name + var name = String.Format("{0} {1}", fName, lName); + ws.Cell(currentRow, columnWithNames).Value = name; + } + + // Format the names as a table with a different style and no autofilters + var ntFirstCell = ws.Cell(table.RangeAddress.FirstAddress.RowNumber, columnWithNames); + var ntLastCell = ws.Cell(currentRow, columnWithNames); + var namesTable = ws.Range(ntFirstCell, ntLastCell).CreateTable(); + namesTable.Theme = XLTableTheme.TableStyleLight12; + namesTable.ShowAutoFilter = false; + + ws.Columns().AdjustToContents(); + ws.Columns("A,G,I").Width = 3; + + wb.SaveAs(filePath); + } + + // Private + + // Override + + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj index 59021ef..3fc374a 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj @@ -265,6 +265,36 @@ Excel\Style\XLStyle.cs + + Excel\Tables\IXLTable.cs + + + Excel\Tables\IXLTableField.cs + + + Excel\Tables\IXLTableRow.cs + + + Excel\Tables\IXLTableRows.cs + + + Excel\Tables\IXLTables.cs + + + Excel\Tables\XLTable.cs + + + Excel\Tables\XLTableField.cs + + + Excel\Tables\XLTableRow.cs + + + Excel\Tables\XLTableRows.cs + + + Excel\Tables\XLTables.cs + Excel\XLAddress.cs diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index 45c5438..04df5b9 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -11,22 +11,31 @@ { class Program { - static void Main(string[] args) + static void xMain(string[] args) { - var wb = new XLWorkbook(); - var ws = wb.Worksheets.Add("Sheet1"); - var col = ws.Row(1); - col.Cells("1:2, 4:5").Style.Fill.BackgroundColor = XLColor.Red; - col.Cells("3").Style.Fill.BackgroundColor = XLColor.Blue; - col.Cell(6).Style.Fill.BackgroundColor = XLColor.Orange; - col.Cells(7, 8).Style.Fill.BackgroundColor = XLColor.Blue; - - var colRng = ws.Range("A2:H2").FirstRow(); + var wb = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); + var ws = wb.Worksheets.Worksheet(0); + var firstCell = ws.FirstCellUsed(); + var lastCell = ws.LastCellUsed(); + var range = ws.Range(firstCell.Address, lastCell.Address); + range.Row(1).Delete(); + range.ClearStyles(); - colRng.Cells("1:2, 4:5").Style.Fill.BackgroundColor = XLColor.Red; - colRng.Cells("3").Style.Fill.BackgroundColor = XLColor.Blue; - colRng.Cell(6).Style.Fill.BackgroundColor = XLColor.Orange; - colRng.Cells(7, 8).Style.Fill.BackgroundColor = XLColor.Blue; + var table = range.CreateTable(); + + //Int32 currentRow = 8; + //foreach (var cell in table.HeadersRow().Cells()) + //{ + // ws.Cell(currentRow, cell.Address.ColumnNumber).Value = cell.Value; + //} + //foreach (var row in table.Rows()) + //{ + // currentRow++; + // foreach (var cell in row.Cells()) + // { + // ws.Cell(currentRow, cell.Address.ColumnNumber).Value = cell.Value; + // } + //} wb.SaveAs(@"C:\Excel Files\ForTesting\Sandbox.xlsx"); @@ -56,7 +65,7 @@ wb.Save(); } - static void xMain(string[] args) + static void Main(string[] args) { List running = new List(); foreach (Int32 r in Enumerable.Range(1, 1)) @@ -65,10 +74,10 @@ FillStyles(); var wb = new XLWorkbook(); - foreach (var i in Enumerable.Range(1, 3)) + foreach (var i in Enumerable.Range(1, 1)) { var ws = wb.Worksheets.Add("Sheet" + i); - foreach (var ro in Enumerable.Range(1, 100)) + foreach (var ro in Enumerable.Range(1, 2000)) { foreach (var co in Enumerable.Range(1, 100)) { @@ -99,7 +108,7 @@ } Console.WriteLine("-------"); Console.WriteLine("Avg total time: {0}", running.Average()); - //Console.ReadKey(); + Console.ReadKey(); } private static IXLStyle style1; diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox_VB/Module1.vb b/ClosedXML/ClosedXML/ClosedXML_Sandbox_VB/Module1.vb index 276fd85..cc7727f 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox_VB/Module1.vb +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox_VB/Module1.vb @@ -1,4 +1,6 @@ -Imports ClosedXML.Excel + +Imports ClosedXML +Imports ClosedXML.Excel Imports System.IO Module Module1 Sub Main1() @@ -24,18 +26,169 @@ workbook.SaveAs("C:\Excel Files\ForTesting\Issue_5957_Saved.xlsx") End Sub - Sub Main() - Dim wb = New XLWorkbook() - Dim ws = wb.Worksheets.Add("Sheet1") - For Each ro In Enumerable.Range(1, 100) - For Each co In Enumerable.Range(1, 10) - ws.Cell(ro, co).Value = ws.Cell(ro, co).Address.ToString() - Next - Next - ws.PageSetup.PagesWide = 1 - Dim ms As New MemoryStream - wb.SaveAs(ms) + Sub Main() + Dim table = GetDataTable(17, 8280) + DataSetToClosedXML1(table, "Center") + 'Console.ReadKey() End Sub + Public Function GetDataTable(ByVal NumberOfColumns As Integer, ByVal NumberOfRows As Integer) + Dim table = New DataTable() + For co = 1 To NumberOfColumns + Dim coName = "Column" & co + Dim coType As Type + Dim val = co Mod 5 + Select Case val + Case 1 + coType = GetType(String) + Case 2 + coType = GetType(Boolean) + Case 3 + coType = GetType(Date) + Case 4 + coType = GetType(Integer) + Case Else + coType = GetType(TimeSpan) + End Select + + table.Columns.Add(coName, coType) + Next + Dim baseDate = Date.Now + Dim rnd = New Random() + For ro = 1 To NumberOfRows + Dim dr As DataRow = table.NewRow() + For co = 1 To NumberOfColumns + Dim coName As String = "Column" & co + Dim coValue As Object + Dim val = co Mod 5 + Select Case val + Case 1 + coValue = Guid.NewGuid().ToString().Substring(1, 5) + Case 2 + coValue = (ro Mod 2 = 0) + Case 3 + coValue = DateTime.Now + Case 4 + coValue = rnd.Next(1, 1000) + Case Else + coValue = (DateTime.Now - baseDate) + End Select + dr.Item(coName) = coValue + Next + table.Rows.Add(dr) + Next + + Return table + End Function + + Public Sub DataSetToClosedXML1(ByVal MyDataTable As DataTable, ByVal BodyAlignment As String) + + 'based on ClosedXML.dll downloaded from this website (free license: Version 0.39.0 12/30/2010) (ASPNET 3.5, not 4.0) - add to References + 'http://closedxml.codeplex.com/ + + 'requires DocumentFormat.OpenXML.dll - add to References + 'DLL can be obtained by downloading the full OpenXML SDK 2.0 package OR just the assembly containing the DLL + 'http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c6e744e5-36e9-45f5-8d8c-331df206e0d0&DisplayLang=en + + 'OpenXML also requires Reference to the WindowsBase assembly (WindowsBase.dll) in order to use the System.IO.Packaging namespace. + + 'inputs: dataset; filename; Tab name; BodyAlignment = None (default), Left, Center, Right; optional SaveToDisk = Yes + 'code sample: DataSetToClosedXML(MyDS, "TestFile", "Test", "Left") + + + On Error GoTo ErrHandler + + + Dim wb As ClosedXML.Excel.XLWorkbook = New ClosedXML.Excel.XLWorkbook + + Dim ws As ClosedXML.Excel.IXLWorksheet = wb.Worksheets.Add("Sheet1") + + Dim column As DataColumn + Dim ColCount As Integer = MyDataTable.Columns.Count + Dim RowCount As Integer = MyDataTable.Rows.Count + Dim ColLetter As String + + For Each column In MyDataTable.Columns + + ws.Cell(1, MyDataTable.Columns.IndexOf(column) + 1).Value = column.ColumnName + + Next + + Dim contentRow As DataRow + Dim r, c As Integer + + For r = 0 To MyDataTable.Rows.Count - 1 + + contentRow = MyDataTable.Rows(r) + + For c = 0 To ColCount - 1 + + 'adjust for header in first row + ws.Cell(r + 2, c + 1).Value = contentRow(c) + + 'format for data type: + + Select Case MyDataTable.Columns(c).DataType.ToString + Case "System.Int16", "System.Int32", "System.Int64", "System.UInt16", "System.UInt32", "System.UInt64", "System.Byte", "System.SByte" + ws.Cell(r + 2, c + 1).Style.NumberFormat.NumberFormatId = 3 + Case "System.Single", "System.Double", "System.Decimal" + ws.Cell(r + 2, c + 1).Style.NumberFormat.NumberFormatId = 0 + Case "System.Boolean" + ws.Cell(r + 2, c + 1).Value = "'" & contentRow(c).ToString() + Case "System.DateTime" + ws.Cell(r + 2, c + 1).Style.NumberFormat.NumberFormatId = 14 + Case "System.String", "System.Char", "System.TimeSpan" + ws.Cell(r + 2, c + 1).Value = "'" & contentRow(c).ToString() + Case "System.Byte[]" + ws.Cell(r + 2, c + 1).DataType = ClosedXML.Excel.XLCellValues.Text + Case Else + ws.Cell(r + 2, c + 1).DataType = ClosedXML.Excel.XLCellValues.Text + End Select + + Next + + Next + + 'header: set to Bold + ws.Range(1, 1, 1, ColCount).Style.Font.Bold = True + + 'header column alignment (always centered) + ws.Range(1, 1, 1, ColCount).Style.Alignment.Horizontal = ClosedXML.Excel.XLAlignmentHorizontalValues.Center + + 'body column alignment + Select Case BodyAlignment + Case "None" + 'do nothing (default) + Case "Left" + ws.Range(2, 1, RowCount + 1, ColCount).Style.Alignment.Horizontal = ClosedXML.Excel.XLAlignmentHorizontalValues.Left + Case "Center" + ws.Range(2, 1, RowCount + 1, ColCount).Style.Alignment.Horizontal = ClosedXML.Excel.XLAlignmentHorizontalValues.Center + Case "Right" + ws.Range(2, 1, RowCount + 1, ColCount).Style.Alignment.Horizontal = ClosedXML.Excel.XLAlignmentHorizontalValues.Right + End Select + + 'auto-fit cols + 'ws.Columns(1, ColCount).AdjustToContents() + + 'View: freeze pane - freezes top row (headers) + ws.SheetView.FreezeRows(1) + + + 'save to disk + Dim startTime = DateTime.Now + wb.SaveAs("C:\Excel Files\ForTesting\Benchmark.xlsx") + Dim endTime = DateTime.Now + Console.WriteLine("Saved in {0} secs.", (endTime - startTime).TotalSeconds) + + Exit Sub + +ErrHandler: + 'this is a library Sub that displays a javascript alert + Console.WriteLine("Error: " & Err.Description) + + End Sub + + + End Module