public void Range1() { var wb = new XLWorkbook(); IXLRange range = wb.Range("ABC"); Assert.IsNull(range); }
private void ImpostaCampiTestata(Pagamento pagamento) { _wb.Range("Nome").Value = pagamento.Nome + " " + pagamento.Cognome; _wb.Range("Articolo").Value = pagamento.ArticoloAcq; _wb.Range("ImportoRata").Value = pagamento.ImportoRata; _wb.Range("ImportoResiduo").Value = pagamento.ImportoResiduo; _wb.Range("ImportoTotale").Value = pagamento.ImportoTotale; }
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 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>()); }