diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs index 17fbe26..0798d6c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs @@ -26,6 +26,12 @@ /// Int32 SheetIndex { get; set; } /// + /// Gets or sets the position of the sheet. + /// When setting the Position all other sheets' positions are shifted accordingly. + /// + Int32 Position { get; set; } + + /// /// Gets an object to manipulate the sheet's print options. /// IXLPageSetup PageSetup { get; } @@ -239,5 +245,12 @@ IXLSheetView SheetView { get; } IXLTables Tables { get; } + + IXLWorksheet CopyTo(String newSheetName); + IXLWorksheet CopyTo(String newSheetName, Int32 position); + IXLWorksheet CopyTo(XLWorkbook workbook, String newSheetName); + IXLWorksheet CopyTo(XLWorkbook workbook, String newSheetName, Int32 position); + + IXLRange RangeUsed(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheets.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheets.cs index d312566..2c19286 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheets.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheets.cs @@ -10,6 +10,7 @@ IXLWorksheet Worksheet(String sheetName); IXLWorksheet Worksheet(Int32 sheetIndex); IXLWorksheet Add(String sheetName); + IXLWorksheet Add(String sheetName, Int32 position); void Delete(String sheetName); void Delete(Int32 sheetIndex); } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs index 5b3a178..9a989f8 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs @@ -213,6 +213,8 @@ IXLTable AsTable(String name); IXLTable CreateTable(); IXLTable CreateTable(String name); + + IXLRange RangeUsed(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs index 8ecf094..95eba21 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs @@ -424,6 +424,7 @@ { return new XLTable(this, false); } + public IXLTable AsTable(String name) { return new XLTable(this, name, false); @@ -433,11 +434,11 @@ { return new XLTable(this, true); } + public IXLTable CreateTable(String name) { return new XLTable(this, name, true); } - #endregion } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index 1f7c42f..0490307 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -845,5 +845,10 @@ } } } + + public IXLRange RangeUsed() + { + return this.Range(this.FirstCellUsed(), this.LastCellUsed()); + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs index 7848a04..33f0a13 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs @@ -3,6 +3,7 @@ using System.Linq; using System.Text; using System.Text.RegularExpressions; +using System.Globalization; namespace ClosedXML.Excel { @@ -61,12 +62,12 @@ if (fixedRow) { columnLetter = cellAddressString.Substring(startPos, rowPos - 1); - rowNumber = Int32.Parse(cellAddressString.Substring(rowPos + 1)); + rowNumber = Int32.Parse(cellAddressString.Substring(rowPos + 1), nfi); } else { columnLetter = cellAddressString.Substring(startPos, rowPos); - rowNumber = Int32.Parse(cellAddressString.Substring(rowPos)); + rowNumber = Int32.Parse(cellAddressString.Substring(rowPos), nfi); } columnNumber = 0; @@ -83,7 +84,7 @@ public static Int32 GetColumnNumberFromLetter(String columnLetter) { if (columnLetter[0] <= '9') - return Int32.Parse(columnLetter); + return Int32.Parse(columnLetter, nfi); columnLetter = columnLetter.ToUpper(); var length = columnLetter.Length; @@ -148,7 +149,7 @@ while (cellAddressString[rowPos] > '9') rowPos++; - return Int32.Parse(cellAddressString.Substring(rowPos)); + return Int32.Parse(cellAddressString.Substring(rowPos), nfi); } public static Int32 GetColumnNumberFromAddress1(String cellAddressString) @@ -168,11 +169,11 @@ if (cellAddressString[rowPos] == '$') { - return Int32.Parse(cellAddressString.Substring(rowPos + 1)); + return Int32.Parse(cellAddressString.Substring(rowPos + 1), nfi); } else { - return Int32.Parse(cellAddressString.Substring(rowPos)); + return Int32.Parse(cellAddressString.Substring(rowPos), nfi); } } @@ -260,21 +261,30 @@ #endregion #region Overrides + private static NumberFormatInfo nfi = CultureInfo.InvariantCulture.NumberFormat; public override string ToString() { - var sb = new StringBuilder(); - if (FixedColumn) sb.Append("$"); - sb.Append(ColumnLetter); - if (FixedRow) sb.Append("$"); - sb.Append(RowNumber.ToString()); - return sb.ToString(); + //var sb = new StringBuilder(); + //if (FixedColumn) sb.Append("$"); + //sb.Append(ColumnLetter); + //if (FixedRow) sb.Append("$"); + //sb.Append(rowNumber.ToString()); + //return sb.ToString(); + + String retVal = ColumnLetter; + if (fixedColumn) + retVal = "$" + retVal; + if (fixedRow) + retVal += "$"; + retVal += rowNumber.ToStringLookup(); + return retVal; } #endregion #region Methods public string GetTrimmedAddress() { - return ColumnLetter + rowNumber.ToString(); + return ColumnLetter + rowNumber.ToStringLookup(); } #endregion diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs index 323a090..b8e4b53 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs @@ -296,5 +296,14 @@ } #endregion + + public IXLWorksheet Worksheet(String name) + { + return Worksheets.Worksheet(name); + } + public IXLWorksheet Worksheet(Int32 sheetIndex) + { + return Worksheets.Worksheet(sheetIndex); + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index cbd38a5..0759614 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -103,7 +103,7 @@ var pane = (Pane)sheetView.Descendants().FirstOrDefault(); if (pane != null) { - if (pane.State != null && pane.State == PaneStateValues.FrozenSplit) + if (pane.State != null && (pane.State == PaneStateValues.FrozenSplit || pane.State == PaneStateValues.Frozen)) { if (pane.HorizontalSplit != null) ws.SheetView.SplitColumn = (Int32)pane.HorizontalSplit.Value; @@ -582,11 +582,11 @@ if (alignment.Horizontal != null) xlStylized.InnerStyle.Alignment.Horizontal = alignmentHorizontalValues.Single(a => a.Value == alignment.Horizontal).Key; if (alignment.Indent != null) - xlStylized.InnerStyle.Alignment.Indent = (Int32)alignment.Indent.Value; + xlStylized.InnerStyle.Alignment.Indent = Int32.Parse(alignment.Indent.ToString()); if (alignment.JustifyLastLine != null) xlStylized.InnerStyle.Alignment.JustifyLastLine = alignment.JustifyLastLine; if (alignment.ReadingOrder != null) - xlStylized.InnerStyle.Alignment.ReadingOrder = (XLAlignmentReadingOrderValues)(Int32)alignment.ReadingOrder.Value ; + xlStylized.InnerStyle.Alignment.ReadingOrder = (XLAlignmentReadingOrderValues)Int32.Parse(alignment.ReadingOrder.ToString()); if (alignment.RelativeIndent != null) xlStylized.InnerStyle.Alignment.RelativeIndent = alignment.RelativeIndent; if (alignment.ShrinkToFit != null) @@ -672,18 +672,17 @@ if (fontColor.HasValue) xlStylized.InnerStyle.Font.FontColor = fontColor; - if (font.FontFamilyNumbering != null) - if (font.FontFamilyNumbering.Val.HasValue) - xlStylized.InnerStyle.Font.FontFamilyNumbering = (XLFontFamilyNumberingValues)font.FontFamilyNumbering.Val.Value; + if (font.FontFamilyNumbering != null && ((FontFamilyNumbering)font.FontFamilyNumbering).Val != null) + xlStylized.InnerStyle.Font.FontFamilyNumbering = (XLFontFamilyNumberingValues)Int32.Parse(((FontFamilyNumbering)font.FontFamilyNumbering).Val.ToString()); if (font.FontName != null) { - if (font.FontName.Val != null) - xlStylized.InnerStyle.Font.FontName = font.FontName.Val; + if (((FontName)font.FontName).Val != null) + xlStylized.InnerStyle.Font.FontName = ((FontName)font.FontName).Val; } if (font.FontSize != null) { - if (font.FontSize.Val != null) - xlStylized.InnerStyle.Font.FontSize = font.FontSize.Val; + if (((FontSize)font.FontSize).Val != null) + xlStylized.InnerStyle.Font.FontSize = ((FontSize)font.FontSize).Val; } xlStylized.InnerStyle.Font.Italic = GetBoolean(font.Italic); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 760771d..34c8138 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -595,7 +595,10 @@ foreach (var xlSheet in Worksheets.Cast().Where(w => w.SheetId == 0).OrderBy(w => w.SheetIndex)) { - var rId = relId.GetNext(RelType.Workbook); + String rId = relId.GetNext(RelType.Workbook); + while (Worksheets.Cast().Where(w=>w.SheetId == Int32.Parse(rId.Substring(3))).Any()) + rId = relId.GetNext(RelType.Workbook); + xlSheet.SheetId = Int32.Parse(rId.Substring(3)); xlSheet.RelId = rId; workbook.Sheets.Append(new Sheet() { Name = xlSheet.Name, Id = rId, SheetId = (UInt32)xlSheet.SheetId }); @@ -1577,6 +1580,9 @@ columns = worksheetPart.Worksheet.Elements().First(); + Dictionary sheetColumnsByMin = columns.Elements().ToDictionary(c => c.Min.Value, c => c); + //Dictionary sheetColumnsByMax = columns.Elements().ToDictionary(c => c.Max.Value, c => c); + Int32 minInColumnsCollection; Int32 maxInColumnsCollection; if (xlWorksheet.Internals.ColumnsCollection.Count > 0) @@ -1607,7 +1613,7 @@ CustomWidth = true }; - UpdateColumn(column, columns); + UpdateColumn(column, columns, sheetColumnsByMin); //, sheetColumnsByMax); } } @@ -1644,7 +1650,7 @@ if (collapsed) column.Collapsed = true; if (outlineLevel > 0) column.OutlineLevel = (byte)outlineLevel; - UpdateColumn(column, columns); + UpdateColumn(column, columns, sheetColumnsByMin); //, sheetColumnsByMax); } foreach (var col in columns.Elements().Where(c => c.Min > (UInt32)(maxInColumnsCollection)).OrderBy(c => c.Min.Value)) @@ -1668,6 +1674,8 @@ }; columns.Append(column); } + + CollapseColumns(columns, sheetColumnsByMin); } #endregion @@ -1772,11 +1780,12 @@ var dataType = opCell.DataType; var cellReference = ((XLAddress)opCell.Address).GetTrimmedAddress(); - Boolean isNewCell = false; + //Boolean isNewCell = false; + Cell cell = row.Elements().FirstOrDefault(c => c.CellReference.Value == cellReference); if (cell == null) { - isNewCell = true; + //isNewCell = true; cell = new Cell() { CellReference = cellReference }; if (row.Elements().Count() == 0) { @@ -1824,10 +1833,10 @@ { if (StringExtensions.IsNullOrWhiteSpace(opCell.InnerText)) { - if (isNewCell) - cellValue = null; - else - cellValue.Text = String.Empty; + //if (isNewCell) + cellValue = null; + //else + // cellValue.Text = String.Empty; } else { @@ -1857,7 +1866,9 @@ } #endregion - var phoneticProperties = worksheetPart.Worksheet.Elements().FirstOrDefault(); + var autoFilter = worksheetPart.Worksheet.Elements().FirstOrDefault(); + + CustomSheetViews customSheetViews = worksheetPart.Worksheet.Elements().FirstOrDefault(); #region MergeCells MergeCells mergeCells = null; @@ -1866,8 +1877,10 @@ if (worksheetPart.Worksheet.Elements().Count() == 0) { OpenXmlElement previousElement; - if (phoneticProperties != null) - previousElement = phoneticProperties; + if (customSheetViews != null) + previousElement = customSheetViews; + else if (autoFilter != null) + previousElement = autoFilter; else if (sheetData != null) previousElement = sheetData; else if (columns != null) @@ -1895,6 +1908,8 @@ } #endregion + var phoneticProperties = worksheetPart.Worksheet.Elements().FirstOrDefault(); + var hyperlinks = worksheetPart.Worksheet.Elements().FirstOrDefault(); #region PrintOptions @@ -1904,10 +1919,14 @@ OpenXmlElement previousElement; if (hyperlinks != null) previousElement = hyperlinks; - else if (mergeCells != null) - previousElement = mergeCells; else if (phoneticProperties != null) previousElement = phoneticProperties; + else if (mergeCells != null) + previousElement = mergeCells; + else if (customSheetViews != null) + previousElement = customSheetViews; + else if (autoFilter != null) + previousElement = autoFilter; else if (sheetData != null) previousElement = sheetData; else if (columns != null) @@ -1934,10 +1953,14 @@ previousElement = printOptions; else if (hyperlinks != null) previousElement = hyperlinks; - else if (mergeCells != null) - previousElement = mergeCells; else if (phoneticProperties != null) previousElement = phoneticProperties; + else if (mergeCells != null) + previousElement = mergeCells; + else if (customSheetViews != null) + previousElement = customSheetViews; + else if (autoFilter != null) + previousElement = autoFilter; else if (sheetData != null) previousElement = sheetData; else if (columns != null) @@ -2163,23 +2186,75 @@ #endregion } + private void CollapseColumns(Columns columns, Dictionary sheetColumns) + { + UInt32 lastMax = 1; + UInt32 lastMin = 1; + Int32 count = sheetColumns.Count; + foreach (var kp in sheetColumns.OrderBy(kp => kp.Key)) + { + if (kp.Key < count && ColumnsAreEqual(kp.Value, sheetColumns[kp.Key + 1])) + { + lastMax = kp.Key; + } + else + { + var newColumn = (Column)kp.Value.CloneNode(true); + newColumn.Min = lastMin; + var columnsToRemove = new List(); + foreach (var c in columns.Elements().Where(co => co.Min >= newColumn.Min && co.Max <= newColumn.Max).Select(co => co)) + { + columnsToRemove.Add(c); + } + columnsToRemove.ForEach(c => columns.RemoveChild(c)); + + columns.Append(newColumn); + + lastMin = kp.Key + 1; + } + + } + } + + + private Double GetColumnWidth(Double columnWidth) { return columnWidth + 0.71; } - private void UpdateColumn(Column column, Columns columns) + private void UpdateColumn(Column column, Columns columns, Dictionary sheetColumnsByMin)//, Dictionary sheetColumnsByMax) { + UInt32 co = column.Min.Value; Column newColumn; - Column existingColumn = columns.Elements().FirstOrDefault(c => c.Min.Value == column.Min.Value); - if (existingColumn == null) + Column existingColumn; // = columns.Elements().FirstOrDefault(c => c.Min.Value == column.Min.Value); + if (!sheetColumnsByMin.ContainsKey(co)) { + //if (sheetColumnsByMin.ContainsKey(co + 1) && ColumnsAreEqual(column, sheetColumnsByMin[co + 1])) + //{ + // var thisColumn = sheetColumnsByMin[co + 1]; + // thisColumn.Min -= 1; + // sheetColumnsByMin.Remove(co + 1); + // sheetColumnsByMin.Add(co, thisColumn); + //} + //else if (sheetColumnsByMax.ContainsKey(co - 1) && ColumnsAreEqual(column, sheetColumnsByMin[co - 1])) + //{ + // var thisColumn = sheetColumnsByMin[co - 1]; + // thisColumn.Max += 1; + // sheetColumnsByMax.Remove(co - 1); + // sheetColumnsByMax.Add(co, thisColumn); + //} + //else + //{ newColumn = (Column)column.CloneNode(true); - //newColumn = new Column() { InnerXml = column.InnerXml }; columns.Append(newColumn); + sheetColumnsByMin.Add(co, newColumn); + // sheetColumnsByMax.Add(co, newColumn); + //} } else { + existingColumn = sheetColumnsByMin[column.Min.Value]; newColumn = (Column)existingColumn.CloneNode(true); //newColumn = new Column() { InnerXml = existingColumn.InnerXml }; newColumn.Min = column.Min; @@ -2203,6 +2278,7 @@ else newColumn.OutlineLevel = null; + sheetColumnsByMin.Remove(column.Min.Value); if (existingColumn.Min + 1 > existingColumn.Max) { //existingColumn.Min = existingColumn.Min + 1; @@ -2210,16 +2286,32 @@ //existingColumn.Remove(); columns.RemoveChild(existingColumn); columns.Append(newColumn); + sheetColumnsByMin.Add(newColumn.Min.Value, newColumn); } else { //columns.InsertBefore(existingColumn, newColumn); columns.Append(newColumn); + sheetColumnsByMin.Add(newColumn.Min.Value, newColumn); existingColumn.Min = existingColumn.Min + 1; + sheetColumnsByMin.Add(existingColumn.Min.Value, existingColumn); } } } + + private bool ColumnsAreEqual(Column column, Column column_2) + { + return + column.Style.Value == column_2.Style.Value + && column.Width.Value == column_2.Width.Value + && ((column.Hidden == null && column_2.Hidden == null) + || (column.Hidden != null && column_2.Hidden != null && column.Hidden.Value == column_2.Hidden.Value)) + && ((column.Collapsed == null && column_2.Collapsed == null) + || (column.Collapsed != null && column_2.Collapsed != null && column.Collapsed.Value == column_2.Collapsed.Value)) + && ((column.OutlineLevel == null && column_2.OutlineLevel == null) + || (column.OutlineLevel != null && column_2.OutlineLevel != null && column.OutlineLevel.Value == column_2.OutlineLevel.Value)); + } #endregion private void GenerateCalculationChainPartContent(WorkbookPart workbookPart) @@ -2239,7 +2331,7 @@ var calculationCells = calculationChain.Elements().Where( cc => cc.CellReference != null && cc.CellReference == c.Address.ToString()).Select(cc=>cc); Boolean addNew = true; - if (calculationCells.Count() > 0) + if (calculationCells.FirstOrDefault() != null) { calculationCells.Where(cc=>cc.SheetId == null).Select(cc=>cc).ForEach(cc=>calculationChain.RemoveChild(cc)); var cCell = calculationCells.FirstOrDefault(cc=>cc.SheetId == worksheet.SheetId); @@ -2257,17 +2349,17 @@ } } - var cCellsToRemove = new List(); - var m = from cc in calculationChain.Elements() - where cc.SheetId == null - && calculationChain.Elements() - .Where(c1 => c1.SheetId != null) - .Select(c1 => c1.CellReference.Value) - .Contains(cc.CellReference.Value) - || worksheet.Internals.CellsCollection.Where(kp=>kp.Key.ToString() == cc.CellReference.Value && StringExtensions.IsNullOrWhiteSpace(kp.Value.FormulaA1)).Any() - select cc; - m.ForEach(cc => cCellsToRemove.Add(cc)); - cCellsToRemove.ForEach(cc=>calculationChain.RemoveChild(cc)); + var cCellsToRemove = new List(); + var m = from cc in calculationChain.Elements() + where cc.SheetId == null + && calculationChain.Elements() + .Where(c1 => c1.SheetId != null) + .Select(c1 => c1.CellReference.Value) + .Contains(cc.CellReference.Value) + || worksheet.Internals.CellsCollection.Where(kp=>kp.Key.ToString() == cc.CellReference.Value && StringExtensions.IsNullOrWhiteSpace(kp.Value.FormulaA1)).Any() + select cc; + m.ForEach(cc => cCellsToRemove.Add(cc)); + cCellsToRemove.ForEach(cc=>calculationChain.RemoveChild(cc)); } if (calculationChain.Count() == 0) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index 98dcd9d..52e41fb 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -175,7 +175,17 @@ sheetIndex = value; } } - + public Int32 Position + { + get + { + return sheetIndex + 1; + } + set + { + SheetIndex = value - 1; + } + } public IXLPageSetup PageSetup { get; private set; } public IXLOutline Outline { get; private set; } @@ -502,6 +512,31 @@ public IXLSheetView SheetView { get; private set; } public IXLTables Tables { get; private set; } + public IXLWorksheet CopyTo(String newSheetName) + { + return CopyTo(this.workbook, newSheetName, workbook.Worksheets.Count() + 1); + } + + public IXLWorksheet CopyTo(String newSheetName, Int32 position) + { + return CopyTo(this.workbook, newSheetName, position); + } + + public IXLWorksheet CopyTo(XLWorkbook workbook, String newSheetName) + { + return CopyTo(workbook, newSheetName, workbook.Worksheets.Count() + 1); + } + + public IXLWorksheet CopyTo(XLWorkbook workbook, String newSheetName, Int32 position) + { + var ws = (XLWorksheet)workbook.Worksheets.Add(newSheetName, position); + this.Internals.CellsCollection.ForEach(kp => ws.Internals.CellsCollection.Add(kp)); + this.Internals.ColumnsCollection.ForEach(kp => ws.Internals.ColumnsCollection.Add(kp)); + this.Internals.MergedRanges.ForEach(kp => ws.Internals.MergedRanges.Add(kp)); + this.Internals.RowsCollection.ForEach(kp => ws.Internals.RowsCollection.Add(kp)); + return ws; + } + #region Outlines private Dictionary columnOutlineCount = new Dictionary(); public void IncrementColumnOutline(Int32 level) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs index 21deb40..a67a02f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs @@ -42,6 +42,13 @@ return sheet; } + public IXLWorksheet Add(String sheetName, Int32 position) + { + var ws = Add(sheetName); + ws.Position = position; + return ws; + } + public void Delete(String sheetName) { Delete(worksheets[sheetName].SheetIndex); diff --git a/ClosedXML/ClosedXML/ClosedXML/Extensions.cs b/ClosedXML/ClosedXML/ClosedXML/Extensions.cs index 9d60bea..9f0de2d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Extensions.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Extensions.cs @@ -3,6 +3,7 @@ using System.Linq; using System.Text; using System.Drawing; +using System.Globalization; namespace ClosedXML { @@ -110,5 +111,19 @@ return true; } } + + public static class IntegerExtensions + { + private static NumberFormatInfo nfi = CultureInfo.InvariantCulture.NumberFormat; + private static Dictionary intToString = new Dictionary(); + public static String ToStringLookup(this Int32 value) + { + if (!intToString.ContainsKey(value)) + { + intToString.Add(value, value.ToString(nfi)); + } + return intToString[value]; + } + } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj index f658c7d..20d97b6 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -58,6 +58,7 @@ + diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs index 97d387f..c86f49d 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs @@ -65,6 +65,7 @@ new FreezePanes().Create(@"C:\Excel Files\Created\FreezePanes.xlsx"); new UsingTables().Create(@"C:\Excel Files\Created\UsingTables.xlsx"); new ShowCase().Create(@"C:\Excel Files\Created\ShowCase.xlsx"); + new CopyingWorksheets().Create(); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/CopyingWorksheets.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/CopyingWorksheets.cs new file mode 100644 index 0000000..10767ae --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/CopyingWorksheets.cs @@ -0,0 +1,70 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; + +using System.Drawing; + +namespace ClosedXML_Examples.Misc +{ + public class CopyingWorksheets + { + #region Variables + + // Public + + // Private + + + #endregion + + #region Properties + + // Public + + // Private + + // Override + + + #endregion + + #region Events + + // Public + + // Private + + // Override + + + #endregion + + #region Methods + + // Public + public void Create() + { + var wb = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); + var wsSource = wb.Worksheet(0); + // Copy the worksheet to a new sheet in this workbook + wsSource.CopyTo("Copy"); + + // We're going to open another workbook to show that you can + // copy a sheet from one workbook to another: + var wbSource = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); + wbSource.Worksheet(0).CopyTo(wb, "Copy From Other"); + + // Save the workbook with the 2 copies + wb.SaveAs(@"C:\Excel Files\Created\CopyingWorksheets.xlsx"); + } + + // Private + + // Override + + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index 9249434..4bf5333 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -12,14 +12,19 @@ { class Program { - static void Main(string[] args) + static void xMain(string[] args) { - var wb = new XLWorkbook(@"C:\Excel Files\ForTesting\Blank_Modified.xlsx"); - wb.SaveAs(@"C:\Excel Files\ForTesting\Blank_Modified2.xlsx"); - //var l = new List(); + //var workbook = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); + var wb = new XLWorkbook(@"C:\Excel Files\ForTesting\Issue_6107.xlsx"); + - //for (Int32 i = 1; i <= 10; i++) - // ClosedXML_Examples.Program.ExecuteMain(); + var templateWorksheet = wb.Worksheets.Where(w => w.Name == "TEMPLATE").FirstOrDefault(); + var agentWorksheet = wb.Worksheets.Add("new"); + + agentWorksheet.FirstCell().Value = templateWorksheet.Range(templateWorksheet.FirstCellUsed(), + templateWorksheet.LastCellUsed()); + + wb.SaveAs(@"C:\Excel Files\ForTesting\Issue_6107_Saved.xlsx"); } static void Main_5961(string[] args) { @@ -46,15 +51,16 @@ wb.Save(); } - static void xMain(string[] args) + static void Main(string[] args) { FillStyles(); List runningSave = new List(); List runningLoad = new List(); List runningSavedBack = new List(); - var wb = new XLWorkbook(); + foreach (Int32 r in Enumerable.Range(1, 5)) { + var wb = new XLWorkbook(); var startTotal = DateTime.Now; foreach (var i in Enumerable.Range(1, 1)) {