diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRange.cs index 653fc81..eaca72a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRange.cs @@ -215,7 +215,6 @@ cellsToDelete.ForEach(c => range.CellsCollection.Remove(c)); cellsToInsert.ForEach(c => range.CellsCollection.Add(c.Key, c.Value)); } - public static void InsertColumnsBefore(this IXLRange range, Int32 numberOfColumns) { var cellsToInsert = new Dictionary(); @@ -240,6 +239,16 @@ cellsToDelete.ForEach(c => range.CellsCollection.Remove(c)); cellsToInsert.ForEach(c => range.CellsCollection.Add(c.Key, c.Value)); } + + public static List Columns(this IXLRange range) + { + var retVal = new List(); + foreach (var r in Enumerable.Range(1, range.RowCount())) + { + retVal.Add(range.Row(r)); + } + return retVal; + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs index f6d88b4..fdfe9e9 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs @@ -15,5 +15,7 @@ new IXLColumn Column(String column); String Name { get; set; } + + List Columns(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index a11dc9a..d526c6a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -62,6 +62,19 @@ public Int32 ColumnNumber { get; private set; } public String ColumnLetter { get; private set; } + public List Columns() + { + var retVal = new List(); + var usedColumns = Enumerable.Range(CellsCollection.Keys.Min(k => k.Column), CellsCollection.Keys.Max(k => k.Column)); + var columnList = usedColumns.ToList(); + columnList.AddRange(ColumnsCollection.Keys.Where(k => !usedColumns.Contains(k)).ToList()); + foreach (var c in columnList) + { + retVal.Add(Column(c)); + } + return retVal; + } + #endregion #region IXLStylized Members diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/ColumnSettings.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/ColumnSettings.cs index 28f04d6..118f462 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/ColumnSettings.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/ColumnSettings.cs @@ -63,11 +63,13 @@ 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; + var col1 = ws.Column("B"); + col1.Style.Fill.BackgroundColor = Color.Red; + col1.Width = 20; + + var col2 = ws.Column(4); + col2.Style.Fill.BackgroundColor = Color.DarkOrange; + col2.Width = 5; workbook.SaveAs(filePath); } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MergeCells.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MergeCells.cs index a9b82db..bdec928 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MergeCells.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/MergeCells.cs @@ -59,14 +59,23 @@ var workbook = new XLWorkbook(); var ws = workbook.Worksheets.Add("Merge Cells"); - ws.Cell("B2").Value = "Merged Cells (B2 - D2)"; - ws.Range("B2:D2").Merge(); + // Merge a row + ws.Cell("B2").Value = "Merged Row(1) of Range (B2:D3)"; + ws.Range("B2:D3").Row(1).Merge(); - ws.Cell("B4").Value = "Merged Cells (B4 - D6)"; + // Merge a column + ws.Cell("F2").Value = "Merged Column(1) of Range (F2:G8)"; + ws.Cell("F2").Style.Alignment.WrapText = true; + ws.Range("F2:G8").Column(1).Merge(); + + + // Merge a range + ws.Cell("B4").Value = "Merged Range (B4:D6)"; ws.Cell("B4").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("B4").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range("B4:D6").Merge(); + // Unmerging a range... ws.Cell("B8").Value = "Unmerged"; ws.Range("B8:D8").Merge(); ws.Range("B8:D8").Unmerge(); diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Rows/RowSettings.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Rows/RowSettings.cs index 5f839a5..22d0e1f 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Rows/RowSettings.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Rows/RowSettings.cs @@ -63,11 +63,13 @@ 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; + var row1 = ws.Row(2); + row1.Style.Fill.BackgroundColor = Color.Red; + row1.Height = 30; + + var row2 = ws.Row(4); + row2.Style.Fill.BackgroundColor = Color.DarkOrange; + row2.Height = 3; workbook.SaveAs(filePath); } diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj b/ClosedXML/ClosedXML/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj index ff83f9e..db1a722 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj @@ -40,6 +40,7 @@ + diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index 3c49e4e..b0ff84b 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -4,6 +4,7 @@ using System.Text; using ClosedXML.Excel; using ClosedXML.Excel.Style; +using System.Drawing; namespace ClosedXML_Sandbox { @@ -13,10 +14,63 @@ { var wb = new XLWorkbook(); var ws = wb.Worksheets.Add("New Sheet"); - ws.Cell(2, 2).Value = "Hello!"; - ws.Cell(2, 2).Style.Font.Bold = true; + + foreach (var c in ws.Range("B2:C3").Columns()) + { + c.Style.Fill.BackgroundColor = Color.Red; + } + + ws.Cell("E1").Value = "Wide 2"; + + foreach (var c in ws.Columns()) + { + c.Width = 20; + } + + + + //foreach (var r in ws.Rows()) + //{ + // r.Height = 15; + //} + + //foreach (var r in ws.Range("B2:C3").Rows()) + //{ + // r.Style.Fill.BackgroundColor = Color.Red; + //} + + //ws.Columns("A:B").Width = 20; + //ws.Columns("3:4").Width = 20; + //ws.Rows("1:2").Height = 30; + + //ws.Columns("A:B").Style.Fill.BackgroundColor = Color.Red; + //ws.Columns("3:4").Style.Fill.BackgroundColor = Color.Blue; + //ws.Rows("1:2").Style.Fill.BackgroundColor = Color.Orange; + + //var rng1 = ws.Range("B2:E5"); + //rng1.Columns("A:B").Style.Fill.BackgroundColor = Color.Red; + //rng1.Columns("3:4").Style.Fill.BackgroundColor = Color.Blue; + //rng1.Rows("1:2").Style.Fill.BackgroundColor = Color.Orange; + + //ws.Row(2).Delete(); + //ws.Column(2).Delete(); + //ws.Column("B").Delete(); + + //ws.Columns("A:B").Delete(); + //ws.Columns("3:4").Delete(); + //ws.Rows("1:2").Delete(); + + //ws.Range("B2:C3").Delete(ShiftCellsUp); + //ws.Range("B2:C3").Delete(ShiftCellsLeft); + + //ws.Range("B2:C3").Column(1).Delete(ShiftCellsUp); + //ws.Range("B2:C3").Column("A").Delete(ShiftCellsLeft); + //ws.Range("B2:C3").Row(1).Delete(ShiftCellsUp); + //ws.Range("B2:C3").Row((1).Delete(ShiftCellsLeft); + wb.SaveAs(@"c:\Sandbox.xlsx"); //Console.ReadKey(); + } } }