//Сдигает выделение после установки ссылки, isValue - производится запись свойства в ячейку, иначе установка ссылки
public void MoveAfterLink()
{
try
{
var acell = GeneralRep.Application.ActiveCell;
Range rcell = null;
int d;
if (int.TryParse(NextCellStep.Text, out d))
{
if (NextCellShift.Text == "Вправо")
{
rcell = (Range)acell.Worksheet.Cells[acell.Row, acell.Column + d];
}
if (NextCellShift.Text == "Вниз")
{
rcell = (Range)acell.Worksheet.Cells[acell.Row + d, acell.Column];
}
if (rcell != null)
{
rcell.Activate();
}
}
else
{
Different.MessageError("Недопустимое значение шага перехода после установки ссылки: " + NextCellStep.Text);
}
}
catch { }
}
private void MoveToKeyRow(Int32 keyRow)
{
XlHlp.DisplayInWatchWindow(string.Format("{0}",
System.Reflection.MethodInfo.GetCurrentMethod().Name));
Microsoft.Office.Interop.Excel.Application app = Globals.ThisAddIn.Application;
Worksheet ws = app.ActiveSheet;
string currentTableName = GetCurrentTable(ws);
try
{
ListObject currentTable = ws.ListObjects[currentTableName];
// Put the rows back in ID order.
//currentTable.Range.Sort(Key1: currentTable.ListColumns[LogColumns.ID].Range);
currentTable.Sort.SortFields.Clear();
currentTable.Sort.SortFields.Add(
Key: currentTable.ListColumns[1].Range,
SortOn: XlSortOn.xlSortOnValues,
Order: XlSortOrder.xlAscending,
DataOption: XlSortDataOption.xlSortNormal);
currentTable.Sort.Apply();
// And move to the key row
Range targetCell = ws.Cells[keyRow, LogColumns.Duration];
targetCell.Activate();
targetCell.Interior.Pattern = XlPattern.xlPatternSolid;
targetCell.Interior.Color = System.Drawing.Color.Yellow;
//targetCell.Interior.Color = Colors.Yellow;
targetCell.Interior.PatternColor = System.Drawing.Color.Yellow;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Recherche le texte à l'intérieur des forumules et retourne l'adresse de la cellule du type A1.
/// La recherche débute aprés le Range Actif, ce qui est parfois générateur de bug.
/// </summary>
/// <param name="TexteATrouver"></param>
/// <returns></returns>
public string ChercherSuivant(string TexteATrouver)
{
object What = (object)TexteATrouver;
if (_MonRange.Cells.Count != 1)
{
this.SelectionRange("A1", "A1");
}
//fonction find
_MonRange = _MaFeuille.Cells.Find(What, _MonRange, _M, XlLookAt.xlPart, XlSearchOrder.xlByColumns, XlSearchDirection.xlNext, (object)false, _M, (object)false);
if (_MonRange == null)
{
return("Recherche infructeuse");
}
else
{
//Active la céllule trouvée
_MonRange.Activate();
return(string.Format("L{0}C{1}", _MonRange.Row.ToString(), _MonRange.Column.ToString()));
}
}
/// <summary> 进行数据的重新排列 </summary>
public void btnReArrange_Click(object sender, RibbonControlEventArgs e)
{
// ---------------------------- 确定Range的有效范围 ------------------------------------------
Worksheet sht = _app.ActiveSheet;
Range rgData = _app.Selection;
rgData = rgData.Areas[1];
Range firstCell = default(Range); // 有效区间中的左上角第一个单元
Range bottomCell = default(Range); // 有效区间中的左下角的那个单元
Range rbcell = default(Range); // 有效区间中的右下角的那个单元
int SortedId = 0;
double interval = 0;
string[] strInterval_Id = EditBox_ReArrangeIntervalId.Text.Split(',');
double.TryParse(strInterval_Id[0], out interval);
int.TryParse(strInterval_Id[1], out SortedId);
int startRow;
//
rbcell = rgData.Ex_CornerCell(CornerIndex.BottomRight);
bottomCell = rgData.Cells[rgData.Rows.Count, SortedId];
firstCell = rgData.Cells[1, 1];
if (bottomCell.Value == null)
{
bottomCell = bottomCell.End[XlDirection.xlUp];
}
if (firstCell.Value == null)
{
firstCell = firstCell.End[XlDirection.xlDown];
}
rgData = sht.Range[firstCell, sht.Cells[bottomCell.Row, rbcell.Column]];
startRow = Convert.ToInt32(rgData.Cells[1, 1].Row);
// ------------------------------------- 提取参数 -------------------------------------
Range rgIdColumn = rgData.Columns[SortedId];
double startData = 0;
double endData = 0;
try
{
startData = double.Parse(EditBox_ReArrangeStart.Text);
}
catch (Exception ex)
{
try
{
startData = DateTime.Parse(EditBox_ReArrangeStart.Text).ToOADate();
}
catch (Exception)
{
try
{
startData = Convert.ToDouble(_app.WorksheetFunction.Min(rgIdColumn));
EditBox_ReArrangeStart.Text = Convert.ToString(startData);
}
catch (Exception)
{
MessageBox.Show("指定的数据列中的数据不能进行排序！" + "\r\n" +
ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
}
try
{
endData = double.Parse(EditBox_ReArrangeEnd.Text);
}
catch (Exception ex)
{
try
{
endData = DateTime.Parse(EditBox_ReArrangeEnd.Text).ToOADate();
}
catch (Exception)
{
try
{
endData = Convert.ToDouble(_app.WorksheetFunction.Max(rgIdColumn));
EditBox_ReArrangeEnd.Text = Convert.ToString(endData);
}
catch (Exception)
{
MessageBox.Show("指定的数据列中的数据不能进行排序！" + "\r\n" +
ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
}
//
// 检查参数的正确性
if (endData <= startData || interval == 0 || interval > endData - startData || SortedId == 0 ||
SortedId > rgData.Columns.Count)
{
MessageBox.Show("指定的参数不正确！", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// 检查数据的有效性
object[,] Value = rgData.Value2;
SortedList <double, int> v_row = new SortedList <double, int>(); //每一个key代表此标志列的实际数据，对应的value代表此数据在指定的区间内的局部行号
int r = 0;
try
{
object v = null;
for (r = 1; r <= Value.Length - 1; r++)
{
v = Value[r, SortedId];
if ((v != null) && string.Compare("", v.ToString().Trim()) != 0)
{
v_row.Add((double)v, r);
}
}
}
catch (Exception ex)
{
Range c = rgData.Cells[r, SortedId];
MessageBox.Show("单元格 " + c.Address + " 的数据不符合规范，请检查。" + "\r\n" +
ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
c.Activate();
return;
}
// ------------------------------------------ 开始重新排列数据 ------------------------------------------
int RowsCount = 0;
int ColsCount = rgData.Columns.Count;
RowsCount = (int)(Math.Floor((endData - startData) / interval) + 1);
object[,] arrResult = new object[RowsCount - 1 + 1, ColsCount - 1 + 1];
var arrKey = v_row.Keys;
var arrValue = v_row.Values;
int valueRow = 0;
for (r = 0; r <= RowsCount - 1; r++)
{
int sourceR = (int)arrKey.IndexOf(startData + r * interval);
if (sourceR >= 0)
{
valueRow = Convert.ToInt32(arrValue[sourceR]); // 指定的数据在Excel区间中的行号
for (int c = 0; c <= ColsCount - 1; c++)
{
arrResult[r, c] = Value[valueRow, c + 1];
}
}
}
// 将排列完成后的结果放置回Excel单元格中
Range rgResult = sht.Range[firstCell, firstCell.Offset[RowsCount - 1, ColsCount - 1]];
rgResult.Value = arrResult;
rgResult.Columns[SortedId].NumberFormatLocal = rgData.Cells[1, SortedId].NumberFormatLocal; // 还原这一列的数值格式
rgResult.Select();
}
//*************************************************************************
// Creates a Worksheet with table overview
private void AddTableOverview(Worksheet tableOverviewWorksheet, Schema schema, List <string> priorities)
{
tableOverviewWorksheet.Name = "tables";
tableOverviewWorksheet.Columns.AutoFit();
List <string> columnNames = new List <string>()
{
"table",
"folder",
"schema",
"rows",
"columns",
"priority",
// "pri-sort",
"entity",
"description",
"note"
};
foreach (string name in columnNames)
{
tableOverviewWorksheet.Cells[1, columnNames.IndexOf(name) + 1] = name;
}
//-------------------------------------------------------------------
int count = 2;
foreach (Table table in schema.Tables)
{
Console.WriteLine("Table: " + table.Name + ", Description: " + table.Description);
GetEntity(table.Description, table);
Console.WriteLine("Table: " + table.Name + ", Description2: " + table.Description);
if (priorities.Contains(table.TablePriority))
{
if (includeTables)
{
Range c1 = tableOverviewWorksheet.Cells[count, 1];
Range c2 = tableOverviewWorksheet.Cells[count, 1];
Range linkCell = tableOverviewWorksheet.get_Range(c1, c2);
Hyperlinks links = tableOverviewWorksheet.Hyperlinks;
links.Add(linkCell, "", GetNumbers(table.Folder) + "!A1", "", table.Name);
Marshal.ReleaseComObject(c1);
Marshal.ReleaseComObject(c2);
Marshal.ReleaseComObject(linkCell);
Marshal.ReleaseComObject(links);
}
else
{
tableOverviewWorksheet.Cells[count, 1] = table.Name;
}
tableOverviewWorksheet.Cells[count, 2] = table.Folder;
tableOverviewWorksheet.Cells[count, 3] = schema.Name;
tableOverviewWorksheet.Cells[count, 4] = table.Rows;
tableOverviewWorksheet.Cells[count, 5] = table.Columns.Count;
Console.WriteLine("Table priority: " + table.TablePriority + ", Rows: " + table.Rows);
if (string.IsNullOrEmpty(table.TablePriority) && table.Rows == 0)
{
table.TablePriority = "EMPTY";
}
tableOverviewWorksheet.Cells[count, 6] = table.TablePriority;
// Pri sort
//tableOverviewWorksheet.Cells[count, 7] = Globals.PriSort(table.TablePriority);
tableOverviewWorksheet.Cells[count, 7] = table.TableEntity;
tableOverviewWorksheet.Cells[count, 8] = table.Description;
// Note
tableOverviewWorksheet.Cells[count, 9] = "";
count++;
}
}
// Freeze Panes
Range range = tableOverviewWorksheet.Cells[2, 1];
range.Activate();
range.Application.ActiveWindow.FreezePanes = true;
Range tempRng = tableOverviewWorksheet.Range["A1", "I1"];
tempRng.Characters.Font.Bold = true;
// Border lines
for (int m = 1; m < count; m++)
{
for (int n = 1; n < 10; n++)
{
tempRng = tableOverviewWorksheet.Cells[m, n];
tempRng.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlContinuous;
tempRng.Borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlContinuous;
tempRng.Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
tempRng.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
}
}
// Cell background color
for (int m = 1; m < count; m++)
{
tempRng = tableOverviewWorksheet.Cells[m, 6];
tempRng.Interior.Color = Color.LightYellow;
tempRng = tableOverviewWorksheet.Cells[m, 7];
tempRng.Interior.Color = Color.LightGreen;
tempRng = tableOverviewWorksheet.Cells[m, 8];
tempRng.Interior.Color = Color.LightSkyBlue;
tempRng = tableOverviewWorksheet.Cells[m, 9];
tempRng.Interior.Color = Color.LightGray;
}
// Alignment
tableOverviewWorksheet.Columns.HorizontalAlignment = XlHAlign.xlHAlignLeft;
tableOverviewWorksheet.Columns.VerticalAlignment = XlVAlign.xlVAlignCenter;
// Column widths
tableOverviewWorksheet.Columns["A:A"].AutoFit();
tableOverviewWorksheet.Columns["B:B"].AutoFit(); // .ColumnWidth = 8;
tableOverviewWorksheet.Columns["C:C"].AutoFit(); // .ColumnWidth = 8;
tableOverviewWorksheet.Columns["D:D"].AutoFit();
tableOverviewWorksheet.Columns["D:D"].HorizontalAlignment = XlHAlign.xlHAlignCenter;
tableOverviewWorksheet.Columns["E:E"].AutoFit();
tableOverviewWorksheet.Columns["E:E"].HorizontalAlignment = XlHAlign.xlHAlignCenter;
tableOverviewWorksheet.Columns["F:F"].ColumnWidth = 10;
tableOverviewWorksheet.Columns["F:F"].HorizontalAlignment = XlHAlign.xlHAlignCenter;
tableOverviewWorksheet.Columns["G:G"].ColumnWidth = 20;
tableOverviewWorksheet.Columns["G:G"].WrapText = true;
tableOverviewWorksheet.Columns["H:H"].ColumnWidth = 60;
tableOverviewWorksheet.Columns["H:H"].WrapText = true;
tableOverviewWorksheet.Columns["I:I"].ColumnWidth = 60;
tableOverviewWorksheet.Columns["I:I"].WrapText = true;
// Column sorting
tableOverviewWorksheet.Sort.SortFields.Clear();
tableOverviewWorksheet.Sort.SortFields.Add(tableOverviewWorksheet.Range["F:F"], XlSortOn.xlSortOnValues, XlSortOrder.xlAscending, "HIGH, MEDIUM, LOW, SYSTEM, STATS, EMPTY, DUMMY", XlSortDataOption.xlSortNormal);
tableOverviewWorksheet.Sort.SetRange(tableOverviewWorksheet.UsedRange);
tableOverviewWorksheet.Sort.Header = XlYesNoGuess.xlYes;
tableOverviewWorksheet.Sort.Apply();
Marshal.ReleaseComObject(tableOverviewWorksheet);
}
public void Goto()
{
cellLocation.Activate();
}
public void Createreport(string sRDGFile, string sOutputFile)
{
StringBuilder sbDetails = new StringBuilder();
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
int row = 2, col = 1;
DataSet ds = new DataSet();
ds.ReadXml(sRDGFile);
System.Data.DataTable Groups = ds.Tables["properties"];
System.Data.DataTable Servers = ds.Tables["server"];
var query =
from groups in Groups.AsEnumerable()
join servers in Servers.AsEnumerable()
on groups.Field <int?>("group_Id") equals
servers.Field <int?>("group_Id")
select new
{
GroupName =
groups.Field <string>("Name"),
ServerName =
servers.Field <string>("Name"),
ServerDisplayName =
servers.Field <string>("displayName")
};
if (xlApp == null)
{
Console.WriteLine("Excel is not properly installed!!");
return;
}
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp.Visible = true;
xlWorkBook = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets[1];
if (xlWorkSheet == null)
{
Console.WriteLine("Worksheet could not be created. Check that your office installation and project references are correct.");
}
//xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "Server Name";
xlWorkSheet.Cells[1, 2] = "Environment";
xlWorkSheet.Cells[1, 3] = "Release";
xlWorkSheet.Cells[1, 4] = "Server Type";
xlWorkSheet.Cells[1, 5] = "Legacy Status";
xlWorkSheet.Cells[1, 6] = "NextGen Status";
xlWorkSheet.Cells[1, 7] = "DXI Status";
xlWorkSheet.Cells[1, 8] = "Mobile Status";
xlWorkSheet.Cells[1, 9] = "TLMServices Status";
xlWorkSheet.Cells[1, 10] = "Dispatcher";
Range firstRow = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Rows[1];
firstRow.Activate();
firstRow.Select();
firstRow.Font.Bold = true;
firstRow.AutoFilter(1, Type.Missing,
Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
//xlWorkBook.Worksheets.Add(xlWorkSheet);
//xlWorkBook.SaveAs("C:\\Work\\SVN_Source\\Utilities\\RemoteDesktopConnectionManager\\CreateServerList\\CreateServerList\\bin\\Debug\\ServerList.xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
//xlWorkBook.Save();
foreach (var server in query)
{
col = 1;
xlWorkSheet.Cells[row, col++] = server.ServerName;
xlWorkSheet.Cells[row, col++] = server.GroupName.Contains("-") ? server.GroupName.Substring(0, server.GroupName.IndexOf("-")) : server.GroupName;
xlWorkSheet.Cells[row, col++] = server.GroupName.Contains("-") ? server.GroupName.Substring(server.GroupName.IndexOf("-"),
server.GroupName.Length - server.GroupName.IndexOf("-")).Replace("_", ".").Replace(",", " ").Replace("-", "").Trim() : string.Empty;
ServerType eServerType = GetServerType(server.ServerDisplayName, server.ServerName);
xlWorkSheet.Cells[row, col++] = eServerType == ServerType.UnKnown ? "" : eServerType.ToString();
switch (eServerType)
{
case ServerType.Web:
col = 5;
break;
case ServerType.Proc:
break;
case ServerType.MP:
col = 7;
break;
case ServerType.MPMax:
col = 7;
break;
case ServerType.DXI:
col = 7;
break;
case ServerType.DXM:
col = 8;
break;
case ServerType.DXIR:
col = 7;
break;
case ServerType.ES:
col = 10;
break;
case ServerType.EventRouter:
col = 10;
break;
case ServerType.REDIR:
col = 5;
break;
case ServerType.v18All:
col = 5;
break;
default:
break;
}
string[] strArry = CreateStatusLinks(eServerType, server.ServerName);
if (strArry != null && strArry.Length > 0)
{
foreach (string str in strArry)
{
if (str != "")
{
string[] strLinks = str.ToString().Split(',');
//string strLink = "=HYPERLINK(\"" + strLinks[1] + "\",\"\"Infragistics\"\")";
if (eServerType == ServerType.v18All)
{
if (col == 6)
{
col = 7;
}
else if (col == 9)
{
col = 10;
}
}
xlWorkSheet.Hyperlinks.Add(xlWorkSheet.Cells[row, col++], strLinks[1], misValue, misValue, strLinks[0]);
}
}
}
row++;
}
// xlWorkBook.SaveAs("C:\\Work\\SVN_Source\\Utilities\\RemoteDesktopConnectionManager\\CreateServerList\\CreateServerList\\bin\\Debug\\ServerList.xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
//using (System.IO.StreamWriter file = new System.IO.StreamWriter(sOutputFile, true))
//{
// file.WriteLine(sbDetails.ToString());
//}
ds.Clear();
}