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))
{
| |