diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index 17b5dfa..70e02b3 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -143,7 +143,7 @@ } } set - { + { FormulaA1 = String.Empty; if (!SetEnumerable(value)) if (!SetRange(value)) @@ -177,12 +177,19 @@ { 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); - targetCell.Style = sourceCell.Style; - targetCell.DataType = sourceCell.DataType; - targetCell.Value = sourceCell.Value; - targetCell.FormulaA1 = sourceCell.FormulaA1; + var sourceCell = (XLCell)asRange.Cell(ro, co); + var targetCell = (XLCell)worksheet.Cell(Address.RowNumber + ro - 1, Address.ColumnNumber + co - 1); + if (!targetCell.Style.Equals(sourceCell.Style)) + targetCell.Style = sourceCell.Style; + + if (targetCell.InnerText != sourceCell.InnerText) + targetCell.Value = sourceCell.Value; + + if (targetCell.DataType != sourceCell.DataType) + targetCell.DataType = sourceCell.DataType; + + if (targetCell.FormulaA1 != sourceCell.FormulaA1) + targetCell.FormulaA1 = sourceCell.FormulaA1; } } var rangesToMerge = new List(); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs index af7ee5d..6b7236c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs @@ -11,6 +11,7 @@ Double RowHeight { get; set; } String Name { get; set; } + Int32 SheetIndex { get; set; } IXLPageSetup PageSetup { get; } IXLOutline Outline { get; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index 2d1d774..01f448e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -89,7 +89,7 @@ var sheetName = dSheet.Name; var ws = (XLWorksheet)Worksheets.Add(sheetName); - + ws.RelId = dSheet.Id; var sheetFormatProperties = (SheetFormatProperties)worksheetPart.Worksheet.Descendants().First(); if (sheetFormatProperties.DefaultRowHeight != null) ws.RowHeight = sheetFormatProperties.DefaultRowHeight; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 2fc8910..2847748 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -277,6 +277,9 @@ else workbookPart = document.WorkbookPart; + var worksheets = (XLWorksheets)Worksheets; + var partsToRemove = workbookPart.Parts.Where(s => worksheets.Deleted.Contains(s.RelationshipId)).ToList(); + partsToRemove.ForEach(s => workbookPart.DeletePart(s.OpenXmlPart)); relId.AddValues(workbookPart.Parts.Select(p=>p.RelationshipId).ToList(), RelType.Workbook); var modifiedSheetNames = Worksheets.Select(w => w.Name.ToLower()).ToList(); @@ -315,18 +318,14 @@ GenerateWorkbookStylesPartContent(workbookStylesPart); - foreach (var worksheet in Worksheets.Cast().OrderBy(w=>w.SheetId)) + foreach (var worksheet in Worksheets.Cast().OrderBy(w=>w.SheetIndex)) { - //Int32 sheetCount = Worksheets.Count(); - //for (Int32 i = 0; i < sheetCount; i++) - //{ - // var worksheet = (XLWorksheet)Worksheets.Worksheet(i); WorksheetPart worksheetPart; var sheets = workbookPart.Workbook.Sheets.Elements(); - if (workbookPart.Parts.Where(p => p.RelationshipId == "rId" + worksheet.SheetId.ToString()).Any()) - worksheetPart = (WorksheetPart)workbookPart.GetPartById("rId" + worksheet.SheetId.ToString()); + if (workbookPart.Parts.Where(p => p.RelationshipId == worksheet.RelId).Any()) + worksheetPart = (WorksheetPart)workbookPart.GetPartById(worksheet.RelId); else - worksheetPart = workbookPart.AddNewPart("rId" + worksheet.SheetId.ToString()); + worksheetPart = workbookPart.AddNewPart(worksheet.RelId); GenerateWorksheetPartContent(worksheetPart, worksheet); } @@ -368,11 +367,9 @@ if (properties.TitlesOfParts == null) properties.TitlesOfParts = new Ap.TitlesOfParts(); - if (properties.HeadingPairs.VTVector == null) - properties.HeadingPairs.VTVector = new Vt.VTVector() { BaseType = Vt.VectorBaseValues.Variant}; + properties.HeadingPairs.VTVector = new Vt.VTVector() { BaseType = Vt.VectorBaseValues.Variant}; - if (properties.TitlesOfParts.VTVector == null) - properties.TitlesOfParts.VTVector = new Vt.VTVector() { BaseType = Vt.VectorBaseValues.Lpstr }; + properties.TitlesOfParts.VTVector = new Vt.VTVector() { BaseType = Vt.VectorBaseValues.Lpstr }; Vt.VTVector vTVector_One; vTVector_One = properties.HeadingPairs.VTVector; @@ -380,36 +377,22 @@ Vt.VTVector vTVector_Two; vTVector_Two = properties.TitlesOfParts.VTVector; - - var modifiedWorksheets = Worksheets.Select(w => w.Name).ToList(); - var modifiedNamedRanges = GetModifiedNamedRanges().Union(modifiedWorksheets); - var existingNamedRanges = GetExistingNamedRanges(vTVector_Two); - var existingWorksheets = GetExistingWorksheets(workbookPart); + var modifiedWorksheets = Worksheets.Select(w => new { w.Name, Order = w.SheetIndex }); + var modifiedNamedRanges = GetModifiedNamedRanges(); - var allWorksheets = existingWorksheets.Union(modifiedWorksheets); - var allNamedRanges = existingNamedRanges.Union(modifiedNamedRanges); + InsertOnVTVector(vTVector_One, "Worksheets", 0, modifiedWorksheets.Count().ToString()); + InsertOnVTVector(vTVector_One, "Named Ranges", 2, (modifiedNamedRanges.Count()).ToString()); - InsertOnVTVector(vTVector_One, "Worksheets", 0, allWorksheets.Count().ToString()); - InsertOnVTVector(vTVector_One, "Named Ranges", 2, (allNamedRanges.Count() - allWorksheets.Count()).ToString()); + vTVector_Two.Size = (UInt32)(modifiedNamedRanges.Count() + modifiedWorksheets.Count()); - vTVector_Two.Size = (UInt32)(allNamedRanges.Count()); - - var worksheetsToInsert = from w in modifiedWorksheets - where !vTVector_Two.Elements().Any(m => w.ToLower() == m.Text.ToLower()) - select w; - - var namedRangesToInsert = from r in modifiedNamedRanges - where !vTVector_Two.Elements().Any(m => r.ToLower() == m.Text.ToLower()) - select r; - - foreach (var w in worksheetsToInsert) + foreach (var w in modifiedWorksheets.OrderBy(w=>w.Order)) { - Vt.VTLPSTR vTLPSTR3 = new Vt.VTLPSTR() { Text = w }; + Vt.VTLPSTR vTLPSTR3 = new Vt.VTLPSTR() { Text = w.Name }; vTVector_Two.Append(vTLPSTR3); } - foreach (var nr in namedRangesToInsert) + foreach (var nr in modifiedNamedRanges) { Vt.VTLPSTR vTLPSTR7 = new Vt.VTLPSTR() { Text = nr }; vTVector_Two.Append(vTLPSTR7); @@ -535,20 +518,39 @@ if (workbook.Sheets == null) workbook.Sheets = new Sheets(); + var worksheets = (XLWorksheets)Worksheets; + workbook.Sheets.Elements().Where(s => worksheets.Deleted.Contains(s.Id)).ForEach(s => s.Remove()); + foreach (var sheet in workbook.Sheets.Elements()) { var sName = sheet.Name.Value; if (Worksheets.Where(w => w.Name.ToLower() == sName.ToLower()).Any()) - ((XLWorksheet)Worksheets.Where(w => w.Name.ToLower() == sName.ToLower()).Single()).SheetId = (Int32)sheet.SheetId.Value; + { + var wks = (XLWorksheet)Worksheets.Where(w => w.Name.ToLower() == sName.ToLower()).Single(); + wks.SheetId = (Int32)sheet.SheetId.Value; + wks.RelId = sheet.Id; + } } - foreach (var xlSheet in Worksheets.Cast().Where(w=>w.SheetId == 0)) + foreach (var xlSheet in Worksheets.Cast().Where(w => w.SheetId == 0).OrderBy(w => w.SheetIndex)) { var 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 }); } + var sheetElements = from sheet in workbook.Sheets.Elements() + join worksheet in Worksheets.Cast() on sheet.Id.Value equals worksheet.RelId + orderby worksheet.SheetIndex + select sheet; + + foreach (var sheet in sheetElements) + { + workbook.Sheets.RemoveChild(sheet); + workbook.Sheets.Append(sheet); + } + DefinedNames definedNames = new DefinedNames(); foreach (var worksheet in Worksheets.Cast()) { @@ -677,7 +679,7 @@ private void GenerateSharedStringTablePartContent(SharedStringTablePart sharedStringTablePart) { - Dictionary modifiedStrings = new Dictionary(); + HashSet modifiedStrings = new HashSet(); foreach (var w in Worksheets.Cast()) { foreach (var c in w.Internals.CellsCollection.Values) @@ -685,10 +687,10 @@ if ( c.DataType == XLCellValues.Text && !StringExtensions.IsNullOrWhiteSpace(c.InnerText) - && !modifiedStrings.ContainsKey(c.Value.ToString()) + && !modifiedStrings.Contains(c.Value.ToString()) ) { - modifiedStrings.Add(c.Value.ToString(), null); + modifiedStrings.Add(c.Value.ToString()); } } } @@ -698,7 +700,7 @@ UInt32 stringCount = (UInt32)modifiedStrings.Count(); Int32 stringId = 0; - foreach (var s in modifiedStrings.Keys) + foreach (var s in modifiedStrings) { SharedStringItem sharedStringItem = new SharedStringItem(); Text text = new Text(); @@ -1637,8 +1639,10 @@ { cell.CellFormula = null; - if (opCell.Value.DataType != XLCellValues.DateTime) - cell.DataType = GetCellValue(dataType); + if (opCell.Value.DataType == XLCellValues.DateTime) + cell.DataType = null; + else + cell.DataType = GetCellValue(dataType); CellValue cellValue = new CellValue(); if (dataType == XLCellValues.Text) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index 2ddd527..7e842ea 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -152,6 +152,30 @@ public String Name { get; set; } public Int32 SheetId { get; set; } + public String RelId { get; set; } + + + internal Int32 sheetIndex; + public Int32 SheetIndex + { + get + { + return sheetIndex; + } + set + { + if (value > workbook.Worksheets.Count()) + throw new IndexOutOfRangeException("Index must be equal or less than the number of worksheets."); + + if (value < sheetIndex) + workbook.Worksheets.Where(w => w.SheetIndex >= value && w.SheetIndex < sheetIndex).ForEach(w => ((XLWorksheet)w).sheetIndex += 1); + + if (value > sheetIndex) + workbook.Worksheets.Where(w => w.SheetIndex <= value && w.SheetIndex > sheetIndex).ForEach(w => ((XLWorksheet)w).sheetIndex -= 1); + + sheetIndex = value; + } + } public IXLPageSetup PageSetup { get; private set; } public IXLOutline Outline { get; private set; } @@ -344,16 +368,16 @@ { // This is a new row so we're going to reference all // cells in columns of this row to preserve their formatting - var distinctColumns = new Dictionary(); + var distinctColumns = new HashSet(); foreach (var k in this.Internals.CellsCollection.Keys) { - if (!distinctColumns.ContainsKey(k.ColumnNumber)) - distinctColumns.Add(k.ColumnNumber, null); + if (!distinctColumns.Contains(k.ColumnNumber)) + distinctColumns.Add(k.ColumnNumber); } var usedColumns = from c in this.Internals.ColumnsCollection join dc in distinctColumns - on c.Key equals dc.Key + on c.Key equals dc where !this.Internals.CellsCollection.ContainsKey(new XLAddress(row, c.Key)) select c.Key; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs index dfe2573..21deb40 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs @@ -8,7 +8,7 @@ internal class XLWorksheets : IXLWorksheets { Dictionary worksheets = new Dictionary(); - + public HashSet Deleted = new HashSet(); XLWorkbook workbook; public XLWorksheets(XLWorkbook workbook) { @@ -17,31 +17,51 @@ #region IXLWorksheets Members - public IXLWorksheet Worksheet(string sheetName) + public IXLWorksheet Worksheet(String sheetName) { return worksheets[sheetName]; } - public IXLWorksheet Worksheet(int sheetIndex) + public IXLWorksheet Worksheet(Int32 sheetIndex) { - return worksheets.ElementAt(sheetIndex).Value; + var wsCount = worksheets.Values.Where(w => w.SheetIndex == sheetIndex).Count(); + if (wsCount == 0) + throw new Exception("There isn't a worksheet associated with that index."); + + if (wsCount > 1) + throw new Exception("Can't retrieve a worksheet because there are multiple worksheets associated with that index."); + + return worksheets.Values.Where(w => w.SheetIndex == sheetIndex).Single(); } public IXLWorksheet Add(String sheetName) { var sheet = new XLWorksheet(sheetName, workbook); worksheets.Add(sheetName, sheet); + sheet.sheetIndex = worksheets.Count - 1; return sheet; } - public void Delete(string sheetName) + public void Delete(String sheetName) { - worksheets.Remove(sheetName); + Delete(worksheets[sheetName].SheetIndex); } public void Delete(Int32 sheetIndex) { - worksheets.Remove(worksheets.ElementAt(sheetIndex).Key); + var wsCount = worksheets.Values.Where(w => w.SheetIndex == sheetIndex).Count(); + if (wsCount == 0) + throw new Exception("There isn't a worksheet associated with that index."); + + if (wsCount > 1) + throw new Exception("Can't delete the worksheet because there are multiple worksheets associated with that index."); + + var ws = (XLWorksheet)worksheets.Values.Where(w => w.SheetIndex == sheetIndex).Single(); + if (!StringExtensions.IsNullOrWhiteSpace(ws.RelId) && !Deleted.Contains(ws.RelId)) + Deleted.Add(ws.RelId); + + worksheets.RemoveAll(w => w.SheetIndex == sheetIndex); + worksheets.Values.Where(w => w.SheetIndex > sheetIndex).ForEach(w => ((XLWorksheet)w).sheetIndex -= 1); } #endregion diff --git a/ClosedXML/ClosedXML/ClosedXML/Extensions.cs b/ClosedXML/ClosedXML/ClosedXML/Extensions.cs index c92bb4c..9d60bea 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Extensions.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Extensions.cs @@ -65,6 +65,19 @@ { return instance.Length - instance.Replace(c.ToString(), "").Length; } + + public static Boolean HasDuplicates(this IEnumerable source) + { + HashSet distinctItems = new HashSet(); + foreach (var item in source) + { + if (distinctItems.Contains(item)) + return true; + else + distinctItems.Add(item); + } + return false; + } } public static class DictionaryExtensions diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs index 1050d41..ac1406b 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs @@ -27,7 +27,7 @@ new InsertColumns().Create(@"C:\Excel Files\Created\InsertColumns.xlsx"); new ColumnCollection().Create(@"C:\Excel Files\Created\ColumnCollection.xlsx"); new DataTypes().Create(@"C:\Excel Files\Created\DataTypes.xlsx"); - new MultipleSheets().Create(@"C:\Excel Files\Created\MultipleSheets.xlsx"); + new MultipleSheets().Create(); new RowCollection().Create(@"C:\Excel Files\Created\RowCollection.xlsx"); new DefiningRanges().Create(@"C:\Excel Files\Created\DefiningRanges.xlsx"); new ClearingRanges().Create(@"C:\Excel Files\Created\ClearingRanges.xlsx"); diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MultipleSheets.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MultipleSheets.cs index b7bd1a2..2ebebf3 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MultipleSheets.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MultipleSheets.cs @@ -44,15 +44,14 @@ #region Methods // Public - public void Create(String filePath) + public void Create() { - var workbook = new XLWorkbook(); - foreach (var wsNum in Enumerable.Range(1, 5)) - { - var ws = workbook.Worksheets.Add("Sheet " + wsNum.ToString()); - } - - workbook.SaveAs(filePath); + var wb = new XLWorkbook(@"C:\Excel Files\Created\MultipleSheets.xlsx"); + var ws = wb.Worksheets.Add("NewOne"); + wb.Worksheets.Worksheet(0).Delete(); + ws.SheetIndex = 0; + wb.Worksheets.Worksheet("Inserted").SheetIndex = wb.Worksheets.Count(); + wb.SaveAs(@"C:\Excel Files\Created\MultipleSheets_Saved.xlsx"); } // Private diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index 00d51cb..1253644 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -1,4 +1,5 @@ -using System;using System.Collections.Generic; +using System; +using System.Collections.Generic; using System.Linq; using System.Text; using ClosedXML.Excel; @@ -12,37 +13,66 @@ { static void Main(string[] args) { - - - FillStyles(); var wb = new XLWorkbook(); - foreach (var i in Enumerable.Range(1, 1)) + foreach (var wsNum in Enumerable.Range(0, 5)) { - var ws = wb.Worksheets.Add("Sheet" + i); - foreach (var ro in Enumerable.Range(1, 100)) - { - foreach (var co in Enumerable.Range(1, 100)) - { - ws.Cell(ro, co).Style = GetRandomStyle(); - ws.Cell(ro, co).Value = GetRandomValue(); - } - System.Threading.Thread.Sleep(10); - } + wb.Worksheets.Add("Original Pos. is " + wsNum.ToString()); } - var start = DateTime.Now; - wb.SaveAs(@"C:\Excel Files\ForTesting\Benchmark.xlsx"); - var end = DateTime.Now; - Console.WriteLine("Saved in {0} secs.", (end - start).TotalSeconds); - var start1 = DateTime.Now; - var wb1 = new XLWorkbook(@"C:\Excel Files\ForTesting\Benchmark.xlsx"); + // Move first worksheet to the last position + wb.Worksheets.Worksheet(0).SheetIndex = wb.Worksheets.Count(); - var end1 = DateTime.Now; - Console.WriteLine("Loaded in {0} secs.", (end1 - start1).TotalSeconds); + // Delete worksheet on position 2 (in this case it's where original position = 3) + wb.Worksheets.Worksheet(2).Delete(); - //var ws = wb.Worksheets.Worksheet(0); - //wb.SaveAs(@"C:\Excel Files\ForTesting\Benchmark_Saved.xlsx"); + // Swap sheets in positions 0 and 1 + wb.Worksheets.Worksheet(1).SheetIndex = 0; + wb.SaveAs(@"C:\Excel Files\ForTesting\OrganizingSheets.xlsx"); + } + static void xMain(string[] args) + { + List running = new List(); + foreach (Int32 r in Enumerable.Range(1, 1)) + { + var startTotal = DateTime.Now; + + FillStyles(); + var wb = new XLWorkbook(); + foreach (var i in Enumerable.Range(1, 3)) + { + var ws = wb.Worksheets.Add("Sheet" + i); + foreach (var ro in Enumerable.Range(1, 100)) + { + foreach (var co in Enumerable.Range(1, 100)) + { + ws.Cell(ro, co).Style = GetRandomStyle(); + ws.Cell(ro, co).Value = GetRandomValue(); + } + //System.Threading.Thread.Sleep(10); + } + } + var start = DateTime.Now; + wb.SaveAs(@"C:\Excel Files\ForTesting\Benchmark.xlsx"); + var end = DateTime.Now; + Console.WriteLine("Saved in {0} secs.", (end - start).TotalSeconds); + + var start1 = DateTime.Now; + var wb1 = new XLWorkbook(@"C:\Excel Files\ForTesting\Benchmark.xlsx"); + + var end1 = DateTime.Now; + Console.WriteLine("Loaded in {0} secs.", (end1 - start1).TotalSeconds); + var start2 = DateTime.Now; + wb1.SaveAs(@"C:\Excel Files\ForTesting\Benchmark_Saved.xlsx"); + var end2 = DateTime.Now; + Console.WriteLine("Saved back in {0} secs.", (end2 - start2).TotalSeconds); + + var endTotal = DateTime.Now; + Console.WriteLine("It all took {0} secs.", (endTotal - startTotal).TotalSeconds); + running.Add((endTotal - startTotal).TotalSeconds); + } + Console.WriteLine("-------"); + Console.WriteLine("Avg total time: {0}", running.Average()); //Console.ReadKey(); } @@ -73,7 +103,7 @@ private static IXLStyle GetRandomStyle() { - var val = rnd.Next(1, 3); + var val = rnd.Next(1, 4); if (val == 1) { return style1; @@ -90,7 +120,7 @@ private static Random rnd = new Random(); private static object GetRandomValue() { - var val = rnd.Next(1, 6); + var val = rnd.Next(1, 7); if (val == 1) return Guid.NewGuid().ToString().Substring(1, 5); else if (val == 2)