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(); }