C# Export to Excel giving '', hexadecimal value 0x02, is an invalid character error

Question

giving '', hexadecimal value 0x02, is an invalid character error

foreach (DataRow dataRow in data.Rows)
    {
         row = new DocumentFormat.OpenXml.Spreadsheet.Row { RowIndex =+rowcount};
         for (int i = 0; i < fieldsToExpose.Length; i++)
            {
               row.Append(CreateTextCell(ColumnLetter(i), rowcount,dataRow[fieldsToExpose[i]].ToString()));
            }
             sheetData.AppendChild(row);
     }
 worksheetPart.Worksheet.Save();

Getting error when saving the worksheetPart.Worksheet.Save() because 'row' contains the hexadecimal value

Can you reduce your data that you are looping through so that you can track down what portion of it is actually causing the problem? Is it a particular cell, or is it endemic throughout the spreadsheet? — REW
– REW, Commented Dec 16, 2017 at 0:08

Jeremy Caney · Accepted Answer · 2021-09-24 18:22:16Z

0

There must be some invalid XML text. you can check it by using below statement:

System.Xml.XmlConvert.VerifyXmlChars(dataRow[fieldsToExpose[i]].ToString())

Use the below code:

row.Append(
  CreateTextCell(
    ColumnLetter(i), 
    rowcount, 
    Regex.Replace(
      dataRow[fieldsToExpose[i]].ToString(), 
      @"[\u0000-\u0008\u000A-\u001F\u0100-\uFFFF]", 
      ""
    )
  )
);

edited Sep 24, 2021 at 18:22

Jeremy Caney

7,808115 gold badges58 silver badges86 bronze badges

answered Sep 24, 2021 at 15:06

imad

1

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

C# Export to Excel giving '', hexadecimal value 0x02, is an invalid character error

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related