diff --git a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj index 183c99e..664ecd9 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj @@ -36,10 +36,6 @@ - - False - ..\..\..\OX.Copyable\OX.Copyable\bin\Release\OX.Copyable.dll - @@ -51,6 +47,7 @@ + diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLColumn.cs index cde3796..f869cba 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLColumn.cs @@ -7,6 +7,6 @@ { public interface IXLColumn: IXLRange { - Int32 Width { get; set; } + Double Width { get; set; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRow.cs new file mode 100644 index 0000000..30fb541 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRow.cs @@ -0,0 +1,12 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public interface IXLRow: IXLRange + { + Double Height { get; set; } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorkbook.cs index e4b3074..6d61534 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorkbook.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorkbook.cs @@ -12,7 +12,6 @@ String Name { get; } String FullName { get; } void SaveAs(String file, Boolean overwrite = false); - //IXLStyle DefaultStyle { get; set; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs index 8b4274a..f6d88b4 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs @@ -7,6 +7,10 @@ { public interface IXLWorksheet: IXLRange { + Dictionary ColumnsCollection { get; } + Dictionary RowsCollection { get; } + + new IXLRow Row(Int32 column); new IXLColumn Column(Int32 column); new IXLColumn Column(String column); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLCell.cs index 7f56f30..290db6c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLCell.cs @@ -17,7 +17,7 @@ public Boolean Initialized { get; private set; } public IXLAddress Address { get; private set; } - private String cellValue; + private String cellValue = String.Empty; public String Value { get diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLColumn.cs index 33092d2..0be5d8f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLColumn.cs @@ -13,9 +13,11 @@ FirstCellAddress = new XLAddress(1, column); LastCellAddress = new XLAddress(XLWorksheet.MaxNumberOfRows, column); CellsCollection = cellsCollection; + this.style = new XLStyle(this, defaultStyle); + this.Width = XLWorkbook.DefaultColumnWidth; } - public Int32 Width { get; set; } + public Double Width { get; set; } #region IXLRange Members diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLRow.cs index 4433232..1d5b2dc 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLRow.cs @@ -6,27 +6,18 @@ namespace ClosedXML.Excel { - public class XLRow: IXLRange + public class XLRow: IXLRow { public XLRow(Int32 row, Dictionary cellsCollection, IXLStyle defaultStyle) { FirstCellAddress = new XLAddress(row, 1); LastCellAddress = new XLAddress(row, XLWorksheet.MaxNumberOfColumns); CellsCollection = cellsCollection; - - var defaultAddress = new XLAddress(row, 0); - if (!cellsCollection.ContainsKey(defaultAddress)) - { - DefaultCell = new XLCell(defaultAddress, defaultStyle); - cellsCollection.Add(defaultAddress, DefaultCell); - } - else - { - DefaultCell = cellsCollection[defaultAddress]; - } + this.style = new XLStyle(this, defaultStyle); + this.Height = XLWorkbook.DefaultRowHeight; } - private IXLCell DefaultCell { get; set; } + public Double Height { get; set; } #region IXLRange Members @@ -38,15 +29,16 @@ #region IXLStylized Members + private IXLStyle style; public IXLStyle Style { get { - return DefaultCell.Style; + return style; } set { - DefaultCell.Style = value; + style = new XLStyle(this, value); } } @@ -55,6 +47,7 @@ get { UpdatingStyle = true; + yield return style; foreach (var c in CellsCollection.Values.Where(c => c.Address.Row == FirstCellAddress.Row)) { yield return c.Style; @@ -67,7 +60,6 @@ #endregion - #region IXLRange Members public IXLRange Row(int row) @@ -83,8 +75,7 @@ public IXLRange Column(string column) { - var address = new XLAddress(FirstCellAddress.Row, column); - return this.Range(address, address); + return Column(Int32.Parse(column)); } #endregion diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs index aa41ba6..2e90a8c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs @@ -12,6 +12,8 @@ { public XLWorkbook() { + DefaultRowHeight = 15; + DefaultColumnWidth = 9.140625; Worksheets = new XLWorksheets(); } #region IXLWorkbook Members @@ -107,6 +109,9 @@ } } + public static Double DefaultRowHeight { get; set; } + public static Double DefaultColumnWidth { get; set; } + #endregion } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 7c0e3ba..c4a4bad 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -350,56 +350,59 @@ UInt32 fillCount = 3; UInt32 borderCount = 1; Int32 numberFormatCount = 1; + var xlStyles = new List(); + foreach (var worksheet in Worksheets) { - foreach (var cell in worksheet.CellsCollection.Values) + xlStyles.AddRange(worksheet.Styles); + worksheet.ColumnsCollection.Values.ForEach(c => xlStyles.Add(c.Style)); + worksheet.RowsCollection.Values.ForEach(c => xlStyles.Add(c.Style)); + } + + + + foreach (var xlStyle in xlStyles) + { + if (!sharedFonts.ContainsKey(xlStyle.Font.ToString())) { - if (!sharedFonts.ContainsKey(cell.Style.Font.ToString())) - { - sharedFonts.Add(cell.Style.Font.ToString(), new FontInfo() { FontId = fontCount++, Font = cell.Style.Font }); - } + sharedFonts.Add(xlStyle.Font.ToString(), new FontInfo() { FontId = fontCount++, Font = xlStyle.Font }); + } - if (!sharedFills.ContainsKey(cell.Style.Fill.ToString())) - { - sharedFills.Add(cell.Style.Fill.ToString(), new FillInfo() { FillId = fillCount++, Fill = cell.Style.Fill }); - } + if (!sharedFills.ContainsKey(xlStyle.Fill.ToString())) + { + sharedFills.Add(xlStyle.Fill.ToString(), new FillInfo() { FillId = fillCount++, Fill = xlStyle.Fill }); + } - if (!sharedBorders.ContainsKey(cell.Style.Border.ToString())) - { - sharedBorders.Add(cell.Style.Border.ToString(), new BorderInfo() { BorderId = borderCount++, Border = cell.Style.Border }); - } + if (!sharedBorders.ContainsKey(xlStyle.Border.ToString())) + { + sharedBorders.Add(xlStyle.Border.ToString(), new BorderInfo() { BorderId = borderCount++, Border = xlStyle.Border }); + } - if (cell.Style.NumberFormat.NumberFormatId == -1 && !sharedNumberFormats.ContainsKey(cell.Style.NumberFormat.ToString())) - { - sharedNumberFormats.Add(cell.Style.NumberFormat.ToString(), new NumberFormatInfo() { NumberFormatId = numberFormatCount + 164, NumberFormat = cell.Style.NumberFormat }); - //cell.Style.NumberFormat = new OPNumberFormat(numberFormatCount); - numberFormatCount++; - } - //else if (!cell.Style.NumberFormat.NumberFormatId.HasValue) - //{ - // cell.Style.NumberFormat = new OPNumberFormat(sharedNumberFormats[cell.Style.NumberFormat.ToString()].NumberFormatId); - //} + if (xlStyle.NumberFormat.NumberFormatId == -1 && !sharedNumberFormats.ContainsKey(xlStyle.NumberFormat.ToString())) + { + sharedNumberFormats.Add(xlStyle.NumberFormat.ToString(), new NumberFormatInfo() { NumberFormatId = numberFormatCount + 164, NumberFormat = xlStyle.NumberFormat }); + numberFormatCount++; + } - if (!sharedStyles.ContainsKey(cell.Style.ToString())) - { - Int32 numberFormatId; - if (cell.Style.NumberFormat.NumberFormatId >= 0) - numberFormatId = cell.Style.NumberFormat.NumberFormatId; - else - numberFormatId = sharedNumberFormats[cell.Style.NumberFormat.ToString()].NumberFormatId; + if (!sharedStyles.ContainsKey(xlStyle.ToString())) + { + Int32 numberFormatId; + if (xlStyle.NumberFormat.NumberFormatId >= 0) + numberFormatId = xlStyle.NumberFormat.NumberFormatId; + else + numberFormatId = sharedNumberFormats[xlStyle.NumberFormat.ToString()].NumberFormatId; - sharedStyles.Add(cell.Style.ToString(), - new StyleInfo() - { - StyleId = styleCount++, - Style = cell.Style, - FontId = sharedFonts[cell.Style.Font.ToString()].FontId, - FillId = sharedFills[cell.Style.Fill.ToString()].FillId, - BorderId = sharedBorders[cell.Style.Border.ToString()].BorderId, - NumberFormatId = 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, + NumberFormatId = numberFormatId + }); } } @@ -571,6 +574,8 @@ workbookStylesPart.Stylesheet = stylesheet1; } + + private void GenerateWorksheetPartContent(WorksheetPart worksheetPart, IXLWorksheet xlWorksheet) { Worksheet worksheet = new Worksheet(); @@ -599,35 +604,44 @@ SheetFormatProperties sheetFormatProperties3 = new SheetFormatProperties() { DefaultRowHeight = 15D }; Columns columns = null; - //if (xlWorksheet.Columns.Count > 0) - //{ - // columns = new Columns(); - // foreach (var opColumn in xlWorksheet.Columns.Values) - // { - // Column column = new Column() - // { - // Min = (UInt32Value)opColumn.Column, - // Max = (UInt32Value)opColumn.Column, - // Width = opColumn.Width, - // CustomWidth = true - // }; - // columns.Append(column); - // } - //} + if (xlWorksheet.ColumnsCollection.Count > 0) + { + columns = new Columns(); + foreach (var xlColumn in xlWorksheet.ColumnsCollection.Values) + { + Column column = new Column() + { + Min = (UInt32Value)(UInt32)xlColumn.FirstCellAddress.Column, + Max = (UInt32Value)(UInt32)xlColumn.FirstCellAddress.Column, + Style = sharedStyles[xlColumn.Style.ToString()].StyleId, + Width = xlColumn.Width, + CustomWidth = true + }; + + columns.Append(column); + } + } SheetData sheetData = new SheetData(); - var distinctRows = xlWorksheet.CellsCollection.Where(c => c.Key.Column > 0 && c.Key.Row > 0).Select(c => c.Key.Row).Distinct(); + var rowsFromCells = xlWorksheet.CellsCollection.Where(c => c.Key.Column > 0 && c.Key.Row > 0).Select(c => c.Key.Row).Distinct(); + var rowsFromCollection = xlWorksheet.RowsCollection.Keys; + var allRows = rowsFromCells.ToList(); + allRows.AddRange(rowsFromCollection); + var distinctRows = allRows.Distinct(); foreach (var distinctRow in distinctRows.OrderBy(r => r)) { Row row = new Row() { RowIndex = (UInt32Value)(UInt32)distinctRow, Spans = new ListValue() { InnerText = "1:" + maxColumn.ToString() } }; - //if (xlWorksheet.Rows.ContainsKey(distinctRow)) - //{ - // row.Height = xlWorksheet.Rows[distinctRow].Height; - // row.CustomHeight = true; - //} + if (xlWorksheet.RowsCollection.ContainsKey(distinctRow)) + { + var thisRow = xlWorksheet.RowsCollection[distinctRow]; + row.Height = thisRow.Height; + row.CustomHeight = true; + row.StyleIndex = sharedStyles[thisRow.Style.ToString()].StyleId; + row.CustomFormat = true; + } foreach (var opCell in xlWorksheet.CellsCollection - .Where(c => c.Key.Row == distinctRow && c.Key.Column != 0) + .Where(c => c.Key.Row == distinctRow) .OrderBy(c => c.Key) .Select(c => c)) { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index f03de68..37a4f7e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -16,12 +16,15 @@ #endregion Dictionary cellsCollection = new Dictionary(); + Dictionary rowsCollection = new Dictionary(); + Dictionary columnsCollection = new Dictionary(); public XLWorksheet(String sheetName) { var defaultAddress = new XLAddress(0,0); DefaultCell = new XLCell(defaultAddress, XLWorkbook.DefaultStyle); cellsCollection.Add(defaultAddress, DefaultCell); + var tmp = this.Cell(1, 1).Value; this.Name = sheetName; } @@ -31,6 +34,14 @@ { get { return cellsCollection; } } + public Dictionary ColumnsCollection + { + get { return columnsCollection; } + } + public Dictionary RowsCollection + { + get { return rowsCollection; } + } public IXLAddress FirstCellAddress { @@ -77,29 +88,55 @@ #endregion - public IXLRange Row(Int32 row) + public IXLRow Row(Int32 row) { - return new XLRow(row, cellsCollection, Style); + IXLRow xlRow; + if (rowsCollection.ContainsKey(row)) + { + xlRow = rowsCollection[row]; + } + else + { + xlRow = new XLRow(row, cellsCollection, Style); + rowsCollection.Add(row, xlRow); + } + + return xlRow; } public IXLColumn Column(Int32 column) { - return new XLColumn(column, cellsCollection, Style); + IXLColumn xlColumn; + if (columnsCollection.ContainsKey(column)) + { + xlColumn = columnsCollection[column]; + } + else + { + xlColumn = new XLColumn(column, cellsCollection, Style); + columnsCollection.Add(column, xlColumn); + } + + return xlColumn; } public IXLColumn Column(String column) { - return new XLColumn(XLAddress.GetColumnNumberFromLetter(column), cellsCollection, Style); + return Column(XLAddress.GetColumnNumberFromLetter(column)); } #region IXLRange Members - + IXLRange IXLRange.Row(Int32 row) + { + var firstCellAddress = new XLAddress(row, 1); + var lastCellAddress = new XLAddress(row, MaxNumberOfColumns); + return this.Range(firstCellAddress, lastCellAddress); + } IXLRange IXLRange.Column(int column) { IXLAddress firstCellAddress = new XLAddress(1, column); IXLAddress lastCellAddress = new XLAddress(MaxNumberOfRows, column); return this.Range(firstCellAddress, lastCellAddress); } - IXLRange IXLRange.Column(string column) { IXLAddress firstCellAddress = new XLAddress(1, column); diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/BasicTable.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/BasicTable.cs index 566c7e8..ee837df 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/BasicTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/BasicTable.cs @@ -52,11 +52,17 @@ var rngNumbers = ws.Range("E2:E4"); //Formatting dates and numbers - rngDates.Style.NumberFormat.Format = "mm-dd-yyyy"; + //Using a OpenXML's predefined formats + rngDates.Style.NumberFormat.NumberFormatId = 15; // .Format = "mm-dd-yyyy"; + //Using a custom format rngNumbers.Style.NumberFormat.Format = "$ #,##0"; + // Adjust column width + ws.Column("D").Width = 12; + //Formatting headers var rngHeaders = ws.Range("A1:E1"); + rngHeaders.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; rngHeaders.Style.Font.Bold = true; rngHeaders.Style.Fill.BackgroundColor = Color.Aqua; diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj index 90514ac..cc8d034 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -49,9 +49,11 @@ + + diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/ColumnSettings.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/ColumnSettings.cs new file mode 100644 index 0000000..28f04d6 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/ColumnSettings.cs @@ -0,0 +1,82 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; +using ClosedXML.Excel.Style; +using System.Drawing; + +namespace ClosedXML_Examples.Columns +{ + public class ColumnSettings + { + #region Variables + + // Public + + // Private + + + #endregion + + #region Properties + + // Public + + // Private + + // Override + + + #endregion + + #region Constructors + + // Public + public ColumnSettings() + { + + } + + + // Private + + + #endregion + + #region Events + + // Public + + // Private + + // Override + + + #endregion + + #region Methods + + // Public + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.Worksheets.Add("Column Settings"); + + ws.Cell("B4").Style.Fill.BackgroundColor = Color.Brown; + ws.Column("B").Style.Fill.BackgroundColor = Color.Red; + ws.Cell("B2").Style.Fill.BackgroundColor = Color.Blue; + ws.Column("B").Width = 15; + ws.Column(4).Style.Fill.BackgroundColor = Color.DarkOrange; + + workbook.SaveAs(filePath); + } + + // Private + + // Override + + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Program.cs index d9d209b..048a34e 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Program.cs @@ -3,6 +3,8 @@ using System.Linq; using System.Text; using ClosedXML_Examples.Styles; +using ClosedXML_Examples.Columns; +using ClosedXML_Examples.Rows; namespace ClosedXML_Examples { @@ -13,6 +15,8 @@ new HelloWorld().Create(@"c:\HelloWorld.xlsx"); new BasicTable().Create(@"c:\BasicTable.xlsx"); new StyleExamples().Create(); + new ColumnSettings().Create(@"c:\ColumnSettings.xlsx"); + new RowSettings().Create(@"c:\RowSettings.xlsx"); } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Rows/RowSettings.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Rows/RowSettings.cs new file mode 100644 index 0000000..5f839a5 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Rows/RowSettings.cs @@ -0,0 +1,82 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; +using ClosedXML.Excel.Style; +using System.Drawing; + +namespace ClosedXML_Examples.Rows +{ + public class RowSettings + { + #region Variables + + // Public + + // Private + + + #endregion + + #region Properties + + // Public + + // Private + + // Override + + + #endregion + + #region Constructors + + // Public + public RowSettings() + { + + } + + + // Private + + + #endregion + + #region Events + + // Public + + // Private + + // Override + + + #endregion + + #region Methods + + // Public + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.Worksheets.Add("Row Settings"); + + ws.Cell("D2").Style.Fill.BackgroundColor = Color.Brown; + ws.Row(2).Style.Fill.BackgroundColor = Color.Red; + ws.Cell("B2").Style.Fill.BackgroundColor = Color.Blue; + ws.Row(2).Height = 30; + ws.Row(4).Style.Fill.BackgroundColor = Color.DarkOrange; + + workbook.SaveAs(filePath); + } + + // Private + + // Override + + + #endregion + } +}