public static Excel.CustomProperty GetProperty(this Excel.Worksheet sheet, string propertyName) { Excel.CustomProperty customProperty = null; Excel.CustomProperties customProperties = null; try { customProperties = sheet.CustomProperties; for (int i = 1; i <= customProperties.Count; i++) { customProperty = customProperties[i]; if (customProperty != null && customProperty.Name.ToLower() == propertyName.ToLower()) { return(customProperty); } else { customProperty = null; } } } catch (Exception ex) { Console.Write(ex.Message); } finally { if (customProperties != null) { Marshal.ReleaseComObject(customProperties); } } return(customProperty); }
private static Excel.CustomProperty AddProperty(Excel.Worksheet worksheet, string propertyName, string value) { if (null == worksheet) { throw new ArgumentNullException("worksheet"); } if (string.IsNullOrWhiteSpace(propertyName)) { throw new ArgumentNullException("propertyName"); } Excel.CustomProperties properties = worksheet.CustomProperties; Excel.CustomProperty property = properties.Add(propertyName, value); return(property); }
public static Excel.CustomProperty AddProperty(this Excel.Worksheet sheet, string propertyName, object propertyValue) { Excel.CustomProperties customProperties = null; Excel.CustomProperty customProperty = null; try { customProperties = sheet.CustomProperties; customProperty = customProperties.Add(propertyName, propertyValue); } finally { if (customProperties != null) { Marshal.ReleaseComObject(customProperties); } } return(customProperty); }
public static string PrimaryKey(this Excel.Worksheet sheet) { Excel.CustomProperties customProperties = null; Excel.CustomProperty primaryKeyProperty = null; string keyName = null; try { customProperties = sheet.CustomProperties; for (int i = 1; i <= customProperties.Count; i++) { primaryKeyProperty = customProperties[i]; if (primaryKeyProperty.Name == "PrimaryKey") { keyName = primaryKeyProperty.Value.ToString(); } if (primaryKeyProperty != null) { Marshal.ReleaseComObject(primaryKeyProperty); } } } catch (Exception ex) { Console.Write(ex.Message); } finally { if (primaryKeyProperty != null) { Marshal.ReleaseComObject(primaryKeyProperty); } if (customProperties != null) { Marshal.ReleaseComObject(customProperties); } } return(keyName); }
/// <summary> /// Checks whether the sheet has a primary key custom property and /// then return true or false indicating it is "connected" to a db table /// </summary> /// <param name="sheet"></param> /// <returns>bool</returns> public static bool ConnectedToDb(this Excel.Worksheet sheet) { Excel.CustomProperties customProperties = null; Excel.CustomProperty primaryKeyProperty = null; try { customProperties = sheet.CustomProperties; for (int i = 1; i <= customProperties.Count; i++) { primaryKeyProperty = customProperties[i]; if (primaryKeyProperty != null) { Marshal.ReleaseComObject(primaryKeyProperty); } } if (primaryKeyProperty != null) { return(true); } } catch (Exception ex) { Console.Write(ex.Message); } finally { if (primaryKeyProperty != null) { Marshal.ReleaseComObject(primaryKeyProperty); } if (customProperties != null) { Marshal.ReleaseComObject(customProperties); } } return(false); }
private static Excel.CustomProperty GetProperty(Excel.Worksheet worksheet, string propertyName) { if (null == worksheet) { throw new ArgumentNullException("worksheet"); } if (string.IsNullOrWhiteSpace(propertyName)) { throw new ArgumentNullException("propertyName"); } Excel.CustomProperties properties = worksheet.CustomProperties; foreach (Excel.CustomProperty property in properties) { if (property.Name == propertyName) { return(property); } } return(null); }
private void tvTables_NodeMouseDoubleClick(object sender, TreeNodeMouseClickEventArgs e) { Excel.Worksheet sheet = null; Excel.Range insertionRange = null; Excel.QueryTable queryTable = null; Excel.QueryTables queryTables = null; Excel.Range cellRange = null; Excel.CustomProperties sheetProperties = null; Excel.CustomProperty primaryKeyProperty = null; SqlConnectionStringBuilder builder = null; string connString = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=@servername;Initial Catalog=@databasename"; string connStringSQL = "OLEDB;Provider=SQLOLEDB.1;Persist Security Info=True;User ID=@username;Password=@password;Data Source=@servername;Initial Catalog=@databasename"; string databaseName = string.Empty; string tableName = string.Empty; try { var module = this.AddinModule as AddinModule; module.SheetChangeEvent = false; tableName = e.Node.Text; sheet = ExcelApp.ActiveSheet as Excel.Worksheet; cellRange = sheet.Cells; insertionRange = cellRange[1, 1] as Excel.Range; builder = new SqlConnectionStringBuilder(dcd.ConnectionString); databaseName = builder.InitialCatalog; if (!builder.IntegratedSecurity) { connString = connStringSQL; } connString = connString.Replace("@servername", builder.DataSource) .Replace("@databasename", databaseName) .Replace("@username", builder.UserID) .Replace("@password", builder.Password); queryTables = sheet.QueryTables; if (queryTables.Count > 0) { queryTable = queryTables.Item(1); queryTable.CommandText = String.Format("SELECT * FROM [{0}].{1}", databaseName, tableName); } else { queryTable = queryTables.Add(connString, insertionRange, String.Format("SELECT * FROM [{0}].{1}", databaseName, tableName)); } queryTable.RefreshStyle = Excel.XlCellInsertionMode.xlOverwriteCells; queryTable.PreserveColumnInfo = true; queryTable.PreserveFormatting = true; queryTable.Refresh(false); var primaryKey = SqlUtils.GetPrimaryKey(dcd.ConnectionString, tableName); // save original table this.tableName = tableName; // to sheet name must be less then 31 characters long sheet.Name = tableName.Substring(0, Math.Min(tableName.Length, 30)); chPrimaryKey.Text = primaryKey; sheetProperties = sheet.CustomProperties; primaryKeyProperty = sheetProperties.Add("PrimaryKey", primaryKey); module.SheetChangeEvent = true; } catch (Exception ex) { Console.Write(ex.Message); } finally { if (primaryKeyProperty != null) { Marshal.ReleaseComObject(primaryKeyProperty); } if (sheetProperties != null) { Marshal.ReleaseComObject(sheetProperties); } if (cellRange != null) { Marshal.ReleaseComObject(cellRange); } if (queryTables != null) { Marshal.ReleaseComObject(queryTables); } if (queryTable != null) { Marshal.ReleaseComObject(queryTable); } if (insertionRange != null) { Marshal.ReleaseComObject(insertionRange); } if (sheet != null) { Marshal.ReleaseComObject(sheet); } } }
private void tvTables_NodeMouseDoubleClick(object sender, TreeNodeMouseClickEventArgs e) { Excel.Worksheet sheet = null; Excel.Range insertionRange = null; Excel.QueryTable queryTable = null; Excel.QueryTables queryTables = null; Excel.Range cellRange = null; Excel.CustomProperties sheetProperties = null; Excel.CustomProperty primaryKeyProperty = null; Excel.CustomProperty tableColumnsProperty = null; Excel.CustomProperty tableLoadedProperty = null; SqlConnectionStringBuilder builder = null; string connString = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=@servername;Initial Catalog=@databasename"; string connStringSQL = "OLEDB;Provider=SQLOLEDB.1;Persist Security Info=True;User ID=@username;Password=@password;Data Source=@servername;Initial Catalog=@databasename"; string databaseName = string.Empty; string tableName = string.Empty; string xmlString = string.Empty; try { ExcelApp.EnableEvents = false; tableName = e.Node.Text; sheet = ExcelApp.ActiveSheet as Excel.Worksheet; cellRange = sheet.Cells; insertionRange = cellRange[1, 1] as Excel.Range; builder = new SqlConnectionStringBuilder(dcd.ConnectionString); databaseName = builder.InitialCatalog; if (!builder.IntegratedSecurity) { connString = connStringSQL; } connString = connString.Replace("@servername", builder.DataSource) .Replace("@databasename", databaseName) .Replace("@username", builder.UserID) .Replace("@password", builder.Password); queryTables = sheet.QueryTables; //clear Excel Querytables foreach (Excel.QueryTable prop in queryTables) { prop.Delete(); } sheet.Cells.ClearContents(); if (queryTables.Count > 0) { queryTable = queryTables.Item(1); queryTable.CommandText = String.Format("SELECT * FROM [{0}].{1}", databaseName, tableName); } else { queryTable = queryTables.Add(connString, insertionRange, String.Format("SELECT * FROM [{0}].{1}", databaseName, tableName)); } queryTable.RefreshStyle = Excel.XlCellInsertionMode.xlOverwriteCells; queryTable.PreserveColumnInfo = true; queryTable.PreserveFormatting = true; queryTable.Refresh(false); var primaryKey = SqlUtils.GetPrimaryKey(dcd.ConnectionString, tableName); var tableColumns = SqlUtils.GetAllColumns(dcd.ConnectionString, tableName); // save original table this.tableName = tableName; // to sheet name must be less then 31 characters long sheet.Name = tableName.Substring(0, Math.Min(tableName.Length, 30)); chPrimaryKey.Text = primaryKey; sheetProperties = sheet.CustomProperties; //clear Excel properties to prevent duplicate primary key property error foreach (Excel.CustomProperty prop in sheetProperties) { prop.Delete(); } primaryKeyProperty = sheetProperties.Add("PrimaryKey", primaryKey); tableLoadedProperty = sheetProperties.Add("TableLoaded", 1); foreach (var cols in tableColumns) { xmlString += "<row column=\"" + cols.Key + "\" "; xmlString += "columndatatype=\"" + cols.Value + "\">"; xmlString += cols.Key; xmlString += "</row>"; } tableColumnsProperty = sheetProperties.Add("TableColumns", xmlString); ExcelApp.EnableEvents = true; } catch (Exception ex) { Console.Write(ex.Message); throw; } finally { if (primaryKeyProperty != null) { Marshal.ReleaseComObject(primaryKeyProperty); } if (tableLoadedProperty != null) { Marshal.ReleaseComObject(tableLoadedProperty); } if (tableColumnsProperty != null) { Marshal.ReleaseComObject(tableColumnsProperty); } if (sheetProperties != null) { Marshal.ReleaseComObject(sheetProperties); } if (cellRange != null) { Marshal.ReleaseComObject(cellRange); } if (queryTables != null) { Marshal.ReleaseComObject(queryTables); } if (queryTable != null) { Marshal.ReleaseComObject(queryTable); } if (insertionRange != null) { Marshal.ReleaseComObject(insertionRange); } if (sheet != null) { Marshal.ReleaseComObject(sheet); } } }