CODEWITHSUNDEEP
c#exceldatatable
irena
irena

Reputation: 81

How to copy data from one column to another at the same datatable in c#

I have a datatable filled with information from an excel file. I have more than four columns but to bring an example I'm writing just four of them. I have to write a program in which if the value of the cell in the column C is 0, then I have to copy column B to column A. If the value of the cell in column C is > 0 then i have to copy the column B to A and should add another row in which i have to copy the value of the column C to A. enter image description here

What i have till now is 

    for (int r = 2; r <= ws.UsedRange.Rows.Count; r++)
    {    if (ws.UsedRange.Cells[r, 3].Text == "0")
            {
                DataRow row = dt.NewRow();

                for (int c = 1; c < ws.UsedRange.Columns.Count; c++)
            {
                string cell = ws.Cells[r, c].Text;
                row[c - 1] = cell;     
          }
     }

So my questions are:

How can i copy a column to another in the same datatable? Copy B to A. How can i add another row and copy the value of C to A only for that row?

Here is the full code:

    public DataTable ReadExcel2(string file)
    {
        ExcelI.Application app = new ExcelI.Application(); //create an excel instance
        ExcelI.Workbook wb = app.Workbooks.Open(file, ReadOnly: true); //open a file
        ExcelI.Worksheet ws = wb.Worksheets[1]; //choose a sheet. The firt one

        var rng = ws.UsedRange; 
        //takes the index of the columns that are going to be filtered
        int service = ColumnIndexByName(ws.Cells[1, 1].EntireRow, "Service");
        int status = ColumnIndexByName(ws.Cells[1, 1].EntireRow, "Status");
        int code = ColumnIndexByName(ws.Cells[1, 1].EntireRow, "Code");
        DataTable dt = new DataTable();
        dt.Columns.Add("A", typeof(string));
        for (int c = 1; c < ws.UsedRange.Columns.Count; c++)
        {
            string colName = ws.Cells[1, c].Text;
            int i = 2;
            while (dt.Columns.Contains(colName))
            {
                colName = ws.Cells[1, c].Text + "{" + i.ToString() + "}";
                i++;
            }
            dt.Columns.Add(colName);
        }
        //do a loop to delete the rows that we dont need
        for (int r = 2; r <= ws.UsedRange.Rows.Count; r++)
        {
                if (ws.UsedRange.Cells[r, 3].Text == "0")
                {
                    DataRow row = dt.NewRow();

                    for (int c = 1; c < ws.UsedRange.Columns.Count; c++)
                    {
                        string cell = ws.Cells[r, c].Text;
                        row[c - 1] = cell;
                    }
                    dt.Rows.Add(row);
                    row["A"] = row["C"];
                }
        }            


//Close the file
        wb.Close();
        //release the excel objects from use
        Marshal.ReleaseComObject(wb);
        Marshal.ReleaseComObject(ws);
        //take the id of excel process
        int pid = app.PID();
        app.Quit();
        StartProc("taskkill", $"/f /pid {pid}");
        return dt;
    }

Upvotes: 0

Views: 1497

Answers (1)

Seyran
Seyran

Reputation: 711

To add row use dt.Rows.Add(row);, about "copy the column B to A" you mean copy value , just assign row[0] = row[2];, by the way , your example missing a bracket.

I think you should review your code according to conditions in your question, and you can do it yourself as well. Just pay attention to condition you wrote in question and conditional operator you checked in the code.

Upvotes: 1

Related Questions