C# Application.OpenWorkbook方法代码示例

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