public static string DeleteRowsFromTable(this Excel.Worksheet sheet, string tableName, bool refresh) { Excel.Range primaryKeyColumnRange = null; Excel.Range columnRange = null; Excel.Range sheetCellsRange = null; string primaryKey = string.Empty; string sql = string.Empty; Excel.Range deletedRows = null; double deletedRowNum; bool rowsDeleted = false; try { deletedRows = sheet.Range["A:A"].SpecialCells(Excel.XlCellType.xlCellTypeBlanks).EntireRow; //got at least 1 empty row foreach (Excel.Range row in deletedRows) { deletedRowNum = sheet.Application.WorksheetFunction.CountA(row); if (deletedRowNum == 0) { rowsDeleted = true; } } } catch (System.Runtime.InteropServices.COMException e) { rowsDeleted = false; } if (rowsDeleted == true | refresh == true) { primaryKey = sheet.PrimaryKey(); columnRange = sheet.Range["A1:CV1"]; sheetCellsRange = sheet.Cells; primaryKeyColumnRange = columnRange.Find(primaryKey, LookAt: Excel.XlLookAt.xlWhole); object[,] pkValues = (object[, ])sheet.Columns[primaryKeyColumnRange.Column].Cells.Value; List <string> primaryKeyValues = pkValues.Cast <object>().ToList().ConvertAll(x => Convert.ToString(x)); primaryKeyValues.RemoveAt(0); primaryKeyValues.RemoveAll(str => String.IsNullOrEmpty(str)); string primaryKeyValuesJoined = string.Join(",", primaryKeyValues); primaryKeyValuesJoined = "'" + primaryKeyValuesJoined.Replace(",", "','") + "'"; if (refresh == false) { sql = "Delete from " + tableName + " Where " + primaryKey + " NOT IN( " + primaryKeyValuesJoined + ")"; } else if (refresh == true) { sql = "Select " + primaryKey + " from " + tableName + " Where " + primaryKey + " NOT IN( " + primaryKeyValuesJoined + ")"; } } //Debug.WriteLine(sql); return(sql); }
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); }