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