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