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