1

I'm trying to replace the value in column A of an excel with the concatenated string from column A and B combined. I'm looping through the rows to do this but I always get that the string is not in the correct format.

Loop is below:

foreach (Row r in rows.Skip(1))
{
    string x = r.Elements<Cell>().ElementAt(0).InnerText;
    string y = r.Elements<Cell>().ElementAt(1).InnerText;

    string xy = x + '_' + y;

    r.Elements<Cell>().ElementAt(0).CellValue = new CellValue(xy);
}

If I replace the last line with either

r.Elements<Cell>().ElementAt(0).CellValue = new CellValue(y);
r.Elements<Cell>().ElementAt(0).CellValue = new CellValue(x);

It works as I'd expected however when joining the strings it doesn't.

I also tried the below:

foreach (Row r in rows.Skip(1))
{
    string x = r.Elements<Cell>().ElementAt(0).InnerText;
    string y = r.Elements<Cell>().ElementAt(1).InnerText;

    string xy = x + '_' + y;

    Cell cell = r.Elements<Cell>().ElementAt(0);
    cell.CellValue = new CellValue(xy);
    cell.DataType = new EnumValue<CellValues>(CellValues.String);
}

This runs but gives me numbers concatenated together rather than the strings I can see when opening the file.

Improve this question
2
  • The following may be of interest: stackoverflow.com/a/77816338/10024425 Commented Jan 20 at 16:55
  • I'm guessing the numbers are indexes into the SharedStringTable. The contents of the cell won't be the actual string but a reference to one. Have a look at stackoverflow.com/a/23104151 for a way to read from the SharedStringTable. Commented Mar 18 at 8:58
Related questions
2374
How do I get a consistent byte representation of strings in C# without manually specifying an encoding?
2191
How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?
2440
Should 'using' directives be inside or outside the namespace in C#?
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.