diff --git a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj index a59cf63..27adbf3 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj @@ -53,22 +53,30 @@ - + + + + + + + + + - - - - - + + + + + - - - + + + @@ -80,7 +88,7 @@ - + @@ -88,7 +96,6 @@ - diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs index 0396c26..34e3807 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs @@ -10,8 +10,14 @@ public interface IXLCell: IXLStylized { - String Value { get; set; } + Object Value { get; set; } IXLAddress Address { get; } XLCellValues DataType { get; set; } + T GetValue(); + String GetString(); + String GetFormattedValue(); + Double GetDouble(); + Boolean GetBoolean(); + DateTime GetDateTime(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index 55e6dc3..bfbc6ed 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -8,26 +8,87 @@ { internal class XLCell : IXLCell { - public XLCell(IXLAddress address, IXLStyle defaultStyle) + XLWorksheet worksheet; + public XLCell(IXLAddress address, IXLStyle defaultStyle, XLWorksheet worksheet) { this.Address = address; Style = defaultStyle; - if (Style == null) Style = XLWorkbook.DefaultStyle; + if (Style == null) Style = worksheet.Style; + this.worksheet = worksheet; } public IXLAddress Address { get; private set; } - - private Boolean initialized = false; - private String cellValue = String.Empty; - public String Value + public String InnerText { - get + get { return cellValue; } + } + + public T GetValue() + { + return (T)Convert.ChangeType(Value, typeof(T)); + } + public String GetString() + { + return GetValue(); + } + public Double GetDouble() + { + return GetValue(); + } + public Boolean GetBoolean() + { + return GetValue(); + } + public DateTime GetDateTime() + { + return GetValue(); + } + public String GetFormattedValue() + { + if (dataType == XLCellValues.Boolean) + { + return (cellValue != "0").ToString(); + } + 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); + } + else { return cellValue; } + } + + private Boolean initialized = false; + private String cellValue = String.Empty; + public Object Value + { + get + { + if (dataType == XLCellValues.Boolean) + { + return cellValue != "0"; + } + else if (dataType == XLCellValues.DateTime) + { + return DateTime.FromOADate(Double.Parse(cellValue)); + } + else if (dataType == XLCellValues.Number) + { + return Double.Parse(cellValue); + } + else + { + return cellValue; + } + } set { - String val = value; + String val = value.ToString(); Double dTest; DateTime dtTest; Boolean bTest; @@ -138,9 +199,9 @@ { Boolean bTest; if (Boolean.TryParse(cellValue, out bTest)) - cellValue = Boolean.Parse(cellValue) ? "1" : "0"; + cellValue = bTest ? "1" : "0"; else - cellValue = value != 0 ? "1" : "0"; + cellValue = cellValue == "0" || String.IsNullOrEmpty(cellValue) ? "0" : "1"; } else if (value == XLCellValues.DateTime) { @@ -172,7 +233,7 @@ { if (dataType == XLCellValues.Boolean) { - cellValue = (cellValue == "0" ? false : true).ToString(); + cellValue = (cellValue != "0").ToString(); } else if (dataType == XLCellValues.Number) { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs index 00c5e4a..1c2afe8 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs @@ -14,5 +14,7 @@ void InsertColumnsAfter(Int32 numberOfColumns); void InsertColumnsBefore(Int32 numberOfColumns); void Clear(); + + IXLCell Cell(int row); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs index caa6462..039e6e2 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs @@ -22,12 +22,10 @@ else { this.style = new XLStyle(this, xlColumnParameters.DefaultStyle); - this.width = xlColumnParameters.Worksheet.DefaultColumnWidth; + this.width = xlColumnParameters.Worksheet.ColumnWidth; } } - - void Worksheet_RangeShiftedColumns(XLRange range, int columnsShifted) { if (range.FirstAddressInSheet.ColumnNumber <= this.ColumnNumber()) @@ -78,13 +76,18 @@ Worksheet.Internals.ColumnsCollection.Remove(columnNumber); } - public void Clear() + public new void Clear() { var range = this.AsRange(); range.Clear(); this.Style = Worksheet.Style; } + public IXLCell Cell(int row) + { + return base.Cell(row, 1); + } + #endregion #region IXLStylized Members @@ -167,14 +170,14 @@ return this.FirstAddressInSheet.ColumnLetter; } - public 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); } - public void InsertColumnsBefore( Int32 numberOfColumns) + public new void InsertColumnsBefore( Int32 numberOfColumns) { var columnNum = this.ColumnNumber(); this.Worksheet.Internals.ColumnsCollection.ShiftColumnsRight(columnNum, numberOfColumns); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs index 7282f6d..4a5a951 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs @@ -112,7 +112,7 @@ if (entireWorksheet) { - worksheet.DefaultColumnWidth = value; + worksheet.ColumnWidth = value; worksheet.Internals.ColumnsCollection.ForEach(c => c.Value.Width = value); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorkbook.cs deleted file mode 100644 index b84e7a4..0000000 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorkbook.cs +++ /dev/null @@ -1,17 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; - - -namespace ClosedXML.Excel -{ - public interface xIXLWorkbook - { - IXLWorksheets Worksheets { get; } - String Name { get; } - String FullName { get; } - void SaveAs(String file, Boolean overwrite = false); - void Load(String file); - } -} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs index 1351acb..bd93bfd 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs @@ -7,11 +7,11 @@ { public interface IXLWorksheet: IXLRangeBase { - Double DefaultColumnWidth { get; set; } - Double DefaultRowHeight { get; set; } + Double ColumnWidth { get; set; } + Double RowHeight { get; set; } String Name { get; set; } - IXLPageOptions PageSetup { get; } + IXLPageSetup PageSetup { get; } IXLRow FirstRowUsed(); IXLRow LastRowUsed(); @@ -27,5 +27,14 @@ IXLRow Row(Int32 row); IXLColumn Column(Int32 column); IXLColumn Column(String column); + IXLRange Range(int firstCellRow, int firstCellColumn, int lastCellRow, int lastCellColumn); + + IXLCell Cell(int row, int column); + IXLCell Cell(string cellAddressInRange); + IXLCell Cell(int row, string column); + IXLCell Cell(IXLAddress cellAddressInRange); + + int RowCount(); + int ColumnCount(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheetInternals.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheetInternals.cs index 2b4b3f5..c3c4a59 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheetInternals.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheetInternals.cs @@ -7,7 +7,7 @@ { internal interface IXLWorksheetInternals { - Dictionary CellsCollection { get; } + Dictionary CellsCollection { get; } XLColumnsCollection ColumnsCollection { get; } XLRowsCollection RowsCollection { get; } List MergedCells { get; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/IXLHFItem.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/IXLHFItem.cs new file mode 100644 index 0000000..9b3dac9 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/IXLHFItem.cs @@ -0,0 +1,24 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public enum XLHFPredefinedText + { + PageNumber, NumberOfPages, Date, Time, FullPath, Path, File, SheetName + } + public enum XLHFOccurrence + { + AllPages, OddPages, EvenPages, FirstPage + } + + public interface IXLHFItem + { + String GetText(XLHFOccurrence occurrence); + void AddText(String text, XLHFOccurrence occurrence = XLHFOccurrence.AllPages, IXLFont xlFont = null); + void AddText(XLHFPredefinedText predefinedText, XLHFOccurrence occurrence = XLHFOccurrence.AllPages, IXLFont xlFont = null); + void Clear(XLHFOccurrence occurrence = XLHFOccurrence.AllPages); + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/IXLHeaderFooter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/IXLHeaderFooter.cs new file mode 100644 index 0000000..5dddd14 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/IXLHeaderFooter.cs @@ -0,0 +1,16 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public enum XLHFMode { OddPagesOnly, OddAndEvenPages, Odd } + public interface IXLHeaderFooter + { + IXLHFItem Left { get; } + IXLHFItem Center { get; } + IXLHFItem Right { get; } + String GetText(XLHFOccurrence occurrence); + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/IXLMargins.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/IXLMargins.cs new file mode 100644 index 0000000..68e28f6 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/IXLMargins.cs @@ -0,0 +1,17 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public interface IXLMargins + { + Double Left { get; set; } + Double Right { get; set; } + Double Top { get; set; } + Double Bottom { get; set; } + Double Header { get; set; } + Double Footer { get; set; } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/IXLPageSetup.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/IXLPageSetup.cs new file mode 100644 index 0000000..bc9f804 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/IXLPageSetup.cs @@ -0,0 +1,128 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public enum XLPageOrientation { Default, Portrait, Landscape } + public enum XLPaperSize + { + LetterPaper = 1, + LetterSmallPaper = 2, + TabloidPaper = 3, + LedgerPaper = 4, + LegalPaper = 5, + StatementPaper = 6, + ExecutivePaper = 7, + A3Paper = 8, + A4Paper = 9, + A4SmallPaper = 10, + A5Paper = 11, + B4Paper = 12, + B5Paper = 13, + FolioPaper = 14, + QuartoPaper = 15, + StandardPaper = 16, + StandardPaper1 = 17, + NotePaper = 18, + No9Envelope = 19, + No10Envelope = 20, + No11Envelope = 21, + No12Envelope = 22, + No14Envelope = 23, + CPaper = 24, + DPaper = 25, + EPaper = 26, + DlEnvelope = 27, + C5Envelope = 28, + C3Envelope = 29, + C4Envelope = 30, + C6Envelope = 31, + C65Envelope = 32, + B4Envelope = 33, + B5Envelope = 34, + B6Envelope = 35, + ItalyEnvelope = 36, + MonarchEnvelope = 37, + No634Envelope = 38, + UsStandardFanfold = 39, + GermanStandardFanfold = 40, + GermanLegalFanfold = 41, + IsoB4 = 42, + JapaneseDoublePostcard = 43, + StandardPaper2 = 44, + StandardPaper3 = 45, + StandardPaper4 = 46, + InviteEnvelope = 47, + LetterExtraPaper = 50, + LegalExtraPaper = 51, + TabloidExtraPaper = 52, + A4ExtraPaper = 53, + LetterTransversePaper = 54, + A4TransversePaper = 55, + LetterExtraTransversePaper = 56, + SuperaSuperaA4Paper = 57, + SuperbSuperbA3Paper = 58, + LetterPlusPaper = 59, + A4PlusPaper = 60, + A5TransversePaper = 61, + JisB5TransversePaper = 62, + A3ExtraPaper = 63, + A5ExtraPaper = 64, + IsoB5ExtraPaper = 65, + A2Paper = 66, + A3TransversePaper = 67, + A3ExtraTransversePaper = 68 + } + public enum XLPageOrderValues { DownThenOver, OverThenDown } + public enum XLShowCommentsValues { None, AtEnd, AsDisplayed } + public enum XLPrintErrorValues { Blank, Dash, Displayed, NA } + + public interface IXLPageSetup + { + IXLPrintAreas PrintAreas { get; } + Int32 FirstRowToRepeatAtTop { get; } + Int32 LastRowToRepeatAtTop { get; } + void SetRowsToRepeatAtTop(String range); + void SetRowsToRepeatAtTop(Int32 firstRowToRepeatAtTop, Int32 lastRowToRepeatAtTop); + Int32 FirstColumnToRepeatAtLeft { get; } + Int32 LastColumnToRepeatAtLeft { get; } + void SetColumnsToRepeatAtLeft(Int32 firstColumnToRepeatAtLeft, Int32 lastColumnToRepeatAtLeft); + void SetColumnsToRepeatAtLeft(String range); + XLPageOrientation PageOrientation { get; set; } + Int32 PagesWide { get; set; } + Int32 PagesTall { get; set; } + Int32 Scale { get; set; } + Int32 HorizontalDpi { get; set; } + Int32 VerticalDpi { get; set; } + Int32 FirstPageNumber { get; set; } + Boolean CenterHorizontally { get; set; } + Boolean CenterVertically { get; set; } + void AdjustTo(Int32 pctOfNormalSize); + void FitToPages(Int32 pagesWide, Int32 pagesTall); + XLPaperSize PaperSize { get; set; } + IXLMargins Margins { get; } + + IXLHeaderFooter Header { get; } + IXLHeaderFooter Footer { get; } + Boolean ScaleHFWithDocument { get; set; } + Boolean AlignHFWithMargins { get; set; } + + Boolean ShowGridlines { get; set; } + Boolean ShowRowAndColumnHeadings { get; set; } + Boolean BlackAndWhite { get; set; } + Boolean DraftQuality { get; set; } + XLPageOrderValues PageOrder { get; set; } + XLShowCommentsValues ShowComments { get; set; } + + + List RowBreaks { get; } + List ColumnBreaks { get; } + void AddHorizontalPageBreak(Int32 row); + void AddVerticalPageBreak(Int32 column); + + XLPrintErrorValues PrintErrorValue { get; set; } + + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/IXLPrintAreas.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/IXLPrintAreas.cs new file mode 100644 index 0000000..2072f00 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/IXLPrintAreas.cs @@ -0,0 +1,16 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public interface IXLPrintAreas: IEnumerable + { + void Clear(); + void Add(Int32 firstCellRow, Int32 firstCellColumn, Int32 lastCellRow, Int32 lastCellColumn); + void Add(String rangeAddress); + void Add(String firstCellAddress, String lastCellAddress); + void Add(IXLAddress firstCellAddress, IXLAddress lastCellAddress); + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/XLHFItem.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/XLHFItem.cs new file mode 100644 index 0000000..d5cc0bc --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/XLHFItem.cs @@ -0,0 +1,136 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal class XLHFItem : IXLHFItem + { + private Dictionary texts = new Dictionary(); + public String GetText(XLHFOccurrence occurrence) + { + if(texts.ContainsKey(occurrence)) + return texts[occurrence]; + else + return String.Empty; + } + + public void AddText(String text, XLHFOccurrence occurrence = XLHFOccurrence.AllPages, IXLFont xlFont = null) + { + if (text.Length > 0) + { + var newText = xlFont != null ? GetHFFont(text, xlFont) : text; + //var newText = hfFont + text; + if (occurrence == XLHFOccurrence.AllPages) + { + AddTextToOccurrence(newText, XLHFOccurrence.EvenPages); + AddTextToOccurrence(newText, XLHFOccurrence.FirstPage); + AddTextToOccurrence(newText, XLHFOccurrence.OddPages); + } + else + { + AddTextToOccurrence(newText, occurrence); + } + } + } + + private void AddTextToOccurrence(String text, XLHFOccurrence occurrence) + { + if (text.Length > 0) + { + var newText = text; + if (texts.ContainsKey(occurrence)) + texts[occurrence] = texts[occurrence] + newText; + else + texts.Add(occurrence, newText); + } + } + + public void AddText(XLHFPredefinedText predefinedText, XLHFOccurrence occurrence = XLHFOccurrence.AllPages, IXLFont xlFont = null) + { + String hfText; + switch (predefinedText) + { + case XLHFPredefinedText.PageNumber: hfText = "&P"; break; + case XLHFPredefinedText.NumberOfPages : hfText = "&N"; break; + case XLHFPredefinedText.Date : hfText = "&D"; break; + case XLHFPredefinedText.Time : hfText = "&T"; break; + case XLHFPredefinedText.Path : hfText = "&Z"; break; + case XLHFPredefinedText.File : hfText = "&F"; break; + case XLHFPredefinedText.SheetName : hfText = "&A"; break; + case XLHFPredefinedText.FullPath: hfText = "&Z&F"; break; + default: throw new NotImplementedException(); + } + AddText(hfText, occurrence, xlFont); + } + + public void Clear(XLHFOccurrence occurrence = XLHFOccurrence.AllPages) + { + if (occurrence == XLHFOccurrence.AllPages) + { + ClearOccurrence(XLHFOccurrence.EvenPages); + ClearOccurrence(XLHFOccurrence.FirstPage); + ClearOccurrence(XLHFOccurrence.OddPages); + } + else + { + ClearOccurrence(occurrence); + } + } + + private void ClearOccurrence(XLHFOccurrence occurrence) + { + if (texts.ContainsKey(occurrence)) + texts.Remove(occurrence); + } + + private String GetHFFont(String text, IXLFont xlFont) + { + String retVal = String.Empty; + + retVal += xlFont.FontName != null ? "&\"" + xlFont.FontName : "\"-"; + retVal += GetHFFontBoldItalic(xlFont); + retVal += xlFont.FontSize > 0 ? "&" + xlFont.FontSize.ToString() : ""; + retVal += xlFont.Strikethrough ? "&S" : ""; + retVal += xlFont.VerticalAlignment == XLFontVerticalTextAlignmentValues.Subscript ? "&Y" : ""; + retVal += xlFont.VerticalAlignment == XLFontVerticalTextAlignmentValues.Superscript ? "&X" : ""; + retVal += xlFont.Underline== XLFontUnderlineValues.Single ? "&U" : ""; + retVal += xlFont.Underline == XLFontUnderlineValues.Double ? "&E" : ""; + retVal += "&K" + xlFont.FontColor.ToHex().Substring(2); + + retVal += text; + + retVal += xlFont.Underline == XLFontUnderlineValues.Double ? "&E" : ""; + retVal += xlFont.Underline == XLFontUnderlineValues.Single ? "&U" : ""; + retVal += xlFont.VerticalAlignment == XLFontVerticalTextAlignmentValues.Superscript ? "&X" : ""; + retVal += xlFont.VerticalAlignment == XLFontVerticalTextAlignmentValues.Subscript ? "&Y" : ""; + retVal += xlFont.Strikethrough ? "&S" : ""; + + return retVal; + } + + private String GetHFFontBoldItalic(IXLFont xlFont) + { + String retVal = String.Empty; + if (xlFont.Bold && xlFont.Italic) + { + retVal += ",Bold Italic\""; + } + else if (xlFont.Bold) + { + retVal += ",Bold\""; + } + else if (xlFont.Italic) + { + retVal += ",Italic\""; + } + else + { + retVal += ",Regular\""; + } + + return retVal; + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/XLHeaderFooter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/XLHeaderFooter.cs new file mode 100644 index 0000000..1bef2b5 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/XLHeaderFooter.cs @@ -0,0 +1,47 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal class XLHeaderFooter: IXLHeaderFooter + { + public XLHeaderFooter() + { + Left = new XLHFItem(); + Right = new XLHFItem(); + Center = new XLHFItem(); + } + public IXLHFItem Left { get; private set; } + public IXLHFItem Center { get; private set; } + public IXLHFItem Right { get; private set; } + + public String GetText(XLHFOccurrence occurrence) + { + if (innerTexts.ContainsKey(occurrence)) return innerTexts[occurrence]; + + var retVal = String.Empty; + var leftText = Left.GetText(occurrence); + var centerText = Center.GetText(occurrence); + var rightText = Right.GetText(occurrence); + retVal += leftText.Length > 0 ? "&L" + leftText : String.Empty; + retVal += centerText.Length > 0 ? "&C" + centerText : String.Empty; + retVal += rightText.Length > 0 ? "&R" + rightText : String.Empty; + if (retVal.Length > 255) + throw new ArgumentOutOfRangeException("Headers and Footers cannot be longer than 255 characters (including style markups)"); + return retVal; + } + + private Dictionary innerTexts = new Dictionary(); + internal String SetInnerText(XLHFOccurrence occurrence, String text) + { + if (innerTexts.ContainsKey(occurrence)) + innerTexts[occurrence] = text; + else + innerTexts.Add(occurrence, text); + + return innerTexts[occurrence]; + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/XLMargins.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/XLMargins.cs new file mode 100644 index 0000000..8ba86ad --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/XLMargins.cs @@ -0,0 +1,17 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal class XLMargins: IXLMargins + { + public Double Left { get; set; } + public Double Right { get; set; } + public Double Top { get; set; } + public Double Bottom { get; set; } + public Double Header { get; set; } + public Double Footer { get; set; } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/XLPageSetup.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/XLPageSetup.cs new file mode 100644 index 0000000..4c8df9a --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/XLPageSetup.cs @@ -0,0 +1,207 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal class XLPageSetup : IXLPageSetup + { + public XLPageSetup(IXLPageSetup defaultPageOptions, XLWorksheet worksheet) + { + this.PrintAreas = new XLPrintAreas(worksheet); + if (defaultPageOptions != null) + { + this.CenterHorizontally = defaultPageOptions.CenterHorizontally; + this.CenterVertically = defaultPageOptions.CenterVertically; + this.FirstPageNumber = defaultPageOptions.FirstPageNumber; + this.HorizontalDpi = defaultPageOptions.HorizontalDpi; + this.PageOrientation = defaultPageOptions.PageOrientation; + this.VerticalDpi = defaultPageOptions.VerticalDpi; + + this.PaperSize = defaultPageOptions.PaperSize; + this.pagesTall = defaultPageOptions.PagesTall; + this.pagesWide = defaultPageOptions.PagesWide; + this.scale = defaultPageOptions.Scale; + + if (defaultPageOptions.Margins != null) + { + this.Margins = new XLMargins() + { + Top = defaultPageOptions.Margins.Top, + Bottom = defaultPageOptions.Margins.Bottom, + Left = defaultPageOptions.Margins.Left, + Right = defaultPageOptions.Margins.Right, + Header = defaultPageOptions.Margins.Header, + Footer = defaultPageOptions.Margins.Footer + }; + } + this.AlignHFWithMargins = defaultPageOptions.AlignHFWithMargins; + this.ScaleHFWithDocument = defaultPageOptions.ScaleHFWithDocument; + this.ShowGridlines = defaultPageOptions.ShowGridlines; + this.ShowRowAndColumnHeadings = defaultPageOptions.ShowRowAndColumnHeadings; + this.BlackAndWhite = defaultPageOptions.BlackAndWhite; + this.DraftQuality = defaultPageOptions.DraftQuality; + this.PageOrder = defaultPageOptions.PageOrder; + + this.ColumnBreaks = new List(); + this.RowBreaks = new List(); + this.PrintErrorValue = defaultPageOptions.PrintErrorValue; + } + Header = new XLHeaderFooter(); + Footer = new XLHeaderFooter(); + } + public IXLPrintAreas PrintAreas { get; private set; } + + + public Int32 FirstRowToRepeatAtTop { get; private set; } + public Int32 LastRowToRepeatAtTop { get; private set; } + public void SetRowsToRepeatAtTop(String range) + { + var arrRange = range.Replace("$", "").Split(':'); + SetRowsToRepeatAtTop(Int32.Parse(arrRange[0]), Int32.Parse(arrRange[1])); + } + public void SetRowsToRepeatAtTop(Int32 firstRowToRepeatAtTop, Int32 lastRowToRepeatAtTop) + { + if (firstRowToRepeatAtTop <= 0) throw new ArgumentOutOfRangeException("The first row has to be greater than zero."); + if (firstRowToRepeatAtTop > lastRowToRepeatAtTop) throw new ArgumentOutOfRangeException("The first row has to be less than the second row."); + + FirstRowToRepeatAtTop = firstRowToRepeatAtTop; + LastRowToRepeatAtTop = lastRowToRepeatAtTop; + } + public Int32 FirstColumnToRepeatAtLeft { get; private set; } + public Int32 LastColumnToRepeatAtLeft { get; private set; } + public void SetColumnsToRepeatAtLeft(String range) + { + var arrRange = range.Replace("$", "").Split(':'); + Int32 iTest; + if (Int32.TryParse(arrRange[0], out iTest)) + SetColumnsToRepeatAtLeft(Int32.Parse(arrRange[0]), Int32.Parse(arrRange[1])); + else + SetColumnsToRepeatAtLeft(arrRange[0], arrRange[1]); + } + public void SetColumnsToRepeatAtLeft(String firstColumnToRepeatAtLeft, String lastColumnToRepeatAtLeft) + { + SetColumnsToRepeatAtLeft(XLAddress.GetColumnNumberFromLetter(firstColumnToRepeatAtLeft), XLAddress.GetColumnNumberFromLetter(lastColumnToRepeatAtLeft)); + } + public void SetColumnsToRepeatAtLeft(Int32 firstColumnToRepeatAtLeft, Int32 lastColumnToRepeatAtLeft) + { + if (firstColumnToRepeatAtLeft <= 0) throw new ArgumentOutOfRangeException("The first column has to be greater than zero."); + if (firstColumnToRepeatAtLeft > lastColumnToRepeatAtLeft) throw new ArgumentOutOfRangeException("The first column has to be less than the second column."); + + FirstColumnToRepeatAtLeft = firstColumnToRepeatAtLeft; + LastColumnToRepeatAtLeft = lastColumnToRepeatAtLeft; + } + + public XLPageOrientation PageOrientation { get; set; } + public XLPaperSize PaperSize { get; set; } + public Int32 HorizontalDpi { get; set; } + public Int32 VerticalDpi { get; set; } + public Int32 FirstPageNumber { get; set; } + public Boolean CenterHorizontally { get; set; } + public Boolean CenterVertically { get; set; } + public XLPrintErrorValues PrintErrorValue { get; set; } + public IXLMargins Margins { get; set; } + + private Int32 pagesWide; + public Int32 PagesWide + { + get + { + return pagesWide; + } + set + { + pagesWide = value; + if (pagesWide >0) + scale = 0; + } + } + + private Int32 pagesTall; + public Int32 PagesTall + { + get + { + return pagesTall; + } + set + { + pagesTall = value; + if (pagesTall >0) + scale = 0; + } + } + + private Int32 scale; + public Int32 Scale + { + get + { + return scale; + } + set + { + scale = value; + if (scale > 0) + { + pagesTall = 0; + pagesWide = 0; + } + } + } + + public void AdjustTo(Int32 pctOfNormalSize) + { + Scale = pctOfNormalSize; + pagesWide = 0; + pagesTall = 0; + } + public void FitToPages(Int32 pagesWide, Int32 pagesTall) + { + this.pagesWide = pagesWide; + this.pagesTall = pagesTall; + scale = 0; + } + + + public IXLHeaderFooter Header { get; private set; } + public IXLHeaderFooter Footer { get; private set; } + + public Boolean ScaleHFWithDocument { get; set; } + public Boolean AlignHFWithMargins { get; set; } + + public Boolean ShowGridlines { get; set; } + public Boolean ShowRowAndColumnHeadings { get; set; } + public Boolean BlackAndWhite { get; set; } + public Boolean DraftQuality { get; set; } + + public XLPageOrderValues PageOrder { get; set; } + public XLShowCommentsValues ShowComments { get; set; } + + public List RowBreaks { get; private set; } + public List ColumnBreaks { get; private set; } + public void AddHorizontalPageBreak(Int32 row) + { + if (!RowBreaks.Contains(row)) + RowBreaks.Add(row); + } + public void AddVerticalPageBreak(Int32 column) + { + if (!ColumnBreaks.Contains(column)) + ColumnBreaks.Add(column); + } + + //public void SetPageBreak(IXLRange range, XLPageBreakLocations breakLocation) + //{ + // switch (breakLocation) + // { + // case XLPageBreakLocations.AboveRange: RowBreaks.Add(range.Internals.Worksheet.Row(range.RowNumber)); break; + // case XLPageBreakLocations.BelowRange: RowBreaks.Add(range.Internals.Worksheet.Row(range.RowCount())); break; + // case XLPageBreakLocations.LeftOfRange: ColumnBreaks.Add(range.Internals.Worksheet.Column(range.ColumnNumber)); break; + // case XLPageBreakLocations.RightOfRange: ColumnBreaks.Add(range.Internals.Worksheet.Column(range.ColumnCount())); break; + // default: throw new NotImplementedException(); + // } + //} + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/XLPrintAreas.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/XLPrintAreas.cs new file mode 100644 index 0000000..2a3481e --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/XLPrintAreas.cs @@ -0,0 +1,52 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal class XLPrintAreas : IXLPrintAreas + { + List ranges = new List(); + private XLWorksheet worksheet; + public XLPrintAreas(XLWorksheet worksheet) + { + this.worksheet = worksheet; + } + + public void Clear() + { + ranges.Clear(); + } + + public void Add(int firstCellRow, int firstCellColumn, int lastCellRow, int lastCellColumn) + { + ranges.Add(worksheet.Range(firstCellRow, firstCellColumn, lastCellRow, lastCellColumn)); + } + + public void Add(string rangeAddress) + { + ranges.Add(worksheet.Range(rangeAddress)); + } + + public void Add(string firstCellAddress, string lastCellAddress) + { + ranges.Add(worksheet.Range(firstCellAddress, lastCellAddress)); + } + + public void Add(IXLAddress firstCellAddress, IXLAddress lastCellAddress) + { + ranges.Add(worksheet.Range(firstCellAddress, lastCellAddress)); + } + + public IEnumerator GetEnumerator() + { + return ranges.GetEnumerator(); + } + + System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() + { + return GetEnumerator(); + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLHFItem.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLHFItem.cs deleted file mode 100644 index 9b3dac9..0000000 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLHFItem.cs +++ /dev/null @@ -1,24 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; - -namespace ClosedXML.Excel -{ - public enum XLHFPredefinedText - { - PageNumber, NumberOfPages, Date, Time, FullPath, Path, File, SheetName - } - public enum XLHFOccurrence - { - AllPages, OddPages, EvenPages, FirstPage - } - - public interface IXLHFItem - { - String GetText(XLHFOccurrence occurrence); - void AddText(String text, XLHFOccurrence occurrence = XLHFOccurrence.AllPages, IXLFont xlFont = null); - void AddText(XLHFPredefinedText predefinedText, XLHFOccurrence occurrence = XLHFOccurrence.AllPages, IXLFont xlFont = null); - void Clear(XLHFOccurrence occurrence = XLHFOccurrence.AllPages); - } -} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLHeaderFooter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLHeaderFooter.cs deleted file mode 100644 index 5dddd14..0000000 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLHeaderFooter.cs +++ /dev/null @@ -1,16 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; - -namespace ClosedXML.Excel -{ - public enum XLHFMode { OddPagesOnly, OddAndEvenPages, Odd } - public interface IXLHeaderFooter - { - IXLHFItem Left { get; } - IXLHFItem Center { get; } - IXLHFItem Right { get; } - String GetText(XLHFOccurrence occurrence); - } -} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLMargins.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLMargins.cs deleted file mode 100644 index 68e28f6..0000000 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLMargins.cs +++ /dev/null @@ -1,17 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; - -namespace ClosedXML.Excel -{ - public interface IXLMargins - { - Double Left { get; set; } - Double Right { get; set; } - Double Top { get; set; } - Double Bottom { get; set; } - Double Header { get; set; } - Double Footer { get; set; } - } -} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLPageOptions.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLPageOptions.cs deleted file mode 100644 index 4526de7..0000000 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLPageOptions.cs +++ /dev/null @@ -1,128 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; - -namespace ClosedXML.Excel -{ - public enum XLPageOrientation { Default, Portrait, Landscape } - public enum XLPaperSize - { - LetterPaper = 1, - LetterSmallPaper = 2, - TabloidPaper = 3, - LedgerPaper = 4, - LegalPaper = 5, - StatementPaper = 6, - ExecutivePaper = 7, - A3Paper = 8, - A4Paper = 9, - A4SmallPaper = 10, - A5Paper = 11, - B4Paper = 12, - B5Paper = 13, - FolioPaper = 14, - QuartoPaper = 15, - StandardPaper = 16, - StandardPaper1 = 17, - NotePaper = 18, - No9Envelope = 19, - No10Envelope = 20, - No11Envelope = 21, - No12Envelope = 22, - No14Envelope = 23, - CPaper = 24, - DPaper = 25, - EPaper = 26, - DlEnvelope = 27, - C5Envelope = 28, - C3Envelope = 29, - C4Envelope = 30, - C6Envelope = 31, - C65Envelope = 32, - B4Envelope = 33, - B5Envelope = 34, - B6Envelope = 35, - ItalyEnvelope = 36, - MonarchEnvelope = 37, - No634Envelope = 38, - UsStandardFanfold = 39, - GermanStandardFanfold = 40, - GermanLegalFanfold = 41, - IsoB4 = 42, - JapaneseDoublePostcard = 43, - StandardPaper2 = 44, - StandardPaper3 = 45, - StandardPaper4 = 46, - InviteEnvelope = 47, - LetterExtraPaper = 50, - LegalExtraPaper = 51, - TabloidExtraPaper = 52, - A4ExtraPaper = 53, - LetterTransversePaper = 54, - A4TransversePaper = 55, - LetterExtraTransversePaper = 56, - SuperaSuperaA4Paper = 57, - SuperbSuperbA3Paper = 58, - LetterPlusPaper = 59, - A4PlusPaper = 60, - A5TransversePaper = 61, - JisB5TransversePaper = 62, - A3ExtraPaper = 63, - A5ExtraPaper = 64, - IsoB5ExtraPaper = 65, - A2Paper = 66, - A3TransversePaper = 67, - A3ExtraTransversePaper = 68 - } - public enum XLPageOrderValues { DownThenOver, OverThenDown } - public enum XLShowCommentsValues { None, AtEnd, AsDisplayed } - public enum XLPrintErrorValues { Blank, Dash, Displayed, NA } - - public interface IXLPageOptions - { - IXLPrintAreas PrintAreas { get; } - Int32 FirstRowToRepeatAtTop { get; } - Int32 LastRowToRepeatAtTop { get; } - void SetRowsToRepeatAtTop(String range); - void SetRowsToRepeatAtTop(Int32 firstRowToRepeatAtTop, Int32 lastRowToRepeatAtTop); - Int32 FirstColumnToRepeatAtLeft { get; } - Int32 LastColumnToRepeatAtLeft { get; } - void SetColumnsToRepeatAtLeft(Int32 firstColumnToRepeatAtLeft, Int32 lastColumnToRepeatAtLeft); - void SetColumnsToRepeatAtLeft(String range); - XLPageOrientation PageOrientation { get; set; } - Int32 PagesWide { get; set; } - Int32 PagesTall { get; set; } - Int32 Scale { get; set; } - Int32 HorizontalDpi { get; set; } - Int32 VerticalDpi { get; set; } - Int32 FirstPageNumber { get; set; } - Boolean CenterHorizontally { get; set; } - Boolean CenterVertically { get; set; } - void AdjustTo(Int32 pctOfNormalSize); - void FitToPages(Int32 pagesWide, Int32 pagesTall); - XLPaperSize PaperSize { get; set; } - IXLMargins Margins { get; } - - IXLHeaderFooter Header { get; } - IXLHeaderFooter Footer { get; } - Boolean ScaleHFWithDocument { get; set; } - Boolean AlignHFWithMargins { get; set; } - - Boolean ShowGridlines { get; set; } - Boolean ShowRowAndColumnHeadings { get; set; } - Boolean BlackAndWhite { get; set; } - Boolean DraftQuality { get; set; } - XLPageOrderValues PageOrder { get; set; } - XLShowCommentsValues ShowComments { get; set; } - - - List RowBreaks { get; } - List ColumnBreaks { get; } - void AddHorizontalPageBreak(Int32 row); - void AddVerticalPageBreak(Int32 column); - - XLPrintErrorValues PrintErrorValue { get; set; } - - } -} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLPrintAreas.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLPrintAreas.cs deleted file mode 100644 index 2072f00..0000000 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLPrintAreas.cs +++ /dev/null @@ -1,16 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; - -namespace ClosedXML.Excel -{ - public interface IXLPrintAreas: IEnumerable - { - void Clear(); - void Add(Int32 firstCellRow, Int32 firstCellColumn, Int32 lastCellRow, Int32 lastCellColumn); - void Add(String rangeAddress); - void Add(String firstCellAddress, String lastCellAddress); - void Add(IXLAddress firstCellAddress, IXLAddress lastCellAddress); - } -} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLHFItem.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLHFItem.cs deleted file mode 100644 index d5cc0bc..0000000 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLHFItem.cs +++ /dev/null @@ -1,136 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; - -namespace ClosedXML.Excel -{ - internal class XLHFItem : IXLHFItem - { - private Dictionary texts = new Dictionary(); - public String GetText(XLHFOccurrence occurrence) - { - if(texts.ContainsKey(occurrence)) - return texts[occurrence]; - else - return String.Empty; - } - - public void AddText(String text, XLHFOccurrence occurrence = XLHFOccurrence.AllPages, IXLFont xlFont = null) - { - if (text.Length > 0) - { - var newText = xlFont != null ? GetHFFont(text, xlFont) : text; - //var newText = hfFont + text; - if (occurrence == XLHFOccurrence.AllPages) - { - AddTextToOccurrence(newText, XLHFOccurrence.EvenPages); - AddTextToOccurrence(newText, XLHFOccurrence.FirstPage); - AddTextToOccurrence(newText, XLHFOccurrence.OddPages); - } - else - { - AddTextToOccurrence(newText, occurrence); - } - } - } - - private void AddTextToOccurrence(String text, XLHFOccurrence occurrence) - { - if (text.Length > 0) - { - var newText = text; - if (texts.ContainsKey(occurrence)) - texts[occurrence] = texts[occurrence] + newText; - else - texts.Add(occurrence, newText); - } - } - - public void AddText(XLHFPredefinedText predefinedText, XLHFOccurrence occurrence = XLHFOccurrence.AllPages, IXLFont xlFont = null) - { - String hfText; - switch (predefinedText) - { - case XLHFPredefinedText.PageNumber: hfText = "&P"; break; - case XLHFPredefinedText.NumberOfPages : hfText = "&N"; break; - case XLHFPredefinedText.Date : hfText = "&D"; break; - case XLHFPredefinedText.Time : hfText = "&T"; break; - case XLHFPredefinedText.Path : hfText = "&Z"; break; - case XLHFPredefinedText.File : hfText = "&F"; break; - case XLHFPredefinedText.SheetName : hfText = "&A"; break; - case XLHFPredefinedText.FullPath: hfText = "&Z&F"; break; - default: throw new NotImplementedException(); - } - AddText(hfText, occurrence, xlFont); - } - - public void Clear(XLHFOccurrence occurrence = XLHFOccurrence.AllPages) - { - if (occurrence == XLHFOccurrence.AllPages) - { - ClearOccurrence(XLHFOccurrence.EvenPages); - ClearOccurrence(XLHFOccurrence.FirstPage); - ClearOccurrence(XLHFOccurrence.OddPages); - } - else - { - ClearOccurrence(occurrence); - } - } - - private void ClearOccurrence(XLHFOccurrence occurrence) - { - if (texts.ContainsKey(occurrence)) - texts.Remove(occurrence); - } - - private String GetHFFont(String text, IXLFont xlFont) - { - String retVal = String.Empty; - - retVal += xlFont.FontName != null ? "&\"" + xlFont.FontName : "\"-"; - retVal += GetHFFontBoldItalic(xlFont); - retVal += xlFont.FontSize > 0 ? "&" + xlFont.FontSize.ToString() : ""; - retVal += xlFont.Strikethrough ? "&S" : ""; - retVal += xlFont.VerticalAlignment == XLFontVerticalTextAlignmentValues.Subscript ? "&Y" : ""; - retVal += xlFont.VerticalAlignment == XLFontVerticalTextAlignmentValues.Superscript ? "&X" : ""; - retVal += xlFont.Underline== XLFontUnderlineValues.Single ? "&U" : ""; - retVal += xlFont.Underline == XLFontUnderlineValues.Double ? "&E" : ""; - retVal += "&K" + xlFont.FontColor.ToHex().Substring(2); - - retVal += text; - - retVal += xlFont.Underline == XLFontUnderlineValues.Double ? "&E" : ""; - retVal += xlFont.Underline == XLFontUnderlineValues.Single ? "&U" : ""; - retVal += xlFont.VerticalAlignment == XLFontVerticalTextAlignmentValues.Superscript ? "&X" : ""; - retVal += xlFont.VerticalAlignment == XLFontVerticalTextAlignmentValues.Subscript ? "&Y" : ""; - retVal += xlFont.Strikethrough ? "&S" : ""; - - return retVal; - } - - private String GetHFFontBoldItalic(IXLFont xlFont) - { - String retVal = String.Empty; - if (xlFont.Bold && xlFont.Italic) - { - retVal += ",Bold Italic\""; - } - else if (xlFont.Bold) - { - retVal += ",Bold\""; - } - else if (xlFont.Italic) - { - retVal += ",Italic\""; - } - else - { - retVal += ",Regular\""; - } - - return retVal; - } - } -} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLHeaderFooter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLHeaderFooter.cs deleted file mode 100644 index 1bef2b5..0000000 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLHeaderFooter.cs +++ /dev/null @@ -1,47 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; - -namespace ClosedXML.Excel -{ - internal class XLHeaderFooter: IXLHeaderFooter - { - public XLHeaderFooter() - { - Left = new XLHFItem(); - Right = new XLHFItem(); - Center = new XLHFItem(); - } - public IXLHFItem Left { get; private set; } - public IXLHFItem Center { get; private set; } - public IXLHFItem Right { get; private set; } - - public String GetText(XLHFOccurrence occurrence) - { - if (innerTexts.ContainsKey(occurrence)) return innerTexts[occurrence]; - - var retVal = String.Empty; - var leftText = Left.GetText(occurrence); - var centerText = Center.GetText(occurrence); - var rightText = Right.GetText(occurrence); - retVal += leftText.Length > 0 ? "&L" + leftText : String.Empty; - retVal += centerText.Length > 0 ? "&C" + centerText : String.Empty; - retVal += rightText.Length > 0 ? "&R" + rightText : String.Empty; - if (retVal.Length > 255) - throw new ArgumentOutOfRangeException("Headers and Footers cannot be longer than 255 characters (including style markups)"); - return retVal; - } - - private Dictionary innerTexts = new Dictionary(); - internal String SetInnerText(XLHFOccurrence occurrence, String text) - { - if (innerTexts.ContainsKey(occurrence)) - innerTexts[occurrence] = text; - else - innerTexts.Add(occurrence, text); - - return innerTexts[occurrence]; - } - } -} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLMargins.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLMargins.cs deleted file mode 100644 index 8ba86ad..0000000 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLMargins.cs +++ /dev/null @@ -1,17 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; - -namespace ClosedXML.Excel -{ - internal class XLMargins: IXLMargins - { - public Double Left { get; set; } - public Double Right { get; set; } - public Double Top { get; set; } - public Double Bottom { get; set; } - public Double Header { get; set; } - public Double Footer { get; set; } - } -} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLPageOptions.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLPageOptions.cs deleted file mode 100644 index 44bd8af..0000000 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLPageOptions.cs +++ /dev/null @@ -1,207 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; - -namespace ClosedXML.Excel -{ - internal class XLPageOptions : IXLPageOptions - { - public XLPageOptions(IXLPageOptions defaultPageOptions, XLWorksheet worksheet) - { - this.PrintAreas = new XLPrintAreas(worksheet); - if (defaultPageOptions != null) - { - this.CenterHorizontally = defaultPageOptions.CenterHorizontally; - this.CenterVertically = defaultPageOptions.CenterVertically; - this.FirstPageNumber = defaultPageOptions.FirstPageNumber; - this.HorizontalDpi = defaultPageOptions.HorizontalDpi; - this.PageOrientation = defaultPageOptions.PageOrientation; - this.VerticalDpi = defaultPageOptions.VerticalDpi; - - this.PaperSize = defaultPageOptions.PaperSize; - this.pagesTall = defaultPageOptions.PagesTall; - this.pagesWide = defaultPageOptions.PagesWide; - this.scale = defaultPageOptions.Scale; - - if (defaultPageOptions.Margins != null) - { - this.Margins = new XLMargins() - { - Top = defaultPageOptions.Margins.Top, - Bottom = defaultPageOptions.Margins.Bottom, - Left = defaultPageOptions.Margins.Left, - Right = defaultPageOptions.Margins.Right, - Header = defaultPageOptions.Margins.Header, - Footer = defaultPageOptions.Margins.Footer - }; - } - this.AlignHFWithMargins = defaultPageOptions.AlignHFWithMargins; - this.ScaleHFWithDocument = defaultPageOptions.ScaleHFWithDocument; - this.ShowGridlines = defaultPageOptions.ShowGridlines; - this.ShowRowAndColumnHeadings = defaultPageOptions.ShowRowAndColumnHeadings; - this.BlackAndWhite = defaultPageOptions.BlackAndWhite; - this.DraftQuality = defaultPageOptions.DraftQuality; - this.PageOrder = defaultPageOptions.PageOrder; - - this.ColumnBreaks = new List(); - this.RowBreaks = new List(); - this.PrintErrorValue = defaultPageOptions.PrintErrorValue; - } - Header = new XLHeaderFooter(); - Footer = new XLHeaderFooter(); - } - public IXLPrintAreas PrintAreas { get; private set; } - - - public Int32 FirstRowToRepeatAtTop { get; private set; } - public Int32 LastRowToRepeatAtTop { get; private set; } - public void SetRowsToRepeatAtTop(String range) - { - var arrRange = range.Replace("$", "").Split(':'); - SetRowsToRepeatAtTop(Int32.Parse(arrRange[0]), Int32.Parse(arrRange[1])); - } - public void SetRowsToRepeatAtTop(Int32 firstRowToRepeatAtTop, Int32 lastRowToRepeatAtTop) - { - if (firstRowToRepeatAtTop <= 0) throw new ArgumentOutOfRangeException("The first row has to be greater than zero."); - if (firstRowToRepeatAtTop > lastRowToRepeatAtTop) throw new ArgumentOutOfRangeException("The first row has to be less than the second row."); - - FirstRowToRepeatAtTop = firstRowToRepeatAtTop; - LastRowToRepeatAtTop = lastRowToRepeatAtTop; - } - public Int32 FirstColumnToRepeatAtLeft { get; private set; } - public Int32 LastColumnToRepeatAtLeft { get; private set; } - public void SetColumnsToRepeatAtLeft(String range) - { - var arrRange = range.Replace("$", "").Split(':'); - Int32 iTest; - if (Int32.TryParse(arrRange[0], out iTest)) - SetColumnsToRepeatAtLeft(Int32.Parse(arrRange[0]), Int32.Parse(arrRange[1])); - else - SetColumnsToRepeatAtLeft(arrRange[0], arrRange[1]); - } - public void SetColumnsToRepeatAtLeft(String firstColumnToRepeatAtLeft, String lastColumnToRepeatAtLeft) - { - SetColumnsToRepeatAtLeft(XLAddress.GetColumnNumberFromLetter(firstColumnToRepeatAtLeft), XLAddress.GetColumnNumberFromLetter(lastColumnToRepeatAtLeft)); - } - public void SetColumnsToRepeatAtLeft(Int32 firstColumnToRepeatAtLeft, Int32 lastColumnToRepeatAtLeft) - { - if (firstColumnToRepeatAtLeft <= 0) throw new ArgumentOutOfRangeException("The first column has to be greater than zero."); - if (firstColumnToRepeatAtLeft > lastColumnToRepeatAtLeft) throw new ArgumentOutOfRangeException("The first column has to be less than the second column."); - - FirstColumnToRepeatAtLeft = firstColumnToRepeatAtLeft; - LastColumnToRepeatAtLeft = lastColumnToRepeatAtLeft; - } - - public XLPageOrientation PageOrientation { get; set; } - public XLPaperSize PaperSize { get; set; } - public Int32 HorizontalDpi { get; set; } - public Int32 VerticalDpi { get; set; } - public Int32 FirstPageNumber { get; set; } - public Boolean CenterHorizontally { get; set; } - public Boolean CenterVertically { get; set; } - public XLPrintErrorValues PrintErrorValue { get; set; } - public IXLMargins Margins { get; set; } - - private Int32 pagesWide; - public Int32 PagesWide - { - get - { - return pagesWide; - } - set - { - pagesWide = value; - if (pagesWide >0) - scale = 0; - } - } - - private Int32 pagesTall; - public Int32 PagesTall - { - get - { - return pagesTall; - } - set - { - pagesTall = value; - if (pagesTall >0) - scale = 0; - } - } - - private Int32 scale; - public Int32 Scale - { - get - { - return scale; - } - set - { - scale = value; - if (scale > 0) - { - pagesTall = 0; - pagesWide = 0; - } - } - } - - public void AdjustTo(Int32 pctOfNormalSize) - { - Scale = pctOfNormalSize; - pagesWide = 0; - pagesTall = 0; - } - public void FitToPages(Int32 pagesWide, Int32 pagesTall) - { - this.pagesWide = pagesWide; - this.pagesTall = pagesTall; - scale = 0; - } - - - public IXLHeaderFooter Header { get; private set; } - public IXLHeaderFooter Footer { get; private set; } - - public Boolean ScaleHFWithDocument { get; set; } - public Boolean AlignHFWithMargins { get; set; } - - public Boolean ShowGridlines { get; set; } - public Boolean ShowRowAndColumnHeadings { get; set; } - public Boolean BlackAndWhite { get; set; } - public Boolean DraftQuality { get; set; } - - public XLPageOrderValues PageOrder { get; set; } - public XLShowCommentsValues ShowComments { get; set; } - - public List RowBreaks { get; private set; } - public List ColumnBreaks { get; private set; } - public void AddHorizontalPageBreak(Int32 row) - { - if (!RowBreaks.Contains(row)) - RowBreaks.Add(row); - } - public void AddVerticalPageBreak(Int32 column) - { - if (!ColumnBreaks.Contains(column)) - ColumnBreaks.Add(column); - } - - //public void SetPageBreak(IXLRange range, XLPageBreakLocations breakLocation) - //{ - // switch (breakLocation) - // { - // case XLPageBreakLocations.AboveRange: RowBreaks.Add(range.Internals.Worksheet.Row(range.RowNumber)); break; - // case XLPageBreakLocations.BelowRange: RowBreaks.Add(range.Internals.Worksheet.Row(range.RowCount())); break; - // case XLPageBreakLocations.LeftOfRange: ColumnBreaks.Add(range.Internals.Worksheet.Column(range.ColumnNumber)); break; - // case XLPageBreakLocations.RightOfRange: ColumnBreaks.Add(range.Internals.Worksheet.Column(range.ColumnCount())); break; - // default: throw new NotImplementedException(); - // } - //} - } -} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLPrintAreas.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLPrintAreas.cs deleted file mode 100644 index 2a3481e..0000000 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLPrintAreas.cs +++ /dev/null @@ -1,52 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; - -namespace ClosedXML.Excel -{ - internal class XLPrintAreas : IXLPrintAreas - { - List ranges = new List(); - private XLWorksheet worksheet; - public XLPrintAreas(XLWorksheet worksheet) - { - this.worksheet = worksheet; - } - - public void Clear() - { - ranges.Clear(); - } - - public void Add(int firstCellRow, int firstCellColumn, int lastCellRow, int lastCellColumn) - { - ranges.Add(worksheet.Range(firstCellRow, firstCellColumn, lastCellRow, lastCellColumn)); - } - - public void Add(string rangeAddress) - { - ranges.Add(worksheet.Range(rangeAddress)); - } - - public void Add(string firstCellAddress, string lastCellAddress) - { - ranges.Add(worksheet.Range(firstCellAddress, lastCellAddress)); - } - - public void Add(IXLAddress firstCellAddress, IXLAddress lastCellAddress) - { - ranges.Add(worksheet.Range(firstCellAddress, lastCellAddress)); - } - - public IEnumerator GetEnumerator() - { - return ranges.GetEnumerator(); - } - - System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() - { - return GetEnumerator(); - } - } -} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs index c31761f..58b0351 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs @@ -9,24 +9,36 @@ public enum XLShiftDeletedCells { ShiftCellsUp, ShiftCellsLeft } public interface IXLRange: IXLRangeBase { - IXLRange Column(int column); - IXLRange Column(string column); - IXLRanges Columns(); - IXLRanges Columns(int firstColumn, int lastColumn); - IXLRanges Columns(string columns); - IXLRanges Columns(string firstColumn, string lastColumn); - IXLRange FirstColumn(); - IXLRange FirstColumnUsed(); - IXLRange FirstRow(); - IXLRange FirstRowUsed(); - IXLRange LastColumn(); - IXLRange LastColumnUsed(); - IXLRange LastRow(); - IXLRange LastRowUsed(); - IXLRange Row(int row); - IXLRanges Rows(); - IXLRanges Rows(int firstRow, int lastRow); - IXLRanges Rows(string rows); + IXLCell Cell(int row, int column); + IXLCell Cell(string cellAddressInRange); + IXLCell Cell(int row, string column); + IXLCell Cell(IXLAddress cellAddressInRange); + + IXLRangeColumn Column(int column); + IXLRangeColumn Column(string column); + IXLRangeColumn FirstColumn(); + IXLRangeColumn FirstColumnUsed(); + IXLRangeColumn LastColumn(); + IXLRangeColumn LastColumnUsed(); + IXLRangeColumns Columns(); + IXLRangeColumns Columns(int firstColumn, int lastColumn); + IXLRangeColumns Columns(string firstColumn, string lastColumn); + IXLRangeColumns Columns(string columns); + + IXLRangeRow FirstRow(); + IXLRangeRow FirstRowUsed(); + IXLRangeRow LastRow(); + IXLRangeRow LastRowUsed(); + IXLRangeRow Row(int row); + IXLRangeRows Rows(); + IXLRangeRows Rows(int firstRow, int lastRow); + IXLRangeRows Rows(string rows); + + IXLRange Range(int firstCellRow, int firstCellColumn, int lastCellRow, int lastCellColumn); + + int RowCount(); + int ColumnCount(); + void InsertColumnsAfter(int numberOfColumns); void InsertColumnsBefore(int numberOfColumns); void InsertRowsAbove(int numberOfRows); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs index 64d6685..be2ca1a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -7,24 +7,19 @@ { public interface IXLRangeBase: IXLStylized { - IXLCell Cell(ClosedXML.Excel.IXLAddress cellAddressInRange); - IXLCell Cell(int row, int column); - IXLCell Cell(int row, string column); - IXLCell Cell(string cellAddressInRange); - IEnumerable Cells(); - IEnumerable CellsUsed(); + IEnumerable Cells(); + IEnumerable CellsUsed(); IXLAddress FirstAddressInSheet { get; } IXLAddress LastAddressInSheet { get; } IXLCell FirstCell(); + IXLCell FirstCellUsed(Boolean ignoreStyle = true); IXLCell LastCell(); - IXLRange Range(ClosedXML.Excel.IXLAddress firstCellAddress, ClosedXML.Excel.IXLAddress lastCellAddress); - IXLRange Range(int firstCellRow, int firstCellColumn, int lastCellRow, int lastCellColumn); - IXLRange Range(string firstCellAddress, string lastCellAddress); + IXLCell LastCellUsed(Boolean ignoreStyle = true); IXLRange Range(string rangeAddress); + IXLRange Range(string firstCellAddress, string lastCellAddress); + IXLRange Range(IXLAddress firstCellAddress, IXLAddress lastCellAddress); IXLRanges Ranges(params string[] ranges); IXLRanges Ranges(string ranges); - int RowCount(); - int ColumnCount(); void Unmerge(); void Merge(); IXLRange AsRange(); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs new file mode 100644 index 0000000..e9a215d --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs @@ -0,0 +1,27 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + + +namespace ClosedXML.Excel +{ + public interface IXLRangeColumn: IXLRangeBase + { + IXLCell Cell(int row); + + IEnumerable Cells(int firstRow, int lastRow); + IXLRange Range(int firstColumn, int lastColumn); + + int RowCount(); + + void InsertColumnsAfter(int numberOfColumns); + void InsertColumnsBefore(int numberOfColumns); + void InsertRowsAbove(int numberOfRows); + void InsertRowsBelow(int numberOfRows); + + void Delete(XLShiftDeletedCells shiftDeleteCells = XLShiftDeletedCells.ShiftCellsLeft); + void Clear(); + } +} + diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs new file mode 100644 index 0000000..c48e535 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs @@ -0,0 +1,13 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public interface IXLRangeColumns: IEnumerable, IXLStylized + { + void Clear(); + void Add(IXLRangeColumn range); + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs new file mode 100644 index 0000000..5371f82 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs @@ -0,0 +1,29 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + + +namespace ClosedXML.Excel +{ + public interface IXLRangeRow: IXLRangeBase + { + IXLCell Cell(int column); + IXLCell Cell(string column); + + IEnumerable Cells(int firstColumn, int lastColumn); + IEnumerable Cells(String firstColumn, String lastColumn); + IXLRange Range(int firstRow, int lastRow); + + int ColumnCount(); + + void InsertColumnsAfter(int numberOfColumns); + void InsertColumnsBefore(int numberOfColumns); + void InsertRowsAbove(int numberOfRows); + void InsertRowsBelow(int numberOfRows); + + void Delete(XLShiftDeletedCells shiftDeleteCells = XLShiftDeletedCells.ShiftCellsUp); + void Clear(); + } +} + diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs new file mode 100644 index 0000000..81b0ab3 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs @@ -0,0 +1,13 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public interface IXLRangeRows: IEnumerable, IXLStylized + { + void Clear(); + void Add(IXLRangeRow range); + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs index 1f220eb..1dc374c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs @@ -6,10 +6,8 @@ namespace ClosedXML.Excel { - internal class XLRange : XLRangeBase, IXLRange + internal class XLRange: XLRangeBase, IXLRange { - public IXLStyle defaultStyle; - public XLRange(XLRangeParameters xlRangeParameters) { FirstAddressInSheet = xlRangeParameters.FirstCellAddress; @@ -27,33 +25,11 @@ if (range.FirstAddressInSheet.RowNumber <= FirstAddressInSheet.RowNumber && range.LastAddressInSheet.RowNumber >= LastAddressInSheet.RowNumber) { - ColumnsCollection_ColumnShifted(range.FirstAddressInSheet.ColumnNumber, columnsShifted); - } - } + if (range.FirstAddressInSheet.ColumnNumber <= FirstAddressInSheet.ColumnNumber) + FirstAddressInSheet = new XLAddress(FirstAddressInSheet.RowNumber, FirstAddressInSheet.ColumnNumber + columnsShifted); - void RowsCollection_RowShifted(int startingRow, int rowsShifted) - { - if (startingRow <= FirstAddressInSheet.RowNumber) - { - FirstAddressInSheet = new XLAddress(FirstAddressInSheet.RowNumber + rowsShifted, FirstAddressInSheet.ColumnNumber); - } - - if (startingRow <= LastAddressInSheet.RowNumber) - { - LastAddressInSheet = new XLAddress(LastAddressInSheet.RowNumber + rowsShifted, LastAddressInSheet.ColumnNumber); - } - } - - void ColumnsCollection_ColumnShifted(int startingColumn, int columnsShifted) - { - if (startingColumn <= FirstAddressInSheet.ColumnNumber) - { - FirstAddressInSheet = new XLAddress(FirstAddressInSheet.RowNumber, FirstAddressInSheet.ColumnNumber + columnsShifted); - } - - if (startingColumn <= LastAddressInSheet.ColumnNumber) - { - LastAddressInSheet = new XLAddress(LastAddressInSheet.RowNumber, LastAddressInSheet.ColumnNumber + columnsShifted); + if (range.FirstAddressInSheet.ColumnNumber <= LastAddressInSheet.ColumnNumber) + LastAddressInSheet = new XLAddress(LastAddressInSheet.RowNumber, LastAddressInSheet.ColumnNumber + columnsShifted); } } @@ -62,22 +38,25 @@ if (range.FirstAddressInSheet.ColumnNumber <= FirstAddressInSheet.ColumnNumber && range.LastAddressInSheet.ColumnNumber >= LastAddressInSheet.ColumnNumber) { - RowsCollection_RowShifted(range.FirstAddressInSheet.RowNumber, rowsShifted); + if (range.FirstAddressInSheet.RowNumber <= FirstAddressInSheet.RowNumber) + FirstAddressInSheet = new XLAddress(FirstAddressInSheet.RowNumber + rowsShifted, FirstAddressInSheet.ColumnNumber); + + if (range.FirstAddressInSheet.RowNumber <= LastAddressInSheet.RowNumber) + LastAddressInSheet = new XLAddress(LastAddressInSheet.RowNumber + rowsShifted, LastAddressInSheet.ColumnNumber); } } - #region IXLRange Members - public IXLRange FirstColumn() + public IXLRangeColumn FirstColumn() { return this.Column(1); } - public IXLRange LastColumn() + public IXLRangeColumn LastColumn() { return this.Column(this.ColumnCount()); } - public IXLRange FirstColumnUsed() + public IXLRangeColumn FirstColumnUsed() { var firstColumn = this.FirstAddressInSheet.ColumnNumber; var columnCount = this.ColumnCount(); @@ -97,9 +76,9 @@ if (minColumnUsed == Int32.MaxValue) return null; else - return this.Row(minColumnUsed); + return this.Column(minColumnUsed); } - public IXLRange LastColumnUsed() + public IXLRangeColumn LastColumnUsed() { var firstColumn = this.FirstAddressInSheet.ColumnNumber; var columnCount = this.ColumnCount(); @@ -122,15 +101,15 @@ return this.Column(maxColumnUsed); } - public IXLRange FirstRow() + public IXLRangeRow FirstRow() { return this.Row(1); } - public IXLRange LastRow() + public IXLRangeRow LastRow() { return this.Row(this.RowCount()); } - public IXLRange FirstRowUsed() + public IXLRangeRow FirstRowUsed() { var firstRow = this.FirstAddressInSheet.RowNumber; var rowCount = this.RowCount(); @@ -152,7 +131,7 @@ else return this.Row(minRowUsed); } - public IXLRange LastRowUsed() + public IXLRangeRow LastRowUsed() { var firstRow = this.FirstAddressInSheet.RowNumber; var rowCount = this.RowCount(); @@ -175,33 +154,60 @@ return this.Row(maxRowUsed); } - public IXLRange Row(Int32 row) + public IXLRangeRow Row(Int32 row) { - IXLAddress firstCellAddress = new XLAddress(row, 1); - IXLAddress lastCellAddress = new XLAddress(row, this.ColumnCount()); - return this.Range(firstCellAddress, lastCellAddress); + IXLAddress firstCellAddress = new XLAddress(FirstAddressInSheet.RowNumber + row - 1, FirstAddressInSheet.ColumnNumber); + IXLAddress lastCellAddress = new XLAddress(FirstAddressInSheet.RowNumber + row - 1, LastAddressInSheet.ColumnNumber); + return new XLRangeRow( + new XLRangeParameters( + firstCellAddress, + lastCellAddress, + Worksheet, + Worksheet.Style)); + } - public IXLRange Column(Int32 column) + public IXLRangeColumn Column(Int32 column) { - return this.Range(1, column, this.RowCount(), column); + IXLAddress firstCellAddress = new XLAddress(FirstAddressInSheet.RowNumber, FirstAddressInSheet.ColumnNumber + column - 1); + IXLAddress lastCellAddress = new XLAddress(LastAddressInSheet.RowNumber, FirstAddressInSheet.ColumnNumber + column - 1); + return new XLRangeColumn( + new XLRangeParameters( + firstCellAddress, + lastCellAddress, + Worksheet, + Worksheet.Style)); } - public IXLRange Column(String column) + public IXLRangeColumn Column(String column) { return this.Column(XLAddress.GetColumnNumberFromLetter(column)); } - public IXLRanges Columns() + public IXLRangeColumns Columns() { - var retVal = new XLRanges(); + var retVal = new XLRangeColumns(Worksheet); foreach (var c in Enumerable.Range(1, this.ColumnCount())) { retVal.Add(this.Column(c)); } return retVal; } - public IXLRanges Columns(String columns) + public IXLRangeColumns Columns(Int32 firstColumn, Int32 lastColumn) { - var retVal = new XLRanges(); + var retVal = new XLRangeColumns(Worksheet); + + for (var co = firstColumn; co <= lastColumn; co++) + { + retVal.Add(this.Column(co)); + } + return retVal; + } + public IXLRangeColumns Columns(String firstColumn, String lastColumn) + { + return this.Columns(XLAddress.GetColumnNumberFromLetter(firstColumn), XLAddress.GetColumnNumberFromLetter(lastColumn)); + } + public IXLRangeColumns Columns(String columns) + { + var retVal = new XLRangeColumns(Worksheet); var columnPairs = columns.Split(','); foreach (var pair in columnPairs) { @@ -233,32 +239,29 @@ } return retVal; } - public IXLRanges Columns(String firstColumn, String lastColumn) - { - return this.Columns(XLAddress.GetColumnNumberFromLetter(firstColumn), XLAddress.GetColumnNumberFromLetter(lastColumn)); - } - public IXLRanges Columns(Int32 firstColumn, Int32 lastColumn) - { - var retVal = new XLRanges(); - for (var co = firstColumn; co <= lastColumn; co++) - { - retVal.Add(this.Column(co)); - } - return retVal; - } - public IXLRanges Rows() + public IXLRangeRows Rows() { - var retVal = new XLRanges(); + var retVal = new XLRangeRows(Worksheet); foreach (var r in Enumerable.Range(1, this.RowCount())) { retVal.Add(this.Row(r)); } return retVal; } - public IXLRanges Rows(String rows) + public IXLRangeRows Rows(Int32 firstRow, Int32 lastRow) { - var retVal = new XLRanges(); + var retVal = new XLRangeRows(Worksheet); + + for (var ro = firstRow; ro <= lastRow; ro++) + { + retVal.Add(this.Row(ro)); + } + return retVal; + } + public IXLRangeRows Rows(String rows) + { + var retVal = new XLRangeRows(Worksheet); var rowPairs = rows.Split(','); foreach (var pair in rowPairs) { @@ -282,310 +285,8 @@ } return retVal; } - public IXLRanges Rows(Int32 firstRow, Int32 lastRow) - { - var retVal = new XLRanges(); - - for (var ro = firstRow; ro <= lastRow; ro++) - { - retVal.Add(this.Row(ro)); - } - return retVal; - } - - public void Clear() - { - // 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 - ); - } - public void Delete(XLShiftDeletedCells shiftDeleteCells) - { - //this.Clear(); - - // Range to shift... - var cellsToInsert = new Dictionary(); - 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); - - 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); - - var columnModifier = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsLeft ? this.ColumnCount() : 0; - var rowModifier = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp ? this.RowCount() : 0; - var cellsQuery = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsLeft ? shiftLeftQuery : shiftUpQuery; - foreach (var c in cellsQuery) - { - var newKey = new XLAddress(c.Key.RowNumber - rowModifier, c.Key.ColumnNumber - columnModifier); - var newCell = new XLCell(newKey, c.Value.Style); - newCell.Value = c.Value.Value; - newCell.DataType = c.Value.DataType; - cellsToDelete.Add(c.Key); - - var canInsert = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsLeft ? - c.Key.ColumnNumber > this.LastAddressInSheet.ColumnNumber : - c.Key.RowNumber > this.LastAddressInSheet.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)); - if (shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp) - { - Worksheet.NotifyRangeShiftedRows(this, rowModifier * -1); - } - else - { - Worksheet.NotifyRangeShiftedColumns(this, columnModifier * -1); - } - } - - public void InsertRowsBelow(Int32 numberOfRows) - { - this.InsertRowsBelow(numberOfRows, false); - } - internal void InsertRowsBelow(Int32 numberOfRows, Boolean onlyUsedCells) - { - var rowCount = this.RowCount(); - var firstRow = this.FirstAddressInSheet.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 lastColumn = firstColumn + this.ColumnCount() - 1; - if (lastColumn > XLWorksheet.MaxNumberOfColumns) lastColumn = XLWorksheet.MaxNumberOfColumns; - - var newRange = (XLRange)this.Worksheet.Range(firstRow, firstColumn, lastRow, lastColumn); - newRange.InsertRowsAbove(numberOfRows, onlyUsedCells); - } - public void InsertRowsAbove(Int32 numberOfRows) - { - this.InsertRowsAbove(numberOfRows, false); - } - internal void InsertRowsAbove(Int32 numberOfRows, Boolean onlyUsedCells) - { - 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; - - if (!onlyUsedCells) - { - var lastRow = this.Worksheet.LastRowUsed().RowNumber(); - for (var ro = lastRow; ro >= firstRow; ro--) - { - for (var co = lastColumn; co >= firstColumn; co--) - { - var oldKey = new XLAddress(ro, co); - var newRow = ro + numberOfRows; - var newKey = new XLAddress(newRow, co); - IXLCell oldCell; - if (this.Worksheet.Internals.CellsCollection.ContainsKey(oldKey)) - { - oldCell = this.Worksheet.Internals.CellsCollection[oldKey]; - } - else - { - oldCell = this.Worksheet.Cell(oldKey); - } - var newCell = new XLCell(newKey, oldCell.Style); - newCell.Value = oldCell.Value; - newCell.DataType = oldCell.DataType; - cellsToInsert.Add(newKey, newCell); - cellsToDelete.Add(oldKey); - if (oldKey.RowNumber < firstRow + numberOfRows) - cellsToBlank.Add(oldKey); - } - } - } - else - { - foreach (var c in this.Worksheet.Internals.CellsCollection - .Where(c => - c.Key.RowNumber >= firstRow - && c.Key.ColumnNumber >= firstColumn - && c.Key.ColumnNumber <= lastColumn - )) - { - var newRow = c.Key.RowNumber + numberOfRows; - var newKey = new XLAddress(newRow, c.Key.ColumnNumber); - var newCell = new XLCell(newKey, c.Value.Style); - newCell.Value = c.Value.Value; - newCell.DataType = c.Value.DataType; - cellsToInsert.Add(newKey, newCell); - cellsToDelete.Add(c.Key); - if (c.Key.RowNumber < firstRow + numberOfRows) - cellsToBlank.Add(c.Key); - } - } - cellsToDelete.ForEach(c => this.Worksheet.Internals.CellsCollection.Remove(c)); - cellsToInsert.ForEach(c => this.Worksheet.Internals.CellsCollection.Add(c.Key, c.Value)); - foreach (var c in cellsToBlank) - { - IXLStyle styleToUse; - if (this.Worksheet.Internals.ColumnsCollection.ContainsKey(c.ColumnNumber)) - styleToUse = this.Worksheet.Internals.ColumnsCollection[c.ColumnNumber].Style; - else - styleToUse = this.Worksheet.Style; - this.Worksheet.Cell(c).Style = styleToUse; - } - - Worksheet.NotifyRangeShiftedRows(this, numberOfRows); - } - - public void InsertColumnsAfter(Int32 numberOfColumns) - { - this.InsertColumnsAfter(numberOfColumns, false); - } - internal void InsertColumnsAfter(Int32 numberOfColumns, Boolean onlyUsedCells) - { - var columnCount = this.ColumnCount(); - var firstColumn = this.FirstAddressInSheet.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 lastRow = firstRow + this.RowCount() - 1; - if (lastRow > XLWorksheet.MaxNumberOfRows) lastRow = XLWorksheet.MaxNumberOfRows; - - var newRange = (XLRange)this.Worksheet.Range(firstRow, firstColumn, lastRow, lastColumn); - newRange.InsertColumnsBefore(numberOfColumns, onlyUsedCells); - } - public void InsertColumnsBefore(Int32 numberOfColumns) - { - this.InsertColumnsBefore(numberOfColumns, false); - } - internal void InsertColumnsBefore(Int32 numberOfColumns, Boolean onlyUsedCells) - { - 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; - - if (!onlyUsedCells) - { - var lastColumn = this.Worksheet.LastColumnUsed().ColumnNumber(); - for (var co = lastColumn; co >= firstColumn; co--) - { - for (var ro = lastRow; ro >= firstRow; ro--) - { - var oldKey = new XLAddress(ro, co); - var newColumn = co + numberOfColumns; - var newKey = new XLAddress(ro, newColumn); - IXLCell oldCell; - if (this.Worksheet.Internals.CellsCollection.ContainsKey(oldKey)) - { - oldCell = this.Worksheet.Internals.CellsCollection[oldKey]; - } - else - { - oldCell = this.Worksheet.Cell(oldKey); - } - var newCell = new XLCell(newKey, oldCell.Style); - newCell.Value = oldCell.Value; - newCell.DataType = oldCell.DataType; - cellsToInsert.Add(newKey, newCell); - cellsToDelete.Add(oldKey); - if (oldKey.ColumnNumber < firstColumn + numberOfColumns) - cellsToBlank.Add(oldKey); - } - } - } - else - { - foreach (var c in this.Worksheet.Internals.CellsCollection - .Where(c => - c.Key.ColumnNumber >= firstColumn - && c.Key.RowNumber >= firstRow - && c.Key.RowNumber <= lastRow - )) - { - var newColumn = c.Key.ColumnNumber + numberOfColumns; - var newKey = new XLAddress(c.Key.RowNumber, newColumn); - var newCell = new XLCell(newKey, c.Value.Style); - newCell.Value = c.Value.Value; - newCell.DataType = c.Value.DataType; - cellsToInsert.Add(newKey, newCell); - cellsToDelete.Add(c.Key); - if (c.Key.ColumnNumber < firstColumn + numberOfColumns) - cellsToBlank.Add(c.Key); - } - } - cellsToDelete.ForEach(c => this.Worksheet.Internals.CellsCollection.Remove(c)); - cellsToInsert.ForEach(c => this.Worksheet.Internals.CellsCollection.Add(c.Key, c.Value)); - foreach (var c in cellsToBlank) - { - IXLStyle styleToUse; - if (this.Worksheet.Internals.RowsCollection.ContainsKey(c.RowNumber)) - styleToUse = this.Worksheet.Internals.RowsCollection[c.RowNumber].Style; - else - styleToUse = this.Worksheet.Style; - this.Worksheet.Cell(c).Style = styleToUse; - } - - Worksheet.NotifyRangeShiftedColumns(this, numberOfColumns); - } - - #endregion - - #region IXLStylized Members - - public override IXLStyle Style - { - get - { - return this.defaultStyle; - } - set - { - this.Cells().ForEach(c => c.Style = value); - } - } - - public override IEnumerable Styles - { - get - { - UpdatingStyle = true; - foreach (var cell in this.Cells()) - { - yield return cell.Style; - } - UpdatingStyle = false; - } - } - - public override Boolean UpdatingStyle { get; set; } #endregion - public override IXLRange AsRange() - { - return this; - } - - public override string ToString() - { - return FirstAddressInSheet.ToString() + ":" + LastAddressInSheet.ToString(); - } - } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index e4fe333..a944212 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -7,6 +7,7 @@ { internal abstract class XLRangeBase: IXLRangeBase { + protected IXLStyle defaultStyle; public IXLAddress FirstAddressInSheet { get; protected set; } public IXLAddress LastAddressInSheet { get; protected set; } internal XLWorksheet Worksheet { get; set; } @@ -20,7 +21,46 @@ return this.Cell(this.RowCount(), this.ColumnCount()); } - public IXLCell Cell( IXLAddress cellAddressInRange) + public IXLCell FirstCellUsed(Boolean ignoreStyle = true) + { + var cellsUsed = CellsUsed(); + if (ignoreStyle) + cellsUsed = cellsUsed.Where(c => c.GetString().Length != 0); + + var cellsUsedFiltered = cellsUsed.Where(cell => cell.Address == cellsUsed.Min(c => c.Address)); + + if (cellsUsedFiltered.Count() > 0) + return cellsUsedFiltered.Single(); + else + return null; + } + + public IXLCell LastCellUsed(Boolean ignoreStyle = true) + { + var cellsUsed = CellsUsed(); + if (ignoreStyle) + cellsUsed = cellsUsed.Where(c => c.GetString().Length != 0); + + var cellsUsedFiltered = cellsUsed.Where(cell => cell.Address == cellsUsed.Max(c => c.Address)); + if (cellsUsedFiltered.Count() > 0) + return cellsUsedFiltered.Single(); + else + return null; + } + + public IXLCell Cell(Int32 row, Int32 column) + { + return this.Cell(new XLAddress(row, column)); + } + public IXLCell Cell(String cellAddressInRange) + { + return this.Cell(new XLAddress(cellAddressInRange)); + } + public IXLCell Cell(Int32 row, String column) + { + return this.Cell(new XLAddress(row, column)); + } + public IXLCell Cell(IXLAddress cellAddressInRange) { IXLAddress absoluteAddress = (XLAddress)cellAddressInRange + (XLAddress)this.FirstAddressInSheet - 1; if (this.Worksheet.Internals.CellsCollection.ContainsKey(absoluteAddress)) @@ -39,23 +79,11 @@ && this.Worksheet.Internals.ColumnsCollection[absoluteAddress.ColumnNumber].Style.ToString() != this.Worksheet.Style.ToString()) style = this.Worksheet.Internals.ColumnsCollection[absoluteAddress.ColumnNumber].Style; } - var newCell = new XLCell(absoluteAddress, style); + var newCell = new XLCell(absoluteAddress, style, Worksheet); this.Worksheet.Internals.CellsCollection.Add(absoluteAddress, newCell); return newCell; } } - public IXLCell Cell( Int32 row, Int32 column) - { - return this.Cell(new XLAddress(row, column)); - } - public IXLCell Cell( Int32 row, String column) - { - return this.Cell(new XLAddress(row, column)); - } - public IXLCell Cell( String cellAddressInRange) - { - return this.Cell(new XLAddress(cellAddressInRange)); - } public Int32 RowCount() { @@ -66,10 +94,6 @@ return this.LastAddressInSheet.ColumnNumber - this.FirstAddressInSheet.ColumnNumber + 1; } - public IXLRange Range( Int32 firstCellRow, Int32 firstCellColumn, Int32 lastCellRow, Int32 lastCellColumn) - { - return this.Range(new XLAddress(firstCellRow, firstCellColumn), new XLAddress(lastCellRow, lastCellColumn)); - } public IXLRange Range( String rangeAddress) { if (rangeAddress.Contains(':')) @@ -86,6 +110,10 @@ { return this.Range(new XLAddress(firstCellAddress), new XLAddress(lastCellAddress)); } + public IXLRange Range(Int32 firstCellRow, Int32 firstCellColumn, Int32 lastCellRow, Int32 lastCellColumn) + { + return this.Range(new XLAddress(firstCellRow, firstCellColumn), new XLAddress(lastCellRow, lastCellColumn)); + } public IXLRange Range( IXLAddress firstCellAddress, IXLAddress lastCellAddress) { var newFirstCellAddress = (XLAddress)firstCellAddress + (XLAddress)this.FirstAddressInSheet - 1; @@ -106,7 +134,7 @@ public IXLRanges Ranges( String ranges) { - var retVal = new XLRanges(); + var retVal = new XLRanges(Worksheet); var rangePairs = ranges.Split(','); foreach (var pair in rangePairs) { @@ -116,7 +144,7 @@ } public IXLRanges Ranges( params String[] ranges) { - var retVal = new XLRanges(); + var retVal = new XLRanges(Worksheet); foreach (var pair in ranges) { retVal.Add(this.Range(pair)); @@ -150,12 +178,298 @@ this.Worksheet.Internals.MergedCells.Remove(this.FirstAddressInSheet.ToString() + ":" + this.LastAddressInSheet.ToString()); } - public abstract IXLStyle Style { get; set; } - public abstract IEnumerable Styles { get; } + public void InsertColumnsAfter(Int32 numberOfColumns) + { + this.InsertColumnsAfter(numberOfColumns, false); + } + public void InsertColumnsAfter(Int32 numberOfColumns, Boolean onlyUsedCells) + { + var columnCount = this.ColumnCount(); + var firstColumn = this.FirstAddressInSheet.ColumnNumber + columnCount; + if (firstColumn > XLWorksheet.MaxNumberOfColumns) firstColumn = XLWorksheet.MaxNumberOfColumns; + var lastColumn = firstColumn + this.ColumnCount() - 1; + if (lastColumn > XLWorksheet.MaxNumberOfColumns) lastColumn = XLWorksheet.MaxNumberOfColumns; - public abstract Boolean UpdatingStyle { get; set; } + var firstRow = this.FirstAddressInSheet.RowNumber; + var lastRow = firstRow + this.RowCount() - 1; + if (lastRow > XLWorksheet.MaxNumberOfRows) lastRow = XLWorksheet.MaxNumberOfRows; - public abstract IXLRange AsRange(); + var newRange = (XLRange)this.Worksheet.Range(firstRow, firstColumn, lastRow, lastColumn); + newRange.InsertColumnsBefore(numberOfColumns, onlyUsedCells); + } + public void InsertColumnsBefore(Int32 numberOfColumns) + { + this.InsertColumnsBefore(numberOfColumns, false); + } + public void InsertColumnsBefore(Int32 numberOfColumns, Boolean onlyUsedCells) + { + 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; + + if (!onlyUsedCells) + { + var lastColumn = this.Worksheet.LastColumnUsed().ColumnNumber(); + for (var co = lastColumn; co >= firstColumn; co--) + { + for (var ro = lastRow; ro >= firstRow; ro--) + { + var oldKey = new XLAddress(ro, co); + var newColumn = co + numberOfColumns; + var newKey = new XLAddress(ro, newColumn); + IXLCell oldCell; + if (this.Worksheet.Internals.CellsCollection.ContainsKey(oldKey)) + { + oldCell = this.Worksheet.Internals.CellsCollection[oldKey]; + } + else + { + oldCell = this.Worksheet.Cell(oldKey); + } + var newCell = new XLCell(newKey, oldCell.Style, Worksheet); + newCell.Value = oldCell.Value; + newCell.DataType = oldCell.DataType; + cellsToInsert.Add(newKey, newCell); + cellsToDelete.Add(oldKey); + if (oldKey.ColumnNumber < firstColumn + numberOfColumns) + cellsToBlank.Add(oldKey); + } + } + } + else + { + foreach (var c in this.Worksheet.Internals.CellsCollection + .Where(c => + c.Key.ColumnNumber >= firstColumn + && c.Key.RowNumber >= firstRow + && c.Key.RowNumber <= lastRow + )) + { + var newColumn = c.Key.ColumnNumber + numberOfColumns; + var newKey = new XLAddress(c.Key.RowNumber, newColumn); + var newCell = new XLCell(newKey, c.Value.Style, Worksheet); + newCell.Value = c.Value.Value; + newCell.DataType = c.Value.DataType; + cellsToInsert.Add(newKey, newCell); + cellsToDelete.Add(c.Key); + if (c.Key.ColumnNumber < firstColumn + numberOfColumns) + cellsToBlank.Add(c.Key); + } + } + cellsToDelete.ForEach(c => this.Worksheet.Internals.CellsCollection.Remove(c)); + cellsToInsert.ForEach(c => this.Worksheet.Internals.CellsCollection.Add(c.Key, c.Value)); + foreach (var c in cellsToBlank) + { + IXLStyle styleToUse; + if (this.Worksheet.Internals.RowsCollection.ContainsKey(c.RowNumber)) + styleToUse = this.Worksheet.Internals.RowsCollection[c.RowNumber].Style; + else + styleToUse = this.Worksheet.Style; + this.Worksheet.Cell(c).Style = styleToUse; + } + + Worksheet.NotifyRangeShiftedColumns((XLRange)this.AsRange(), numberOfColumns); + } + + public void InsertRowsBelow(Int32 numberOfRows) + { + this.InsertRowsBelow(numberOfRows, false); + } + public void InsertRowsBelow(Int32 numberOfRows, Boolean onlyUsedCells) + { + var rowCount = this.RowCount(); + var firstRow = this.FirstAddressInSheet.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 lastColumn = firstColumn + this.ColumnCount() - 1; + if (lastColumn > XLWorksheet.MaxNumberOfColumns) lastColumn = XLWorksheet.MaxNumberOfColumns; + + var newRange = (XLRange)this.Worksheet.Range(firstRow, firstColumn, lastRow, lastColumn); + newRange.InsertRowsAbove(numberOfRows, onlyUsedCells); + } + public void InsertRowsAbove(Int32 numberOfRows) + { + this.InsertRowsAbove(numberOfRows, false); + } + public void InsertRowsAbove(Int32 numberOfRows, Boolean onlyUsedCells) + { + 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; + + if (!onlyUsedCells) + { + var lastRow = this.Worksheet.LastRowUsed().RowNumber(); + for (var ro = lastRow; ro >= firstRow; ro--) + { + for (var co = lastColumn; co >= firstColumn; co--) + { + var oldKey = new XLAddress(ro, co); + var newRow = ro + numberOfRows; + var newKey = new XLAddress(newRow, co); + IXLCell oldCell; + if (this.Worksheet.Internals.CellsCollection.ContainsKey(oldKey)) + { + oldCell = this.Worksheet.Internals.CellsCollection[oldKey]; + } + else + { + oldCell = this.Worksheet.Cell(oldKey); + } + var newCell = new XLCell(newKey, oldCell.Style, Worksheet); + newCell.Value = oldCell.Value; + newCell.DataType = oldCell.DataType; + cellsToInsert.Add(newKey, newCell); + cellsToDelete.Add(oldKey); + if (oldKey.RowNumber < firstRow + numberOfRows) + cellsToBlank.Add(oldKey); + } + } + } + else + { + foreach (var c in this.Worksheet.Internals.CellsCollection + .Where(c => + c.Key.RowNumber >= firstRow + && c.Key.ColumnNumber >= firstColumn + && c.Key.ColumnNumber <= lastColumn + )) + { + var newRow = c.Key.RowNumber + numberOfRows; + var newKey = new XLAddress(newRow, c.Key.ColumnNumber); + var newCell = new XLCell(newKey, c.Value.Style, Worksheet); + newCell.Value = c.Value.Value; + newCell.DataType = c.Value.DataType; + cellsToInsert.Add(newKey, newCell); + cellsToDelete.Add(c.Key); + if (c.Key.RowNumber < firstRow + numberOfRows) + cellsToBlank.Add(c.Key); + } + } + cellsToDelete.ForEach(c => this.Worksheet.Internals.CellsCollection.Remove(c)); + cellsToInsert.ForEach(c => this.Worksheet.Internals.CellsCollection.Add(c.Key, c.Value)); + foreach (var c in cellsToBlank) + { + IXLStyle styleToUse; + if (this.Worksheet.Internals.ColumnsCollection.ContainsKey(c.ColumnNumber)) + styleToUse = this.Worksheet.Internals.ColumnsCollection[c.ColumnNumber].Style; + else + styleToUse = this.Worksheet.Style; + this.Worksheet.Cell(c).Style = styleToUse; + } + + Worksheet.NotifyRangeShiftedRows((XLRange)this.AsRange(), numberOfRows); + } + + public void Clear() + { + // 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 + ); + } + public void Delete(XLShiftDeletedCells shiftDeleteCells) + { + //this.Clear(); + + // Range to shift... + var cellsToInsert = new Dictionary(); + 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); + + 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); + + var columnModifier = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsLeft ? this.ColumnCount() : 0; + var rowModifier = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp ? this.RowCount() : 0; + var cellsQuery = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsLeft ? shiftLeftQuery : shiftUpQuery; + foreach (var c in cellsQuery) + { + var newKey = new XLAddress(c.Key.RowNumber - rowModifier, c.Key.ColumnNumber - columnModifier); + var newCell = new XLCell(newKey, c.Value.Style, Worksheet); + newCell.Value = c.Value.Value; + newCell.DataType = c.Value.DataType; + cellsToDelete.Add(c.Key); + + var canInsert = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsLeft ? + c.Key.ColumnNumber > this.LastAddressInSheet.ColumnNumber : + c.Key.RowNumber > this.LastAddressInSheet.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)); + if (shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp) + { + Worksheet.NotifyRangeShiftedRows((XLRange)this.AsRange(), rowModifier * -1); + } + else + { + Worksheet.NotifyRangeShiftedColumns((XLRange)this.AsRange(), columnModifier * -1); + } + } + + #region IXLStylized Members + + public virtual IXLStyle Style + { + get + { + return this.defaultStyle; + } + set + { + this.Cells().ForEach(c => c.Style = value); + } + } + + public virtual IEnumerable Styles + { + get + { + UpdatingStyle = true; + foreach (var cell in this.Cells()) + { + yield return cell.Style; + } + UpdatingStyle = false; + } + } + + public virtual Boolean UpdatingStyle { get; set; } + + #endregion + + public virtual IXLRange AsRange() + { + return Worksheet.Range(FirstAddressInSheet, LastAddressInSheet); + } + + public override string ToString() + { + return FirstAddressInSheet.ToString() + ":" + LastAddressInSheet.ToString(); + } + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs new file mode 100644 index 0000000..cb064d5 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs @@ -0,0 +1,65 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + + +namespace ClosedXML.Excel +{ + internal class XLRangeColumn: XLRangeBase, IXLRangeColumn + { + public XLRangeColumn(XLRangeParameters xlRangeParameters) + { + FirstAddressInSheet = xlRangeParameters.FirstCellAddress; + LastAddressInSheet = xlRangeParameters.LastCellAddress; + Worksheet = xlRangeParameters.Worksheet; + Worksheet.RangeShiftedRows += new RangeShiftedRowsDelegate(Worksheet_RangeShiftedRows); + Worksheet.RangeShiftedColumns += new RangeShiftedColumnsDelegate(Worksheet_RangeShiftedColumns); + 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 (range.FirstAddressInSheet.ColumnNumber <= FirstAddressInSheet.ColumnNumber) + FirstAddressInSheet = new XLAddress(FirstAddressInSheet.RowNumber, FirstAddressInSheet.ColumnNumber + columnsShifted); + + if (range.FirstAddressInSheet.ColumnNumber <= LastAddressInSheet.ColumnNumber) + LastAddressInSheet = new XLAddress(LastAddressInSheet.RowNumber, LastAddressInSheet.ColumnNumber + columnsShifted); + } + } + void Worksheet_RangeShiftedRows(XLRange range, int rowsShifted) + { + if (range.FirstAddressInSheet.ColumnNumber <= FirstAddressInSheet.ColumnNumber + && range.LastAddressInSheet.ColumnNumber >= LastAddressInSheet.ColumnNumber) + { + if (range.FirstAddressInSheet.RowNumber <= FirstAddressInSheet.RowNumber) + FirstAddressInSheet = new XLAddress(FirstAddressInSheet.RowNumber + rowsShifted, FirstAddressInSheet.ColumnNumber); + + if (range.FirstAddressInSheet.RowNumber <= LastAddressInSheet.RowNumber) + LastAddressInSheet = new XLAddress(LastAddressInSheet.RowNumber + rowsShifted, LastAddressInSheet.ColumnNumber); + } + } + + public IXLCell Cell(int row) + { + return Cell(row, 1); + } + + public IEnumerable Cells(int firstRow, int lastRow) + { + return Cells() + .Where(c => c.Address.RowNumber >= firstRow + && c.Address.RowNumber <= lastRow); + } + + + public IXLRange Range(int firstRow, int lastRow) + { + return Range(firstRow, 1, lastRow, 1); + } + } +} + diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs new file mode 100644 index 0000000..7b5dca6 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs @@ -0,0 +1,80 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal class XLRangeColumns : IXLRangeColumns + { + public XLRangeColumns(XLWorksheet worksheet) + { + Style = worksheet.Style; + } + + List ranges = new List(); + + public void Clear() + { + ranges.ForEach(r => r.Clear()); + } + + public void Add(IXLRangeColumn range) + { + ranges.Add((XLRangeColumn)range); + } + + public IEnumerator GetEnumerator() + { + return ranges.ToList().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); + + } + } + + 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.FirstAddressInSheet.RowNumber + && c.Address.RowNumber <= rng.LastAddressInSheet.RowNumber + && c.Address.ColumnNumber >= rng.FirstAddressInSheet.ColumnNumber + && c.Address.ColumnNumber <= rng.LastAddressInSheet.ColumnNumber + )) + { + yield return r.Style; + } + } + UpdatingStyle = false; + } + } + + public Boolean UpdatingStyle { get; set; } + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs new file mode 100644 index 0000000..127be51 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs @@ -0,0 +1,74 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + + +namespace ClosedXML.Excel +{ + internal class XLRangeRow: XLRangeBase, IXLRangeRow + { + public XLRangeRow(XLRangeParameters xlRangeParameters) + { + FirstAddressInSheet = xlRangeParameters.FirstCellAddress; + LastAddressInSheet = xlRangeParameters.LastCellAddress; + Worksheet = xlRangeParameters.Worksheet; + Worksheet.RangeShiftedRows += new RangeShiftedRowsDelegate(Worksheet_RangeShiftedRows); + Worksheet.RangeShiftedColumns += new RangeShiftedColumnsDelegate(Worksheet_RangeShiftedColumns); + 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 (range.FirstAddressInSheet.ColumnNumber <= FirstAddressInSheet.ColumnNumber) + FirstAddressInSheet = new XLAddress(FirstAddressInSheet.RowNumber, FirstAddressInSheet.ColumnNumber + columnsShifted); + + if (range.FirstAddressInSheet.ColumnNumber <= LastAddressInSheet.ColumnNumber) + LastAddressInSheet = new XLAddress(LastAddressInSheet.RowNumber, LastAddressInSheet.ColumnNumber + columnsShifted); + } + } + void Worksheet_RangeShiftedRows(XLRange range, int rowsShifted) + { + if (range.FirstAddressInSheet.ColumnNumber <= FirstAddressInSheet.ColumnNumber + && range.LastAddressInSheet.ColumnNumber >= LastAddressInSheet.ColumnNumber) + { + if (range.FirstAddressInSheet.RowNumber <= FirstAddressInSheet.RowNumber) + FirstAddressInSheet = new XLAddress(FirstAddressInSheet.RowNumber + rowsShifted, FirstAddressInSheet.ColumnNumber); + + if (range.FirstAddressInSheet.RowNumber <= LastAddressInSheet.RowNumber) + LastAddressInSheet = new XLAddress(LastAddressInSheet.RowNumber + rowsShifted, LastAddressInSheet.ColumnNumber); + } + } + + public IXLCell Cell(int column) + { + return Cell(1, column); + } + public new IXLCell Cell(string column) + { + return Cell(1, column); + } + + public IEnumerable Cells(int firstColumn, int lastColumn) + { + return Cells() + .Where(c => c.Address.ColumnNumber >= firstColumn + && c.Address.ColumnNumber <= lastColumn); + } + public IEnumerable Cells(String firstColumn, String lastColumn) + { + return Cells() + .Where(c => c.Address.ColumnNumber >= XLAddress.GetColumnNumberFromLetter(firstColumn) + && c.Address.ColumnNumber <= XLAddress.GetColumnNumberFromLetter(lastColumn)); + } + + public IXLRange Range(int firstColumn, int lastColumn) + { + return Range(1, firstColumn, 1, lastColumn); + } + } +} + diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs new file mode 100644 index 0000000..4e72012 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs @@ -0,0 +1,80 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal class XLRangeRows : IXLRangeRows + { + public XLRangeRows(XLWorksheet worksheet) + { + Style = worksheet.Style; + } + + List ranges = new List(); + + public void Clear() + { + ranges.ForEach(r => r.Clear()); + } + + public void Add(IXLRangeRow range) + { + ranges.Add((XLRangeRow)range); + } + + public IEnumerator GetEnumerator() + { + return ranges.ToList().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); + + } + } + + 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.FirstAddressInSheet.RowNumber + && c.Address.RowNumber <= rng.LastAddressInSheet.RowNumber + && c.Address.ColumnNumber >= rng.FirstAddressInSheet.ColumnNumber + && c.Address.ColumnNumber <= rng.LastAddressInSheet.ColumnNumber + )) + { + yield return r.Style; + } + } + UpdatingStyle = false; + } + } + + public Boolean UpdatingStyle { get; set; } + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs index d50e3d1..2fa087f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs @@ -7,9 +7,9 @@ { internal class XLRanges : IXLRanges { - public XLRanges() + public XLRanges(XLWorksheet worksheet) { - Style = XLWorkbook.DefaultStyle; + Style = worksheet.Style; } List ranges = new List(); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs index e0794ec..2192723 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs @@ -13,5 +13,8 @@ void InsertRowsBelow(Int32 numberOfRows); void InsertRowsAbove(Int32 numberOfRows); void Clear(); + + IXLCell Cell(Int32 column); + IXLCell Cell(String column); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs index c23cbe7..6be2051 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs @@ -21,7 +21,7 @@ else { this.style = new XLStyle(this, xlRowParameters.DefaultStyle); - this.height = xlRowParameters.Worksheet.DefaultRowHeight; + this.height = xlRowParameters.Worksheet.RowHeight; } } @@ -43,7 +43,6 @@ LastAddressInSheet = new XLAddress(row, XLWorksheet.MaxNumberOfColumns); } - public Boolean IsReference { get; private set; } #region IXLRow Members @@ -87,7 +86,7 @@ return this.FirstAddressInSheet.RowNumber; } - public void InsertRowsBelow(Int32 numberOfRows) + public new void InsertRowsBelow(Int32 numberOfRows) { var rowNum = this.RowNumber(); this.Worksheet.Internals.RowsCollection.ShiftRowsDown(rowNum + 1, numberOfRows); @@ -95,7 +94,7 @@ range.InsertRowsBelow(numberOfRows, true); } - public void InsertRowsAbove(Int32 numberOfRows) + public new void InsertRowsAbove(Int32 numberOfRows) { var rowNum = this.RowNumber(); this.Worksheet.Internals.RowsCollection.ShiftRowsDown(rowNum, numberOfRows); @@ -105,15 +104,23 @@ range.InsertRowsAbove(numberOfRows, true); } - public void Clear() + public new void Clear() { var range = this.AsRange(); range.Clear(); this.Style = Worksheet.Style; } - #endregion + public IXLCell Cell(Int32 column) + { + return base.Cell(1, column); + } + public new IXLCell Cell(String column) + { + return base.Cell(1, column); + } + #endregion #region IXLStylized Members diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs index 8c7e61d..1332ff9 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs @@ -114,7 +114,7 @@ rows.ForEach(c => c.Height = value); if (entireWorksheet) { - worksheet.DefaultRowHeight = value; + worksheet.RowHeight = value; worksheet.Internals.RowsCollection.ForEach(r => r.Value.Height = value); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs index 4cfa971..fe05fa9 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs @@ -14,12 +14,15 @@ { DefaultRowHeight = 15; DefaultColumnWidth = 9.140625; - Worksheets = new XLWorksheets(); - + Worksheets = new XLWorksheets(this); PopulateEnums(); + Style = DefaultStyle; + RowHeight = DefaultRowHeight; + ColumnWidth = DefaultColumnWidth; + PageOptions = DefaultPageOptions; } - public XLWorkbook(String file) + public XLWorkbook(String file): this() { Load(file); } @@ -47,106 +50,100 @@ CreatePackage(file); } + public IXLStyle Style { get; set; } + public Double RowHeight { get; set; } + public Double ColumnWidth { get; set; } + public IXLPageSetup PageOptions { get; set; } + #endregion #region Static - private static IXLStyle defaultStyle; - /// - /// Gets the default style for new workbooks. - /// public static IXLStyle DefaultStyle { get { - if (defaultStyle == null) + var defaultStyle = new XLStyle(null, null) { - defaultStyle = new XLStyle(null, null) + Font = new XLFont(null, null) { - Font = new XLFont(null, null) + Bold = false, + Italic = false, + Underline = XLFontUnderlineValues.None, + Strikethrough = false, + VerticalAlignment = XLFontVerticalTextAlignmentValues.Baseline, + FontSize = 11, + FontColor = Color.FromArgb(0, 0, 0), + FontName = "Calibri", + FontFamilyNumbering = XLFontFamilyNumberingValues.Swiss + }, + + Fill = new XLFill(null) + { + BackgroundColor = Color.FromArgb(255, 255, 255), + PatternType = XLFillPatternValues.None, + PatternColor = Color.FromArgb(255, 255, 255) + }, + + Border = new XLBorder(null) { - Bold = false, - Italic = false, - Underline = XLFontUnderlineValues.None, - Strikethrough = false, - VerticalAlignment = XLFontVerticalTextAlignmentValues.Baseline, - FontSize = 11, - FontColor = Color.FromArgb(0, 0, 0), - FontName = "Calibri", - FontFamilyNumbering = XLFontFamilyNumberingValues.Swiss + BottomBorder = XLBorderStyleValues.None, + DiagonalBorder = XLBorderStyleValues.None, + DiagonalDown = false, + DiagonalUp = false, + LeftBorder = XLBorderStyleValues.None, + RightBorder = XLBorderStyleValues.None, + TopBorder = XLBorderStyleValues.None, + BottomBorderColor = Color.Black, + DiagonalBorderColor = Color.Black, + LeftBorderColor = Color.Black, + RightBorderColor = Color.Black, + TopBorderColor = Color.Black }, - - Fill = new XLFill(null) - { - BackgroundColor = Color.FromArgb(255, 255, 255), - PatternType = XLFillPatternValues.None, - PatternColor = Color.FromArgb(255, 255, 255) - }, - - Border = new XLBorder(null) - { - BottomBorder = XLBorderStyleValues.None, - DiagonalBorder = XLBorderStyleValues.None, - DiagonalDown = false, - DiagonalUp = false, - LeftBorder = XLBorderStyleValues.None, - RightBorder = XLBorderStyleValues.None, - TopBorder = XLBorderStyleValues.None, - BottomBorderColor = Color.Black, - DiagonalBorderColor = Color.Black, - LeftBorderColor = Color.Black, - RightBorderColor = Color.Black, - TopBorderColor = Color.Black - }, - NumberFormat = new XLNumberFormat(null) { NumberFormatId = 0 }, - Alignment = new XLAlignment(null) - { - Horizontal = XLAlignmentHorizontalValues.General, - Indent = 0, - JustifyLastLine = false, - ReadingOrder = XLAlignmentReadingOrderValues.ContextDependent, - RelativeIndent = 0, - ShrinkToFit = false, - TextRotation = 0, - Vertical = XLAlignmentVerticalValues.Bottom, - WrapText = false - } - }; - } + NumberFormat = new XLNumberFormat(null) { NumberFormatId = 0 }, + Alignment = new XLAlignment(null) + { + Horizontal = XLAlignmentHorizontalValues.General, + Indent = 0, + JustifyLastLine = false, + ReadingOrder = XLAlignmentReadingOrderValues.ContextDependent, + RelativeIndent = 0, + ShrinkToFit = false, + TextRotation = 0, + Vertical = XLAlignmentVerticalValues.Bottom, + WrapText = false + } + }; return defaultStyle; } } - public static Double DefaultRowHeight { get; set; } - public static Double DefaultColumnWidth { get; set; } + public static Double DefaultRowHeight { get; private set; } + public static Double DefaultColumnWidth { get; private set; } - public static IXLPageOptions defaultPageOptions; - public static IXLPageOptions DefaultPageOptions + public static IXLPageSetup DefaultPageOptions { get { - if (defaultPageOptions == null) + var defaultPageOptions = new XLPageSetup(null, null) { - defaultPageOptions = new XLPageOptions(null, null) + PageOrientation = XLPageOrientation.Default, + Scale = 100, + PaperSize = XLPaperSize.LetterPaper, + Margins = new XLMargins() { - PageOrientation = XLPageOrientation.Default, - Scale = 100, - PaperSize = XLPaperSize.LetterPaper, - Margins = new XLMargins() - { - Top = 0.75, - Bottom = 0.75, - Left = 0.75, - Right = 0.75, - Header = 0.75, - Footer = 0.75 - }, - ScaleHFWithDocument = true, - AlignHFWithMargins = true, - PrintErrorValue = XLPrintErrorValues.Displayed, - ShowComments = XLShowCommentsValues.None - }; - } + Top = 0.75, + Bottom = 0.75, + Left = 0.75, + Right = 0.75, + Header = 0.75, + Footer = 0.75 + }, + ScaleHFWithDocument = true, + AlignHFWithMargins = true, + PrintErrorValue = XLPrintErrorValues.Displayed, + ShowComments = XLShowCommentsValues.None + }; return defaultPageOptions; } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index 7aa27fc..0d7ab1c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -19,7 +19,7 @@ { public partial class XLWorkbook { - public void Load(String file) + private void Load(String file) { LoadSheets(file); @@ -60,8 +60,8 @@ var ws = (XLWorksheet)Worksheets.Add(sheetName); var sheetFormatProperties = (SheetFormatProperties)worksheetPart.Worksheet.Descendants().First(); - ws.DefaultRowHeight = sheetFormatProperties.DefaultRowHeight; - ws.DefaultColumnWidth = sheetFormatProperties.DefaultColumnWidth; + ws.RowHeight = sheetFormatProperties.DefaultRowHeight; + ws.ColumnWidth = sheetFormatProperties.DefaultColumnWidth; foreach (var mCell in worksheetPart.Worksheet.Descendants()) { @@ -73,7 +73,7 @@ var wsDefaultColumn = worksheetPart.Worksheet.Descendants().Where( c => c.Max == XLWorksheet.MaxNumberOfColumns).Single(); - if (wsDefaultColumn.Width != null) ws.DefaultColumnWidth = wsDefaultColumn.Width; + if (wsDefaultColumn.Width != null) ws.ColumnWidth = wsDefaultColumn.Width; Int32 styleIndexDefault = wsDefaultColumn.Style != null ? Int32.Parse(wsDefaultColumn.Style.InnerText) : -1; if (styleIndexDefault >= 0) @@ -90,7 +90,7 @@ if (col.Width != null) ((XLColumns)toApply).Width = col.Width; else - ((XLColumns)toApply).Width = ws.DefaultColumnWidth; + ((XLColumns)toApply).Width = ws.ColumnWidth; Int32 styleIndex = col.Style != null ? Int32.Parse(col.Style.InnerText) : -1; if (styleIndex > 0) @@ -111,7 +111,7 @@ if (row.Height != null) xlRow.Height = row.Height; else - xlRow.Height = ws.DefaultRowHeight; + xlRow.Height = ws.RowHeight; Int32 styleIndex = row.StyleIndex != null ? Int32.Parse(row.StyleIndex.InnerText) : -1; if (styleIndex > 0) @@ -150,12 +150,12 @@ else if (dCell.DataType == CellValues.Date) { xlCell.DataType = XLCellValues.DateTime; - xlCell.Value = DateTime.FromOADate(Double.Parse(dCell.CellValue.Text)).ToString(); + xlCell.Value = DateTime.FromOADate(Double.Parse(dCell.CellValue.Text)); } else if (dCell.DataType == CellValues.Boolean) { xlCell.DataType = XLCellValues.Boolean; - xlCell.Value = (dCell.CellValue.Text == "1").ToString(); + xlCell.Value = (dCell.CellValue.Text == "1"); } else if (dCell.DataType == CellValues.Number) { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 6eb253f..944bca2 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -424,7 +424,7 @@ private void GenerateSharedStringTablePartContent(SharedStringTablePart sharedStringTablePart) { List combined = new List(); - Worksheets.Cast().ForEach(w => combined.AddRange(w.Internals.CellsCollection.Values.Where(c => c.DataType == XLCellValues.Text && c.Value != null).Select(c => c.Value).Distinct())); + Worksheets.Cast().ForEach(w => combined.AddRange(w.Internals.CellsCollection.Values.Where(c => c.DataType == XLCellValues.Text && c.InnerText != null).Select(c => c.GetString()).Distinct())); var distinctStrings = combined.Distinct(); UInt32 stringCount = (UInt32)distinctStrings.Count(); SharedStringTable sharedStringTable = new SharedStringTable() { Count = (UInt32Value)stringCount, UniqueCount = (UInt32Value)stringCount }; @@ -446,7 +446,7 @@ private void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart) { - var defaultStyle = XLWorkbook.DefaultStyle; + var defaultStyle = DefaultStyle; Dictionary sharedFonts = new Dictionary(); sharedFonts.Add(defaultStyle.Font.ToString(), new FontInfo() { FontId = 0, Font = defaultStyle.Font }); @@ -684,7 +684,7 @@ // Cell styles = Named styles CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U }; - var defaultFormatId = sharedStyles.Values.Where(s => s.Style.ToString() == XLWorkbook.DefaultStyle.ToString()).Single().StyleId; + var defaultFormatId = sharedStyles.Values.Where(s => s.Style.ToString() == DefaultStyle.ToString()).Single().StyleId; CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)defaultFormatId, BuiltinId = (UInt32Value)0U }; cellStyles1.Append(cellStyle1); @@ -748,7 +748,7 @@ SheetView sheetView = new SheetView() { TabSelected = tabSelected, WorkbookViewId = (UInt32Value)0U }; sheetViews.Append(sheetView); - SheetFormatProperties sheetFormatProperties3 = new SheetFormatProperties() { DefaultRowHeight = xlWorksheet.DefaultRowHeight, DefaultColumnWidth = xlWorksheet.DefaultColumnWidth , CustomHeight = true }; + SheetFormatProperties sheetFormatProperties3 = new SheetFormatProperties() { DefaultRowHeight = xlWorksheet.RowHeight, DefaultColumnWidth = xlWorksheet.ColumnWidth , CustomHeight = true }; Columns columns = new Columns(); @@ -773,7 +773,7 @@ Min = 1, Max = (UInt32Value)(UInt32)(minInColumnsCollection - 1), Style = sharedStyles[xlWorksheet.Style.ToString()].StyleId, - Width = xlWorksheet.DefaultColumnWidth, + Width = xlWorksheet.ColumnWidth, CustomWidth = true }; columns.Append(column); @@ -791,7 +791,7 @@ else { styleId = sharedStyles[xlWorksheet.Style.ToString()].StyleId; - columnWidth = xlWorksheet.DefaultColumnWidth; + columnWidth = xlWorksheet.ColumnWidth; } Column column = new Column() @@ -812,7 +812,7 @@ Min = (UInt32Value)(UInt32)(maxInColumnsCollection + 1), Max = (UInt32Value)(UInt32)(XLWorksheet.MaxNumberOfColumns), Style = sharedStyles[xlWorksheet.Style.ToString()].StyleId, - Width = xlWorksheet.DefaultColumnWidth, + Width = xlWorksheet.ColumnWidth, CustomWidth = true }; columns.Append(column); @@ -842,7 +842,7 @@ } else { - row.Height = xlWorksheet.DefaultRowHeight; + row.Height = xlWorksheet.RowHeight; row.CustomHeight = true; } @@ -883,11 +883,11 @@ CellValue cellValue = new CellValue(); if (dataType == XLCellValues.Text) { - cellValue.Text = sharedStrings[opCell.Value.Value].ToString(); + cellValue.Text = sharedStrings[opCell.Value.InnerText].ToString(); } else { - cellValue.Text = opCell.Value.Value; + cellValue.Text = opCell.Value.InnerText; } cell.Append(cellValue); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index 13b0691..48cf2b8 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -21,16 +21,16 @@ #endregion - public XLWorksheet(String sheetName) + public XLWorksheet(String sheetName, XLWorkbook workbook) { Worksheet = this; - Style = XLWorkbook.DefaultStyle; - Internals = new XLWorksheetInternals(new Dictionary(), new XLColumnsCollection(), new XLRowsCollection(), new List()); + 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 XLPageOptions(XLWorkbook.DefaultPageOptions, this); - DefaultColumnWidth = XLWorkbook.DefaultColumnWidth; - DefaultRowHeight = XLWorkbook.DefaultRowHeight; + PageSetup = new XLPageSetup(workbook.PageOptions, this); + ColumnWidth = workbook.ColumnWidth; + RowHeight = workbook.RowHeight; this.Name = sheetName; RangeShiftedRows += new RangeShiftedRowsDelegate(XLWorksheet_RangeShiftedRows); RangeShiftedColumns += new RangeShiftedColumnsDelegate(XLWorksheet_RangeShiftedColumns); @@ -93,7 +93,6 @@ if (RangeShiftedColumns != null) RangeShiftedColumns(range, columnsShifted); } - public XLWorksheetInternals Internals { get; private set; } @@ -130,12 +129,12 @@ #endregion - public Double DefaultColumnWidth { get; set; } - public Double DefaultRowHeight { get; set; } + public Double ColumnWidth { get; set; } + public Double RowHeight { get; set; } public String Name { get; set; } - public IXLPageOptions PageSetup { get; private set; } + public IXLPageSetup PageSetup { get; private set; } public IXLRow FirstRowUsed() { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs index 8f85edd..fd888e7 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs @@ -8,7 +8,7 @@ internal class XLWorksheetInternals: IXLWorksheetInternals { public XLWorksheetInternals( - Dictionary cellsCollection , + Dictionary cellsCollection , XLColumnsCollection columnsCollection, XLRowsCollection rowsCollection, List mergedCells) @@ -19,7 +19,7 @@ MergedCells = mergedCells; } - public Dictionary CellsCollection { get; private set; } + public Dictionary CellsCollection { get; private set; } public XLColumnsCollection ColumnsCollection { get; private set; } public XLRowsCollection RowsCollection { get; private set; } public List MergedCells { get; internal set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs index 95d64a3..60a3fad 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs @@ -9,6 +9,12 @@ { Dictionary worksheets = new Dictionary(); + XLWorkbook workbook; + public XLWorksheets(XLWorkbook workbook) + { + this.workbook = workbook; + } + #region IXLWorksheets Members public IXLWorksheet GetWorksheet(string sheetName) @@ -23,7 +29,7 @@ public IXLWorksheet Add(String sheetName) { - var sheet = new XLWorksheet(sheetName); + var sheet = new XLWorksheet(sheetName, workbook); worksheets.Add(sheetName, sheet); return sheet; } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/BasicTable.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/BasicTable.cs index 9cf5335..416a069 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/BasicTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/BasicTable.cs @@ -35,19 +35,19 @@ //Adding more data types //Is an outcast? ws.Cell("D3").Value = "Outcast"; - ws.Cell("D4").Value = true.ToString(); - ws.Cell("D5").Value = false.ToString(); - ws.Cell("D6").Value = false.ToString(); + 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).ToString(); - ws.Cell("E5").Value = new DateTime(1907, 3, 4).ToString(); - ws.Cell("E6").Value = new DateTime(1921, 12, 15).ToString(); + 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"; + ws.Cell("F4").Value = 2000; + ws.Cell("F5").Value = 40000; + ws.Cell("F6").Value = 10000; //Defining ranges //From worksheet diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj index 5b56173..cf7ce8d 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -54,7 +54,9 @@ + + @@ -78,6 +80,7 @@ + diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/DeletingColumns.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/DeletingColumns.cs index bdf55eb..ea60d91 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/DeletingColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/DeletingColumns.cs @@ -51,8 +51,6 @@ var rngTitles = ws.Range("B2:D2"); ws.Row(1).InsertRowsBelow(2); - Console.Write(rngTitles.ToString()); // Prints "B4:D4 - Console.ReadKey(); var rng1 = ws.Range("B2:D2"); var rng2 = ws.Range("F2:G2"); diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs index 357aa8e..f28b7f1 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs @@ -42,7 +42,10 @@ new StyleRowsColumns().Create(@"C:\Excel Files\Created\StyleRowsColumns.xlsx"); new InsertingDeletingRows().Create(@"C:\Excel Files\Created\InsertingDeletingRows.xlsx"); new InsertingDeletingColumns().Create(@"C:\Excel Files\Created\InsertingDeletingColumns.xlsx"); - //new DeletingColumns().Create(@"C:\Excel Files\Created\DeletingColumns.xlsx"); + new DeletingColumns().Create(@"C:\Excel Files\Created\DeletingColumns.xlsx"); + new CellValues().Create(@"C:\Excel Files\Created\CellValues.xlsx"); + new LambdaExpressions().Create(); + new DefaultStyles().Create(@"C:\Excel Files\Created\DefaultStyles.xlsx"); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/ChangingBasicTable.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/ChangingBasicTable.cs index 3b6a883..096cb1d 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/ChangingBasicTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/ChangingBasicTable.cs @@ -12,8 +12,7 @@ { public void Create() { - var workbook = new XLWorkbook(); - workbook.Load(@"C:\Excel Files\Created\BasicTable.xlsx"); + var workbook = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); var ws = workbook.Worksheets.GetWorksheet(0); // Change the background color of the headers @@ -32,7 +31,7 @@ cell.Value += " Dollars"; } - workbook.SaveAs(@"C:\Excel Files\Modified\BasicTable_Modified.xlsx"); + workbook.SaveAs(@"C:\Excel Files\Created\BasicTable_Modified.xlsx"); } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/LoadFiles.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/LoadFiles.cs index c829bd5..09393da 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/LoadFiles.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/LoadFiles.cs @@ -36,8 +36,7 @@ private static void LoadAndSaveFile(String input, String output) { - var wb = new XLWorkbook(); - wb.Load(input); + var wb = new XLWorkbook(input); wb.SaveAs(output); } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/CellValues.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/CellValues.cs new file mode 100644 index 0000000..5801d3a --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/CellValues.cs @@ -0,0 +1,166 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; + +using System.Drawing; + +namespace ClosedXML_Examples.Misc +{ + public class CellValues + { + #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 workbook = new XLWorkbook(); + var ws = workbook.Worksheets.Add("Cell Values"); + + // Set the titles + ws.Cell(2, 2).Value = "Initial Value"; + ws.Cell(2, 3).Value = "Casting"; + 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()"; + + ////////////////////////////////////////////////////////////////// + // DateTime + + // Fill a cell with a date + var cellDateTime = ws.Cell(3, 2); + cellDateTime.Value = new DateTime(2010, 9, 2); + cellDateTime.Style.DateFormat.Format = "yyyy-MMM-dd"; + + // Extract the date in different ways + DateTime dateTime1 = (DateTime)cellDateTime.Value; + DateTime dateTime2 = cellDateTime.GetDateTime(); + DateTime dateTime3 = cellDateTime.GetValue(); + String dateTimeString = cellDateTime.GetString(); + String dateTimeFormattedString = cellDateTime.GetFormattedValue(); + + // Set the values back to cells + // The apostrophe is to force ClosedXML to treat the date as a string + ws.Cell(3, 3).Value = dateTime1; + ws.Cell(3, 4).Value = dateTime2; + ws.Cell(3, 5).Value = dateTime3; + ws.Cell(3, 6).Value = "'" + dateTimeString; + ws.Cell(3, 7).Value = "'" + dateTimeFormattedString; + + ////////////////////////////////////////////////////////////////// + // Boolean + + // Fill a cell with a boolean + var cellBoolean = ws.Cell(4, 2); + cellBoolean.Value = true; + + // Extract the boolean in different ways + Boolean boolean1 = (Boolean)cellBoolean.Value; + Boolean boolean2 = cellBoolean.GetBoolean(); + Boolean boolean3 = cellBoolean.GetValue(); + String booleanString = cellBoolean.GetString(); + String booleanFormattedString = cellBoolean.GetFormattedValue(); + + // Set the values back to cells + // The apostrophe is to force ClosedXML to treat the boolean as a string + ws.Cell(4, 3).Value = boolean1; + ws.Cell(4, 4).Value = boolean2; + ws.Cell(4, 5).Value = boolean3; + ws.Cell(4, 6).Value = "'" + booleanString; + ws.Cell(4, 7).Value = "'" + booleanFormattedString; + + ////////////////////////////////////////////////////////////////// + // Double + + // Fill a cell with a double + var cellDouble = ws.Cell(5, 2); + cellDouble.Value = 1234.567; + cellDouble.Style.NumberFormat.Format = "#,##0.00"; + + // Extract the double in different ways + Double double1 = (Double)cellDouble.Value; + Double double2 = cellDouble.GetDouble(); + Double double3 = cellDouble.GetValue(); + String doubleString = cellDouble.GetString(); + String doubleFormattedString = cellDouble.GetFormattedValue(); + + // Set the values back to cells + // The apostrophe is to force ClosedXML to treat the double as a string + ws.Cell(5, 3).Value = double1; + ws.Cell(5, 4).Value = double2; + ws.Cell(5, 5).Value = double3; + ws.Cell(5, 6).Value = "'" + doubleString; + ws.Cell(5, 7).Value = "'" + doubleFormattedString; + + ////////////////////////////////////////////////////////////////// + // String + + // Fill a cell with a string + var cellString = ws.Cell(6, 2); + cellString.Value = "Test Case"; + + // Extract the string in different ways + String string1 = (String)cellString.Value; + String string2 = cellString.GetString(); + String string3 = cellString.GetValue(); + String stringString = cellString.GetString(); + String stringFormattedString = cellString.GetFormattedValue(); + + // Set the values back to cells + ws.Cell(6, 3).Value = string1; + ws.Cell(6, 4).Value = string2; + ws.Cell(6, 5).Value = string3; + ws.Cell(6, 6).Value = stringString; + ws.Cell(6, 7).Value = stringFormattedString; + + ////////////////////////////////////////////////////////////////// + // Do some formatting + ws.Columns("B:G").Width = 20; + var rngTitle = ws.Range("B2:G2"); + rngTitle.Style.Font.Bold = true; + rngTitle.Style.Fill.BackgroundColor = Color.Cyan; + + workbook.SaveAs(filePath); + } + + // Private + + // Override + + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataTypes.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataTypes.cs index 8df2eb2..e600575 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataTypes.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataTypes.cs @@ -56,13 +56,13 @@ ws.Cell(ro, co + 1).Value = "Hello World."; ws.Cell(++ro, co).Value = "Plain Date:"; - ws.Cell(ro, co + 1).Value = new DateTime(2010, 9, 2).ToString(); + ws.Cell(ro, co + 1).Value = new DateTime(2010, 9, 2); ws.Cell(++ro, co).Value = "Plain Boolean:"; - ws.Cell(ro, co + 1).Value = true.ToString(); + ws.Cell(ro, co + 1).Value = true; ws.Cell(++ro, co).Value = "Plain Number:"; - ws.Cell(ro, co + 1).Value = "123.45"; + ws.Cell(ro, co + 1).Value = 123.45; ro++; @@ -85,15 +85,15 @@ ro++; ws.Cell(++ro, co).Value = "Date to Text:"; - ws.Cell(ro, co + 1).Value = new DateTime(2010, 9, 2).ToString(); + ws.Cell(ro, co + 1).Value = new DateTime(2010, 9, 2); ws.Cell(ro, co + 1).DataType = XLCellValues.Text; ws.Cell(++ro, co).Value = "Boolean to Text:"; - ws.Cell(ro, co + 1).Value = true.ToString(); + ws.Cell(ro, co + 1).Value = true; ws.Cell(ro, co + 1).DataType = XLCellValues.Text; ws.Cell(++ro, co).Value = "Number to Text:"; - ws.Cell(ro, co + 1).Value = "123.45"; + ws.Cell(ro, co + 1).Value = 123.45; ws.Cell(ro, co + 1).DataType = XLCellValues.Text; ws.Cell(++ro, co).Value = "Text to Date:"; @@ -111,19 +111,19 @@ ro++; ws.Cell(++ro, co).Value = "Formatted Date to Text:"; - ws.Cell(ro, co + 1).Value = new DateTime(2010, 9, 2).ToString(); + ws.Cell(ro, co + 1).Value = new DateTime(2010, 9, 2); ws.Cell(ro, co + 1).Style.DateFormat.Format = "yyyy-MM-dd"; ws.Cell(ro, co + 1).DataType = XLCellValues.Text; ws.Cell(++ro, co).Value = "Formatted Number to Text:"; - ws.Cell(ro, co + 1).Value = "12345.6789"; + ws.Cell(ro, co + 1).Value = 12345.6789; ws.Cell(ro, co + 1).Style.NumberFormat.Format = "#,##0.00"; ws.Cell(ro, co + 1).DataType = XLCellValues.Text; ro++; ws.Cell(++ro, co).Value = "Blank Text:"; - ws.Cell(ro, co + 1).Value = "12345.6789"; + ws.Cell(ro, co + 1).Value = 12345.6789; ws.Cell(ro, co + 1).Style.NumberFormat.Format = "#,##0.00"; ws.Cell(ro, co + 1).DataType = XLCellValues.Text; ws.Cell(ro, co + 1).Value = ""; diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/LambdaExpressions.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/LambdaExpressions.cs new file mode 100644 index 0000000..15202d0 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/LambdaExpressions.cs @@ -0,0 +1,39 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; +using System.Drawing; +using ClosedXML; + +namespace ClosedXML_Examples +{ + public class LambdaExpressions + { + public void Create() + { + var workbook = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); + var ws = workbook.Worksheets.GetWorksheet(0); + + // Define a range with the data + var firstDataCell = ws.Cell("B4"); + var lastDataCell = ws.LastCellUsed(); + var rngData = ws.Range(firstDataCell.Address, lastDataCell.Address); + + // Delete all rows where Outcast = false (the 3rd column) + rngData.Rows() // From all rows + .Where(r => !r.Cell(3).GetBoolean()) // where the 3rd cell of each row is false + .ForEach(r => r.Delete()); // delete the row and shift the cells up (the default for rows in a range) + + // Put a light gray background to all text cells + rngData.Cells() // From all cells + .Where(c => c.DataType == XLCellValues.Text) // where the data type is Text + .ForEach(c => c.Style.Fill.BackgroundColor = Color.LightGray); // Fill with a light gray + + // Put a thick border to the bottom of the table (we may have deleted the bottom cells with the border) + rngData.LastRow().Style.Border.BottomBorder = XLBorderStyleValues.Thick; + + workbook.SaveAs(@"C:\Excel Files\Created\LambdaExpressions.xlsx"); + } + } +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MergeCells.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MergeCells.cs index 72ebb06..2ea5444 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MergeCells.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MergeCells.cs @@ -68,7 +68,6 @@ ws.Cell("F2").Style.Alignment.WrapText = true; ws.Range("F2:G8").Column(1).Merge(); - // Merge a range ws.Cell("B4").Value = "Merged Range (B4:D6)"; ws.Cell("B4").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/MovingRanges.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/MovingRanges.cs index 7e3dae9..6540be5 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/MovingRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/MovingRanges.cs @@ -11,8 +11,7 @@ { public void Create() { - var workbook = new XLWorkbook(); - workbook.Load(@"C:\Excel Files\Created\BasicTable.xlsx"); + var workbook = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); var ws = workbook.Worksheets.GetWorksheet(0); // Get a range object diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/DefaultStyles.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/DefaultStyles.cs new file mode 100644 index 0000000..15d4c32 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/DefaultStyles.cs @@ -0,0 +1,41 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; +using System.Drawing; + + +namespace ClosedXML_Examples.Styles +{ + public class DefaultStyles + { + public void Create(String filePath) + { + // The static default values are read-only so even if + // you try to change a referenced type, the changes will be discarded. + var style = XLWorkbook.DefaultStyle; + style.Border.DiagonalUp = true; + style.Border.DiagonalDown = true; + style.Border.DiagonalBorder = XLBorderStyleValues.Thick; + style.Border.DiagonalBorderColor = Color.Red; + + // Create our workbook + var workbook = new XLWorkbook(); + + // This worksheet will have the default style, row height, column width, and page setup + var ws1 = workbook.Worksheets.Add("Default Style"); + + // Change the default row height for all new worksheets in this workbook + workbook.RowHeight = 30; + + var ws2 = workbook.Worksheets.Add("Tall Rows"); + + // Create a worksheet and change the default row height + var ws3 = workbook.Worksheets.Add("Short Rows"); + ws3.RowHeight = 7.5; + + workbook.SaveAs(filePath); + } + } +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index 2462ffe..0f7ef5d 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -17,17 +17,19 @@ ws.Row(1).Style.Fill.BackgroundColor = Color.Red; ws.Cell(1, 1).Value = "Hello"; - // Also test painting a row/column, setting the value of a cell, and then moving it. - // Change Internal references on XLRows/XLColumns so they return the values from Worksheet.Internal.Rows/Columns collection //wb.Load(@"c:\Initial.xlsx"); wb.SaveAs(@"C:\Excel Files\ForTesting\Sandbox.xlsx"); //Console.ReadKey(); } - // Apply a style to the entire sheet (not just the used cells) + // Invalidate range references when they point to a deleted range. + // Implement formulas // Implement grouping of rows and columns - // Adjust rows/columns heights/widths + // Autosize rows/columns + // Save defaults to a .config file + + // Add/Copy/Paste (maybe another name?) rows, columns, ranges into an area. } }