How to change data table column names dynamically using interop excel in C#

Question

0.00/5 (No votes)

See more:

https://i.stack.imgur.com/7RBox.jpg[^]

Above image shows what i want, i wish to remove prefix part all columns names like ,WK1-600,WK1-700,WK1-800....WK2-600,WK2-700,WK2-800....WK3-600,WK3-700,WK4-800....WK4-600,WK4-700,WK4-800 etc then remove 600, 700, 800 alternately.

I am trying below code but i dont know how to fix it correclty in interop excel... i am very jr in coding please help me....big thanks in advance

What I have tried:

if i wish to remove all numbers in column names, which placing after ('-'), then what will i do?...anyone please help

C#

foreach (DataColumn column in ds.Tables[0].Columns)
  {
      string colname = column.ColumnName;
      if (colname == "Retailer" || colname == "Brand") ;
      else
      {
          ds.Tables[0].Columns[colname].ColumnName = "WK" + colname.split('-')[0];
      }
  }

Posted 25-Jun-19 9:38am

Abu Aamir Ahmad

Updated 26-Jun-19 10:16am

Patrice T

v3

Add a Solution

Comments

BillWoodruff 25-Jun-19 21:17pm

You really want all the column names to be "WK" ? That's what your description, and code, imply.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2019-06-26T10:16:00

Not sure what you want to achieve, because the headers of columns haven't changed.
Assuming that you want to add rows based on column names, you can use something like that:

C#

DataTable dt = ds.Tables[0]; 
dt.Columns.AddRange(names.Select(x=>new DataColumn(x)).ToArray());

var row2 =  dt.Columns.Cast<DataColumn>()
		.Select(c=>c.ColumnName.Split('-')[0])
		.ToArray();
var row1 =  dt.Columns.Cast<DataColumn>()
		.Select(c=>c.ColumnName.Split('-')[1])
		.ToArray();
dt.Rows.Add(row1);		
dt.Rows.Add(row2);

Result:

WK1-600 WK1-700 WK1-800 WK2-600 WK2-700 WK2-800 WK3-600 WK3-700 WK3-800 WK4-600 WK4-700 WK4-800
600     700     800     600     700     800     600     700     800     600     700     800 
WK1     WK1     WK1     WK2     WK2     WK2     WK3     WK3     WK3     WK4     WK4     WK4

Gerry Schmitz · Answer 2 · 2019-06-25T13:14:00

Solution 1

Just remove the "old" row and insert a new one with the values you want.

How to add new row to excel file in C# - Stack Overflow[^]

Posted 25-Jun-19 13:14pm

Gerry Schmitz

Comments

BillWoodruff 25-Jun-19 21:54pm

My vote of #1: the question is about changing Column headers, has nothing to do with Rows.