public static DataTable RemoveDuplicates(this DataTable table, string columnName) { var urls = new List<string>(); var rowsToRemove = new List<DataRow>(); foreach (DataRow row in table.Rows) { var url = row[columnName].ToString(); if (urls.Contains(url)) { rowsToRemove.Add(row); } else { urls.Add(url); } } rowsToRemove.ForEach(i => table.Rows.Remove(i)); table.AcceptChanges(); return table; }
public static void RemoveNull(this DataSet dataSet) { for (var t = 0; t < dataSet.Tables.Count; ++t) for (var r = 0; r < dataSet.Tables[t].Rows.Count; ++r) if (dataSet.Tables[t].Rows[r].IsNull(0)) dataSet.Tables[t].Rows[r].Delete(); dataSet.AcceptChanges(); }
public static void ReplaceAllOccurrences(this DataTable dt, object ofWhat, object withWhat) { for (int r = 0; r < dt.Rows.Count; ++r) { for (int c = 0; c < dt.Columns.Count; ++c) { if (dt.Rows[r][c].Equals(ofWhat)) { dt.Rows[r][c] = withWhat; } } } dt.AcceptChanges(); }
public static int ExportToExcel(this DataTable dt, string filedirectory = null, string filename = null) { if (filedirectory == null) filedirectory = DefaultExcelFileDirectory; if (filename == null) filename = DefaultExcelFileName; if (!Directory.Exists(filedirectory)) { Directory.CreateDirectory(filedirectory); } string filepath = filedirectory + "\\" + filename; try { dt.AcceptChanges(); if (dt == null || dt.Columns.Count == 0) { return -2; // not saved, data null } Excel.Application app = new Excel.Application(); app.Workbooks.Add(); Excel._Worksheet sheet = app.ActiveSheet; // headings var cols = dt.Columns; for (int i = 1; i < cols.Count; ++i) { sheet.Cells[1, i] = cols[i].ColumnName; } // rows var rows = dt.Rows; for (int i = 0; i < rows.Count; ++i) { for (int j = 1; j < cols.Count; ++j) { sheet.Cells[(i + 2), j] = rows[i][j]; } } if (filepath != null && filepath != "") { try { File.Delete(filepath); sheet.SaveAs(filepath); app.Quit(); return 0; // file saved } catch { return 1; // not saved, error with file path } } else { app.Visible = true; return 2; // empty filepath } } catch { return -1; // exception thrown } }