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