public void replaceInDOC(Document document, List <string> stringsToReplase)
{
// https://msdn.microsoft.com/en-us/library/6b9478cs.aspx
object start = 0;
object end = 12;
Hyperlinks myLinks = document.Hyperlinks;
object linkAddr = "google.ru";
//string test_bookmark = "testsbookmark";
//object linkSubAddr = test_bookmark;
Range rng = document.Range(ref start, ref end);
rng.Text = "New Text";
Hyperlink myLink = myLinks.Add(rng, ref linkAddr);
rng.Select();
}
protected override IAsyncResult BeginExecute(AsyncCodeActivityContext context, AsyncCallback callback, object state)
{
PropertyDescriptor property = context.DataContext.GetProperties()[WordCreate.GetWordAppTag];
Application wordApp = property.GetValue(context.DataContext) as Application;
try
{
string linkName = LinkName.Get(context);
string linkMark = LinkMark.Get(context);
string linkAddr = LinkAddr.Get(context);
string bookMark = BookMark.Get(context);
string pic = Pic.Get(context);
if (linkName != null)
{
Hyperlinks links = wordApp.Selection.Hyperlinks;
links.Add(wordApp.Selection.Range, linkAddr, linkMark, "", linkName, linkMark);
}
if (bookMark != null)
{
Bookmarks bookmarks = wordApp.Selection.Bookmarks;
bookmarks.Add(bookMark);
}
if (pic != null)
{
InlineShapes lineshapes = wordApp.Selection.InlineShapes;
InlineShape lineshape = lineshapes.AddPicture(pic);
}
}
catch (Exception e)
{
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "Word执行过程出错", e.Message);
CommonVariable.realaseProcessExit(wordApp);
}
m_Delegate = new runDelegate(Run);
return(m_Delegate.BeginInvoke(callback, state));
}
//*************************************************************************
// Creates a Worksheet with information for each table
private void AddTable(Worksheet tableWorksheet, Schema schema, Table table)
{
Range tempRng;
Console.WriteLine("Table: " + table.Name);
tableWorksheet.Name = GetNumbers(table.Folder);
tableWorksheet.Columns.AutoFit();
Range c1 = tableWorksheet.Cells[1, 1];
Range c2 = tableWorksheet.Cells[1, 1];
Range linkCell = tableWorksheet.get_Range(c1, c2);
Hyperlinks links = tableWorksheet.Hyperlinks;
links.Add(linkCell, "", "tables!A1", "", "column <<< tables");
//tableWorksheet.Name = table.Name;
List <string> columnNames = new List <string>()
{
"column",
"name",
"type",
"typeOriginal",
"nullable",
"defaultValue",
"lobFolder",
"entity",
"description",
"note"
};
foreach (string name in columnNames.Skip(1))
{
tableWorksheet.Cells[1, columnNames.IndexOf(name) + 1] = name;
}
//-------------------------------------------------------------------
string[][] rowNamesArray = new string[9][] {
new string[2] {
"schemaName", schema.Name
},
new string[2] {
"schemaFolder", schema.Folder
},
new string[2] {
"tableName", table.Name
},
new string[2] {
"tableFolder", table.Folder
},
new string[2] {
"tablePriority", table.TablePriority
},
new string[2] {
"tableEntity", table.TableEntity
},
new string[2] {
"tableDescription", table.Description
},
new string[2] {
"rows", table.Rows.ToString()
},
new string[2] {
"columns", table.Columns.Count().ToString()
},
};
int cellCount = 2;
foreach (string[] rn in rowNamesArray)
{
tableWorksheet.Cells[cellCount, 1] = rn;
tableWorksheet.Cells[cellCount, 2] = rn[1];
cellCount++;
}
// Primary keys
if (table.PrimaryKey == null)
{
arrayKeysCounters[thisSchemaNo, 3]++; // noPKs
}
else
{
arrayKeysCounters[thisSchemaNo, 0]++; // PKs == (yesPKs)
tempRng = tableWorksheet.Cells[cellCount, 1];
tempRng.Interior.Color = Color.LightGray;
tempRng = tableWorksheet.Cells[cellCount, 2];
tempRng.Interior.Color = Color.LightGray;
tableWorksheet.Cells[cellCount, 1] = "pkName";
tableWorksheet.Cells[cellCount, 2] = table.PrimaryKey.Name;
cellCount++;
if (table.PrimaryKey.Columns != null)
{
foreach (string column in table.PrimaryKey.Columns)
{
tableWorksheet.Cells[cellCount, 1] = "pkColumn";
tableWorksheet.Cells[cellCount, 2] = column;
cellCount++;
}
}
// Entity
tableWorksheet.Cells[cellCount, 1] = "pkEntity";
string pk_extr_entity = ExtractEntity(table.PrimaryKey.Description, "entity");
tableWorksheet.Cells[cellCount, 2] = pk_extr_entity;
for (int n = 1; n < 8; n++)
{
tempRng = tableWorksheet.Cells[cellCount, n];
tempRng.Interior.Color = Color.LightGreen;
}
cellCount++;
// Description
tableWorksheet.Cells[cellCount, 1] = "pkDescription";
string pk_extr_description = ExtractEntity(table.PrimaryKey.Description, "description");
tableWorksheet.Cells[cellCount, 2] = pk_extr_description;
for (int n = 1; n < 8; n++)
{
tempRng = tableWorksheet.Cells[cellCount, n];
tempRng.Interior.Color = Color.LightSkyBlue;
}
cellCount++;
}
Console.WriteLine("fKEYS");
// Foreign keys
if (table.ForeignKeys == null)
{
arrayKeysCounters[thisSchemaNo, 4]++; // noFKs
}
else
{
arrayKeysCounters[thisSchemaNo, 6]++; // yesFKs
foreach (ForeignKey fkey in table.ForeignKeys)
{
arrayKeysCounters[thisSchemaNo, 1]++; // FKs
tempRng = tableWorksheet.Cells[cellCount, 1];
tempRng.Interior.Color = Color.LightPink;
tempRng = tableWorksheet.Cells[cellCount, 2];
tempRng.Interior.Color = Color.LightPink;
tableWorksheet.Cells[cellCount, 1] = "fkName";
tableWorksheet.Cells[cellCount, 2] = fkey.Name;
cellCount++;
if (fkey.Columns != null)
{
foreach (string column in fkey.Columns)
{
tableWorksheet.Cells[cellCount, 1] = "fkColumn";
tableWorksheet.Cells[cellCount, 2] = column;
cellCount++;
}
}
tableWorksheet.Cells[cellCount, 1] = "fkRefSchema";
tableWorksheet.Cells[cellCount, 2] = fkey.ReferencedSchema;
cellCount++;
tableWorksheet.Cells[cellCount, 1] = "fkRefTable";
tableWorksheet.Cells[cellCount, 2] = fkey.ReferencedTable;
cellCount++;
if (fkey.ReferencedColumns != null)
{
foreach (string column in fkey.ReferencedColumns)
{
tableWorksheet.Cells[cellCount, 1] = "fkReferencedColumns";
tableWorksheet.Cells[cellCount, 2] = column;
cellCount++;
}
}
// Entity
tableWorksheet.Cells[cellCount, 1] = "fkEntity";
string fk_extr_entity = ExtractEntity(fkey.Description, "entity");
tableWorksheet.Cells[cellCount, 2] = fk_extr_entity;
for (int n = 1; n < 8; n++)
{
tempRng = tableWorksheet.Cells[cellCount, n];
tempRng.Interior.Color = Color.LightGreen;
}
cellCount++;
// Description
tableWorksheet.Cells[cellCount, 1] = "fkDescription";
string fk_extr_description = ExtractEntity(fkey.Description, "description");
tableWorksheet.Cells[cellCount, 2] = fk_extr_description;
for (int n = 1; n < 8; n++)
{
tempRng = tableWorksheet.Cells[cellCount, n];
tempRng.Interior.Color = Color.LightSkyBlue;
}
cellCount++;
tableWorksheet.Cells[cellCount, 1] = "fkDeleteAction";
tableWorksheet.Cells[cellCount, 2] = fkey.DeleteAction;
cellCount++;
tableWorksheet.Cells[cellCount, 1] = "fkUpdateAction";
tableWorksheet.Cells[cellCount, 2] = fkey.UpdateAction;
cellCount++;
}
}
Console.WriteLine("cKEYS");
// Candidate keys
if (table.CandidateKeys == null)
{
arrayKeysCounters[thisSchemaNo, 5]++; // noCKs
}
else
{
arrayKeysCounters[thisSchemaNo, 7]++; // yesCKs
foreach (CandidateKey ckey in table.CandidateKeys)
{
arrayKeysCounters[thisSchemaNo, 2]++; // CKs
tempRng = tableWorksheet.Cells[cellCount, 1];
tempRng.Interior.Color = Color.PaleTurquoise;
tempRng = tableWorksheet.Cells[cellCount, 2];
tempRng.Interior.Color = Color.PaleTurquoise;
tableWorksheet.Cells[cellCount, 1] = "ckName";
tableWorksheet.Cells[cellCount, 2] = ckey.Name;
cellCount++;
if (ckey.Columns != null)
{
foreach (string column in ckey.Columns)
{
tableWorksheet.Cells[cellCount, 1] = "ckColumn";
tableWorksheet.Cells[cellCount, 2] = column;
cellCount++;
}
}
// Entity
tableWorksheet.Cells[cellCount, 1] = "ckEntity";
string ck_extr_entity = ExtractEntity(ckey.Description, "entity");
tableWorksheet.Cells[cellCount, 2] = ck_extr_entity;
for (int n = 1; n < 8; n++)
{
tempRng = tableWorksheet.Cells[cellCount, n];
tempRng.Interior.Color = Color.LightGreen;
}
cellCount++;
// Description
tableWorksheet.Cells[cellCount, 1] = "ckDescription";
string ck_extr_description = ExtractEntity(ckey.Description, "description");
tableWorksheet.Cells[cellCount, 2] = ck_extr_description;
for (int n = 1; n < 8; n++)
{
tempRng = tableWorksheet.Cells[cellCount, n];
tempRng.Interior.Color = Color.LightSkyBlue;
}
cellCount++;
}
}
// Repeat header row for visibility at top of first Table Column row
foreach (string name in columnNames.Skip(1))
{
tableWorksheet.Cells[cellCount, columnNames.IndexOf(name) + 1] = name;
}
// Repeat header row bold & border lines
for (int n = 1; n < 11; n++)
{
tempRng = tableWorksheet.Cells[cellCount, n];
tempRng.Characters.Font.Bold = true;
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;
if (n < 8)
{
tempRng.Interior.Color = Color.LightGray;
}
}
// Cell background color
tempRng = tableWorksheet.Cells[cellCount, 8];
tempRng.Interior.Color = Color.LightGreen;
tempRng = tableWorksheet.Cells[cellCount, 9];
tempRng.Interior.Color = Color.LightSkyBlue;
tempRng = tableWorksheet.Cells[cellCount, 10];
tempRng.Interior.Color = Color.LightGray;
cellCount++;
// Insert Table Column rows
int columnCount = 1;
foreach (Column column in table.Columns)
{
GetEntity(column.Description, null, column);
tableWorksheet.Cells[cellCount, 1] = columnCount;
tableWorksheet.Cells[cellCount, 2] = column.Name;
tableWorksheet.Cells[cellCount, 3] = column.Datatype;
//typeOriginal
tableWorksheet.Cells[cellCount, 4] = "";
tableWorksheet.Cells[cellCount, 5] = column.Nullable;
//defaultValue
tableWorksheet.Cells[cellCount, 6] = "";
tableWorksheet.Cells[cellCount, 7] = column.Folder;
tableWorksheet.Cells[cellCount, 8] = column.Entity;
tableWorksheet.Cells[cellCount, 9] = column.Description;
//note
tableWorksheet.Cells[cellCount, 10] = "";
// Border line
for (int n = 1; n < 11; n++)
{
tempRng = tableWorksheet.Cells[cellCount, 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;
}
// Console.WriteLine("lobFolder = '" + column.Folder + "'");
if (null != column.Folder && "" != column.Folder)
{
for (int n = 1; n < 8; n++)
{
tempRng = tableWorksheet.Cells[cellCount, n];
tempRng.Interior.Color = Color.LightYellow;
}
}
// Background color
tempRng = tableWorksheet.Cells[cellCount, 8];
tempRng.Interior.Color = Color.LightGreen;
tempRng = tableWorksheet.Cells[cellCount, 9];
tempRng.Interior.Color = Color.LightSkyBlue;
tempRng = tableWorksheet.Cells[cellCount, 10];
tempRng.Interior.Color = Color.LightGray;
cellCount++;
columnCount++;
}
// Range range = tableWorksheet.Cells[5, 1];
tempRng = tableWorksheet.Cells[9, 3];
tempRng.Activate();
tempRng.Application.ActiveWindow.FreezePanes = true;
// First row bold
tempRng = tableWorksheet.Range["A1", "J1"];
tempRng.Characters.Font.Bold = true;
// Border lines
for (int n = 1; n < 11; n++)
{
tempRng = tableWorksheet.Cells[1, 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;
if (n < 8)
{
tempRng.Interior.Color = Color.LightGray;
}
}
for (int m = 2; m < 8; m++)
{
for (int n = 1; n < 3; n++)
{
tempRng = tableWorksheet.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
tempRng = tableWorksheet.Cells[1, 8];
tempRng.Interior.Color = Color.LightGreen;
tempRng = tableWorksheet.Cells[1, 9];
tempRng.Interior.Color = Color.LightSkyBlue;
tempRng = tableWorksheet.Cells[1, 10];
tempRng.Interior.Color = Color.LightGray;
tempRng = tableWorksheet.Range["A6", "B6"];
tempRng.Interior.Color = Color.LightYellow;
tempRng = tableWorksheet.Range["A7", "J7"];
tempRng.Interior.Color = Color.LightGreen;
// ToDo: Make Wrap Text expand to selected range, not only the single cell
// tempRng.WrapText = true;
// tempRng.Style.WrapText = true;
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;
tempRng = tableWorksheet.Range["A8", "J8"];
tempRng.Interior.Color = Color.LightSkyBlue;
// ToDo: Make Wrap Text expand to selected range, not only the single cell
// tempRng.WrapText = true;
// tempRng.Style.WrapText = true;
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;
// Column widths
tableWorksheet.Columns["A:A"].AutoFit();
tableWorksheet.Columns["B:B"].ColumnWidth = 30;
tableWorksheet.Columns["C:C"].AutoFit();
tableWorksheet.Columns["D:D"].AutoFit();
tableWorksheet.Columns["E:E"].AutoFit();
tableWorksheet.Columns["F:F"].AutoFit();
tableWorksheet.Columns["G:G"].AutoFit(); // .ColumnWidth = 26;
tableWorksheet.Columns["G:G"].WrapText = true;
tableWorksheet.Columns["H:H"].ColumnWidth = 30;
tableWorksheet.Columns["H:H"].WrapText = true;
tableWorksheet.Columns["I:I"].ColumnWidth = 60;
tableWorksheet.Columns["I:I"].WrapText = true;
tableWorksheet.Columns["J:J"].ColumnWidth = 60;
tableWorksheet.Columns["J:J"].WrapText = true;
// Alignment
tableWorksheet.Columns.HorizontalAlignment = XlHAlign.xlHAlignLeft;
tableWorksheet.Columns.VerticalAlignment = XlVAlign.xlVAlignCenter;
Marshal.ReleaseComObject(tableWorksheet);
}
//*************************************************************************
// 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);
}
