public void WbNamedRangesOneString()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet1");
wb.NamedRanges.Add("TestRange", "Sheet1!$A$1,Sheet1!$A$3");
IXLRanges wbRanges = ws.Ranges("TestRange");
Assert.AreEqual("$A$1:$A$1", wbRanges.First().RangeAddress.ToStringFixed());
Assert.AreEqual("$A$3:$A$3", wbRanges.Last().RangeAddress.ToStringFixed());
IXLRanges wsRanges = ws.Ranges("TestRange");
Assert.AreEqual("$A$1:$A$1", wsRanges.First().RangeAddress.ToStringFixed());
Assert.AreEqual("$A$3:$A$3", wsRanges.Last().RangeAddress.ToStringFixed());
}
public void GetRangesFromFullAddress()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Sheet1");
var r1 = ws.Ranges("A1:B2,C1:E3");
var r2 = wb.Ranges("Sheet1!A1:B2,Sheet1!C1:E3");
Assert.AreEqual(2, r2.Count);
Assert.AreSame(r1.First(), r2.First());
Assert.AreSame(r1.Last(), r2.Last());
}
public void WsNamedRanges()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet1");
ws.Cell(1, 1).SetValue("A");
ws.Cell(2, 1).SetValue("B");
ws.Cell(3, 1).SetValue("C");
IXLRange original = ws.Range("A1:A2");
original.AddToNamed("TestRange", XLScope.Worksheet);
IXLRanges namedRanges = ws.Ranges("TestRange, A3");
Assert.AreEqual(original.RangeAddress.ToStringFixed(), namedRanges.First().RangeAddress.ToString());
Assert.AreEqual("$A$3:$A$3", namedRanges.Last().RangeAddress.ToStringFixed());
}
public void ConsolidateShiftsFormulaRelativelyToTopMostCell()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet");
var ranges = ws.Ranges("B3:B8,C3:C4,A3:A4,C5:C8,A5:A8").Cast <XLRange>();
var cf = new XLConditionalFormat(ranges);
cf.Values.Add(new XLFormula("=A3=$D3"));
cf.Style.Fill.SetBackgroundColor(XLColor.Red);
ws.ConditionalFormats.Add(cf);
((XLConditionalFormats)ws.ConditionalFormats).Consolidate();
Assert.AreEqual(1, ws.ConditionalFormats.Count());
Assert.AreEqual((ws.ConditionalFormats.Single().Style as XLStyle).Value, (cf.Style as XLStyle).Value);
Assert.AreEqual("A3:C8", ws.ConditionalFormats.Single().Ranges.Single().RangeAddress.ToString());
Assert.IsTrue(ws.ConditionalFormats.Single().Values.Single().Value.IsFormula);
Assert.AreEqual("A3=$D3", ws.ConditionalFormats.Single().Values.Single().Value.Value);
}
private void AddPerformanceFormatting(IXLWorksheet performanceSheet)
{
int lastRowUsed = performanceSheet.LastRowUsed().RowNumber();
//freeze panels
performanceSheet.SheetView.Freeze(1, 2);
//performance global styles
performanceSheet.Range(1, 1, performanceSheet.LastCellUsed().Address.RowNumber, performanceSheet.LastCellUsed().Address.ColumnNumber)
.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
//column specific formatting
//date columns
performanceSheet.Range(string.Format("A2:A{0}", lastRowUsed)).Style.DateFormat.Format = "dd/MM/yyyy";
//percentage formatting (0.00%)
performanceSheet.Ranges(string.Format("E2:E{0},H2:I{1},Y2:Y{2},AC2:AC{3},AE2:AF{4},AH2:AI{5},AK2:AL{6},AN2:AO{7}",
lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed
)).Style.NumberFormat.NumberFormatId = 10;
//no decimal points
performanceSheet.Range(string.Format("R2:R{0}", lastRowUsed)).Style.NumberFormat.Format = "0";
//decimal format (0.00)
performanceSheet.Ranges(string.Format("J2:J{0},L2:N{1}",
lastRowUsed, lastRowUsed
)).Style.NumberFormat.Format = "0.00";
//three decimal points (0.000)
performanceSheet.Range(string.Format("U2:U{0}",
lastRowUsed
)).Style.NumberFormat.Format = "0.000";
//money with two decimals ($ 0.00)
performanceSheet.Ranges(string.Format("T2:T{0},W2:W{1},Z2:Z{2}",
lastRowUsed, lastRowUsed, lastRowUsed
)).Style.NumberFormat.Format = "$ 0.00";
//money with three decimals ($ 0.000)
performanceSheet.Range(string.Format("V2:V{0}",
lastRowUsed, lastRowUsed, lastRowUsed
)).Style.NumberFormat.Format = "$ 0.000";
// adjust to content
performanceSheet.Columns().AdjustToContents();
}
