C# XLWorkbook.Range方法代码示例

 public void CellsUsed()
 {
     IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
     ws.Cell(1, 1);
     ws.Cell(2, 2);
     int count = ws.Range("A1:B2").CellsUsed().Count();
     Assert.AreEqual(0, count);
 }

 public void IsEmpty2()
 {
     IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
     IXLCell cell = ws.Cell(1, 1);
     IXLRange range = ws.Range("A1:B2");
     bool actual = range.IsEmpty(true);
     bool expected = true;
     Assert.AreEqual(expected, actual);
 }

 public void IsEmpty5()
 {
     IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
     IXLCell cell = ws.Cell(1, 1);
     cell.Style.Fill.BackgroundColor = XLColor.Red;
     IXLRange range = ws.Range("A1:B2");
     bool actual = range.IsEmpty(true);
     bool expected = false;
     Assert.AreEqual(expected, actual);
 }

        public static Dictionary<string, string> GetMarkupValues(Stream stream, params string[] markupArr)
        {
            var result = new Dictionary<string, string>();

            string sheetName = "Заявка";
            string addressName = "et_name";

            var workbook = new XLWorkbook(stream);
            var worksheet = workbook.Worksheets.Worksheet(sheetName);

            var range = workbook.Range(addressName);

            var cells = range.Cells();

            foreach (var xlCell in cells)
            {
                result.Add("KEY", xlCell.Value.ToString());
            }

            //SpreadsheetDocument document = SpreadsheetDocument.Open(stream, false);

            //WorkbookPart wbPart = document.WorkbookPart;

            //Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().
            //  Where(s => s.Name == sheetName).FirstOrDefault();

            //// Throw an exception if there is no sheet.
            //if (theSheet == null)
            //{
            //    throw new ArgumentException("sheetName");
            //}

            //WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));

            //Cell theCell = wsPart.Worksheet.Descendants<Cell>().Where(c => c.LocalName == addressName).FirstOrDefault();

            //if (theCell != null)
            //{
            //    result.Add("KEY", theCell.InnerText);
            //    // Code removed here…
            //}

            return result;
        }

        public void MergedRanges()
        {
            IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
            ws.Range("A1:B2").Merge();
            ws.Range("C1:D3").Merge();
            ws.Range("D2:E2").Merge();

            Assert.AreEqual(2, ws.MergedRanges.Count);
            Assert.AreEqual("A1:B2", ws.MergedRanges.First().RangeAddress.ToStringRelative());
            Assert.AreEqual("D2:E2", ws.MergedRanges.Last().RangeAddress.ToStringRelative());
        }

        public void TableRange()
        {
            var wb = new XLWorkbook();
            IXLWorksheet ws = wb.Worksheets.Add("Sheet1");
            IXLRangeColumn rangeColumn = ws.Column(1).Column(1, 4);
            rangeColumn.Cell(1).Value = "FName";
            rangeColumn.Cell(2).Value = "John";
            rangeColumn.Cell(3).Value = "Hank";
            rangeColumn.Cell(4).Value = "Dagny";
            IXLTable table = rangeColumn.CreateTable();
            wb.NamedRanges.Add("FNameColumn", String.Format("{0}[{1}]", table.Name, "FName"));

            IXLRange namedRange = wb.Range("FNameColumn");
            Assert.AreEqual(3, namedRange.Cells().Count());
            Assert.IsTrue(
                namedRange.CellsUsed().Select(cell => cell.GetString()).SequenceEqual(new[] {"John", "Hank", "Dagny"}));
        }

 public void SingleCell()
 {
     var wb = new XLWorkbook();
     IXLWorksheet ws = wb.Worksheets.Add("Sheet1");
     ws.Cell(1, 1).Value = "Hello World!";
     wb.NamedRanges.Add("SingleCell", "Sheet1!$A$1");
     IXLRange range = wb.Range("SingleCell");
     Assert.AreEqual(1, range.CellsUsed().Count());
     Assert.AreEqual("Hello World!", range.CellsUsed().Single().GetString());
 }

        // Public
        public void Create(String filePath)
        {
            var wb = new XLWorkbook();
            var wsPresentation = wb.Worksheets.Add("Presentation");
            var wsData = wb.Worksheets.Add("Data");

            // Fill up some data
            wsData.Cell(1, 1).Value = "Name";
            wsData.Cell(1, 2).Value = "Age";
            wsData.Cell(2, 1).Value = "Tom";
            wsData.Cell(2, 2).Value = 30;
            wsData.Cell(3, 1).Value = "Dick";
            wsData.Cell(3, 2).Value = 25;
            wsData.Cell(4, 1).Value = "Harry";
            wsData.Cell(4, 2).Value = 29;

            // Create a named range with the data:
            wsData.Range("A2:B4").AddToNamed("PeopleData"); // Default named range scope is Workbook

            // Let's use the named range in a formula:
            wsPresentation.Cell(1, 1).Value = "People Count:";
            wsPresentation.Cell(1, 2).FormulaA1 = "COUNT(PeopleData)";

            // Create a named range with worksheet scope:
            wsPresentation.Range("B1").AddToNamed("PeopleCount", XLScope.Worksheet);

            // Let's use the named range:
            wsPresentation.Cell(2, 1).Value = "Total:";
            wsPresentation.Cell(2, 2).FormulaA1 = "PeopleCount";

            // Copy the data in a named range:
            wsPresentation.Cell(4, 1).Value = "People Data:";
            wsPresentation.Cell(5, 1).Value = wb.Range("PeopleData");

            /////////////////////////////////////////////////////////////////////////
            // For the Excel geeks out there who actually know about
            // named ranges with relative addresses, you can
            // create such a thing with the following methods:

            // The following creates a relative named range pointing to the same row
            // and one column to the right. For example if the current cell is B4
            // relativeRange1 will point to C4.
            wsPresentation.NamedRanges.Add("relativeRange1", "Presentation!B1");

            // The following creates a ralative named range pointing to the same row
            // and one column to the left. For example if the current cell is D2
            // relativeRange2 will point to C2.
            wb.NamedRanges.Add("relativeRange2", "Presentation!XFD1");

            // Explanation: The address of a relative range always starts at A1
            // and moves from then on. To get the desired relative range just
            // add or subtract the required rows and/or columns from A1.
            // Column -1 = XFD, Column -2 = XFC, etc.
            // Row -1 = 1048576, Row -2 = 1048575, etc.
            /////////////////////////////////////////////////////////////////////////

            wsData.Columns().AdjustToContents();
            wsPresentation.Columns().AdjustToContents();

            wb.SaveAs(filePath);
        }

 public void WbNamedRange()
 {
     var wb = new XLWorkbook();
     IXLWorksheet ws = wb.Worksheets.Add("Sheet1");
     ws.Cell(1, 1).SetValue("A");
     ws.Cell(2, 1).SetValue("B");
     IXLRange original = ws.Range("A1:A2");
     original.AddToNamed("TestRange");
     Assert.AreEqual(original.RangeAddress.ToStringFixed(), wb.Range("TestRange").RangeAddress.ToString());
     Assert.AreEqual(original.RangeAddress.ToStringFixed(), ws.Range("TestRange").RangeAddress.ToString());
 }

 public void Range3()
 {
     var wb = new XLWorkbook();
     IXLWorksheet ws = wb.AddWorksheet("Sheet1");
     ws.FirstCell().SetValue(1).AddToNamed("Result");
     IXLRange range = wb.Range("Sheet1!Result");
     Assert.IsNotNull(range);
     Assert.AreEqual(1, range.Cells().Count());
     Assert.AreEqual(1, range.FirstCell().GetValue<Int32>());
 }

 public void Range1()
 {
     var wb = new XLWorkbook();
     IXLRange range = wb.Range("ABC");
     Assert.IsNull(range);
 }

        public void CanGetNamedFromAnother()
        {
            var wb = new XLWorkbook();
            var ws1 = wb.Worksheets.Add("Sheet1");
            ws1.Cell("A1").SetValue(1).AddToNamed("value1");

            Assert.AreEqual(1, wb.Cell("value1").GetValue<int>());
            Assert.AreEqual(1, wb.Range("value1").FirstCell().GetValue<int>());

            Assert.AreEqual(1, ws1.Cell("value1").GetValue<int>());
            Assert.AreEqual(1, ws1.Range("value1").FirstCell().GetValue<int>());

            var ws2 = wb.Worksheets.Add("Sheet2");

            ws2.Cell("A1").SetFormulaA1("=value1").AddToNamed("value2");

            Assert.AreEqual(1, wb.Cell("value2").GetValue<int>());
            Assert.AreEqual(1, wb.Range("value2").FirstCell().GetValue<int>());

            Assert.AreEqual(1, ws2.Cell("value1").GetValue<int>());
            Assert.AreEqual(1, ws2.Range("value1").FirstCell().GetValue<int>());

            Assert.AreEqual(1, ws2.Cell("value2").GetValue<int>());
            Assert.AreEqual(1, ws2.Range("value2").FirstCell().GetValue<int>());
        }