diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs index 3a543df..abf5885 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs @@ -35,8 +35,15 @@ private void SetColumnNumber(Int32 column) { - RangeAddress.FirstAddress = new XLAddress(1, column); - RangeAddress.LastAddress = new XLAddress(XLWorksheet.MaxNumberOfRows, column); + if (column <= 0) + { + RangeAddress.IsInvalid = false; + } + else + { + RangeAddress.FirstAddress = new XLAddress(1, column); + RangeAddress.LastAddress = new XLAddress(XLWorksheet.MaxNumberOfRows, column); + } } public Boolean IsReference { get; private set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs index 61a7a72..e6b4159 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs @@ -18,64 +18,15 @@ void Worksheet_RangeShiftedColumns(XLRange range, int columnsShifted) { - if (!RangeAddress.IsInvalid && !range.RangeAddress.IsInvalid) - { - if (columnsShifted < 0 - // all columns - && RangeAddress.FirstAddress.ColumnNumber >= range.RangeAddress.FirstAddress.ColumnNumber - && RangeAddress.LastAddress.ColumnNumber <= range.RangeAddress.FirstAddress.ColumnNumber - columnsShifted - // all rows - && RangeAddress.FirstAddress.RowNumber >= range.RangeAddress.FirstAddress.RowNumber - && RangeAddress.LastAddress.RowNumber <= range.RangeAddress.LastAddress.RowNumber - ) - { - ((XLRangeAddress)RangeAddress).IsInvalid = true; - } - else - { - if (range.RangeAddress.FirstAddress.RowNumber <= RangeAddress.FirstAddress.RowNumber - && range.RangeAddress.LastAddress.RowNumber >= RangeAddress.LastAddress.RowNumber) - { - if (range.RangeAddress.FirstAddress.ColumnNumber <= RangeAddress.FirstAddress.ColumnNumber) - RangeAddress.FirstAddress = new XLAddress(RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber + columnsShifted); - - if (range.RangeAddress.FirstAddress.ColumnNumber <= RangeAddress.LastAddress.ColumnNumber) - RangeAddress.LastAddress = new XLAddress(RangeAddress.LastAddress.RowNumber, RangeAddress.LastAddress.ColumnNumber + columnsShifted); - } - } - } + ShiftColumns(this.RangeAddress, range, columnsShifted); } void Worksheet_RangeShiftedRows(XLRange range, int rowsShifted) { - if (!RangeAddress.IsInvalid && !range.RangeAddress.IsInvalid) - { - if (rowsShifted < 0 - // all columns - && RangeAddress.FirstAddress.ColumnNumber >= range.RangeAddress.FirstAddress.ColumnNumber - && RangeAddress.LastAddress.ColumnNumber <= range.RangeAddress.FirstAddress.ColumnNumber - // all rows - && RangeAddress.FirstAddress.RowNumber >= range.RangeAddress.FirstAddress.RowNumber - && RangeAddress.LastAddress.RowNumber <= range.RangeAddress.LastAddress.RowNumber - rowsShifted - ) - { - ((XLRangeAddress)RangeAddress).IsInvalid = true; - } - else - { - if (range.RangeAddress.FirstAddress.ColumnNumber <= RangeAddress.FirstAddress.ColumnNumber - && range.RangeAddress.LastAddress.ColumnNumber >= RangeAddress.LastAddress.ColumnNumber) - { - if (range.RangeAddress.FirstAddress.RowNumber <= RangeAddress.FirstAddress.RowNumber) - RangeAddress.FirstAddress = new XLAddress(RangeAddress.FirstAddress.RowNumber + rowsShifted, RangeAddress.FirstAddress.ColumnNumber); - - if (range.RangeAddress.FirstAddress.RowNumber <= RangeAddress.LastAddress.RowNumber) - RangeAddress.LastAddress = new XLAddress(RangeAddress.LastAddress.RowNumber + rowsShifted, RangeAddress.LastAddress.ColumnNumber); - } - } - } + ShiftRows(this.RangeAddress, range, rowsShifted); } + #region IXLRange Members public IXLRangeColumn FirstColumn() diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index d76eb7f..c11acc4 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -60,13 +60,31 @@ { return this.Cell(new XLAddress(cellAddressInRange)); } + public IXLCell CellFast(String cellAddressInRange) + { + String toUse = cellAddressInRange; + //if (cellAddressInRange.Contains('$')) + // toUse = cellAddressInRange.Replace("$", ""); + //else + // toUse = cellAddressInRange; + + var cIndex = 1; + while (!Char.IsNumber(toUse, cIndex)) + cIndex++; + + return this.Cell(new XLAddress(Int32.Parse(toUse.Substring(cIndex)), toUse.Substring(0, cIndex))); + } 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.RangeAddress.FirstAddress - 1; + return Cell((XLAddress)cellAddressInRange); + } + public IXLCell Cell(XLAddress cellAddressInRange) + { + IXLAddress absoluteAddress = cellAddressInRange + (XLAddress)this.RangeAddress.FirstAddress - 1; if (this.Worksheet.Internals.CellsCollection.ContainsKey(absoluteAddress)) { return this.Worksheet.Internals.CellsCollection[absoluteAddress]; @@ -565,5 +583,69 @@ Worksheet.NamedRanges.Add(rangeName, this.AsRange(), comment); } } + + protected void ShiftColumns(IXLRangeAddress thisRangeAddress, XLRange shiftedRange, int columnsShifted) + { + if (!thisRangeAddress.IsInvalid && !shiftedRange.RangeAddress.IsInvalid) + { + if ((columnsShifted < 0 + // all columns + && thisRangeAddress.FirstAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber + && thisRangeAddress.LastAddress.ColumnNumber <= shiftedRange.RangeAddress.FirstAddress.ColumnNumber - columnsShifted + // all rows + && thisRangeAddress.FirstAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber + && thisRangeAddress.LastAddress.RowNumber <= shiftedRange.RangeAddress.LastAddress.RowNumber + ) || (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber + && shiftedRange.RangeAddress.LastAddress.RowNumber >= thisRangeAddress.LastAddress.RowNumber + && thisRangeAddress.FirstAddress.ColumnNumber + columnsShifted <= 0)) + { + thisRangeAddress.IsInvalid = true; + } + else + { + if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber + && shiftedRange.RangeAddress.LastAddress.RowNumber >= thisRangeAddress.LastAddress.RowNumber) + { + if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= thisRangeAddress.FirstAddress.ColumnNumber) + thisRangeAddress.FirstAddress = new XLAddress(thisRangeAddress.FirstAddress.RowNumber, thisRangeAddress.FirstAddress.ColumnNumber + columnsShifted); + + if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= thisRangeAddress.LastAddress.ColumnNumber) + thisRangeAddress.LastAddress = new XLAddress(thisRangeAddress.LastAddress.RowNumber, thisRangeAddress.LastAddress.ColumnNumber + columnsShifted); + } + } + } + } + + protected void ShiftRows(IXLRangeAddress thisRangeAddress, XLRange shiftedRange, int rowsShifted) + { + if (!thisRangeAddress.IsInvalid && !shiftedRange.RangeAddress.IsInvalid) + { + if ((rowsShifted < 0 + // all columns + && thisRangeAddress.FirstAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber + && thisRangeAddress.LastAddress.ColumnNumber <= shiftedRange.RangeAddress.FirstAddress.ColumnNumber + // all rows + && thisRangeAddress.FirstAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber + && thisRangeAddress.LastAddress.RowNumber <= shiftedRange.RangeAddress.LastAddress.RowNumber - rowsShifted + ) || (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= thisRangeAddress.FirstAddress.ColumnNumber + && shiftedRange.RangeAddress.LastAddress.ColumnNumber >= thisRangeAddress.LastAddress.ColumnNumber + && thisRangeAddress.FirstAddress.RowNumber + rowsShifted <= 0)) + { + thisRangeAddress.IsInvalid = true; + } + else + { + if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= thisRangeAddress.FirstAddress.ColumnNumber + && shiftedRange.RangeAddress.LastAddress.ColumnNumber >= thisRangeAddress.LastAddress.ColumnNumber) + { + if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber) + thisRangeAddress.FirstAddress = new XLAddress(thisRangeAddress.FirstAddress.RowNumber + rowsShifted, thisRangeAddress.FirstAddress.ColumnNumber); + + if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.LastAddress.RowNumber) + thisRangeAddress.LastAddress = new XLAddress(thisRangeAddress.LastAddress.RowNumber + rowsShifted, thisRangeAddress.LastAddress.ColumnNumber); + } + } + } + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs index 26fc3d8..f6e060b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs @@ -19,27 +19,11 @@ void Worksheet_RangeShiftedColumns(XLRange range, int columnsShifted) { - if (range.RangeAddress.FirstAddress.RowNumber <= RangeAddress.FirstAddress.RowNumber - && range.RangeAddress.LastAddress.RowNumber >= RangeAddress.LastAddress.RowNumber) - { - if (range.RangeAddress.FirstAddress.ColumnNumber <= RangeAddress.FirstAddress.ColumnNumber) - RangeAddress.FirstAddress = new XLAddress(RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber + columnsShifted); - - if (range.RangeAddress.FirstAddress.ColumnNumber <= RangeAddress.LastAddress.ColumnNumber) - RangeAddress.LastAddress = new XLAddress(RangeAddress.LastAddress.RowNumber, RangeAddress.LastAddress.ColumnNumber + columnsShifted); - } + ShiftColumns(this.RangeAddress, range, columnsShifted); } void Worksheet_RangeShiftedRows(XLRange range, int rowsShifted) { - if (range.RangeAddress.FirstAddress.ColumnNumber <= RangeAddress.FirstAddress.ColumnNumber - && range.RangeAddress.LastAddress.ColumnNumber >= RangeAddress.LastAddress.ColumnNumber) - { - if (range.RangeAddress.FirstAddress.RowNumber <= RangeAddress.FirstAddress.RowNumber) - RangeAddress.FirstAddress = new XLAddress(RangeAddress.FirstAddress.RowNumber + rowsShifted, RangeAddress.FirstAddress.ColumnNumber); - - if (range.RangeAddress.FirstAddress.RowNumber <= RangeAddress.LastAddress.RowNumber) - RangeAddress.LastAddress = new XLAddress(RangeAddress.LastAddress.RowNumber + rowsShifted, RangeAddress.LastAddress.ColumnNumber); - } + ShiftRows(this.RangeAddress, range, rowsShifted); } public IXLCell Cell(int row) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs index 08a5893..6fac31a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs @@ -18,27 +18,11 @@ void Worksheet_RangeShiftedColumns(XLRange range, int columnsShifted) { - if (range.RangeAddress.FirstAddress.RowNumber <= RangeAddress.FirstAddress.RowNumber - && range.RangeAddress.LastAddress.RowNumber >= RangeAddress.LastAddress.RowNumber) - { - if (range.RangeAddress.FirstAddress.ColumnNumber <= RangeAddress.FirstAddress.ColumnNumber) - RangeAddress.FirstAddress = new XLAddress(RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber + columnsShifted); - - if (range.RangeAddress.FirstAddress.ColumnNumber <= RangeAddress.LastAddress.ColumnNumber) - RangeAddress.LastAddress = new XLAddress(RangeAddress.LastAddress.RowNumber, RangeAddress.LastAddress.ColumnNumber + columnsShifted); - } + ShiftColumns(this.RangeAddress, range, columnsShifted); } void Worksheet_RangeShiftedRows(XLRange range, int rowsShifted) { - if (range.RangeAddress.FirstAddress.ColumnNumber <= RangeAddress.FirstAddress.ColumnNumber - && range.RangeAddress.LastAddress.ColumnNumber >= RangeAddress.LastAddress.ColumnNumber) - { - if (range.RangeAddress.FirstAddress.RowNumber <= RangeAddress.FirstAddress.RowNumber) - RangeAddress.FirstAddress = new XLAddress(RangeAddress.FirstAddress.RowNumber + rowsShifted, RangeAddress.FirstAddress.ColumnNumber); - - if (range.RangeAddress.FirstAddress.RowNumber <= RangeAddress.LastAddress.RowNumber) - RangeAddress.LastAddress = new XLAddress(RangeAddress.LastAddress.RowNumber + rowsShifted, RangeAddress.LastAddress.ColumnNumber); - } + ShiftRows(this.RangeAddress, range, rowsShifted); } public IXLCell Cell(int column) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs index a5bb7c3..067f460 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs @@ -40,8 +40,15 @@ private void SetRowNumber(Int32 row) { - RangeAddress.FirstAddress = new XLAddress(row, 1); - RangeAddress.LastAddress = new XLAddress(row, XLWorksheet.MaxNumberOfColumns); + if (row <= 0) + { + RangeAddress.IsInvalid = false; + } + else + { + RangeAddress.FirstAddress = new XLAddress(row, 1); + RangeAddress.LastAddress = new XLAddress(row, XLWorksheet.MaxNumberOfColumns); + } } public Boolean IsReference { get; private set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs index e979b5f..eff6dcf 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs @@ -19,7 +19,7 @@ { this.rowNumber = rowNumber; this.columnNumber = columnNumber; - this.columnLetter = GetColumnLetterFromNumber(columnNumber); + this.columnLetter = null; fixedColumn = false; fixedRow = false; } @@ -32,7 +32,7 @@ public XLAddress(Int32 rowNumber, String columnLetter) { this.rowNumber = rowNumber; - this.columnNumber = GetColumnNumberFromLetter(columnLetter); + this.columnNumber = 0; this.columnLetter = columnLetter; fixedColumn = false; fixedRow = false; @@ -50,7 +50,7 @@ columnLetter = m.Groups[1].Value.Replace("$", ""); fixedRow = m.Groups[1].Value.StartsWith("$"); rowNumber = Int32.Parse(m.Groups[2].Value.Replace("$", "")); - columnNumber = GetColumnNumberFromLetter(columnLetter); + columnNumber = 0; } #endregion @@ -157,7 +157,13 @@ /// public Int32 ColumnNumber { - get { return columnNumber; } + get + { + if (columnNumber == 0) + columnNumber = GetColumnNumberFromLetter(columnLetter); + + return columnNumber; + } private set { columnNumber = value; } } @@ -167,7 +173,13 @@ /// public String ColumnLetter { - get { return columnLetter; } + get + { + if (columnLetter == null) + columnLetter = GetColumnLetterFromNumber(columnNumber); + + return columnLetter; + } private set { columnLetter = value; } } @@ -177,10 +189,10 @@ public override string ToString() { var sb = new StringBuilder(); - if (fixedColumn) sb.Append("$"); - sb.Append(this.columnLetter); - if (fixedRow) sb.Append("$"); - sb.Append(this.rowNumber.ToString()); + if (FixedColumn) sb.Append("$"); + sb.Append(ColumnLetter); + if (FixedRow) sb.Append("$"); + sb.Append(RowNumber.ToString()); return sb.ToString(); } #endregion diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index 2a4c7d4..ba8cc18 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -61,9 +61,47 @@ var s = (Stylesheet)workbookStylesPart.Stylesheet; var numberingFormats = (NumberingFormats)s.NumberingFormats; Fills fills = (Fills)s.Fills; + //var fillDictionary = new Dictionary(); + //for (var i = 0; i < fills.Count; i++) + //{ + // fillDictionary.Add(i, (Fill)fills.ElementAt(i)); + //} + //var cellFormatToFill = new Dictionary(); + //for (var i = 0; i < s.CellFormats.Count; i++) + //{ + // var cellFormat = (CellFormat)s.CellFormats.ElementAt(i); + // if (cellFormat.FillId.HasValue) + // cellFormatToFill.Add(i, fillDictionary[(Int32)cellFormat.FillId.Value]); + //} + Borders borders = (Borders)s.Borders; + //var borderDictionary = new Dictionary(); + //for (var i = 0; i < borders.Count; i++) + //{ + // borderDictionary.Add(i, (Border)borders.ElementAt(i)); + //} + //var cellFormatToBorder = new Dictionary(); + //for (var i = 0; i < s.CellFormats.Count; i++) + //{ + // var cellFormat = (CellFormat)s.CellFormats.ElementAt(i); + // if (cellFormat.BorderId.HasValue) + // cellFormatToBorder.Add(i, borderDictionary[(Int32)cellFormat.BorderId.Value]); + //} + Fonts fonts = (Fonts)s.Fonts; - + //var fontDictionary = new Dictionary(); + //for (var i = 0; i < fonts.Count; i++) + //{ + // fontDictionary.Add(i, (Font)fonts.ElementAt(i)); + //} + //var cellFormatToFont = new Dictionary(); + //for (var i = 0; i < s.CellFormats.Count; i++) + //{ + // var cellFormat = (CellFormat)s.CellFormats.ElementAt(i); + // if (cellFormat.FontId.HasValue) + // cellFormatToFont.Add(i, fontDictionary[(Int32)cellFormat.FontId.Value]); + //} + var sheets = dSpreadsheet.WorkbookPart.Workbook.Sheets; foreach (var sheet in sheets) @@ -137,7 +175,7 @@ foreach (var row in worksheetPart.Worksheet.Descendants().Where(r=>r.CustomFormat != null && r.CustomFormat).Select(r=>r)) { //var dRow = (Column)col; - var xlRow = ws.Row(Int32.Parse(row.RowIndex.ToString())); + var xlRow = ws.Row((Int32)row.RowIndex.Value); if (row.Height != null) xlRow.Height = row.Height; else @@ -168,7 +206,7 @@ { var dCell = (Cell)cell; Int32 styleIndex = dCell.StyleIndex != null ? Int32.Parse(dCell.StyleIndex.InnerText) : -1; - var xlCell = ws.Cell(dCell.CellReference); + var xlCell = ws.CellFast(dCell.CellReference); if (styleIndex > 0) { styleIndex = Int32.Parse(dCell.StyleIndex.InnerText); @@ -432,12 +470,31 @@ return null; } - private void ApplyStyle(IXLStylized xlStylized, Int32 styleIndex, Stylesheet s, Fills fills, Borders borders, Fonts fonts, NumberingFormats numberingFormats ) + //private Dictionary alignmentDictionary; + //private Dictionary GetAlignmentDictionary(Stylesheet s) + //{ + // if (alignmentDictionary == null) + // { + // alignmentDictionary = new Dictionary(); + // for (Int32 i = 0; i < s.CellFormats.Count; i++) + // { + // var alignment = ((CellFormat)s.CellFormats.ElementAt(i)).Alignment; + // if (alignment != null) + // alignmentDictionary.Add(i, alignment); + // } + // } + // return alignmentDictionary; + //} + + private void ApplyStyle(IXLStylized xlStylized, Int32 styleIndex, Stylesheet s, Fills fills, Borders borders, Fonts fonts, NumberingFormats numberingFormats) { + //if (fills.ContainsKey(styleIndex)) + //{ + // var fill = fills[styleIndex]; var fillId = ((CellFormat)((CellFormats)s.CellFormats).ElementAt(styleIndex)).FillId.Value; if (fillId > 0) { - var fill = (Fill)fills.ElementAt(Int32.Parse(fillId.ToString())); + var fill = (Fill)fills.ElementAt((Int32)fillId); if (fill.PatternFill != null) { if (fill.PatternFill.PatternType != null) @@ -451,6 +508,11 @@ } } + //var alignmentDictionary = GetAlignmentDictionary(s); + + //if (alignmentDictionary.ContainsKey(styleIndex)) + //{ + // var alignment = alignmentDictionary[styleIndex]; var alignment = (Alignment)((CellFormat)((CellFormats)s.CellFormats).ElementAt(styleIndex)).Alignment; if (alignment != null) { @@ -467,15 +529,19 @@ if (alignment.ShrinkToFit != null) xlStylized.Style.Alignment.ShrinkToFit = alignment.ShrinkToFit; if (alignment.TextRotation != null) - xlStylized.Style.Alignment.TextRotation = Int32.Parse(alignment.TextRotation.ToString()); + xlStylized.Style.Alignment.TextRotation = (Int32)alignment.TextRotation.Value; if (alignment.Vertical != null) xlStylized.Style.Alignment.Vertical = alignmentVerticalValues.Single(a => a.Value == alignment.Vertical).Key; if (alignment.WrapText !=null) xlStylized.Style.Alignment.WrapText = alignment.WrapText; } + + //if (borders.ContainsKey(styleIndex)) + //{ + // var border = borders[styleIndex]; var borderId = ((CellFormat)((CellFormats)s.CellFormats).ElementAt(styleIndex)).BorderId.Value; - var border = (Border)borders.ElementAt(Int32.Parse(borderId.ToString())); + var border = (Border)borders.ElementAt((Int32)borderId); if (border != null) { var bottomBorder = (BottomBorder)border.BottomBorder; @@ -530,12 +596,14 @@ } } + //if (fonts.ContainsKey(styleIndex)) + //{ + // var font = fonts[styleIndex]; var fontId = ((CellFormat)((CellFormats)s.CellFormats).ElementAt(styleIndex)).FontId; - var font = (Font)fonts.ElementAt(Int32.Parse(fontId.ToString())); + var font = (Font)fonts.ElementAt((Int32)fontId.Value); if (font != null) { - if (font.Bold != null && font.Bold.Val != null) - xlStylized.Style.Font.Bold = font.Bold.Val; + xlStylized.Style.Font.Bold = GetBoolean(font.Bold); var fontColor = GetColor(font.Color); if (fontColor != null) @@ -553,13 +621,11 @@ if (((FontSize)font.FontSize).Val != null) xlStylized.Style.Font.FontSize = ((FontSize)font.FontSize).Val; } - if (font.Italic != null && font.Italic.Val != null) - xlStylized.Style.Font.Italic = font.Italic.Val; - if (font.Shadow != null && font.Shadow.Val != null) - xlStylized.Style.Font.Shadow = font.Shadow.Val; - if (font.Strike != null && font.Strike.Val != null) - xlStylized.Style.Font.Strikethrough = font.Strike.Val; - if (font.Underline != null && ((Underline)font.Underline).Val == null) + + xlStylized.Style.Font.Italic = GetBoolean(font.Italic); + xlStylized.Style.Font.Shadow = GetBoolean(font.Shadow); + xlStylized.Style.Font.Strikethrough = GetBoolean(font.Strike); + 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; @@ -584,11 +650,27 @@ if (formatCode.Length > 0) xlStylized.Style.NumberFormat.Format = formatCode; else - xlStylized.Style.NumberFormat.NumberFormatId = Int32.Parse(numberFormatId); + xlStylized.Style.NumberFormat.NumberFormatId = (Int32)numberFormatId.Value; } } } + private Boolean GetBoolean(BooleanPropertyType property) + { + if (property != null) + { + if (property.Val != null) + return property.Val; + else + return true; + } + else + { + return false; + } + } + + private static Dictionary indexedColorList; private static Dictionary GetIndexedColors() { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index ff538d1..42f1baf 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -336,8 +336,15 @@ else { // This is a new row so we're going to reference all - // cells in this row to preserve their formatting - this.Internals.ColumnsCollection.Keys.ForEach(c => Cell(row, c)); + // cells in columns of this row to preserve their formatting + var distinctColumns = this.Internals.CellsCollection.Keys.Select(k=>k.ColumnNumber).Distinct().ToDictionary(r=>r, r=>0); + + var usedColumns = from c in this.Internals.ColumnsCollection + join dc in distinctColumns + on c.Key equals dc.Key + select c.Key; + + usedColumns.ForEach(c => Cell(row, c)); styleToUse = this.Style; this.Internals.RowsCollection.Add(row, new XLRow(row, new XLRowParameters(this, styleToUse, false))); } diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index df5f5ba..5e3f418 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -12,12 +12,22 @@ { static void Main(string[] args) { - var wb = new XLWorkbook(@"C:\Excel Files\ForTesting\2007.xlsx"); + //while (true) + //{ + // var startTime = DateTime.Now; + // var wb = new XLWorkbook(@"C:\Excel Files\ForTesting\2007.xlsx"); + // var endTime = DateTime.Now; + + // Console.WriteLine("{0} secs.", (endTime - startTime).TotalSeconds); + //} + //Console.ReadKey(); + + //var ws = wb.Worksheets.Worksheet("Sheet1"); //ws.Cell(1, 1).Value = "something"; - wb.SaveAs(@"C:\Excel Files\ForTesting\Sandbox.xlsx"); + //wb.SaveAs(@"C:\Excel Files\ForTesting\Sandbox.xlsx"); //Console.ReadKey(); }