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