Thom Lamb
2cd6df6481
Establishes the foundational structure, core Excel functionality, and tooling. - Implements base components for cells, ranges, columns, and rows. - Integrates a custom calculation engine with a wide range of Excel functions. - Adds comprehensive support for styling, conditional formatting, data validation, comments, pictures, and charts. - Configures build setup, project metadata (NuGet), and developer guidelines. - Includes editor and Git attributes for consistent code style and line endings.
86 lines
3.3 KiB
C#
86 lines
3.3 KiB
C#
using ClosedXML.Excel;
|
|
using System;
|
|
|
|
namespace ClosedXML_Examples.Misc
|
|
{
|
|
public class NamedRanges : IXLExample
|
|
{
|
|
#region Methods
|
|
|
|
// 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
|
|
|
|
// Create a hidden named range
|
|
wb.NamedRanges.Add("Headers", wsData.Range("A1:B1")).Visible = false;
|
|
|
|
// Create a hidden named range n worksheet scope
|
|
wsData.NamedRanges.Add("HeadersAndData", wsData.Range("A1:B4")).Visible = false;
|
|
|
|
// 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);
|
|
}
|
|
|
|
// Private
|
|
|
|
// Override
|
|
|
|
#endregion Methods
|
|
}
|
|
}
|