diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs index 7eaaf5d..ae96970 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs @@ -38,6 +38,8 @@ /// XLCellValues DataType { get; set; } + IXLCell SetDataType(XLCellValues dataType); + IXLCell SetValue(T value); /// diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCells.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCells.cs index e368d19..b6b843c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCells.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCells.cs @@ -30,6 +30,8 @@ /// XLCellValues DataType { set; } + IXLCells SetDataType(XLCellValues dataType); + /// /// Clears the contents of these cells (including styles). /// diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index 00bb80c..15d39f5 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -679,6 +679,12 @@ #endregion + public IXLCell SetDataType(XLCellValues dataType) + { + DataType = dataType; + return this; + } + internal XLCellValues dataType; public XLCellValues DataType { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs index f42b38f..d7da5b0 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs @@ -171,6 +171,12 @@ } } + public IXLCells SetDataType(XLCellValues dataType) + { + this.ForEach(c => c.DataType = dataType); + return this; + } + public XLCellValues DataType { set diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs index fa1190f..25c60da 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs @@ -173,5 +173,7 @@ /// Adds a vertical page break after this column. /// IXLColumn AddVerticalPageBreak(); + + IXLColumn SetDataType(XLCellValues dataType); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs index e90a926..b30e5ae 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs @@ -112,6 +112,8 @@ /// /// Adds a vertical page break after these columns. /// - IXLColumns AddVerticalPageBreaks(); + IXLColumns AddVerticalPageBreaks(); + + IXLColumns SetDataType(XLCellValues dataType); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs index 677a53b..d19b4be 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs @@ -669,5 +669,11 @@ Worksheet.PageSetup.AddVerticalPageBreak(this.ColumnNumber()); return this; } + + public IXLColumn SetDataType(XLCellValues dataType) + { + DataType = dataType; + return this; + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs index afbf817..c0f44e9 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs @@ -256,6 +256,12 @@ foreach (var col in columns) col.Worksheet.PageSetup.AddVerticalPageBreak(col.ColumnNumber()); return this; - } + } + + public IXLColumns SetDataType(XLCellValues dataType) + { + columns.ForEach(c => c.DataType = dataType); + return this; + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs index 760e580..f4ec080 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs @@ -240,6 +240,7 @@ IXLRange Sort(XLSortOrientation sortOrientation, XLSortOrder sortOrder, Boolean matchCase); IXLRange Sort(XLSortOrientation sortOrientation, String elementsToSortBy, Boolean matchCase); + IXLRange SetDataType(XLCellValues dataType); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs index 096d50d..40be9ed 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs @@ -91,6 +91,8 @@ IXLRangeColumn Column(Int32 start, Int32 end); IXLRangeColumns Columns(String columns); + + IXLRangeColumn SetDataType(XLCellValues dataType); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs index a5ad6a0..74cd7a9 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs @@ -41,7 +41,7 @@ IXLStyle Style { get; set; } - + IXLRangeColumns SetDataType(XLCellValues dataType); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs index 86ddb4d..f91346c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs @@ -99,6 +99,8 @@ IXLRangeRow Row(Int32 start, Int32 end); IXLRangeRows Rows(String rows); + + IXLRangeRow SetDataType(XLCellValues dataType); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs index 388f011..1d7807b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs @@ -40,6 +40,6 @@ IXLStyle Style { get; set; } - + IXLRangeRows SetDataType(XLCellValues dataType); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs index 34682f0..095641b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs @@ -83,5 +83,6 @@ /// if set to true will return all cells with a value or a style different than the default. IXLCells CellsUsed(Boolean includeStyles); + IXLRanges SetDataType(XLCellValues dataType); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs index a649692..f1e02a4 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs @@ -952,6 +952,10 @@ lastColumnNumber); } - + public IXLRange SetDataType(XLCellValues dataType) + { + DataType = dataType; + return this; + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs index 1433bb4..28fe738 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs @@ -245,6 +245,12 @@ } return retVal; } + + public IXLRangeColumn SetDataType(XLCellValues dataType) + { + DataType = dataType; + return this; + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs index 8350066..2fd57d5 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs @@ -132,7 +132,11 @@ } } - + public IXLRangeColumns SetDataType(XLCellValues dataType) + { + ranges.ForEach(c => c.DataType = dataType); + return this; + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs index 527b46e..6a69d45 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs @@ -260,6 +260,12 @@ } return retVal; } + + public IXLRangeRow SetDataType(XLCellValues dataType) + { + DataType = dataType; + return this; + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs index c7142ff..d9a4156 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs @@ -131,6 +131,10 @@ } } - + public IXLRangeRows SetDataType(XLCellValues dataType) + { + ranges.ForEach(c => c.DataType = dataType); + return this; + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs index d9d9f7e..cb847e3 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs @@ -260,5 +260,11 @@ } return (IXLCells)cells; } + + public IXLRanges SetDataType(XLCellValues dataType) + { + ranges.ForEach(c => c.DataType = dataType); + return this; + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs index 196ecad..46a181e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs @@ -176,5 +176,7 @@ /// Adds a horizontal page break after this row. /// IXLRow AddHorizontalPageBreak(); + + IXLRow SetDataType(XLCellValues dataType); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs index 9c08ff5..5430247 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs @@ -113,5 +113,7 @@ /// Adds a horizontal page break after these rows. /// IXLRows AddHorizontalPageBreaks(); + + IXLRows SetDataType(XLCellValues dataType); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs index 063d4c5..cca78d3 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs @@ -708,5 +708,11 @@ Worksheet.PageSetup.AddHorizontalPageBreak(this.RowNumber()); return this; } + + public IXLRow SetDataType(XLCellValues dataType) + { + DataType = dataType; + return this; + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs index b3565c1..52edcc1 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs @@ -254,6 +254,12 @@ row.Worksheet.PageSetup.AddHorizontalPageBreak(row.RowNumber()); } return this; - } + } + + public IXLRows SetDataType(XLCellValues dataType) + { + rows.ForEach(c => c.DataType = dataType); + return this; + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs index 2d82e89..0e90d82 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs @@ -363,14 +363,15 @@ } #endregion #region Methods + private String trimmedAddress; public string GetTrimmedAddress() { - return ColumnLetter + m_rowNumber.ToStringLookup(); + return trimmedAddress ?? (trimmedAddress = ColumnLetter + m_rowNumber.ToStringLookup()); } public string ToStringRelative() { - return ColumnLetter + m_rowNumber.ToStringLookup(); + return GetTrimmedAddress(); } public string ToStringFixed() diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 8abc8cf..ece3ceb 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -70,22 +70,27 @@ public IXLStyle Style; }; - private CellValues GetCellValue(XLCell xlCell) + private readonly EnumValue cvSharedString = new EnumValue(CellValues.SharedString); + private readonly EnumValue cvInlineString = new EnumValue(CellValues.InlineString); + private readonly EnumValue cvNumber = new EnumValue(CellValues.Number); + private readonly EnumValue cvDate = new EnumValue(CellValues.Date); + private readonly EnumValue cvBoolean = new EnumValue(CellValues.Boolean); + + private EnumValue GetCellValue(XLCell xlCell) { - var xlCellValue = xlCell.DataType; - switch (xlCellValue) + switch (xlCell.DataType) { - case XLCellValues.Boolean: return CellValues.Boolean; - case XLCellValues.DateTime: return CellValues.Date; - case XLCellValues.Number: return CellValues.Number; case XLCellValues.Text: { if (xlCell.ShareString) - return CellValues.SharedString; + return cvSharedString; else - return CellValues.InlineString; + return cvInlineString; } - case XLCellValues.TimeSpan: return CellValues.Number; + case XLCellValues.Number: return cvNumber; + case XLCellValues.DateTime: return cvDate; + case XLCellValues.Boolean: return cvBoolean; + case XLCellValues.TimeSpan: return cvNumber; default: throw new NotImplementedException(); } } @@ -705,14 +710,26 @@ UInt32 fillCount = 3; UInt32 borderCount = 1; Int32 numberFormatCount = 1; - var xlStyles = new List(); + var xlStyles = new HashSet(); foreach (var worksheet in Worksheets.Cast()) { - xlStyles.AddRange(worksheet.Styles); - worksheet.Internals.ColumnsCollection.Values.ForEach(c => xlStyles.Add(c.Style)); - worksheet.Internals.RowsCollection.Values.ForEach(c => xlStyles.Add(c.Style)); + foreach (var s in worksheet.Styles) + if (!xlStyles.Contains(s)) + xlStyles.Add(s); + + foreach (var s in worksheet.Internals.ColumnsCollection.Select(kp=>kp.Value.Style)) + if (!xlStyles.Contains(s)) + xlStyles.Add(s); + + foreach (var s in worksheet.Internals.RowsCollection.Select(kp => kp.Value.Style)) + if (!xlStyles.Contains(s)) + xlStyles.Add(s); + + //xlStyles.AddRange(worksheet.Styles); + //worksheet.Internals.ColumnsCollection.Values.ForEach(c => xlStyles.Add(c.Style)); + //worksheet.Internals.RowsCollection.Values.ForEach(c => xlStyles.Add(c.Style)); } @@ -1778,6 +1795,7 @@ if (cellsByRow.ContainsKey(distinctRow)) { var cellsByReference = row.Elements().ToDictionary(c => c.CellReference.Value, c => c); + Boolean isNewRow = !row.Elements().Any(); foreach (var opCell in cellsByRow[distinctRow] .OrderBy(c => c.Address.ColumnNumber) .Select(c => (XLCell)c)) @@ -1797,8 +1815,8 @@ else { //isNewCell = true; - cell = new Cell() { CellReference = cellReference }; - if (!row.Elements().Any()) + cell = new Cell() { CellReference = new StringValue(cellReference) }; + if (isNewRow) { row.Append(cell); } diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index 2106cac..111f9ae 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -15,12 +15,12 @@ static void xMain(string[] args) { //var fileName = "DataValidation"; - //var fileName = "Sandbox"; - var fileName = "Issue_0000"; + var fileName = "Sandbox"; + //var fileName = "Issue_0000"; //var wb = new XLWorkbook(String.Format(@"c:\Excel Files\ForTesting\{0}.xlsx", fileName)); - //var wb = new XLWorkbook(); - //var ws = wb.Worksheets.Add("SheetX"); - + var wb = new XLWorkbook(); + var ws = wb.Worksheets.Add("SheetX"); + ws.Cell(1, 1).Value = "1 234"; //ws.Cell("A1").Value = "Category"; //ws.Cell("A2").Value = "A"; @@ -37,16 +37,16 @@ //Now, when i use workbook.Worksheet("CCR").Range("B1:C34").RangeUsed(). //The expect is B1:C29. - //wb.SaveAs(String.Format(@"c:\Excel Files\ForTesting\{0}_Saved.xlsx", fileName)); + wb.SaveAs(String.Format(@"c:\Excel Files\ForTesting\{0}_Saved.xlsx", fileName)); - var start = DateTime.Now; - var wb = new XLWorkbook(@"C:\Excel Files\ForTesting\Issue_0000.xlsx"); - var end = DateTime.Now; - Console.WriteLine(String.Format("Opened file in {0} seconds", (end - start).TotalSeconds)); - var ws = wb.Worksheet(1); - var cell = ws.Cell(100000, 13); - Console.WriteLine(cell.GetString()); + //var start = DateTime.Now; + //var wb = new XLWorkbook(@"C:\Excel Files\ForTesting\Issue_0000.xlsx"); + //var end = DateTime.Now; + //Console.WriteLine(String.Format("Opened file in {0} seconds", (end - start).TotalSeconds)); + //var ws = wb.Worksheet(1); + //var cell = ws.Cell(100000, 13); + //Console.WriteLine(cell.GetString()); Console.ReadKey(); }