Compare between datatable and excel file

[arif_samet_ipek]

Hi everyone,

I have an excel file which has dynamic columns with dynamic labels. Which looks like this

And also ı have datatable with dynamic values. The values from name column is label, other column is values.

So what ı have to do is read the datatable, take the name column ones (labels). Compare them with dynamic excel column names. If ıt matches, write the second column values from dt to excel.

For example in this case. I have “Renk” label in my dynamic dt and dynamic excel column. I have to write the “Açık Kırmızı” value to excel’s “Renk” column.

Waiting for your help, thanks.

[ashokkarale]

@arif_samet_ipek,

Try this approach:

1. Read the Source Datatable
2. Read Excel Data
3. Use For Each and If conditions like below:

For Each col As DataColumn In dtExcel.Columns
    If dtSource.Columns.Contains(col.ColumnName) Then
        idx = dtSource.Columns.IndexOf(col.ColumnName)
        For Each row As DataRow In dtSource.Rows
            value = row(idx).ToString()
            ' Assuming columns in Excel start at row 2
            cellAddress = col.ColumnName + (dtSource.Rows.IndexOf(row) + 2).ToString()
            ' Write value to Excel
            Write Cell
            Input: filepath, SheetName: "Destination", Cell: cellAddress, Value: value
        Next
    End If
Next

[arif_samet_ipek]

Hello,

Thank you so much, ı got an error like this;

dt_sablon123 is my excel dt, dt_attribute is the one with values. Am ı doing it right ?

Could you help me please ? @ashokkarale

[ashokkarale]

@arif_samet_ipek,

Change the DataColumn to currentDataColumn or something which is not a reserved keyword in VB. We cannot use datatype DataColumn as a variable.

[arif_samet_ipek]

@ashokkarale

currentDataColumn

thanks for the effort, unfortunately it didnt work.

[V_Roboto_V]

Hey @arif_samet_ipek

The error states that Columns is not a member of String. This means that dt_attribute is of Type String. Please change the data type of dt_attribute to DataTable.

[ashokkarale]

@arif_samet_ipek,

Check the datatype of dt_attribute. It should be DataTable

[arif_samet_ipek]

Thank you. it fixed the error says “not a member of string” but ı have the other one, do you have an idea ? Could you help me please ? @ashokkarale @V_Roboto_V

[V_Roboto_V]

Hi. can you try currentDataColumn.ColumnName.ToString

[ashokkarale]

@arif_samet_ipek,

Make sure your For Each activity Argument Type is System.Data.DataColumn