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);
}
}
}
