Replace Column names in Excel worksheet using C#.NET

Aspose.Cells Product Family
,
1

Hi!
I’m currently creating a workbook from few CSV files…each csv file represents a worksheet.
Trying to replace the Column names and for this was using first the Aspose Cells built in method worksheet.Replace. Created 2 string arrays one with the old names and one with the new Names and called the replace method in an iteration:
//for (int i = 0; i < tabTitles.Length; i++)
{ wsheet.Replace(tabFields[i], tabTitles[i]); }
From around 25 of the names to be changed on the 1st worksheet, around 7 column names were wrong (by mixing the old name with new name values)
On the 2nd attempt I’ve used the System.String method Replace (from .Net) founded on Find and Replace in Spreadsheet|Documentation and this time got 2 column names wrong (less than with previous but still issue)
Below i attached a simple console application executable with the feature (ReplaceColumnNames) trying to be implemented. The workbook resulted can be found in the project: testBook.xlsx and the expected result (how it should be) is: MonRep.xlsx.
SampleProject.zip (95.2 KB)

2

@Remus87,

I did test your sample code a bit with your file and it is difficult to find out those culprit column names. Could you elaborate which two column names are wrong for your second attempt, you may also share some screenshots to highlight the issue?

For your first approach using Worksheet.Replace method, you may also highlight those 7 columns, so we could evaluate your issue precisely to figure it out soon.

3

Hi Amjad! Thank you for the prompt reply.
ColumnName issue_using Aspose Replace method.PNG (76.0 KB)
ColumnName issue_using .Net System String.Replace method.PNG (85.1 KB)
Above i attached 2 screenshots -> 1 using Aspose Replace method (7 mismatches), 1 using .Net Replace method (2 mismatches). On both screenshots i circled the columns where the name is wrong.

4

@Remus87,

Thanks for the screenshots.

I evaluated your sample code further and found your fields (as you defined array to store them in code) are not in order, so you are getting those two wrong entries (column names) while using Find feature to replace it with string.Replace method in the loop. Please update your relevant code segment with the following sample code, it will work fine as I tested:
e.g.
Sample code:

.....
FindOptions Opts = new FindOptions();
Opts.LookInType = LookInType.Values;
Opts.LookAtType = LookAtType.EntireContent;//you have to search the entire column on safer side as your defined arrays in code are not in order.
Cell cell = null;

for (int i = 0; i < tabTitles.Length; i++)
 {
      cell = sheet.Cells.Find(tabFields[i], cell, Opts);
      if (cell != null)
        {
            string celltext = cell.Value.ToString();
            celltext = celltext.Replace(tabFields[i], tabTitles[i]);
            cell.PutValue(celltext);
            cell = null;//you got to set it null, so everytime Aspose.Cells would search it from the very first cell at the start.
       }
 }
......

Hope, this helps a bit.

5

Thanks Amjad. It works perfect the solution provided.

6

@Remus87,

Good to know that your issue is sorted out by the suggested code. In the event of further queries or issue, feel free to write us back.