diff --git a/ClosedXML/ClosedXML/Excel/XLAddress.cs b/ClosedXML/ClosedXML/Excel/XLAddress.cs index 48cfde4..f5f1243 100644 --- a/ClosedXML/ClosedXML/Excel/XLAddress.cs +++ b/ClosedXML/ClosedXML/Excel/XLAddress.cs @@ -6,33 +6,58 @@ namespace ClosedXML.Excel { + /// + /// Represents the address of a single cell in a worksheet. + /// public struct XLAddress : IEqualityComparer, IEquatable, IComparable, IComparable { #region Constructors + + /// + /// Initializes a new struct using R1C1 notation. + /// + /// The row number of the cell address. + /// The column number of the cell address. public XLAddress(UInt32 row, UInt32 column) { this.row = row; this.column = column; - this.columnLetter = ColumnNumberToLetter(column); + this.columnLetter = GetColumnLetterFromNumber(column); } + /// + /// Initializes a new struct using a mixed notation. + /// + /// The row number of the cell address. + /// The column letter of the cell address. public XLAddress(UInt32 row, String columnLetter) { this.row = row; - this.column = ColumnLetterToNumber(columnLetter); + this.column = GetColumnNumberFromLetter(columnLetter); this.columnLetter = columnLetter; } + + /// + /// Initializes a new struct using A1 notation. + /// + /// The cell address. public XLAddress(String cellAddressString) { Match m = Regex.Match(cellAddressString, @"^([a-zA-Z]+)(\d+)$"); columnLetter = m.Groups[1].Value; this.row = UInt32.Parse(m.Groups[2].Value); - this.column = ColumnLetterToNumber(columnLetter); + this.column = GetColumnNumberFromLetter(columnLetter); } + #endregion + #region Properties + private UInt32 row; + /// + /// Gets the row number of this address. + /// public UInt32 Row { get { return row; } @@ -40,6 +65,9 @@ } private UInt32 column; + /// + /// Gets the column number of this address. + /// public UInt32 Column { get { return column; } @@ -47,13 +75,24 @@ } private String columnLetter; + /// + /// Gets the column letter(s) of this address. + /// public String ColumnLetter { get { return columnLetter; } private set { columnLetter = value; } } - public static UInt32 ColumnLetterToNumber(String column) + #endregion + + #region Static + + /// + /// Gets the column number of a given column letter. + /// + /// The column letter to translate into a column number. + public static UInt32 GetColumnNumberFromLetter(String column) { Int32 intColumnLetterLength = column.Length; Int32 retVal = 0; @@ -64,7 +103,11 @@ return (UInt32)retVal; } - public static String ColumnNumberToLetter(UInt32 column) + /// + /// Gets the column letter of a given column number. + /// + /// The column number to translate into a column letter. + public static String GetColumnLetterFromNumber(UInt32 column) { String s = String.Empty; for ( @@ -91,11 +134,17 @@ return s; } + #endregion + + #region Overridden + public override String ToString() { return ColumnLetter + Row.ToString(); } + #endregion + #region Operator Overloads public static XLAddress operator +(XLAddress xlCellAddressLeft, XLAddress xlCellAddressRight) diff --git a/ClosedXML/ClosedXML/Excel/XLRange.cs b/ClosedXML/ClosedXML/Excel/XLRange.cs index b862259..69f6b18 100644 --- a/ClosedXML/ClosedXML/Excel/XLRange.cs +++ b/ClosedXML/ClosedXML/Excel/XLRange.cs @@ -104,6 +104,12 @@ return Range(new XLAddress(firstCellAddress), new XLAddress(lastCellAddress)); } + public XLRange Range(String rangeAddress) + { + String[] arrRange = rangeAddress.Split(':'); + return Range(arrRange[0], arrRange[1]); + } + public XLRange Cell(XLAddress address) { XLAddress absoluteCellAddress; diff --git a/ClosedXML/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/ClosedXML/Excel/XLWorkbook.cs index 7dc9f79..b9d83e9 100644 --- a/ClosedXML/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/ClosedXML/Excel/XLWorkbook.cs @@ -112,8 +112,8 @@ /// New Excel file to be created. public XLWorkbook(String file) { - if (File.Exists(file)) - throw new ArgumentException("File already exists."); + if (File.Exists(file)) File.Delete(file); + //throw new ArgumentException("File already exists."); FileInfo fi = new FileInfo(file); this.Name = fi.Name; diff --git a/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML_Sandbox/Program.cs index c77b7ec..7fd8ae9 100644 --- a/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -3,6 +3,7 @@ using System.Linq; using System.Text; using ClosedXML.Excel; +using ClosedXML.Excel.Style; namespace ClosedXML_Sandbox { @@ -10,18 +11,47 @@ { static void Main(string[] args) { - var wbExample = new XLWorkbook(@"c:\Example.xlsx"); - var wsWorld = wbExample.Worksheets.Add("World"); - var wsNameList = wbExample.Worksheets.Add("Name List"); - var wsDeleteMe = wbExample.Worksheets.Add("Delete Me"); + var wb = new XLWorkbook(@"c:\ContactList.xlsx"); + var ws = wb.Worksheets.Add("Contacts"); + //First Names + ws.Cell("A1").Value = "FName"; + ws.Cell("A2").Value = "John"; + ws.Cell("A3").Value = "Hank"; + ws.Cell("A4").Value = "Dagny"; + //Last Names + ws.Cell("B1").Value = "LName"; + ws.Cell("B2").Value = "Galt"; + ws.Cell("B3").Value = "Rearden"; + ws.Cell("B4").Value = "Taggart"; + //Is an outcast? + ws.Cell("C1").Value = "Outcast"; + ws.Cell("C2").Value = true.ToString(); + ws.Cell("C3").Value = false.ToString(); + ws.Cell("C4").Value = false.ToString(); + //Date of Birth + ws.Cell("D1").Value = "DOB"; + ws.Cell("D2").Value = new DateTime(1919, 1, 21).ToString(); + ws.Cell("D3").Value = new DateTime(1907, 3, 4).ToString(); + ws.Cell("D4").Value = new DateTime(1921, 12, 15).ToString(); + //Income + ws.Cell("E1").Value = "Income"; + ws.Cell("E2").Value = "2000"; + ws.Cell("E3").Value = "40000"; + ws.Cell("E4").Value = "10000"; + + var rngDates = ws.Range("D2:D4"); + var rngNumbers = ws.Range("E2:E4"); + + rngDates.Style.NumberFormat.Format = "mm-dd-yy"; + rngNumbers.Style.NumberFormat.Format = "$ #,##0"; + + var rngHeaders = ws.Range("A1:E1"); + rngHeaders.Style.Font.Bold = true; + rngHeaders.Style.Fill.BackgroundColor = "6BE8FF"; - var a1 = wsNameList.Cell("A1"); - var a2 = wsNameList.Cell("A2"); - a1.Value = "Hello!"; - wbExample.Save(); - // a2.Font.Bold = true; + wb.Save(); } } } diff --git a/ClosedXML/ClosedXML_Test/ClosedXML_Test.csproj b/ClosedXML/ClosedXML_Test/ClosedXML_Test.csproj index 4872842..8ec6041 100644 --- a/ClosedXML/ClosedXML_Test/ClosedXML_Test.csproj +++ b/ClosedXML/ClosedXML_Test/ClosedXML_Test.csproj @@ -57,6 +57,7 @@ + diff --git a/ClosedXML/ClosedXML_Test/XLAddressTest.cs b/ClosedXML/ClosedXML_Test/XLAddressTest.cs new file mode 100644 index 0000000..6172051 --- /dev/null +++ b/ClosedXML/ClosedXML_Test/XLAddressTest.cs @@ -0,0 +1,401 @@ +using ClosedXML.Excel; +using Microsoft.VisualStudio.TestTools.UnitTesting; +using System; + +namespace ClosedXML_Test +{ + + + /// + ///This is a test class for XLAddressTest and is intended + ///to contain all XLAddressTest Unit Tests + /// + [TestClass()] + public class XLAddressTest + { + + + private TestContext testContextInstance; + + /// + ///Gets or sets the test context which provides + ///information about and functionality for the current test run. + /// + public TestContext TestContext + { + get + { + return testContextInstance; + } + set + { + testContextInstance = value; + } + } + + #region Additional test attributes + // + //You can use the following additional attributes as you write your tests: + // + //Use ClassInitialize to run code before running the first test in the class + //[ClassInitialize()] + //public static void MyClassInitialize(TestContext testContext) + //{ + //} + // + //Use ClassCleanup to run code after all tests in a class have run + //[ClassCleanup()] + //public static void MyClassCleanup() + //{ + //} + // + //Use TestInitialize to run code before running each test + //[TestInitialize()] + //public void MyTestInitialize() + //{ + //} + // + //Use TestCleanup to run code after each test has run + //[TestCleanup()] + //public void MyTestCleanup() + //{ + //} + // + #endregion + + + /// + ///A test for XLAddress Constructor + /// + [TestMethod()] + public void XLAddress_R1C1Constructor_Test() + { + uint row = 4; + uint column = 4; + XLAddress target = new XLAddress(row, column); + + Assert.AreEqual(target.Row, 4U); + Assert.AreEqual(target.Column, 4U); + Assert.AreEqual(target.ColumnLetter, "D"); + } + + [TestMethod()] + public void XLAddress_A1Constructor_Test() + { + String address = "D4"; + XLAddress target = new XLAddress(address); + + Assert.AreEqual(target.Row, 4U); + Assert.AreEqual(target.Column, 4U); + Assert.AreEqual(target.ColumnLetter, "D"); + } + + [TestMethod()] + public void XLAddress_MixedConstructor_Test() + { + uint row = 4; + String columnLetter = "D"; + XLAddress target = new XLAddress(row, columnLetter); + + Assert.AreEqual(target.Row, 4U); + Assert.AreEqual(target.Column, 4U); + Assert.AreEqual(target.ColumnLetter, "D"); + } + + /// + ///A test for GetColumnNumberFromLetter + /// + [TestMethod()] + public void GetColumnNumberFromLetterTest() + { + string column = "OMV"; + uint expected = 10500; + uint actual; + actual = XLAddress.GetColumnNumberFromLetter(column); + Assert.AreEqual(expected, actual); + } + + /// + ///A test for GetColumnLetterFromNumber + /// + [TestMethod()] + public void GetColumnLetterFromNumberTest() + { + uint column = 10500; + string expected = "OMV"; + string actual; + actual = XLAddress.GetColumnLetterFromNumber(column); + Assert.AreEqual(expected, actual); + } + + /// + ///A test for op_Addition + /// + [TestMethod()] + public void op_AdditionTest() + { + XLAddress xlCellAddressLeft = new XLAddress(7, 5); + uint right = 1; + XLAddress expected = new XLAddress(8,6); + XLAddress actual; + actual = (xlCellAddressLeft + right); + Assert.AreEqual(expected, actual); + } + + /// + ///A test for op_Addition + /// + [TestMethod()] + public void op_AdditionTest1() + { + XLAddress xlCellAddressLeft = new XLAddress(7,5); + XLAddress xlCellAddressRight = new XLAddress(10, 3); + XLAddress expected = new XLAddress(17, 8); + XLAddress actual; + actual = (xlCellAddressLeft + xlCellAddressRight); + Assert.AreEqual(expected, actual); + } + + /// + ///A test for op_Equality + /// + [TestMethod()] + public void op_EqualityTest() + { + XLAddress xlCellAddressLeft = new XLAddress(1,1); + XLAddress xlCellAddressRight = new XLAddress(1,1); + bool expected = true; + bool actual; + actual = (xlCellAddressLeft == xlCellAddressRight); + Assert.AreEqual(expected, actual); + } + + [TestMethod()] + public void op_EqualityTest1() + { + XLAddress xlCellAddressLeft = new XLAddress(1, 1); + XLAddress xlCellAddressRight = new XLAddress(1, 2); + bool expected = false; + bool actual; + actual = (xlCellAddressLeft == xlCellAddressRight); + Assert.AreEqual(expected, actual); + } + + /// + ///A test for op_GreaterThan + /// + [TestMethod()] + public void op_GreaterThanTest() + { + XLAddress xlCellAddressLeft = new XLAddress(3,3); + XLAddress xlCellAddressRight = new XLAddress(2,4); + bool expected = true; + bool actual; + actual = (xlCellAddressLeft > xlCellAddressRight); + Assert.AreEqual(expected, actual); + } + + [TestMethod()] + public void op_GreaterThanTest1() + { + XLAddress xlCellAddressLeft = new XLAddress(3, 3); + XLAddress xlCellAddressRight = new XLAddress(4, 1); + bool expected = true; + bool actual; + actual = (xlCellAddressLeft > xlCellAddressRight); + Assert.AreEqual(expected, actual); + } + + public void op_GreaterThanTest2() + { + XLAddress xlCellAddressLeft = new XLAddress(3, 3); + XLAddress xlCellAddressRight = new XLAddress(3, 4); + bool expected = false; + bool actual; + actual = (xlCellAddressLeft > xlCellAddressRight); + Assert.AreEqual(expected, actual); + } + + /// + ///A test for op_GreaterThanOrEqual + /// + [TestMethod()] + public void op_GreaterThanOrEqualTest() + { + XLAddress xlCellAddressLeft = new XLAddress(3,3); + XLAddress xlCellAddressRight = new XLAddress(2,4); + bool expected = true; + bool actual; + actual = (xlCellAddressLeft >= xlCellAddressRight); + Assert.AreEqual(expected, actual); + } + + [TestMethod()] + public void op_GreaterThanOrEqualTest1() + { + XLAddress xlCellAddressLeft = new XLAddress(3, 3); + XLAddress xlCellAddressRight = new XLAddress(4, 1); + bool expected = true; + bool actual; + actual = (xlCellAddressLeft >= xlCellAddressRight); + Assert.AreEqual(expected, actual); + } + + [TestMethod()] + public void op_GreaterThanOrEqualTest2() + { + XLAddress xlCellAddressLeft = new XLAddress(3, 3); + XLAddress xlCellAddressRight = new XLAddress(3, 3); + bool expected = true; + bool actual; + actual = (xlCellAddressLeft >= xlCellAddressRight); + Assert.AreEqual(expected, actual); + } + + [TestMethod()] + public void op_GreaterThanOrEqualTest3() + { + XLAddress xlCellAddressLeft = new XLAddress(3, 3); + XLAddress xlCellAddressRight = new XLAddress(3, 4); + bool expected = false; + bool actual; + actual = (xlCellAddressLeft >= xlCellAddressRight); + Assert.AreEqual(expected, actual); + } + + /// + ///A test for op_Inequality + /// + [TestMethod()] + public void op_InequalityTest() + { + XLAddress xlCellAddressLeft = new XLAddress(1,1); + XLAddress xlCellAddressRight = new XLAddress(1,2); + bool expected = true; + bool actual; + actual = (xlCellAddressLeft != xlCellAddressRight); + Assert.AreEqual(expected, actual); + } + + [TestMethod()] + public void op_InequalityTest1() + { + XLAddress xlCellAddressLeft = new XLAddress(1, 1); + XLAddress xlCellAddressRight = new XLAddress(1, 1); + bool expected = false; + bool actual; + actual = (xlCellAddressLeft != xlCellAddressRight); + Assert.AreEqual(expected, actual); + } + + /// + ///A test for op_LessThan + /// + [TestMethod()] + public void op_LessThanTest() + { + XLAddress xlCellAddressLeft = new XLAddress(3, 3); + XLAddress xlCellAddressRight = new XLAddress(2, 4); + bool expected = true; + bool actual; + actual = (xlCellAddressLeft < xlCellAddressRight); + Assert.AreEqual(expected, actual); + } + + [TestMethod()] + public void op_LessThanTest1() + { + XLAddress xlCellAddressLeft = new XLAddress(3, 3); + XLAddress xlCellAddressRight = new XLAddress(4, 1); + bool expected = true; + bool actual; + actual = (xlCellAddressLeft < xlCellAddressRight); + Assert.AreEqual(expected, actual); + } + + public void op_LessThanTest2() + { + XLAddress xlCellAddressLeft = new XLAddress(3, 3); + XLAddress xlCellAddressRight = new XLAddress(3, 4); + bool expected = true; + bool actual; + actual = (xlCellAddressLeft < xlCellAddressRight); + Assert.AreEqual(expected, actual); + } + + /// + ///A test for op_LessThanOrEqual + /// + [TestMethod()] + public void op_LessThanOrEqualTest() + { + XLAddress xlCellAddressLeft = new XLAddress(3, 3); + XLAddress xlCellAddressRight = new XLAddress(2, 4); + bool expected = true; + bool actual; + actual = (xlCellAddressLeft <= xlCellAddressRight); + Assert.AreEqual(expected, actual); + } + + [TestMethod()] + public void op_LessThanOrEqualTest1() + { + XLAddress xlCellAddressLeft = new XLAddress(3, 3); + XLAddress xlCellAddressRight = new XLAddress(4, 1); + bool expected = true; + bool actual; + actual = (xlCellAddressLeft <= xlCellAddressRight); + Assert.AreEqual(expected, actual); + } + + [TestMethod()] + public void op_LessThanOrEqualTest2() + { + XLAddress xlCellAddressLeft = new XLAddress(3, 3); + XLAddress xlCellAddressRight = new XLAddress(3, 3); + bool expected = true; + bool actual; + actual = (xlCellAddressLeft <= xlCellAddressRight); + Assert.AreEqual(expected, actual); + } + + [TestMethod()] + public void op_LessThanOrEqualTest3() + { + XLAddress xlCellAddressLeft = new XLAddress(3, 3); + XLAddress xlCellAddressRight = new XLAddress(3, 4); + bool expected = true; + bool actual; + actual = (xlCellAddressLeft <= xlCellAddressRight); + Assert.AreEqual(expected, actual); + } + + /// + ///A test for op_Subtraction + /// + [TestMethod()] + public void op_SubtractionTest() + { + XLAddress xlCellAddressLeft = new XLAddress(6, 3); + uint right = 2; + XLAddress expected = new XLAddress(4,1); + XLAddress actual; + actual = (xlCellAddressLeft - right); + Assert.AreEqual(expected, actual); + } + + /// + ///A test for op_Subtraction + /// + [TestMethod()] + public void op_SubtractionTest1() + { + XLAddress xlCellAddressLeft = new XLAddress(6,3); + XLAddress xlCellAddressRight = new XLAddress(2,1); + XLAddress expected = new XLAddress(4,2); + XLAddress actual; + actual = (xlCellAddressLeft - xlCellAddressRight); + Assert.AreEqual(expected, actual); + } + } +}