diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index 28a9ff2..1666c10 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -122,6 +122,7 @@ } set { + FormulaA1 = String.Empty; if (!SetEnumerable(value)) if (!SetRange(value)) SetValue(value); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCellCollection.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCellCollection.cs index 5b1afd0..5e4fe6a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCellCollection.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCellCollection.cs @@ -5,54 +5,46 @@ namespace ClosedXML.Excel { - //internal delegate void RangeShiftedDelegate(XLRange range, Int32 cellsToShift, XLShiftDirection shiftDirection); - - internal class XLCellsCollection : IDictionary + internal class XLCellCollection : IDictionary { - //public event RangeShiftedDelegate RangeShifted; + private Dictionary dictionary = new Dictionary(); - //public void ShiftRange(XLRange range, Int32 cellsToShift, XLShiftDirection shiftDirection) - //{ - - // foreach (var ro in dictionary.Keys.Where(k => k >= startingCell).OrderByDescending(k => k)) - // { - // var cellToMove = dictionary[ro]; - // var newCell = ro + cellsToShift; - // if (newCell <= XLWorksheet.MaxNumberOfCells) - // { - // var xlCellParameters = new XLCellParameters(cellToMove.Worksheet, cellToMove.Style, false); - // dictionary.Add(newCell, new XLCell(newCell, xlCellParameters)); - // } - // dictionary.Remove(ro); - - // if (RangeShifted != null) - // RangeShifted(ro, newCell); - // } - //} - - private Dictionary dictionary = new Dictionary(); - - public void Add(int key, XLCell value) + private Dictionary deleted = new Dictionary(); + public Dictionary Deleted { + get + { + return deleted; + } + } + + public void Add(IXLAddress key, XLCell value) + { + if (deleted.ContainsKey(key)) + deleted.Remove(key); + dictionary.Add(key, value); } - public bool ContainsKey(int key) + public bool ContainsKey(IXLAddress key) { return dictionary.ContainsKey(key); } - public ICollection Keys + public ICollection Keys { get { return dictionary.Keys; } } - public bool Remove(int key) + public bool Remove(IXLAddress key) { + if (!deleted.ContainsKey(key)) + deleted.Add(key, dictionary[key]); + return dictionary.Remove(key); } - public bool TryGetValue(int key, out XLCell value) + public bool TryGetValue(IXLAddress key, out XLCell value) { return dictionary.TryGetValue(key, out value); } @@ -62,7 +54,7 @@ get { return dictionary.Values; } } - public XLCell this[int key] + public XLCell this[IXLAddress key] { get { @@ -74,22 +66,29 @@ } } - public void Add(KeyValuePair item) + public void Add(KeyValuePair item) { + if (deleted.ContainsKey(item.Key)) + deleted.Remove(item.Key); dictionary.Add(item.Key, item.Value); } public void Clear() { + foreach (var kp in dictionary) + { + if (!deleted.ContainsKey(kp.Key)) + deleted.Add(kp.Key, kp.Value); + } dictionary.Clear(); } - public bool Contains(KeyValuePair item) + public bool Contains(KeyValuePair item) { return dictionary.Contains(item); } - public void CopyTo(KeyValuePair[] array, int arrayIndex) + public void CopyTo(KeyValuePair[] array, int arrayIndex) { throw new NotImplementedException(); } @@ -104,12 +103,15 @@ get { return false; } } - public bool Remove(KeyValuePair item) + public bool Remove(KeyValuePair item) { + if (!deleted.ContainsKey(item.Key)) + deleted.Add(item.Key, dictionary[item.Key]); + return dictionary.Remove(item.Key); } - public IEnumerator> GetEnumerator() + public IEnumerator> GetEnumerator() { return dictionary.GetEnumerator(); } @@ -118,5 +120,21 @@ { return dictionary.GetEnumerator(); } + + public void RemoveAll() + { + RemoveAll(c => true); + } + + public void RemoveAll(Func predicate) + { + foreach (var kp in dictionary.Values.Where(predicate).Select(c=>c)) + { + if (!deleted.ContainsKey(kp.Address)) + deleted.Add(kp.Address, kp); + } + + dictionary.RemoveAll(predicate); + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheetInternals.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheetInternals.cs index 9bd19c6..aefd5d8 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheetInternals.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheetInternals.cs @@ -7,7 +7,7 @@ { internal interface IXLWorksheetInternals { - Dictionary CellsCollection { get; } + XLCellCollection CellsCollection { get; } XLColumnsCollection ColumnsCollection { get; } XLRowsCollection RowsCollection { get; } List MergedCells { get; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index c11acc4..5f35186 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -31,12 +31,16 @@ if (ignoreStyle) cellsUsed = cellsUsed.Where(c => c.GetString().Length != 0); - var cellsUsedFiltered = cellsUsed.Where(cell => cell.Address == cellsUsed.Min(c => c.Address)); - - if (cellsUsedFiltered.Count() > 0) - return cellsUsedFiltered.Single(); - else + if (cellsUsed.Count() == 0) + { return null; + } + else + { + var firstRow = cellsUsed.Min(c => c.Address.RowNumber); + var firstColumn = cellsUsed.Min(c => c.Address.ColumnNumber); + return Worksheet.Cell(firstRow, firstColumn); + } } public IXLCell LastCellUsed(Boolean ignoreStyle = true) @@ -45,11 +49,16 @@ if (ignoreStyle) cellsUsed = cellsUsed.Where(c => c.GetString().Length != 0); - var cellsUsedFiltered = cellsUsed.Where(cell => cell.Address == cellsUsed.Max(c => c.Address)); - if (cellsUsedFiltered.Count() > 0) - return cellsUsedFiltered.Single(); - else + if (cellsUsed.Count() == 0) + { return null; + } + else + { + var lastRow = cellsUsed.Max(c => c.Address.RowNumber); + var lastColumn = cellsUsed.Max(c => c.Address.ColumnNumber); + return Worksheet.Cell(lastRow, lastColumn); + } } public IXLCell Cell(Int32 row, Int32 column) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLAlignment.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLAlignment.cs index b77ff65..29bbf3d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLAlignment.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLAlignment.cs @@ -8,6 +8,9 @@ internal class XLAlignment : IXLAlignment { IXLStylized container; + + public XLAlignment() : this(null, XLWorkbook.DefaultStyle.Alignment) { } + public XLAlignment(IXLStylized container, IXLAlignment d = null) { this.container = container; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLBorder.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLBorder.cs index 8ce1366..33401ef 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLBorder.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLBorder.cs @@ -8,7 +8,10 @@ internal class XLBorder : IXLBorder { IXLStylized container; - public XLBorder(IXLStylized container, IXLBorder defaultBorder = null) + + public XLBorder() : this(null, XLWorkbook.DefaultStyle.Border) { } + + public XLBorder(IXLStylized container, IXLBorder defaultBorder) { this.container = container; if (defaultBorder != null) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFill.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFill.cs index 9830f68..1f005e2 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFill.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFill.cs @@ -83,6 +83,10 @@ #region Constructors + public XLFill(): this(null, XLWorkbook.DefaultStyle.Fill) + { + } + IXLStylized container; public XLFill(IXLStylized container, IXLFill defaultFill = null) { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs index fc38114..681b891 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs @@ -11,24 +11,11 @@ { IXLStylized container; public XLFont() + : this(null, XLWorkbook.DefaultStyle.Font) { - IXLFont defaultFont = XLWorkbook.DefaultStyle.Font; - if (defaultFont != null) - { - bold = defaultFont.Bold; - italic = defaultFont.Italic; - underline = defaultFont.Underline; - strikethrough = defaultFont.Strikethrough; - verticalAlignment = defaultFont.VerticalAlignment; - shadow = defaultFont.Shadow; - fontSize = defaultFont.FontSize; - fontColor = defaultFont.FontColor; - fontName = defaultFont.FontName; - fontFamilyNumbering = defaultFont.FontFamilyNumbering; - } } - public XLFont(IXLStylized container, IXLFont defaultFont = null) + public XLFont(IXLStylized container, IXLFont defaultFont) { this.container = container; if (defaultFont != null) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLNumberFormat.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLNumberFormat.cs index f52eccc..7a8e905 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLNumberFormat.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLNumberFormat.cs @@ -51,7 +51,13 @@ #region Constructors - public XLNumberFormat(IXLStylized container, IXLNumberFormat defaultNumberFormat = null) + public XLNumberFormat() + : this(null, XLWorkbook.DefaultStyle.NumberFormat) + { + } + + + public XLNumberFormat(IXLStylized container, IXLNumberFormat defaultNumberFormat) { this.container = container; if (defaultNumberFormat != null) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLStyle.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLStyle.cs index ea2678a..cedb2e2 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLStyle.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLStyle.cs @@ -19,11 +19,11 @@ } else { - Font = new XLFont(container); + Font = new XLFont(container, null); Alignment = new XLAlignment(container); - Border = new XLBorder(container); + Border = new XLBorder(container, null); Fill = new XLFill(container); - NumberFormat = new XLNumberFormat(container); + NumberFormat = new XLNumberFormat(container, null); } DateFormat = NumberFormat; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs index cda483a..3f720c0 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs @@ -29,8 +29,10 @@ ReferenceStyle = XLReferenceStyle.Default; } + private String originalFile; public XLWorkbook(String file): this() { + originalFile = file; Load(file); } @@ -49,12 +51,32 @@ /// public String FullName { get; private set; } - public void SaveAs(String file, Boolean overwrite = false) + public void Save() { - if (overwrite && File.Exists(file)) File.Delete(file); + if (originalFile == null) + throw new Exception("This is a new file, please use one of the following methods: SaveAs, MergeInto, or SaveChangesTo"); - // For maintainability reasons the XLWorkbook class was divided into two files. - // The method CreatePackage can be located in the file XLWorkbook_Save.cs + MergeInto(originalFile); + } + + public void SaveAs(String file) + { + if (originalFile == null) + File.Delete(file); + else if (originalFile.Trim().ToLower() != file.Trim().ToLower()) + File.Copy(originalFile, file, true); + + CreatePackage(file); + } + + public void MergeInto(String file) + { + CreatePackage(file); + } + + public void SaveChangesTo(String file) + { + if (File.Exists(file)) File.Delete(file); CreatePackage(file); } @@ -97,7 +119,7 @@ PatternColor = Color.FromArgb(255, 255, 255) }, - Border = new XLBorder(null) + Border = new XLBorder(null, null) { BottomBorder = XLBorderStyleValues.None, DiagonalBorder = XLBorderStyleValues.None, @@ -112,7 +134,7 @@ RightBorderColor = Color.Black, TopBorderColor = Color.Black }, - NumberFormat = new XLNumberFormat(null) { NumberFormatId = 0 }, + NumberFormat = new XLNumberFormat(null, null) { NumberFormatId = 0 }, Alignment = new XLAlignment(null) { Horizontal = XLAlignmentHorizontalValues.General, diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbookProperties.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbookProperties.cs index 8131a83..16c6b4d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbookProperties.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbookProperties.cs @@ -7,6 +7,11 @@ { public class XLWorkbookProperties { + public XLWorkbookProperties() + { + Company = null; + Manager = null; + } public String Author { get; set; } public String Title { get; set; } public String Subject { get; set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index ba8cc18..4c571c3 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -61,52 +61,16 @@ 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) { - var dSheet = ((Sheet)sheet); + var sharedFormulas = new Dictionary(); + + Sheet dSheet = ((Sheet)sheet); WorksheetPart worksheetPart = (WorksheetPart)dSpreadsheet.WorkbookPart.GetPartById(dSheet.Id); var sheetName = dSheet.Name; @@ -201,15 +165,20 @@ } } - + foreach (var cell in worksheetPart.Worksheet.Descendants() + .Where(c=>c.CellFormula != null && c.CellFormula.SharedIndex != null && c.CellFormula.Reference != null) + .Select(c=>c)) + { + sharedFormulas.Add(cell.CellFormula.SharedIndex.Value, cell.CellFormula); + } foreach (var cell in worksheetPart.Worksheet.Descendants()) { var dCell = (Cell)cell; - Int32 styleIndex = dCell.StyleIndex != null ? Int32.Parse(dCell.StyleIndex.InnerText) : -1; + Int32 styleIndex = dCell.StyleIndex != null ? Int32.Parse(dCell.StyleIndex.InnerText) : 0; var xlCell = ws.CellFast(dCell.CellReference); if (styleIndex > 0) { - styleIndex = Int32.Parse(dCell.StyleIndex.InnerText); + //styleIndex = Int32.Parse(dCell.StyleIndex.InnerText); ApplyStyle(xlCell, styleIndex, s, fills, borders, fonts, numberingFormats); } else @@ -219,17 +188,27 @@ if(dCell.CellFormula != null) { - xlCell.FormulaA1 = dCell.CellFormula.Text; + if (dCell.CellFormula.SharedIndex != null) + xlCell.FormulaA1 = sharedFormulas[dCell.CellFormula.SharedIndex.Value].Text; + else + xlCell.FormulaA1 = dCell.CellFormula.Text; } else if (dCell.DataType != null) { if (dCell.DataType == CellValues.SharedString) { xlCell.DataType = XLCellValues.Text; - if (!String.IsNullOrWhiteSpace(dCell.CellValue.Text)) - xlCell.Value = sharedStrings[Int32.Parse(dCell.CellValue.Text)].InnerText; + if (dCell.CellValue != null) + { + if (!String.IsNullOrWhiteSpace(dCell.CellValue.Text)) + xlCell.Value = sharedStrings[Int32.Parse(dCell.CellValue.Text)].InnerText; + else + xlCell.Value = dCell.CellValue.Text; + } else - xlCell.Value = dCell.CellValue.Text; + { + xlCell.Value = String.Empty; + } } else if (dCell.DataType == CellValues.Date) { @@ -447,7 +426,7 @@ Properties.Author = p.Creator; Properties.Category = p.Category; Properties.Comments = p.Description; - if (p.Created.HasValue) + if (p.Created != null) Properties.Created = p.Created.Value; Properties.Keywords = p.Keywords; Properties.LastModifiedBy = p.LastModifiedBy; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index d3c716c..ece4108 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -12,6 +12,8 @@ using System.Linq; using System.Text; using System.IO; +using System.Globalization; +using System.IO.Packaging; @@ -31,6 +33,7 @@ private List> printErrorValues = new List>(); private List> calculateModeValues = new List>(); private List> referenceModeValues = new List>(); + private List> alignmentReadingOrderValues = new List>(); private void PopulateEnums() { PopulateFillPatternValues(); @@ -44,20 +47,36 @@ PopulateShowCommentsValues(); PopulatePrintErrorValues(); PopulateCalculateModeValues(); - PoulateReferenceModeValues(); + PopulateReferenceModeValues(); + PopulateAlignmentReadingOrderValues(); } private enum RelType { General, Workbook, Worksheet } - private class RelId + private class RelIdGenerator { - private static Dictionary relIds = new Dictionary(); - public static Int32 GetNext(RelType relType) + private Dictionary> relIds = new Dictionary>(); + public String GetNext(RelType relType) { if (!relIds.ContainsKey(relType)) - relIds.Add(relType, -1); - var relId = relIds[relType]; - relIds[relType] = ++relId; - return relId; + relIds.Add(relType, new List()); + + Int32 id = 1; + while (true) + { + String relId = String.Format("rId{0}", id); + if (!relIds[relType].Contains(relId)) + { + relIds[relType].Add(relId); + return relId; + } + id++; + } + } + public void AddValues(List values, RelType relType) + { + if (!relIds.ContainsKey(relType)) + relIds.Add(relType, new List()); + relIds[relType].AddRange(values); } } @@ -209,187 +228,323 @@ calculateModeValues.Add(new KeyValuePair(XLCalculateMode.Manual, CalculateModeValues.Manual)); } - private void PoulateReferenceModeValues() + private void PopulateReferenceModeValues() { referenceModeValues.Add(new KeyValuePair(XLReferenceStyle.R1C1, ReferenceModeValues.R1C1)); referenceModeValues.Add(new KeyValuePair(XLReferenceStyle.A1, ReferenceModeValues.A1)); } + private void PopulateAlignmentReadingOrderValues() + { + alignmentReadingOrderValues.Add(new KeyValuePair(XLAlignmentReadingOrderValues.ContextDependent, 0)); + alignmentReadingOrderValues.Add(new KeyValuePair(XLAlignmentReadingOrderValues.LeftToRight, 1)); + alignmentReadingOrderValues.Add(new KeyValuePair(XLAlignmentReadingOrderValues.RightToLeft, 2)); + } // Creates a SpreadsheetDocument. private void CreatePackage(String filePath) { - using (SpreadsheetDocument package = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook)) + SpreadsheetDocument package; + if (File.Exists(filePath)) + package = SpreadsheetDocument.Open(filePath, true); + else + package = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook); + + using (package) { - RelId.GetNext(RelType.Worksheet); CreateParts(package); } } // Adds child parts and generates content of the specified part. + private RelIdGenerator relId; private void CreateParts(SpreadsheetDocument document) { - Int32 startId = Worksheets.Count(); - ExtendedFilePropertiesPart extendedFilePropertiesPart1 = document.AddNewPart("rId" + (startId)); - GenerateExtendedFilePropertiesPartContent(extendedFilePropertiesPart1); + relId = new RelIdGenerator(); - WorkbookPart workbookPart = document.AddWorkbookPart(); + WorkbookPart workbookPart; + if (document.WorkbookPart == null) + workbookPart = document.AddWorkbookPart(); + else + workbookPart = document.WorkbookPart; + + relId.AddValues(workbookPart.Parts.Select(p=>p.RelationshipId).ToList(), RelType.Workbook); + + var modifiedSheetNames = Worksheets.Select(w => w.Name.ToLower()).ToList(); + + List existingSheetNames; + if (workbookPart.Workbook != null && workbookPart.Workbook.Sheets != null) + existingSheetNames = workbookPart.Workbook.Sheets.Elements().Select(s => s.Name.Value.ToLower()).ToList(); + else + existingSheetNames = new List(); + + var allSheetNames = existingSheetNames.Union(modifiedSheetNames); + + ExtendedFilePropertiesPart extendedFilePropertiesPart; + if (document.ExtendedFilePropertiesPart == null) + extendedFilePropertiesPart = document.AddNewPart(relId.GetNext(RelType.Workbook)); + else + extendedFilePropertiesPart = document.ExtendedFilePropertiesPart; + + GenerateExtendedFilePropertiesPartContent(extendedFilePropertiesPart, workbookPart); + GenerateWorkbookPartContent(workbookPart); - - SharedStringTablePart sharedStringTablePart = workbookPart.AddNewPart("rId" + (startId + 3)); + + SharedStringTablePart sharedStringTablePart; + if (workbookPart.SharedStringTablePart == null) + sharedStringTablePart = workbookPart.AddNewPart(relId.GetNext(RelType.Workbook)); + else + sharedStringTablePart = workbookPart.SharedStringTablePart; + GenerateSharedStringTablePartContent(sharedStringTablePart); - WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart("rId" + (startId + 2)); + WorkbookStylesPart workbookStylesPart; + if (workbookPart.WorkbookStylesPart == null) + workbookStylesPart = workbookPart.AddNewPart(relId.GetNext(RelType.Workbook)); + else + workbookStylesPart = workbookPart.WorkbookStylesPart; + GenerateWorkbookStylesPartContent(workbookStylesPart); - UInt32 sheetId = 0; - foreach (var worksheet in Worksheets) + foreach (var worksheet in Worksheets.Cast().OrderBy(w=>w.SheetId)) { - sheetId++; - WorksheetPart worksheetPart = workbookPart.AddNewPart("rId" + sheetId.ToString()); - GenerateWorksheetPartContent(worksheetPart, (XLWorksheet)worksheet); + 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()); + else + worksheetPart = workbookPart.AddNewPart("rId" + worksheet.SheetId.ToString()); + + GenerateWorksheetPartContent(worksheetPart, worksheet); } - GenerateCalculationChainPartContent(workbookPart, "rId" + (startId + 4)); + GenerateCalculationChainPartContent(workbookPart); - ThemePart themePart1 = workbookPart.AddNewPart("rId" + (startId + 1)); - GenerateThemePartContent(themePart1); + if (workbookPart.ThemePart == null) + { + ThemePart themePart = workbookPart.AddNewPart(relId.GetNext(RelType.Workbook)); + GenerateThemePartContent(themePart); + } SetPackageProperties(document); } - private void GenerateExtendedFilePropertiesPartContent(ExtendedFilePropertiesPart extendedFilePropertiesPart) + private void GenerateExtendedFilePropertiesPartContent(ExtendedFilePropertiesPart extendedFilePropertiesPart, WorkbookPart workbookPart) { - Ap.Properties properties1 = new Ap.Properties(); - properties1.AddNamespaceDeclaration("vt", "http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"); - Ap.Application application1 = new Ap.Application(); - application1.Text = "Microsoft Excel"; - Ap.DocumentSecurity documentSecurity1 = new Ap.DocumentSecurity(); - documentSecurity1.Text = "0"; - Ap.ScaleCrop scaleCrop1 = new Ap.ScaleCrop(); - scaleCrop1.Text = "false"; + //if (extendedFilePropertiesPart.Properties.NamespaceDeclarations.Contains(new KeyValuePair( + Ap.Properties properties; + if (extendedFilePropertiesPart.Properties == null) + extendedFilePropertiesPart.Properties = new Ap.Properties(); - Ap.HeadingPairs headingPairs1 = new Ap.HeadingPairs(); + properties = extendedFilePropertiesPart.Properties; + if (!properties.NamespaceDeclarations.Contains(new KeyValuePair("vt", "http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"))) + properties.AddNamespaceDeclaration("vt", "http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"); - Vt.VTVector vTVector1 = new Vt.VTVector() { BaseType = Vt.VectorBaseValues.Variant, Size = (UInt32Value)4U }; + if (properties.Application == null) + properties.Append(new Ap.Application() { Text = "Microsoft Excel" }); - Vt.Variant variant1 = new Vt.Variant(); - Vt.VTLPSTR vTLPSTR1 = new Vt.VTLPSTR(); - vTLPSTR1.Text = "Worksheets"; + if (properties.DocumentSecurity == null) + properties.Append(new Ap.DocumentSecurity() { Text = "0" }); - variant1.Append(vTLPSTR1); + if (properties.ScaleCrop == null) + properties.Append(new Ap.ScaleCrop() { Text = "false" }); - Vt.Variant variant2 = new Vt.Variant(); - Vt.VTInt32 vTInt321 = new Vt.VTInt32(); - vTInt321.Text = Worksheets.Count().ToString(); + if (properties.HeadingPairs == null) + properties.HeadingPairs = new Ap.HeadingPairs(); - variant2.Append(vTInt321); + if (properties.TitlesOfParts == null) + properties.TitlesOfParts = new Ap.TitlesOfParts(); - Vt.Variant variant3 = new Vt.Variant(); - Vt.VTLPSTR vTLPSTR2 = new Vt.VTLPSTR(); - vTLPSTR2.Text = "Named Ranges"; + if (properties.HeadingPairs.VTVector == null) + properties.HeadingPairs.VTVector = new Vt.VTVector() { BaseType = Vt.VectorBaseValues.Variant}; - variant3.Append(vTLPSTR2); + if (properties.TitlesOfParts.VTVector == null) + properties.TitlesOfParts.VTVector = new Vt.VTVector() { BaseType = Vt.VectorBaseValues.Lpstr }; - var namedCount = NamedRanges.Count() + Worksheets.Aggregate(0, (counter, ws) => counter += ws.NamedRanges.Count()); - Vt.Variant variant4 = new Vt.Variant(); - Vt.VTInt32 vTInt322 = new Vt.VTInt32(); - vTInt322.Text = ( - Worksheets.Count() * 2 // for the worksheets print area and titles - + namedCount - ).ToString(); + Vt.VTVector vTVector_One; + vTVector_One = properties.HeadingPairs.VTVector; - variant4.Append(vTInt322); + Vt.VTVector vTVector_Two; + vTVector_Two = properties.TitlesOfParts.VTVector; - vTVector1.Append(variant1); - vTVector1.Append(variant2); - vTVector1.Append(variant3); - vTVector1.Append(variant4); + + var modifiedWorksheets = Worksheets.Select(w => w.Name).ToList(); + var modifiedNamedRanges = GetModifiedNamedRanges().Union(modifiedWorksheets); - headingPairs1.Append(vTVector1); + var existingNamedRanges = GetExistingNamedRanges(vTVector_Two); + var existingWorksheets = GetExistingWorksheets(workbookPart); - Ap.TitlesOfParts titlesOfParts1 = new Ap.TitlesOfParts(); + var allWorksheets = existingWorksheets.Union(modifiedWorksheets); + var allNamedRanges = existingNamedRanges.Union(modifiedNamedRanges); - UInt32 sheetCount = (UInt32)Worksheets.Count(); - Vt.VTVector vTVector2 = new Vt.VTVector() { BaseType = Vt.VectorBaseValues.Lpstr, Size = (UInt32Value)(sheetCount * 3 + namedCount) }; - foreach (var worksheet in Worksheets) + InsertOnVTVector(vTVector_One, "Worksheets", 0, allWorksheets.Count().ToString()); + InsertOnVTVector(vTVector_One, "Named Ranges", 2, (allNamedRanges.Count() - allWorksheets.Count()).ToString()); + + 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) { - Vt.VTLPSTR vTLPSTR3 = new Vt.VTLPSTR(); - vTLPSTR3.Text = worksheet.Name; - vTVector2.Append(vTLPSTR3); + Vt.VTLPSTR vTLPSTR3 = new Vt.VTLPSTR() { Text = w }; + vTVector_Two.Append(vTLPSTR3); + } - Vt.VTLPSTR vTLPSTR4 = new Vt.VTLPSTR(); - vTLPSTR4.Text = worksheet.Name + "!Print_Area"; - vTVector2.Append(vTLPSTR4); + foreach (var nr in namedRangesToInsert) + { + Vt.VTLPSTR vTLPSTR7 = new Vt.VTLPSTR() { Text = nr }; + vTVector_Two.Append(vTLPSTR7); + } - Vt.VTLPSTR vTLPSTR5 = new Vt.VTLPSTR(); - vTLPSTR5.Text = worksheet.Name + "!Print_Titles"; - vTVector2.Append(vTLPSTR5); - - foreach (var nr in worksheet.NamedRanges) + if (Properties.Manager != null) + { + if (!String.IsNullOrWhiteSpace(Properties.Manager)) { - Vt.VTLPSTR vTLPSTR6 = new Vt.VTLPSTR(); - vTLPSTR6.Text = worksheet.Name + "!" + nr.Name; - vTVector2.Append(vTLPSTR6); + if (properties.Manager == null) + properties.Manager = new Ap.Manager(); + + properties.Manager.Text = Properties.Manager; + } + else + { + properties.Manager = null; } } - foreach (var nr in NamedRanges) + if (Properties.Company != null) { - Vt.VTLPSTR vTLPSTR7 = new Vt.VTLPSTR(); - vTLPSTR7.Text = nr.Name; - vTVector2.Append(vTLPSTR7); + if (!String.IsNullOrWhiteSpace(Properties.Company)) + { + if (properties.Company == null) + properties.Company = new Ap.Company(); + + properties.Company.Text = Properties.Company; + } + else + { + properties = null; + } + } + } + + private void InsertOnVTVector(Vt.VTVector vTVector, String property, Int32 index, String text) + { + var m = from e1 in vTVector.Elements() + where e1.Elements().Any(e2 => e2.Text == property) + select e1; + if (m.Count() == 0) + { + if (vTVector.Size == null) + vTVector.Size = new UInt32Value(0U); + + vTVector.Size += 2U; + Vt.Variant variant1 = new Vt.Variant(); + Vt.VTLPSTR vTLPSTR1 = new Vt.VTLPSTR() { Text = property }; + variant1.Append(vTLPSTR1); + vTVector.InsertAt(variant1, index); + + Vt.Variant variant2 = new Vt.Variant(); + Vt.VTInt32 vTInt321 = new Vt.VTInt32(); + variant2.Append(vTInt321); + vTVector.InsertAt(variant2, index + 1); } - titlesOfParts1.Append(vTVector2); - Ap.Manager manager1 = new Ap.Manager(); - manager1.Text = Properties.Manager; - Ap.Company company1 = new Ap.Company(); - company1.Text = Properties.Company; + Int32 targetIndex = 0; + foreach (var e in vTVector.Elements()) + { + if (e.Elements().Any(e2 => e2.Text == property)) + { + vTVector.ElementAt(targetIndex + 1).GetFirstChild().Text = text; + break; + } + targetIndex++; + } + } - Ap.LinksUpToDate linksUpToDate1 = new Ap.LinksUpToDate(); - linksUpToDate1.Text = "false"; - Ap.SharedDocument sharedDocument1 = new Ap.SharedDocument(); - sharedDocument1.Text = "false"; - Ap.HyperlinksChanged hyperlinksChanged1 = new Ap.HyperlinksChanged(); - hyperlinksChanged1.Text = "false"; - Ap.ApplicationVersion applicationVersion1 = new Ap.ApplicationVersion(); - applicationVersion1.Text = "12.0000"; + private List GetExistingWorksheets(WorkbookPart workbookPart) + { + if (workbookPart != null && workbookPart.Workbook != null && workbookPart.Workbook.Sheets != null) + return workbookPart.Workbook.Sheets.Select(s=>((Sheet)s).Name.Value).ToList(); + else + return new List(); + } - properties1.Append(application1); - properties1.Append(documentSecurity1); - properties1.Append(scaleCrop1); - properties1.Append(headingPairs1); - properties1.Append(titlesOfParts1); - properties1.Append(manager1); - properties1.Append(company1); - properties1.Append(linksUpToDate1); - properties1.Append(sharedDocument1); - properties1.Append(hyperlinksChanged1); - properties1.Append(applicationVersion1); + private List GetExistingNamedRanges(Vt.VTVector vTVector_Two) + { + if (vTVector_Two.Count() > 0) + return vTVector_Two.Elements().Select(e => e.Text).ToList(); + else + return new List(); + } - extendedFilePropertiesPart.Properties = properties1; + private List GetModifiedNamedRanges() + { + var namedRanges = new List(); + foreach (var w in Worksheets) + { + foreach (var n in w.NamedRanges) + { + namedRanges.Add(w.Name + "!" + n.Name); + } + namedRanges.Add(w.Name + "!Print_Area"); + namedRanges.Add(w.Name + "!Print_Titles"); + } + namedRanges.AddRange(NamedRanges.Select(n => n.Name)); + return namedRanges; } private void GenerateWorkbookPartContent(WorkbookPart workbookPart) { - Workbook workbook1 = new Workbook(); - workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); - FileVersion fileVersion1 = new FileVersion() { ApplicationName = "xl", LastEdited = "4", LowestEdited = "4", BuildVersion = "4506" }; - WorkbookProperties workbookProperties1 = new WorkbookProperties() { CodeName = "ThisWorkbook", DefaultThemeVersion = (UInt32Value)124226U }; + if (workbookPart.Workbook == null) + workbookPart.Workbook = new Workbook(); - BookViews bookViews1 = new BookViews(); - WorkbookView workbookView1 = new WorkbookView() { XWindow = 0, YWindow = 30, WindowWidth = (UInt32Value)14160U, WindowHeight = (UInt32Value)11580U }; + var workbook = workbookPart.Workbook; + if (!workbook.NamespaceDeclarations.Contains(new KeyValuePair("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"))) + workbook.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); - bookViews1.Append(workbookView1); + #region WorkbookProperties + if (workbook.WorkbookProperties == null) + workbook.WorkbookProperties = new WorkbookProperties(); - UInt32 sheetId = 0; - Sheets sheets = new Sheets(); + if (workbook.WorkbookProperties.CodeName == null) + workbook.WorkbookProperties.CodeName = "ThisWorkbook"; + + if (workbook.WorkbookProperties.DefaultThemeVersion == null) + workbook.WorkbookProperties.DefaultThemeVersion = (UInt32Value)124226U; + #endregion + + if (workbook.Sheets == null) + workbook.Sheets = new Sheets(); + + 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; + } + + foreach (var xlSheet in Worksheets.Cast().Where(w=>w.SheetId == 0)) + { + var rId = relId.GetNext(RelType.Workbook); + xlSheet.SheetId = Int32.Parse(rId.Substring(3)); + workbook.Sheets.Append(new Sheet() { Name = xlSheet.Name, Id = rId, SheetId = (UInt32)xlSheet.SheetId }); + } + DefinedNames definedNames = new DefinedNames(); foreach (var worksheet in Worksheets.Cast()) { - sheetId++; - Sheet sheet = new Sheet() { Name = worksheet.Name, SheetId = (UInt32Value)sheetId, Id = "rId" + sheetId.ToString() }; - sheets.Append(sheet); + UInt32 sheetId = 0; + foreach (var s in workbook.Sheets.Elements()) + { + if (s.SheetId == (UInt32)worksheet.SheetId) + break; + sheetId++; + } if (worksheet.PageSetup.PrintAreas.Count() == 0) { @@ -400,7 +555,7 @@ } if (worksheet.PageSetup.PrintAreas.Count() > 0) { - DefinedName definedName = new DefinedName() { Name = "_xlnm.Print_Area", LocalSheetId = (UInt32Value)sheetId - 1 }; + DefinedName definedName = new DefinedName() { Name = "_xlnm.Print_Area", LocalSheetId = sheetId}; var definedNameText = String.Empty; foreach (var printArea in worksheet.PageSetup.PrintAreas) { @@ -415,8 +570,8 @@ foreach (var nr in worksheet.NamedRanges) { DefinedName definedName = new DefinedName() { - Name = nr.Name, - LocalSheetId = (UInt32Value)sheetId - 1, + Name = nr.Name, + LocalSheetId = sheetId, Text = nr.ToString() }; if (!String.IsNullOrWhiteSpace(nr.Comment)) definedName.Comment = nr.Comment; @@ -452,7 +607,7 @@ if (titles.Length > 0) { - DefinedName definedName = new DefinedName() { Name = "_xlnm.Print_Titles", LocalSheetId = (UInt32Value)sheetId - 1 }; + DefinedName definedName = new DefinedName() { Name = "_xlnm.Print_Titles", LocalSheetId = sheetId}; definedName.Text = titles; definedNames.Append(definedName); } @@ -469,45 +624,89 @@ definedNames.Append(definedName); } - CalculationProperties calculationProperties = new CalculationProperties() { CalculationId = (UInt32Value)125725U }; - if (CalculateMode != XLCalculateMode.Default) - calculationProperties.CalculationMode = calculateModeValues.Single(p => p.Key == CalculateMode).Value; + if (workbook.DefinedNames == null) + workbook.DefinedNames = new DefinedNames(); - if (ReferenceStyle != XLReferenceStyle.Default) - calculationProperties.ReferenceMode = referenceModeValues.Single(p=>p.Key==ReferenceStyle).Value; + foreach (DefinedName dn in definedNames) + { + if (workbook.DefinedNames.Elements().Any(d => d.Name.Value.ToLower() == dn.Name.Value.ToLower() + && ((d.LocalSheetId != null && dn.LocalSheetId !=null && d.LocalSheetId.InnerText == dn.LocalSheetId.InnerText) + || d.LocalSheetId == null || dn.LocalSheetId == null) + )) + { + DefinedName existingDefinedName = (DefinedName)workbook.DefinedNames.Where(d => ((DefinedName)d).Name.Value.ToLower() == dn.Name.Value.ToLower()).First(); + existingDefinedName.Text = dn.Text; + existingDefinedName.LocalSheetId = dn.LocalSheetId; + existingDefinedName.Comment = dn.Comment; + } + else + { + workbook.DefinedNames.Append(dn.CloneNode(true)); + } + } + + if (workbook.CalculationProperties == null) + workbook.CalculationProperties = new CalculationProperties() { CalculationId = (UInt32Value)125725U }; + + if (CalculateMode == XLCalculateMode.Default) + workbook.CalculationProperties.CalculationMode = null; + else + workbook.CalculationProperties.CalculationMode = calculateModeValues.Single(p => p.Key == CalculateMode).Value; + + + if (ReferenceStyle == XLReferenceStyle.Default) + workbook.CalculationProperties.ReferenceMode = null; + else + workbook.CalculationProperties.ReferenceMode = referenceModeValues.Single(p => p.Key == ReferenceStyle).Value; - - workbook1.Append(fileVersion1); - workbook1.Append(workbookProperties1); - workbook1.Append(bookViews1); - workbook1.Append(sheets); - if (definedNames.Count() > 0) workbook1.Append(definedNames); - workbook1.Append(calculationProperties); - - workbookPart.Workbook = workbook1; } private void GenerateSharedStringTablePartContent(SharedStringTablePart sharedStringTablePart) { - List combined = new List(); - Worksheets.Cast().ForEach(w => combined.AddRange(w.Internals.CellsCollection.Values.Where(c => c.DataType == XLCellValues.Text && c.InnerText != null).Select(c => c.GetString()).Distinct())); - var distinctStrings = combined.Distinct(); - UInt32 stringCount = (UInt32)distinctStrings.Count(); - SharedStringTable sharedStringTable = new SharedStringTable() { Count = (UInt32Value)stringCount, UniqueCount = (UInt32Value)stringCount }; + List modifiedStrings = new List(); + Worksheets.Cast().ForEach(w => modifiedStrings.AddRange(w.Internals.CellsCollection.Values.Where(c => c.DataType == XLCellValues.Text && !String.IsNullOrWhiteSpace(c.InnerText)).Select(c => c.GetString()).Distinct())); - UInt32 stringId = 0; - foreach (var s in distinctStrings) + List existingStrings; + if (sharedStringTablePart.SharedStringTable != null) + existingStrings = sharedStringTablePart.SharedStringTable.Elements().Select(e => e.Text.Text).ToList(); + else { - sharedStrings.Add(s, stringId++); - - SharedStringItem sharedStringItem = new SharedStringItem(); - Text text = new Text(); - text.Text = s; - sharedStringItem.Append(text); - sharedStringTable.Append(sharedStringItem); + existingStrings = new List(); + sharedStringTablePart.SharedStringTable = new SharedStringTable() { Count = 0, UniqueCount = 0 }; } - sharedStringTablePart.SharedStringTable = sharedStringTable; + var distinctStrings = modifiedStrings.Distinct().Union(existingStrings); + + UInt32 stringCount = (UInt32)distinctStrings.Count(); + + foreach (var s in distinctStrings) + { + Int32 stringId = 0; + var ds = sharedStringTablePart.SharedStringTable.Elements().Select(t=>t.Text.Text).Distinct(); + Boolean foundOne = false; + foreach (var ssi in ds) + { + if (ssi == s) + { + foundOne = true; + break; + } + stringId++; + } + + if (!foundOne) + { + SharedStringItem sharedStringItem = new SharedStringItem(); + Text text = new Text(); + text.Text = s; + sharedStringItem.Append(text); + sharedStringTablePart.SharedStringTable.Append(sharedStringItem); + sharedStringTablePart.SharedStringTable.Count += 1; + sharedStringTablePart.SharedStringTable.UniqueCount += 1; + } + + sharedStrings.Add(s, (UInt32)stringId); + } } private void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart) @@ -579,214 +778,559 @@ sharedNumberFormats.Add(xlStyle.NumberFormat.ToString(), new NumberFormatInfo() { NumberFormatId = numberFormatCount + 164, NumberFormat = xlStyle.NumberFormat }); numberFormatCount++; } + } + + if (workbookStylesPart.Stylesheet == null) + workbookStylesPart.Stylesheet = new Stylesheet(); + var allSharedNumberFormats = ResolveNumberFormats(workbookStylesPart, sharedNumberFormats); + var allSharedFonts = ResolveFonts(workbookStylesPart, sharedFonts); + var allSharedFills = ResolveFills(workbookStylesPart, sharedFills); + var allSharedBorders = ResolveBorders(workbookStylesPart, sharedBorders); + + foreach (var xlStyle in xlStyles) + { if (!sharedStyles.ContainsKey(xlStyle.ToString())) { Int32 numberFormatId; if (xlStyle.NumberFormat.NumberFormatId >= 0) numberFormatId = xlStyle.NumberFormat.NumberFormatId; else - numberFormatId = sharedNumberFormats[xlStyle.NumberFormat.ToString()].NumberFormatId; + numberFormatId = allSharedNumberFormats[xlStyle.NumberFormat.ToString()].NumberFormatId; sharedStyles.Add(xlStyle.ToString(), new StyleInfo() { StyleId = styleCount++, Style = xlStyle, - FontId = sharedFonts[xlStyle.Font.ToString()].FontId, - FillId = sharedFills[xlStyle.Fill.ToString()].FillId, - BorderId = sharedBorders[xlStyle.Border.ToString()].BorderId, + FontId = allSharedFonts[xlStyle.Font.ToString()].FontId, + FillId = allSharedFills[xlStyle.Fill.ToString()].FillId, + BorderId = allSharedBorders[xlStyle.Border.ToString()].BorderId, NumberFormatId = numberFormatId }); } } - Stylesheet stylesheet1 = new Stylesheet(); - - NumberingFormats numberingFormats = new NumberingFormats() { Count = (UInt32Value)(UInt32)numberFormatCount }; - foreach (var numberFormatInfo in sharedNumberFormats.Values) - { - NumberingFormat numberingFormat = new NumberingFormat() { NumberFormatId = (UInt32Value)(UInt32)numberFormatInfo.NumberFormatId, FormatCode = numberFormatInfo.NumberFormat.Format }; - numberingFormats.Append(numberingFormat); - } - - Fonts fonts = new Fonts() { Count = (UInt32Value)fontCount }; - - foreach (var fontInfo in sharedFonts.Values) - { - Bold bold = fontInfo.Font.Bold ? new Bold() : null; - Italic italic = fontInfo.Font.Italic ? new Italic() : null; - Underline underline = fontInfo.Font.Underline != XLFontUnderlineValues.None ? new Underline() { Val = underlineValuesList.Single(u=>u.Key == fontInfo.Font.Underline).Value } : null; - Strike strike = fontInfo.Font.Strikethrough ? new Strike() : null; - VerticalTextAlignment verticalAlignment = new VerticalTextAlignment() { Val = fontVerticalTextAlignmentValues.Single(f=>f.Key == fontInfo.Font.VerticalAlignment).Value }; - Shadow shadow = fontInfo.Font.Shadow ? new Shadow() : null; - Font font = new Font(); - FontSize fontSize = new FontSize() { Val = fontInfo.Font.FontSize }; - Color color = new Color() { Rgb = fontInfo.Font.FontColor.ToHex() }; - FontName fontName = new FontName() { Val = fontInfo.Font.FontName }; - FontFamilyNumbering fontFamilyNumbering = new FontFamilyNumbering() { Val = (Int32)fontInfo.Font.FontFamilyNumbering }; - //FontScheme fontScheme = new FontScheme() { Val = FontSchemeValues.Minor }; - - if (bold != null) font.Append(bold); - if (italic != null) font.Append(italic); - if (underline != null) font.Append(underline); - if (strike != null) font.Append(strike); - font.Append(verticalAlignment); - if (shadow != null) font.Append(shadow); - font.Append(fontSize); - font.Append(color); - font.Append(fontName); - font.Append(fontFamilyNumbering); - //font.Append(fontScheme); - - fonts.Append(font); - } - - Fills fills = new Fills() { Count = (UInt32Value)fillCount }; - - Fill fill1 = new Fill(); - PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None }; - fill1.Append(patternFill1); - fills.Append(fill1); - - Fill fill2 = new Fill(); - PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 }; - fill2.Append(patternFill2); - fills.Append(fill2); - - foreach (var fillInfo in sharedFills.Values) - { - Fill fill = new Fill(); - - PatternFill patternFill = new PatternFill() { PatternType = fillPatternValues.Single(p=>p.Key == fillInfo.Fill.PatternType).Value }; - ForegroundColor foregroundColor = new ForegroundColor() { Rgb = fillInfo.Fill.PatternColor.ToHex() }; - BackgroundColor backgroundColor = new BackgroundColor() { Rgb = fillInfo.Fill.PatternBackgroundColor.ToHex() }; - - patternFill.Append(foregroundColor); - patternFill.Append(backgroundColor); - - fill.Append(patternFill); - fills.Append(fill); - } - - Borders borders = new Borders() { Count = (UInt32Value)borderCount }; - - foreach (var borderInfo in sharedBorders.Values) - { - Border border = new Border() { DiagonalUp = borderInfo.Border.DiagonalUp, DiagonalDown = borderInfo.Border.DiagonalDown }; - - LeftBorder leftBorder = new LeftBorder() { Style = borderStyleValues.Single(b=>b.Key == borderInfo.Border.LeftBorder).Value }; - Color leftBorderColor = new Color() { Rgb = borderInfo.Border.LeftBorderColor.ToHex() }; - leftBorder.Append(leftBorderColor); - border.Append(leftBorder); - - RightBorder rightBorder = new RightBorder() { Style = borderStyleValues.Single(b => b.Key == borderInfo.Border.RightBorder).Value }; - Color rightBorderColor = new Color() { Rgb = borderInfo.Border.RightBorderColor.ToHex() }; - rightBorder.Append(rightBorderColor); - border.Append(rightBorder); - - TopBorder topBorder = new TopBorder() { Style = borderStyleValues.Single(b => b.Key == borderInfo.Border.TopBorder).Value }; - Color topBorderColor = new Color() { Rgb = borderInfo.Border.TopBorderColor.ToHex() }; - topBorder.Append(topBorderColor); - border.Append(topBorder); - - BottomBorder bottomBorder = new BottomBorder() { Style = borderStyleValues.Single(b => b.Key == borderInfo.Border.BottomBorder).Value }; - Color bottomBorderColor = new Color() { Rgb = borderInfo.Border.BottomBorderColor.ToHex() }; - bottomBorder.Append(bottomBorderColor); - border.Append(bottomBorder); - - DiagonalBorder diagonalBorder = new DiagonalBorder() { Style = borderStyleValues.Single(b => b.Key == borderInfo.Border.DiagonalBorder).Value }; - Color diagonalBorderColor = new Color() { Rgb = borderInfo.Border.DiagonalBorderColor.ToHex() }; - diagonalBorder.Append(diagonalBorderColor); - border.Append(diagonalBorder); - - borders.Append(border); - } - - - - // Cell style formats = Formats to be used by the cells and named styles - CellStyleFormats cellStyleFormats = new CellStyleFormats() { Count = (UInt32Value)styleCount }; - // Cell formats = Any kind of formatting applied to a cell - CellFormats cellFormats = new CellFormats() { Count = (UInt32Value)styleCount }; - foreach (var styleInfo in sharedStyles.Values) - { - var formatId = styleInfo.StyleId; - var numberFormatId = styleInfo.NumberFormatId; - var fontId = styleInfo.FontId; - var fillId = styleInfo.FillId; - var borderId = styleInfo.BorderId; - Boolean applyFill = fillPatternValues.Single(p => p.Key == styleInfo.Style.Fill.PatternType).Value == PatternValues.None; - IXLBorder opBorder = styleInfo.Style.Border; - Boolean applyBorder = ( - borderStyleValues.Single(b => b.Key == opBorder.BottomBorder).Value != BorderStyleValues.None - || borderStyleValues.Single(b => b.Key == opBorder.DiagonalBorder).Value != BorderStyleValues.None - || borderStyleValues.Single(b => b.Key == opBorder.RightBorder).Value != BorderStyleValues.None - || borderStyleValues.Single(b => b.Key == opBorder.LeftBorder).Value != BorderStyleValues.None - || borderStyleValues.Single(b => b.Key == opBorder.TopBorder).Value != BorderStyleValues.None); - - CellFormat cellStyleFormat = new CellFormat() { NumberFormatId = (UInt32Value)(UInt32)numberFormatId, FontId = (UInt32Value)fontId, FillId = (UInt32Value)fillId, BorderId = (UInt32Value)borderId, ApplyNumberFormat = false, ApplyFill = applyFill, ApplyBorder = applyBorder, ApplyAlignment = false, ApplyProtection = false }; - cellStyleFormats.Append(cellStyleFormat); - - CellFormat cellFormat = new CellFormat() { NumberFormatId = (UInt32Value)(UInt32)numberFormatId, FontId = (UInt32Value)fontId, FillId = (UInt32Value)fillId, BorderId = (UInt32Value)borderId, FormatId = (UInt32Value)formatId, ApplyNumberFormat = false, ApplyFill = applyFill, ApplyBorder = applyBorder, ApplyAlignment = false, ApplyProtection = false }; - Alignment alignment = new Alignment() - { - Horizontal = alignmentHorizontalValues.Single(a=>a.Key== styleInfo.Style.Alignment.Horizontal).Value, - Vertical = alignmentVerticalValues.Single(a=>a.Key == styleInfo.Style.Alignment.Vertical).Value, - Indent = (UInt32)styleInfo.Style.Alignment.Indent, - ReadingOrder = (UInt32)styleInfo.Style.Alignment.ReadingOrder, - WrapText = styleInfo.Style.Alignment.WrapText, - TextRotation = (UInt32)styleInfo.Style.Alignment.TextRotation, - ShrinkToFit = styleInfo.Style.Alignment.ShrinkToFit, - RelativeIndent = styleInfo.Style.Alignment.RelativeIndent, - JustifyLastLine = styleInfo.Style.Alignment.JustifyLastLine - }; - cellFormat.Append(alignment); - - cellFormats.Append(cellFormat); - } - - + var allCellStyleFormats = ResolveCellStyleFormats(workbookStylesPart); + ResolveAlignments(workbookStylesPart); // Cell styles = Named styles - CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U }; - var defaultFormatId = sharedStyles.Values.Where(s => s.Style.ToString() == DefaultStyle.ToString()).Single().StyleId; - CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)defaultFormatId, BuiltinId = (UInt32Value)0U }; - cellStyles1.Append(cellStyle1); + if (workbookStylesPart.Stylesheet.CellStyles == null) + workbookStylesPart.Stylesheet.CellStyles = new CellStyles(); - DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = (UInt32Value)0U }; - TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium9", DefaultPivotStyle = "PivotStyleLight16" }; + if (!workbookStylesPart.Stylesheet.CellStyles.Elements().Where(c => c.Name == "Normal").Any()) + { + var defaultFormatId = sharedStyles.Values.Where(s => s.Style.ToString() == DefaultStyle.ToString()).Single().StyleId; - stylesheet1.Append(numberingFormats); - stylesheet1.Append(fonts); - stylesheet1.Append(fills); - stylesheet1.Append(borders); - stylesheet1.Append(cellStyleFormats); - stylesheet1.Append(cellFormats); - stylesheet1.Append(cellStyles1); - stylesheet1.Append(differentialFormats1); - stylesheet1.Append(tableStyles1); + CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)defaultFormatId, BuiltinId = (UInt32Value)0U }; + workbookStylesPart.Stylesheet.CellStyles.Append(cellStyle1); + } + workbookStylesPart.Stylesheet.CellStyles.Count = (UInt32)workbookStylesPart.Stylesheet.CellStyles.Count(); - workbookStylesPart.Stylesheet = stylesheet1; + var newSharedStyles = new Dictionary(); + foreach (var ss in sharedStyles) + { + Int32 styleId = -1; + foreach (CellFormat f in workbookStylesPart.Stylesheet.CellFormats) + { + styleId++; + if (CellFormatsAreEqual(f, ss.Value)) + break; + } + if (styleId == -1) styleId = 0; + var si = ss.Value; + si.StyleId = (UInt32)styleId; + newSharedStyles.Add(ss.Key, si); + } + sharedStyles.Clear(); + newSharedStyles.ForEach(kp => sharedStyles.Add(kp.Key, kp.Value)); + } + + private void ResolveAlignments(WorkbookStylesPart workbookStylesPart) + { + if (workbookStylesPart.Stylesheet.CellFormats == null) + workbookStylesPart.Stylesheet.CellFormats = new CellFormats(); + + foreach (var styleInfo in sharedStyles.Values) + { + Int32 styleId = 0; + Boolean foundOne = false; + foreach (CellFormat f in workbookStylesPart.Stylesheet.CellFormats) + { + if (CellFormatsAreEqual(f, styleInfo)) + { + foundOne = true; + break; + } + styleId++; + } + if (!foundOne) + { + Int32 formatId = 0; + foreach (CellFormat f in workbookStylesPart.Stylesheet.CellStyleFormats) + { + if (CellFormatsAreEqual(f, styleInfo)) + break; + styleId++; + } + + CellFormat cellFormat = new CellFormat() { NumberFormatId = (UInt32)styleInfo.NumberFormatId, FontId = (UInt32)styleInfo.FontId, FillId = (UInt32)styleInfo.FillId, BorderId = (UInt32)styleInfo.BorderId, ApplyNumberFormat = false, ApplyFill = ApplyFill(styleInfo), ApplyBorder = ApplyBorder(styleInfo), ApplyAlignment = false, ApplyProtection = false, FormatId = (UInt32)formatId }; + Alignment alignment = new Alignment() + { + Horizontal = alignmentHorizontalValues.Single(a => a.Key == styleInfo.Style.Alignment.Horizontal).Value, + Vertical = alignmentVerticalValues.Single(a => a.Key == styleInfo.Style.Alignment.Vertical).Value, + Indent = (UInt32)styleInfo.Style.Alignment.Indent, + ReadingOrder = (UInt32)styleInfo.Style.Alignment.ReadingOrder, + WrapText = styleInfo.Style.Alignment.WrapText, + TextRotation = (UInt32)styleInfo.Style.Alignment.TextRotation, + ShrinkToFit = styleInfo.Style.Alignment.ShrinkToFit, + RelativeIndent = styleInfo.Style.Alignment.RelativeIndent, + JustifyLastLine = styleInfo.Style.Alignment.JustifyLastLine + }; + cellFormat.Append(alignment); + workbookStylesPart.Stylesheet.CellFormats.Append(cellFormat); + } + } + workbookStylesPart.Stylesheet.CellFormats.Count = (UInt32)workbookStylesPart.Stylesheet.CellFormats.Count(); + } + + private Dictionary ResolveCellStyleFormats(WorkbookStylesPart workbookStylesPart) + { + if (workbookStylesPart.Stylesheet.CellStyleFormats == null) + workbookStylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats(); + + var allSharedStyles = new Dictionary(); + foreach (var styleInfo in sharedStyles.Values) + { + Int32 styleId = 0; + Boolean foundOne = false; + foreach (CellFormat f in workbookStylesPart.Stylesheet.CellStyleFormats) + { + if (CellFormatsAreEqual(f, styleInfo)) + { + foundOne = true; + break; + } + styleId++; + } + if (!foundOne) + { + CellFormat cellStyleFormat = new CellFormat() { NumberFormatId = (UInt32)styleInfo.NumberFormatId, FontId = (UInt32)styleInfo.FontId, FillId = (UInt32)styleInfo.FillId, BorderId = (UInt32)styleInfo.BorderId, ApplyNumberFormat = false, ApplyFill = ApplyFill(styleInfo), ApplyBorder = ApplyBorder(styleInfo), ApplyAlignment = false, ApplyProtection = false }; + workbookStylesPart.Stylesheet.CellStyleFormats.Append(cellStyleFormat); + } + allSharedStyles.Add(styleInfo.Style.ToString(), new StyleInfo() { Style = styleInfo.Style, StyleId = (UInt32)styleId }); + } + workbookStylesPart.Stylesheet.CellStyleFormats.Count = (UInt32)workbookStylesPart.Stylesheet.CellStyleFormats.Count(); + + return allSharedStyles; + } + + private Boolean ApplyFill(StyleInfo styleInfo) + { + return fillPatternValues.Single(p => p.Key == styleInfo.Style.Fill.PatternType).Value == PatternValues.None; + } + + private Boolean ApplyBorder(StyleInfo styleInfo) + { + IXLBorder opBorder = styleInfo.Style.Border; + return ( + borderStyleValues.Single(b => b.Key == opBorder.BottomBorder).Value != BorderStyleValues.None + || borderStyleValues.Single(b => b.Key == opBorder.DiagonalBorder).Value != BorderStyleValues.None + || borderStyleValues.Single(b => b.Key == opBorder.RightBorder).Value != BorderStyleValues.None + || borderStyleValues.Single(b => b.Key == opBorder.LeftBorder).Value != BorderStyleValues.None + || borderStyleValues.Single(b => b.Key == opBorder.TopBorder).Value != BorderStyleValues.None); + } + + private bool CellFormatsAreEqual(CellFormat f, StyleInfo styleInfo) + { + return + styleInfo.BorderId == f.BorderId + && styleInfo.FillId == f.FillId + && styleInfo.FontId == f.FontId + && styleInfo.NumberFormatId == f.NumberFormatId + && f.ApplyNumberFormat != null && f.ApplyNumberFormat == false + && f.ApplyAlignment != null && f.ApplyAlignment == false + && f.ApplyProtection != null && f.ApplyProtection == false + && f.ApplyFill != null && f.ApplyFill == ApplyFill(styleInfo) + && f.ApplyBorder != null && f.ApplyBorder == ApplyBorder(styleInfo) + && AlignmentsAreEqual(f.Alignment, styleInfo.Style.Alignment) + ; + } + + private bool AlignmentsAreEqual(Alignment alignment, IXLAlignment xlAlignment) + { + var a = new XLAlignment(); + if (alignment != null) + { + if (alignment.Horizontal != null) + a.Horizontal = alignmentHorizontalValues.Single(p => p.Value == alignment.Horizontal.Value).Key; + if (alignment.Vertical != null) + a.Vertical = alignmentVerticalValues.Single(p => p.Value == alignment.Vertical.Value).Key; + if (alignment.Indent != null) + a.Indent = (Int32)alignment.Indent.Value; + if (alignment.ReadingOrder != null) + a.ReadingOrder = alignmentReadingOrderValues.Single(p => p.Value == alignment.ReadingOrder.Value).Key; + if (alignment.WrapText != null) + a.WrapText = alignment.WrapText.Value; + if (alignment.TextRotation != null) + a.TextRotation = (Int32)alignment.TextRotation.Value; + if (alignment.ShrinkToFit != null) + a.ShrinkToFit = alignment.ShrinkToFit.Value; + if (alignment.RelativeIndent != null) + a.RelativeIndent = alignment.RelativeIndent.Value; + if (alignment.JustifyLastLine != null) + a.JustifyLastLine = alignment.JustifyLastLine.Value; + } + return a.ToString() == xlAlignment.ToString(); + } + + private Dictionary ResolveBorders(WorkbookStylesPart workbookStylesPart, Dictionary sharedBorders) + { + if (workbookStylesPart.Stylesheet.Borders == null) + workbookStylesPart.Stylesheet.Borders = new Borders(); + + var allSharedBorders = new Dictionary(); + foreach (var borderInfo in sharedBorders.Values) + { + Int32 borderId = 0; + Boolean foundOne = false; + foreach (Border f in workbookStylesPart.Stylesheet.Borders) + { + if (BordersAreEqual(f, borderInfo.Border)) + { + foundOne = true; + break; + } + borderId++; + } + if (!foundOne) + { + Border border = GetNewBorder(borderInfo); + workbookStylesPart.Stylesheet.Borders.Append(border); + } + allSharedBorders.Add(borderInfo.Border.ToString(), new BorderInfo() { Border = borderInfo.Border, BorderId = (UInt32)borderId }); + } + workbookStylesPart.Stylesheet.Borders.Count = (UInt32)workbookStylesPart.Stylesheet.Borders.Count(); + return allSharedBorders; + } + + private Border GetNewBorder(BorderInfo borderInfo) + { + Border border = new Border() { DiagonalUp = borderInfo.Border.DiagonalUp, DiagonalDown = borderInfo.Border.DiagonalDown }; + + LeftBorder leftBorder = new LeftBorder() { Style = borderStyleValues.Single(b => b.Key == borderInfo.Border.LeftBorder).Value }; + Color leftBorderColor = new Color() { Rgb = borderInfo.Border.LeftBorderColor.ToHex() }; + leftBorder.Append(leftBorderColor); + border.Append(leftBorder); + + RightBorder rightBorder = new RightBorder() { Style = borderStyleValues.Single(b => b.Key == borderInfo.Border.RightBorder).Value }; + Color rightBorderColor = new Color() { Rgb = borderInfo.Border.RightBorderColor.ToHex() }; + rightBorder.Append(rightBorderColor); + border.Append(rightBorder); + + TopBorder topBorder = new TopBorder() { Style = borderStyleValues.Single(b => b.Key == borderInfo.Border.TopBorder).Value }; + Color topBorderColor = new Color() { Rgb = borderInfo.Border.TopBorderColor.ToHex() }; + topBorder.Append(topBorderColor); + border.Append(topBorder); + + BottomBorder bottomBorder = new BottomBorder() { Style = borderStyleValues.Single(b => b.Key == borderInfo.Border.BottomBorder).Value }; + Color bottomBorderColor = new Color() { Rgb = borderInfo.Border.BottomBorderColor.ToHex() }; + bottomBorder.Append(bottomBorderColor); + border.Append(bottomBorder); + + DiagonalBorder diagonalBorder = new DiagonalBorder() { Style = borderStyleValues.Single(b => b.Key == borderInfo.Border.DiagonalBorder).Value }; + Color diagonalBorderColor = new Color() { Rgb = borderInfo.Border.DiagonalBorderColor.ToHex() }; + diagonalBorder.Append(diagonalBorderColor); + border.Append(diagonalBorder); + + return border; + } + + private bool BordersAreEqual(Border b, IXLBorder xlBorder) + { + var nb = new XLBorder(); + if (b.DiagonalUp != null) + nb.DiagonalUp = b.DiagonalUp.Value; + + if (b.DiagonalDown != null) + nb.DiagonalDown = b.DiagonalDown.Value; + + if (b.LeftBorder != null) + { + if (b.LeftBorder.Style != null) + nb.LeftBorder = borderStyleValues.Single(p => p.Value == b.LeftBorder.Style).Key; + var bColor = GetColor(b.LeftBorder.Color); + if (bColor != null) + nb.LeftBorderColor = bColor.Value; + } + + if (b.RightBorder != null) + { + if (b.RightBorder.Style != null) + nb.RightBorder = borderStyleValues.Single(p => p.Value == b.RightBorder.Style).Key; + var bColor = GetColor(b.RightBorder.Color); + if (bColor != null) + nb.RightBorderColor = bColor.Value; + } + + if (b.TopBorder != null) + { + if (b.TopBorder.Style != null) + nb.TopBorder = borderStyleValues.Single(p => p.Value == b.TopBorder.Style).Key; + var bColor = GetColor(b.TopBorder.Color); + if (bColor != null) + nb.TopBorderColor = bColor.Value; + } + + if (b.BottomBorder != null) + { + if (b.BottomBorder.Style != null) + nb.BottomBorder = borderStyleValues.Single(p => p.Value == b.BottomBorder.Style).Key; + var bColor = GetColor(b.BottomBorder.Color); + if (bColor != null) + nb.BottomBorderColor = bColor.Value; + } + + return nb.ToString() == xlBorder.ToString(); + } + + private Dictionary ResolveFills(WorkbookStylesPart workbookStylesPart, Dictionary sharedFills) + { + if (workbookStylesPart.Stylesheet.Fills == null) + workbookStylesPart.Stylesheet.Fills = new Fills(); + + ResolveFillWithPattern(workbookStylesPart.Stylesheet.Fills, PatternValues.None); + ResolveFillWithPattern(workbookStylesPart.Stylesheet.Fills, PatternValues.Gray125); + + var allSharedFills = new Dictionary(); + foreach (var fillInfo in sharedFills.Values) + { + Int32 fillId = 0; + Boolean foundOne = false; + foreach (Fill f in workbookStylesPart.Stylesheet.Fills) + { + if (FillsAreEqual(f, fillInfo.Fill)) + { + foundOne = true; + break; + } + fillId++; + } + if (!foundOne) + { + Fill fill = GetNewFill(fillInfo); + workbookStylesPart.Stylesheet.Fills.Append(fill); + } + allSharedFills.Add(fillInfo.Fill.ToString(), new FillInfo() { Fill = fillInfo.Fill, FillId = (UInt32)fillId }); + } + + workbookStylesPart.Stylesheet.Fills.Count = (UInt32)workbookStylesPart.Stylesheet.Fills.Count(); + return allSharedFills; + } + + private void ResolveFillWithPattern(Fills fills, PatternValues patternValues) + { + if (!fills.Elements().Where(f => + f.PatternFill.PatternType == patternValues + && f.PatternFill.ForegroundColor == null + && f.PatternFill.BackgroundColor == null + ).Any()) + { + Fill fill1 = new Fill(); + PatternFill patternFill1 = new PatternFill() { PatternType = patternValues }; + fill1.Append(patternFill1); + fills.Append(fill1); + } + + } + + private Fill GetNewFill(FillInfo fillInfo) + { + Fill fill = new Fill(); + + PatternFill patternFill = new PatternFill() { PatternType = fillPatternValues.Single(p => p.Key == fillInfo.Fill.PatternType).Value }; + ForegroundColor foregroundColor = new ForegroundColor() { Rgb = fillInfo.Fill.PatternColor.ToHex() }; + BackgroundColor backgroundColor = new BackgroundColor() { Rgb = fillInfo.Fill.PatternBackgroundColor.ToHex() }; + + patternFill.Append(foregroundColor); + patternFill.Append(backgroundColor); + + fill.Append(patternFill); + + return fill; + } + + private bool FillsAreEqual(Fill f, IXLFill xlFill) + { + var nF = new XLFill(); + if (f.PatternFill != null) + { + if (f.PatternFill.PatternType != null) + nF.PatternType = fillPatternValues.Single(p => p.Value == f.PatternFill.PatternType).Key; + + var fColor = GetColor(f.PatternFill.ForegroundColor); + if (fColor != null) + nF.PatternColor = fColor.Value; + + var bColor = GetColor(f.PatternFill.BackgroundColor); + if (bColor != null) + nF.PatternBackgroundColor = bColor.Value; + } + return nF.ToString() == xlFill.ToString(); + } + + private Dictionary ResolveFonts(WorkbookStylesPart workbookStylesPart, Dictionary sharedFonts) + { + if (workbookStylesPart.Stylesheet.Fonts == null) + workbookStylesPart.Stylesheet.Fonts = new Fonts(); + + var allSharedFonts = new Dictionary(); + foreach (var fontInfo in sharedFonts.Values) + { + Int32 fontId = 0; + Boolean foundOne = false; + foreach (Font f in workbookStylesPart.Stylesheet.Fonts) + { + if (FontsAreEqual(f, fontInfo.Font)) + { + foundOne = true; + break; + } + fontId++; + } + if (!foundOne) + { + Font font = GetNewFont(fontInfo); + workbookStylesPart.Stylesheet.Fonts.Append(font); + } + allSharedFonts.Add(fontInfo.Font.ToString(), new FontInfo() { Font = fontInfo.Font, FontId = (UInt32)fontId }); + } + workbookStylesPart.Stylesheet.Fonts.Count = (UInt32)workbookStylesPart.Stylesheet.Fonts.Count(); + return allSharedFonts; + } + + private Font GetNewFont(FontInfo fontInfo) + { + Font font = new Font(); + Bold bold = fontInfo.Font.Bold ? new Bold() : null; + Italic italic = fontInfo.Font.Italic ? new Italic() : null; + Underline underline = fontInfo.Font.Underline != XLFontUnderlineValues.None ? new Underline() { Val = underlineValuesList.Single(u => u.Key == fontInfo.Font.Underline).Value } : null; + Strike strike = fontInfo.Font.Strikethrough ? new Strike() : null; + VerticalTextAlignment verticalAlignment = new VerticalTextAlignment() { Val = fontVerticalTextAlignmentValues.Single(f => f.Key == fontInfo.Font.VerticalAlignment).Value }; + Shadow shadow = fontInfo.Font.Shadow ? new Shadow() : null; + FontSize fontSize = new FontSize() { Val = fontInfo.Font.FontSize }; + Color color = new Color() { Rgb = fontInfo.Font.FontColor.ToHex() }; + FontName fontName = new FontName() { Val = fontInfo.Font.FontName }; + FontFamilyNumbering fontFamilyNumbering = new FontFamilyNumbering() { Val = (Int32)fontInfo.Font.FontFamilyNumbering }; + + if (bold != null) font.Append(bold); + if (italic != null) font.Append(italic); + if (underline != null) font.Append(underline); + if (strike != null) font.Append(strike); + font.Append(verticalAlignment); + if (shadow != null) font.Append(shadow); + font.Append(fontSize); + font.Append(color); + font.Append(fontName); + font.Append(fontFamilyNumbering); + + return font; + } + + private bool FontsAreEqual(Font f, IXLFont xlFont) + { + var nf = XLWorkbook.GetXLFont(); + nf.Bold = f.Bold != null; + nf.Italic = f.Italic != null; + if (f.Underline != null) + nf.Underline = underlineValuesList.Single(u => u.Value == f.Underline.Val).Key; + nf.Strikethrough = f.Strike != null; + if (f.VerticalTextAlignment != null) + nf.VerticalAlignment = fontVerticalTextAlignmentValues.Single(v => v.Value == f.VerticalTextAlignment.Val).Key; + nf.Shadow = f.Shadow != null; + if (f.FontSize != null) + nf.FontSize = f.FontSize.Val; + var fColor = GetColor(f.Color); + if (fColor != null) + nf.FontColor = fColor.Value; + if (f.FontName != null) + nf.FontName = f.FontName.Val; + if (f.FontFamilyNumbering != null) + nf.FontFamilyNumbering = (XLFontFamilyNumberingValues)f.FontFamilyNumbering.Val.Value; + + return nf.ToString() == xlFont.ToString(); + } + + private Dictionary ResolveNumberFormats(WorkbookStylesPart workbookStylesPart, Dictionary sharedNumberFormats) + { + if (workbookStylesPart.Stylesheet.NumberingFormats == null) + workbookStylesPart.Stylesheet.NumberingFormats = new NumberingFormats(); + + var allSharedNumberFormats = new Dictionary(); + foreach (var numberFormatInfo in sharedNumberFormats.Values) + { + Int32 numberingFormatId = 0; + Boolean foundOne = false; + foreach (NumberingFormat nf in workbookStylesPart.Stylesheet.NumberingFormats) + { + if (NumberFormatsAreEqual(nf, numberFormatInfo.NumberFormat)) + { + foundOne = true; + break; + } + numberingFormatId++; + } + if (!foundOne) + { + NumberingFormat numberingFormat = new NumberingFormat() { NumberFormatId = (UInt32)numberingFormatId, FormatCode = numberFormatInfo.NumberFormat.Format }; + workbookStylesPart.Stylesheet.NumberingFormats.Append(numberingFormat); + } + allSharedNumberFormats.Add(numberFormatInfo.NumberFormat.ToString(), new NumberFormatInfo() { NumberFormat = numberFormatInfo.NumberFormat, NumberFormatId = numberingFormatId }); + } + workbookStylesPart.Stylesheet.NumberingFormats.Count = (UInt32)workbookStylesPart.Stylesheet.NumberingFormats.Count(); + return allSharedNumberFormats; + } + + private bool NumberFormatsAreEqual(NumberingFormat nf, IXLNumberFormat xlNumberFormat) + { + var newXLNumberFormat = new XLNumberFormat(); + + if (nf.FormatCode != null && !String.IsNullOrWhiteSpace(nf.FormatCode.Value)) + newXLNumberFormat.Format = nf.FormatCode.Value; + else if (nf.NumberFormatId != null) + newXLNumberFormat.NumberFormatId = (Int32)nf.NumberFormatId.Value; + + return newXLNumberFormat.ToString() == xlNumberFormat.ToString(); } private void GenerateWorksheetPartContent(WorksheetPart worksheetPart, XLWorksheet xlWorksheet) { - Worksheet worksheet = new Worksheet(); - worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); - SheetProperties sheetProperties = new SheetProperties() { CodeName = xlWorksheet.Name.RemoveSpecialCharacters() }; - OutlineProperties outlineProperties = new OutlineProperties() { - SummaryBelow = (xlWorksheet.Outline.SummaryVLocation == XLOutlineSummaryVLocation.Bottom), - SummaryRight = (xlWorksheet.Outline.SummaryHLocation == XLOutlineSummaryHLocation.Right) - }; - sheetProperties.Append(outlineProperties); + #region Worksheet + if (worksheetPart.Worksheet == null) + worksheetPart.Worksheet = new Worksheet(); + + if (!worksheetPart.Worksheet.NamespaceDeclarations.Contains(new KeyValuePair("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"))) + worksheetPart.Worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); + #endregion + + #region SheetProperties + if (worksheetPart.Worksheet.SheetProperties == null) + worksheetPart.Worksheet.SheetProperties = new SheetProperties() { CodeName = xlWorksheet.Name.RemoveSpecialCharacters() }; + + if (worksheetPart.Worksheet.SheetProperties.OutlineProperties == null) + worksheetPart.Worksheet.SheetProperties.OutlineProperties = new OutlineProperties(); + + worksheetPart.Worksheet.SheetProperties.OutlineProperties.SummaryBelow = (xlWorksheet.Outline.SummaryVLocation == XLOutlineSummaryVLocation.Bottom); + worksheetPart.Worksheet.SheetProperties.OutlineProperties.SummaryRight = (xlWorksheet.Outline.SummaryHLocation == XLOutlineSummaryHLocation.Right); + + if (worksheetPart.Worksheet.SheetProperties.PageSetupProperties == null && (xlWorksheet.PageSetup.PagesTall > 0 || xlWorksheet.PageSetup.PagesWide > 0)) + worksheetPart.Worksheet.SheetProperties.PageSetupProperties = new PageSetupProperties(); if (xlWorksheet.PageSetup.PagesTall > 0 || xlWorksheet.PageSetup.PagesWide > 0) - { - PageSetupProperties pageSetupProperties = new PageSetupProperties() { FitToPage = true }; - sheetProperties.Append(pageSetupProperties); - } + worksheetPart.Worksheet.SheetProperties.PageSetupProperties.FitToPage = true; + #endregion + UInt32 maxColumn = 0; UInt32 maxRow = 0; @@ -811,14 +1355,18 @@ if (maxRowCollection > maxRow) maxRow = maxRowCollection; } - SheetDimension sheetDimension = new SheetDimension() { Reference = sheetDimensionReference }; + #region SheetViews + if (worksheetPart.Worksheet.SheetDimension == null) + worksheetPart.Worksheet.SheetDimension = new SheetDimension() { Reference = sheetDimensionReference }; - Boolean tabSelected = xlWorksheet.Name == Worksheets.Worksheet(0).Name; - SheetViews sheetViews = new SheetViews(); - SheetView sheetView = new SheetView() { TabSelected = tabSelected, WorkbookViewId = (UInt32Value)0U }; + if (worksheetPart.Worksheet.SheetViews == null) + worksheetPart.Worksheet.SheetViews = new SheetViews(); - sheetViews.Append(sheetView); + if (worksheetPart.Worksheet.SheetViews.Count() == 0) + worksheetPart.Worksheet.SheetViews.Append(new SheetView() { WorkbookViewId = (UInt32Value)0U }); + + #endregion var maxOutlineColumn = 0; if (xlWorksheet.Columns().Count() > 0) @@ -828,90 +1376,145 @@ if (xlWorksheet.Rows().Count() > 0) maxOutlineRow = xlWorksheet.Rows().Cast().Max(c => c.OutlineLevel); - SheetFormatProperties sheetFormatProperties3 = new SheetFormatProperties() { DefaultRowHeight = xlWorksheet.RowHeight, DefaultColumnWidth = xlWorksheet.ColumnWidth , CustomHeight = true }; + #region SheetFormatProperties + if (worksheetPart.Worksheet.SheetFormatProperties == null) + worksheetPart.Worksheet.SheetFormatProperties = new SheetFormatProperties(); + + worksheetPart.Worksheet.SheetFormatProperties.DefaultRowHeight = xlWorksheet.RowHeight; + worksheetPart.Worksheet.SheetFormatProperties.DefaultColumnWidth = xlWorksheet.ColumnWidth; + worksheetPart.Worksheet.SheetFormatProperties.CustomHeight = true; + if (maxOutlineColumn > 0) - sheetFormatProperties3.OutlineLevelColumn = (byte)maxOutlineColumn; + worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelColumn = (byte)maxOutlineColumn; + else + worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelColumn = null; + if (maxOutlineRow > 0) - sheetFormatProperties3.OutlineLevelRow = (byte)maxOutlineRow; + worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelRow = (byte)maxOutlineRow; + else + worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelRow = null; + #endregion - Columns columns = new Columns(); - - - Int32 minInColumnsCollection; - Int32 maxInColumnsCollection; - if (xlWorksheet.Internals.ColumnsCollection.Count > 0) + #region Columns + Columns columns = null; + if (xlWorksheet.Internals.CellsCollection.Count == 0) { - minInColumnsCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Min(); - maxInColumnsCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Max(); + worksheetPart.Worksheet.RemoveAllChildren(); } else { - minInColumnsCollection = 1; - maxInColumnsCollection = 0; - } - - if (minInColumnsCollection > 1) - { - Column column = new Column() - { - Min = 1, - Max = (UInt32Value)(UInt32)(minInColumnsCollection - 1), - Style = sharedStyles[xlWorksheet.Style.ToString()].StyleId, - Width = xlWorksheet.ColumnWidth, - CustomWidth = true - }; - columns.Append(column); - } + if (worksheetPart.Worksheet.Elements().Count() == 0) + worksheetPart.Worksheet.InsertAfter(new Columns(), worksheetPart.Worksheet.SheetFormatProperties); - for(var co = minInColumnsCollection; co <= maxInColumnsCollection; co++) - { - UInt32 styleId; - Double columnWidth; - Boolean isHidden = false; - Boolean collapsed = false; - Int32 outlineLevel = 0; - if (xlWorksheet.Internals.ColumnsCollection.ContainsKey(co)) + columns = worksheetPart.Worksheet.Elements().First(); + + Int32 minInColumnsCollection; + Int32 maxInColumnsCollection; + if (xlWorksheet.Internals.ColumnsCollection.Count > 0) { - styleId = sharedStyles[xlWorksheet.Internals.ColumnsCollection[co].Style.ToString()].StyleId; - columnWidth = xlWorksheet.Internals.ColumnsCollection[co].Width; - isHidden = xlWorksheet.Internals.ColumnsCollection[co].IsHidden; - collapsed = xlWorksheet.Internals.ColumnsCollection[co].Collapsed; - outlineLevel = xlWorksheet.Internals.ColumnsCollection[co].OutlineLevel; + minInColumnsCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Min(); + maxInColumnsCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Max(); } else { - styleId = sharedStyles[xlWorksheet.Style.ToString()].StyleId; - columnWidth = xlWorksheet.ColumnWidth; + minInColumnsCollection = 1; + maxInColumnsCollection = 0; } - Column column = new Column() + if (minInColumnsCollection > 1) { - Min = (UInt32Value)(UInt32)co, - Max = (UInt32Value)(UInt32)co, - Style = styleId, - Width = columnWidth, - CustomWidth = true - }; - if (isHidden) column.Hidden = true; - if (collapsed) column.Collapsed = true; - if (outlineLevel > 0) column.OutlineLevel = (byte)outlineLevel; - columns.Append(column); - } + UInt32Value min = 1; + UInt32Value max = (UInt32)(minInColumnsCollection - 1); + var styleId = sharedStyles[xlWorksheet.Style.ToString()].StyleId; - if (maxInColumnsCollection < XLWorksheet.MaxNumberOfColumns) + for (var co = min; co <= max; co++) + { + Column column = new Column() + { + Min = co, + Max = co, + Style = styleId, + Width = xlWorksheet.ColumnWidth, + CustomWidth = true + }; + + UpdateColumn(column, columns); + } + } + + for (var co = minInColumnsCollection; co <= maxInColumnsCollection; co++) + { + UInt32 styleId; + Double columnWidth; + Boolean isHidden = false; + Boolean collapsed = false; + Int32 outlineLevel = 0; + if (xlWorksheet.Internals.ColumnsCollection.ContainsKey(co)) + { + styleId = sharedStyles[xlWorksheet.Internals.ColumnsCollection[co].Style.ToString()].StyleId; + columnWidth = xlWorksheet.Internals.ColumnsCollection[co].Width; + isHidden = xlWorksheet.Internals.ColumnsCollection[co].IsHidden; + collapsed = xlWorksheet.Internals.ColumnsCollection[co].Collapsed; + outlineLevel = xlWorksheet.Internals.ColumnsCollection[co].OutlineLevel; + } + else + { + styleId = sharedStyles[xlWorksheet.Style.ToString()].StyleId; + columnWidth = xlWorksheet.ColumnWidth; + } + + Column column = new Column() + { + Min = (UInt32)co, + Max = (UInt32)co, + Style = styleId, + Width = columnWidth, + CustomWidth = true + }; + if (isHidden) column.Hidden = true; + if (collapsed) column.Collapsed = true; + if (outlineLevel > 0) column.OutlineLevel = (byte)outlineLevel; + + UpdateColumn(column, columns); + } + + foreach (var col in columns.Elements().Where(c => c.Min > (UInt32)(maxInColumnsCollection)).OrderBy(c => c.Min.Value)) + { + col.Style = sharedStyles[xlWorksheet.Style.ToString()].StyleId; + col.Width = xlWorksheet.ColumnWidth; + col.CustomWidth = true; + if ((Int32)col.Max.Value > maxInColumnsCollection) + maxInColumnsCollection = (Int32)col.Max.Value; + } + + if (maxInColumnsCollection < XLWorksheet.MaxNumberOfColumns) + { + Column column = new Column() + { + Min = (UInt32)(maxInColumnsCollection + 1), + Max = (UInt32)(XLWorksheet.MaxNumberOfColumns), + Style = sharedStyles[xlWorksheet.Style.ToString()].StyleId, + Width = xlWorksheet.ColumnWidth, + CustomWidth = true + }; + columns.Append(column); + } + } +#endregion + + #region SheetData + SheetData sheetData; + if (worksheetPart.Worksheet.Elements().Count() == 0) { - Column column = new Column() - { - Min = (UInt32Value)(UInt32)(maxInColumnsCollection + 1), - Max = (UInt32Value)(UInt32)(XLWorksheet.MaxNumberOfColumns), - Style = sharedStyles[xlWorksheet.Style.ToString()].StyleId, - Width = xlWorksheet.ColumnWidth, - CustomWidth = true - }; - columns.Append(column); + OpenXmlElement previousElement; + if (columns != null) + previousElement = columns; + else + previousElement = worksheetPart.Worksheet.SheetFormatProperties; + worksheetPart.Worksheet.InsertAfter(new SheetData(), previousElement); } - SheetData sheetData = new SheetData(); + sheetData = worksheetPart.Worksheet.Elements().First(); var rowsFromCells = xlWorksheet.Internals.CellsCollection.Where(c => c.Key.ColumnNumber > 0 && c.Key.RowNumber > 0).Select(c => c.Key.RowNumber).Distinct(); var rowsFromCollection = xlWorksheet.Internals.RowsCollection.Keys; @@ -921,7 +1524,27 @@ foreach (var distinctRow in distinctRows.OrderBy(r => r)) { - Row row = new Row() { RowIndex = (UInt32Value)(UInt32)distinctRow }; + Row row = sheetData.Elements().FirstOrDefault(r=>r.RowIndex.Value == (UInt32)distinctRow); + if (row == null) + { + row = new Row() { RowIndex = (UInt32)distinctRow }; + if (sheetData.Elements().Count() == 0) + { + sheetData.Append(row); + } + else + { + Row rowBeforeInsert = sheetData.Elements() + .Where(c => c.RowIndex.Value > row.RowIndex.Value) + .OrderBy(c => c.RowIndex.Value) + .FirstOrDefault(); + if (rowBeforeInsert == null) + sheetData.Append(row); + else + sheetData.InsertBefore(row, rowBeforeInsert); + } + } + if (maxColumn > 0) row.Spans = new ListValue() { InnerText = "1:" + maxColumn.ToString() }; @@ -944,137 +1567,269 @@ row.Hidden = false; } + List cellsToRemove = new List(); + foreach (var cell in row.Elements()) + { + var cellReference = cell.CellReference; + if (xlWorksheet.Internals.CellsCollection.Deleted.ContainsKey(new XLAddress(cellReference))) + cellsToRemove.Add(cell); + } + cellsToRemove.ForEach(cell => row.RemoveChild(cell)); + foreach (var opCell in xlWorksheet.Internals.CellsCollection .Where(c => c.Key.RowNumber == distinctRow) .OrderBy(c => c.Key) .Select(c => c)) { var styleId = sharedStyles[opCell.Value.Style.ToString()].StyleId; - Cell cell; + var dataType = opCell.Value.DataType; var cellReference = opCell.Key.ToString(); - if (!String.IsNullOrWhiteSpace(opCell.Value.FormulaA1)) + Boolean isNewCell = false; + Cell cell = row.Elements().FirstOrDefault(c => c.CellReference.Value == cellReference); + if (cell == null) { - cell = new Cell() { CellReference = cellReference, StyleIndex = styleId }; - cell.Append(new CellFormula(opCell.Value.FormulaA1)); - } - else - { - if (opCell.Value.DataType == XLCellValues.DateTime) + isNewCell = true; + cell = new Cell() { CellReference = cellReference }; + if (row.Elements().Count() == 0) { - cell = new Cell() - { - CellReference = cellReference, - StyleIndex = styleId - }; - } - else if (styleId == 0) - { - cell = new Cell() - { - CellReference = cellReference, - DataType = GetCellValue(dataType) - }; + row.Append(cell); } else { - cell = new Cell() - { - CellReference = cellReference, - DataType = GetCellValue(dataType), - StyleIndex = styleId - }; + Int32 newColumn = new XLAddress(cellReference).ColumnNumber; + Cell cellBeforeInsert = row.Elements() + .Where(c => new XLAddress(c.CellReference.Value).ColumnNumber > newColumn) + .OrderBy(c => new XLAddress(c.CellReference.Value).ColumnNumber) + .FirstOrDefault(); + if (cellBeforeInsert == null) + row.Append(cell); + else + row.InsertBefore(cell, cellBeforeInsert); } + } + + cell.StyleIndex = styleId; + if (!String.IsNullOrWhiteSpace(opCell.Value.FormulaA1)) + { + cell.CellFormula = new CellFormula(opCell.Value.FormulaA1); + cell.CellValue = null; + } + else + { + cell.CellFormula = null; + + if (opCell.Value.DataType != XLCellValues.DateTime) + cell.DataType = GetCellValue(dataType); + CellValue cellValue = new CellValue(); - if (dataType == XLCellValues.Text && !String.IsNullOrWhiteSpace(opCell.Value.InnerText)) + if (dataType == XLCellValues.Text) { - cellValue.Text = sharedStrings[opCell.Value.InnerText].ToString(); + if (String.IsNullOrWhiteSpace(opCell.Value.InnerText)) + { + if (isNewCell) + cellValue = null; + else + cellValue.Text = String.Empty; + } + else + { + cellValue.Text = sharedStrings[opCell.Value.InnerText].ToString(); + } + cell.CellValue = cellValue; + } + else if (dataType == XLCellValues.DateTime || dataType == XLCellValues.Number) + { + cellValue.Text = Double.Parse(opCell.Value.InnerText).ToString(CultureInfo.InvariantCulture); + cell.CellValue = cellValue; } else { cellValue.Text = opCell.Value.InnerText; + cell.CellValue = cellValue; } - cell.Append(cellValue); } - - row.Append(cell); } - sheetData.Append(row); } + #endregion + var phoneticProperties = worksheetPart.Worksheet.Elements().FirstOrDefault(); + + #region MergeCells MergeCells mergeCells = null; if (xlWorksheet.Internals.MergedCells.Count > 0) { - mergeCells = new MergeCells() { Count = (UInt32Value)(UInt32)xlWorksheet.Internals.MergedCells.Count }; + if (worksheetPart.Worksheet.Elements().Count() == 0) + { + OpenXmlElement previousElement; + if (phoneticProperties != null) + previousElement = phoneticProperties; + else if (sheetData != null) + previousElement = sheetData; + else if (columns != null) + previousElement = columns; + else + previousElement = worksheetPart.Worksheet.SheetFormatProperties; + + worksheetPart.Worksheet.InsertAfter(new MergeCells(), previousElement); + } + + mergeCells = worksheetPart.Worksheet.Elements().First(); + mergeCells.RemoveAllChildren(); + foreach (var merged in xlWorksheet.Internals.MergedCells) { MergeCell mergeCell = new MergeCell() { Reference = merged }; mergeCells.Append(mergeCell); } + + mergeCells.Count = (UInt32)mergeCells.Count(); + } + else + { + worksheetPart.Worksheet.RemoveAllChildren(); + } + #endregion + + #region PrintOptions + PrintOptions printOptions = null; + if (xlWorksheet.Internals.CellsCollection.Count == 0 + //|| !( + // xlWorksheet.PageSetup.CenterHorizontally + //|| xlWorksheet.PageSetup.CenterVertically + //|| xlWorksheet.PageSetup.ShowRowAndColumnHeadings + //|| xlWorksheet.PageSetup.ShowGridlines) + ) + { + worksheetPart.Worksheet.RemoveAllChildren(); + } + else + { + if (worksheetPart.Worksheet.Elements().Count() == 0) + { + OpenXmlElement previousElement; + if (mergeCells != null) + previousElement = mergeCells; + else if (phoneticProperties != null) + previousElement = phoneticProperties; + else if (sheetData != null) + previousElement = sheetData; + else if (columns != null) + previousElement = columns; + else + previousElement = worksheetPart.Worksheet.SheetFormatProperties; + + worksheetPart.Worksheet.InsertAfter(new PrintOptions(), previousElement); + } + + printOptions = worksheetPart.Worksheet.Elements().First(); + + printOptions.HorizontalCentered = xlWorksheet.PageSetup.CenterHorizontally; + printOptions.VerticalCentered = xlWorksheet.PageSetup.CenterVertically; + printOptions.Headings = xlWorksheet.PageSetup.ShowRowAndColumnHeadings; + printOptions.GridLines = xlWorksheet.PageSetup.ShowGridlines; + } + #endregion + + #region PageMargins + if (worksheetPart.Worksheet.Elements().Count() == 0) + { + OpenXmlElement previousElement; + if (printOptions != null) + previousElement = printOptions; + else if (mergeCells != null) + previousElement = mergeCells; + else if (phoneticProperties != null) + previousElement = phoneticProperties; + else if (sheetData != null) + previousElement = sheetData; + else if (columns != null) + previousElement = columns; + else + previousElement = worksheetPart.Worksheet.SheetFormatProperties; + + worksheetPart.Worksheet.InsertAfter(new PageMargins(), previousElement); } - PageMargins pageMargins = new PageMargins() { - Left = xlWorksheet.PageSetup.Margins.Left, - Right = xlWorksheet.PageSetup.Margins.Right, - Top = xlWorksheet.PageSetup.Margins.Top, - Bottom = xlWorksheet.PageSetup.Margins.Bottom, - Header = xlWorksheet.PageSetup.Margins.Header, - Footer = xlWorksheet.PageSetup.Margins.Footer - }; + PageMargins pageMargins = worksheetPart.Worksheet.Elements().First(); + pageMargins.Left = xlWorksheet.PageSetup.Margins.Left; + pageMargins.Right = xlWorksheet.PageSetup.Margins.Right; + pageMargins.Top = xlWorksheet.PageSetup.Margins.Top; + pageMargins.Bottom = xlWorksheet.PageSetup.Margins.Bottom; + pageMargins.Header = xlWorksheet.PageSetup.Margins.Header; + pageMargins.Footer = xlWorksheet.PageSetup.Margins.Footer; + #endregion - + #region PageSetup + if (worksheetPart.Worksheet.Elements().Count() == 0) + { + var nps = new PageSetup(); + nps.Id = relId.GetNext(RelType.Workbook); + worksheetPart.Worksheet.InsertAfter(new PageSetup(), pageMargins); + } - //Drawing drawing1 = new Drawing() { Id = "rId1" }; + PageSetup pageSetup = worksheetPart.Worksheet.Elements().First(); - PageSetup pageSetup1 = new PageSetup() { - Orientation = pageOrientationValues.Single(p=>p.Key == xlWorksheet.PageSetup.PageOrientation).Value, - Id = "rId" + RelId.GetNext(RelType.Worksheet), - PaperSize = (UInt32Value)(UInt32)xlWorksheet.PageSetup.PaperSize, - BlackAndWhite = xlWorksheet.PageSetup.BlackAndWhite, - Draft = xlWorksheet.PageSetup.DraftQuality, - PageOrder = pageOrderValues.Single(p=>p.Key == xlWorksheet.PageSetup.PageOrder).Value, - CellComments = showCommentsValues.Single(s=>s.Key == xlWorksheet.PageSetup.ShowComments).Value, - Errors = printErrorValues.Single(p=>p.Key == xlWorksheet.PageSetup.PrintErrorValue).Value - }; + pageSetup.Orientation = pageOrientationValues.Single(p=>p.Key == xlWorksheet.PageSetup.PageOrientation).Value; + pageSetup.PaperSize = (UInt32)xlWorksheet.PageSetup.PaperSize; + pageSetup.BlackAndWhite = xlWorksheet.PageSetup.BlackAndWhite; + pageSetup.Draft = xlWorksheet.PageSetup.DraftQuality; + pageSetup.PageOrder = pageOrderValues.Single(p=>p.Key == xlWorksheet.PageSetup.PageOrder).Value; + pageSetup.CellComments = showCommentsValues.Single(s=>s.Key == xlWorksheet.PageSetup.ShowComments).Value; + pageSetup.Errors = printErrorValues.Single(p => p.Key == xlWorksheet.PageSetup.PrintErrorValue).Value; if (xlWorksheet.PageSetup.FirstPageNumber > 0) { - pageSetup1.FirstPageNumber = (UInt32Value)(UInt32)xlWorksheet.PageSetup.FirstPageNumber; - pageSetup1.UseFirstPageNumber = true; + pageSetup.FirstPageNumber = (UInt32)xlWorksheet.PageSetup.FirstPageNumber; + pageSetup.UseFirstPageNumber = true; + } + else + { + pageSetup.FirstPageNumber = null; + pageSetup.UseFirstPageNumber = null; } if (xlWorksheet.PageSetup.HorizontalDpi > 0) - pageSetup1.HorizontalDpi = (UInt32Value)(UInt32)xlWorksheet.PageSetup.HorizontalDpi; + pageSetup.HorizontalDpi = (UInt32)xlWorksheet.PageSetup.HorizontalDpi; + else + pageSetup.HorizontalDpi = null; if (xlWorksheet.PageSetup.VerticalDpi > 0) - pageSetup1.VerticalDpi = (UInt32Value)(UInt32)xlWorksheet.PageSetup.VerticalDpi; + pageSetup.VerticalDpi = (UInt32)xlWorksheet.PageSetup.VerticalDpi; + else + pageSetup.VerticalDpi = null; if (xlWorksheet.PageSetup.Scale > 0) { - pageSetup1.Scale = (UInt32Value)(UInt32)xlWorksheet.PageSetup.Scale; + pageSetup.Scale = (UInt32)xlWorksheet.PageSetup.Scale; + pageSetup.FitToWidth = null; + pageSetup.FitToHeight = null; } else { if (xlWorksheet.PageSetup.PagesWide > 0) - pageSetup1.FitToWidth = (UInt32Value)(UInt32)xlWorksheet.PageSetup.PagesWide; + pageSetup.FitToWidth = (UInt32)xlWorksheet.PageSetup.PagesWide; + else + pageSetup.FitToWidth = null; + if (xlWorksheet.PageSetup.PagesTall > 0) - pageSetup1.FitToHeight = (UInt32Value)(UInt32)xlWorksheet.PageSetup.PagesTall; + pageSetup.FitToHeight = (UInt32)xlWorksheet.PageSetup.PagesTall; + else + pageSetup.FitToHeight = null; } + #endregion - PrintOptions printOptions = new PrintOptions() - { - HorizontalCentered = xlWorksheet.PageSetup.CenterHorizontally, - VerticalCentered = xlWorksheet.PageSetup.CenterVertically, - Headings = xlWorksheet.PageSetup.ShowRowAndColumnHeadings, - GridLines = xlWorksheet.PageSetup.ShowGridlines - }; + #region HeaderFooter + if (worksheetPart.Worksheet.Elements().Count() == 0) + worksheetPart.Worksheet.InsertAfter(new HeaderFooter(), pageSetup); - HeaderFooter headerFooter = new HeaderFooter(); + HeaderFooter headerFooter = worksheetPart.Worksheet.Elements().First(); + headerFooter.RemoveAllChildren(); + headerFooter.ScaleWithDoc = xlWorksheet.PageSetup.ScaleHFWithDocument; headerFooter.AlignWithMargins = xlWorksheet.PageSetup.AlignHFWithMargins; headerFooter.DifferentFirst = true; headerFooter.DifferentOddEven = true; - OddHeader oddHeader = new OddHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.OddPages)); headerFooter.Append(oddHeader); @@ -1086,76 +1841,186 @@ EvenFooter evenFooter = new EvenFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.EvenPages)); headerFooter.Append(evenFooter); - //var firstHeaderText = "&L" + xlWorksheet.PageSetup.Header.Left.GetText(XLHFOccurrence.FirstPage) + "&C" + xlWorksheet.PageSetup.Header.Center.GetText(XLHFOccurrence.FirstPage) + "&R" + xlWorksheet.PageSetup.Header.Right.GetText(XLHFOccurrence.FirstPage) + ""; - FirstHeader firstHeader = new FirstHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.FirstPage)); headerFooter.Append(firstHeader); FirstFooter firstFooter = new FirstFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.FirstPage)); headerFooter.Append(firstFooter); - RowBreaks rowBreaks = null; + if (!headerFooter.Any(hf => hf.InnerText.Length > 0)) + worksheetPart.Worksheet.RemoveAllChildren(); + #endregion + + #region RowBreaks + if (worksheetPart.Worksheet.Elements().Count() == 0) + { + OpenXmlElement previousElement; + if (worksheetPart.Worksheet.Elements().Count() > 0) + previousElement = headerFooter; + else + previousElement = pageSetup; + + worksheetPart.Worksheet.InsertAfter(new RowBreaks(), previousElement); + } + + RowBreaks rowBreaks = worksheetPart.Worksheet.Elements().First(); + var rowBreakCount = xlWorksheet.PageSetup.RowBreaks.Count; if (rowBreakCount > 0) { - rowBreaks = new RowBreaks() { Count = (UInt32Value)(UInt32)rowBreakCount, ManualBreakCount = (UInt32)rowBreakCount }; + rowBreaks.Count = (UInt32)rowBreakCount; + rowBreaks.ManualBreakCount = (UInt32)rowBreakCount; foreach (var rb in xlWorksheet.PageSetup.RowBreaks) { - Break break1 = new Break() { Id = (UInt32Value)(UInt32)rb, Max = (UInt32Value)(UInt32)xlWorksheet.RangeAddress.LastAddress.RowNumber, ManualPageBreak = true }; + Break break1 = new Break() { Id = (UInt32)rb, Max = (UInt32)xlWorksheet.RangeAddress.LastAddress.RowNumber, ManualPageBreak = true }; rowBreaks.Append(break1); } - + + } + else + { + worksheetPart.Worksheet.RemoveAllChildren(); + } + #endregion + + #region ColumnBreaks + + if (worksheetPart.Worksheet.Elements().Count() == 0) + { + OpenXmlElement previousElement; + if (worksheetPart.Worksheet.Elements().Count() > 0) + previousElement = rowBreaks; + else if (worksheetPart.Worksheet.Elements().Count() > 0) + previousElement = headerFooter; + else + previousElement = pageSetup; + + worksheetPart.Worksheet.InsertAfter(new ColumnBreaks(), previousElement); } - ColumnBreaks columnBreaks = null; + ColumnBreaks columnBreaks = worksheetPart.Worksheet.Elements().First(); + var columnBreakCount = xlWorksheet.PageSetup.ColumnBreaks.Count; if (columnBreakCount > 0) { - columnBreaks = new ColumnBreaks() { Count = (UInt32Value)(UInt32)columnBreakCount, ManualBreakCount = (UInt32Value)(UInt32)columnBreakCount }; + columnBreaks.Count = (UInt32)columnBreakCount; + columnBreaks.ManualBreakCount = (UInt32)columnBreakCount; foreach (var cb in xlWorksheet.PageSetup.ColumnBreaks) { - Break break1 = new Break() { Id = (UInt32Value)(UInt32)cb, Max = (UInt32Value)(UInt32)xlWorksheet.RangeAddress.LastAddress.ColumnNumber, ManualPageBreak = true }; + Break break1 = new Break() { Id = (UInt32)cb, Max = (UInt32)xlWorksheet.RangeAddress.LastAddress.ColumnNumber, ManualPageBreak = true }; columnBreaks.Append(break1); } } - - worksheet.Append(sheetProperties); - worksheet.Append(sheetDimension); - worksheet.Append(sheetViews); - worksheet.Append(sheetFormatProperties3); - if (columns != null) worksheet.Append(columns); - worksheet.Append(sheetData); - if (mergeCells != null) worksheet.Append(mergeCells); - worksheet.Append(printOptions); - worksheet.Append(pageMargins); - worksheet.Append(pageSetup1); - if (headerFooter.Any(hf=>hf.InnerText.Length > 0)) - worksheet.Append(headerFooter); - if (rowBreaks != null) worksheet.Append(rowBreaks); - if (columnBreaks != null) worksheet.Append(columnBreaks); - //worksheet.Append(drawing1); - - worksheetPart.Worksheet = worksheet; + else + { + worksheetPart.Worksheet.RemoveAllChildren(); + } + #endregion } - private void GenerateCalculationChainPartContent(WorkbookPart workbookPart, String rId) + private void UpdateColumn(Column column, Columns columns) { - Boolean foundOne = false; - CalculationChain calculationChain = new CalculationChain(); - Int32 sheetId = 0; - foreach (var worksheet in Worksheets.Cast()) + Column newColumn; + Column existingColumn = columns.Elements().FirstOrDefault(c => c.Min.Value == column.Min.Value); + if (existingColumn == null) { - sheetId++; - foreach (var c in worksheet.Internals.CellsCollection.Values.Where(c => !String.IsNullOrWhiteSpace(c.FormulaA1))) + newColumn = (Column)column.CloneNode(true); + //newColumn = new Column() { InnerXml = column.InnerXml }; + columns.Append(newColumn); + } + else + { + newColumn = (Column)existingColumn.CloneNode(true); + //newColumn = new Column() { InnerXml = existingColumn.InnerXml }; + newColumn.Min = column.Min; + newColumn.Max = column.Max; + newColumn.Style = column.Style; + newColumn.Width = column.Width; + newColumn.CustomWidth = column.CustomWidth; + + if (column.Hidden != null) + newColumn.Hidden = true; + else + newColumn.Hidden = null; + + if (column.Collapsed != null) + newColumn.Collapsed = true; + else + newColumn.Collapsed = null; + + if (column.OutlineLevel != null && column.OutlineLevel > 0) + newColumn.OutlineLevel = (byte)column.OutlineLevel; + else + newColumn.Hidden = null; + + if (existingColumn.Min + 1 > existingColumn.Max) { - CalculationCell calculationCell = new CalculationCell() { CellReference = c.Address.ToString(), SheetId = sheetId }; - calculationChain.Append(calculationCell); - if (!foundOne) foundOne = true; + //existingColumn.Min = existingColumn.Min + 1; + //columns.InsertBefore(existingColumn, newColumn); + //existingColumn.Remove(); + columns.RemoveChild(existingColumn); + columns.Append(newColumn); + } + else + { + //columns.InsertBefore(existingColumn, newColumn); + columns.Append(newColumn); + existingColumn.Min = existingColumn.Min + 1; } } - if (foundOne) + + } + + private void GenerateCalculationChainPartContent(WorkbookPart workbookPart) + { + var thisRelId = relId.GetNext(RelType.Workbook); + if (workbookPart.CalculationChainPart == null) + workbookPart.AddNewPart(thisRelId); + + if (workbookPart.CalculationChainPart.CalculationChain == null) + workbookPart.CalculationChainPart.CalculationChain = new CalculationChain(); + + CalculationChain calculationChain = workbookPart.CalculationChainPart.CalculationChain; + foreach (var worksheet in Worksheets.Cast()) { - CalculationChainPart calculationChainPart = workbookPart.AddNewPart(rId); - calculationChainPart.CalculationChain = calculationChain; + foreach (var c in worksheet.Internals.CellsCollection.Values.Where(c => !String.IsNullOrWhiteSpace(c.FormulaA1))) + { + var calculationCells = calculationChain.Elements().Where( + cc => cc.CellReference != null && cc.CellReference == c.Address.ToString()).Select(cc=>cc); + Boolean addNew = true; + if (calculationCells.Count() > 0) + { + calculationCells.Where(cc=>cc.SheetId == null).Select(cc=>cc).ForEach(cc=>calculationChain.RemoveChild(cc)); + var cCell = calculationCells.FirstOrDefault(cc=>cc.SheetId == worksheet.SheetId); + if (cCell != null) + { + cCell.SheetId = worksheet.SheetId; + addNew = false; + } + } + + if (addNew) + { + CalculationCell calculationCell = new CalculationCell() { CellReference = c.Address.ToString(), SheetId = worksheet.SheetId }; + calculationChain.Append(calculationCell); + } + } + + 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 && String.IsNullOrWhiteSpace(kp.Value.FormulaA1)).Any() + select cc; + m.ForEach(cc => cCellsToRemove.Add(cc)); + cCellsToRemove.ForEach(cc=>calculationChain.RemoveChild(cc)); + } + + if (calculationChain.Count() == 0) + { + workbookPart.DeletePart(workbookPart.CalculationChainPart); } } @@ -1740,5 +2605,6 @@ document.PackageProperties.Description = Properties.Comments; document.PackageProperties.ContentStatus = Properties.Status; } + } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index 42f1baf..34acf8e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -29,7 +29,7 @@ NamedRanges = new XLNamedRanges(workbook); this.workbook = workbook; Style = workbook.Style; - Internals = new XLWorksheetInternals(new Dictionary(), new XLColumnsCollection(), new XLRowsCollection(), new List(), workbook); + Internals = new XLWorksheetInternals(new XLCellCollection(), new XLColumnsCollection(), new XLRowsCollection(), new List(), workbook); PageSetup = new XLPageSetup(workbook.PageOptions, this); Outline = new XLOutline(workbook.Outline); ColumnWidth = workbook.ColumnWidth; @@ -151,6 +151,7 @@ public Double RowHeight { get; set; } public String Name { get; set; } + public Int32 SheetId { get; set; } public IXLPageSetup PageSetup { get; private set; } public IXLOutline Outline { get; private set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs index 7a304f7..7cf7263 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs @@ -8,7 +8,7 @@ internal class XLWorksheetInternals: IXLWorksheetInternals { public XLWorksheetInternals( - Dictionary cellsCollection , + XLCellCollection cellsCollection, XLColumnsCollection columnsCollection, XLRowsCollection rowsCollection, List mergedCells, @@ -22,7 +22,7 @@ Workbook = workbook; } - public Dictionary CellsCollection { get; private set; } + public XLCellCollection CellsCollection { get; private set; } public XLColumnsCollection ColumnsCollection { get; private set; } public XLRowsCollection RowsCollection { get; private set; } public List MergedCells { get; internal set; } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj index e74906e..fbff07a 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -56,6 +56,7 @@ + diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs index 2fe454a..1050d41 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs @@ -57,6 +57,7 @@ new Collections().Create(@"C:\Excel Files\Created\Collections.xlsx"); new NamedRanges().Create(@"C:\Excel Files\Created\NamedRanges.xlsx"); new CopyingRanges().Create(); + new BlankCells().Create(@"C:\Excel Files\Created\BlankCells.xlsx"); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/LoadFiles.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/LoadFiles.cs index 09393da..2e5ddcd 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/LoadFiles.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/LoadFiles.cs @@ -21,17 +21,7 @@ LoadAndSaveFile(forLoadingFolder + @"\" + fileName, forSavingFolder + @"\" + fileName); } - //LoadAndSaveFile(forLoadingFolder + @"\StyleWorksheet.xlsx", forSavingFolder + @"\StyleWorksheet.xlsx"); - //LoadAndSaveFile(forLoadingFolder + "DataTypes.xlsx", forSavingFolder + "DataTypes.xlsx"); - //LoadAndSaveFile(forLoadingFolder + "MultipleSheets.xlsx", forSavingFolder + "MultipleSheets.xlsx"); - //LoadAndSaveFile(forLoadingFolder + "styleNumberFormat.xlsx", forSavingFolder + "styleNumberFormat.xlsx"); - //LoadAndSaveFile(forLoadingFolder + "styleFill.xlsx", forSavingFolder + "styleFill.xlsx"); - //LoadAndSaveFile(forLoadingFolder + "styleAlignment.xlsx", forSavingFolder + "styleAlignment.xlsx"); - //LoadAndSaveFile(forLoadingFolder + "styleBorder.xlsx", forSavingFolder + "styleBorder.xlsx"); - //LoadAndSaveFile(forLoadingFolder + "styleFont.xlsx", forSavingFolder + "styleFont.xlsx"); - //LoadAndSaveFile(forLoadingFolder + "MergedCells.xlsx", forSavingFolder + "MergedCells.xlsx"); - //LoadAndSaveFile(forLoadingFolder + "ColumnSettings.xlsx", forSavingFolder + "ColumnSettings.xlsx"); - //LoadAndSaveFile(forLoadingFolder + "RowSettings.xlsx", forSavingFolder + "RowSettings.xlsx"); + //LoadAndSaveFile(forLoadingFolder + @"\BasicTable.xlsx", forSavingFolder + @"\BasicTable.xlsx"); } private static void LoadAndSaveFile(String input, String output) diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/BlankCells.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/BlankCells.cs new file mode 100644 index 0000000..f9850b6 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/BlankCells.cs @@ -0,0 +1,63 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; + +using System.Drawing; + +namespace ClosedXML_Examples.Misc +{ + public class BlankCells + { + #region Variables + + // Public + + // Private + + + #endregion + + #region Properties + + // Public + + // Private + + // Override + + + #endregion + + #region Events + + // Public + + // Private + + // Override + + + #endregion + + #region Methods + + // Public + public void Create(String filePath) + { + var wb = new XLWorkbook(); + var ws = wb.Worksheets.Add("Sheet1"); + ws.Cell(1, 1).Value = "X"; + ws.Cell(1, 1).Clear(); + wb.SaveAs(filePath); + } + + // Private + + // Override + + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index 5e3f418..7c572c9 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -5,6 +5,7 @@ using ClosedXML.Excel; using System.Drawing; +using System.IO; namespace ClosedXML_Sandbox { @@ -12,23 +13,32 @@ { static void Main(string[] args) { - //while (true) - //{ - // var startTime = DateTime.Now; - // var wb = new XLWorkbook(@"C:\Excel Files\ForTesting\2007.xlsx"); - // var endTime = DateTime.Now; + var wb = new XLWorkbook(@"C:\Excel Files\ForTesting\Sandbox.xlsx"); + var sheet1 = wb.Worksheets.Worksheet("Sheet1"); + sheet1.Cell(5, 1).Value = 200; + sheet1.Cell(6, 1).Value = 200; + sheet1.Cell(4, 1).FormulaA1 = "A2 + 5"; - // Console.WriteLine("{0} secs.", (endTime - startTime).TotalSeconds); - //} - //Console.ReadKey(); + var sheet2 = wb.Worksheets.Worksheet("Sheet2"); + sheet2.Cell("B3").Value = 50; + wb.SaveAs(@"C:\Excel Files\ForTesting\Sandbox_Saved.xlsx"); + //File.Copy(@"C:\Excel Files\ForTesting\NamedRanges.xlsx", @"C:\Excel Files\ForTesting\Sandbox_Merged.xlsx", true); + //var wb = new XLWorkbook(@"C:\Excel Files\ForTesting\NamedRanges.xlsx"); + //wb.Worksheets.Worksheet(0).Cell(1, 1).Value = "XXX"; + //var ws = wb.Worksheets.Add("Testing"); + //ws.PageSetup.PrintAreas.Add("A1:C3"); + //ws.Range("A1").CreateNamedRange("SuperTest"); + //ws.Cell(1, 1).Value = "Nada"; + //ws.Cell(1, 1).Style.Fill.BackgroundColor = Color.Red; + //wb.NamedRanges.Delete("PeopleData"); + //wb.NamedRanges.Add("PeopleData", ws.Range("A1"), "SuperComment"); + //ws.Cell(1, 2).FormulaA1 = "1+1"; - //var ws = wb.Worksheets.Worksheet("Sheet1"); - - //ws.Cell(1, 1).Value = "something"; + //wb.MergeInto(@"C:\Excel Files\ForTesting\Sandbox_Merged.xlsx"); + //wb.SaveAs(@"C:\Excel Files\ForTesting\Sandbox_Saved.xlsx"); + //wb.SaveChangesTo(@"C:\Excel Files\ForTesting\Sandbox_Changes.xlsx"); - //wb.SaveAs(@"C:\Excel Files\ForTesting\Sandbox.xlsx"); - //Console.ReadKey(); } class Person