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);
}
public static string ChangesToSql(this Excel.Worksheet sheet, string tableName, string primaryKeyName)
{
Excel.CustomProperty customProperty = null;
string xml = string.Empty;
string sql = string.Empty;
try
{
customProperty = sheet.GetProperty("uncommittedchanges");
if (customProperty != null)
{
xml = ToSafeXml("<uncommittedchanges>" + customProperty.Value.ToString() + "</uncommittedchanges>");
XDocument doc = XDocument.Parse(xml);
foreach (var dm in doc.Descendants("row"))
{
string key = dm.Attribute("key").Value;
string keyDataType = dm.Attribute("keydatatype").Value;
string column = dm.Attribute("column").Value;
string columnDataType = dm.Attribute("columndatatype").Value;
string value = dm.Value;
sql += "UPDATE " + tableName + " SET " + column + " = ";
if (columnDataType.ToLower().Contains("date") || columnDataType.ToLower().Contains("string") || columnDataType.ToLower().Contains("boolean"))
{
sql += "'" + value + "'";
}
else
{
sql += value;
}
sql += " WHERE " + primaryKeyName + " = ";
if (keyDataType.ToLower().Contains("date") || keyDataType.ToLower().Contains("string"))
{
sql += "'" + key + "'";
}
else
{
sql += key;
}
sql += Environment.NewLine;
}
}
}
finally
{
if (customProperty != null)
{
Marshal.ReleaseComObject(customProperty);
}
}
return(sql);
}
private void Application_SheetChange(object Sh, Excel.Range Target)
{
Excel.CustomProperty tableLoadedProperty = null;
Excel.Worksheet activeSheet = ((Excel.Worksheet)Application.ActiveSheet);
tableLoadedProperty = activeSheet.GetProperty("TableLoaded");
if (tableLoadedProperty != null)
{
activeSheet.AddChangedRow(Target);
}
}
private void btnApplyChangesToDb_Click(object sender, EventArgs e)
{
Excel.Worksheet sheet = null;
Excel.CustomProperty primaryKeyProperty = null;
string primaryKey = string.Empty;
try
{
if (MessageBox.Show("This will commit the changes to the database. This action cannot be reversed. Are you sure?", "Confirm", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Warning) == DialogResult.Yes)
{
sheet = ExcelApp.ActiveSheet as Excel.Worksheet;
if (sheet != null)
{
primaryKeyProperty = sheet.GetProperty("PrimaryKey");
if (primaryKeyProperty != null)
{
primaryKey = primaryKeyProperty.Value.ToString();
string sql = sheet.ChangesToSql(this.tableName, primaryKey);
sql += Environment.NewLine;
sql += sheet.DeleteRowsFromTable(this.tableName, false);
sql += Environment.NewLine;
sql += sheet.InsertRowsIntoTable(this.tableName);
if (!string.IsNullOrEmpty(sql))
{
using (SqlConnection conn = new SqlConnection(dcd.ConnectionString))
{
SqlCommand cmd = new SqlCommand(sql, conn);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
cmd.ExecuteNonQuery();
}
}
RefreshSheetData();
}
}
}
}
}
finally
{
if (primaryKeyProperty != null)
{
Marshal.ReleaseComObject(primaryKeyProperty);
}
if (sheet != null)
{
Marshal.ReleaseComObject(sheet);
}
}
}
private void RefreshSheetData()
{
Excel.Worksheet sheet = null;
Excel.QueryTables queryTables = null;
Excel.QueryTable queryTable = null;
Excel.CustomProperty changesProperty = null;
try
{
var module = this.AddinModule as AddinModule;
module.SheetChangeEvent = false;
sheet = ExcelApp.ActiveSheet as Excel.Worksheet;
if (sheet != null)
{
queryTables = sheet.QueryTables;
if (queryTables.Count > 0)
{
queryTable = queryTables.Item(1);
queryTable.RefreshStyle = Excel.XlCellInsertionMode.xlOverwriteCells;
queryTable.PreserveColumnInfo = true;
queryTable.PreserveFormatting = true;
queryTable.Refresh(false);
}
changesProperty = sheet.GetProperty("uncommittedchanges");
if (changesProperty != null)
{
changesProperty.Delete();
}
}
module.SheetChangeEvent = true;
}
finally
{
if (changesProperty != null)
{
Marshal.ReleaseComObject(changesProperty);
}
if (queryTable != null)
{
Marshal.ReleaseComObject(queryTable);
}
if (queryTables != null)
{
Marshal.ReleaseComObject(queryTables);
}
if (sheet != null)
{
Marshal.ReleaseComObject(sheet);
}
}
}
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 Get(Excel.Worksheet worksheet, string propertyName)
{
if (null == worksheet)
{
throw new ArgumentNullException("worksheet");
}
if (string.IsNullOrWhiteSpace(propertyName))
{
throw new ArgumentNullException("propertyName");
}
Excel.CustomProperty property = GetProperty(worksheet, propertyName);
if (null == property)
{
return(null);
}
return(property.Value);
}
public static void Set(Excel.Worksheet worksheet, string propertyName, string value)
{
if (null == worksheet)
{
throw new ArgumentNullException("worksheet");
}
if (string.IsNullOrWhiteSpace(propertyName))
{
throw new ArgumentNullException("propertyName");
}
Excel.CustomProperty property = GetProperty(worksheet, propertyName);
if (null == property)
{
property = AddProperty(worksheet, propertyName, value);
}
property.Value = value;
}
private void btnSaveChangesToFile_Click(object sender, EventArgs e)
{
Excel.Worksheet sheet = null;
Excel.CustomProperty primaryKeyProperty = null;
string primaryKey = string.Empty;
string tableName = string.Empty;
try
{
sheet = ExcelApp.ActiveSheet as Excel.Worksheet;
if (sheet != null)
{
tableName = sheet.Name;
primaryKeyProperty = sheet.GetProperty("PrimaryKey");
if (primaryKeyProperty != null)
{
primaryKey = primaryKeyProperty.Value.ToString();
string sql = sheet.ChangesToSql(tableName, primaryKey);
diagSaveFile.ShowDialog();
if (!string.IsNullOrEmpty(diagSaveFile.FileName))
{
File.WriteAllText(diagSaveFile.FileName, sql);
RefreshSheetData();
}
}
}
}
finally
{
if (primaryKeyProperty != null)
{
Marshal.ReleaseComObject(primaryKeyProperty);
}
if (sheet != null)
{
Marshal.ReleaseComObject(sheet);
}
}
}
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);
}
public void RefreshChanges()
{
Excel.Worksheet activeSheet = null;
Excel.CustomProperty changesProperty = null;
string xml = string.Empty;
try
{
activeSheet = ExcelApp.ActiveSheet as Excel.Worksheet;
changesProperty = activeSheet.GetProperty("UncommittedChanges");
lvSheetChanges.Items.Clear();
if (changesProperty != null)
{
lvSheetChanges.Visible = true;
xml = ToSafeXml("<uncommittedchanges>" + changesProperty.Value.ToString() + "</uncommittedchanges>");
XDocument doc = XDocument.Parse(xml);
foreach (var dm in doc.Descendants("row"))
{
ListViewItem item = new ListViewItem(new string[]
{
dm.Attribute("key").Value,
dm.Attribute("column").Value,
dm.Value
});
lvSheetChanges.Items.Add(item);
}
}
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
finally
{
//if (activeSheet != null) Marshal.ReleaseComObject(activeSheet);
//if (changesProperty != null) Marshal.ReleaseComObject(changesProperty);
}
}
/// <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);
}
public static string InsertRowsIntoTable(this Excel.Worksheet sheet, string tableName)
{
string sql = string.Empty;
List <string> rowValues = new List <string>();
string rowValuesJoined = string.Empty;
string primaryKey = string.Empty;
Excel.CustomProperty tableColumnsProperty = null;
Dictionary <string, string> tableColumnTypes = new Dictionary <string, string>();
string xml = string.Empty;
tableColumnsProperty = sheet.GetProperty("TableColumns");
try
{
if (tableColumnsProperty != null)
{
xml = ToSafeXml("<tablecolumns>" + tableColumnsProperty.Value.ToString() + "</tablecolumns>");
XDocument doc = XDocument.Parse(xml);
foreach (var dm in doc.Descendants("row"))
{
string colValue = dm.Attribute("column").Value;
string colDataTypeValue = dm.Attribute("columndatatype").Value;
tableColumnTypes.Add(colValue, colDataTypeValue);
}
}
}
catch (System.NullReferenceException e)
{
Console.Write(e.Message);
}
primaryKey = sheet.PrimaryKey();
//int lastTableRow;
int lastTableColumn;
//https://stackoverflow.com/questions/7674573/programmatically-getting-the-last-filled-excel-row-using-c-sharp
// Find the last real row
//lastTableRow = sheet.Cells.Find("*", System.Reflection.Missing.Value,System.Reflection.Missing.Value, System.Reflection.Missing.Value, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlPrevious, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row;
// Find the last real column
lastTableColumn = sheet.Cells.Find("*", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Excel.XlSearchOrder.xlByColumns, Excel.XlSearchDirection.xlPrevious, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Column;
try
{
Excel.Range insertRows = sheet.Columns["A:A"].SpecialCells(Excel.XlCellType.xlCellTypeBlanks).EntireRow as Excel.Range;
Excel.Range headerRow = sheet.Rows["1:1"].EntireRow as Excel.Range;
object[,] hdrValues = (object[, ])headerRow.Cells.Value;
List <string> headerRowValues = hdrValues.Cast <object>().ToList().ConvertAll(x => Convert.ToString(x));
headerRowValues.RemoveAll(header => header == string.Empty);
headerRowValues.Remove(primaryKey);
string headerRowValuesJoined = string.Join(",", headerRowValues);
foreach (Excel.Range row in insertRows)
{
object[,] rwValues = (object[, ])row.Cells.Value2;
rowValues = rwValues.Cast <object>().ToList().ConvertAll(x => Convert.ToString(x).Replace("'", "''"));
rowValues.RemoveRange(lastTableColumn, rowValues.Count - lastTableColumn);
rowValues.RemoveAt(0);
for (int i = 0; i < rowValues.Count; i++)
{
try
{
if (tableColumnTypes[headerRowValues[i]].ToString().Contains("date") & !string.IsNullOrEmpty(rowValues[i]))
{
rowValues[i] = DateTime.FromOADate(Convert.ToDouble(rowValues[i])).ToString(CultureInfo.InvariantCulture);
}
}
catch (System.Collections.Generic.KeyNotFoundException e)
{
Console.WriteLine(e.Message);
}
if (string.IsNullOrEmpty(rowValues[i]))
{
rowValues[i] = "NULL";
}
}
rowValuesJoined = string.Join("','", rowValues);
rowValuesJoined = "'" + rowValuesJoined + "'";
rowValuesJoined = rowValuesJoined.Replace("'NULL'", "NULL");
HashSet <string> unique_items = new HashSet <string>(rowValuesJoined.Split(','));
if (unique_items.Count != 1)
{
sql += "Insert into " + tableName + "(" + headerRowValuesJoined + ") VALUES( " + rowValuesJoined + ")";
sql += Environment.NewLine;
}
}
}
catch (System.Runtime.InteropServices.COMException e)
{
Console.Write(e.Message);
}
//Debug.WriteLine(sql);
return(sql);
}
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);
}
}
}
public static void AddChangedRow(this Excel.Worksheet sheet, int col, int row)
{
Excel.Range columnRange = null;
Excel.Range primaryKeyColumnRange = null;
Excel.Range primaryKeyValueRange = null;
Excel.Range rowValueRange = null;
Excel.Range sheetCellRange = null;
Excel.CustomProperty uncommittedChangesProperty = null;
string primaryKey = string.Empty;
string primaryKeyDataType = string.Empty;
object primaryKeyValue = string.Empty;
string columnName = string.Empty;
object rowValue = string.Empty;
string rowValueDataType = string.Empty;
try
{
primaryKey = sheet.PrimaryKey();
columnRange = sheet.Range["A1:CV1"];
sheetCellRange = sheet.Cells;
rowValueRange = sheetCellRange[row, col] as Excel.Range;
primaryKeyColumnRange = columnRange.Find(primaryKey);
if (primaryKeyColumnRange != null)
{
primaryKeyValueRange = sheetCellRange[row, primaryKeyColumnRange.Column] as Excel.Range;
if (primaryKeyValueRange != null)
{
primaryKeyValue = primaryKeyValueRange.Value;
primaryKeyDataType = primaryKeyValue.GetType().ToString();
}
}
columnName = sheet.ColumnName(col);
if (rowValueRange != null)
{
rowValue = rowValueRange.Value;
rowValueDataType = rowValue.GetType().ToString();
}
string xmlString = "<row key=\"" + primaryKeyValue.ToString() + "\" ";
xmlString += "keydatatype=\"" + primaryKeyDataType + "\" ";
xmlString += "column=\"" + columnName + "\" ";
xmlString += "columndatatype=\"" + rowValueDataType + "\">";
xmlString += rowValue.ToString();
xmlString += "</row>";
xmlString = stripNonValidXMLCharacters(xmlString);
uncommittedChangesProperty = sheet.GetProperty("UncommittedChanges");
if (uncommittedChangesProperty == null)
{
uncommittedChangesProperty = sheet.AddProperty("UncommittedChanges", xmlString);
}
else
{
uncommittedChangesProperty.Value = uncommittedChangesProperty.Value + xmlString;
}
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
finally
{
}
}
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);
}
}
}
public void RefreshChanges()
{
Excel.Worksheet activeSheet = null;
Excel.CustomProperty changesProperty = null;
string xml = string.Empty;
string sql = string.Empty;
try
{
activeSheet = ExcelApp.ActiveSheet as Excel.Worksheet;
changesProperty = activeSheet.GetProperty("UncommittedChanges");
lvSheetChanges.Items.Clear();
if (changesProperty != null)
{
lvSheetChanges.Visible = true;
xml = ToSafeXml("<uncommittedchanges>" + changesProperty.Value.ToString() + "</uncommittedchanges>");
XDocument doc = XDocument.Parse(xml);
foreach (var dm in doc.Descendants("row"))
{
ListViewItem item = new ListViewItem(new string[]
{
dm.Attribute("key").Value,
dm.Attribute("column").Value,
dm.Value
});
lvSheetChanges.Items.Add(item);
}
}
sql = activeSheet.DeleteRowsFromTable(this.tableName, true);
var primaryKey = activeSheet.PrimaryKey();
if (!string.IsNullOrEmpty(sql))
{
using (SqlConnection conn = new SqlConnection(dcd.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
SqlDataReader dbReader;
conn.Open();
dbReader = cmd.ExecuteReader();
while (dbReader.Read())
{
ListViewItem item = new ListViewItem(new string[]
{
dbReader[primaryKey].ToString(),
primaryKey,
"delete"
});
lvSheetChanges.Items.Add(item);
}
}
conn.Close();
}
}
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
finally
{
//if (activeSheet != null) Marshal.ReleaseComObject(activeSheet);
//if (changesProperty != null) Marshal.ReleaseComObject(changesProperty);
}
}
public static void AddChangedRow(this Excel.Worksheet sheet, Excel.Range changedRange)
{
Excel.Range columnRange = null;
Excel.Range primaryKeyColumnRange = null;
Excel.Range primaryKeyValueRange = null;
Excel.Range rowValueRange = null;
Excel.Range sheetCellsRange = null;
Excel.Range rowsRange = null;
Excel.Range colsRange = null;
Excel.CustomProperty uncommittedChangesProperty = null;
object rowValue = string.Empty;
string rowValueDataType = string.Empty;
string primaryKey = string.Empty;
string primaryKeyDataType = string.Empty;
object primaryKeyValue = string.Empty;
string columnName = string.Empty;
string xmlString = string.Empty;
try
{
primaryKey = sheet.PrimaryKey();
columnRange = sheet.Range["A1:CV1"];
sheetCellsRange = sheet.Cells;
primaryKeyColumnRange = columnRange.Find(primaryKey, LookAt: Excel.XlLookAt.xlWhole);
rowsRange = changedRange.Rows;
colsRange = rowsRange.Columns;
foreach (Excel.Range row in rowsRange)
{
if (primaryKeyColumnRange != null)
{
int rowNum = row.Row;
int colNum = primaryKeyColumnRange.Column;
primaryKeyValueRange = sheetCellsRange[rowNum, colNum] as Excel.Range;
if (primaryKeyValueRange != null)
{
primaryKeyValue = primaryKeyValueRange.Value;
primaryKeyDataType = primaryKeyValue.GetType().ToString();
foreach (Excel.Range col in colsRange)
{
colNum = col.Column;
columnName = sheet.ColumnName(colNum);
rowValueRange = sheetCellsRange[rowNum, col.Column] as Excel.Range;
if (rowValueRange != null)
{
rowValue = rowValueRange.Value;
rowValueDataType = rowValue.GetType().ToString();
xmlString += "<row key=\"" + primaryKeyValue.ToString() + "\" ";
xmlString += "keydatatype=\"" + primaryKeyDataType + "\" ";
xmlString += "column=\"" + columnName + "\" ";
xmlString += "columndatatype=\"" + rowValueDataType + "\">";
xmlString += rowValue.ToString();
xmlString += "</row>";
}
}
}
}
}
uncommittedChangesProperty = sheet.GetProperty("UncommittedChanges");
if (uncommittedChangesProperty == null)
{
uncommittedChangesProperty = sheet.AddProperty("UncommittedChanges", xmlString);
}
else
{
uncommittedChangesProperty.Value = uncommittedChangesProperty.Value + xmlString;
}
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
finally
{
if (uncommittedChangesProperty != null)
{
Marshal.ReleaseComObject(uncommittedChangesProperty);
}
if (colsRange != null)
{
Marshal.ReleaseComObject(colsRange);
}
if (rowsRange != null)
{
Marshal.ReleaseComObject(rowsRange);
}
if (sheetCellsRange != null)
{
Marshal.ReleaseComObject(sheetCellsRange);
}
if (rowValueRange != null)
{
Marshal.ReleaseComObject(rowValueRange);
}
if (primaryKeyValueRange != null)
{
Marshal.ReleaseComObject(primaryKeyValueRange);
}
if (primaryKeyColumnRange != null)
{
Marshal.ReleaseComObject(primaryKeyColumnRange);
}
}
}