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