Example #1
0
        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;
        }
Example #2
0
        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();
 }
Example #4
0
        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
            }
        }