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(); } }
void app_SheetSelectionChange(object Sh, Microsoft.Office.Interop.Excel.Range Target) { //MessageBox.Show("In Sheet Selection Change"); string sheetName = ""; string rangeName = ""; try { if ((Excel.Worksheet)Sh is Excel.Worksheet) { Excel.Worksheet ws = (Excel.Worksheet)Sh; sheetName = ws.Name; Excel.Name nm = (Excel.Name)Target.Name; rangeName = nm.Name; notifyRangeSelected(rangeName); } } catch (Exception e) { string errorMsg = e.Message; string donothing_removewarning = "error: " + errorMsg; //will error when a named range is not selected as name does not exist for other/individual cells //MessageBox.Show("ERROR: " + sheetName+ " "+donothing_removewarning); } //notifyRangeSelected(rangeName); }
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 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(); } }
/// <summary> /// Name a cell, and replace all references to it with the name /// </summary> /// <exception cref="AggregateException">If any cells could not be inlined, with as innerexceptions the individual errors.</exception> public void Refactor(ExcelRaw.Range toName, string name) { ParseTreeNode parse; try { parse = Helper.Parse(name); } catch (ArgumentException) { // Parse error throw new ArgumentException($"Name {name} is not a valid name for a named range"); } parse = parse.SkipToRelevant(true); if (!parse.Is(GrammarNames.NamedRange)) { throw new ArgumentException($"Name {name} is not a valid name for a named range, because Excel interpets it as a {parse.Type()}"); } // Set the name var Scope = toName.Worksheet; var Names = Scope.Names; ExcelRaw.Name newName = Names.Add(name, toName); Marshal.ReleaseComObject(newName); Marshal.ReleaseComObject(Names); Marshal.ReleaseComObject(Scope); // Perform refactoring var ctx = toName.CreateContext(); Inline(toName, ctx, ctx.Parse(name)); }
private void DrawGraphFromSelectedCells(int graphRow, int graphColumn, string title, string valueName, Worksheet worksheet) { bool screenUpdating = Application.ScreenUpdating; Application.ScreenUpdating = false; Excel.Application exApp = Globals.ThisAddIn.Application; Excel.Range selectedRange = exApp.Selection; long row = selectedRange.Row; long col = selectedRange.Column; Worksheet activeWorksheet = Globals.Factory.GetVstoObject(Application.ActiveWorkbook.ActiveSheet); string name = GetGraphName(activeWorksheet); Chart chart = activeWorksheet.Controls.AddChart(activeWorksheet.Range["A1"].Resize[_GRAPH_HEIGHT, _GRAPH_WIDTH] .Offset[graphRow * _GRAPH_HEIGHT, graphColumn * _GRAPH_WIDTH], name); chart.ChartType = Excel.XlChartType.xlXYScatterLinesNoMarkers; chart.SetSourceData(selectedRange, Excel.XlRowCol.xlColumns); chart.HasTitle = true; chart.ChartTitle.Text = title; Excel.Axis xAxis = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlCategory); xAxis.HasTitle = true; xAxis.AxisTitle.Text = activeWorksheet.Cells[row, col].Text; Excel.Axis yAxis = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlValue); yAxis.HasTitle = true; yAxis.AxisTitle.Text = valueName; xAxis.MinimumScaleIsAuto = false; Excel.Name newName = activeWorksheet.Names.Add("firstCol", selectedRange.Resize[selectedRange.Rows.Count, 1], false); xAxis.MinimumScale = (double)activeWorksheet.Evaluate("MIN(firstCol)"); xAxis.MaximumScale = (double)activeWorksheet.Evaluate("MAX(firstCol)"); newName.Delete(); newName = activeWorksheet.Names.Add("col", selectedRange.Resize[selectedRange.Rows.Count, 1].Offset[missing, 1], false); double maxValue = (double)activeWorksheet.Evaluate("MAX(col)"); double minValue = (double)activeWorksheet.Evaluate("MIN(col)"); newName.Delete(); for (int i = 2; i < selectedRange.Columns.Count; ++i) { newName = activeWorksheet.Names.Add("col", selectedRange.Resize[selectedRange.Rows.Count, 1].Offset[missing, i], false); double maxValueNow = (double)activeWorksheet.Evaluate("MAX(col)"); double minValueNow = (double)activeWorksheet.Evaluate("MIN(col)"); newName.Delete(); if (maxValueNow > maxValue) { maxValue = maxValueNow; } if (minValueNow < minValue) { minValue = minValueNow; } } yAxis.MinimumScaleIsAuto = false; yAxis.MinimumScale = minValue; yAxis.MaximumScale = maxValue; chart.Location(Excel.XlChartLocation.xlLocationAsObject, worksheet.Name); activeWorksheet.Activate(); Application.ScreenUpdating = screenUpdating; }
private bool IsNovenaNamedRange(MSExcel.Name namedRange) { // get first 7 characters of named range var endIndex = namedRange.Name.IndexOf("__") + 2; var first7Chars = namedRange.Name.Substring(0, endIndex).ToLower(); return((first7Chars == "novena__") ? true : false); }
/// <summary> /// Returns the range with the given name from the workbook. /// </summary> /// <param name="workbook">The workbook containing the named range.</param> /// <param name="name">The name of the desired range.</param> /// <returns>The range with the given name from the workbook.</returns> internal static Excel.Range GetNamedRange(Excel.Workbook workbook, string name) { Excel.Name nameObject = workbook.Names.Item( name, Type.Missing, Type.Missing); return(nameObject.RefersToRange); }
/// <summary> /// Returns the name of an Excel.Name object without the sheet prefix, if any /// </summary> /// <param name="name">Excel.Name object</param> /// <returns></returns> public static string ShortName(this Excel.Name name) { if (name.Name.Contains('!')) { return(name.Name.Split('!').Last()); } else { return(name.Name); } }
private string ParseColumnFromNamedRange(MSExcel.Name namedRange) { // get column name from named range's name var startIndex = namedRange.Name.IndexOf("__"); if (startIndex != -1) { // if novena return(namedRange.Name.Substring(startIndex + 1)); } else { return(null); } // return column name }
public override void RealDispose() { if (rangeName != null) { rangeName.Delete(); } if (NestedContextItem != null) { NestedContextItem.Dispose(); } ExcelApplication.ReleaseComObject(rangeName); ExcelApplication.ReleaseComObject(Range); rangeName = null; Range = null; }
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 CreateControl(ExcelInterop.Range range) { ExcelInterop.Worksheet workSheet = null; try { Range = range; workSheet = Range.Worksheet; if (!string.IsNullOrEmpty(name)) { ExcelInterop.Names names = null; try { names = workSheet.Names; rangeName = names.Add(name, Range); } catch (COMException ex) { throw new EtkException($"Cannot create named caller '{name}': {ex.Message}"); } finally { if (names != null) { ExcelApplication.ReleaseComObject(names); names = null; } } } if (NestedContextItem != null && NestedContextItem is IExcelControl) { ((IExcelControl)NestedContextItem).CreateControl(range); } } finally { if (workSheet != null) { ExcelApplication.ReleaseComObject(workSheet); workSheet = null; } } }
private string GetNameValue(Excel.Workbook wb, string Name) { Excel.Name n1 = null; try { n1 = wb.Names.Item(Name); string value = n1.RefersToRange.Value2.ToString().Trim(); return(value); } catch { return(null); } finally { Marshal.FinalReleaseComObject(n1); } }
// возвращаем диапазон именованных ячеек public static Excel.Range NamedRange(Excel.Workbook wBook, string Name) { Excel.Range result = null; if ((Name != null) && (Name != "") && (wBook != null) && (wBook.Names != null) && (wBook.Names.Count > 0)) { foreach (var item in wBook.Names) { Excel.Name namedRange = (Excel.Name)item; if (namedRange.Name.ToLower() == Name.ToLower()) { result = namedRange.RefersToRange; } } } return(result); }
private static string existingName(ExcelRaw.Range range) { try { ExcelRaw.Name eName = range.Name; string name = eName.Name; Marshal.ReleaseComObject(eName); // Remove sheet name var exclmarkIndex = name.IndexOf("!"); if (exclmarkIndex >= 0) { name = name.Substring(exclmarkIndex + 1); } return(name); } catch (COMException) { return(""); } }
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 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(); } }
/// <summary> /// Cria validações de dados nas colunas de referência a tabelas /// </summary> /// <param name="eBook">workbook correspondente do excel (COM)</param> /// <param name="xWorkbook">xworkbook</param> private Excel.Workbook createValidation(Excel.Workbook eBook, XWorkbook xWorkbook) { foreach (XWorksheet xWorksheet in xWorkbook.getWorksheets()) { Excel.Worksheet eWorksheet = eBook.Sheets[xWorksheet.getName()]; foreach (XDataTable xDataTable in xWorksheet.getDataTables()) { Excel.ListObjects eListObjects = eWorksheet.ListObjects; Excel.ListObject eListObject = eListObjects[xDataTable.getName()]; foreach (XDataTableColumn sheetColumn in xDataTable.getDataTableColumns()) { Excel.ListColumns eListColumns = eListObject.ListColumns; Excel.ListColumn eListColumn = eListColumns[sheetColumn.getName()]; string xreference = sheetColumn.getXReference(); if (xreference != null) { var targettable = new XDataTable(); var targetsheet = GetXReference(xWorkbook, xreference, ref targettable); if (targettable != null) { var index = targettable.getKeyIndex(); eWorksheet = eBook.Sheets[targetsheet.getName()]; eListObjects = eWorksheet.ListObjects; eListObject = eListObjects[targettable.getName()]; eListColumns = eListObject.ListColumns; eListColumn = eListColumns[index]; string rangename = targettable.getName() + index.ToString(CultureInfo.InvariantCulture); eBook.Names.Add(rangename, eListColumn.DataBodyRange); Excel.Name targetName = eBook.Names.Item(rangename, Type.Missing, Type.Missing); string nameLocal = "=" + targetName.NameLocal; eWorksheet = eBook.Sheets[xWorksheet.getName()]; eListObjects = eWorksheet.ListObjects; eListObject = eListObjects[xDataTable.getName()]; eListColumns = eListObject.ListColumns; eListColumn = eListColumns[sheetColumn.getName()]; eListColumn.DataBodyRange.Validation.Add(Excel.XlDVType.xlValidateList, Excel.XlDVAlertStyle.xlValidAlertStop, Missing.Value, nameLocal, Missing.Value); } Marshal.ReleaseComObject(eListColumn); } Marshal.ReleaseComObject(eListColumn); Marshal.ReleaseComObject(eListColumns); } Marshal.ReleaseComObject(eListObject); Marshal.ReleaseComObject(eListObjects); } Marshal.ReleaseComObject(eWorksheet); } return(eBook); }
public static void DeleteNamesWithExcelApp(String[] tps) { Excel.Workbooks xlWorkbooks = null; Excel.Workbook xlWorkbook = null; Excel.Names ranges = null; var xlApp = new Excel.Application(); try { foreach (String m in tps) { xlWorkbooks = xlApp.Workbooks; xlWorkbook = xlWorkbooks.Open(m); ranges = xlWorkbook.Names; int leftoveritems = ranges.Count; Excel.Name name = null; try { for (int i = leftoveritems; i >= 1; i--) { name = xlWorkbook.Names.Item(i); name.Delete(); if (name != null) { Marshal.ReleaseComObject(name); } name = null; } } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { if (name != null) { Marshal.ReleaseComObject(name); } } if (xlWorkbook != null) { xlWorkbook.Close(true); Marshal.ReleaseComObject(xlWorkbook); xlWorkbook = null; } if (xlWorkbooks != null) { Marshal.ReleaseComObject(xlWorkbooks); } } } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { if (ranges != null) { Marshal.ReleaseComObject(ranges); } if (xlWorkbook != null) { Marshal.ReleaseComObject(xlWorkbook); } if (xlWorkbooks != null) { Marshal.ReleaseComObject(xlWorkbooks); } if (xlApp != null) { xlApp.Quit(); Marshal.ReleaseComObject(xlApp); xlApp = null; } } }
/// <summary> /// Retrieve worksheet and name range names. /// </summary> /// <returns></returns> /// <remarks></remarks> public bool GetInformation() { bool Success = true; if (!(System.IO.File.Exists(FileName))) { Exception ex = new Exception("Failed to locate '" + FileName + "'"); this.LastException = ex; throw ex; } mSheets.Clear(); mNameRanges.Clear(); mSheetsData.Clear(); if (mReferenceTables != null) { mReferenceTables.Clear(); } Excel.Application xlApp = null; Excel.Workbooks xlWorkBooks = null; Excel.Workbook xlWorkBook = null; Excel.Workbook xlActiveRanges = null; Excel.Names xlNames = null; Excel.Sheets xlWorkSheets = null; try { xlApp = new Excel.Application(); xlApp.DisplayAlerts = false; xlWorkBooks = xlApp.Workbooks; xlWorkBook = xlWorkBooks.Open(FileName); xlActiveRanges = xlApp.ActiveWorkbook; xlNames = xlActiveRanges.Names; for (int x = 1; x <= xlNames.Count; x++) { Excel.Name xlName = xlNames.Item(x); mNameRanges.Add(xlName.Name); Marshal.FinalReleaseComObject(xlName); xlName = null; } xlWorkSheets = xlWorkBook.Sheets; for (int x = 1; x <= xlWorkSheets.Count; x++) { Excel.Worksheet Sheet1 = (Excel.Worksheet)xlWorkSheets[x]; mSheets.Add(Sheet1.Name); mSheetsData.Add(x, Sheet1.Name); Marshal.FinalReleaseComObject(Sheet1); Sheet1 = null; } GetReferenceTables(xlWorkSheets); ReleaseComObject(xlWorkSheets); xlWorkBook.Close(); xlApp.UserControl = true; xlApp.Quit(); } catch (Exception ex) { this.LastException = ex; Success = false; } finally { if (xlWorkSheets != null) { Marshal.FinalReleaseComObject(xlWorkSheets); xlWorkSheets = null; } if (xlNames != null) { Marshal.FinalReleaseComObject(xlNames); xlNames = null; } if (xlActiveRanges != null) { Marshal.FinalReleaseComObject(xlActiveRanges); xlActiveRanges = null; } if (xlActiveRanges != null) { Marshal.FinalReleaseComObject(xlActiveRanges); xlActiveRanges = null; } if (xlWorkBook != null) { Marshal.FinalReleaseComObject(xlWorkBook); xlWorkBook = null; } if (xlWorkBooks != null) { Marshal.FinalReleaseComObject(xlWorkBooks); xlWorkBooks = null; } if (xlApp != null) { Marshal.FinalReleaseComObject(xlApp); xlApp = null; } } return(Success); }