diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index cd279bc..28a9ff2 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -133,10 +133,26 @@ var asRange = rangeObject as XLRangeBase; if (asRange != null) { - worksheet.Range(Address.RowNumber, Address.ColumnNumber, asRange.RowCount(), asRange.ColumnCount()).Clear(); - for (var ro = 1; ro <= asRange.RowCount(); ro++) + Int32 maxRows; + Int32 maxColumns; + if (asRange is XLRow || asRange is XLColumn) { - for (var co = 1; co <= asRange.RowCount(); co++) + var lastCellUsed = asRange.LastCellUsed(); + maxRows = lastCellUsed.Address.RowNumber; + maxColumns = lastCellUsed.Address.ColumnNumber; + //if (asRange is XLRow) + // worksheet.Range(Address.RowNumber, Address.ColumnNumber, , maxColumns).Clear(); + } + else + { + maxRows = asRange.RowCount(); + maxColumns = asRange.ColumnCount(); + worksheet.Range(Address.RowNumber, Address.ColumnNumber, maxRows, maxColumns).Clear(); + } + + for (var ro = 1; ro <= maxRows; ro++) + { + for (var co = 1; co <= maxColumns; co++) { var sourceCell = asRange.Cell(ro, co); var targetCell = worksheet.Cell(Address.RowNumber + ro - 1, Address.ColumnNumber + co - 1); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLAlignment.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLAlignment.cs index d5a3e45..b77ff65 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLAlignment.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLAlignment.cs @@ -218,17 +218,26 @@ public override string ToString() { - return - Horizontal.ToString() - + "-" + Vertical.ToString() - + "-" + Indent.ToString() - + "-" + JustifyLastLine.ToString() - + "-" + ReadingOrder.ToString() - + "-" + RelativeIndent.ToString() - + "-" + ShrinkToFit.ToString() - + "-" + TextRotation.ToString() - + "-" + WrapText.ToString() - ; + var sb = new StringBuilder(); + sb.Append(Horizontal); + sb.Append("-"); + sb.Append(Vertical); + sb.Append("-"); + sb.Append(Indent); + sb.Append("-"); + sb.Append(JustifyLastLine); + sb.Append("-"); + sb.Append(ReadingOrder); + sb.Append("-"); + sb.Append(RelativeIndent); + sb.Append("-"); + sb.Append(ShrinkToFit); + sb.Append("-"); + sb.Append(TextRotation); + sb.Append("-"); + sb.Append(WrapText); + sb.Append("-"); + return sb.ToString(); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLBorder.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLBorder.cs index af96d16..8ce1366 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLBorder.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLBorder.cs @@ -222,20 +222,31 @@ public override string ToString() { - return - LeftBorder.ToString() + "-" + - LeftBorderColor.ToString() + "-" + - RightBorder.ToString() + "-" + - RightBorderColor.ToString() + "-" + - TopBorder.ToString() + "-" + - TopBorderColor.ToString() + "-" + - BottomBorder.ToString() + "-" + - BottomBorderColor.ToString() + "-" + - DiagonalBorder.ToString() + "-" + - DiagonalBorderColor.ToString() + "-" + - DiagonalUp.ToString() + "-" + - DiagonalDown.ToString(); - + var sb = new StringBuilder(); + sb.Append(LeftBorder.ToString()); + sb.Append("-"); + sb.Append(LeftBorderColor.ToString()); + sb.Append("-"); + sb.Append(RightBorder.ToString()); + sb.Append("-"); + sb.Append(RightBorderColor.ToString()); + sb.Append("-"); + sb.Append(TopBorder.ToString()); + sb.Append("-"); + sb.Append(TopBorderColor.ToString()); + sb.Append("-"); + sb.Append(BottomBorder.ToString()); + sb.Append("-"); + sb.Append(BottomBorderColor.ToString()); + sb.Append("-"); + sb.Append(DiagonalBorder.ToString()); + sb.Append("-"); + sb.Append(DiagonalBorderColor.ToString()); + sb.Append("-"); + sb.Append(DiagonalUp.ToString()); + sb.Append("-"); + sb.Append(DiagonalDown.ToString()); + return sb.ToString(); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFill.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFill.cs index a889d1f..9830f68 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFill.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFill.cs @@ -101,7 +101,13 @@ public override string ToString() { - return BackgroundColor.ToString() + "-" + PatternType.ToString() + "-" + PatternColor.ToString(); + var sb = new StringBuilder(); + sb.Append(BackgroundColor.ToString()); + sb.Append("-"); + sb.Append(PatternType.ToString()); + sb.Append("-"); + sb.Append(PatternColor.ToString()); + return sb.ToString(); } #endregion diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLNumberFormat.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLNumberFormat.cs index 883bda2..f52eccc 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLNumberFormat.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLNumberFormat.cs @@ -67,7 +67,7 @@ public override string ToString() { - return numberFormatId.ToString() + "-" + format.ToString(); + return numberFormatId.ToString() + "-" + format; } #endregion diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLStyle.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLStyle.cs index aabe7e0..ea2678a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLStyle.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLStyle.cs @@ -55,12 +55,18 @@ public override string ToString() { - return - "Font:" + Font.ToString() - + " Fill:" + Fill.ToString() - + " Border:" + Border.ToString() - + " NumberFormat: " + NumberFormat.ToString() - + " Alignment: " + Alignment.ToString(); + var sb = new StringBuilder(); + sb.Append("Font:"); + sb.Append(Font.ToString()); + sb.Append(" Fill:"); + sb.Append(Fill.ToString()); + sb.Append(" Border:"); + sb.Append(Border.ToString()); + sb.Append(" NumberFormat: "); + sb.Append(NumberFormat.ToString()); + sb.Append(" Alignment: "); + sb.Append(Alignment.ToString()); + return sb.ToString(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index 918746d..2a4c7d4 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -222,9 +222,13 @@ if (printOptionsQuery.Count() > 1) { var printOptions = (PrintOptions)printOptionsQuery.First(); + if (printOptions.GridLines != null) ws.PageSetup.ShowGridlines = printOptions.GridLines; + if (printOptions.HorizontalCentered != null) ws.PageSetup.CenterHorizontally = printOptions.HorizontalCentered; + if (printOptions.VerticalCentered != null) ws.PageSetup.CenterVertically = printOptions.VerticalCentered; + if (printOptions.Headings != null) ws.PageSetup.ShowRowAndColumnHeadings = printOptions.Headings; } @@ -232,11 +236,17 @@ if (pageMarginsQuery.Count() > 0) { var pageMargins = (PageMargins)pageMarginsQuery.First(); + if (pageMargins.Bottom != null) ws.PageSetup.Margins.Bottom = pageMargins.Bottom; + if (pageMargins.Footer != null) ws.PageSetup.Margins.Footer = pageMargins.Footer; + if (pageMargins.Header != null) ws.PageSetup.Margins.Header = pageMargins.Header; + if (pageMargins.Left != null) ws.PageSetup.Margins.Left = pageMargins.Left; + if (pageMargins.Right != null) ws.PageSetup.Margins.Right = pageMargins.Right; + if (pageMargins.Top != null) ws.PageSetup.Margins.Top = pageMargins.Top; } @@ -244,20 +254,30 @@ if (pageSetupQuery.Count() > 0) { var pageSetup = (PageSetup)pageSetupQuery.First(); - ws.PageSetup.PaperSize = (XLPaperSize)Int32.Parse(pageSetup.PaperSize.InnerText); + if (pageSetup.PaperSize !=null) + ws.PageSetup.PaperSize = (XLPaperSize)Int32.Parse(pageSetup.PaperSize.InnerText); if (pageSetup.Scale != null) { ws.PageSetup.Scale = Int32.Parse(pageSetup.Scale.InnerText); } else { - ws.PageSetup.FitToPages(Int32.Parse(pageSetup.FitToWidth.InnerText), Int32.Parse(pageSetup.FitToHeight.InnerText)); + if (pageSetup.FitToWidth != null) + ws.PageSetup.PagesWide = Int32.Parse(pageSetup.FitToWidth.InnerText); + if (pageSetup.FitToHeight != null) + ws.PageSetup.PagesTall = Int32.Parse(pageSetup.FitToHeight.InnerText); } + if (pageSetup.PageOrder != null) ws.PageSetup.PageOrder = pageOrderValues.Single(p => p.Value == pageSetup.PageOrder).Key; + if (pageSetup.Orientation != null) ws.PageSetup.PageOrientation = pageOrientationValues.Single(p => p.Value == pageSetup.Orientation).Key; + if (pageSetup.BlackAndWhite != null) ws.PageSetup.BlackAndWhite = pageSetup.BlackAndWhite; + if (pageSetup.Draft != null) ws.PageSetup.DraftQuality = pageSetup.Draft; + if (pageSetup.CellComments != null) ws.PageSetup.ShowComments = showCommentsValues.Single(sc => sc.Value == pageSetup.CellComments).Key; + if (pageSetup.Errors != null) ws.PageSetup.PrintErrorValue = printErrorValues.Single(p => p.Value == pageSetup.Errors).Key; if (pageSetup.HorizontalDpi != null) ws.PageSetup.HorizontalDpi = Int32.Parse(pageSetup.HorizontalDpi.InnerText); if (pageSetup.VerticalDpi != null) ws.PageSetup.VerticalDpi = Int32.Parse(pageSetup.VerticalDpi.InnerText); @@ -268,7 +288,8 @@ if (headerFooters.Count() > 0) { var headerFooter = (HeaderFooter)headerFooters.First(); - ws.PageSetup.AlignHFWithMargins = headerFooter.AlignWithMargins; + if (headerFooter.AlignWithMargins != null) + ws.PageSetup.AlignHFWithMargins = headerFooter.AlignWithMargins; // Footers var xlFooter = (XLHeaderFooter)ws.PageSetup.Footer; @@ -330,7 +351,8 @@ var columnBreaks = (ColumnBreaks)columnBreaksList.First(); foreach (var columnBreak in columnBreaks.Descendants()) { - ws.PageSetup.ColumnBreaks.Add(Int32.Parse(columnBreak.Id.InnerText)); + if (columnBreak.Id != null) + ws.PageSetup.ColumnBreaks.Add(Int32.Parse(columnBreak.Id.InnerText)); } } } @@ -432,22 +454,24 @@ var alignment = (Alignment)((CellFormat)((CellFormats)s.CellFormats).ElementAt(styleIndex)).Alignment; if (alignment != null) { - xlStylized.Style.Alignment.Horizontal = alignmentHorizontalValues.Single(a => a.Value == alignment.Horizontal).Key; + if (alignment.Horizontal != null) + xlStylized.Style.Alignment.Horizontal = alignmentHorizontalValues.Single(a => a.Value == alignment.Horizontal).Key; if (alignment.Indent != null) xlStylized.Style.Alignment.Indent = Int32.Parse(alignment.Indent.ToString()); - xlStylized.Style.Alignment.JustifyLastLine = alignment.JustifyLastLine; + if (alignment.JustifyLastLine != null) + xlStylized.Style.Alignment.JustifyLastLine = alignment.JustifyLastLine; if (alignment.ReadingOrder != null) xlStylized.Style.Alignment.ReadingOrder = (XLAlignmentReadingOrderValues)Int32.Parse(alignment.ReadingOrder.ToString()); if (alignment.RelativeIndent != null) xlStylized.Style.Alignment.RelativeIndent = alignment.RelativeIndent; - - xlStylized.Style.Alignment.ShrinkToFit = alignment.ShrinkToFit; + if (alignment.ShrinkToFit != null) + xlStylized.Style.Alignment.ShrinkToFit = alignment.ShrinkToFit; if (alignment.TextRotation != null) xlStylized.Style.Alignment.TextRotation = Int32.Parse(alignment.TextRotation.ToString()); if (alignment.Vertical != null) xlStylized.Style.Alignment.Vertical = alignmentVerticalValues.Single(a => a.Value == alignment.Vertical).Key; - - xlStylized.Style.Alignment.WrapText = alignment.WrapText; + if (alignment.WrapText !=null) + xlStylized.Style.Alignment.WrapText = alignment.WrapText; } var borderId = ((CellFormat)((CellFormats)s.CellFormats).ElementAt(styleIndex)).BorderId.Value; @@ -530,12 +554,13 @@ xlStylized.Style.Font.FontSize = ((FontSize)font.FontSize).Val; } if (font.Italic != null && font.Italic.Val != null) - xlStylized.Style.Font.Italic = font.Italic.Val; + xlStylized.Style.Font.Italic = font.Italic.Val; if (font.Shadow != null && font.Shadow.Val != null) - xlStylized.Style.Font.Shadow = font.Shadow.Val; + xlStylized.Style.Font.Shadow = font.Shadow.Val; if (font.Strike != null && font.Strike.Val != null) - xlStylized.Style.Font.Strikethrough = font.Strike.Val; - xlStylized.Style.Font.Underline = font.Underline == null || ((Underline)font.Underline).Val == null ? XLWorkbook.DefaultStyle.Font.Underline : underlineValuesList.Single(u => u.Value == ((Underline)font.Underline).Val).Key; + xlStylized.Style.Font.Strikethrough = font.Strike.Val; + if (font.Underline != null && ((Underline)font.Underline).Val == null) + xlStylized.Style.Font.Underline = underlineValuesList.Single(u => u.Value == ((Underline)font.Underline).Val).Key; if (font.VerticalTextAlignment != null && ((VerticalTextAlignment)font.VerticalTextAlignment).Val != null) xlStylized.Style.Font.VerticalAlignment = fontVerticalTextAlignmentValues.Single(f => f.Value == ((VerticalTextAlignment)font.VerticalTextAlignment).Val).Key; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 7692c62..d3c716c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -918,6 +918,7 @@ var allRows = rowsFromCells.ToList(); allRows.AddRange(rowsFromCollection); var distinctRows = allRows.Distinct(); + foreach (var distinctRow in distinctRows.OrderBy(r => r)) { Row row = new Row() { RowIndex = (UInt32Value)(UInt32)distinctRow }; diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index 1eb44b0..df5f5ba 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -12,7 +12,7 @@ { static void Main(string[] args) { - var wb = new XLWorkbook(@"C:\Excel Files\ForTesting\Excel2003.xlsx"); + var wb = new XLWorkbook(@"C:\Excel Files\ForTesting\2007.xlsx"); //var ws = wb.Worksheets.Worksheet("Sheet1"); //ws.Cell(1, 1).Value = "something";