how to read and write in datagridview row by row in c#?

Question

I have an app with three button . 1:for import from Clipboard 2:for save to db 3:for export Clipboard data

I have read that for long duration process we should use thread. for read from clipboard(bcoz the clipboard data is very much 3k with 22 columns) I need to control the form state from being hang w

ith backgroundprocess. this is full code for read operation:

   private void ReadFromClipboard(DataGridView dg)
    {
        string s = Clipboard.GetText();

        //step 1 : get data from clipboard
        if (s != string.Empty && s.Contains("\r") && s.Contains("\t") && s.Contains("\n"))
        {
            string[] lines = s.Replace("\r", "").Split("\n");
            lines = lines.SkipLast(1).ToArray();//reeove last element null in array
            dg.Rows.Add(lines.Length);
            string[] fields;
            int row = 0;
            int col = 2;
            foreach (string item in lines)
            {
                fields = item.Split('\t');
                //check if excel copied cell biger than datagridview column
                //ColumnCount-2 bcoz Row , Result column addded
                if (fields.Length <= dataGridView1.ColumnCount - 2)
                {
                    for (int fieldIndex = 0; fieldIndex < fields.Length; fieldIndex++)
                    {
                        if (fieldIndex < dg.ColumnCount)
                        {
                            dg[col, row].Value = fields[fieldIndex];
                            col++;
                        }
                    }
                    row++;
                    col = 2;
                }
                else
                {
                    MessageBox.Show("columns not similar");
                    ClearGridView();
                    return;
                }
            }
        }

        //step 2 : fill empty cell in dgv
        for (int rowIndex = 0; rowIndex < dataGridView1.Rows.Count - 1; rowIndex++)
        {
            //fill Row Column Number
            dataGridView1.Rows[rowIndex].Cells[0].Value = rowIndex;

            for (int cellIndex = 2; cellIndex < dataGridView1.Rows[rowIndex].Cells.Count; cellIndex++)
            {
                if (String.IsNullOrEmpty(dataGridView1.Rows[rowIndex].Cells[cellIndex].Value.ToString()))
                    dataGridView1.Rows[rowIndex].Cells[cellIndex].Value = "*";
            }
        }
    }
    private void backgroundWorker1_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e)
    {
        ReadFromClipboard(dataGridView1);
        // Do some work 
        for (int i = 0; i <= 10; i++)
        {
            backgroundWorker1.ReportProgress(i);
            Thread.Sleep(1);
        }
    }

    private void backgroundWorker1_ProgressChanged(object sender, System.ComponentModel.ProgressChangedEventArgs e)
    {
        progress.Value = e.ProgressPercentage;
    }

    private void backgroundWorker1_RunWorkerCompleted(object sender, System.ComponentModel.RunWorkerCompletedEventArgs e)
    {
        MessageBox.Show(e.Result.ToString());
    }

First : I am confuesd that where should I put my Reading From Clipboard Code? Second : Is there a way to simultaneously add a record to the datagridview when it is read from the clipboard. And the progress bar is also active until the end of the operation? THIS IMAGE SHOW MY NEED

Answer 1

@Farshad Valizade, Welcome to Microsoft Q&A, you could try the following code to copy data from excel to datagirdview by using a progressbar to show the progress.

 public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            backgroundWorker1.WorkerReportsProgress = true;
            //backgroundWorker1.WorkerSupportsCancellation = true;
      
        }
        List<string> list = new List<string>();
        private void ReadFromClipboard(DataGridView dg)
        {
            if (Clipboard.GetText() != string.Empty)
            {
    
                string s = Clipboard.GetText();
         
                string[] lines = s.Replace("\n", "").Split('\r');
                list = lines.ToList();
 
               
            }

            //step 2 : fill empty cell in dgv
            //for (int rowIndex = 0; rowIndex < dataGridView1.Rows.Count - 1; rowIndex++)
            //{
            //    //fill Row Column Number
            //    dataGridView1.Rows[rowIndex].Cells[0].Value = rowIndex;

            //    for (int cellIndex = 2; cellIndex < dataGridView1.Rows[rowIndex].Cells.Count; cellIndex++)
            //    {
            //        if (String.IsNullOrEmpty(dataGridView1.Rows[rowIndex].Cells[cellIndex].Value.ToString()))
            //            dataGridView1.Rows[rowIndex].Cells[cellIndex].Value = "*";
            //    }
            //}
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (backgroundWorker1.IsBusy != true)
            {
                // Start the asynchronous operation.
                backgroundWorker1.RunWorkerAsync();
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            dataGridView1.Columns.Add("Name", "Name");
            dataGridView1.Columns.Add("Age", "Age");
            dataGridView1.Columns.Add("Id", "Id");
            ReadFromClipboard(dataGridView1);
        }

        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
            //ReadFromClipboard(dataGridView1);
            //for (int i = 0; i < 100; i++)
            //{
            //    Thread.Sleep(1000);
            //    backgroundWorker1.ReportProgress(i);
            //}
       
            string[] fields;
            int row = 0;
            int col = 0;

            foreach (string item in list)
            {
               dataGridView1.Invoke(new Action(delegate ()
               {
                   dataGridView1.Rows.Add();
               }));
                fields = item.Split('\t');
                foreach (string f in fields)
                {
                    dataGridView1[col, row].Value = f;
                    col++;
                }
                row++;
                col = 0;
                int percentage = (row) * 100 / list.Count;
                backgroundWorker1.ReportProgress(percentage);
            }
        }

        private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            progressBar1.Value = e.ProgressPercentage;
        }
    }

First : I am confuesd that where should I put my Reading From Clipboard Code?

It is hard to place your ReadFromClipboard method to use the progressbar. I split it into two parts. First part is get the list string from the Clipboard. And second part is used to add rows and add data to the row.

Second : Is there a way to simultaneously add a record to the datagridview when it is read from the clipboard.

It is impossible for you to them simultaneously because you get the data from the Clipboard just one time.

Best Regards,

Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

First thought, use task rather than a BackGroundWorker.

In the screenshot some data has foreign characters.

Backend code

public class Operations
{
    public delegate void OnTableCreated(DataTable dataTable);
    public static event OnTableCreated TableCreated;

    public delegate void OnRowAdded(DataRow row);
    public static event OnRowAdded RowAdded;

    public delegate void OnIssue(string message);
    public static event OnIssue Issue;

    public delegate void OnException(Exception exception);
    public static event OnException ExceptionEncountered;

    public delegate void OnDone();
    public static event OnDone Done;
    
    public static async Task Work(CancellationToken ct)
    {

        bool tableCreated = false;
        try
        {

            IDataObject clipboardData = Clipboard.GetDataObject();

            if (clipboardData != null)
            {

                if (clipboardData.GetDataPresent(DataFormats.CommaSeparatedValue))
                {

                    var clipboardStream = new StreamReader(((Stream)clipboardData.GetData(DataFormats.CommaSeparatedValue))!);

                    DataTable excelTable = new DataTable { TableName = "ExcelData" };
                    

                    while (clipboardStream.Peek() > 0)
                    {
                        
                        if (ct.IsCancellationRequested)
                        {
                            ct.ThrowIfCancellationRequested();
                        }

                        int loopCounter = 0;

                        var formattedData = await clipboardStream.ReadLineAsync(ct);
                        
                        Array singleRowData = formattedData!.Split(",".ToCharArray());

                        if (excelTable.Columns.Count <= 0)
                        {
                            for (loopCounter = 0; loopCounter <= singleRowData.GetUpperBound(0); loopCounter++)
                            {
                                excelTable.Columns.Add(singleRowData.GetValue(loopCounter)!.ToString());
                            }

                            continue;
                        }

                        if (!tableCreated)
                        {
                            TableCreated?.Invoke(excelTable);
                            tableCreated = true;
                        }
                        
                        var rowNew = excelTable.NewRow();

                        for (loopCounter = 0; loopCounter <= singleRowData.GetUpperBound(0); loopCounter++)
                        {
                            rowNew[loopCounter] = singleRowData.GetValue(loopCounter)!;
                        }
                        
                        excelTable.Rows.Add(rowNew);
                        RowAdded?.Invoke(rowNew);

                    }

                    clipboardStream.Close();
                    excelTable = null;

                }
                else
                {
                    Issue?.Invoke("Clipboard data does not seem to be copied from Excel!");
                }
            }
            else
            {
                Issue?.Invoke("Clipboard is empty!");
            }
        }
        catch (Exception exception)
        {
            ExceptionEncountered?.Invoke(exception);
        }

        Done?.Invoke();
    }


}

Form code

public partial class ExcelForm : Form
{
    private DataTable _dataTable;
    private CancellationTokenSource _cts = new CancellationTokenSource();

    public ExcelForm()
    {
        InitializeComponent();

        Operations.Issue += Operations_Issue;
        Operations.TableCreated += Operations_TableCreated;
        Operations.RowAdded += Operations_RowAdded;
        Operations.ExceptionEncountered += OperationsOnExceptionEncountered;
        Operations.Done += Operations_Done;
    }

    private void Operations_RowAdded(DataRow row)
    {
        _dataTable.ImportRow(row);
    }

    private void Operations_Done()
    {
        dataGridView1.ExpandColumns();
    }

    private void OperationsOnExceptionEncountered(Exception exception)
    {
        MessageBox.Show(exception.Message);
    }
    
    private void Operations_TableCreated(DataTable dataTable)
    {
        _dataTable = dataTable.Clone();
        dataGridView1.DataSource = _dataTable;
    }

    private void Operations_Issue(string message)
    {
        MessageBox.Show(message);
    }

    private async void ReadDataButton_Click(object sender, EventArgs e)
    {
        if (_cts.IsCancellationRequested == true)
        {
            _cts.Dispose();
            _cts = new CancellationTokenSource();
        }

        try
        {
            await Operations.Work(_cts.Token);
        }
        catch (OperationCanceledException)
        {
            MessageBox.Show("Canceled");
        }
    }

    private void CancelButton_Click(object sender, EventArgs e)
    {
        _cts.Cancel();
    }
}

Extension

internal static class DataGridViewExtensions
{
    public static void ExpandColumns(this DataGridView source, bool sizable = false)
    {
        foreach (DataGridViewColumn col in source.Columns)
        {
            if (col.ValueType.Name != "ICollection`1")
            {
                col.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            }
        }

        if (!sizable) return;

        for (int index = 0; index <= source.Columns.Count - 1; index++)
        {
            int columnWidth = source.Columns[index].Width;

            source.Columns[index].AutoSizeMode = DataGridViewAutoSizeColumnMode.None;

            // Set Width to calculated AutoSize value:
            source.Columns[index].Width = columnWidth;
        }


    }
}