internal static Name GetName(Range targetRange)
{
ExcelUIThreadProtecter.CheckIsExcelUIMainThread();
Name result = null;
if (targetRange != null)
{
Worksheet ws = targetRange.Worksheet;
Workbook wb = ws.Parent;
Names allNames = wb.Names;
Name item = null;
Range itemRange = null;
for (int i = 1; i <= allNames.Count; i++) //VB start from 1
{
item = allNames.Item(i);
itemRange = GetRangeOfName(item);
if (IsRangeOverlap(itemRange, targetRange) == true)
{
result = item;
break;
}
}
}
return(result);
}
internal static Range GetRange(Worksheet ws, string rangeName)
{
ExcelUIThreadProtecter.CheckIsExcelUIMainThread();
Range result = null;
if (ws != null && string.IsNullOrEmpty(rangeName) == false)
{
Workbook wk = ws.Parent;
Name namedName = GetName(wk, rangeName);
if (namedName != null)
{
result = GetRangeOfName(namedName);
}
if (result == null)
{
ListObject lo = GetListObject(ws, rangeName);
if (lo != null)
{
result = GetListObjectRange(lo);
}
}
}
return(result);
}
internal static GoogleHistory GetHistoryByRange(Range targetRange)
{
ExcelUIThreadProtecter.CheckIsExcelUIMainThread();
GoogleHistory result = null;
string rangeName = MainThreadLogic.RangeManager.GetRangeName(targetRange);
if (string.IsNullOrEmpty(rangeName) == false)
{
result = ExcelWvvm.Entities.GoogleHistories.GetByRangeName(rangeName);
}
return(result);
}
internal static string GetWorksheetPropertyString(Worksheet ws, string name)
{
ExcelUIThreadProtecter.CheckIsExcelUIMainThread();
string result = string.Empty;
dynamic cp = GetWorksheetCusotmProperty(ws, name);
if (cp != null)
{
result = cp.Value;
}
return(result);
}
internal static void SetWorksheetProperty(Worksheet ws, string propertyName, string propertyValue)
{
ExcelUIThreadProtecter.CheckIsExcelUIMainThread();
dynamic cp = GetWorksheetCusotmProperty(ws, propertyName);
if (cp == null)
{
CustomProperties cps = ws.CustomProperties;
cps.Add(propertyName, propertyValue);
}
else
{
cp.Value = propertyValue;
}
}
internal static void SetWorkbookProperty(Workbook wk, string propertyName, string propertyValue)
{
ExcelUIThreadProtecter.CheckIsExcelUIMainThread();
dynamic cp = GetWorkbookCustomProperty(wk, propertyName);
if (cp == null)
{
dynamic cps = wk.CustomDocumentProperties;
cps.Add(propertyName, false, MsoDocProperties.msoPropertyTypeString, propertyValue);
}
else
{
cp.Value = propertyValue;
}
}
internal static Range GetListObjectRange(ListObject listObject)
{
ExcelUIThreadProtecter.CheckIsExcelUIMainThread();
Range result = null;
if (listObject != null)
{
try
{
result = listObject.Range;
}
catch //ignore error here
{
}
}
return(result);
}
internal static ListObject GetListObject(Worksheet sheet, string rangeName)
{
ExcelUIThreadProtecter.CheckIsExcelUIMainThread();
ListObject result = null;
if (sheet != null && string.IsNullOrEmpty(rangeName) == false)
{
try
{
result = sheet.ListObjects[rangeName];
}
catch //ignore error here
{
}
}
return(result);
}
internal static Range GetRangeOfName(Name name)
{
ExcelUIThreadProtecter.CheckIsExcelUIMainThread();
Range result = null;
if (name != null)
{
try
{
result = name.RefersToRange;
}
catch //ignore error here
{
}
}
return(result);
}
internal static Name GetName(Workbook wk, string rangeName)
{
ExcelUIThreadProtecter.CheckIsExcelUIMainThread();
Name result = null;
if (wk != null && string.IsNullOrEmpty(rangeName) == false)
{
try
{
result = wk.Names.Item(rangeName);
}
catch //ignore error here
{
}
}
return(result);
}
internal static void ShowRefreshingComment(GoogleHistory history, string commnet = "Refreshing...")
{
ExcelUIThreadProtecter.CheckIsExcelUIMainThread();
Worksheet ws = ExcelApp.Application.ActiveSheet;
Range targetRange = MainThreadLogic.RangeManager.GetRange(ws, history.RangeName);
if (targetRange != null)
{
targetRange = targetRange.Cells[1, 1];
Comment refreshingComment = targetRange.Comment;
if (refreshingComment == null)
{
refreshingComment = targetRange.AddComment();
}
refreshingComment.Shape.TextFrame.AutoSize = true;
refreshingComment.Shape.Fill.Visible = Microsoft.Office.Core.MsoTriState.msoTrue;
//refreshingComment.Shape.Fill.ForeColor.RGB =RGB(220, 220, 220);
refreshingComment.Shape.Fill.OneColorGradient(Microsoft.Office.Core.MsoGradientStyle.msoGradientDiagonalUp, 1, (float)0.4);
refreshingComment.Visible = true;
refreshingComment.Text(commnet);
}
}
internal static string GetRangeName(Range targetRange)
{
ExcelUIThreadProtecter.CheckIsExcelUIMainThread();
string result = null;
if (targetRange != null)
{
Name namedRange = GetName(targetRange);
if (namedRange != null)
{
result = namedRange.Name;
}
if (string.IsNullOrEmpty(result) == true)
{
ListObject lo = GetListObject(targetRange);
if (lo != null)
{
result = lo.Name;
}
}
}
return(result);
}
internal static ListObject GetListObject(Range targetRange)
{
ExcelUIThreadProtecter.CheckIsExcelUIMainThread();
ListObject result = null;
if (targetRange != null)
{
Worksheet ws = targetRange.Worksheet;
ListObjects allListObject = ws.ListObjects;
ListObject item = null;
Range itemRange = null;
for (int i = 1; i <= allListObject.Count; i++) //VB start from 1
{
item = allListObject[i];
itemRange = GetListObjectRange(item);
if (IsRangeOverlap(itemRange, targetRange) == true)
{
result = item;
break;
}
}
}
return(result);
}
internal static bool IsRangeOverlap(Range sourceRange, Range targetRange)
{
ExcelUIThreadProtecter.CheckIsExcelUIMainThread();
bool result = false;
if (sourceRange != null && targetRange != null)
{
Worksheet sourceSheet = sourceRange.Worksheet;
Worksheet targetSheet = targetRange.Worksheet;
Workbook sourceBook = sourceSheet.Parent;
Workbook targetBook = targetSheet.Parent;
if (string.Compare(sourceBook.Name, targetBook.Name, false) == 0)
{
if (string.Compare(sourceSheet.Name, targetSheet.Name, false) == 0)
{
if (ExcelApp.Application.Intersect(sourceRange, targetRange) != null)
{
result = true;
}
}
}
}
return(result);
}
