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