diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeInternals.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeInternals.cs deleted file mode 100644 index e73d246..0000000 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeInternals.cs +++ /dev/null @@ -1,14 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; - -namespace ClosedXML.Excel -{ - public interface IXLRangeInternals - { - IXLAddress FirstCellAddress { get; } - IXLAddress LastCellAddress { get; } - IXLWorksheet Worksheet { get; } - } -} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeInternals.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeInternals.cs deleted file mode 100644 index 1bd9cc7..0000000 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeInternals.cs +++ /dev/null @@ -1,20 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; - -namespace ClosedXML.Excel -{ - public class XLRangeInternals: IXLRangeInternals - { - public XLRangeInternals(IXLAddress firstCellAddress, IXLAddress lastCellAddress, IXLWorksheet worksheet) - { - FirstCellAddress = firstCellAddress; - LastCellAddress = lastCellAddress; - Worksheet = worksheet; - } - public IXLAddress FirstCellAddress { get; private set; } - public IXLAddress LastCellAddress { get; private set; } - public IXLWorksheet Worksheet { get; private set; } - } -} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj index 98c9ea9..5b56173 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -51,6 +51,7 @@ + @@ -60,8 +61,12 @@ + + + + @@ -73,6 +78,8 @@ + + diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/ColumnCollections.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/ColumnCollections.cs index 0caa4e3..6defc18 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/ColumnCollections.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/ColumnCollections.cs @@ -73,10 +73,9 @@ // Use a single number/letter bigRange.Columns("V").Style.Fill.BackgroundColor = Color.Cyan; - // Only the used columns in a worksheet + // Adjust the width ws.Columns("A:V").Width = 3; - var ws2 = workbook.Worksheets.Add("Columns of a worksheet"); // Contiguous columns by number @@ -97,6 +96,7 @@ // Use a single number/letter ws2.Columns("V").Style.Fill.BackgroundColor = Color.Cyan; + // Adjust the width ws2.Columns("A:V").Width = 3; workbook.SaveAs(filePath); diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/DeletingColumns.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/DeletingColumns.cs new file mode 100644 index 0000000..bdf55eb --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/DeletingColumns.cs @@ -0,0 +1,80 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; + +using System.Drawing; + +namespace ClosedXML_Examples +{ + public class DeletingColumns + { + #region Variables + + // Public + + // Private + + + #endregion + + #region Properties + + // Public + + // Private + + // Override + + + #endregion + + #region Events + + // Public + + // Private + + // Override + + + #endregion + + #region Methods + + // Public + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.Worksheets.Add("Deleting Columns"); + + var rngTitles = ws.Range("B2:D2"); + ws.Row(1).InsertRowsBelow(2); + Console.Write(rngTitles.ToString()); // Prints "B4:D4 + Console.ReadKey(); + + var rng1 = ws.Range("B2:D2"); + var rng2 = ws.Range("F2:G2"); + var rng3 = ws.Range("A1:A3"); + var col1 = ws.Column(1); + + // rng1 will have 2 columns starting at A2 + ws.Columns("A,C,E:H").Delete(); + + rng1.Style.Fill.BackgroundColor = Color.Orange; + rng2.Style.Fill.BackgroundColor = Color.Blue; + //rng3.Style.Fill.BackgroundColor = Color.Red; + //col1.Style.Fill.BackgroundColor = Color.Red; + + workbook.SaveAs(filePath); + } + + // Private + + // Override + + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/InsertColumns.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/InsertColumns.cs index 8015ee5..f926103 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/InsertColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/InsertColumns.cs @@ -47,21 +47,32 @@ public void Create(String filePath) { var workbook = new XLWorkbook(); - var ws = workbook.Worksheets.Add("Insert Columns"); + var ws = workbook.Worksheets.Add("Inserting Columns"); + // Color the entire spreadsheet using columns + ws.Columns().Style.Fill.BackgroundColor = Color.LightCyan; + + // Put a value in a few cells foreach (var r in Enumerable.Range(1, 5)) - { foreach (var c in Enumerable.Range(1, 5)) - { ws.Cell(r, c).Value = "X"; - ws.Cell(r, c).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; - } - } - ws.Column(3).InsertColumnsAfter(2); - ws.Column(1).InsertColumnsBefore(2); - ws.Range("D3:E4").InsertColumnsAfter(2); - ws.Range("B3:C5").InsertColumnsBefore(2); + + var blueColumn = ws.Column(2); + var redColumn = ws.Column(5); + + blueColumn.Style.Fill.BackgroundColor = Color.Blue; + blueColumn.InsertColumnsAfter(2); + + + redColumn.Style.Fill.BackgroundColor = Color.Red; + redColumn.InsertColumnsBefore(2); + + ws.Rows(3, 4).Style.Fill.BackgroundColor = Color.Orange; + ws.Range("B1:D1").InsertColumnsAfter(2); + ws.Range("B2:D2").InsertColumnsBefore(2); + ws.Range("B3:D3").InsertColumnsAfter(2); + ws.Range("B4:D4").InsertColumnsBefore(2); workbook.SaveAs(filePath); } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs index c1cf1c9..357aa8e 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs @@ -18,6 +18,8 @@ new HelloWorld().Create(@"C:\Excel Files\Created\HelloWorld.xlsx"); new BasicTable().Create(@"C:\Excel Files\Created\BasicTable.xlsx"); new StyleExamples().Create(); + new ChangingBasicTable().Create(); + new MovingRanges().Create(); new ColumnSettings().Create(@"C:\Excel Files\Created\ColumnSettings.xlsx"); new RowSettings().Create(@"C:\Excel Files\Created\RowSettings.xlsx"); new MergeCells().Create(@"C:\Excel Files\Created\MergedCells.xlsx"); @@ -35,7 +37,12 @@ new HeaderFooters().Create(@"C:\Excel Files\Created\HeaderFooters.xlsx"); new Sheets().Create(@"C:\Excel Files\Created\Sheets.xlsx"); new SheetTab().Create(@"C:\Excel Files\Created\SheetTab.xlsx"); - new ChangingBasicTable().Create(); + new MultipleRanges().Create(@"C:\Excel Files\Created\MultipleRanges.xlsx"); + new StyleWorksheet().Create(@"C:\Excel Files\Created\StyleWorksheet.xlsx"); + new StyleRowsColumns().Create(@"C:\Excel Files\Created\StyleRowsColumns.xlsx"); + new InsertingDeletingRows().Create(@"C:\Excel Files\Created\InsertingDeletingRows.xlsx"); + new InsertingDeletingColumns().Create(@"C:\Excel Files\Created\InsertingDeletingColumns.xlsx"); + //new DeletingColumns().Create(@"C:\Excel Files\Created\DeletingColumns.xlsx"); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/ChangingBasicTable.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/ChangingBasicTable.cs index 018b3fd..3b6a883 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(); - workbook.Load(@"C:\BasicTable.xlsx"); + workbook.Load(@"C:\Excel Files\Created\BasicTable.xlsx"); var ws = workbook.Worksheets.GetWorksheet(0); // Change the background color of the headers @@ -22,7 +22,7 @@ // Change the date formats var rngDates = ws.Range("E4:E6"); - rngDates.Style.NumberFormat.Format = "MM/dd/yyyy"; + rngDates.Style.DateFormat.Format = "MM/dd/yyyy"; // Change the income values to text var rngNumbers = ws.Range("F4:F6"); @@ -32,7 +32,7 @@ cell.Value += " Dollars"; } - workbook.SaveAs(@"C:\BasicTable_Modified.xlsx"); + workbook.SaveAs(@"C:\Excel Files\Modified\BasicTable_Modified.xlsx"); } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/LoadFiles.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/LoadFiles.cs index 4c73461..c829bd5 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/LoadFiles.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/LoadFiles.cs @@ -11,7 +11,7 @@ { public static void LoadAllFiles() { - var forLoadingFolder = @"C:\Excel Files\ForLoading"; + var forLoadingFolder = @"C:\Excel Files\Created"; var forSavingFolder = @"C:\Excel Files\Modified"; foreach (var file in Directory.GetFiles(forLoadingFolder)) @@ -21,7 +21,7 @@ LoadAndSaveFile(forLoadingFolder + @"\" + fileName, forSavingFolder + @"\" + fileName); } - //LoadAndSaveFile(forLoadingFolder + @"\HeaderFooters.xlsx", forSavingFolder + @"\HeaderFooters.xlsx"); + //LoadAndSaveFile(forLoadingFolder + @"\StyleWorksheet.xlsx", forSavingFolder + @"\StyleWorksheet.xlsx"); //LoadAndSaveFile(forLoadingFolder + "DataTypes.xlsx", forSavingFolder + "DataTypes.xlsx"); //LoadAndSaveFile(forLoadingFolder + "MultipleSheets.xlsx", forSavingFolder + "MultipleSheets.xlsx"); //LoadAndSaveFile(forLoadingFolder + "styleNumberFormat.xlsx", forSavingFolder + "styleNumberFormat.xlsx"); diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataTypes.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataTypes.cs index 6757f09..8df2eb2 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataTypes.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataTypes.cs @@ -112,7 +112,7 @@ ws.Cell(++ro, co).Value = "Formatted Date to Text:"; ws.Cell(ro, co + 1).Value = new DateTime(2010, 9, 2).ToString(); - ws.Cell(ro, co + 1).Style.NumberFormat.Format = "yyyy-MM-dd"; + ws.Cell(ro, co + 1).Style.DateFormat.Format = "yyyy-MM-dd"; ws.Cell(ro, co + 1).DataType = XLCellValues.Text; ws.Cell(++ro, co).Value = "Formatted Number to Text:"; diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/PageSetup/HeaderFooters.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/PageSetup/HeaderFooters.cs index 73e467d..e63a636 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/PageSetup/HeaderFooters.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/PageSetup/HeaderFooters.cs @@ -22,10 +22,21 @@ ws.PageSetup.Header.Left.AddText("Created with ClosedXML"); // Using various fonts for the right header on the first page only - ws.PageSetup.Header.Right.AddText("The ", XLHFOccurrence.FirstPage, new XLFont() { Bold = true }); - ws.PageSetup.Header.Right.AddText("First ", XLHFOccurrence.FirstPage, new XLFont() { FontColor = Color.Red }); - ws.PageSetup.Header.Right.AddText("Colorful ", XLHFOccurrence.FirstPage, new XLFont() { Underline = XLFontUnderlineValues.Double}); - ws.PageSetup.Header.Right.AddText("Page", XLHFOccurrence.FirstPage, new XLFont() { FontName = "Broadway" }); + var font1 = XLWorkbook.GetXLFont(); + font1.Bold = true; + ws.PageSetup.Header.Right.AddText("The ", XLHFOccurrence.FirstPage, font1); + + var font2 = XLWorkbook.GetXLFont(); + font2.FontColor = Color.Red; + ws.PageSetup.Header.Right.AddText("First ", XLHFOccurrence.FirstPage, font2); + + var font3 = XLWorkbook.GetXLFont(); + font3.Underline = XLFontUnderlineValues.Double; + ws.PageSetup.Header.Right.AddText("Colorful ", XLHFOccurrence.FirstPage, font3); + + var font4 = XLWorkbook.GetXLFont(); + font4.FontName = "Broadway"; + ws.PageSetup.Header.Right.AddText("Page", XLHFOccurrence.FirstPage, font4); // Using predefined header/footer text: diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/ClearingRanges.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/ClearingRanges.cs index 29c1c94..bb205c1 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/ClearingRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/ClearingRanges.cs @@ -16,7 +16,7 @@ public void Create(String filePath) { var workbook = new XLWorkbook(); - var ws = workbook.Worksheets.Add("Deleting a Range"); + var ws = workbook.Worksheets.Add("Clearing Ranges"); foreach (var ro in Enumerable.Range(1, 10)) { foreach (var co in Enumerable.Range(1, 10)) diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/InsertingDeletingColumns.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/InsertingDeletingColumns.cs new file mode 100644 index 0000000..9df1d52 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/InsertingDeletingColumns.cs @@ -0,0 +1,59 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; +using System.Drawing; + +namespace ClosedXML_Examples +{ + public class InsertingDeletingColumns + { + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.Worksheets.Add("Inserting and Deleting Columns"); + + // Range starts with 2 columns + var rng = ws.Range("B2:C3"); // Range starts on B2 + + // Insert a column before the range + ws.Column(1).InsertColumnsAfter(1); // Range starts on C2 + + // Insert a column in between the range + ws.Column(3).InsertColumnsAfter(1); // Range now has 3 columns + + // Insert a column (from a range) before the range + ws.Range("A2:A3").InsertColumnsAfter(1); // Range starts on D2 + + // Insert a column (from a range) in between the range + ws.Range("D2:D3").InsertColumnsAfter(1); // Range now has 4 columns + + // Inserting columns from a range not covering all columns + // does not affect our defined range + ws.Range("A1:A2").InsertColumnsAfter(1); + ws.Range("E3:E4").InsertColumnsAfter(1); + + // Delete a column before the range + ws.Column(1).Delete(); // Range starts on C2 + + // Delete a column (from a range) before the range + ws.Range("A2:A3").Delete(XLShiftDeletedCells.ShiftCellsLeft); // Range starts on B2 + + // Delete a column in between the range + ws.Column(3).Delete(); // Range now has 3 columns + + // Delete a column (from a range) in between the range + ws.Range("C2:C3").Delete(XLShiftDeletedCells.ShiftCellsLeft); // Range now has 2 columns + + // Deleting columns from a range not covering all columns + // does not affect our defined range + ws.Range("A1:A2").Delete(XLShiftDeletedCells.ShiftCellsLeft); + ws.Range("D3:D4").Delete(XLShiftDeletedCells.ShiftCellsLeft); + + rng.Style.Fill.BackgroundColor = Color.Orange; + + workbook.SaveAs(filePath); + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/InsertingDeletingRows.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/InsertingDeletingRows.cs new file mode 100644 index 0000000..466ca88 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/InsertingDeletingRows.cs @@ -0,0 +1,59 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; +using System.Drawing; + +namespace ClosedXML_Examples +{ + public class InsertingDeletingRows + { + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.Worksheets.Add("Inserting and Deleting Rows"); + + // Range starts with 2 rows + var rng = ws.Range("B2:C3"); + + // Insert a row above the range + ws.Row(1).InsertRowsBelow(1); // Range starts on B3 + + // Insert a row in between the range + ws.Row(3).InsertRowsBelow(1); // Range now has 3 rows + + // Insert a row (from a range) above the range + ws.Range("B1:C1").InsertRowsBelow(1); // Range starts on B4 + + // Insert a row (from a range) in between the range + ws.Range("B4:C4").InsertRowsBelow(1); // Range now has 4 rows + + // Inserting rows from a range not covering all columns + // does not affect our defined range + ws.Range("A1:B1").InsertRowsBelow(1); + ws.Range("C4:D4").InsertRowsBelow(1); + + // Delete a row above the range + ws.Row(1).Delete(); // Range starts on B3 + + // Delete a row (from a range) above the range + ws.Range("B1:C1").Delete(XLShiftDeletedCells.ShiftCellsUp); // Range starts on B2 + + // Delete a row in between the range + ws.Row(3).Delete(); // Range now has 3 rows + + // Delete a row (from a range) in between the range + ws.Range("B3:C3").Delete(XLShiftDeletedCells.ShiftCellsUp); // Range now has 2 rows + + // Deleting rows from a range not covering all columns + // does not affect our defined range + ws.Range("A1:B1").Delete(XLShiftDeletedCells.ShiftCellsUp); + ws.Range("C4:D4").Delete(XLShiftDeletedCells.ShiftCellsUp); + + rng.Style.Fill.BackgroundColor = Color.Orange; + + workbook.SaveAs(filePath); + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/MovingRanges.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/MovingRanges.cs new file mode 100644 index 0000000..7e3dae9 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/MovingRanges.cs @@ -0,0 +1,31 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; +using System.Drawing; + +namespace ClosedXML_Examples +{ + public class MovingRanges + { + public void Create() + { + var workbook = new XLWorkbook(); + workbook.Load(@"C:\Excel Files\Created\BasicTable.xlsx"); + var ws = workbook.Worksheets.GetWorksheet(0); + + // Get a range object + var rngHeaders = ws.Range("B3:F3"); + + // Insert some rows/columns before the range + ws.Row(1).InsertRowsAbove(2); + ws.Column(1).InsertColumnsBefore(2); + + // Change the background color of the headers + rngHeaders.Style.Fill.BackgroundColor = Color.LightSalmon; + + workbook.SaveAs(@"C:\Excel Files\Created\MovingRanges.xlsx"); + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/MultipleRanges.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/MultipleRanges.cs new file mode 100644 index 0000000..91b69f7 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/MultipleRanges.cs @@ -0,0 +1,26 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; +using System.Drawing; + +namespace ClosedXML_Examples +{ + public class MultipleRanges + { + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.Worksheets.Add("Multiple Ranges"); + + // using multiple string range definitions + ws.Ranges("A1:B2", "C3:D4", "E5:F6").Style.Fill.BackgroundColor = Color.Red; + + // using a single string separated by commas + ws.Ranges("A5:B6,E1:F2").Style.Fill.BackgroundColor = Color.Orange; + + workbook.SaveAs(filePath); + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Rows/InsertRows.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Rows/InsertRows.cs index 6649207..b1b8efe 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Rows/InsertRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Rows/InsertRows.cs @@ -47,21 +47,31 @@ public void Create(String filePath) { var workbook = new XLWorkbook(); - var ws = workbook.Worksheets.Add("Insert Rows"); + var ws = workbook.Worksheets.Add("Inserting Rows"); + // Color the entire spreadsheet using rows + ws.Rows().Style.Fill.BackgroundColor = Color.LightCyan; + + // Put a value in a few cells foreach (var r in Enumerable.Range(1, 5)) - { foreach (var c in Enumerable.Range(1, 5)) - { ws.Cell(r, c).Value = "X"; - ws.Cell(r, c).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; - } - } - ws.Row(3).InsertRowsBelow(2); - ws.Row(1).InsertRowsAbove(2); - ws.Range("D3:E4").InsertRowsBelow(2); - ws.Range("B3:C5").InsertRowsAbove(2); + var blueRow = ws.Row(2); + var redRow = ws.Row(5); + + blueRow.Style.Fill.BackgroundColor = Color.Blue; + blueRow.InsertRowsBelow(2); + + + redRow.Style.Fill.BackgroundColor = Color.Red; + redRow.InsertRowsAbove(2); + + ws.Columns(3, 4).Style.Fill.BackgroundColor = Color.Orange; + ws.Range("A2:A4").InsertRowsBelow(2); + ws.Range("B2:B4").InsertRowsAbove(2); + ws.Range("C2:C4").InsertRowsBelow(2); + ws.Range("D2:D4").InsertRowsAbove(2); workbook.SaveAs(filePath); } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Rows/RowCollections.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Rows/RowCollections.cs index 0f88b95..7621cb4 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Rows/RowCollections.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Rows/RowCollections.cs @@ -67,7 +67,7 @@ // Using a single number bigRange.Rows("13").Style.Fill.BackgroundColor = Color.Cyan; - // Only the used rows in a worksheet + // Adjust the height ws.Rows().Height = 15; var ws2 = workbook.Worksheets.Add("Rows of a Worksheet"); @@ -84,6 +84,7 @@ // Using a single number ws2.Rows("13").Style.Fill.BackgroundColor = Color.Cyan; + // Adjust the height ws2.Rows("1:13").Height = 15; workbook.SaveAs(filePath); diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleFill.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleFill.cs index ecfcebd..b2ef9a8 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleFill.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleFill.cs @@ -8,6 +8,8 @@ namespace ClosedXML_Examples.Styles { + + public class StyleFill { public void Create(String filePath) diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleRowsColumns.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleRowsColumns.cs new file mode 100644 index 0000000..f47b100 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleRowsColumns.cs @@ -0,0 +1,38 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; +using System.Drawing; + + +namespace ClosedXML_Examples.Styles +{ + public class StyleRowsColumns + { + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.Worksheets.Add("Style Rows and Columns"); + + //Set the entire worksheet's cells to be bold and with a light cyan background + ws.Style.Font.Bold = true; + ws.Style.Fill.BackgroundColor = Color.LightCyan; + + // Set the width of all columns in the worksheet + ws.Columns().Width = 5; + + // Set the height of all rows in the worksheet + ws.Rows().Height = 20; + + // Let's play with the rows and columns + ws.Rows(2, 3).Style.Fill.BackgroundColor = Color.Blue; + ws.Columns(3, 4).Style.Fill.BackgroundColor = Color.Orange; + ws.Rows(5, 5).Style.Fill.BackgroundColor = Color.Pink; + ws.Row(6).Style.Fill.BackgroundColor = Color.Brown; + ws.Column("E").Style.Fill.BackgroundColor = Color.Gray; + + workbook.SaveAs(filePath); + } + } +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleWorksheet.cs new file mode 100644 index 0000000..0f88485 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleWorksheet.cs @@ -0,0 +1,46 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; +using System.Drawing; + + +namespace ClosedXML_Examples.Styles +{ + public class StyleWorksheet + { + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.Worksheets.Add("Style Worksheet"); + + ws.Style.Font.Bold = true; + ws.Style.Font.FontColor = Color.Red; + ws.Style.Fill.BackgroundColor = Color.Cyan; + + // The following cells will be bold and red + // because we've specified those attributes to the entire worksheet + ws.Cell(1, 1).Value = "Test"; + ws.Cell(1, 2).Value = "Case"; + + // Here we'll change the style of a single cell + ws.Cell(2, 1).Value = "Default"; + ws.Cell(2, 1).Style = XLWorkbook.DefaultStyle; + + // Let's play with some rows + ws.Row(4).Style = XLWorkbook.DefaultStyle; + ws.Row(4).Height = 20; + ws.Rows(5, 6).Style = XLWorkbook.DefaultStyle; + ws.Rows(5, 6).Height = 20; + + // Let's play with some columns + ws.Column(4).Style = XLWorkbook.DefaultStyle; + ws.Column(4).Width = 5; + ws.Columns(5, 6).Style = XLWorkbook.DefaultStyle; + ws.Columns(5, 6).Width = 5; + + workbook.SaveAs(filePath); + } + } +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index ae3ae4c..2462ffe 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -13,15 +13,21 @@ static void Main(string[] args) { var wb = new XLWorkbook(); - wb.Load(@"c:\Initial.xlsx"); - wb.SaveAs(@"c:\Initial_Saved.xlsx"); - //Console.ReadKey(); + var ws = wb.Worksheets.Add("Test"); + ws.Row(1).Style.Fill.BackgroundColor = Color.Red; + ws.Cell(1, 1).Value = "Hello"; + // Also test painting a row/column, setting the value of a cell, and then moving it. + // Change Internal references on XLRows/XLColumns so they return the values from Worksheet.Internal.Rows/Columns collection + + //wb.Load(@"c:\Initial.xlsx"); + wb.SaveAs(@"C:\Excel Files\ForTesting\Sandbox.xlsx"); + //Console.ReadKey(); } - // Modify IXLRange to have the "IXLRanges Ranges(...)" methods - // Modify DefiningRanges example to show how to select multiple ranges + // Apply a style to the entire sheet (not just the used cells) // Implement formulas // Implement grouping of rows and columns + // Adjust rows/columns heights/widths } }