使用DocumentFormat.OpenXml 2.9.1生成Excel时遇无效字符错误求助

使用C#的DocumentFormat.OpenXml 2.9.1库从DataTable生成Excel时，遇到如下错误：

Error: '[1]', hexadecimal value 0x02, is an invalid character.

相关代码片段：

public TestMethod()
{
    using (var ms = new MemoryStream())
    {
        using (var workbook = SpreadsheetDocument.Create(ms, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
        {
            var workbookPart = workbook.AddWorkbookPart();
            workbook.WorkbookPart.Workbook = new Workbook();
            workbook.WorkbookPart.Workbook.Sheets = new Sheets();
            AddStyleSheet(workbook);
            uint sheetId = 1;
            foreach (DataTable table in ds.Tables)
            {
                var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                var sheetData = new SheetData();
                sheetPart.Worksheet = new Worksheet();
                Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<Sheets>();
                string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
                if (sheets.Elements<Sheet>().Count() > 0)
                {
                    sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }

                Sheet sheet = new Sheet()
                {Id = relationshipId, SheetId = sheetId, Name = reportNames?.FirstOrDefault() ?? "Report"};
                sheets.Append(sheet);
                Row headerRow = new Row();
                List<String> columns = new List<string>();
                foreach (DataColumn column in table.Columns)
                {
                    columns.Add(column.ColumnName);
                    Cell cell = new Cell();
                    cell.DataType = CellValues.String;
                    cell.StyleIndex = 1;
                    cell.CellValue = new CellValue(column.ColumnName);
                    headerRow.AppendChild(cell);
                }

                sheetData.AppendChild(headerRow);
                foreach (DataRow dsrow in table.Rows)
                {
                    Row newRow = new Row();
                    foreach (String col in columns)
                    {
                        Cell cell = new Cell{StyleIndex = 0};
                        cell.StyleIndex = 2;
                        cell.DataType = CellValues.String;
                        cell.CellValue = new CellValue(dsrow[col]?.ToString()); 
                        newRow.AppendChild(cell);
                    }

                    sheetData.AppendChild(newRow);
                }

                var columnSizeUpdate = ColumnSizeUpdate(sheetData, columnsWidthToUpdate);
                sheetPart.Worksheet.InsertAt(columnSizeUpdate, 0);
                sheetPart.Worksheet.AppendChild(sheetData);
            }

            workbook.WorkbookPart.Workbook.Save();
            workbook.Close();
            fileStream = ms.ToArray();
        }
    }
}

错误指向cell.CellValue = new CellValue(dsrow[col]?.ToString());一行，怀疑数据中存在无效字符，但无法准确定位和处理。

OpenXML格式基于XML 1.0规范，该规范严格禁止部分控制字符出现在文本内容中，比如0x00-0x08、0x0B-0x0C、0x0E-0x1F这类不可见控制字符。你遇到的0x02就是其中一个无效字符，当代码尝试将包含这类字符的内容写入Excel时，就会触发该错误。

1. 识别无效字符范围 #

XML允许的合法字符范围为：

• 制表符（\t，0x09）、换行（\n，0x0A）、回车（\r，0x0D）
• 0x20-0xD7FF区间字符
• 0xE000-0xFFFD区间字符
• 0x10000-0x10FFFF区间字符

2. 通用清理无效字符函数 #

编写工具函数自动移除字符串中的无效XML字符：

public static string CleanInvalidXmlChars(string input)
{
    if (string.IsNullOrEmpty(input))
        return input;

    var validChars = input.Where(c => 
        c == '\t' || c == '\n' || c == '\r' ||
        (c >= '\u0020' && c <= '\uD7FF') ||
        (c >= '\uE000' && c <= '\uFFFD') ||
        (c >= '\u10000' && c <= '\u10FFFF'));

    return new string(validChars.ToArray());
}

3. 修改现有代码 #

将原代码中获取数据的部分替换为清理后的字符串：

cell.CellValue = new CellValue(CleanInvalidXmlChars(dsrow[col]?.ToString()));

可选：标记无效字符（用于调试） #

如果需要定位具体哪些数据包含无效字符，可以修改函数，将无效字符替换为占位符方便排查：

public static string CleanInvalidXmlCharsWithMarker(string input)
{
    if (string.IsNullOrEmpty(input))
        return input;

    var cleaned = new StringBuilder();
    foreach (var c in input)
    {
        if (c == '\t' || c == '\n' || c == '\r' ||
            (c >= '\u0020' && c <= '\uD7FF') ||
            (c >= '\uE000' && c <= '\uFFFD') ||
            (c >= '\u10000' && c <= '\u10FFFF'))
        {
            cleaned.Append(c);
        }
        else
        {
            cleaned.Append($"[INVALID_CHAR_0x{(int)c:X2}]");
        }
    }
    return cleaned.ToString();
}

内容的提问来源于stack exchange，提问作者santosh kumar patro