public void GetRangeTest()
{
InteropExcel.Application application = new InteropExcel.Application();
try
{
InteropExcel.Workbook book = application.OpenWorkbook("TestData.xlsx", false);
InteropExcel._Worksheet worksheet = book.ActiveSheet as InteropExcel._Worksheet;
// Get the named range stored in the test data excel file.
InteropExcel.Name expected = book.Names.GetNamedRange("GetRange");
InteropExcel.Range firstCell = worksheet.Cells[9, 5];
int rowSize = 8;
int columnSize = 5;
InteropExcel.Range actual = null;
// Get the range using the custom extension methods.
actual = WorksheetExtensions.GetRange(worksheet, firstCell, rowSize, columnSize);
Assert.AreEqual(expected.RefersToRange.Address, actual.Address);
}
finally
{
application.Close();
}
}
public void IsSheetEmptyTest()
{
InteropExcel.Application application = new InteropExcel.Application();
try
{
InteropExcel.Workbook workbook = application.OpenWorkbook("TestData.xlsx", false);
// Get the target range that will be used to set the active sheet
InteropExcel.Name targetName = workbook.Names.GetNamedRange("TestRangeTarget");
// Activate the worksheet which contains the named range.
((_Worksheet)targetName.RefersToRange.Worksheet).Activate();
InteropExcel._Worksheet worksheet = workbook.ActiveSheet as InteropExcel._Worksheet;
bool expected = true;
bool actual;
actual = WorksheetExtensions.IsSheetEmpty(worksheet);
Assert.AreEqual(expected, actual);
}
finally
{
application.Close();
}
}
public void GetValidNameTest()
{
InteropExcel.Application application = new InteropExcel.Application();
try
{
InteropExcel.Workbook workbook = application.OpenWorkbook("TestData.xlsx", false);
// Get the target range that will be used to set the active sheet
InteropExcel.Name targetName = workbook.Names.GetNamedRange("GetValidName");
// Activate the worksheet which contains the named range.
((_Worksheet)targetName.RefersToRange.Worksheet).Activate();
string name = ((_Worksheet)targetName.RefersToRange.Worksheet).Name;
string expected = "WWTLayer_012TestS";
string actual;
actual = WorkbookExtensions_Accessor.GetValidName(name);
Assert.AreEqual(expected, actual);
}
finally
{
application.Close();
}
}
/// <summary>
/// Возвращает массив с данными для импорта
/// </summary>
/// <param name="fileName">путь к файлу</param>
/// <returns></returns>
private static object[,] GetData(string fileName)
{
object[,] dataArr;
try
{
//if (System.IO.Path.GetExtension(fileName).ToLower() == ".mxl")
//{
// var curWb = new SpreadsheetDocument();
// curWb.Open(fileName);
// var a = curWb.Area(1,1,10,10).Value;
// return null;
//}
//else
//{
var curWb = new Excel.Application().Workbooks.Open(fileName);
dynamic curSheet = curWb.Worksheets.Item[1];
var lastRow = curSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
var lastCol = curSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Column;
// Считываем лист в массив
var range = (Excel.Range)curSheet.Range[curSheet.Cells[1, 1], curSheet.Cells[lastRow, lastCol]];
dataArr = (object[, ])range.Value;
curWb.Close();
//}
return(dataArr);
}
catch (Exception e)
{
LogManager.GetCurrentClassLogger().Error("Ошибка чтения файла ({0}), {1}", fileName, e);
throw;
}
}
public void GetHeaderNullDataTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range range = worksheet.get_Range("A1", "B1");
range.Value2 = null;
Collection <string> expected = new Collection <string>()
{
string.Empty, string.Empty
};
Collection <string> actual = range.GetHeader();
Assert.AreEqual(actual.Count, 2);
Assert.AreEqual(expected[0], actual[0]);
Assert.AreEqual(expected[1], actual[1]);
}
finally
{
excelApp.Close();
}
}
public void GetDataArrayMultipleCellRangeTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range range = worksheet.get_Range("A1", "B1");
Range cellOne = range.Cells[1, 1];
cellOne.Value = "TestValue1";
Range cellTwo = range.Cells[1, 2];
cellTwo.Value = "TestValue2";
object[,] expected = (object[, ])Array.CreateInstance(typeof(object), new int[] { 1, 2 }, new int[] { 1, 1 });
expected[1, 1] = "TestValue1";
expected[1, 2] = "TestValue2";
object[,] actual = range.GetDataArray(false);
Assert.AreEqual(expected.Length, actual.Length);
Assert.AreEqual(expected[1, 1], actual[1, 1]);
Assert.AreEqual(expected[1, 2], actual[1, 2]);
}
finally
{
excelApp.Close();
}
}
public void GetHeaderSingleCellDataTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range range = worksheet.get_Range("A1", Type.Missing);
range.Value2 = "TestValue";
Collection <string> expected = new Collection <string>()
{
"TestValue"
};
Collection <string> actual = range.GetHeader();
Assert.AreEqual(expected.Count, actual.Count);
Assert.AreEqual(expected[0], actual[0]);
}
finally
{
excelApp.Close();
}
}
public void HasChangedIntersectionNegativeSingleAndMultipleAreasTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range areaOne = worksheet.get_Range("A1", "I7");
Range areaTwo = worksheet.get_Range("A10", "I15");
Range mergedRange = excelApp.Union(areaOne, areaTwo);
Range areaThree = worksheet.get_Range("D12", Type.Missing);
Range mergedWithSingle = excelApp.Union(mergedRange, areaThree);
Range target = worksheet.get_Range("A8", "I8");
bool expected = false;
bool actual = mergedWithSingle.HasChanged(target);
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void GetDataFromRangePositiveTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range range = worksheet.get_Range("A1", "B3");
Range cell = range.Cells[1, 1];
cell.Value = "HeaderValueOne";
cell = range.Cells[1, 2];
cell.Value = "HeaderValueTwo";
cell = range.Cells[2, 1];
cell.Value = "DataValueOne";
cell = range.Cells[2, 2];
cell.Value = "DataValueTwo";
cell = range.Cells[3, 1];
cell.Value = "DataValueThree";
cell = range.Cells[3, 2];
cell.Value = "DataValueFour";
string expected = "HeaderValueOne\tHeaderValueTwo" + Environment.NewLine + "DataValueOne\tDataValueTwo" + Environment.NewLine + "DataValueThree\tDataValueFour" + Environment.NewLine;
string actual = RangeExtensions_Accessor.GetDataFromRange(range);
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void GetFirstDataRowTwoAreasTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
InteropExcel.Workbook workbook = excelApp.OpenWorkbook("TestData.xlsx", false);
// Get the target range that will be used to set the active sheet
InteropExcel.Name targetName = workbook.Names.GetNamedRange("DataRangeTwo");
Range range = targetName.RefersToRange;
Collection <string> expected = new Collection <string>()
{
"45", "63", "4.6", "3/7/2011 1:39:45 PM"
};
Collection <string> actual;
actual = range.GetFirstDataRow();
Assert.AreEqual(expected.Count, actual.Count);
for (int i = 0; i < expected.Count; i++)
{
Assert.AreEqual(expected[i], actual[i]);
}
}
finally
{
excelApp.Close();
}
}
private void Button_Click(object sender, RoutedEventArgs e)
{
dynamic exApp = new exc.Application();
var book = exApp.Workbooks[0];
var sheet = book.ActiveSheet;
exApp.Close();
}
public void GetCustomXmlPartTestNoParts()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
InteropExcel.Workbook workbook = excelApp.Workbooks.Add();
string expected = string.Empty;
string actual;
actual = WorkbookExtensions.GetCustomXmlPart(workbook, Common.Constants.XmlNamespace);
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void GetAffectedNamedRangesTest()
{
InteropExcel.Application application = new InteropExcel.Application();
try
{
InteropExcel.Workbook workbook = application.OpenWorkbook("TestData.xlsx", false);
InteropExcel._Worksheet worksheet = workbook.ActiveSheet as InteropExcel._Worksheet;
Dictionary <string, string> namedRanges = new Dictionary <string, string>();
// Initialize ranges
workbook.Names.GetNamedRange("TestRangeOne").Visible = false;
workbook.Names.GetNamedRange("TestRangeTwo").Visible = false;
workbook.Names.GetNamedRange("TestRangeThree").Visible = false;
workbook.Names.GetNamedRange("TestRangeTarget").Visible = false;
// Build the dictionary of name : address pairs
namedRanges["TestRangeOne"] = workbook.Names.GetNamedRange("TestRangeOne").RefersToRange.Address;
namedRanges["TestRangeTwo"] = workbook.Names.GetNamedRange("TestRangeTwo").RefersToRange.Address;
namedRanges["TestRangeThree"] = workbook.Names.GetNamedRange("TestRangeThree").RefersToRange.Address;
// Get the target range that will be tested for intersection with the above ranges
InteropExcel.Range targetRange = workbook.Names.GetNamedRange("TestRangeTarget").RefersToRange;
// Build the expected output
Dictionary <string, string> expected = new Dictionary <string, string>();
expected["TestRangeTwo"] = namedRanges["TestRangeTwo"];
expected["TestRangeThree"] = namedRanges["TestRangeThree"];
// Get the actual output
Dictionary <string, string> actual;
actual = worksheet.GetAffectedNamedRanges(targetRange, namedRanges);
Assert.AreEqual(expected.Count, actual.Count);
foreach (string rangeName in expected.Keys)
{
Assert.IsTrue(actual.ContainsKey(rangeName));
}
}
finally
{
application.Close();
}
}
public void AddCustomXmlPartTestOnePartInvalidXML()
{
string content = "<Product xmlns:ns=\"" + Common.Constants.XmlNamespace + "\"></Product>" + "<ProductName>WWT Excel Add-In</ProductName>";
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Workbook workbook = excelApp.Workbooks.Add();
workbook.AddCustomXmlPart(content, Common.Constants.XmlNamespace);
Assert.Fail("Invalid XML is inserted in to custom XML part!");
}
finally
{
excelApp.Close();
}
}
public void GetRangeNameForActiveCellTest()
{
InteropExcel.Application application = new InteropExcel.Application();
try
{
InteropExcel.Workbook workbook = application.OpenWorkbook("TestData.xlsx", false);
// Initialize Ranges
workbook.Names.GetNamedRange("TestRangeOne").Visible = false;
workbook.Names.GetNamedRange("TestRangeTwo").Visible = false;
workbook.Names.GetNamedRange("TestRangeThree").Visible = false;
workbook.Names.GetNamedRange("TestRangeTarget").Visible = false;
// Get the target range that will be used to set the active sheet
InteropExcel.Name targetName = workbook.Names.GetNamedRange("TestRangeTarget");
// Activate the worksheet which contains the named range.
((_Worksheet)targetName.RefersToRange.Worksheet).Activate();
InteropExcel._Worksheet worksheet = workbook.ActiveSheet as InteropExcel._Worksheet;
// Select a cell in this sheet
Range activeCell = targetName.RefersToRange.Cells[1, 1];
activeCell.Select();
Dictionary <string, string> namedRanges = new Dictionary <string, string>();
// Build the dictionary of name : address pairs
namedRanges["TestRangeOne"] = workbook.Names.GetNamedRange("TestRangeOne").RefersToRange.Address;
namedRanges["TestRangeTwo"] = workbook.Names.GetNamedRange("TestRangeTwo").RefersToRange.Address;
namedRanges["TestRangeThree"] = workbook.Names.GetNamedRange("TestRangeThree").RefersToRange.Address;
namedRanges["TestRangeTarget"] = workbook.Names.GetNamedRange("TestRangeTarget").RefersToRange.Address;
string expected = "TestRangeTarget";
string actual;
actual = WorksheetExtensions.GetRangeNameForActiveCell(worksheet, activeCell, namedRanges);
Assert.AreEqual(expected, actual);
}
finally
{
application.Close();
}
}
public void IsWWTRangeTestNegative()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Visible = false;
excelApp.Workbooks.Add();
InteropExcel.Workbook workbook = excelApp.ActiveWorkbook;
Name namedRange = workbook.Names.Add("TestNameOne", "A1", true);
bool expected = false;
bool actual = NameExtensions.IsWWTRange(namedRange);
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void IsWWTRangeTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Visible = false;
excelApp.Workbooks.Add();
InteropExcel.Workbook workbook = excelApp.ActiveWorkbook;
Name namedRange = workbook.Names.Add("TestNameOne", "A1", false);
bool expected = true;
bool actual = NameExtensions.IsWWTRange(namedRange);
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void AddCustomXmlPartTestTwoParts()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
InteropExcel.Workbook workbook = excelApp.Workbooks.Add();
string content = "<Product xmlns:ns=\"" + Common.Constants.XmlNamespace + "\">" + "<ProductName>WWT Excel Add-In</ProductName>" + "</Product>";
string moreContent = "<Product xmlns:ns=\"" + Common.Constants.XmlNamespace + "\">" + "<Publisher>Microsoft Corporation</Publisher>" + "</Product>";
workbook.AddCustomXmlPart(content, Common.Constants.XmlNamespace);
workbook.AddCustomXmlPart(moreContent, Common.Constants.XmlNamespace);
string existingContent = workbook.GetCustomXmlPart(Common.Constants.XmlNamespace);
Assert.AreEqual(moreContent, existingContent);
}
finally
{
excelApp.Close();
}
}
public void CreateNamedRangeTestNullArguments()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
InteropExcel.Workbook workbook = excelApp.Workbooks.Add();
string name = null;
Range range = null;
Name expected = null;
Name actual;
actual = workbook.CreateNamedRange(name, range);
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void AddCustomXmlPartTestOnePart()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Workbook workbook = excelApp.Workbooks.Add();
string content = "<Product xmlns:ns=\"" + Common.Constants.XmlNamespace + "\">" + "<ProductName>WWT Excel Add-In</ProductName>" + "</Product>";
workbook.AddCustomXmlPart(content, Common.Constants.XmlNamespace);
string existingContent = workbook.GetCustomXmlPart(Common.Constants.XmlNamespace);
Assert.AreEqual(content, existingContent);
}
finally
{
excelApp.Close();
}
}
public void IsValidSingleAreaTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range range = worksheet.get_Range("A1", Type.Missing);
bool expected = true;
bool actual = range.IsValid();
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void CreateNamedRangeTestWhitespaceArgument()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
InteropExcel.Workbook workbook = excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
string name = " ";
Range range = worksheet.get_Range("A1", Type.Missing);
Name expected = null;
Name actual;
actual = WorkbookExtensions.CreateNamedRange(workbook, name, range);
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void ResetRangeTest()
{
InteropExcel.Application application = new InteropExcel.Application();
try
{
InteropExcel.Workbook book = application.OpenWorkbook("TestData.xlsx", false);
Name name = book.Names.GetNamedRange("TestRangeOne");
LayerMap target = new LayerMap(name);
Name resetRangeName = book.Names.GetNamedRange("TestRangeTarget");
string expected = resetRangeName.Name;
target.ResetRange(resetRangeName);
string actual = target.RangeDisplayName;
Assert.AreEqual(expected, actual);
}
finally
{
application.Close();
}
}
public void SerializeTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
InteropExcel.Workbook workbook = excelApp.ActiveWorkbook;
ViewpointMap viewpointMap = new ViewpointMap(workbook);
viewpointMap.SerializablePerspective = new System.Collections.ObjectModel.ObservableCollection <Perspective>();
viewpointMap.SerializablePerspective.Add(new Perspective("Earth", "Earth", false, "2/25/2011 7:24:01 AM", "1", "9932 km", "SD8834DFA", "30.0", "30.0", "2.0", ".3", "1.5"));
string expected = "<?xml version=\"1.0\" encoding=\"utf-16\"?><ViewpointMap xmlns:d1p1=\"http://schemas.datacontract.org/2004/07/Microsoft.Research.Wwt.Excel.Addin\" xmlns:i=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns=\"Microsoft.Research.Wwt.Excel.Addin.ViewpointMap\"><d1p1:SerializablePerspective xmlns:d2p1=\"http://schemas.datacontract.org/2004/07/Microsoft.Research.Wwt.Excel.Common\"><d2p1:Perspective><d2p1:Declination i:nil=\"true\" /><d2p1:HasRADec>false</d2p1:HasRADec><d2p1:Latitude>2/25/2011 7:24:01 AM</d2p1:Latitude><d2p1:Longitude>1</d2p1:Longitude><d2p1:LookAngle>30.0</d2p1:LookAngle><d2p1:LookAt>Earth</d2p1:LookAt><d2p1:Name i:nil=\"true\" /><d2p1:ObservingTime>30.0</d2p1:ObservingTime><d2p1:ReferenceFrame>Earth</d2p1:ReferenceFrame><d2p1:RightAscention i:nil=\"true\" /><d2p1:Rotation>SD8834DFA</d2p1:Rotation><d2p1:TimeRate>2.0</d2p1:TimeRate><d2p1:ViewToken>1.5</d2p1:ViewToken><d2p1:Zoom>9932 km</d2p1:Zoom><d2p1:ZoomText>.3</d2p1:ZoomText></d2p1:Perspective></d1p1:SerializablePerspective></ViewpointMap>";
string actual;
actual = ViewpointMapExtensions.Serialize(viewpointMap);
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void GetHeaderInvalidRangeTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range range = worksheet.get_Range("A1", "I7");
Range anotherRange = worksheet.get_Range("B10", "J15");
Range mergedRange = excelApp.Union(range, anotherRange);
Collection <string> actual = mergedRange.GetHeader();
Assert.AreEqual(0, actual.Count);
}
finally
{
excelApp.Close();
}
}
public void HasChangedNullArgumentsTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range range = worksheet.get_Range("A1", Type.Missing);
Range target = null;
bool expected = false;
bool actual = range.HasChanged(target);
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void HasChangedIntersectionNegativeSingleAreaTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range range = worksheet.get_Range("A1", "I7");
Range target = worksheet.get_Range("A9", "I15");
bool expected = false;
bool actual = range.HasChanged(target);
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void AddCustomXmlPartTestEmptyString()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Workbook workbook = excelApp.Workbooks.Add();
string content = string.Empty;
int expected = workbook.CustomXMLParts.Count;
workbook.AddCustomXmlPart(content, Common.Constants.XmlNamespace);
Assert.AreEqual(workbook.CustomXMLParts.Count, expected);
string existingContent = workbook.GetCustomXmlPart(Common.Constants.XmlNamespace);
Assert.AreEqual(content, existingContent);
}
finally
{
excelApp.Close();
}
}
public void HasChangedRangesOnDifferentWorksheetsTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
InteropExcel.Workbook workbook = excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range range = worksheet.get_Range("A1", "I7");
Worksheet secondSheet = workbook.Worksheets[2];
Range target = secondSheet.get_Range("C1", "E5");
bool expected = false;
bool actual = range.HasChanged(target);
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void AddCustomXmlPartTestEmptyString()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Workbook workbook = excelApp.Workbooks.Add();
string content = string.Empty;
int expected = workbook.CustomXMLParts.Count;
workbook.AddCustomXmlPart(content, Common.Constants.XmlNamespace);
Assert.AreEqual(workbook.CustomXMLParts.Count, expected);
string existingContent = workbook.GetCustomXmlPart(Common.Constants.XmlNamespace);
Assert.AreEqual(content, existingContent);
}
finally
{
excelApp.Close();
}
}
public void GetDataArrayInvalidRangeTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range range = worksheet.get_Range("A1", "I7");
Range test = worksheet.get_Range("B10", "J15");
Range mergedRange = excelApp.Union(range, test);
object[,] expected = null;
object[,] actual = mergedRange.GetDataArray(false);
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void GetDataArrayInvalidRangeTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range range = worksheet.get_Range("A1", "I7");
Range test = worksheet.get_Range("B10", "J15");
Range mergedRange = excelApp.Union(range, test);
object[,] expected = null;
object[,] actual = mergedRange.GetDataArray(false);
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void IsValidTwoAreasPositiveTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range range = worksheet.get_Range("A1", "I7");
bool expected = true;
Range test = worksheet.get_Range("A10", "I15");
Range mergedRange = excelApp.Union(range, test);
bool actual = mergedRange.IsValid();
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void GetNamedRangeTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Visible = false;
excelApp.Workbooks.Add();
InteropExcel.Workbook workbook = excelApp.ActiveWorkbook;
workbook.Names.Add("TestNameOne", "A1", false);
workbook.Names.Add("TestNameTwo", "A2", false);
Names namedCollection = workbook.Names;
string expected = "TestNameOne";
Name expectedName = workbook.Names.GetNamedRange("TestNameOne");
Name actualName;
actualName = NameExtensions.GetNamedRange(namedCollection, expected);
Assert.AreEqual(expectedName, actualName);
}
finally
{
excelApp.Close();
}
}
public void GetData()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
InteropExcel.Workbook workbook = excelApp.OpenWorkbook("TestData.xlsx", false);
// Get the target range
InteropExcel.Name targetName = workbook.Names.GetNamedRange("DataRangeTwo");
string[] expected = new string[] { "23\t34\t3\t3/7/2011 12:00:00 AM\r\n", "45\t63\t4.6\t3/7/2011 1:39:45 PM\r\n67\t32\t5.3\t2/1/2009 12:00:00 AM\r\n" };
string[] actual = targetName.RefersToRange.GetData();
Assert.AreEqual(expected.Length, actual.Length);
Assert.AreEqual(expected[0], actual[0]);
Assert.AreEqual(expected[1], actual[1]);
}
finally
{
excelApp.Close();
}
}
public void GetData()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
InteropExcel.Workbook workbook = excelApp.OpenWorkbook("TestData.xlsx", false);
// Get the target range
InteropExcel.Name targetName = workbook.Names.GetNamedRange("DataRangeTwo");
string[] expected = new string[] { "23\t34\t3\t3/7/2011 12:00:00 AM\r\n", "45\t63\t4.6\t3/7/2011 1:39:45 PM\r\n67\t32\t5.3\t2/1/2009 12:00:00 AM\r\n" };
string[] actual = targetName.RefersToRange.GetData();
Assert.AreEqual(expected.Length, actual.Length);
Assert.AreEqual(expected[0], actual[0]);
Assert.AreEqual(expected[1], actual[1]);
}
finally
{
excelApp.Close();
}
}
public void DeserializeTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
InteropExcel.Workbook workbook = excelApp.ActiveWorkbook;
ViewpointMap expected = new ViewpointMap(workbook);
expected.SerializablePerspective = new System.Collections.ObjectModel.ObservableCollection<Perspective>();
expected.SerializablePerspective.Add(new Perspective("Earth", "Earth", false, "2/25/2011 7:24:01 AM", "1", "9932 km", "SD8834DFA", "30.0", "30.0", "2.0", ".3", "1.5"));
ViewpointMap viewpointMap = new ViewpointMap(workbook);
string xmlContent = "<?xml version=\"1.0\" encoding=\"utf-16\"?><ViewpointMap xmlns:d1p1=\"http://schemas.datacontract.org/2004/07/Microsoft.Research.Wwt.Excel.Addin\" xmlns:i=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns=\"Microsoft.Research.Wwt.Excel.Addin.ViewpointMap\"><d1p1:SerializablePerspective xmlns:d2p1=\"http://schemas.datacontract.org/2004/07/Microsoft.Research.Wwt.Excel.Common\"><d2p1:Perspective><d2p1:Declination i:nil=\"true\" /><d2p1:HasRADec>false</d2p1:HasRADec><d2p1:Latitude>2/25/2011 7:24:01 AM</d2p1:Latitude><d2p1:Longitude>1</d2p1:Longitude><d2p1:LookAngle>30.0</d2p1:LookAngle><d2p1:LookAt>Earth</d2p1:LookAt><d2p1:Name i:nil=\"true\" /><d2p1:ObservingTime>30.0</d2p1:ObservingTime><d2p1:ReferenceFrame>Earth</d2p1:ReferenceFrame><d2p1:RightAscention i:nil=\"true\" /><d2p1:Rotation>SD8834DFA</d2p1:Rotation><d2p1:TimeRate>2.0</d2p1:TimeRate><d2p1:ViewToken>1.5</d2p1:ViewToken><d2p1:Zoom>9932 km</d2p1:Zoom><d2p1:ZoomText>.3</d2p1:ZoomText></d2p1:Perspective></d1p1:SerializablePerspective></ViewpointMap>";
ViewpointMap actual;
actual = ViewpointMapExtensions.Deserialize(viewpointMap, xmlContent);
Assert.AreEqual(expected.SerializablePerspective[0].Name, actual.SerializablePerspective[0].Name);
}
finally
{
excelApp.Close();
}
}
public void HasChangedNullArgumentsTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range range = worksheet.get_Range("A1", Type.Missing);
Range target = null;
bool expected = false;
bool actual = range.HasChanged(target);
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void GetFirstDataRowTwoAreasTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
InteropExcel.Workbook workbook = excelApp.OpenWorkbook("TestData.xlsx", false);
// Get the target range that will be used to set the active sheet
InteropExcel.Name targetName = workbook.Names.GetNamedRange("DataRangeTwo");
Range range = targetName.RefersToRange;
Collection<string> expected = new Collection<string>() { "45", "63", "4.6", "3/7/2011 1:39:45 PM" };
Collection<string> actual;
actual = range.GetFirstDataRow();
Assert.AreEqual(expected.Count, actual.Count);
for (int i = 0; i < expected.Count; i++)
{
Assert.AreEqual(expected[i], actual[i]);
}
}
finally
{
excelApp.Close();
}
}
public void HasChangedInvalidRangeTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range range = worksheet.get_Range("A1", "I7");
Range test = worksheet.get_Range("B10", "J15");
Range mergedRange = excelApp.Union(range, test);
Range target = worksheet.get_Range("A1", Type.Missing);
bool expected = false;
bool actual = mergedRange.HasChanged(target);
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void GetHeaderNullDataTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range range = worksheet.get_Range("A1", "B1");
range.Value2 = null;
Collection<string> expected = new Collection<string>() { string.Empty, string.Empty };
Collection<string> actual = range.GetHeader();
Assert.AreEqual(actual.Count, 2);
Assert.AreEqual(expected[0], actual[0]);
Assert.AreEqual(expected[1], actual[1]);
}
finally
{
excelApp.Close();
}
}
public void CreateNamedRangeTestNullArguments()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
InteropExcel.Workbook workbook = excelApp.Workbooks.Add();
string name = null;
Range range = null;
Name expected = null;
Name actual;
actual = workbook.CreateNamedRange(name, range);
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void GetDataTwoCellsTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range range = worksheet.get_Range("A1", "A2");
Range cell = range.Cells[1, 1];
cell.Value = "TestValueOne";
cell = range.Cells[2, 1];
cell.Value = "TestValueTwo";
string[] expected = new string[] { "TestValueOne" + Environment.NewLine + "TestValueTwo" + Environment.NewLine };
string[] actual = range.GetData();
Assert.AreEqual(actual.Length, 1);
Assert.AreEqual(expected[0], actual[0]);
}
finally
{
excelApp.Close();
}
}
public void IsValidTwoAreasPositiveTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range range = worksheet.get_Range("A1", "I7");
bool expected = true;
Range test = worksheet.get_Range("A10", "I15");
Range mergedRange = excelApp.Union(range, test);
bool actual = mergedRange.IsValid();
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void HasChangedIntersectionPositiveSingleAndMultipleAreasTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range areaOne = worksheet.get_Range("A1", "I7");
Range areaTwo = worksheet.get_Range("A10", "I15");
Range mergedRange = excelApp.Union(areaOne, areaTwo);
Range areaThree = worksheet.get_Range("D12", Type.Missing);
Range mergedWithSingle = excelApp.Union(mergedRange, areaThree);
Range target = worksheet.get_Range("A11", "I13");
bool expected = true;
bool actual = mergedWithSingle.HasChanged(target);
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void GetValidNameTest()
{
InteropExcel.Application application = new InteropExcel.Application();
try
{
InteropExcel.Workbook workbook = application.OpenWorkbook("TestData.xlsx", false);
// Get the target range that will be used to set the active sheet
InteropExcel.Name targetName = workbook.Names.GetNamedRange("GetValidName");
// Activate the worksheet which contains the named range.
((_Worksheet)targetName.RefersToRange.Worksheet).Activate();
string name = ((_Worksheet)targetName.RefersToRange.Worksheet).Name;
string expected = "WWTLayer_012TestS";
string actual;
actual = WorkbookExtensions_Accessor.GetValidName(name);
Assert.AreEqual(expected, actual);
}
finally
{
application.Close();
}
}
public void GetSelectionRangeNameWithZeroIndexTest()
{
InteropExcel.Application application = new InteropExcel.Application();
try
{
InteropExcel.Workbook workbook = application.OpenWorkbook("TestData.xlsx", false);
// Get the target range that will be used to set the active sheet
InteropExcel.Name targetName = workbook.Names.GetNamedRange("TestRangeOne");
// Activate the worksheet which contains the named range.
((_Worksheet)targetName.RefersToRange.Worksheet).Activate();
string expected = string.Format(CultureInfo.InvariantCulture, "{0}_{1}", ((_Worksheet)targetName.RefersToRange.Worksheet).Name, "1");
string actual;
actual = WorkbookExtensions.GetSelectionRangeName(workbook);
Assert.AreEqual(expected, actual);
}
finally
{
application.Close();
}
}
public void HasChangedRangesOnDifferentWorksheetsTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
InteropExcel.Workbook workbook = excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range range = worksheet.get_Range("A1", "I7");
Worksheet secondSheet = workbook.Worksheets[2];
Range target = secondSheet.get_Range("C1", "E5");
bool expected = false;
bool actual = range.HasChanged(target);
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void CreateNamedRangeTestWhitespaceArgument()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
InteropExcel.Workbook workbook = excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
string name = " ";
Range range = worksheet.get_Range("A1", Type.Missing);
Name expected = null;
Name actual;
actual = WorkbookExtensions.CreateNamedRange(workbook, name, range);
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void GetHeaderSingleCellDataTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range range = worksheet.get_Range("A1", Type.Missing);
range.Value2 = "TestValue";
Collection<string> expected = new Collection<string>() { "TestValue" };
Collection<string> actual = range.GetHeader();
Assert.AreEqual(expected.Count, actual.Count);
Assert.AreEqual(expected[0], actual[0]);
}
finally
{
excelApp.Close();
}
}
public void GetDataArrayMultipleCellRangeTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range range = worksheet.get_Range("A1", "B1");
Range cellOne = range.Cells[1, 1];
cellOne.Value = "TestValue1";
Range cellTwo = range.Cells[1, 2];
cellTwo.Value = "TestValue2";
object[,] expected = (object[,])Array.CreateInstance(typeof(object), new int[] { 1, 2 }, new int[] { 1, 1 });
expected[1, 1] = "TestValue1";
expected[1, 2] = "TestValue2";
object[,] actual = range.GetDataArray(false);
Assert.AreEqual(expected.Length, actual.Length);
Assert.AreEqual(expected[1, 1], actual[1, 1]);
Assert.AreEqual(expected[1, 2], actual[1, 2]);
}
finally
{
excelApp.Close();
}
}
public void AddCustomXmlPartTestOnePartInvalidXML()
{
string content = "<Product xmlns:ns=\"" + Common.Constants.XmlNamespace + "\"></Product>" + "<ProductName>WWT Excel Add-In</ProductName>";
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Workbook workbook = excelApp.Workbooks.Add();
workbook.AddCustomXmlPart(content, Common.Constants.XmlNamespace);
Assert.Fail("Invalid XML is inserted in to custom XML part!");
}
finally
{
excelApp.Close();
}
}
public void IsValidSingleAreaTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range range = worksheet.get_Range("A1", Type.Missing);
bool expected = true;
bool actual = range.IsValid();
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void GetDataArraySingleCellRangeTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range range = worksheet.get_Range("A1", Type.Missing);
range.Value2 = "TestValue";
object[,] expected = (object[,])Array.CreateInstance(typeof(object), new int[] { 1, 1 }, new int[] { 1, 1 });
expected[1, 1] = "TestValue";
object[,] actual = range.GetDataArray(false);
Assert.AreEqual(expected.Length, actual.Length);
Assert.AreEqual(expected[1, 1], actual[1, 1]);
}
finally
{
excelApp.Close();
}
}
public void GetDataFromRangePositiveTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range range = worksheet.get_Range("A1", "B3");
Range cell = range.Cells[1, 1];
cell.Value = "HeaderValueOne";
cell = range.Cells[1, 2];
cell.Value = "HeaderValueTwo";
cell = range.Cells[2, 1];
cell.Value = "DataValueOne";
cell = range.Cells[2, 2];
cell.Value = "DataValueTwo";
cell = range.Cells[3, 1];
cell.Value = "DataValueThree";
cell = range.Cells[3, 2];
cell.Value = "DataValueFour";
string expected = "HeaderValueOne\tHeaderValueTwo" + Environment.NewLine + "DataValueOne\tDataValueTwo" + Environment.NewLine + "DataValueThree\tDataValueFour" + Environment.NewLine;
string actual = RangeExtensions_Accessor.GetDataFromRange(range);
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void HasChangedIntersectionNegativeMultipleAreasTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
// worksheet names
Range areaOne = worksheet.get_Range("A1", "I7");
Range areaTwo = worksheet.get_Range("A10", "I15");
Range mergedRange = excelApp.Union(areaOne, areaTwo);
Range target = worksheet.get_Range("A8", "I8");
bool expected = false;
bool actual = mergedRange.HasChanged(target);
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void ColumnsListTest()
{
InteropExcel.Application application = new InteropExcel.Application();
try
{
InteropExcel.Workbook book = application.OpenWorkbook("TestData.xlsx", false);
// Get the named range stored in the test data excel file.
Name name = book.Names.GetNamedRange("ColumnList");
LayerMap target = new LayerMap(name);
target.ColumnsList = ColumnExtensions.PopulateColumnList();
// Check the count of columns.
Assert.AreEqual(target.ColumnsList.Count, 19);
// Check the column names
Assert.AreEqual(target.ColumnsList[0].ColumnDisplayValue, "Select One");
Assert.AreEqual(target.ColumnsList[1].ColumnDisplayValue, "Latitude");
Assert.AreEqual(target.ColumnsList[2].ColumnDisplayValue, "Longitude");
Assert.AreEqual(target.ColumnsList[3].ColumnDisplayValue, "Start Date");
Assert.AreEqual(target.ColumnsList[4].ColumnDisplayValue, "End Date");
Assert.AreEqual(target.ColumnsList[5].ColumnDisplayValue, "Depth");
Assert.AreEqual(target.ColumnsList[6].ColumnDisplayValue, "Altitude");
Assert.AreEqual(target.ColumnsList[7].ColumnDisplayValue, "Distance");
Assert.AreEqual(target.ColumnsList[8].ColumnDisplayValue, "Magnitude");
Assert.AreEqual(target.ColumnsList[9].ColumnDisplayValue, "Geometry");
Assert.AreEqual(target.ColumnsList[10].ColumnDisplayValue, "Color");
Assert.AreEqual(target.ColumnsList[11].ColumnDisplayValue, "RA");
Assert.AreEqual(target.ColumnsList[12].ColumnDisplayValue, "Dec");
}
finally
{
application.Close();
}
}
public void HasChangedIntersectionPositiveSingleAreaTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range range = worksheet.get_Range("A1", "I7");
Range target = worksheet.get_Range("C1", "E5");
bool expected = true;
bool actual = range.HasChanged(target);
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void GetCustomXmlPartTestNoParts()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
InteropExcel.Workbook workbook = excelApp.Workbooks.Add();
string expected = string.Empty;
string actual;
actual = WorkbookExtensions.GetCustomXmlPart(workbook, Common.Constants.XmlNamespace);
Assert.AreEqual(expected, actual);
}
finally
{
excelApp.Close();
}
}
public void GetHeaderInvalidRangeTest()
{
InteropExcel.Application excelApp = new InteropExcel.Application();
try
{
excelApp.Workbooks.Add();
InteropExcel.Worksheet worksheet = excelApp.ActiveSheet;
Range range = worksheet.get_Range("A1", "I7");
Range anotherRange = worksheet.get_Range("B10", "J15");
Range mergedRange = excelApp.Union(range, anotherRange);
Collection<string> actual = mergedRange.GetHeader();
Assert.AreEqual(0, actual.Count);
}
finally
{
excelApp.Close();
}
}