/// <summary>
/// 断开全部表格的域链接
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void DiscAllLink_Click(object sender, RibbonControlEventArgs e)
{
//弹出窗体提示
DialogResult IsWait = MessageBox.Show("请在使用HertZ_Excel插件生成的Word附注中使用该功能" + Environment.NewLine + "是否继续？", "请选择", MessageBoxButtons.YesNo);
if (IsWait != DialogResult.Yes)
{
return;
}
WordApp = Globals.ThisAddIn.Application;
WordDoc = WordApp.ActiveDocument;
int i5 = WordDoc.Fields.Count;
int i4 = 0;
//WordDoc.Fields.Update();
WordApp.ScreenUpdating = false;//关闭屏幕刷新
foreach (Word.Field TempField in WordDoc.Fields)
{
i4++;
if (TempField.Type != Word.WdFieldType.wdFieldLink)
{
//显示进度
WordApp.StatusBar = "当前进度:" + Math.Round((i4 * 100d / i5), 2) + "%";
continue;
}
//断开链接
TempField.Unlink();
//显示进度
WordApp.StatusBar = "当前进度:" + Math.Round((i4 * 100d / i5), 2) + "%";
}
WordApp.ScreenUpdating = true;//打开屏幕刷新
MessageBox.Show("Link域已全部断开链接！");
}
/// <summary>
/// 刷新当前域
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void UpdateOneLink_Click(object sender, RibbonControlEventArgs e)
{
WordApp = Globals.ThisAddIn.Application;
WordDoc = WordApp.ActiveDocument;
Word.Field TempField;
Word.Table TempTable;
int WordRows = 0;
int WordColumns = 0;
int ExcelRows = 0;
int ExcelColumns = 0;
string TempStr;
//如果选中的区域不包含表格则退出
if (WordApp.Selection.Tables.Count == 0)
{
return;
}
//如果选中的区域不在域中则退出
if (!WordApp.Selection.Information[Word.WdInformation.wdInFieldResult])
{
return;
}
WordApp.ScreenUpdating = false;//关闭屏幕刷新
//选中整个表格
TempTable = WordApp.Selection.Tables[1];
TempTable.Select();
//获取Word中表格行列数
WordRows = TempTable.Rows.Count;
WordColumns = TempTable.Columns.Count;
//获取域
WordApp.Selection.Next(Word.WdUnits.wdWord, 2).Select();
WordApp.Selection.PreviousField();
TempField = WordApp.Selection.Fields[1];
TempStr = FunC.LinkPath(TempField.Code.Text);
//检查文件是否存在
if (!File.Exists(TempStr))
{
MessageBox.Show("未发现域链接的Excel文件，请检查！");
}
Excel.Application ExcelApp = null;
Excel.Workbook WBK = null;
Excel.Worksheet WST = null;
//获取区域的行列数
if (FunC.IsFileInUse(TempStr))//如果目标文件已被打开
{
try
{
ExcelApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
WBK = ExcelApp.Workbooks[Path.GetFileName(TempStr)];
}
catch
{
MessageBox.Show("链接的Excel文件已被后台程序打开，请检查并清理后台程序");
return;
}
//如果没有工作表
if (!FunC.SheetExist(WBK, FunC.LinkSheet(TempField.Code.Text)))
{
MessageBox.Show("链接的Excel文件中未发现该工作表，请检查");
return;
}
WST = WBK.Worksheets[FunC.LinkSheet(TempField.Code.Text)];
try
{
ExcelRows = WST.Range[FunC.LinkArea(TempField.Code.Text)].Rows.Count;
ExcelColumns = WST.Range[FunC.LinkArea(TempField.Code.Text)].Columns.Count;
}
catch
{
MessageBox.Show("链接的Excel文件中未发现该域的命名区域，请检查");
return;
}
}
else
{
bool QuitExcel;
try
{
ExcelApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
QuitExcel = false;
}
catch
{
//弹出窗体提示
DialogResult IsWait = MessageBox.Show("当前版本插件建议打开Excel程序再刷新域" + Environment.NewLine + "否则可能需要清理后台Excel程序，是否继续？", "请选择", MessageBoxButtons.YesNo);
if (IsWait != DialogResult.Yes)
{
WordApp.ScreenUpdating = true; return;
}
ExcelApp = new Excel.Application();
ExcelApp.Visible = false;
QuitExcel = true;
}
WBK = ExcelApp.Workbooks.Open(TempStr);
//如果没有工作表
if (!FunC.SheetExist(WBK, FunC.LinkSheet(TempField.Code.Text)))
{
MessageBox.Show("链接的Excel文件中未发现该工作表，请检查");
return;
}
WST = WBK.Worksheets[FunC.LinkSheet(TempField.Code.Text)];
try
{
ExcelRows = WST.Range[FunC.LinkArea(TempField.Code.Text)].Rows.Count;
ExcelColumns = WST.Range[FunC.LinkArea(TempField.Code.Text)].Columns.Count;
}
catch
{
MessageBox.Show("链接的Excel文件中未发现该域的命名区域，请检查");
return;
}
WBK.Close();
WBK = null;
WST = null;
//退出Excel程序
if (QuitExcel)
{
ExcelApp.Visible = true;
ExcelApp.Quit();
ExcelApp = null;
}
}
int TempInt = WordRows - ExcelRows;
//检查行数
if (TempInt < 0)
{
for (int i = 1; i <= Math.Abs(TempInt); i++)
{
TempTable.Cell(Math.Max(WordRows - 1, 1), 1).Range.Rows.Add(TempTable.Cell(Math.Max(WordRows - 1, 1), 1).Range.Rows);
}
}
else if (TempInt > 0)
{
for (int i = TempInt; i >= 1; i--)
{
TempTable.Cell(Math.Max(WordRows - i, 1), 1).Range.Rows.Delete();
}
}
TempInt = WordColumns - ExcelColumns;
//检查列数
if (TempInt < 0)
{
for (int i = 1; i <= Math.Abs(TempInt); i++)
{
TempTable.Cell(ExcelRows, Math.Max(WordColumns - 2, 1)).Range.Columns.Add(TempTable.Cell(ExcelRows, Math.Max(WordColumns - 2, 1)).Range.Columns);
}
}
else if (TempInt > 0)
{
for (int i = TempInt; i >= 1; i--)
{
TempTable.Cell(ExcelRows, Math.Max(WordColumns - 1, 1)).Range.Columns.Delete();
}
}
TempField.Update();
TempField.Select();
GC.Collect();
WordApp.ScreenUpdating = true;//打开屏幕刷新
}
/// <summary>
/// 更新全部域
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void UpdateLink_Click(object sender, RibbonControlEventArgs e)
{
//弹出窗体提示
DialogResult IsWait = MessageBox.Show("请在使用HertZ_Excel插件生成的Word附注中使用该功能" + Environment.NewLine + "是否继续？", "请选择", MessageBoxButtons.YesNo);
if (IsWait != DialogResult.Yes)
{
return;
}
WordApp = Globals.ThisAddIn.Application;
WordDoc = WordApp.ActiveDocument;
Excel.Workbook WBK = null;
Word.Table TempTable;
bool QuitExcel = false;
bool CloseWBK = false;
int ExcelRows;
int ExcelColumns;
int WordRows;
int WordColumns;
int TempInt;
//WordDoc.Fields.Update();
WordApp.ScreenUpdating = false;//关闭屏幕刷新
WordApp.StatusBar = "当前进度:0.00%";
string TempStr = null;
//检查Excel文件
foreach (Word.Field TempField in WordDoc.Fields)
{
if (TempField.Type == Word.WdFieldType.wdFieldLink)
{
TempStr = FunC.LinkPath(TempField.Code.Text);
//检查文件是否存在
if (!File.Exists(TempStr))
{
MessageBox.Show("未发现" + TempStr + "，请检查");
return;
}
break;
}
}
if (TempStr == null)
{
MessageBox.Show("未发现表格域，请在久其生成的附注中使用该功能"); return;
}
//获取区域的行列数
if (FunC.IsFileInUse(TempStr))//如果目标文件已被打开
{
try
{
ExcelApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
WBK = ExcelApp.Workbooks[Path.GetFileName(TempStr)];
}
catch
{
MessageBox.Show("链接的Excel文件已被后台程序打开，请检查并清理后台程序");
return;
}
}
else
{
CloseWBK = true;
try
{
ExcelApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
QuitExcel = false;
}
catch
{
//弹出窗体提示
IsWait = MessageBox.Show("当前版本插件建议打开Excel程序再刷新域" + Environment.NewLine + "否则可能需要清理后台Excel程序，是否继续？", "请选择", MessageBoxButtons.YesNo);
if (IsWait != DialogResult.Yes)
{
WordApp.ScreenUpdating = true; return;
}
ExcelApp = new Excel.Application();
ExcelApp.Visible = false;
QuitExcel = true;
}
WBK = ExcelApp.Workbooks.Open(TempStr);
}
//遍历WBK工作表名，加入字典
Dictionary <string, string> wstDic = new Dictionary <string, string> {
};
object[,] ORG;
foreach (Excel.Worksheet wst in WBK.Worksheets)
{
ExcelColumns = FunC.AllColumns(wst, 4, 2);
ExcelRows = FunC.AllRows(wst, "A", 2);
ORG = wst.Range["A1:A" + ExcelRows].Value2;
for (int i = ExcelRows; i > Math.Max(ExcelRows - 5, 4); i--)
{
if (FunC.TS(ORG[i, 1]).Contains("注：") || FunC.TS(ORG[i, 1]).Contains("注:"))
{
ExcelRows = i - 1;
}
}
wstDic.Add(wst.Name, ExcelRows + ":" + ExcelColumns);
}
//关闭workbook
if (CloseWBK)
{
WBK.Close();
}
//退出Excel程序
if (QuitExcel)
{
ExcelApp.Visible = true;
ExcelApp.Quit();
ExcelApp = null;
}
int i5 = WordDoc.Fields.Count;
int i4 = 0;
foreach (Word.Field TempField in WordDoc.Fields)
{
i4++;
if (TempField.Type != Word.WdFieldType.wdFieldLink)
{
//显示进度
WordApp.StatusBar = "当前进度:" + Math.Round((i4 * 100d / i5), 2) + "%";
continue;
}
TempStr = TempField.Code.Text;
if (TempStr.Contains('"'))
{
if (wstDic.ContainsKey(FunC.LinkSheet(TempStr)))
{
TempField.Select();
TempTable = WordApp.Selection.Tables[1];
WordRows = TempTable.Rows.Count;
WordColumns = TempTable.Columns.Count;
ExcelRows = int.Parse(wstDic[FunC.LinkSheet(TempStr)].Split(':')[0]);
ExcelColumns = int.Parse(wstDic[FunC.LinkSheet(TempStr)].Split(':')[1]);
TempInt = WordRows - ExcelRows;
//检查行数
if (TempInt < 0)
{
for (int i = 1; i <= Math.Abs(TempInt); i++)
{
TempTable.Cell(Math.Max(WordRows - 1, 1), 1).Range.Rows.Add(TempTable.Cell(Math.Max(WordRows - 1, 1), 1).Range.Rows);
}
}
else if (TempInt > 0)
{
for (int i = TempInt; i >= 1; i--)
{
TempTable.Cell(Math.Max(WordRows - i, 1), 1).Range.Rows.Delete();
}
}
TempInt = WordColumns - ExcelColumns;
//检查列数
if (TempInt < 0)
{
for (int i = 1; i <= Math.Abs(TempInt); i++)
{
TempTable.Cell(ExcelRows, Math.Max(WordColumns - 2, 1)).Range.Columns.Add(TempTable.Cell(ExcelRows, Math.Max(WordColumns - 2, 1)).Range.Columns);
}
}
else if (TempInt > 0)
{
for (int i = TempInt; i >= 1; i--)
{
TempTable.Cell(ExcelRows, Math.Max(WordColumns - 1, 1)).Range.Columns.Delete();
}
}
TempField.Update();
}
}
//显示进度
WordApp.StatusBar = "当前进度:" + Math.Round((i4 * 100d / i5), 2) + "%";
}
WordApp.ScreenUpdating = true;//打开屏幕刷新
MessageBox.Show("域更新完成！");
}
private void BtnCreate_Click(object sender, EventArgs e)
{
string PrevTableName = "", Query = "", PrimaryKeyFields = "", PrevDBName = "";
int Row1 = TransflexViewXls.RowCount - 1;
int Counter = 0;
CreateDatabases();
if (SqlCon.State == ConnectionState.Closed)
{
SqlCon.Open();
}
string FieldName, FieldType, Alias, KeyFieldInd, NullInd, PrimaryKeyInd, FieldLength;
string AutoIncreamentInd, TableName = "", DatabaseName, ViewFieldName, TempField;
while (Row1 > 0)
{
if (TransflexViewXls["IsAView", Counter].Value.ToString() == "Y")
{
Counter++;
Row1--;
continue;
}
FieldName = TransflexViewXls["FieldName", Counter].Value.ToString();
FieldType = TransflexViewXls["FieldType", Counter].Value.ToString();
TempField = TransflexViewXls["FieldLength", Counter].Value.ToString();
if (TempField == null || TempField == "")
{
TempField = "0";
}
FieldLength = Convert.ToString(TempField.Replace('.', ','));
Alias = TransflexViewXls["Alias", Counter].Value.ToString();
KeyFieldInd = TransflexViewXls["KeyFieldInd", Counter].Value.ToString();
NullInd = TransflexViewXls["NullInd", Counter].Value.ToString();
PrimaryKeyInd = TransflexViewXls["PrimaryKeyInd", Counter].Value.ToString();
AutoIncreamentInd = TransflexViewXls["AutoIncreamentInd", Counter].Value.ToString();
TableName = TransflexViewXls["TableName", Counter].Value.ToString();
DatabaseName = TransflexViewXls["DatabaseName", Counter].Value.ToString();
ViewFieldName = TransflexViewXls["ViewFieldName", Counter].Value.ToString();
if (PrevTableName != TableName)
{
if (Counter != 0)
{
if (PrimaryKeyFields != "")
{
Query = Query + ", CONSTRAINT " + PrevTableName + "PK PRIMARY KEY(" + PrimaryKeyFields + ")";
}
Query = Query + ")";
strQ = "USE " + DatabaseName + "\n"
+ "select table_name TN from INFORMATION_SCHEMA.tables where table_name = '" + PrevTableName + "'";
cmd = new SqlCommand(strQ, SqlCon);
Rdr = cmd.ExecuteReader();
if (Rdr.HasRows == true && Rdr.Read() && Rdr["TN"].ToString() == PrevTableName)
{
Rdr.Close();
}
else
{
Rdr.Close();
cmd = new SqlCommand(Query, SqlCon);
cmd.ExecuteNonQuery();
}
PrimaryKeyFields = "";
}
Query = "USE " + DatabaseName + "\n" + "CREATE TABLE " + TableName + "(";
PrevTableName = TableName;
PrevDBName = DatabaseName;
}
else
{
Query = Query + ",";
}
FieldType = FieldType.ToUpper();
Query = Query + FieldName + " " + FieldType;
if (FieldType != "INT" && FieldType != "DATE" && FieldType != "DATETIME" && FieldType != "CHAR")
{
Query = Query + "(" + FieldLength + ")";
}
if (PrimaryKeyInd == "Y")
{
if (PrimaryKeyFields != "")
{
PrimaryKeyFields = PrimaryKeyFields + "," + FieldName;
}
else
{
PrimaryKeyFields = FieldName;
}
}
if (AutoIncreamentInd == "Y")
{
Query = Query + " IDENTITY(1,1) ";
}
if (NullInd == "N")
{
Query = Query + " NOT NULL ";
}
Counter++;
Row1--;
}
if (PrimaryKeyFields != "")
{
Query = Query + ", CONSTRAINT " + PrevTableName + "PK PRIMARY KEY(" + PrimaryKeyFields + ")";
}
Query = Query + ")";
strQ = "USE " + PrevDBName + "\n"
+ "select table_name TN from INFORMATION_SCHEMA.tables where table_name = '" + PrevTableName + "'";
cmd = new SqlCommand(strQ, SqlCon);
Rdr = cmd.ExecuteReader();
if (Rdr.HasRows == true && Rdr.Read() && Rdr["TN"].ToString() == PrevTableName)
{
Rdr.Close();
}
else
{
Rdr.Close();
cmd = new SqlCommand(Query, SqlCon);
cmd.ExecuteNonQuery();
}
if (SqlCon.State == ConnectionState.Open)
{
SqlCon.Close();
}
BtnIns.Enabled = true;
}