diff --git a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj index 27adbf3..d382ef7 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj @@ -40,6 +40,7 @@ + @@ -54,6 +55,8 @@ + + @@ -111,6 +114,7 @@ + diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs index 34e3807..9483aea 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs @@ -15,9 +15,11 @@ XLCellValues DataType { get; set; } T GetValue(); String GetString(); - String GetFormattedValue(); + String GetFormattedString(); Double GetDouble(); Boolean GetBoolean(); DateTime GetDateTime(); + void Clear(); + void Delete(XLShiftDeletedCells shiftDeleteCells); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index bfbc6ed..5518bba 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -43,19 +43,21 @@ { return GetValue(); } - public String GetFormattedValue() + public String GetFormattedString() { if (dataType == XLCellValues.Boolean) { return (cellValue != "0").ToString(); } + else if (dataType == XLCellValues.DateTime || IsDateFormat()) + { + String format = GetFormat(); + return DateTime.FromOADate(Double.Parse(cellValue)).ToString(format); + } else if (dataType == XLCellValues.Number) { - return Double.Parse(cellValue).ToString(Style.NumberFormat.Format); - } - else if (dataType == XLCellValues.DateTime) - { - return DateTime.FromOADate(Double.Parse(cellValue)).ToString(Style.NumberFormat.Format); + String format = GetFormat(); + return Double.Parse(cellValue).ToString(format); } else { @@ -63,6 +65,31 @@ } } + private bool IsDateFormat() + { + return (dataType == XLCellValues.Number + && String.IsNullOrWhiteSpace(Style.NumberFormat.Format) + && ((Style.NumberFormat.NumberFormatId >= 14 + && Style.NumberFormat.NumberFormatId <= 22) + || (Style.NumberFormat.NumberFormatId >= 45 + && Style.NumberFormat.NumberFormatId <= 47))); + } + + private String GetFormat() + { + String format; + if (String.IsNullOrWhiteSpace(Style.NumberFormat.Format)) + { + var formatCodes = GetFormatCodes(); + format = formatCodes[Style.NumberFormat.NumberFormatId]; + } + else + { + format = Style.NumberFormat.Format; + } + return format; + } + private Boolean initialized = false; private String cellValue = String.Empty; public Object Value @@ -248,5 +275,53 @@ dataType = value; } } + + public void Clear() + { + worksheet.Range(Address, Address).Clear(); + } + public void Delete(XLShiftDeletedCells shiftDeleteCells) + { + worksheet.Range(Address, Address).Delete(shiftDeleteCells); + } + + private static Dictionary formatCodes; + private static Dictionary GetFormatCodes() + { + if (formatCodes == null) + { + var fCodes = new Dictionary(); + fCodes.Add(0, ""); + fCodes.Add(1, "0"); + fCodes.Add(2, "0.00"); + fCodes.Add(3, "#,##0"); + fCodes.Add(4, "#,##0.00"); + fCodes.Add(9, "0%"); + fCodes.Add(10, "0.00%"); + fCodes.Add(11, "0.00E+00"); + fCodes.Add(12, "# ?/?"); + fCodes.Add(13, "# ??/??"); + fCodes.Add(14, "MM-dd-yy"); + fCodes.Add(15, "d-MMM-yy"); + fCodes.Add(16, "d-MMM"); + fCodes.Add(17, "MMM-yy"); + fCodes.Add(18, "h:mm AM/PM"); + fCodes.Add(19, "h:mm:ss AM/PM"); + fCodes.Add(20, "h:mm"); + fCodes.Add(21, "h:mm:ss"); + fCodes.Add(22, "M/d/yy h:mm"); + fCodes.Add(37, "#,##0 ;(#,##0)"); + fCodes.Add(38, "#,##0 ;[Red](#,##0)"); + fCodes.Add(39, "#,##0.00;(#,##0.00)"); + fCodes.Add(40, "#,##0.00;[Red](#,##0.00)"); + fCodes.Add(45, "mm:ss"); + fCodes.Add(46, "[h]:mm:ss"); + fCodes.Add(47, "mmss.0"); + fCodes.Add(48, "##0.0E+0"); + fCodes.Add(49, "@"); + formatCodes = fCodes; + } + return formatCodes; + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs index 1c2afe8..4dea625 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs @@ -16,5 +16,6 @@ void Clear(); IXLCell Cell(int row); + void AdjustToContents(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs index 4bb1d54..fdd373a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs @@ -9,5 +9,6 @@ { Double Width { set; } void Delete(); + void AdjustToContents(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs index 039e6e2..130b047 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs @@ -9,10 +9,11 @@ internal class XLColumn: XLRangeBase, IXLColumn { public XLColumn(Int32 column, XLColumnParameters xlColumnParameters) + : base(new XLRangeAddress(1, column, XLWorksheet.MaxNumberOfRows, column)) { SetColumnNumber(column); Worksheet = xlColumnParameters.Worksheet; - + this.IsReference = xlColumnParameters.IsReference; if (IsReference) @@ -28,14 +29,14 @@ void Worksheet_RangeShiftedColumns(XLRange range, int columnsShifted) { - if (range.FirstAddressInSheet.ColumnNumber <= this.ColumnNumber()) + if (range.RangeAddress.FirstAddress.ColumnNumber <= this.ColumnNumber()) SetColumnNumber(this.ColumnNumber() + columnsShifted); } private void SetColumnNumber(Int32 column) { - FirstAddressInSheet = new XLAddress(1, column); - LastAddressInSheet = new XLAddress(XLWorksheet.MaxNumberOfRows, column); + RangeAddress.FirstAddress = new XLAddress(1, column); + RangeAddress.LastAddress = new XLAddress(XLWorksheet.MaxNumberOfRows, column); } public Boolean IsReference { get; private set; } @@ -163,11 +164,11 @@ public Int32 ColumnNumber() { - return this.FirstAddressInSheet.ColumnNumber; + return this.RangeAddress.FirstAddress.ColumnNumber; } public String ColumnLetter() { - return this.FirstAddressInSheet.ColumnLetter; + return this.RangeAddress.FirstAddress.ColumnLetter; } public new void InsertColumnsAfter( Int32 numberOfColumns) @@ -191,5 +192,21 @@ { return Range(1, 1, XLWorksheet.MaxNumberOfRows, 1); } + + public void AdjustToContents() + { + Double maxWidth = 0; + foreach (var c in CellsUsed()) + { + var thisWidth = ((XLFont)c.Style.Font).GetWidth(c.GetFormattedString()); + if (thisWidth > maxWidth) + maxWidth = thisWidth; + } + + if (maxWidth == 0) + maxWidth = Worksheet.ColumnWidth; + + Width = maxWidth; + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs index 4a5a951..15733c6 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs @@ -53,7 +53,7 @@ foreach (var col in columns) { col.Style = value; - foreach (var c in col.Worksheet.Internals.CellsCollection.Values.Where(c => c.Address.ColumnNumber == col.FirstAddressInSheet.ColumnNumber)) + foreach (var c in col.Worksheet.Internals.CellsCollection.Values.Where(c => c.Address.ColumnNumber == col.RangeAddress.FirstAddress.ColumnNumber)) { c.Style = value; } @@ -85,7 +85,7 @@ foreach (var col in columns) { yield return col.Style; - foreach (var c in col.Worksheet.Internals.CellsCollection.Values.Where(c => c.Address.ColumnNumber == col.FirstAddressInSheet.ColumnNumber)) + foreach (var c in col.Worksheet.Internals.CellsCollection.Values.Where(c => c.Address.ColumnNumber == col.RangeAddress.FirstAddress.ColumnNumber)) { yield return c.Style; } @@ -140,5 +140,10 @@ { columns.Add(column); } + + public void AdjustToContents() + { + columns.ForEach(c => c.AdjustToContents()); + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLAddress.cs index ba6b523..44525d5 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLAddress.cs @@ -11,9 +11,4 @@ Int32 ColumnNumber { get; } String ColumnLetter { get; } } - - public static class IXLAddressMethods - { - - } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeAddress.cs new file mode 100644 index 0000000..6c720af --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeAddress.cs @@ -0,0 +1,14 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public interface IXLRangeAddress + { + IXLAddress FirstAddress { get; set; } + IXLAddress LastAddress { get; set; } + Boolean IsInvalid { get; set; } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs index be2ca1a..dc4b760 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -9,12 +9,12 @@ { IEnumerable Cells(); IEnumerable CellsUsed(); - IXLAddress FirstAddressInSheet { get; } - IXLAddress LastAddressInSheet { get; } + IXLRangeAddress RangeAddress { get; } IXLCell FirstCell(); IXLCell FirstCellUsed(Boolean ignoreStyle = true); IXLCell LastCell(); IXLCell LastCellUsed(Boolean ignoreStyle = true); + IXLRange Range(IXLRangeAddress rangeAddress); IXLRange Range(string rangeAddress); IXLRange Range(string firstCellAddress, string lastCellAddress); IXLRange Range(IXLAddress firstCellAddress, IXLAddress lastCellAddress); @@ -23,5 +23,6 @@ void Unmerge(); void Merge(); IXLRange AsRange(); + Boolean ContainsRange(String rangeAddress); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs index 784ec11..1d5e3d9 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs @@ -8,41 +8,71 @@ { internal class XLRange: XLRangeBase, IXLRange { - public XLRange(XLRangeParameters xlRangeParameters) + public XLRange(XLRangeParameters xlRangeParameters): base(xlRangeParameters.RangeAddress) { - FirstAddressInSheet = xlRangeParameters.FirstCellAddress; - LastAddressInSheet = xlRangeParameters.LastCellAddress; Worksheet = xlRangeParameters.Worksheet; Worksheet.RangeShiftedRows += new RangeShiftedRowsDelegate(Worksheet_RangeShiftedRows); Worksheet.RangeShiftedColumns += new RangeShiftedColumnsDelegate(Worksheet_RangeShiftedColumns); - //Worksheet.Internals.RowsCollection.RowShifted += new RowShiftedDelegate(RowsCollection_RowShifted); - //Worksheet.Internals.ColumnsCollection.ColumnShifted += new ColumnShiftedDelegate(ColumnsCollection_ColumnShifted); this.defaultStyle = new XLStyle(this, xlRangeParameters.DefaultStyle); } void Worksheet_RangeShiftedColumns(XLRange range, int columnsShifted) { - if (range.FirstAddressInSheet.RowNumber <= FirstAddressInSheet.RowNumber - && range.LastAddressInSheet.RowNumber >= LastAddressInSheet.RowNumber) + if (!RangeAddress.IsInvalid && !range.RangeAddress.IsInvalid) { - if (range.FirstAddressInSheet.ColumnNumber <= FirstAddressInSheet.ColumnNumber) - FirstAddressInSheet = new XLAddress(FirstAddressInSheet.RowNumber, FirstAddressInSheet.ColumnNumber + columnsShifted); + if (columnsShifted < 0 + // all columns + && RangeAddress.FirstAddress.ColumnNumber >= range.RangeAddress.FirstAddress.ColumnNumber + && RangeAddress.LastAddress.ColumnNumber <= range.RangeAddress.FirstAddress.ColumnNumber - columnsShifted + // all rows + && RangeAddress.FirstAddress.RowNumber >= range.RangeAddress.FirstAddress.RowNumber + && RangeAddress.LastAddress.RowNumber <= range.RangeAddress.LastAddress.RowNumber + ) + { + ((XLRangeAddress)RangeAddress).IsInvalid = true; + } + else + { + if (range.RangeAddress.FirstAddress.RowNumber <= RangeAddress.FirstAddress.RowNumber + && range.RangeAddress.LastAddress.RowNumber >= RangeAddress.LastAddress.RowNumber) + { + if (range.RangeAddress.FirstAddress.ColumnNumber <= RangeAddress.FirstAddress.ColumnNumber) + RangeAddress.FirstAddress = new XLAddress(RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber + columnsShifted); - if (range.FirstAddressInSheet.ColumnNumber <= LastAddressInSheet.ColumnNumber) - LastAddressInSheet = new XLAddress(LastAddressInSheet.RowNumber, LastAddressInSheet.ColumnNumber + columnsShifted); + if (range.RangeAddress.FirstAddress.ColumnNumber <= RangeAddress.LastAddress.ColumnNumber) + RangeAddress.LastAddress = new XLAddress(RangeAddress.LastAddress.RowNumber, RangeAddress.LastAddress.ColumnNumber + columnsShifted); + } + } } } void Worksheet_RangeShiftedRows(XLRange range, int rowsShifted) { - if (range.FirstAddressInSheet.ColumnNumber <= FirstAddressInSheet.ColumnNumber - && range.LastAddressInSheet.ColumnNumber >= LastAddressInSheet.ColumnNumber) + if (!RangeAddress.IsInvalid && !range.RangeAddress.IsInvalid) { - if (range.FirstAddressInSheet.RowNumber <= FirstAddressInSheet.RowNumber) - FirstAddressInSheet = new XLAddress(FirstAddressInSheet.RowNumber + rowsShifted, FirstAddressInSheet.ColumnNumber); + if (rowsShifted < 0 + // all columns + && RangeAddress.FirstAddress.ColumnNumber >= range.RangeAddress.FirstAddress.ColumnNumber + && RangeAddress.LastAddress.ColumnNumber <= range.RangeAddress.FirstAddress.ColumnNumber + // all rows + && RangeAddress.FirstAddress.RowNumber >= range.RangeAddress.FirstAddress.RowNumber + && RangeAddress.LastAddress.RowNumber <= range.RangeAddress.LastAddress.RowNumber - rowsShifted + ) + { + ((XLRangeAddress)RangeAddress).IsInvalid = true; + } + else + { + if (range.RangeAddress.FirstAddress.ColumnNumber <= RangeAddress.FirstAddress.ColumnNumber + && range.RangeAddress.LastAddress.ColumnNumber >= RangeAddress.LastAddress.ColumnNumber) + { + if (range.RangeAddress.FirstAddress.RowNumber <= RangeAddress.FirstAddress.RowNumber) + RangeAddress.FirstAddress = new XLAddress(RangeAddress.FirstAddress.RowNumber + rowsShifted, RangeAddress.FirstAddress.ColumnNumber); - if (range.FirstAddressInSheet.RowNumber <= LastAddressInSheet.RowNumber) - LastAddressInSheet = new XLAddress(LastAddressInSheet.RowNumber + rowsShifted, LastAddressInSheet.ColumnNumber); + if (range.RangeAddress.FirstAddress.RowNumber <= RangeAddress.LastAddress.RowNumber) + RangeAddress.LastAddress = new XLAddress(RangeAddress.LastAddress.RowNumber + rowsShifted, RangeAddress.LastAddress.ColumnNumber); + } + } } } @@ -58,7 +88,7 @@ } public IXLRangeColumn FirstColumnUsed() { - var firstColumn = this.FirstAddressInSheet.ColumnNumber; + var firstColumn = this.RangeAddress.FirstAddress.ColumnNumber; var columnCount = this.ColumnCount(); Int32 minColumnUsed = Int32.MaxValue; Int32 minColumnInCells = Int32.MaxValue; @@ -80,7 +110,7 @@ } public IXLRangeColumn LastColumnUsed() { - var firstColumn = this.FirstAddressInSheet.ColumnNumber; + var firstColumn = this.RangeAddress.FirstAddress.ColumnNumber; var columnCount = this.ColumnCount(); Int32 maxColumnUsed = 0; Int32 maxColumnInCells = 0; @@ -111,7 +141,7 @@ } public IXLRangeRow FirstRowUsed() { - var firstRow = this.FirstAddressInSheet.RowNumber; + var firstRow = this.RangeAddress.FirstAddress.RowNumber; var rowCount = this.RowCount(); Int32 minRowUsed = Int32.MaxValue; Int32 minRowInCells = Int32.MaxValue; @@ -133,7 +163,7 @@ } public IXLRangeRow LastRowUsed() { - var firstRow = this.FirstAddressInSheet.RowNumber; + var firstRow = this.RangeAddress.FirstAddress.RowNumber; var rowCount = this.RowCount(); Int32 maxRowUsed = 0; Int32 maxRowInCells = 0; @@ -156,24 +186,20 @@ public IXLRangeRow Row(Int32 row) { - IXLAddress firstCellAddress = new XLAddress(FirstAddressInSheet.RowNumber + row - 1, FirstAddressInSheet.ColumnNumber); - IXLAddress lastCellAddress = new XLAddress(FirstAddressInSheet.RowNumber + row - 1, LastAddressInSheet.ColumnNumber); + IXLAddress firstCellAddress = new XLAddress(RangeAddress.FirstAddress.RowNumber + row - 1, RangeAddress.FirstAddress.ColumnNumber); + IXLAddress lastCellAddress = new XLAddress(RangeAddress.FirstAddress.RowNumber + row - 1, RangeAddress.LastAddress.ColumnNumber); return new XLRangeRow( - new XLRangeParameters( - firstCellAddress, - lastCellAddress, + new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet, Worksheet.Style)); } public IXLRangeColumn Column(Int32 column) { - IXLAddress firstCellAddress = new XLAddress(FirstAddressInSheet.RowNumber, FirstAddressInSheet.ColumnNumber + column - 1); - IXLAddress lastCellAddress = new XLAddress(LastAddressInSheet.RowNumber, FirstAddressInSheet.ColumnNumber + column - 1); + IXLAddress firstCellAddress = new XLAddress(RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber + column - 1); + IXLAddress lastCellAddress = new XLAddress(RangeAddress.LastAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber + column - 1); return new XLRangeColumn( - new XLRangeParameters( - firstCellAddress, - lastCellAddress, + new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet, Worksheet.Style)); } @@ -298,25 +324,25 @@ MoveOrClearForTranspose(transposeOption, rowCount, columnCount); TransposeMerged(); TransposeRange(squareSide); - this.LastAddressInSheet = new XLAddress( + this.RangeAddress.LastAddress = new XLAddress( firstCell.Address.RowNumber + columnCount - 1, firstCell.Address.ColumnNumber + rowCount - 1); if (rowCount > columnCount) { var rng = Worksheet.Range( - this.LastAddressInSheet.RowNumber + 1, - this.FirstAddressInSheet.ColumnNumber, - this.LastAddressInSheet.RowNumber + (rowCount - columnCount), - this.LastAddressInSheet.ColumnNumber); + this.RangeAddress.LastAddress.RowNumber + 1, + this.RangeAddress.FirstAddress.ColumnNumber, + this.RangeAddress.LastAddress.RowNumber + (rowCount - columnCount), + this.RangeAddress.LastAddress.ColumnNumber); rng.Delete(XLShiftDeletedCells.ShiftCellsUp); } else if (columnCount > rowCount) { var rng = Worksheet.Range( - this.FirstAddressInSheet.RowNumber, - this.LastAddressInSheet.ColumnNumber + 1, - this.LastAddressInSheet.RowNumber, - this.LastAddressInSheet.ColumnNumber + (columnCount - rowCount)); + this.RangeAddress.FirstAddress.RowNumber, + this.RangeAddress.LastAddress.ColumnNumber + 1, + this.RangeAddress.LastAddress.RowNumber, + this.RangeAddress.LastAddress.ColumnNumber + (columnCount - rowCount)); rng.Delete(XLShiftDeletedCells.ShiftCellsLeft); } @@ -339,10 +365,10 @@ var cellsToInsert = new Dictionary(); var cellsToDelete = new List(); XLRange rngToTranspose = (XLRange)Worksheet.Range( - this.FirstAddressInSheet.RowNumber, - this.FirstAddressInSheet.ColumnNumber, - this.FirstAddressInSheet.RowNumber + squareSide, - this.FirstAddressInSheet.ColumnNumber + squareSide); + this.RangeAddress.FirstAddress.RowNumber, + this.RangeAddress.FirstAddress.ColumnNumber, + this.RangeAddress.FirstAddress.RowNumber + squareSide, + this.RangeAddress.FirstAddress.ColumnNumber + squareSide); foreach (var c in rngToTranspose.Cells()) { @@ -396,9 +422,9 @@ { var toMove = columnCount - rowCount; var rngToClear = Worksheet.Range( - this.FirstAddressInSheet.RowNumber, + this.RangeAddress.FirstAddress.RowNumber, columnCount + 1, - this.LastAddressInSheet.RowNumber, + this.RangeAddress.LastAddress.RowNumber, columnCount + toMove); rngToClear.Clear(); } @@ -407,33 +433,13 @@ var toMove = rowCount - columnCount; var rngToClear = Worksheet.Range( rowCount + 1, - this.FirstAddressInSheet.ColumnNumber, + this.RangeAddress.FirstAddress.ColumnNumber, rowCount + toMove, - this.LastAddressInSheet.ColumnNumber); + this.RangeAddress.LastAddress.ColumnNumber); rngToClear.Clear(); } } } - - public Boolean ContainsRange(String rangeAddress) - { - XLAddress firstAddress; - XLAddress lastAddress; - if (rangeAddress.Contains(':')) - { - String[] arrRange = rangeAddress.Split(':'); - firstAddress = new XLAddress(arrRange[0]); - lastAddress = new XLAddress(arrRange[1]); - } - else - { - firstAddress = new XLAddress(rangeAddress); - lastAddress = new XLAddress(rangeAddress); - } - return - firstAddress >= (XLAddress)this.FirstAddressInSheet - && lastAddress <= (XLAddress)this.LastAddressInSheet; - } #endregion } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs new file mode 100644 index 0000000..9ae76d5 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs @@ -0,0 +1,77 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal class XLRangeAddress: IXLRangeAddress + { + private IXLAddress firstAddress; + public IXLAddress FirstAddress + { + get + { + if (IsInvalid) + throw new Exception("Range is invalid."); + + return firstAddress; + } + set { firstAddress = value; } + } + + private IXLAddress lastAddress; + public IXLAddress LastAddress + { + get + { + if (IsInvalid) + throw new Exception("Range is invalid."); + + return lastAddress; + } + set { lastAddress = value; } + } + + public Boolean IsInvalid { get; set; } + + public XLRangeAddress(String firstCellAddress, String lastCellAddress) + { + FirstAddress = new XLAddress(firstCellAddress); + LastAddress = new XLAddress(lastCellAddress); + } + + public XLRangeAddress(Int32 firstCellRow, Int32 firstCellColumn, Int32 lastCellRow, Int32 lastCellColumn) + { + FirstAddress = new XLAddress(firstCellRow, firstCellColumn); + LastAddress = new XLAddress(lastCellRow, lastCellColumn); + } + + public XLRangeAddress(IXLAddress firstAddress, IXLAddress lastAddress) + { + FirstAddress = firstAddress; + LastAddress = lastAddress; + } + + public XLRangeAddress(String rangeAddress) + { + XLAddress firstAddress; + XLAddress lastAddress; + if (rangeAddress.Contains(':')) + { + String[] arrRange = rangeAddress.Split(':'); + var firstPart = arrRange[0]; + var secondPart = arrRange[1]; + firstAddress = new XLAddress(firstPart); + lastAddress = new XLAddress(secondPart); + } + else + { + firstAddress = new XLAddress(rangeAddress); + lastAddress = new XLAddress(rangeAddress); + } + FirstAddress = firstAddress; + LastAddress = lastAddress; + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index 3d902a5..12cf0a1 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -7,9 +7,13 @@ { internal abstract class XLRangeBase: IXLRangeBase { + public XLRangeBase(IXLRangeAddress rangeAddress) + { + RangeAddress = rangeAddress; + } + protected IXLStyle defaultStyle; - public IXLAddress FirstAddressInSheet { get; protected set; } - public IXLAddress LastAddressInSheet { get; protected set; } + public IXLRangeAddress RangeAddress { get; protected set; } internal XLWorksheet Worksheet { get; set; } public IXLCell FirstCell() @@ -62,7 +66,7 @@ } public IXLCell Cell(IXLAddress cellAddressInRange) { - IXLAddress absoluteAddress = (XLAddress)cellAddressInRange + (XLAddress)this.FirstAddressInSheet - 1; + IXLAddress absoluteAddress = (XLAddress)cellAddressInRange + (XLAddress)this.RangeAddress.FirstAddress - 1; if (this.Worksheet.Internals.CellsCollection.ContainsKey(absoluteAddress)) { return this.Worksheet.Internals.CellsCollection[absoluteAddress]; @@ -87,45 +91,46 @@ public Int32 RowCount() { - return this.LastAddressInSheet.RowNumber - this.FirstAddressInSheet.RowNumber + 1; + return this.RangeAddress.LastAddress.RowNumber - this.RangeAddress.FirstAddress.RowNumber + 1; } public Int32 ColumnCount() { - return this.LastAddressInSheet.ColumnNumber - this.FirstAddressInSheet.ColumnNumber + 1; + return this.RangeAddress.LastAddress.ColumnNumber - this.RangeAddress.FirstAddress.ColumnNumber + 1; } - public IXLRange Range( String rangeAddress) + public IXLRange Range(String rangeAddressStr) { - if (rangeAddress.Contains(':')) - { - String[] arrRange = rangeAddress.Split(':'); - return this.Range(arrRange[0], arrRange[1]); - } - else - { - return this.Range(rangeAddress, rangeAddress); - } + var rangeAddress = new XLRangeAddress(rangeAddressStr); + return Range(rangeAddress); } - public IXLRange Range( String firstCellAddress, String lastCellAddress) + public IXLRange Range(String firstCellAddress, String lastCellAddress) { - return this.Range(new XLAddress(firstCellAddress), new XLAddress(lastCellAddress)); + var rangeAddress = new XLRangeAddress(firstCellAddress, lastCellAddress); + return Range(rangeAddress); } public IXLRange Range(Int32 firstCellRow, Int32 firstCellColumn, Int32 lastCellRow, Int32 lastCellColumn) { - return this.Range(new XLAddress(firstCellRow, firstCellColumn), new XLAddress(lastCellRow, lastCellColumn)); + var rangeAddress = new XLRangeAddress(firstCellRow, firstCellColumn, lastCellRow, lastCellColumn); + return Range(rangeAddress); } - public IXLRange Range( IXLAddress firstCellAddress, IXLAddress lastCellAddress) + public IXLRange Range(IXLAddress firstCellAddress, IXLAddress lastCellAddress) { - var newFirstCellAddress = (XLAddress)firstCellAddress + (XLAddress)this.FirstAddressInSheet - 1; - var newLastCellAddress = (XLAddress)lastCellAddress + (XLAddress)this.FirstAddressInSheet - 1; - var xlRangeParameters = new XLRangeParameters(newFirstCellAddress, newLastCellAddress, this.Worksheet, this.Style); + var rangeAddress = new XLRangeAddress(firstCellAddress, lastCellAddress); + return Range(rangeAddress); + } + public IXLRange Range(IXLRangeAddress rangeAddress) + { + var newFirstCellAddress = (XLAddress)rangeAddress.FirstAddress + (XLAddress)this.RangeAddress.FirstAddress - 1; + var newLastCellAddress = (XLAddress)rangeAddress.LastAddress + (XLAddress)this.RangeAddress.FirstAddress - 1; + var newRangeAddress = new XLRangeAddress(newFirstCellAddress, newLastCellAddress); + var xlRangeParameters = new XLRangeParameters(newRangeAddress, this.Worksheet, this.Style); if ( - newFirstCellAddress.RowNumber < this.FirstAddressInSheet.RowNumber - || newFirstCellAddress.RowNumber > this.LastAddressInSheet.RowNumber - || newLastCellAddress.RowNumber > this.LastAddressInSheet.RowNumber - || newFirstCellAddress.ColumnNumber < this.FirstAddressInSheet.ColumnNumber - || newFirstCellAddress.ColumnNumber > this.LastAddressInSheet.ColumnNumber - || newLastCellAddress.ColumnNumber > this.LastAddressInSheet.ColumnNumber + newFirstCellAddress.RowNumber < this.RangeAddress.FirstAddress.RowNumber + || newFirstCellAddress.RowNumber > this.RangeAddress.LastAddress.RowNumber + || newLastCellAddress.RowNumber > this.RangeAddress.LastAddress.RowNumber + || newFirstCellAddress.ColumnNumber < this.RangeAddress.FirstAddress.ColumnNumber + || newFirstCellAddress.ColumnNumber > this.RangeAddress.LastAddress.ColumnNumber + || newLastCellAddress.ColumnNumber > this.RangeAddress.LastAddress.ColumnNumber ) throw new ArgumentOutOfRangeException(String.Format("The cells {0} and {1} are outside the range '{2}'.", newFirstCellAddress.ToString(), newLastCellAddress.ToString(), this.ToString())); @@ -164,18 +169,18 @@ } public IEnumerable CellsUsed() { - return this.Worksheet.Internals.CellsCollection.Values.AsEnumerable(); + return this.Worksheet.Internals.CellsCollection.Where(c => this.ContainsRange(c.Key.ToString())).Select(c => c.Value).AsEnumerable(); } public void Merge() { - var mergeRange = this.FirstAddressInSheet.ToString() + ":" + this.LastAddressInSheet.ToString(); + var mergeRange = this.RangeAddress.FirstAddress.ToString() + ":" + this.RangeAddress.LastAddress.ToString(); if (!this.Worksheet.Internals.MergedCells.Contains(mergeRange)) this.Worksheet.Internals.MergedCells.Add(mergeRange); } public void Unmerge() { - this.Worksheet.Internals.MergedCells.Remove(this.FirstAddressInSheet.ToString() + ":" + this.LastAddressInSheet.ToString()); + this.Worksheet.Internals.MergedCells.Remove(this.RangeAddress.FirstAddress.ToString() + ":" + this.RangeAddress.LastAddress.ToString()); } @@ -186,12 +191,12 @@ public void InsertColumnsAfter(Int32 numberOfColumns, Boolean onlyUsedCells) { var columnCount = this.ColumnCount(); - var firstColumn = this.FirstAddressInSheet.ColumnNumber + columnCount; + var firstColumn = this.RangeAddress.FirstAddress.ColumnNumber + columnCount; if (firstColumn > XLWorksheet.MaxNumberOfColumns) firstColumn = XLWorksheet.MaxNumberOfColumns; var lastColumn = firstColumn + this.ColumnCount() - 1; if (lastColumn > XLWorksheet.MaxNumberOfColumns) lastColumn = XLWorksheet.MaxNumberOfColumns; - var firstRow = this.FirstAddressInSheet.RowNumber; + var firstRow = this.RangeAddress.FirstAddress.RowNumber; var lastRow = firstRow + this.RowCount() - 1; if (lastRow > XLWorksheet.MaxNumberOfRows) lastRow = XLWorksheet.MaxNumberOfRows; @@ -207,9 +212,9 @@ var cellsToInsert = new Dictionary(); var cellsToDelete = new List(); var cellsToBlank = new List(); - var firstColumn = this.FirstAddressInSheet.ColumnNumber; - var firstRow = this.FirstAddressInSheet.RowNumber; - var lastRow = this.FirstAddressInSheet.RowNumber + this.RowCount() - 1; + var firstColumn = this.RangeAddress.FirstAddress.ColumnNumber; + var firstRow = this.RangeAddress.FirstAddress.RowNumber; + var lastRow = this.RangeAddress.FirstAddress.RowNumber + this.RowCount() - 1; if (!onlyUsedCells) { @@ -282,12 +287,12 @@ public void InsertRowsBelow(Int32 numberOfRows, Boolean onlyUsedCells) { var rowCount = this.RowCount(); - var firstRow = this.FirstAddressInSheet.RowNumber + rowCount; + var firstRow = this.RangeAddress.FirstAddress.RowNumber + rowCount; if (firstRow > XLWorksheet.MaxNumberOfRows) firstRow = XLWorksheet.MaxNumberOfRows; var lastRow = firstRow + this.RowCount() - 1; if (lastRow > XLWorksheet.MaxNumberOfRows) lastRow = XLWorksheet.MaxNumberOfRows; - var firstColumn = this.FirstAddressInSheet.ColumnNumber; + var firstColumn = this.RangeAddress.FirstAddress.ColumnNumber; var lastColumn = firstColumn + this.ColumnCount() - 1; if (lastColumn > XLWorksheet.MaxNumberOfColumns) lastColumn = XLWorksheet.MaxNumberOfColumns; @@ -303,9 +308,9 @@ var cellsToInsert = new Dictionary(); var cellsToDelete = new List(); var cellsToBlank = new List(); - var firstRow = this.FirstAddressInSheet.RowNumber; - var firstColumn = this.FirstAddressInSheet.ColumnNumber; - var lastColumn = this.FirstAddressInSheet.ColumnNumber + this.ColumnCount() - 1; + var firstRow = this.RangeAddress.FirstAddress.RowNumber; + var firstColumn = this.RangeAddress.FirstAddress.ColumnNumber; + var lastColumn = this.RangeAddress.FirstAddress.ColumnNumber + this.ColumnCount() - 1; if (!onlyUsedCells) { @@ -367,7 +372,6 @@ styleToUse = this.Worksheet.Style; this.Worksheet.Cell(c).Style = styleToUse; } - Worksheet.NotifyRangeShiftedRows((XLRange)this.AsRange(), numberOfRows); } @@ -375,12 +379,56 @@ { // Remove cells inside range this.Worksheet.Internals.CellsCollection.RemoveAll(c => - c.Address.ColumnNumber >= this.FirstAddressInSheet.ColumnNumber - && c.Address.ColumnNumber <= this.LastAddressInSheet.ColumnNumber - && c.Address.RowNumber >= this.FirstAddressInSheet.RowNumber - && c.Address.RowNumber <= this.LastAddressInSheet.RowNumber + c.Address.ColumnNumber >= this.RangeAddress.FirstAddress.ColumnNumber + && c.Address.ColumnNumber <= this.RangeAddress.LastAddress.ColumnNumber + && c.Address.RowNumber >= this.RangeAddress.FirstAddress.RowNumber + && c.Address.RowNumber <= this.RangeAddress.LastAddress.RowNumber ); + + ClearMerged(); } + + private void ClearMerged() + { + List mergeToDelete = new List(); + foreach (var merge in Worksheet.Internals.MergedCells) + { + var ma = new XLRangeAddress(merge); + var ra = RangeAddress; + + if (!( // See if the two ranges intersect... + ma.FirstAddress.ColumnNumber > ra.LastAddress.ColumnNumber + || ma.LastAddress.ColumnNumber < ra.FirstAddress.ColumnNumber + || ma.FirstAddress.RowNumber > ra.LastAddress.RowNumber + || ma.LastAddress.RowNumber < ra.FirstAddress.RowNumber + )) + { + mergeToDelete.Add(merge); + } + } + mergeToDelete.ForEach(m => this.Worksheet.Internals.MergedCells.Remove(m)); + } + + public Boolean ContainsRange(String rangeAddress) + { + XLAddress firstAddress; + XLAddress lastAddress; + if (rangeAddress.Contains(':')) + { + String[] arrRange = rangeAddress.Split(':'); + firstAddress = new XLAddress(arrRange[0]); + lastAddress = new XLAddress(arrRange[1]); + } + else + { + firstAddress = new XLAddress(rangeAddress); + lastAddress = new XLAddress(rangeAddress); + } + return + firstAddress >= (XLAddress)this.RangeAddress.FirstAddress + && lastAddress <= (XLAddress)this.RangeAddress.LastAddress; + } + public void Delete(XLShiftDeletedCells shiftDeleteCells) { //this.Clear(); @@ -390,15 +438,15 @@ var cellsToDelete = new List(); var shiftLeftQuery = this.Worksheet.Internals.CellsCollection .Where(c => - c.Key.RowNumber >= this.FirstAddressInSheet.RowNumber - && c.Key.RowNumber <= this.LastAddressInSheet.RowNumber - && c.Key.ColumnNumber >= this.FirstAddressInSheet.ColumnNumber); + c.Key.RowNumber >= this.RangeAddress.FirstAddress.RowNumber + && c.Key.RowNumber <= this.RangeAddress.LastAddress.RowNumber + && c.Key.ColumnNumber >= this.RangeAddress.FirstAddress.ColumnNumber); var shiftUpQuery = this.Worksheet.Internals.CellsCollection .Where(c => - c.Key.ColumnNumber >= this.FirstAddressInSheet.ColumnNumber - && c.Key.ColumnNumber <= this.LastAddressInSheet.ColumnNumber - && c.Key.RowNumber >= this.FirstAddressInSheet.RowNumber); + c.Key.ColumnNumber >= this.RangeAddress.FirstAddress.ColumnNumber + && c.Key.ColumnNumber <= this.RangeAddress.LastAddress.ColumnNumber + && c.Key.RowNumber >= this.RangeAddress.FirstAddress.RowNumber); var columnModifier = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsLeft ? this.ColumnCount() : 0; var rowModifier = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp ? this.RowCount() : 0; @@ -412,21 +460,22 @@ cellsToDelete.Add(c.Key); var canInsert = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsLeft ? - c.Key.ColumnNumber > this.LastAddressInSheet.ColumnNumber : - c.Key.RowNumber > this.LastAddressInSheet.RowNumber; + c.Key.ColumnNumber > this.RangeAddress.LastAddress.ColumnNumber : + c.Key.RowNumber > this.RangeAddress.LastAddress.RowNumber; if (canInsert) cellsToInsert.Add(newKey, newCell); } cellsToDelete.ForEach(c => this.Worksheet.Internals.CellsCollection.Remove(c)); cellsToInsert.ForEach(c => this.Worksheet.Internals.CellsCollection.Add(c.Key, c.Value)); + var shiftedRange = (XLRange)this.AsRange(); if (shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp) { - Worksheet.NotifyRangeShiftedRows((XLRange)this.AsRange(), rowModifier * -1); + Worksheet.NotifyRangeShiftedRows(shiftedRange, rowModifier * -1); } else { - Worksheet.NotifyRangeShiftedColumns((XLRange)this.AsRange(), columnModifier * -1); + Worksheet.NotifyRangeShiftedColumns(shiftedRange, columnModifier * -1); } } @@ -463,12 +512,12 @@ public virtual IXLRange AsRange() { - return Worksheet.Range(FirstAddressInSheet, LastAddressInSheet); + return Worksheet.Range(RangeAddress.FirstAddress, RangeAddress.LastAddress); } public override string ToString() { - return FirstAddressInSheet.ToString() + ":" + LastAddressInSheet.ToString(); + return RangeAddress.FirstAddress.ToString() + ":" + RangeAddress.LastAddress.ToString(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs index cb064d5..26fc3d8 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs @@ -9,9 +9,8 @@ internal class XLRangeColumn: XLRangeBase, IXLRangeColumn { public XLRangeColumn(XLRangeParameters xlRangeParameters) + : base(xlRangeParameters.RangeAddress) { - FirstAddressInSheet = xlRangeParameters.FirstCellAddress; - LastAddressInSheet = xlRangeParameters.LastCellAddress; Worksheet = xlRangeParameters.Worksheet; Worksheet.RangeShiftedRows += new RangeShiftedRowsDelegate(Worksheet_RangeShiftedRows); Worksheet.RangeShiftedColumns += new RangeShiftedColumnsDelegate(Worksheet_RangeShiftedColumns); @@ -20,26 +19,26 @@ void Worksheet_RangeShiftedColumns(XLRange range, int columnsShifted) { - if (range.FirstAddressInSheet.RowNumber <= FirstAddressInSheet.RowNumber - && range.LastAddressInSheet.RowNumber >= LastAddressInSheet.RowNumber) + if (range.RangeAddress.FirstAddress.RowNumber <= RangeAddress.FirstAddress.RowNumber + && range.RangeAddress.LastAddress.RowNumber >= RangeAddress.LastAddress.RowNumber) { - if (range.FirstAddressInSheet.ColumnNumber <= FirstAddressInSheet.ColumnNumber) - FirstAddressInSheet = new XLAddress(FirstAddressInSheet.RowNumber, FirstAddressInSheet.ColumnNumber + columnsShifted); + if (range.RangeAddress.FirstAddress.ColumnNumber <= RangeAddress.FirstAddress.ColumnNumber) + RangeAddress.FirstAddress = new XLAddress(RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber + columnsShifted); - if (range.FirstAddressInSheet.ColumnNumber <= LastAddressInSheet.ColumnNumber) - LastAddressInSheet = new XLAddress(LastAddressInSheet.RowNumber, LastAddressInSheet.ColumnNumber + columnsShifted); + if (range.RangeAddress.FirstAddress.ColumnNumber <= RangeAddress.LastAddress.ColumnNumber) + RangeAddress.LastAddress = new XLAddress(RangeAddress.LastAddress.RowNumber, RangeAddress.LastAddress.ColumnNumber + columnsShifted); } } void Worksheet_RangeShiftedRows(XLRange range, int rowsShifted) { - if (range.FirstAddressInSheet.ColumnNumber <= FirstAddressInSheet.ColumnNumber - && range.LastAddressInSheet.ColumnNumber >= LastAddressInSheet.ColumnNumber) + if (range.RangeAddress.FirstAddress.ColumnNumber <= RangeAddress.FirstAddress.ColumnNumber + && range.RangeAddress.LastAddress.ColumnNumber >= RangeAddress.LastAddress.ColumnNumber) { - if (range.FirstAddressInSheet.RowNumber <= FirstAddressInSheet.RowNumber) - FirstAddressInSheet = new XLAddress(FirstAddressInSheet.RowNumber + rowsShifted, FirstAddressInSheet.ColumnNumber); + if (range.RangeAddress.FirstAddress.RowNumber <= RangeAddress.FirstAddress.RowNumber) + RangeAddress.FirstAddress = new XLAddress(RangeAddress.FirstAddress.RowNumber + rowsShifted, RangeAddress.FirstAddress.ColumnNumber); - if (range.FirstAddressInSheet.RowNumber <= LastAddressInSheet.RowNumber) - LastAddressInSheet = new XLAddress(LastAddressInSheet.RowNumber + rowsShifted, LastAddressInSheet.ColumnNumber); + if (range.RangeAddress.FirstAddress.RowNumber <= RangeAddress.LastAddress.RowNumber) + RangeAddress.LastAddress = new XLAddress(RangeAddress.LastAddress.RowNumber + rowsShifted, RangeAddress.LastAddress.ColumnNumber); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs index 7b5dca6..273f634 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs @@ -60,10 +60,10 @@ { yield return rng.Style; foreach (var r in rng.Worksheet.Internals.CellsCollection.Values.Where(c => - c.Address.RowNumber >= rng.FirstAddressInSheet.RowNumber - && c.Address.RowNumber <= rng.LastAddressInSheet.RowNumber - && c.Address.ColumnNumber >= rng.FirstAddressInSheet.ColumnNumber - && c.Address.ColumnNumber <= rng.LastAddressInSheet.ColumnNumber + 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; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeParameters.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeParameters.cs index 10e8940..361f6a1 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeParameters.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeParameters.cs @@ -8,18 +8,16 @@ { internal class XLRangeParameters { - public XLRangeParameters(IXLAddress firstCellAddress, IXLAddress lastCellAddress, XLWorksheet worksheet, IXLStyle defaultStyle) + public XLRangeParameters(IXLRangeAddress rangeAddress, XLWorksheet worksheet, IXLStyle defaultStyle) { - FirstCellAddress = firstCellAddress; - LastCellAddress = lastCellAddress; + RangeAddress = rangeAddress; Worksheet = worksheet; DefaultStyle = defaultStyle; } #region Properties // Public - public IXLAddress FirstCellAddress { get; private set; } - public IXLAddress LastCellAddress { get; private set; } + public IXLRangeAddress RangeAddress { get; private set; } public XLWorksheet Worksheet { get; private set; } public IXLStyle DefaultStyle { get; private set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs index 127be51..08a5893 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs @@ -8,10 +8,8 @@ { internal class XLRangeRow: XLRangeBase, IXLRangeRow { - public XLRangeRow(XLRangeParameters xlRangeParameters) + public XLRangeRow(XLRangeParameters xlRangeParameters): base(xlRangeParameters.RangeAddress) { - FirstAddressInSheet = xlRangeParameters.FirstCellAddress; - LastAddressInSheet = xlRangeParameters.LastCellAddress; Worksheet = xlRangeParameters.Worksheet; Worksheet.RangeShiftedRows += new RangeShiftedRowsDelegate(Worksheet_RangeShiftedRows); Worksheet.RangeShiftedColumns += new RangeShiftedColumnsDelegate(Worksheet_RangeShiftedColumns); @@ -20,26 +18,26 @@ void Worksheet_RangeShiftedColumns(XLRange range, int columnsShifted) { - if (range.FirstAddressInSheet.RowNumber <= FirstAddressInSheet.RowNumber - && range.LastAddressInSheet.RowNumber >= LastAddressInSheet.RowNumber) + if (range.RangeAddress.FirstAddress.RowNumber <= RangeAddress.FirstAddress.RowNumber + && range.RangeAddress.LastAddress.RowNumber >= RangeAddress.LastAddress.RowNumber) { - if (range.FirstAddressInSheet.ColumnNumber <= FirstAddressInSheet.ColumnNumber) - FirstAddressInSheet = new XLAddress(FirstAddressInSheet.RowNumber, FirstAddressInSheet.ColumnNumber + columnsShifted); + if (range.RangeAddress.FirstAddress.ColumnNumber <= RangeAddress.FirstAddress.ColumnNumber) + RangeAddress.FirstAddress = new XLAddress(RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber + columnsShifted); - if (range.FirstAddressInSheet.ColumnNumber <= LastAddressInSheet.ColumnNumber) - LastAddressInSheet = new XLAddress(LastAddressInSheet.RowNumber, LastAddressInSheet.ColumnNumber + columnsShifted); + if (range.RangeAddress.FirstAddress.ColumnNumber <= RangeAddress.LastAddress.ColumnNumber) + RangeAddress.LastAddress = new XLAddress(RangeAddress.LastAddress.RowNumber, RangeAddress.LastAddress.ColumnNumber + columnsShifted); } } void Worksheet_RangeShiftedRows(XLRange range, int rowsShifted) { - if (range.FirstAddressInSheet.ColumnNumber <= FirstAddressInSheet.ColumnNumber - && range.LastAddressInSheet.ColumnNumber >= LastAddressInSheet.ColumnNumber) + if (range.RangeAddress.FirstAddress.ColumnNumber <= RangeAddress.FirstAddress.ColumnNumber + && range.RangeAddress.LastAddress.ColumnNumber >= RangeAddress.LastAddress.ColumnNumber) { - if (range.FirstAddressInSheet.RowNumber <= FirstAddressInSheet.RowNumber) - FirstAddressInSheet = new XLAddress(FirstAddressInSheet.RowNumber + rowsShifted, FirstAddressInSheet.ColumnNumber); + if (range.RangeAddress.FirstAddress.RowNumber <= RangeAddress.FirstAddress.RowNumber) + RangeAddress.FirstAddress = new XLAddress(RangeAddress.FirstAddress.RowNumber + rowsShifted, RangeAddress.FirstAddress.ColumnNumber); - if (range.FirstAddressInSheet.RowNumber <= LastAddressInSheet.RowNumber) - LastAddressInSheet = new XLAddress(LastAddressInSheet.RowNumber + rowsShifted, LastAddressInSheet.ColumnNumber); + if (range.RangeAddress.FirstAddress.RowNumber <= RangeAddress.LastAddress.RowNumber) + RangeAddress.LastAddress = new XLAddress(RangeAddress.LastAddress.RowNumber + rowsShifted, RangeAddress.LastAddress.ColumnNumber); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs index 4e72012..e0e71b5 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs @@ -60,10 +60,10 @@ { yield return rng.Style; foreach (var r in rng.Worksheet.Internals.CellsCollection.Values.Where(c => - c.Address.RowNumber >= rng.FirstAddressInSheet.RowNumber - && c.Address.RowNumber <= rng.LastAddressInSheet.RowNumber - && c.Address.ColumnNumber >= rng.FirstAddressInSheet.ColumnNumber - && c.Address.ColumnNumber <= rng.LastAddressInSheet.ColumnNumber + 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; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs index 2fa087f..856ba10 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs @@ -60,10 +60,10 @@ { yield return rng.Style; foreach (var r in rng.Worksheet.Internals.CellsCollection.Values.Where(c => - c.Address.RowNumber >= rng.FirstAddressInSheet.RowNumber - && c.Address.RowNumber <= rng.LastAddressInSheet.RowNumber - && c.Address.ColumnNumber >= rng.FirstAddressInSheet.ColumnNumber - && c.Address.ColumnNumber <= rng.LastAddressInSheet.ColumnNumber + 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; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs index 2192723..e75cfe3 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs @@ -16,5 +16,7 @@ IXLCell Cell(Int32 column); IXLCell Cell(String column); + + void AdjustToContents(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs index 487a718..96f45c5 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs @@ -9,6 +9,6 @@ { Double Height { set; } void Delete(); - //void Add(IXLRow row); + void AdjustToContents(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs index 6be2051..7cc9a1c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs @@ -9,6 +9,7 @@ internal class XLRow: XLRangeBase, IXLRow { public XLRow(Int32 row, XLRowParameters xlRowParameters) + : base(new XLRangeAddress(row, 1, row, XLWorksheet.MaxNumberOfColumns)) { SetRowNumber(row); Worksheet = xlRowParameters.Worksheet; @@ -27,7 +28,7 @@ void Worksheet_RangeShiftedRows(XLRange range, int rowsShifted) { - if (range.FirstAddressInSheet.RowNumber <= this.RowNumber()) + if (range.RangeAddress.FirstAddress.RowNumber <= this.RowNumber()) SetRowNumber(this.RowNumber() + rowsShifted); } @@ -39,8 +40,8 @@ private void SetRowNumber(Int32 row) { - FirstAddressInSheet = new XLAddress(row, 1); - LastAddressInSheet = new XLAddress(row, XLWorksheet.MaxNumberOfColumns); + RangeAddress.FirstAddress = new XLAddress(row, 1); + RangeAddress.LastAddress = new XLAddress(row, XLWorksheet.MaxNumberOfColumns); } public Boolean IsReference { get; private set; } @@ -83,7 +84,7 @@ public Int32 RowNumber() { - return this.FirstAddressInSheet.RowNumber; + return this.RangeAddress.FirstAddress.RowNumber; } public new void InsertRowsBelow(Int32 numberOfRows) @@ -120,6 +121,20 @@ return base.Cell(1, column); } + public void AdjustToContents() + { + Double maxHeight = 0; + var cellsUsed = CellsUsed(); + foreach (var c in cellsUsed) + { + var thisHeight = ((XLFont)c.Style.Font).GetHeight(); + if (thisHeight > maxHeight) + maxHeight = thisHeight; + } + if (maxHeight > 0) + Height = maxHeight; + } + #endregion #region IXLStylized Members diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs index 1332ff9..acdc9d7 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs @@ -54,7 +54,7 @@ foreach (var row in rows) { row.Style = value; - foreach (var c in row.Worksheet.Internals.CellsCollection.Values.Where(c => c.Address.RowNumber == row.FirstAddressInSheet.RowNumber)) + foreach (var c in row.Worksheet.Internals.CellsCollection.Values.Where(c => c.Address.RowNumber == row.RangeAddress.FirstAddress.RowNumber)) { c.Style = value; } @@ -88,7 +88,7 @@ foreach (var row in rows) { yield return row.Style; - foreach (var c in row.Worksheet.Internals.CellsCollection.Values.Where(c => c.Address.RowNumber == row.FirstAddressInSheet.RowNumber)) + foreach (var c in row.Worksheet.Internals.CellsCollection.Values.Where(c => c.Address.RowNumber == row.RangeAddress.FirstAddress.RowNumber)) { yield return c.Style; } @@ -137,5 +137,10 @@ { rows.Add(row); } + + public void AdjustToContents() + { + rows.ForEach(r => r.AdjustToContents()); + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs index a152589..a862ae4 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs @@ -3,6 +3,7 @@ using System.Linq; using System.Text; using System.Drawing; +using System.Windows.Forms; namespace ClosedXML.Excel { @@ -234,5 +235,40 @@ #endregion + public Double GetWidth(String text) + { + if (String.IsNullOrWhiteSpace(text)) + return 0; + + System.Drawing.Font stringFont = new System.Drawing.Font(fontName, (float)fontSize); + return GetWidth(stringFont, text); + } + + private Double GetWidth(System.Drawing.Font stringFont, string text) + { + // This formula is based on this article plus a nudge ( + 0.2M ) + // http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.column.width.aspx + // Truncate(((256 * Solve_For_This + Truncate(128 / 7)) / 256) * 7) = DeterminePixelsOfString + + Size textSize = TextRenderer.MeasureText(text, stringFont); + double width = (double)(((textSize.Width / (double)7) * 256) - (128 / 7)) / 256; + width = (double)decimal.Round((decimal)width + 0.2M, 2); + + return width; + } + + public Double GetHeight() + { + System.Drawing.Font stringFont = new System.Drawing.Font(fontName, (float)fontSize); + return GetHeight(stringFont); + } + + private Double GetHeight(System.Drawing.Font stringFont) + { + Size textSize = TextRenderer.MeasureText("X", stringFont); + var val = (double)textSize.Height * 0.85; + return val; + } + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs index fe05fa9..2abc97c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs @@ -20,6 +20,7 @@ RowHeight = DefaultRowHeight; ColumnWidth = DefaultColumnWidth; PageOptions = DefaultPageOptions; + Properties = new XLWorkbookProperties(); } public XLWorkbook(String file): this() @@ -54,6 +55,7 @@ public Double RowHeight { get; set; } public Double ColumnWidth { get; set; } public IXLPageSetup PageOptions { get; set; } + public XLWorkbookProperties Properties { get; set; } #endregion diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbookProperties.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbookProperties.cs new file mode 100644 index 0000000..8131a83 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbookProperties.cs @@ -0,0 +1,23 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public class XLWorkbookProperties + { + public String Author { get; set; } + public String Title { get; set; } + public String Subject { get; set; } + public String Category { get; set; } + public String Keywords { get; set; } + public String Comments { get; set; } + public String Status { get; set; } + public DateTime Created { get; set; } + public DateTime Modified { get; set; } + public String LastModifiedBy { get; set; } + public String Company { get; set; } + public String Manager { get; set; } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index 0d7ab1c..1860352 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -30,6 +30,7 @@ // Open file as read-only. using (SpreadsheetDocument dSpreadsheet = SpreadsheetDocument.Open(fileName, false)) { + SetProperties(dSpreadsheet); SharedStringItem[] sharedStrings = null; if (dSpreadsheet.WorkbookPart.GetPartsOfType().Count() > 0) { @@ -37,6 +38,12 @@ sharedStrings = shareStringPart.SharedStringTable.Elements().ToArray(); } + if (dSpreadsheet.ExtendedFilePropertiesPart.Properties.Elements().Count() > 0) + Properties.Company = dSpreadsheet.ExtendedFilePropertiesPart.Properties.GetFirstChild().Text; + + if (dSpreadsheet.ExtendedFilePropertiesPart.Properties.Elements().Count() > 0) + Properties.Manager = dSpreadsheet.ExtendedFilePropertiesPart.Properties.GetFirstChild().Text; + var workbookStylesPart = (WorkbookStylesPart)dSpreadsheet.WorkbookPart.WorkbookStylesPart; var s = (Stylesheet)workbookStylesPart.Stylesheet; var numberingFormats = (NumberingFormats)s.NumberingFormats; @@ -278,6 +285,22 @@ } } + private void SetProperties(SpreadsheetDocument dSpreadsheet) + { + var p = dSpreadsheet.PackageProperties; + Properties.Author = p.Creator; + Properties.Category = p.Category; + Properties.Comments = p.Description; + if (p.Created.HasValue) + Properties.Created = p.Created.Value; + Properties.Keywords = p.Keywords; + Properties.LastModifiedBy = p.LastModifiedBy; + Properties.Status = p.ContentStatus; + Properties.Subject = p.Subject; + Properties.Title = p.Title; + + } + private void ApplyStyle(IXLStylized xlStylized, Int32 styleIndex, Stylesheet s, Fills fills, Borders borders, Fonts fonts, NumberingFormats numberingFormats ) { var fillId = ((CellFormat)((CellFormats)s.CellFormats).ElementAt(styleIndex)).FillId.Value; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 944bca2..bfc28c8 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -236,7 +236,7 @@ ThemePart themePart1 = workbookPart.AddNewPart("rId" + (startId + 1)); GenerateThemePartContent(themePart1); - //SetPackageProperties(document); + SetPackageProperties(document); } private void GenerateExtendedFilePropertiesPartContent(ExtendedFilePropertiesPart extendedFilePropertiesPart) @@ -305,8 +305,11 @@ } titlesOfParts1.Append(vTVector2); + Ap.Manager manager1 = new Ap.Manager(); + manager1.Text = Properties.Manager; Ap.Company company1 = new Ap.Company(); - company1.Text = "NSI"; + company1.Text = Properties.Company; + Ap.LinksUpToDate linksUpToDate1 = new Ap.LinksUpToDate(); linksUpToDate1.Text = "false"; Ap.SharedDocument sharedDocument1 = new Ap.SharedDocument(); @@ -321,6 +324,7 @@ properties1.Append(scaleCrop1); properties1.Append(headingPairs1); properties1.Append(titlesOfParts1); + properties1.Append(manager1); properties1.Append(company1); properties1.Append(linksUpToDate1); properties1.Append(sharedDocument1); @@ -365,8 +369,8 @@ foreach (var printArea in worksheet.PageSetup.PrintAreas) { definedNameText += "'" + worksheet.Name + "'!" - + printArea.FirstAddressInSheet.ToString() - + ":" + printArea.LastAddressInSheet.ToString() + ","; + + printArea.RangeAddress.FirstAddress.ToString() + + ":" + printArea.RangeAddress.LastAddress.ToString() + ","; } definedName.Text = definedNameText.Substring(0, definedNameText.Length - 1); @@ -994,7 +998,7 @@ rowBreaks = new RowBreaks() { Count = (UInt32Value)(UInt32)rowBreakCount, ManualBreakCount = (UInt32)rowBreakCount }; foreach (var rb in xlWorksheet.PageSetup.RowBreaks) { - Break break1 = new Break() { Id = (UInt32Value)(UInt32)rb, Max = (UInt32Value)(UInt32)xlWorksheet.LastAddressInSheet.RowNumber, ManualPageBreak = true }; + Break break1 = new Break() { Id = (UInt32Value)(UInt32)rb, Max = (UInt32Value)(UInt32)xlWorksheet.RangeAddress.LastAddress.RowNumber, ManualPageBreak = true }; rowBreaks.Append(break1); } @@ -1007,7 +1011,7 @@ columnBreaks = new ColumnBreaks() { Count = (UInt32Value)(UInt32)columnBreakCount, ManualBreakCount = (UInt32Value)(UInt32)columnBreakCount }; foreach (var cb in xlWorksheet.PageSetup.ColumnBreaks) { - Break break1 = new Break() { Id = (UInt32Value)(UInt32)cb, Max = (UInt32Value)(UInt32)xlWorksheet.LastAddressInSheet.ColumnNumber, ManualPageBreak = true }; + Break break1 = new Break() { Id = (UInt32Value)(UInt32)cb, Max = (UInt32Value)(UInt32)xlWorksheet.RangeAddress.LastAddress.ColumnNumber, ManualPageBreak = true }; columnBreaks.Append(break1); } @@ -1031,55 +1035,6 @@ worksheetPart.Worksheet = worksheet; } - //private void AppendColumns(Columns columns, Int32 startingColumn, IXLWorksheet xlWorksheet) - //{ - // var minUsable = xlWorksheet.Internals.ColumnsCollection.Keys.Where(i => i >= startingColumn).Select(i=>i).Min(); - // if (startingColumn < minUsable) - // { - // Column column = new Column() - // { - // Min = (UInt32Value)(UInt32)startingColumn, - // Max = (UInt32Value)(UInt32)(minUsable - 1), - // Style = sharedStyles[xlWorksheet.Style.ToString()].StyleId, - // Width = xlWorksheet.DefaultColumnWidth, - // CustomWidth = true - // }; - // columns.Append(column); - // } - // else - // { - // var maxInColumnsCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Max(); - // var maxUsable = maxInColumnsCollection; - // for (var co = minUsable + 1; co <= maxInColumnsCollection; co++) - // { - // if (!xlWorksheet.Internals.ColumnsCollection.ContainsKey(co + 1)) - // { - // maxUsable = co; - // break; - // } - // } - - // Column column = new Column() - // { - // Min = (UInt32Value)(UInt32)minUsable, - // Max = (UInt32Value)(UInt32)maxUsable, - // Style = sharedStyles[xlWorksheet.Style.ToString()].StyleId, - // Width = xlWorksheet.DefaultColumnWidth, - // CustomWidth = true - // }; - // columns.Append(column); - // } - // for(var co = startingColumn; co < minUsable; co++) - // { - - // } - - // var maxTotalColumns = XLWorksheet.MaxNumberOfColumns; - - - // //var xlWorksheet.Internals.ColumnsCollection - //} - private void GenerateThemePartContent(ThemePart themePart) { A.Theme theme1 = new A.Theme() { Name = "Office Theme" }; @@ -1647,10 +1602,19 @@ private void SetPackageProperties(OpenXmlPackage document) { - document.PackageProperties.Creator = Environment.UserName; - document.PackageProperties.Created = DateTime.Now; - document.PackageProperties.Modified = DateTime.Now; - document.PackageProperties.LastModifiedBy = Environment.UserName; + var created = Properties.Created == DateTime.MinValue ? DateTime.Now : Properties.Created; + var modified = Properties.Modified == DateTime.MinValue ? DateTime.Now : Properties.Modified; + document.PackageProperties.Created = created; + document.PackageProperties.Modified = modified; + document.PackageProperties.LastModifiedBy = Properties.LastModifiedBy; + + document.PackageProperties.Creator = Properties.Author; + document.PackageProperties.Title = Properties.Title; + document.PackageProperties.Subject = Properties.Subject; + document.PackageProperties.Category = Properties.Category; + document.PackageProperties.Keywords = Properties.Keywords; + document.PackageProperties.Description = Properties.Comments; + document.PackageProperties.ContentStatus = Properties.Status; } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index 48cf2b8..f41d226 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -22,12 +22,11 @@ #endregion public XLWorksheet(String sheetName, XLWorkbook workbook) + : base((IXLRangeAddress)new XLRangeAddress(new XLAddress(1, 1), new XLAddress(MaxNumberOfRows, MaxNumberOfColumns))) { Worksheet = this; Style = workbook.Style; Internals = new XLWorksheetInternals(new Dictionary(), new XLColumnsCollection(), new XLRowsCollection(), new List()); - FirstAddressInSheet = new XLAddress(1, 1); - LastAddressInSheet = new XLAddress(MaxNumberOfRows, MaxNumberOfColumns); PageSetup = new XLPageSetup(workbook.PageOptions, this); ColumnWidth = workbook.ColumnWidth; RowHeight = workbook.RowHeight; @@ -41,19 +40,23 @@ var newMerge = new List(); foreach (var merge in Internals.MergedCells) { - var rng = Range(merge); - if (range.FirstAddressInSheet.ColumnNumber <= rng.FirstAddressInSheet.ColumnNumber) + var rngMerged = Range(merge); + if (range.RangeAddress.FirstAddress.ColumnNumber <= rngMerged.RangeAddress.FirstAddress.ColumnNumber + && rngMerged.RangeAddress.FirstAddress.RowNumber >= range.RangeAddress.FirstAddress.RowNumber + && rngMerged.RangeAddress.LastAddress.RowNumber <= range.RangeAddress.LastAddress.RowNumber) { var newRng = Range( - rng.FirstAddressInSheet.RowNumber, - rng.FirstAddressInSheet.ColumnNumber + columnsShifted, - rng.LastAddressInSheet.RowNumber, - rng.LastAddressInSheet.ColumnNumber + columnsShifted); + rngMerged.RangeAddress.FirstAddress.RowNumber, + rngMerged.RangeAddress.FirstAddress.ColumnNumber + columnsShifted, + rngMerged.RangeAddress.LastAddress.RowNumber, + rngMerged.RangeAddress.LastAddress.ColumnNumber + columnsShifted); newMerge.Add(newRng.ToString()); } - else + else if ( + !(range.RangeAddress.FirstAddress.ColumnNumber <= rngMerged.RangeAddress.FirstAddress.ColumnNumber + && range.RangeAddress.FirstAddress.RowNumber <= rngMerged.RangeAddress.LastAddress.RowNumber)) { - newMerge.Add(rng.ToString()); + newMerge.Add(rngMerged.ToString()); } } Internals.MergedCells = newMerge; @@ -64,19 +67,22 @@ var newMerge = new List(); foreach (var merge in Internals.MergedCells) { - var rng = Range(merge); - if (range.FirstAddressInSheet.RowNumber <= rng.FirstAddressInSheet.RowNumber) + var rngMerged = Range(merge); + if (range.RangeAddress.FirstAddress.RowNumber <= rngMerged.RangeAddress.FirstAddress.RowNumber + && rngMerged.RangeAddress.FirstAddress.ColumnNumber >= range.RangeAddress.FirstAddress.ColumnNumber + && rngMerged.RangeAddress.LastAddress.ColumnNumber <= range.RangeAddress.LastAddress.ColumnNumber) { var newRng = Range( - rng.FirstAddressInSheet.RowNumber + rowsShifted, - rng.FirstAddressInSheet.ColumnNumber, - rng.LastAddressInSheet.RowNumber + rowsShifted, - rng.LastAddressInSheet.ColumnNumber); + rngMerged.RangeAddress.FirstAddress.RowNumber + rowsShifted, + rngMerged.RangeAddress.FirstAddress.ColumnNumber, + rngMerged.RangeAddress.LastAddress.RowNumber + rowsShifted, + rngMerged.RangeAddress.LastAddress.ColumnNumber); newMerge.Add(newRng.ToString()); } - else + else if (!(range.RangeAddress.FirstAddress.RowNumber <= rngMerged.RangeAddress.FirstAddress.RowNumber + && range.RangeAddress.FirstAddress.ColumnNumber <= rngMerged.RangeAddress.LastAddress.ColumnNumber)) { - newMerge.Add(rng.ToString()); + newMerge.Add(rngMerged.ToString()); } } Internals.MergedCells = newMerge; @@ -141,7 +147,7 @@ var rngRow = this.AsRange().FirstRowUsed(); if (rngRow != null) { - return this.Row(rngRow.FirstAddressInSheet.RowNumber); + return this.Row(rngRow.RangeAddress.FirstAddress.RowNumber); } else { @@ -153,7 +159,7 @@ var rngRow = this.AsRange().LastRowUsed(); if (rngRow != null) { - return this.Row(rngRow.LastAddressInSheet.RowNumber); + return this.Row(rngRow.RangeAddress.LastAddress.RowNumber); } else { @@ -165,7 +171,7 @@ var rngColumn = this.AsRange().FirstColumnUsed(); if (rngColumn != null) { - return this.Column(rngColumn.FirstAddressInSheet.ColumnNumber); + return this.Column(rngColumn.RangeAddress.FirstAddress.ColumnNumber); } else { @@ -177,7 +183,7 @@ var rngColumn = this.AsRange().LastColumnUsed(); if (rngColumn != null) { - return this.Column(rngColumn.LastAddressInSheet.ColumnNumber); + return this.Column(rngColumn.RangeAddress.LastAddress.ColumnNumber); } else { @@ -260,8 +266,8 @@ if (this.Internals.CellsCollection.Count > 0) rowList.AddRange(this.Internals.CellsCollection.Keys.Select(k => k.RowNumber).Distinct()); - if (this.Internals.ColumnsCollection.Count > 0) - rowList.AddRange(this.Internals.ColumnsCollection.Keys.Where(r => !rowList.Contains(r))); + if (this.Internals.RowsCollection.Count > 0) + rowList.AddRange(this.Internals.RowsCollection.Keys.Where(r => !rowList.Contains(r))); foreach (var r in rowList) { diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/BasicTable.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/BasicTable.cs index 416a069..b5e948e 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/BasicTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/BasicTable.cs @@ -62,9 +62,6 @@ //Using a custom format rngNumbers.Style.NumberFormat.Format = "$ #,##0"; - // Adjust column width - ws.Column("E").Width = 12; - //Formatting headers var rngHeaders = rngTable.Range("A2:E2"); // The address is relative to rngTable (NOT the worksheet) rngHeaders.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; @@ -92,6 +89,9 @@ //Bottom border rngTable.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/ClosedXML_Examples.csproj b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj index 34d86d5..293404c 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -54,6 +54,9 @@ + + + diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/DeletingColumns.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/DeletingColumns.cs index ea60d91..7b1552f 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/DeletingColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/DeletingColumns.cs @@ -52,18 +52,19 @@ var rngTitles = ws.Range("B2:D2"); ws.Row(1).InsertRowsBelow(2); - var rng1 = ws.Range("B2:D2"); + var rng1 = ws.Range("B2:D2"); var rng2 = ws.Range("F2:G2"); var rng3 = ws.Range("A1:A3"); var col1 = ws.Column(1); - // rng1 will have 2 columns starting at A2 - ws.Columns("A,C,E:H").Delete(); - rng1.Style.Fill.BackgroundColor = Color.Orange; rng2.Style.Fill.BackgroundColor = Color.Blue; - //rng3.Style.Fill.BackgroundColor = Color.Red; - //col1.Style.Fill.BackgroundColor = Color.Red; + rng3.Style.Fill.BackgroundColor = Color.Red; + col1.Style.Fill.BackgroundColor = Color.Black; + + ws.Columns("A,C,E:H").Delete(); + ws.Cell("A2").Value = "OK"; + ws.Cell("B2").Value = "OK"; workbook.SaveAs(filePath); } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs index 2fd1957..16e28bd 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs @@ -48,6 +48,9 @@ new DefaultStyles().Create(@"C:\Excel Files\Created\DefaultStyles.xlsx"); new TransposeRanges().Create(); new TransposeRangesPlus().Create(); + new MergeMoves().Create(); + new WorkbookProperties().Create(@"C:\Excel Files\Created\WorkbookProperties.xlsx"); + new AdjustToContents().Create(@"C:\Excel Files\Created\AdjustToContents.xlsx"); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/ChangingBasicTable.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/ChangingBasicTable.cs index 096cb1d..649075d 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/ChangingBasicTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/ChangingBasicTable.cs @@ -31,6 +31,8 @@ cell.Value += " Dollars"; } + ws.Columns().AdjustToContents(); + workbook.SaveAs(@"C:\Excel Files\Created\BasicTable_Modified.xlsx"); } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/AdjustToContents.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/AdjustToContents.cs new file mode 100644 index 0000000..309b8a7 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/AdjustToContents.cs @@ -0,0 +1,80 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; + +using System.Drawing; + +namespace ClosedXML_Examples.Misc +{ + public class AdjustToContents + { + #region Variables + + // Public + + // Private + + + #endregion + + #region Properties + + // Public + + // Private + + // Override + + + #endregion + + #region Events + + // Public + + // Private + + // Override + + + #endregion + + #region Methods + + // Public + public void Create(String filePath) + { + var wb = new XLWorkbook(); + var ws = wb.Worksheets.Add("Adjust To Contents"); + + // Set some values with different font sizes + ws.Cell(2, 2).Value = "A"; + ws.Cell(2, 2).Style.Font.FontSize = 30; + ws.Cell(3, 2).Value = "really, really, long text"; + ws.Cell(4, 2).Value = "long text"; + ws.Cell(5, 2).Value = "really long text"; + ws.Cell(5, 2).Style.Font.FontSize = 20; + + // Adjust the width of column 2 to its contents + ws.Column(2).AdjustToContents(); + + // Adjust the height of row 5 to its contents + ws.Row(5).AdjustToContents(); + + // You can also adjust all rows/columns in one shot + //ws.Rows().AdjustToContents(); + //ws.Columns().AdjustToContents(); + + wb.SaveAs(filePath); + } + + // Private + + // Override + + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/CellValues.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/CellValues.cs index 5801d3a..30c7368 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/CellValues.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/CellValues.cs @@ -55,7 +55,7 @@ ws.Cell(2, 4).Value = "Using Get...()"; ws.Cell(2, 5).Value = "Using GetValue()"; ws.Cell(2, 6).Value = "GetString()"; - ws.Cell(2, 7).Value = "GetFormattedValue()"; + ws.Cell(2, 7).Value = "GetFormattedString()"; ////////////////////////////////////////////////////////////////// // DateTime @@ -70,7 +70,7 @@ DateTime dateTime2 = cellDateTime.GetDateTime(); DateTime dateTime3 = cellDateTime.GetValue(); String dateTimeString = cellDateTime.GetString(); - String dateTimeFormattedString = cellDateTime.GetFormattedValue(); + String dateTimeFormattedString = cellDateTime.GetFormattedString(); // Set the values back to cells // The apostrophe is to force ClosedXML to treat the date as a string @@ -92,7 +92,7 @@ Boolean boolean2 = cellBoolean.GetBoolean(); Boolean boolean3 = cellBoolean.GetValue(); String booleanString = cellBoolean.GetString(); - String booleanFormattedString = cellBoolean.GetFormattedValue(); + String booleanFormattedString = cellBoolean.GetFormattedString(); // Set the values back to cells // The apostrophe is to force ClosedXML to treat the boolean as a string @@ -115,7 +115,7 @@ Double double2 = cellDouble.GetDouble(); Double double3 = cellDouble.GetValue(); String doubleString = cellDouble.GetString(); - String doubleFormattedString = cellDouble.GetFormattedValue(); + String doubleFormattedString = cellDouble.GetFormattedString(); // Set the values back to cells // The apostrophe is to force ClosedXML to treat the double as a string @@ -137,7 +137,7 @@ String string2 = cellString.GetString(); String string3 = cellString.GetValue(); String stringString = cellString.GetString(); - String stringFormattedString = cellString.GetFormattedValue(); + String stringFormattedString = cellString.GetFormattedString(); // Set the values back to cells ws.Cell(6, 3).Value = string1; @@ -153,6 +153,8 @@ rngTitle.Style.Font.Bold = true; rngTitle.Style.Fill.BackgroundColor = Color.Cyan; + ws.Columns().AdjustToContents(); + workbook.SaveAs(filePath); } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataTypes.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataTypes.cs index e600575..5dbd6ee 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataTypes.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataTypes.cs @@ -128,6 +128,8 @@ ws.Cell(ro, co + 1).DataType = XLCellValues.Text; ws.Cell(ro, co + 1).Value = ""; + ws.Columns(2, 3).AdjustToContents(); + workbook.SaveAs(filePath); } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MergeMoves.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MergeMoves.cs new file mode 100644 index 0000000..78c9d47 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MergeMoves.cs @@ -0,0 +1,93 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; + +using System.Drawing; + +namespace ClosedXML_Examples.Misc +{ + public class MergeMoves + { + #region Variables + + // Public + + // Private + + + #endregion + + #region Properties + + // Public + + // Private + + // Override + + + #endregion + + #region Constructors + + // Public + + + // Private + + + #endregion + + #region Events + + // Public + + // Private + + // Override + + + #endregion + + #region Methods + + // Public + public void Create() + { + var workbook = new XLWorkbook(@"C:\Excel Files\Created\MergedCells.xlsx"); + var ws = workbook.Worksheets.GetWorksheet(0); + + ws.Range("B1:F1").InsertRowsBelow(1); + ws.Range("A3:A9").InsertColumnsAfter(1); + ws.Row(1).Delete(); + ws.Column(1).Delete(); + + ws.Range("E8:E9").InsertColumnsAfter(1); + ws.Range("F2:F8").Merge(); + ws.Range("E3:E4").InsertColumnsAfter(1); + ws.Range("F2:F8").Merge(); + ws.Range("E1:E2").InsertColumnsAfter(1); + ws.Range("G2:G8").Merge(); + ws.Range("E1:E2").Delete(XLShiftDeletedCells.ShiftCellsLeft); + + ws.Range("D3:E3").InsertRowsBelow(1); + ws.Range("A1:B1").InsertRowsBelow(1); + ws.Range("B3:D3").Merge(); + ws.Range("A1:B1").Delete(XLShiftDeletedCells.ShiftCellsUp); + + ws.Range("B8:D8").Merge(); + ws.Range("D8:D9").Clear(); + + workbook.SaveAs(@"C:\Excel Files\Created\MergedMoves.xlsx"); + } + + // Private + + // Override + + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/WorkbookProperties.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/WorkbookProperties.cs new file mode 100644 index 0000000..7477139 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/WorkbookProperties.cs @@ -0,0 +1,73 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; + +using System.Drawing; + +namespace ClosedXML_Examples.Misc +{ + public class WorkbookProperties + { + #region Variables + + // Public + + // Private + + + #endregion + + #region Properties + + // Public + + // Private + + // Override + + + #endregion + + #region Events + + // Public + + // Private + + // Override + + + #endregion + + #region Methods + + // Public + public void Create(String filePath) + { + var wb = new XLWorkbook(); + var ws = wb.Worksheets.Add("Workbook Properties"); + + wb.Properties.Author = "theAuthor"; + wb.Properties.Title = "theTitle"; + wb.Properties.Subject = "theSubject"; + wb.Properties.Category = "theCategory"; + wb.Properties.Keywords = "theKeywords"; + wb.Properties.Comments = "theComments"; + wb.Properties.Status = "theStatus"; + wb.Properties.LastModifiedBy = "theLastModifiedBy"; + wb.Properties.Company = "theCompany"; + wb.Properties.Manager = "theManager"; + + wb.SaveAs(filePath); + } + + // Private + + // Override + + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/DefiningRanges.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/DefiningRanges.cs index 01971a8..6592258 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/DefiningRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/DefiningRanges.cs @@ -38,6 +38,8 @@ range5.Cell(1, 1).Value = "ws.Range(4, 1, 4, 2).Merge()"; range5.Merge(); + ws.Column("A").AdjustToContents(); + workbook.SaveAs(filePath); } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/ShiftingRanges.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/ShiftingRanges.cs index 93bd549..3877268 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/ShiftingRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/ShiftingRanges.cs @@ -24,6 +24,8 @@ // Change the background color of the headers rngHeaders.Style.Fill.BackgroundColor = Color.LightSalmon; + ws.Columns().AdjustToContents(); + workbook.SaveAs(@"C:\Excel Files\Created\ShiftingRanges.xlsx"); } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRangesPlus.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRangesPlus.cs index 3da6d12..0343d1e 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRangesPlus.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRangesPlus.cs @@ -20,9 +20,9 @@ rngTable.Row(rngTable.RowCount() - 1).Delete(XLShiftDeletedCells.ShiftCellsUp); // Place some markers - var cellNextRow = ws.Cell(rngTable.LastAddressInSheet.RowNumber + 1, rngTable.LastAddressInSheet.ColumnNumber); + var cellNextRow = ws.Cell(rngTable.RangeAddress.LastAddress.RowNumber + 1, rngTable.RangeAddress.LastAddress.ColumnNumber); cellNextRow.Value = "Next Row"; - var cellNextColumn = ws.Cell(rngTable.LastAddressInSheet.RowNumber, rngTable.LastAddressInSheet.ColumnNumber + 1); + var cellNextColumn = ws.Cell(rngTable.RangeAddress.LastAddress.RowNumber, rngTable.RangeAddress.LastAddress.ColumnNumber + 1); cellNextColumn.Value = "Next Column"; rngTable.Transpose(XLTransposeOptions.MoveCells); @@ -30,6 +30,8 @@ rngTable.Transpose(XLTransposeOptions.ReplaceCells); rngTable.Transpose(XLTransposeOptions.ReplaceCells); + ws.Columns().AdjustToContents(); + workbook.SaveAs(@"C:\Excel Files\Created\TransposeRangesPlus.xlsx"); } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleFont.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleFont.cs index 6990d5a..76258f6 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleFont.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleFont.cs @@ -47,6 +47,8 @@ ws.Cell(++ro, co).Value = "VerticalAlignment - Superscript"; ws.Cell(ro, co).Style.Font.VerticalAlignment = XLFontVerticalTextAlignmentValues.Superscript; + + ws.Column(co).AdjustToContents(); workbook.SaveAs(filePath); } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleNumberFormat.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleNumberFormat.cs index e89c3e1..6976e96 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleNumberFormat.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleNumberFormat.cs @@ -73,6 +73,8 @@ ws.Cell(++ro, co).Value = "12.345"; ws.Cell(ro, co).Style.NumberFormat.NumberFormatId = 3; + ws.Column(2).AdjustToContents(); + workbook.SaveAs(filePath); } diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index 0f7ef5d..1f507c9 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -14,9 +14,9 @@ { var wb = new XLWorkbook(); var ws = wb.Worksheets.Add("Test"); - ws.Row(1).Style.Fill.BackgroundColor = Color.Red; - ws.Cell(1, 1).Value = "Hello"; - + var rng = ws.Range("B2:B2"); + ws.Column(2).Delete(); + rng.Style.Fill.BackgroundColor = Color.Blue; //wb.Load(@"c:\Initial.xlsx"); wb.SaveAs(@"C:\Excel Files\ForTesting\Sandbox.xlsx");