diff --git a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj index 79b7ed2..b1cbc88 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj @@ -50,7 +50,7 @@ - + @@ -66,7 +66,7 @@ - + diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLColumn.cs index 11dcbe1..e6f918b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLColumn.cs @@ -8,6 +8,6 @@ public interface IXLColumn: IXLRange { Double Width { get; set; } - + void Delete(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRange.cs index c5f3b50..0e172a5 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRange.cs @@ -15,8 +15,11 @@ Int32 ColumnNumber { get; } String ColumnLetter { get; } IXLRangeInternals Internals { get; } + //void Delete(XLShiftDeletedCells shiftDeleteCells); } + public enum XLShiftDeletedCells { ShiftCellsUp, ShiftCellsLeft } + public static class IXLRangeMethods { public static IXLCell FirstCell(this IXLRange range) @@ -70,8 +73,15 @@ } public static IXLRange Range(this IXLRange range, String rangeAddress) { - String[] arrRange = rangeAddress.Split(':'); - return range.Range(arrRange[0], arrRange[1]); + if (rangeAddress.Contains(':')) + { + String[] arrRange = rangeAddress.Split(':'); + return range.Range(arrRange[0], arrRange[1]); + } + else + { + return range.Range(rangeAddress, rangeAddress); + } } public static IXLRange Range(this IXLRange range, String firstCellAddress, String lastCellAddress) { @@ -245,10 +255,112 @@ } return retVal; } - - public static void SetAsPrintArea(this IXLRange range) + public static List Columns(this IXLRange range, String columns) { - range.Internals.Worksheet.PrintOptions.PrintArea = range; + var retVal = new List(); + var columnPairs = columns.Split(','); + foreach (var pair in columnPairs) + { + var columnRange = pair.Split(':'); + var firstColumn = columnRange[0]; + var lastColumn = columnRange[1]; + Int32 tmp; + if (Int32.TryParse(firstColumn, out tmp)) + retVal.AddRange(range.Columns(Int32.Parse(firstColumn), Int32.Parse(lastColumn))); + else + retVal.AddRange(range.Columns(firstColumn, lastColumn)); + } + return retVal; + } + public static List Columns(this IXLRange range, String firstColumn, String lastColumn) + { + return range.Columns(XLAddress.GetColumnNumberFromLetter(firstColumn), XLAddress.GetColumnNumberFromLetter(lastColumn)); + } + public static List Columns(this IXLRange range, Int32 firstColumn, Int32 lastColumn) + { + var retVal = new List(); + + for (var co = firstColumn; co <= lastColumn; co++) + { + retVal.Add(range.Column(co)); + } + return retVal; + } + public static List Rows(this IXLRange range) + { + var retVal = new List(); + foreach (var r in Enumerable.Range(1, range.RowCount())) + { + retVal.Add(range.Row(r)); + } + return retVal; + } + public static List Rows(this IXLRange range, String rows) + { + var retVal = new List(); + var rowPairs = rows.Split(','); + foreach (var pair in rowPairs) + { + var rowRange = pair.Split(':'); + var firstRow = rowRange[0]; + var lastRow = rowRange[1]; + retVal.AddRange(range.Rows(Int32.Parse(firstRow), Int32.Parse(lastRow))); + } + return retVal; + } + public static List Rows(this IXLRange range, Int32 firstRow, Int32 lastRow) + { + var retVal = new List(); + + for(var ro = firstRow; ro <= lastRow; ro++) + { + retVal.Add(range.Row(ro)); + } + return retVal; + } + + public static void Clear(this IXLRange range) + { + // Remove cells inside range + range.Internals.Worksheet.Internals.CellsCollection.RemoveAll(c => + c.Address.Column >= range.ColumnNumber + && c.Address.Column <= range.LastColumn().ColumnNumber + && c.Address.Row >= range.RowNumber + && c.Address.Row <= range.LastRow().RowNumber + ); + } + public static void Delete(this IXLRange range, XLShiftDeletedCells shiftDeleteCells) + { + range.Clear(); + + // Range to shift... + var cellsToInsert = new Dictionary(); + var cellsToDelete = new List(); + var shiftLeft = range.Internals.Worksheet.Internals.CellsCollection + .Where(c => c.Key.Column > range.LastColumn().ColumnNumber + && c.Key.Row >= range.RowNumber + && c.Key.Row <= range.LastRow().RowNumber); + + var shiftUp = range.Internals.Worksheet.Internals.CellsCollection + .Where(c => + c.Key.Column >= range.ColumnNumber + && c.Key.Column <= range.LastColumn().ColumnNumber + && c.Key.Row > range.RowNumber); + + foreach (var c in shiftDeleteCells == XLShiftDeletedCells.ShiftCellsLeft ? shiftLeft : shiftUp) + { + var columnModifier = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsLeft ? range.ColumnCount() : 0; + var rowModifier = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp ? range.RowCount() : 0; + var newKey = new XLAddress(c.Key.Row - rowModifier, c.Key.Column - columnModifier); + var newCell = new XLCell(newKey, c.Value.Style); + newCell.Value = c.Value.Value; + cellsToInsert.Add(newKey, newCell); + cellsToDelete.Add(c.Key); + } + cellsToDelete.ForEach(c => range.Internals.Worksheet.Internals.CellsCollection.Remove(c)); + cellsToInsert.ForEach(c => range.Internals.Worksheet.Internals.CellsCollection.Add(c.Key, c.Value)); + + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRow.cs index 2f6a053..a62a0fd 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRow.cs @@ -8,6 +8,7 @@ public interface IXLRow: IXLRange { Double Height { get; set; } + void Delete(); } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs index 61265d9..72b35c5 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs @@ -7,13 +7,19 @@ { public interface IXLWorksheet: IXLRange { - new IXLRow Row(Int32 column); + new IXLRow Row(Int32 row); new IXLColumn Column(Int32 column); new IXLColumn Column(String column); String Name { get; set; } List Columns(); + List Columns(String columns); + List Columns(String firstColumn, String lastColumn); + List Columns(Int32 firstColumn, Int32 lastColumn); + List Rows(); + List Rows(String rows); + List Rows(Int32 firstRow, Int32 lastRow); - IXLPrintOptions PrintOptions { get; } + IXLPageSetup PageSetup { get; } new IXLWorksheetInternals Internals { get; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLHeaderFooter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLHeaderFooter.cs index 23bb3c4..5dddd14 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLHeaderFooter.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLHeaderFooter.cs @@ -11,5 +11,6 @@ IXLHFItem Left { get; } IXLHFItem Center { get; } IXLHFItem Right { get; } + String GetText(XLHFOccurrence occurrence); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLPageOptions.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLPageOptions.cs new file mode 100644 index 0000000..a1776d2 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLPageOptions.cs @@ -0,0 +1,122 @@ +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 + { + List PrintAreas { get; set; } + List RowTitles { get; set; } + void SetRowTitles(List rowTitles); + List ColumnTitles { get; set; } + void SetColumnTitles(List columnTitles); + 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; } + XLMargins 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 AddPageBreak(IXLRow row); + void AddPageBreak(IXLColumn column); + + XLPrintErrorValues PrintErrorValue { get; set; } + + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLPrintOptions.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLPrintOptions.cs deleted file mode 100644 index c1668f3..0000000 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLPrintOptions.cs +++ /dev/null @@ -1,100 +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 interface IXLPrintOptions - { - IXLRange PrintArea { get; set; } - 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 FitTo(Int32 pagesWide, Int32 pagesTall); - XLPaperSize PaperSize { get; set; } - XLMargins Margins { get; } - - IXLHeaderFooter Header { get; } - IXLHeaderFooter Footer { get; } - Boolean ScaleHFWithDocument { get; set; } - Boolean AlignHFWithMargins { get; set; } - } -} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLHFItem.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLHFItem.cs index b6e41d9..a592967 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLHFItem.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLHFItem.cs @@ -20,8 +20,26 @@ { if (text.Length > 0) { - var hfFont = GetHFFont(xlFont); - var newText = hfFont + text; + 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 @@ -41,6 +59,7 @@ 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); @@ -48,11 +67,25 @@ 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(IXLFont xlFont) + private String GetHFFont(String text, IXLFont xlFont) { String retVal = String.Empty; @@ -64,7 +97,16 @@ retVal += xlFont.VerticalAlignment == XLFontVerticalTextAlignmentValues.Superscript ? "&X" : ""; retVal += xlFont.Underline== XLFontUnderlineValues.Single ? "&U" : ""; retVal += xlFont.Underline == XLFontUnderlineValues.Double ? "&E" : ""; - retVal += "&K" + xlFont.FontColor.ToHex(); + 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; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLHeaderFooter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLHeaderFooter.cs index b0f91ee..626779e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLHeaderFooter.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLHeaderFooter.cs @@ -7,29 +7,28 @@ { public class XLHeaderFooter: IXLHeaderFooter { - - public bool ScaleWithDocument + public XLHeaderFooter() { - get - { - throw new NotImplementedException(); - } - set - { - throw new NotImplementedException(); - } + 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 bool AlignWithMargins + public String GetText(XLHFOccurrence occurrence) { - get - { - throw new NotImplementedException(); - } - set - { - throw new NotImplementedException(); - } + 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; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLPageOptions.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLPageOptions.cs new file mode 100644 index 0000000..4b99290 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLPageOptions.cs @@ -0,0 +1,203 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public class XLPageOptions : IXLPageSetup + { + public XLPageOptions(XLPageOptions defaultPrintOptions) + { + this.PrintAreas = new List(); + this.RowTitles = new List(); + this.ColumnTitles = new List(); + if (defaultPrintOptions != null) + { + this.CenterHorizontally = defaultPrintOptions.CenterHorizontally; + this.CenterVertically = defaultPrintOptions.CenterVertically; + this.FirstPageNumber = defaultPrintOptions.FirstPageNumber; + this.HorizontalDpi = defaultPrintOptions.HorizontalDpi; + this.PageOrientation = defaultPrintOptions.PageOrientation; + this.VerticalDpi = defaultPrintOptions.VerticalDpi; + foreach (var printArea in defaultPrintOptions.PrintAreas) + { + this.PrintAreas.Add( + new XLRange( + new XLRangeParameters( + printArea.Internals.FirstCellAddress, + printArea.Internals.LastCellAddress, + printArea.Internals.Worksheet, + printArea.Style) + ) + ); + } + + foreach (var rowTitle in defaultPrintOptions.RowTitles) + { + this.RowTitles.Add( + new XLRow(rowTitle.RowNumber, new XLRowParameters(rowTitle.Internals.Worksheet, rowTitle.Style)) + ); + } + foreach (var columnTitle in defaultPrintOptions.ColumnTitles) + { + this.ColumnTitles.Add( + new XLColumn(columnTitle.ColumnNumber, new XLColumnParameters(columnTitle.Internals.Worksheet, columnTitle.Style)) + ); + } + this.PaperSize = defaultPrintOptions.PaperSize; + this.pagesTall = defaultPrintOptions.pagesTall; + this.pagesWide = defaultPrintOptions.pagesWide; + this.scale = defaultPrintOptions.scale; + + if (defaultPrintOptions.Margins != null) + { + this.Margins = new XLMargins() + { + Top = defaultPrintOptions.Margins.Top, + Bottom = defaultPrintOptions.Margins.Bottom, + Left = defaultPrintOptions.Margins.Left, + Right = defaultPrintOptions.Margins.Right, + Header = defaultPrintOptions.Margins.Header, + Footer = defaultPrintOptions.Margins.Footer + }; + } + this.AlignHFWithMargins = defaultPrintOptions.AlignHFWithMargins; + this.ScaleHFWithDocument = defaultPrintOptions.ScaleHFWithDocument; + this.ShowGridlines = defaultPrintOptions.ShowGridlines; + this.ShowRowAndColumnHeadings = defaultPrintOptions.ShowRowAndColumnHeadings; + this.BlackAndWhite = defaultPrintOptions.BlackAndWhite; + this.DraftQuality = defaultPrintOptions.DraftQuality; + this.PageOrder = defaultPrintOptions.PageOrder; + + this.ColumnBreaks = new List(); + this.RowBreaks = new List(); + this.PrintErrorValue = defaultPrintOptions.PrintErrorValue; + } + Header = new XLHeaderFooter(); + Footer = new XLHeaderFooter(); + } + public List PrintAreas { get; set; } + public List RowTitles { get; set; } + public List ColumnTitles { get; set; } + public void SetRowTitles(List rowTitles) + { + RowTitles.Clear(); + RowTitles.AddRange(rowTitles); + } + public void SetColumnTitles(List columnTitles) + { + ColumnTitles.Clear(); + ColumnTitles.AddRange(columnTitles); + } + 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 XLMargins 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 AddPageBreak(IXLRow row) + { + RowBreaks.Add(row); + } + public void AddPageBreak(IXLColumn 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/XLPrintOptions.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLPrintOptions.cs deleted file mode 100644 index bb305f7..0000000 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLPrintOptions.cs +++ /dev/null @@ -1,122 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; - -namespace ClosedXML.Excel -{ - public class XLPrintOptions : IXLPrintOptions - { - public XLPrintOptions(XLPrintOptions defaultPrintOptions) - { - if (defaultPrintOptions != null) - { - this.CenterHorizontally = defaultPrintOptions.CenterHorizontally; - this.CenterVertically = defaultPrintOptions.CenterVertically; - this.FirstPageNumber = defaultPrintOptions.FirstPageNumber; - this.HorizontalDpi = defaultPrintOptions.HorizontalDpi; - this.PageOrientation = defaultPrintOptions.PageOrientation; - this.VerticalDpi = defaultPrintOptions.VerticalDpi; - if (defaultPrintOptions.PrintArea != null) - { - this.PrintArea = new XLRange( - new XLRangeParameters( - defaultPrintOptions.PrintArea.Internals.FirstCellAddress, - defaultPrintOptions.PrintArea.Internals.LastCellAddress, - defaultPrintOptions.PrintArea.Internals.Worksheet, - defaultPrintOptions.PrintArea.Style) - ); - } - this.PaperSize = defaultPrintOptions.PaperSize; - this.pagesTall = defaultPrintOptions.pagesTall; - this.pagesWide = defaultPrintOptions.pagesWide; - this.scale = defaultPrintOptions.scale; - - if (defaultPrintOptions.Margins != null) - { - this.Margins = new XLMargins() - { - Top = defaultPrintOptions.Margins.Top, - Bottom = defaultPrintOptions.Margins.Bottom, - Left = defaultPrintOptions.Margins.Left, - Right = defaultPrintOptions.Margins.Right, - Header = defaultPrintOptions.Margins.Header, - Footer = defaultPrintOptions.Margins.Footer - }; - } - - if (defaultPrintOptions.HeaderFooters != null) - { - //HeaderFooters = new XLHeaderFooter(); - } - } - } - public IXLRange PrintArea { get; set; } - 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 XLMargins Margins { get; set; } - public IXLHeaderFooter HeaderFooters { get; private set; } - - private Int32 pagesWide; - public Int32 PagesWide - { - get - { - return pagesWide; - } - set - { - pagesWide = value; - scale = 0; - } - } - - private Int32 pagesTall; - public Int32 PagesTall - { - get - { - return pagesTall; - } - set - { - pagesTall = value; - scale = 0; - } - } - - private Int32 scale; - public Int32 Scale - { - get - { - return scale; - } - set - { - scale = value; - pagesTall = 0; - pagesWide = 0; - } - } - - public void AdjustTo(Int32 pctOfNormalSize) - { - Scale = pctOfNormalSize; - pagesWide = 0; - pagesTall = 0; - } - public void FitTo(Int32 pagesWide, Int32 pagesTall) - { - this.pagesWide = pagesWide; - this.pagesTall = pagesTall; - Scale = 0; - } - } -} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs index 7256711..96c90d7 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs @@ -9,7 +9,25 @@ public class XLFont: IXLFont { IXLStylized container; - public XLFont(IXLStylized container = null, IXLFont defaultFont = null) + public XLFont() + { + IXLFont defaultFont = XLWorkbook.DefaultStyle.Font; + + if (defaultFont != null) + { + bold = defaultFont.Bold; + italic = defaultFont.Italic; + underline = defaultFont.Underline; + strikethrough = defaultFont.Strikethrough; + verticalAlignment = defaultFont.VerticalAlignment; + shadow = defaultFont.Shadow; + fontSize = defaultFont.FontSize; + fontColor = defaultFont.FontColor; + fontName = defaultFont.FontName; + fontFamilyNumbering = defaultFont.FontFamilyNumbering; + } + } + public XLFont(IXLStylized container, IXLFont defaultFont = null) { this.container = container; if (defaultFont != null) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLColumn.cs index 705bcc3..70a15b7 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLColumn.cs @@ -22,6 +22,10 @@ public Int32 RowNumber { get; private set; } public Int32 ColumnNumber { get; private set; } public String ColumnLetter { get; private set; } + public void Delete() + { + this.Column(ColumnNumber).Delete(XLShiftDeletedCells.ShiftCellsLeft); + } #region IXLStylized Members diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLRow.cs index b20514d..28506a4 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLRow.cs @@ -22,7 +22,12 @@ public Int32 RowNumber { get; private set; } public Int32 ColumnNumber { get; private set; } public String ColumnLetter { get; private set; } + public void Delete() + { + this.Row(RowNumber).Delete(XLShiftDeletedCells.ShiftCellsUp); + } + #region IXLRange Members public IXLRangeInternals Internals { get; private set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs index 6990031..df92afe 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs @@ -112,14 +112,14 @@ public static Double DefaultRowHeight { get; set; } public static Double DefaultColumnWidth { get; set; } - public static XLPrintOptions defaultPrintOptions; - public static XLPrintOptions DefaultPrintOptions + public static XLPageOptions defaultPrintOptions; + public static XLPageOptions DefaultPrintOptions { get { if (defaultPrintOptions == null) { - defaultPrintOptions = new XLPrintOptions(null) + defaultPrintOptions = new XLPageOptions(null) { PageOrientation = XLPageOrientation.Default, Scale = 100, @@ -132,7 +132,11 @@ Right = 0.75, Header = 0.75, Footer = 0.75 - } + }, + ScaleHFWithDocument = true, + AlignHFWithMargins = true, + PrintErrorValue = XLPrintErrorValues.Displayed, + ShowComments = XLShowCommentsValues.None }; } return defaultPrintOptions; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index a6beecd..25a1588 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -177,6 +177,40 @@ } } + private PageOrderValues GetPageOrderValue(XLPageOrderValues pageOrderValue) + { + switch (pageOrderValue) + { + case XLPageOrderValues.DownThenOver: return PageOrderValues.DownThenOver; + case XLPageOrderValues.OverThenDown: return PageOrderValues.OverThenDown; + default: throw new NotImplementedException(); + } + } + + private CellCommentsValues GetCellCommentValue(XLShowCommentsValues showCommentsValue) + { + switch (showCommentsValue) + { + case XLShowCommentsValues.AsDisplayed: return CellCommentsValues.AsDisplayed; + case XLShowCommentsValues.AtEnd: return CellCommentsValues.AtEnd; + case XLShowCommentsValues.None: return CellCommentsValues.None; + default: throw new NotImplementedException(); + } + } + + private PrintErrorValues GetPrintErrorValue(XLPrintErrorValues printErrorValues) + { + switch (printErrorValues) + { + case XLPrintErrorValues.Blank: return PrintErrorValues.Blank; + case XLPrintErrorValues.Dash: return PrintErrorValues.Dash; + case XLPrintErrorValues.Displayed: return PrintErrorValues.Displayed; + case XLPrintErrorValues.NA: return PrintErrorValues.NA; + default: throw new NotImplementedException(); + } + } + + // Creates a SpreadsheetDocument. private void CreatePackage(String filePath) { @@ -252,7 +286,7 @@ Vt.Variant variant4 = new Vt.Variant(); Vt.VTInt32 vTInt322 = new Vt.VTInt32(); - vTInt322.Text = Worksheets.Count().ToString(); + vTInt322.Text = (Worksheets.Count() * 2).ToString(); variant4.Append(vTInt322); @@ -266,7 +300,7 @@ Ap.TitlesOfParts titlesOfParts1 = new Ap.TitlesOfParts(); UInt32 sheetCount = (UInt32)Worksheets.Count(); - Vt.VTVector vTVector2 = new Vt.VTVector() { BaseType = Vt.VectorBaseValues.Lpstr, Size = (UInt32Value)sheetCount * 2 }; + Vt.VTVector vTVector2 = new Vt.VTVector() { BaseType = Vt.VectorBaseValues.Lpstr, Size = (UInt32Value)sheetCount * 3 }; foreach (var worksheet in Worksheets) { Vt.VTLPSTR vTLPSTR3 = new Vt.VTLPSTR(); @@ -276,6 +310,10 @@ Vt.VTLPSTR vTLPSTR4 = new Vt.VTLPSTR(); vTLPSTR4.Text = worksheet.Name + "!Print_Area"; vTVector2.Append(vTLPSTR4); + + Vt.VTLPSTR vTLPSTR5 = new Vt.VTLPSTR(); + vTLPSTR5.Text = worksheet.Name + "!Print_Titles"; + vTVector2.Append(vTLPSTR5); } titlesOfParts1.Append(vTVector2); @@ -325,19 +363,60 @@ Sheet sheet = new Sheet() { Name = worksheet.Name, SheetId = (UInt32Value)sheetId, Id = "rId" + sheetId.ToString() }; sheets.Append(sheet); - if (worksheet.PrintOptions.PrintArea == null) + if (worksheet.PageSetup.PrintAreas.Count == 0) { var minCell = worksheet.Internals.CellsCollection.Min(c => c.Key); var maxCell = worksheet.Internals.CellsCollection.Max(c => c.Key); if (minCell != null && maxCell != null) - worksheet.PrintOptions.PrintArea = worksheet.Range(minCell, maxCell); + worksheet.PageSetup.PrintAreas.Add(worksheet.Range(minCell, maxCell)); } - if (worksheet.PrintOptions.PrintArea != null) + if (worksheet.PageSetup.PrintAreas.Count > 0) { DefinedName definedName = new DefinedName() { Name = "_xlnm.Print_Area", LocalSheetId = (UInt32Value)sheetId - 1 }; - definedName.Text = "'" + worksheet.Name + "'!" - + worksheet.PrintOptions.PrintArea.Internals.FirstCellAddress.ToString() - + ":" + worksheet.PrintOptions.PrintArea.Internals.LastCellAddress.ToString(); + var definedNameText = String.Empty; + foreach (var printArea in worksheet.PageSetup.PrintAreas) + { + definedNameText += "'" + worksheet.Name + "'!" + + printArea.Internals.FirstCellAddress.ToString() + + ":" + printArea.Internals.LastCellAddress.ToString() + ","; + } + + definedName.Text = definedNameText.Substring(0, definedNameText.Length - 1); + definedNames.Append(definedName); + } + + var titles = String.Empty; + var definedNameTextRow = String.Empty; + var definedNameTextColumn = String.Empty; + if (worksheet.PageSetup.RowTitles.Count > 0) + { + definedNameTextRow = "'" + worksheet.Name + "'!$" + worksheet.PageSetup.RowTitles.Min(r => r.RowNumber).ToString() + + ":$" + worksheet.PageSetup.RowTitles.Max(r => r.RowNumber).ToString(); + } + if (worksheet.PageSetup.ColumnTitles.Count > 0) + { + var minColumn = worksheet.PageSetup.ColumnTitles.Min(r => r.ColumnNumber); + var maxColumn = worksheet.PageSetup.ColumnTitles.Max(r => r.ColumnNumber); + definedNameTextColumn = "'" + worksheet.Name + "'!$" + XLAddress.GetColumnLetterFromNumber(minColumn) + + ":$" + XLAddress.GetColumnLetterFromNumber(maxColumn); + } + + if (definedNameTextColumn.Length > 0) + { + titles = definedNameTextColumn; + if (definedNameTextRow.Length > 0) + titles += "," + definedNameTextRow; + } + else + { + titles = definedNameTextRow; + } + + + if (titles.Length > 0) + { + DefinedName definedName = new DefinedName() { Name = "_xlnm.Print_Titles", LocalSheetId = (UInt32Value)sheetId - 1 }; + definedName.Text = titles; definedNames.Append(definedName); } } @@ -643,12 +722,9 @@ Worksheet worksheet = new Worksheet(); worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); SheetProperties sheetProperties = new SheetProperties() { CodeName = xlWorksheet.Name.RemoveSpecialCharacters() }; - if (xlWorksheet.PrintOptions.PagesTall >= 0 || xlWorksheet.PrintOptions.PagesWide >= 0) + if (xlWorksheet.PageSetup.PagesTall > 0 || xlWorksheet.PageSetup.PagesWide > 0) { - PageSetupProperties pageSetupProperties = new PageSetupProperties(); - if (xlWorksheet.PrintOptions.PagesWide > 0 || xlWorksheet.PrintOptions.PagesTall > 0) - pageSetupProperties.FitToPage = true; - + PageSetupProperties pageSetupProperties = new PageSetupProperties() { FitToPage = true }; sheetProperties.Append(pageSetupProperties); } @@ -784,69 +860,111 @@ } PageMargins pageMargins = new PageMargins() { - Left = xlWorksheet.PrintOptions.Margins.Left, - Right = xlWorksheet.PrintOptions.Margins.Right, - Top = xlWorksheet.PrintOptions.Margins.Top, - Bottom = xlWorksheet.PrintOptions.Margins.Bottom, - Header = xlWorksheet.PrintOptions.Margins.Header, - Footer = xlWorksheet.PrintOptions.Margins.Footer + Left = xlWorksheet.PageSetup.Margins.Left, + Right = xlWorksheet.PageSetup.Margins.Right, + Top = xlWorksheet.PageSetup.Margins.Top, + Bottom = xlWorksheet.PageSetup.Margins.Bottom, + Header = xlWorksheet.PageSetup.Margins.Header, + Footer = xlWorksheet.PageSetup.Margins.Footer }; + + + //Drawing drawing1 = new Drawing() { Id = "rId1" }; PageSetup pageSetup1 = new PageSetup() { - Orientation = GetOrientationValue(xlWorksheet.PrintOptions.PageOrientation), + Orientation = GetOrientationValue(xlWorksheet.PageSetup.PageOrientation), Id = "rId" + RelId.GetNext(RelType.Worksheet), - PaperSize = (UInt32Value)(UInt32)xlWorksheet.PrintOptions.PaperSize + PaperSize = (UInt32Value)(UInt32)xlWorksheet.PageSetup.PaperSize, + BlackAndWhite = xlWorksheet.PageSetup.BlackAndWhite, + Draft = xlWorksheet.PageSetup.DraftQuality, + PageOrder = GetPageOrderValue(xlWorksheet.PageSetup.PageOrder), + CellComments = GetCellCommentValue(xlWorksheet.PageSetup.ShowComments), + Errors = GetPrintErrorValue(xlWorksheet.PageSetup.PrintErrorValue) }; - if (xlWorksheet.PrintOptions.FirstPageNumber > 0) + if (xlWorksheet.PageSetup.FirstPageNumber > 0) { - pageSetup1.FirstPageNumber = (UInt32Value)(UInt32)xlWorksheet.PrintOptions.FirstPageNumber; + pageSetup1.FirstPageNumber = (UInt32Value)(UInt32)xlWorksheet.PageSetup.FirstPageNumber; pageSetup1.UseFirstPageNumber = true; } - if (xlWorksheet.PrintOptions.HorizontalDpi > 0) - pageSetup1.HorizontalDpi = (UInt32Value)(UInt32)xlWorksheet.PrintOptions.HorizontalDpi; + if (xlWorksheet.PageSetup.HorizontalDpi > 0) + pageSetup1.HorizontalDpi = (UInt32Value)(UInt32)xlWorksheet.PageSetup.HorizontalDpi; - if (xlWorksheet.PrintOptions.VerticalDpi > 0) - pageSetup1.VerticalDpi = (UInt32Value)(UInt32)xlWorksheet.PrintOptions.VerticalDpi; + if (xlWorksheet.PageSetup.VerticalDpi > 0) + pageSetup1.VerticalDpi = (UInt32Value)(UInt32)xlWorksheet.PageSetup.VerticalDpi; - if (xlWorksheet.PrintOptions.Scale > 0) + if (xlWorksheet.PageSetup.Scale > 0) { - pageSetup1.Scale = (UInt32Value)(UInt32)xlWorksheet.PrintOptions.Scale; + pageSetup1.Scale = (UInt32Value)(UInt32)xlWorksheet.PageSetup.Scale; } else { - if (xlWorksheet.PrintOptions.PagesWide > 0) - pageSetup1.FitToWidth = (UInt32Value)(UInt32)xlWorksheet.PrintOptions.PagesWide; - if (xlWorksheet.PrintOptions.PagesTall > 0) - pageSetup1.FitToHeight = (UInt32Value)(UInt32)xlWorksheet.PrintOptions.PagesTall; + if (xlWorksheet.PageSetup.PagesWide > 0) + pageSetup1.FitToWidth = (UInt32Value)(UInt32)xlWorksheet.PageSetup.PagesWide; + if (xlWorksheet.PageSetup.PagesTall > 0) + pageSetup1.FitToHeight = (UInt32Value)(UInt32)xlWorksheet.PageSetup.PagesTall; } - PrintOptions printOptions = new PrintOptions() { - HorizontalCentered = xlWorksheet.PrintOptions.CenterHorizontally, - VerticalCentered = xlWorksheet.PrintOptions.CenterVertically }; + PrintOptions printOptions = new PrintOptions() + { + HorizontalCentered = xlWorksheet.PageSetup.CenterHorizontally, + VerticalCentered = xlWorksheet.PageSetup.CenterVertically, + Headings = xlWorksheet.PageSetup.ShowRowAndColumnHeadings, + GridLines = xlWorksheet.PageSetup.ShowGridlines + }; HeaderFooter headerFooter = new HeaderFooter(); - headerFooter.ScaleWithDoc = xlWorksheet.PrintOptions.ScaleHFWithDocument; - headerFooter.AlignWithMargins = xlWorksheet.PrintOptions.AlignHFWithMargins; + headerFooter.ScaleWithDoc = xlWorksheet.PageSetup.ScaleHFWithDocument; + headerFooter.AlignWithMargins = xlWorksheet.PageSetup.AlignHFWithMargins; + headerFooter.DifferentFirst = true; + headerFooter.DifferentOddEven = true; - FirstHeader firstHeader = new FirstHeader("&L" + xlWorksheet.PrintOptions.Header.Left.GetText(XLHFOccurrence.FirstPage) + "&C" + xlWorksheet.PrintOptions.Header.Center.GetText(XLHFOccurrence.FirstPage) + "&R" + xlWorksheet.PrintOptions.Header.Right.GetText(XLHFOccurrence.FirstPage) + ""); - headerFooter.Append(firstHeader); - OddHeader oddHeader = new OddHeader("&L" + xlWorksheet.PrintOptions.Header.Left.GetText(XLHFOccurrence.OddPages) + "&C" + xlWorksheet.PrintOptions.Header.Center.GetText(XLHFOccurrence.OddPages) + "&R" + xlWorksheet.PrintOptions.Header.Right.GetText(XLHFOccurrence.OddPages) + ""); - headerFooter.Append(oddHeader); - EvenHeader evenHeader = new EvenHeader("&L" + xlWorksheet.PrintOptions.Header.Left.GetText(XLHFOccurrence.EvenPages) + "&C" + xlWorksheet.PrintOptions.Header.Center.GetText(XLHFOccurrence.EvenPages) + "&R" + xlWorksheet.PrintOptions.Header.Right.GetText(XLHFOccurrence.EvenPages) + ""); - headerFooter.Append(evenHeader); - - FirstFooter firstFooter = new FirstFooter("&L" + xlWorksheet.PrintOptions.Footer.Left.GetText(XLHFOccurrence.FirstPage) + "&C" + xlWorksheet.PrintOptions.Footer.Center.GetText(XLHFOccurrence.FirstPage) + "&R" + xlWorksheet.PrintOptions.Footer.Right.GetText(XLHFOccurrence.FirstPage) + ""); - headerFooter.Append(firstFooter); - OddFooter oddFooter = new OddFooter("&L" + xlWorksheet.PrintOptions.Footer.Left.GetText(XLHFOccurrence.OddPages) + "&C" + xlWorksheet.PrintOptions.Footer.Center.GetText(XLHFOccurrence.OddPages) + "&R" + xlWorksheet.PrintOptions.Footer.Right.GetText(XLHFOccurrence.OddPages) + ""); - headerFooter.Append(oddFooter); - EvenFooter evenFooter = new EvenFooter("&L" + xlWorksheet.PrintOptions.Footer.Left.GetText(XLHFOccurrence.EvenPages) + "&C" + xlWorksheet.PrintOptions.Footer.Center.GetText(XLHFOccurrence.EvenPages) + "&R" + xlWorksheet.PrintOptions.Footer.Right.GetText(XLHFOccurrence.EvenPages) + ""); - headerFooter.Append(evenFooter); + OddHeader oddHeader = new OddHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.OddPages)); + headerFooter.Append(oddHeader); + OddFooter oddFooter = new OddFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.OddPages)); + headerFooter.Append(oddFooter); + EvenHeader evenHeader = new EvenHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.EvenPages)); + headerFooter.Append(evenHeader); + EvenFooter evenFooter = new EvenFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.EvenPages)); + headerFooter.Append(evenFooter); + //var firstHeaderText = "&L" + xlWorksheet.PageSetup.Header.Left.GetText(XLHFOccurrence.FirstPage) + "&C" + xlWorksheet.PageSetup.Header.Center.GetText(XLHFOccurrence.FirstPage) + "&R" + xlWorksheet.PageSetup.Header.Right.GetText(XLHFOccurrence.FirstPage) + ""; + + FirstHeader firstHeader = new FirstHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.FirstPage)); + headerFooter.Append(firstHeader); + FirstFooter firstFooter = new FirstFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.FirstPage)); + headerFooter.Append(firstFooter); + + RowBreaks rowBreaks = null; + var rowBreakCount = xlWorksheet.PageSetup.RowBreaks.Count; + if (rowBreakCount > 0) + { + rowBreaks = new RowBreaks() { Count = (UInt32Value)(UInt32)rowBreakCount, ManualBreakCount = (UInt32)rowBreakCount }; + foreach (var rb in xlWorksheet.PageSetup.RowBreaks) + { + Break break1 = new Break() { Id = (UInt32Value)(UInt32)rb.RowNumber, Max = (UInt32Value)(UInt32)rb.ColumnCount(), ManualPageBreak = true }; + rowBreaks.Append(break1); + } + + } + + ColumnBreaks columnBreaks = null; + var columnBreakCount = xlWorksheet.PageSetup.ColumnBreaks.Count; + if (columnBreakCount > 0) + { + columnBreaks = new ColumnBreaks() { Count = (UInt32Value)(UInt32)columnBreakCount, ManualBreakCount = (UInt32Value)(UInt32)columnBreakCount }; + foreach (var cb in xlWorksheet.PageSetup.ColumnBreaks) + { + Break break1 = new Break() { Id = (UInt32Value)(UInt32)cb.ColumnNumber, Max = (UInt32Value)(UInt32)cb.RowCount(), ManualPageBreak = true }; + columnBreaks.Append(break1); + } + + } + worksheet.Append(sheetProperties); worksheet.Append(sheetDimension); worksheet.Append(sheetViews); @@ -858,6 +976,8 @@ worksheet.Append(pageMargins); worksheet.Append(pageSetup1); worksheet.Append(headerFooter); + if (rowBreaks != null) worksheet.Append(rowBreaks); + if (columnBreaks != null) worksheet.Append(columnBreaks); //worksheet.Append(drawing1); worksheetPart.Worksheet = worksheet; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index 743af71..c486d9b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -22,7 +22,7 @@ RowNumber = 1; ColumnNumber = 1; ColumnLetter = "A"; - PrintOptions = new XLPrintOptions(XLWorkbook.DefaultPrintOptions); + PageSetup = new XLPageOptions(XLWorkbook.DefaultPrintOptions); this.Name = sheetName; } @@ -52,8 +52,85 @@ return retVal; } + public List Columns(String columns) + { + var retVal = new List(); + var columnPairs = columns.Split(','); + foreach (var pair in columnPairs) + { + var columnRange = pair.Split(':'); + var firstColumn = columnRange[0]; + var lastColumn = columnRange[1]; + Int32 tmp; + if (Int32.TryParse(firstColumn, out tmp)) + retVal.AddRange(Columns(Int32.Parse(firstColumn), Int32.Parse(lastColumn))); + else + retVal.AddRange(Columns(firstColumn, lastColumn)); + } + return retVal; + } + public List Columns(String firstColumn, String lastColumn) + { + return Columns(XLAddress.GetColumnNumberFromLetter(firstColumn), XLAddress.GetColumnNumberFromLetter(lastColumn)); + } + public List Columns(Int32 firstColumn, Int32 lastColumn) + { + var retVal = new List(); + + for (var co = firstColumn; co <= lastColumn; co++) + { + retVal.Add(Column(co)); + } + return retVal; + } + + public List Rows() + { + var retVal = new List(); + var rowList = new List(); + + if (Internals.CellsCollection.Count > 0) + rowList.AddRange(Internals.CellsCollection.Keys.Select(k => k.Row).Distinct()); + + if (Internals.ColumnsCollection.Count > 0) + rowList.AddRange(Internals.ColumnsCollection.Keys.Where(r => !rowList.Contains(r))); + + foreach (var r in rowList) + { + retVal.Add(Row(r)); + } + + return retVal; + } + public List Rows(String rows) + { + var retVal = new List(); + var rowPairs = rows.Split(','); + foreach (var pair in rowPairs) + { + var rowRange = pair.Split(':'); + var firstRow = rowRange[0]; + var lastRow = rowRange[1]; + retVal.AddRange(Rows(Int32.Parse(firstRow), Int32.Parse(lastRow))); + } + return retVal; + } + public List Rows(Int32 firstRow, Int32 lastRow) + { + var retVal = new List(); + + for (var ro = firstRow; ro <= lastRow; ro++) + { + retVal.Add(Row(ro)); + } + return retVal; + } - public IXLRange PrintArea { get; set; } + + public IEnumerable Cells() + { + return Internals.CellsCollection.Values.AsEnumerable(); + } #endregion @@ -81,6 +158,7 @@ { yield return c.Style; } + UpdatingStyle = false; } } @@ -153,7 +231,7 @@ public String Name { get; set; } - public IXLPrintOptions PrintOptions { get; private set; } + public IXLPageSetup PageSetup { get; private set; } IXLRangeInternals IXLRange.Internals diff --git a/ClosedXML/ClosedXML/ClosedXML/Extensions.cs b/ClosedXML/ClosedXML/ClosedXML/Extensions.cs index 70bb1fe..6522dbd 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Extensions.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Extensions.cs @@ -62,5 +62,18 @@ } } + + public static class DictionaryExtensions + { + public static void RemoveAll(this Dictionary dic, + Func predicate) + { + var keys = dic.Keys.Where(k => predicate(dic[k])).ToList(); + foreach (var key in keys) + { + dic.Remove(key); + } + } + } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj index 21b5975..4ac7a71 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -49,8 +49,17 @@ - + + + + + + + + + + @@ -73,6 +82,7 @@ ClosedXML +