diff --git a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj index ea59871..1c06a19 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj @@ -56,6 +56,10 @@ + + + + @@ -77,7 +81,6 @@ - diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs index 9ef9c20..72a822b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs @@ -2,6 +2,7 @@ using System.Collections.Generic; using System.Linq; using System.Text; +using System.Collections; namespace ClosedXML.Excel @@ -118,5 +119,17 @@ IXLRange AsRange(); IXLStyle Style { get; set; } + + /// + /// Gets or sets a value indicating whether this cell's text should be shared or not. + /// + /// + /// If false the cell's text will not be shared and stored as an inline value. + /// + Boolean ShareString { get; set; } + + IXLRange InsertData(IEnumerable data); + IXLTable InsertTable(IEnumerable data); + IXLTable InsertTable(IEnumerable data, String tableName); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index 3c072d2..f0c30be 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -16,7 +16,8 @@ public XLCell(IXLAddress address, IXLStyle defaultStyle, XLWorksheet worksheet) { this.Address = address; - + this.ShareString = true; + if (defaultStyle == null) style = new XLStyle(this, worksheet.Style); else @@ -232,11 +233,150 @@ private Boolean SetEnumerable(Object collectionObject) { var asEnumerable = collectionObject as IEnumerable; - if (asEnumerable != null && collectionObject.GetType() != typeof(String)) + return InsertData(asEnumerable) != null; + } + + public IXLTable InsertTable(IEnumerable data) + { + return InsertTable(data, null, true); + } + public IXLTable InsertTable(IEnumerable data, Boolean createTable) + { + return InsertTable(data, null, createTable); + } + public IXLTable InsertTable(IEnumerable data, String tableName) + { + return InsertTable(data, tableName, true); + } + public IXLTable InsertTable(IEnumerable data, String tableName, Boolean createTable) + { + if (data != null && data.GetType() != typeof(String)) + { + Int32 co; + Int32 ro = Address.RowNumber + 1; + Int32 fRo = Address.RowNumber; + Boolean hasTitles = false; + Int32 maxCo = 0; + Boolean isDataTable = false; + foreach (var m in data) + { + co = Address.ColumnNumber; + + if (m.GetType().IsPrimitive || m.GetType() == typeof(String) || m.GetType() == typeof(DateTime)) + { + if (!hasTitles) + { + SetValue(m.GetType().Name, fRo, co); + hasTitles = true; + co = Address.ColumnNumber; + } + SetValue(m, ro, co); + co++; + } + else if (m.GetType().IsArray) + { + foreach (var item in (Array)m) + { + SetValue(item, ro, co); + co++; + } + } + else if (isDataTable || (m as DataRow) != null) + { + if (!isDataTable) isDataTable = true; + if (!hasTitles) + { + foreach (DataColumn column in (m as DataRow).Table.Columns) + { + SetValue(column.ColumnName, fRo, co); + co++; + } + co = Address.ColumnNumber; + hasTitles = true; + } + + foreach (var item in (m as DataRow).ItemArray) + { + SetValue(item, ro, co); + co++; + } + } + else + { + var fieldInfo = m.GetType().GetFields(); + var propertyInfo = m.GetType().GetProperties(); + if (!hasTitles) + { + foreach (var info in fieldInfo) + { + SetValue(info.Name, fRo, co); + co++; + } + + foreach (var info in propertyInfo) + { + if ((info as IEnumerable) == null) + SetValue(info.Name, fRo, co); + co++; + } + co = Address.ColumnNumber; + hasTitles = true; + } + + foreach (var info in fieldInfo) + { + SetValue(info.GetValue(m), ro, co); + co++; + } + + foreach (var info in propertyInfo) + { + if ((info as IEnumerable) == null) + SetValue(info.GetValue(m, null), ro, co); + co++; + } + } + + if (co > maxCo) + maxCo = co; + + ro++; + } + ClearMerged(ro - 1, maxCo - 1); + var range = worksheet.Range( + Address.RowNumber, + Address.ColumnNumber, + ro - 1, + maxCo - 1); + + if (createTable) + { + if (tableName == null) + return range.CreateTable(); + else + return range.CreateTable(tableName); + } + else + { + if (tableName == null) + return range.AsTable(); + else + return range.AsTable(tableName); + } + } + else + { + return null; + } + } + + public IXLRange InsertData(IEnumerable data) + { + if (data != null && data.GetType() != typeof(String)) { Int32 ro = Address.RowNumber; Int32 maxCo = 0; - foreach (var m in asEnumerable) + foreach (var m in data) { Int32 co = Address.ColumnNumber; @@ -284,11 +424,15 @@ ro++; } ClearMerged(ro - 1, maxCo - 1); - return true; - } + return worksheet.Range( + Address.RowNumber, + Address.ColumnNumber, + Address.RowNumber + ro - 1, + Address.ColumnNumber + maxCo - 1); + } else { - return false; + return null; } } @@ -823,5 +967,7 @@ else if (!StringExtensions.IsNullOrWhiteSpace(formulaA1)) FormulaA1 = GetFormula(formulaA1, FormulaConversionType.R1C1toA1, rowsToShift, columnsToShift); } + + public Boolean ShareString { get; set; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/IXLCustomProperties.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/IXLCustomProperties.cs new file mode 100644 index 0000000..380ba41 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/IXLCustomProperties.cs @@ -0,0 +1,15 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public interface IXLCustomProperties: IEnumerable + { + void Add(IXLCustomProperty customProperty); + void Add(String name, T value); + void Delete(String name); + IXLCustomProperty CustomProperty(String name); + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/IXLCustomProperty.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/IXLCustomProperty.cs new file mode 100644 index 0000000..261659e --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/IXLCustomProperty.cs @@ -0,0 +1,16 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public enum XLCustomPropertyType { Text, Number, Date, Boolean} + public interface IXLCustomProperty + { + String Name { get; set; } + XLCustomPropertyType Type { get; } + Object Value { get; set; } + T GetValue(); + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/XLCustomProperties.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/XLCustomProperties.cs new file mode 100644 index 0000000..6d13fa7 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/XLCustomProperties.cs @@ -0,0 +1,46 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal class XLCustomProperties: IXLCustomProperties, IEnumerable + { + XLWorkbook workbook; + public XLCustomProperties(XLWorkbook workbook) + { + this.workbook = workbook; + } + + private Dictionary customProperties = new Dictionary(); + public void Add(IXLCustomProperty customProperty) + { + customProperties.Add(customProperty.Name, customProperty); + } + public void Add(String name, T value) + { + var cp = new XLCustomProperty(workbook) { Name = name, Value = value }; + Add(cp); + } + + public void Delete(String name) + { + customProperties.Remove(name); + } + public IXLCustomProperty CustomProperty(String name) + { + return customProperties[name]; + } + + public IEnumerator GetEnumerator() + { + return customProperties.Values.GetEnumerator(); + } + + System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() + { + return GetEnumerator(); + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/XLCustomProperty.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/XLCustomProperty.cs new file mode 100644 index 0000000..3a45ea5 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/XLCustomProperty.cs @@ -0,0 +1,51 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal class XLCustomProperty: IXLCustomProperty + { + XLWorkbook workbook; + public XLCustomProperty(XLWorkbook workbook) + { + this.workbook = workbook; + } + private String name; + public String Name + { + get + { + return name; + } + set + { + if (workbook.CustomProperties.Where(t => t.Name == value).Any()) + throw new ArgumentException(String.Format("This workbook already contains a custom property named '{0}'", value)); + + name = value; + } + } + public XLCustomPropertyType Type + { + get + { + Double dTest; + if (Value is DateTime) + return XLCustomPropertyType.Date; + else if (Value is Boolean) + return XLCustomPropertyType.Boolean; + else if (Double.TryParse(Value.ToString(), out dTest)) + return XLCustomPropertyType.Number; + else + return XLCustomPropertyType.Text; + } + } + public Object Value { get; set; } + public T GetValue() + { + return (T)Convert.ChangeType(Value, typeof(T)); + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs index 0798d6c..8799bed 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs @@ -20,11 +20,7 @@ /// Gets or sets the name (caption) of this worksheet. /// String Name { get; set; } - /// - /// Gets or sets the position of the sheet (zero based). - /// When setting the SheetIndex all other sheets' indexes are shifted accordingly. - /// - Int32 SheetIndex { get; set; } + /// /// Gets or sets the position of the sheet. /// When setting the Position all other sheets' positions are shifted accordingly. @@ -244,6 +240,7 @@ /// IXLSheetView SheetView { get; } + IXLTable Table(String name); IXLTables Tables { get; } IXLWorksheet CopyTo(String newSheetName); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheets.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheets.cs index 2c19286..910a62b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheets.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheets.cs @@ -8,10 +8,10 @@ public interface IXLWorksheets: IEnumerable { IXLWorksheet Worksheet(String sheetName); - IXLWorksheet Worksheet(Int32 sheetIndex); + IXLWorksheet Worksheet(Int32 position); IXLWorksheet Add(String sheetName); IXLWorksheet Add(String sheetName, Int32 position); void Delete(String sheetName); - void Delete(Int32 sheetIndex); + void Delete(Int32 position); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs index 752a7ae..3606e1e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -146,5 +146,13 @@ IXLRange AsRange(); IXLStyle Style { get; set; } + + /// + /// Gets or sets a value indicating whether this cell's text should be shared or not. + /// + /// + /// If false the cell's text will not be shared and stored as an inline value. + /// + Boolean ShareString { set; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index 0490307..a206dc9 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -850,5 +850,13 @@ { return this.Range(this.FirstCellUsed(), this.LastCellUsed()); } + + public Boolean ShareString + { + set + { + Cells().ForEach(c => c.ShareString = value); + } + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTables.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTables.cs index 8ab010a..7e00f81 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTables.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTables.cs @@ -8,7 +8,7 @@ public interface IXLTables: IEnumerable { void Add(IXLTable table); - IXLTable Table(Int32 index); + //IXLTable Table(Int32 index); IXLTable Table(String name); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs index 7025909..dc1f500 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs @@ -8,7 +8,6 @@ internal class XLTable : XLRange, IXLTable { public String RelId { get; set; } - public String Name { get; set; } public Boolean EmphasizeFirstColumn { get; set; } public Boolean EmphasizeLastColumn { get; set; } public Boolean ShowRowStripes { get; set; } @@ -16,6 +15,22 @@ public Boolean ShowAutoFilter { get; set; } public XLTableTheme Theme { get; set; } + private String name; + public String Name + { + get + { + return name; + } + set + { + if (Worksheet.Tables.Where(t => t.Name == value).Any()) + throw new ArgumentException(String.Format("This worksheet already contains a table named '{0}'", value)); + + name = value; + } + } + internal Boolean showTotalsRow; public Boolean ShowTotalsRow { @@ -53,8 +68,7 @@ if (!Worksheet.Tables.Where(t=>t.Name == tableName).Any()) { Name = tableName; - if (addToTables) - Worksheet.Tables.Add(this); + AddToTables(range, addToTables); break; } id++; @@ -74,10 +88,44 @@ InitializeValues(); this.Name = name; - if (addToTables) - Worksheet.Tables.Add(this); + AddToTables(range, addToTables); } + private void AddToTables(XLRange range, Boolean addToTables) + { + if (addToTables) + { + uniqueNames = new HashSet(); + Int32 co = 1; + foreach (var c in range.Row(1).Cells()) + { + if (StringExtensions.IsNullOrWhiteSpace(((XLCell)c).InnerText)) + c.Value = GetUniqueName("Column" + co.ToStringLookup()); + uniqueNames.Add(c.GetString()); + co++; + } + Worksheet.Tables.Add(this); + } + } + + HashSet uniqueNames; + private String GetUniqueName(String originalName) + { + String name = originalName; + if (uniqueNames.Contains(name)) + { + Int32 i = 1; + name = originalName + i.ToStringLookup(); + while (uniqueNames.Contains(name)) + { + i++; + name = originalName + i.ToStringLookup(); + } + } + + uniqueNames.Add(name); + return name; + } public IXLRangeRow HeadersRow() { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTables.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTables.cs index 17eddcb..78a7f79 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTables.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTables.cs @@ -7,10 +7,10 @@ { public class XLTables: IXLTables { - private List tables = new List(); + private Dictionary tables = new Dictionary(); public IEnumerator GetEnumerator() { - return tables.GetEnumerator(); + return tables.Values.GetEnumerator(); } System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() @@ -20,17 +20,17 @@ public void Add(IXLTable table) { - tables.Add(table); + tables.Add(table.Name, table); } - public IXLTable Table(Int32 index) - { - return tables[index]; - } + //public IXLTable Table(Int32 index) + //{ + // return tables[index]; + //} public IXLTable Table(String name) { - return tables.Where(t => t.Name == name).Single(); + return tables[name]; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLSheetView.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLSheetView.cs index 493ea72..a7524b3 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLSheetView.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLSheetView.cs @@ -7,6 +7,14 @@ { internal class XLSheetView: IXLSheetView { + public XLSheetView() { } + public XLSheetView(IXLSheetView sheetView) + { + this.SplitRow = sheetView.SplitRow; + this.SplitColumn = sheetView.SplitColumn; + this.FreezePanes = ((XLSheetView)sheetView).FreezePanes; + } + public Int32 SplitRow { get; set; } public Int32 SplitColumn { get; set; } public Boolean FreezePanes { get; set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs index b8e4b53..9ebc2da 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs @@ -23,6 +23,7 @@ DefaultColumnWidth = 9.140625; Worksheets = new XLWorksheets(this); NamedRanges = new XLNamedRanges(this); + CustomProperties = new XLCustomProperties(this); PopulateEnums(); Style = DefaultStyle; RowHeight = DefaultRowHeight; @@ -301,9 +302,43 @@ { return Worksheets.Worksheet(name); } - public IXLWorksheet Worksheet(Int32 sheetIndex) + public IXLWorksheet Worksheet(Int32 position) { - return Worksheets.Worksheet(sheetIndex); + return Worksheets.Worksheet(position); } + + private HashSet addedSharedStrings = new HashSet(); + public void AddSharedString(String value) + { + addedSharedStrings.Add(value); + } + + public HashSet GetSharedStrings() + { + HashSet modifiedStrings = new HashSet(); + addedSharedStrings.ForEach(s => modifiedStrings.Add(s)); + foreach (var w in Worksheets.Cast()) + { + foreach (var c in w.Internals.CellsCollection.Values) + { + if ( + c.DataType == XLCellValues.Text + && !StringExtensions.IsNullOrWhiteSpace(c.InnerText) + && !modifiedStrings.Contains(c.Value.ToString()) + ) + { + modifiedStrings.Add(c.Value.ToString()); + } + } + } + return modifiedStrings; + } + + public IXLCustomProperty CustomProperty(String name) + { + return CustomProperties.CustomProperty(name); + } + + public IXLCustomProperties CustomProperties { get; private set; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index 8d40c02..05ae786 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -6,6 +6,7 @@ using A = DocumentFormat.OpenXml.Drawing; using Xdr = DocumentFormat.OpenXml.Drawing.Spreadsheet; using C = DocumentFormat.OpenXml.Drawing.Charts; +using Op = DocumentFormat.OpenXml.CustomProperties; using System; using System.Collections.Generic; @@ -52,6 +53,23 @@ sharedStrings = shareStringPart.SharedStringTable.Elements().ToArray(); } + if (dSpreadsheet.WorkbookPart.GetPartsOfType().Count() > 0) + { + CustomFilePropertiesPart customFilePropertiesPart = dSpreadsheet.WorkbookPart.GetPartsOfType().First(); + foreach (Op.CustomDocumentProperty m in customFilePropertiesPart.Properties.Elements()) + { + String name = m.Name.Value; + if (m.VTLPWSTR != null) + CustomProperties.Add(name, m.VTLPWSTR.Text); + else if (m.VTFileTime != null) + CustomProperties.Add(name, DateTime.ParseExact(m.VTFileTime.Text, "yyyy'-'MM'-'dd'T'HH':'mm':'ss'Z'", CultureInfo.InvariantCulture)); + else if (m.VTDouble != null) + CustomProperties.Add(name, Double.Parse(m.VTDouble.Text, CultureInfo.InvariantCulture)); + else if (m.VTBool != null) + CustomProperties.Add(name, m.VTBool.Text == "true"); + } + } + var referenceMode = dSpreadsheet.WorkbookPart.Workbook.CalculationProperties.ReferenceMode; if (referenceMode != null) { @@ -82,7 +100,7 @@ foreach (var sheet in sheets) { - var sharedFormulas = new Dictionary(); + var sharedFormulasR1C1 = new Dictionary(); Sheet dSheet = ((Sheet)sheet); WorksheetPart worksheetPart = (WorksheetPart)dSpreadsheet.WorkbookPart.GetPartById(dSheet.Id); @@ -201,9 +219,6 @@ foreach (var cell in worksheetPart.Worksheet.Descendants()) { - if (cell.CellFormula != null && cell.CellFormula.SharedIndex != null && cell.CellFormula.Reference != null) - sharedFormulas.Add(cell.CellFormula.SharedIndex.Value, cell.CellFormula); - var dCell = (Cell)cell; Int32 styleIndex = dCell.StyleIndex != null ? Int32.Parse(dCell.StyleIndex.InnerText) : 0; var xlCell = (XLCell)ws.CellFast(dCell.CellReference); @@ -218,16 +233,27 @@ xlCell.Style = DefaultStyle; } - if (dCell.CellFormula != null) + if (cell.CellFormula != null && cell.CellFormula.SharedIndex != null && cell.CellFormula.Reference != null) + { + xlCell.FormulaA1 = cell.CellFormula.Text; + sharedFormulasR1C1.Add(cell.CellFormula.SharedIndex.Value, xlCell.FormulaR1C1); + } + else if (dCell.CellFormula != null) { if (dCell.CellFormula.SharedIndex != null) - xlCell.FormulaA1 = sharedFormulas[dCell.CellFormula.SharedIndex.Value].Text; + xlCell.FormulaR1C1 = sharedFormulasR1C1[dCell.CellFormula.SharedIndex.Value]; else xlCell.FormulaA1 = dCell.CellFormula.Text; } else if (dCell.DataType != null) { - if (dCell.DataType == CellValues.SharedString) + if (dCell.DataType == CellValues.InlineString) + { + xlCell.Value = dCell.InlineString.Text.Text; + xlCell.DataType = XLCellValues.Text; + xlCell.ShareString = false; + } + else if (dCell.DataType == CellValues.SharedString) { if (dCell.CellValue != null) { @@ -487,7 +513,7 @@ } else { - Worksheets.Worksheet(Int32.Parse(localSheetId)).NamedRanges.Add(name, text, comment); + Worksheet(Int32.Parse(localSheetId) + 1).NamedRanges.Add(name, text, comment); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 34c8138..5bb1e7b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -6,6 +6,7 @@ using A = DocumentFormat.OpenXml.Drawing; using Xdr = DocumentFormat.OpenXml.Drawing.Spreadsheet; using C = DocumentFormat.OpenXml.Drawing.Charts; +using Op = DocumentFormat.OpenXml.CustomProperties; using System; using System.Collections.Generic; @@ -15,9 +16,6 @@ using System.Globalization; using DRW = System.Drawing; - - - namespace ClosedXML.Excel { public partial class XLWorkbook @@ -107,14 +105,21 @@ public IXLStyle Style; }; - private CellValues GetCellValue(XLCellValues xlCellValue) + private CellValues GetCellValue(XLCell xlCell) { + var xlCellValue = xlCell.DataType; switch (xlCellValue) { case XLCellValues.Boolean: return CellValues.Boolean; case XLCellValues.DateTime: return CellValues.Date; case XLCellValues.Number: return CellValues.Number; - case XLCellValues.Text: return CellValues.SharedString; + case XLCellValues.Text: + { + if (xlCell.ShareString) + return CellValues.SharedString; + else + return CellValues.InlineString; + } case XLCellValues.TimeSpan: return CellValues.Number; default: throw new NotImplementedException(); } @@ -297,6 +302,7 @@ relId = new RelIdGenerator(); sharedStrings = new Dictionary(); sharedStyles = new Dictionary(); + tableNames = new HashSet(); tableId = 0; WorkbookPart workbookPart; @@ -346,7 +352,7 @@ GenerateWorkbookStylesPartContent(workbookStylesPart); - foreach (var worksheet in Worksheets.Cast().OrderBy(w=>w.SheetIndex)) + foreach (var worksheet in Worksheets.Cast().OrderBy(w=>w.Position)) { WorksheetPart worksheetPart; var sheets = workbookPart.Workbook.Sheets.Elements(); @@ -383,6 +389,13 @@ GenerateThemePartContent(themePart); } + if (CustomProperties.Count() > 0) + { + document.GetPartsOfType().ToList().ForEach(p => document.DeletePart(p)); + CustomFilePropertiesPart customFilePropertiesPart = document.AddNewPart(relId.GetNext(RelType.Workbook)); + + GenerateCustomFilePropertiesPartContent(customFilePropertiesPart); + } SetPackageProperties(document); } @@ -439,7 +452,7 @@ vTVector_Two = properties.TitlesOfParts.VTVector; - var modifiedWorksheets = Worksheets.Select(w => new { w.Name, Order = w.SheetIndex }); + var modifiedWorksheets = Worksheets.Select(w => new { w.Name, Order = w.Position }); var modifiedNamedRanges = GetModifiedNamedRanges(); InsertOnVTVector(vTVector_One, "Worksheets", 0, modifiedWorksheets.Count().ToString()); @@ -593,7 +606,7 @@ } } - foreach (var xlSheet in Worksheets.Cast().Where(w => w.SheetId == 0).OrderBy(w => w.SheetIndex)) + foreach (var xlSheet in Worksheets.Cast().Where(w => w.SheetId == 0).OrderBy(w => w.Position)) { String rId = relId.GetNext(RelType.Workbook); while (Worksheets.Cast().Where(w=>w.SheetId == Int32.Parse(rId.Substring(3))).Any()) @@ -606,7 +619,7 @@ var sheetElements = from sheet in workbook.Sheets.Elements() join worksheet in Worksheets.Cast() on sheet.Id.Value equals worksheet.RelId - orderby worksheet.SheetIndex + orderby worksheet.Position select sheet; foreach (var sheet in sheetElements) @@ -750,21 +763,7 @@ private void GenerateSharedStringTablePartContent(SharedStringTablePart sharedStringTablePart) { - HashSet modifiedStrings = new HashSet(); - foreach (var w in Worksheets.Cast()) - { - foreach (var c in w.Internals.CellsCollection.Values) - { - if ( - c.DataType == XLCellValues.Text - && !StringExtensions.IsNullOrWhiteSpace(c.InnerText) - && !modifiedStrings.Contains(c.Value.ToString()) - ) - { - modifiedStrings.Add(c.Value.ToString()); - } - } - } + HashSet modifiedStrings = GetSharedStrings(); sharedStringTablePart.SharedStringTable = new SharedStringTable() { Count = 0, UniqueCount = 0 }; @@ -1826,23 +1825,27 @@ if (opCell.DataType == XLCellValues.DateTime) cell.DataType = null; else - cell.DataType = GetCellValue(dataType); - + cell.DataType = GetCellValue(opCell); + CellValue cellValue = new CellValue(); if (dataType == XLCellValues.Text) { if (StringExtensions.IsNullOrWhiteSpace(opCell.InnerText)) { - //if (isNewCell) - cellValue = null; - //else - // cellValue.Text = String.Empty; + cell.CellValue = null; } else { - cellValue.Text = sharedStrings[opCell.InnerText].ToString(); + if (opCell.ShareString) + { + cellValue.Text = sharedStrings[opCell.InnerText].ToString(); + cell.CellValue = cellValue; + } + else + { + cell.InlineString = new InlineString() { Text = new Text(opCell.GetString()) }; + } } - cell.CellValue = cellValue; } else if (dataType == XLCellValues.TimeSpan) { @@ -2933,6 +2936,45 @@ themePart.Theme = theme1; } + private void GenerateCustomFilePropertiesPartContent(CustomFilePropertiesPart customFilePropertiesPart1) + { + Op.Properties properties2 = new Op.Properties(); + properties2.AddNamespaceDeclaration("vt", "http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"); + Int32 propertyId = 1; + foreach (var p in CustomProperties) + { + propertyId++; + Op.CustomDocumentProperty customDocumentProperty = new Op.CustomDocumentProperty() { FormatId = "{D5CDD505-2E9C-101B-9397-08002B2CF9AE}", PropertyId = propertyId, Name = p.Name }; + if (p.Type == XLCustomPropertyType.Text) + { + Vt.VTLPWSTR vTLPWSTR1 = new Vt.VTLPWSTR(); + vTLPWSTR1.Text = p.GetValue(); + customDocumentProperty.Append(vTLPWSTR1); + } + else if (p.Type == XLCustomPropertyType.Date) + { + Vt.VTFileTime vTFileTime1 = new Vt.VTFileTime(); + vTFileTime1.Text = p.GetValue().ToUniversalTime().ToString("yyyy'-'MM'-'dd'T'HH':'mm':'ss'Z'"); + customDocumentProperty.Append(vTFileTime1); + } + else if (p.Type == XLCustomPropertyType.Number) + { + Vt.VTDouble vTDouble1 = new Vt.VTDouble(); + vTDouble1.Text = p.GetValue().ToString(CultureInfo.InvariantCulture); + customDocumentProperty.Append(vTDouble1); + } + else + { + Vt.VTBool vTBool1 = new Vt.VTBool(); + vTBool1.Text = p.GetValue().ToString().ToLower(); + customDocumentProperty.Append(vTBool1); + } + properties2.Append(customDocumentProperty); + } + + customFilePropertiesPart1.Properties = properties2; + } + private void SetPackageProperties(OpenXmlPackage document) { var created = Properties.Created == DateTime.MinValue ? DateTime.Now : Properties.Created; @@ -2951,16 +2993,36 @@ } UInt32 tableId = 0; + HashSet tableNames; + private String GetTableName(String originalTableName) + { + String tableName = originalTableName.RemoveSpecialCharacters(); + String name = tableName; + if (tableNames.Contains(name)) + { + Int32 i = 1; + name = tableName + i.ToStringLookup(); + while (tableNames.Contains(name)) + { + i++; + name = tableName + i.ToStringLookup(); + } + } + + tableNames.Add(name); + return name; + } + private void GenerateTableDefinitionPartContent(TableDefinitionPart tableDefinitionPart, XLTable xlTable) { tableId++; String reference; reference = xlTable.RangeAddress.FirstAddress.ToString() + ":" + xlTable.RangeAddress.LastAddress.ToString(); - + String tableName = GetTableName(xlTable.Name); Table table = new Table() { Id = tableId, - Name = xlTable.Name.RemoveSpecialCharacters(), - DisplayName = xlTable.Name, + Name = tableName, + DisplayName = tableName, Reference = reference }; if (xlTable.ShowTotalsRow) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index 52e41fb..86cd60b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -154,38 +154,28 @@ public Int32 SheetId { get; set; } public String RelId { get; set; } - internal Int32 sheetIndex; - public Int32 SheetIndex + internal Int32 position; + public Int32 Position { get { - return sheetIndex; + return position; } set { - if (value > workbook.Worksheets.Count()) - throw new IndexOutOfRangeException("Index must be equal or less than the number of worksheets."); + if (value > workbook.Worksheets.Count() + 1) + throw new IndexOutOfRangeException("Index must be equal or less than the number of worksheets + 1."); - if (value < sheetIndex) - workbook.Worksheets.Where(w => w.SheetIndex >= value && w.SheetIndex < sheetIndex).ForEach(w => ((XLWorksheet)w).sheetIndex += 1); + if (value < position) + workbook.Worksheets.Where(w => ((XLWorksheet)w).Position >= value && ((XLWorksheet)w).Position < position).ForEach(w => ((XLWorksheet)w).position += 1); - if (value > sheetIndex) - workbook.Worksheets.Where(w => w.SheetIndex <= value && w.SheetIndex > sheetIndex).ForEach(w => ((XLWorksheet)w).sheetIndex -= 1); + if (value > position) + workbook.Worksheets.Where(w => ((XLWorksheet)w).Position <= value && ((XLWorksheet)w).Position > position).ForEach(w => ((XLWorksheet)w).position -= 1); - sheetIndex = value; + position = value; } } - public Int32 Position - { - get - { - return sheetIndex + 1; - } - set - { - SheetIndex = value - 1; - } - } + public IXLPageSetup PageSetup { get; private set; } public IXLOutline Outline { get; private set; } @@ -511,6 +501,10 @@ public IXLNamedRanges NamedRanges { get; private set; } public IXLSheetView SheetView { get; private set; } public IXLTables Tables { get; private set; } + public IXLTable Table(String name) + { + return Tables.Table(name); + } public IXLWorksheet CopyTo(String newSheetName) { @@ -534,6 +528,11 @@ this.Internals.ColumnsCollection.ForEach(kp => ws.Internals.ColumnsCollection.Add(kp)); this.Internals.MergedRanges.ForEach(kp => ws.Internals.MergedRanges.Add(kp)); this.Internals.RowsCollection.ForEach(kp => ws.Internals.RowsCollection.Add(kp)); + this.PageSetup = new XLPageSetup(this.PageSetup, ws); + this.Outline = new XLOutline(this.Outline); + this.SheetView = new XLSheetView(this.SheetView); + this.NamedRanges.ForEach(r => ws.NamedRanges.Add(r.Name, r.Ranges)); + this.Tables.ForEach(t => ws.Tables.Add(t)); return ws; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs index a67a02f..041efab 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs @@ -7,7 +7,7 @@ { internal class XLWorksheets : IXLWorksheets { - Dictionary worksheets = new Dictionary(); + Dictionary worksheets = new Dictionary(); public HashSet Deleted = new HashSet(); XLWorkbook workbook; public XLWorksheets(XLWorkbook workbook) @@ -22,23 +22,23 @@ return worksheets[sheetName]; } - public IXLWorksheet Worksheet(Int32 sheetIndex) + public IXLWorksheet Worksheet(Int32 position) { - var wsCount = worksheets.Values.Where(w => w.SheetIndex == sheetIndex).Count(); + var wsCount = worksheets.Values.Where(w => w.Position == position).Count(); if (wsCount == 0) - throw new Exception("There isn't a worksheet associated with that index."); + throw new Exception("There isn't a worksheet associated with that position."); if (wsCount > 1) - throw new Exception("Can't retrieve a worksheet because there are multiple worksheets associated with that index."); + throw new Exception("Can't retrieve a worksheet because there are multiple worksheets associated with that position."); - return worksheets.Values.Where(w => w.SheetIndex == sheetIndex).Single(); + return worksheets.Values.Where(w => w.Position == position).Single(); } public IXLWorksheet Add(String sheetName) { var sheet = new XLWorksheet(sheetName, workbook); worksheets.Add(sheetName, sheet); - sheet.sheetIndex = worksheets.Count - 1; + sheet.position = worksheets.Count; return sheet; } @@ -51,24 +51,24 @@ public void Delete(String sheetName) { - Delete(worksheets[sheetName].SheetIndex); + Delete(worksheets[sheetName].Position); } - public void Delete(Int32 sheetIndex) + public void Delete(Int32 position) { - var wsCount = worksheets.Values.Where(w => w.SheetIndex == sheetIndex).Count(); + var wsCount = worksheets.Values.Where(w => w.Position == position).Count(); if (wsCount == 0) throw new Exception("There isn't a worksheet associated with that index."); if (wsCount > 1) throw new Exception("Can't delete the worksheet because there are multiple worksheets associated with that index."); - var ws = (XLWorksheet)worksheets.Values.Where(w => w.SheetIndex == sheetIndex).Single(); + var ws = (XLWorksheet)worksheets.Values.Where(w => w.Position == position).Single(); if (!StringExtensions.IsNullOrWhiteSpace(ws.RelId) && !Deleted.Contains(ws.RelId)) Deleted.Add(ws.RelId); - worksheets.RemoveAll(w => w.SheetIndex == sheetIndex); - worksheets.Values.Where(w => w.SheetIndex > sheetIndex).ForEach(w => ((XLWorksheet)w).sheetIndex -= 1); + worksheets.RemoveAll(w => w.Position == position); + worksheets.Values.Where(w => w.Position > position).ForEach(w => ((XLWorksheet)w).position -= 1); } #endregion @@ -77,7 +77,10 @@ public IEnumerator GetEnumerator() { - return worksheets.Values.GetEnumerator(); + foreach (var w in worksheets.Values) + { + yield return (IXLWorksheet)w; + } } #endregion diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj index 20d97b6..4cb632d 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -58,6 +58,8 @@ + + diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs index c86f49d..c299a00 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs @@ -66,6 +66,8 @@ new UsingTables().Create(@"C:\Excel Files\Created\UsingTables.xlsx"); new ShowCase().Create(@"C:\Excel Files\Created\ShowCase.xlsx"); new CopyingWorksheets().Create(); + new InsertingTables().Create(@"C:\Excel Files\Created\InsertingTables.xlsx"); + new InsertingData().Create(@"C:\Excel Files\Created\InsertingData.xlsx"); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/ChangingBasicTable.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/ChangingBasicTable.cs index 836416d..e22e7f6 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/ChangingBasicTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/ChangingBasicTable.cs @@ -13,7 +13,7 @@ public void Create() { var workbook = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); - var ws = workbook.Worksheets.Worksheet(0); + var ws = workbook.Worksheet(1); // Change the background color of the headers var rngHeaders = ws.Range("B3:F3"); diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/CopyingWorksheets.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/CopyingWorksheets.cs index 10767ae..2fd3603 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/CopyingWorksheets.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/CopyingWorksheets.cs @@ -46,15 +46,15 @@ // Public public void Create() { - var wb = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); - var wsSource = wb.Worksheet(0); + var wb = new XLWorkbook(@"C:\Excel Files\Created\UsingTables.xlsx"); + var wsSource = wb.Worksheet(1); // Copy the worksheet to a new sheet in this workbook wsSource.CopyTo("Copy"); // We're going to open another workbook to show that you can // copy a sheet from one workbook to another: var wbSource = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); - wbSource.Worksheet(0).CopyTo(wb, "Copy From Other"); + wbSource.Worksheet(1).CopyTo(wb, "Copy From Other"); // Save the workbook with the 2 copies wb.SaveAs(@"C:\Excel Files\Created\CopyingWorksheets.xlsx"); diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataTypes.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataTypes.cs index 9b942a7..12946f8 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataTypes.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataTypes.cs @@ -156,6 +156,22 @@ ws.Cell(ro, co + 1).DataType = XLCellValues.Text; ws.Cell(ro, co + 1).Value = ""; + ro++; + + // Using inline strings (few users will ever need to use this feature) + // + // By default all strings are stored as shared so one block of text + // can be reference by multiple cells. + // You can override this by setting the .ShareString property to false + ws.Cell(++ro, co).Value = "Inline String:"; + var cell = ws.Cell(ro, co + 1); + cell.Value = "Not Shared"; + cell.ShareString = false; + // You can also add your own strings to the workbook's shared strings pool: + // workbook.AddSharedString("custom shared string"); + // To view all shared strings (all texts in the workbook actually), use the following: + // workbook.GetSharedStrings() + ws.Columns(2, 3).AdjustToContents(); workbook.SaveAs(filePath); diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/InsertingData.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/InsertingData.cs new file mode 100644 index 0000000..def80d7 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/InsertingData.cs @@ -0,0 +1,103 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; + +using System.Drawing; +using System.Data; + +namespace ClosedXML_Examples.Misc +{ + public class InsertingData + { + #region Methods + + // Public + public void Create(String filePath) + { + var wb = new XLWorkbook(); + var ws = wb.Worksheets.Add("Inserting Data"); + + // From a list of strings + var listOfStrings = new List(); + listOfStrings.Add("House"); + listOfStrings.Add("Car"); + ws.Cell(1, 1).Value = "From Strings"; + ws.Cell(1, 1).AsRange().AddToNamed("Titles"); + ws.Cell(2, 1).InsertData(listOfStrings); + + // From a list of arrays + var listOfArr = new List(); + listOfArr.Add(new Int32[] { 1, 2, 3 }); + listOfArr.Add(new Int32[] { 1 }); + listOfArr.Add(new Int32[] { 1, 2, 3, 4, 5, 6 }); + ws.Cell(1, 3).Value = "From Arrays"; + ws.Range(1, 3, 1, 8).Merge().AddToNamed("Titles"); + ws.Cell(2, 3).InsertData(listOfArr); + + // From a DataTable + var dataTable = GetTable(); + ws.Cell(6, 1).Value = "From DataTable"; + ws.Range(6, 1, 6, 4).Merge().AddToNamed("Titles"); + ws.Cell(7, 1).InsertData(dataTable.AsEnumerable()); + + // From a query + var list = new List(); + list.Add(new Person() { Name = "John", Age = 30, House = "On Elm St." }); + list.Add(new Person() { Name = "Mary", Age = 15, House = "On Main St." }); + list.Add(new Person() { Name = "Luis", Age = 21, House = "On 23rd St." }); + list.Add(new Person() { Name = "Henry", Age = 45, House = "On 5th Ave." }); + + var people = from p in list + where p.Age >= 21 + select new { p.Name, p.House, p.Age }; + + ws.Cell(6, 6).Value = "From Query"; + ws.Range(6, 6, 6, 8).Merge().AddToNamed("Titles"); + ws.Cell(7, 6).InsertData(people.AsEnumerable()); + + // Prepare the style for the titles + var titlesStyle = wb.Style; + titlesStyle.Font.Bold = true; + titlesStyle.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; + titlesStyle.Fill.BackgroundColor = XLColor.Cyan; + + // Format all titles in one shot + wb.NamedRanges.NamedRange("Titles").Ranges.Style = titlesStyle; + + ws.Columns().AdjustToContents(); + + wb.SaveAs(filePath); + } + + class Person + { + public String House { get; set; } + public String Name { get; set; } + public Int32 Age { get; set; } + } + + // Private + private DataTable GetTable() + { + + DataTable table = new DataTable(); + table.Columns.Add("Dosage", typeof(int)); + table.Columns.Add("Drug", typeof(string)); + table.Columns.Add("Patient", typeof(string)); + table.Columns.Add("Date", typeof(DateTime)); + + table.Rows.Add(25, "Indocin", "David", DateTime.Now); + table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now); + table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now); + table.Rows.Add(21, "Combivent", "Janet", DateTime.Now); + table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now); + return table; + } + // Override + + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/InsertingTables.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/InsertingTables.cs new file mode 100644 index 0000000..d72aa34 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/InsertingTables.cs @@ -0,0 +1,103 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; + +using System.Drawing; +using System.Data; + +namespace ClosedXML_Examples.Misc +{ + public class InsertingTables + { + #region Methods + + // Public + public void Create(String filePath) + { + var wb = new XLWorkbook(); + var ws = wb.Worksheets.Add("Inserting Tables"); + + // From a list of strings + var listOfStrings = new List(); + listOfStrings.Add("House"); + listOfStrings.Add("Car"); + ws.Cell(1, 1).Value = "From Strings"; + ws.Cell(1, 1).AsRange().AddToNamed("Titles"); + ws.Cell(2, 1).InsertTable(listOfStrings); + + // From a list of arrays + var listOfArr = new List(); + listOfArr.Add(new Int32[] { 1, 2, 3 }); + listOfArr.Add(new Int32[] { 1 }); + listOfArr.Add(new Int32[] { 1, 2, 3, 4, 5, 6 }); + ws.Cell(1, 3).Value = "From Arrays"; + ws.Range(1, 3, 1, 8).Merge().AddToNamed("Titles"); + ws.Cell(2, 3).InsertTable(listOfArr); + + // From a DataTable + var dataTable = GetTable(); + ws.Cell(7, 1).Value = "From DataTable"; + ws.Range(7, 1, 7, 4).Merge().AddToNamed("Titles"); + ws.Cell(8, 1).InsertTable(dataTable.AsEnumerable()); + + // From a query + var list = new List(); + list.Add(new Person() { Name = "John", Age = 30, House = "On Elm St." }); + list.Add(new Person() { Name = "Mary", Age = 15, House = "On Main St." }); + list.Add(new Person() { Name = "Luis", Age = 21, House = "On 23rd St." }); + list.Add(new Person() { Name = "Henry", Age = 45, House = "On 5th Ave." }); + + var people = from p in list + where p.Age >= 21 + select new { p.Name, p.House, p.Age }; + + ws.Cell(7, 6).Value = "From Query"; + ws.Range(7, 6, 7, 8).Merge().AddToNamed("Titles"); + ws.Cell(8, 6).InsertTable(people.AsEnumerable()); + + // Prepare the style for the titles + var titlesStyle = wb.Style; + titlesStyle.Font.Bold = true; + titlesStyle.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; + titlesStyle.Fill.BackgroundColor = XLColor.Cyan; + + // Format all titles in one shot + wb.NamedRanges.NamedRange("Titles").Ranges.Style = titlesStyle; + + ws.Columns().AdjustToContents(); + + wb.SaveAs(filePath); + } + + class Person + { + public String House { get; set; } + public String Name { get; set; } + public Int32 Age { get; set; } + } + + // Private + private DataTable GetTable() + { + + DataTable table = new DataTable(); + table.Columns.Add("Dosage", typeof(int)); + table.Columns.Add("Drug", typeof(string)); + table.Columns.Add("Patient", typeof(string)); + table.Columns.Add("Date", typeof(DateTime)); + + table.Rows.Add(25, "Indocin", "David", DateTime.Now); + table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now); + table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now); + table.Rows.Add(21, "Combivent", "Janet", DateTime.Now); + table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now); + return table; + } + // Override + + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/LambdaExpressions.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/LambdaExpressions.cs index 7d6446b..ff7d14d 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/LambdaExpressions.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/LambdaExpressions.cs @@ -13,7 +13,7 @@ public void Create() { var workbook = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); - var ws = workbook.Worksheets.Worksheet(0); + var ws = workbook.Worksheet(1); // Define a range with the data var firstDataCell = ws.Cell("B4"); diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MergeMoves.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MergeMoves.cs index 7e57a18..bea1df4 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MergeMoves.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MergeMoves.cs @@ -57,7 +57,7 @@ public void Create() { var workbook = new XLWorkbook(@"C:\Excel Files\Created\MergedCells.xlsx"); - var ws = workbook.Worksheets.Worksheet(0); + var ws = workbook.Worksheet(1); ws.Range("B1:F1").InsertRowsBelow(1); ws.Range("A3:A9").InsertColumnsAfter(1); diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MultipleSheets.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MultipleSheets.cs index 949490b..bd8ea6a 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MultipleSheets.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MultipleSheets.cs @@ -46,27 +46,20 @@ // Public public void Create() { - var wb = new XLWorkbook(@"C:\Excel Files\Created\MultipleSheets.xlsx"); - var ws = wb.Worksheets.Add("NewOne"); - wb.Worksheets.Worksheet(0).Delete(); - ws.SheetIndex = 0; - wb.Worksheets.Worksheet("Inserted").SheetIndex = wb.Worksheets.Count(); - wb.SaveAs(@"C:\Excel Files\Created\MultipleSheets_Saved.xlsx"); - - wb = new XLWorkbook(); - foreach (var wsNum in Enumerable.Range(0, 5)) + var wb = new XLWorkbook(); + foreach (var wsNum in Enumerable.Range(1, 5)) { wb.Worksheets.Add("Original Pos. is " + wsNum.ToString()); } // Move first worksheet to the last position - wb.Worksheets.Worksheet(0).SheetIndex = wb.Worksheets.Count(); + wb.Worksheet(1).Position = wb.Worksheets.Count() + 1; - // Delete worksheet on position 2 (in this case it's where original position = 3) - wb.Worksheets.Worksheet(2).Delete(); + // Delete worksheet on position 4 (in this case it's where original position = 5) + wb.Worksheet(4).Delete(); - // Swap sheets in positions 0 and 1 - wb.Worksheets.Worksheet(1).SheetIndex = 0; + // Swap sheets in positions 1 and 2 + wb.Worksheet(2).Position = 1; wb.SaveAs(@"C:\Excel Files\Created\OrganizingSheets.xlsx"); } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/WorkbookProperties.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/WorkbookProperties.cs index 7477139..c0c1805 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/WorkbookProperties.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/WorkbookProperties.cs @@ -60,6 +60,12 @@ wb.Properties.Company = "theCompany"; wb.Properties.Manager = "theManager"; + // Creating/Using custom properties + wb.CustomProperties.Add("theText", "XXX"); + wb.CustomProperties.Add("theDate", new DateTime(2011, 1, 1)); + wb.CustomProperties.Add("theNumber", 123.456); + wb.CustomProperties.Add("theBoolean", true); + wb.SaveAs(filePath); } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/CopyingRanges.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/CopyingRanges.cs index 89b1962..5147fc5 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/CopyingRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/CopyingRanges.cs @@ -13,7 +13,7 @@ public void Create() { var workbook = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); - var ws = workbook.Worksheets.Worksheet(0); + var ws = workbook.Worksheet(1); // Define a range with the data var firstTableCell = ws.FirstCellUsed(); diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/ShiftingRanges.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/ShiftingRanges.cs index 3a5ed6f..29be11a 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/ShiftingRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/ShiftingRanges.cs @@ -12,7 +12,7 @@ public void Create() { var workbook = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); - var ws = workbook.Worksheets.Worksheet(0); + var ws = workbook.Worksheet(1); // Get a range object var rngHeaders = ws.Range("B3:F3"); diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRanges.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRanges.cs index 0b9b32b..d84eeb4 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRanges.cs @@ -13,7 +13,7 @@ public void Create() { var workbook = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); - var ws = workbook.Worksheets.Worksheet(0); + var ws = workbook.Worksheet(1); var rngTable = ws.Range("B2:F6"); diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRangesPlus.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRangesPlus.cs index 704d99d..3a2bf6e 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRangesPlus.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRangesPlus.cs @@ -13,7 +13,7 @@ public void Create() { var workbook = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); - var ws = workbook.Worksheets.Worksheet(0); + var ws = workbook.Worksheet(1); var rngTable = ws.Range("B2:F6"); diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/UsingTables.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/UsingTables.cs index 6817e0c..3373640 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/UsingTables.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/UsingTables.cs @@ -16,7 +16,7 @@ public void Create(String filePath) { var wb = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); - var ws = wb.Worksheets.Worksheet(0); + var ws = wb.Worksheet(1); var firstCell = ws.FirstCellUsed(); var lastCell = ws.LastCellUsed(); var range = ws.Range(firstCell.Address, lastCell.Address); diff --git a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj index d6f6410..85731d1 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj @@ -82,6 +82,18 @@ Excel\Columns\XLColumns.cs + + Excel\CustomProperties\IXLCustomProperties.cs + + + Excel\CustomProperties\IXLCustomProperty.cs + + + Excel\CustomProperties\XLCustomProperties.cs + + + Excel\CustomProperties\XLCustomProperty.cs + Excel\IXLAddress.cs diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj b/ClosedXML/ClosedXML/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj index 8b1cfcd..858a71f 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj @@ -62,6 +62,10 @@ {BD5E6BFE-E837-4A35-BCA9-39667D873A20} ClosedXML + + {03A518D0-1CB7-488E-861C-C4E782B27A46} + ClosedXML_Examples +