无外部库构建XLSX文件遇LibreOffice输入输出通用错误求助

问题背景 #

原本使用NPOI生成XLSX文件，但处理大量数据时速度极慢，改用CSV作为临时方案效果不佳。于是手动编写C#代码构建XLSX文件（利用XLSX是压缩XML集合的特性），但用LibreOffice Calc打开生成的文件时，出现“通用错误，输入/输出通用错误”提示。

错误原因 #

手动构建的XML存在多处格式问题，导致LibreOffice无法正常解析：

• 命名空间缺失：workbook.xml中使用了r:id属性，但未声明对应的r命名空间，违反XML语法规范。
• 未处理特殊字符：直接将属性名和数据值拼入XML，若内容包含<、>、&等特殊字符，会破坏XML结构。
• 多余双引号：拼接XML字符串时存在重复双引号（如""），导致XML格式不合法。

修复后的代码 #

using System;
using System.Collections.Generic;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Reflection;
using System.Text;
using Microsoft.AspNetCore.Mvc;

public static class XlsxExtensions
{
    public static FileResult DownloadXlsxFile(this ControllerBase controller, object data, string fileName = "file.xlsx")
    {
        if (data is IEnumerable<object> dataList)
        {
            var sheetXmlBuilder = new StringBuilder();
            // 初始化工作表XML，避免多余缩进和引号问题
            sheetXmlBuilder.Append(@"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>
<worksheet xmlns=""http://schemas.openxmlformats.org/spreadsheetml/2006/main"">
    <sheetData>");

            var props = dataList.First().GetType().GetProperties();
            // 写入表头行
            sheetXmlBuilder.Append("<row>");
            for (int i = 0; i < props.Length; i++)
            {
                var escapedHeader = System.Security.SecurityElement.Escape(props[i].Name);
                sheetXmlBuilder.Append($"<c r=\"{(char)('A' + i)}1\" t=\"inlineStr\"><is><t>{escapedHeader}</t></is></c>");
            }
            sheetXmlBuilder.Append("</row>");

            // 写入数据行
            int rowIndex = 2; // 表头为第1行，数据从第2行开始
            foreach (var item in dataList)
            {
                sheetXmlBuilder.Append("<row>");
                int colIndex = 0;
                foreach (var prop in props)
                {
                    var value = prop.GetValue(item, null)?.ToString() ?? string.Empty;
                    var escapedValue = System.Security.SecurityElement.Escape(value);
                    sheetXmlBuilder.Append($"<c r=\"{(char)('A' + colIndex)}{rowIndex}\" t=\"inlineStr\"><is><t>{escapedValue}</t></is></c>");
                    colIndex++;
                }
                sheetXmlBuilder.Append("</row>");
                rowIndex++;
            }

            sheetXmlBuilder.Append(@"
    </sheetData>
</worksheet>");

            using (var memoryStream = new MemoryStream())
            {
                using (var archive = new ZipArchive(memoryStream, ZipArchiveMode.Create, true))
                {
                    // 添加工作表XML
                    var sheetEntry = archive.CreateEntry("xl/worksheets/sheet1.xml");
                    using (var writer = new StreamWriter(sheetEntry.Open(), Encoding.UTF8))
                    {
                        writer.Write(sheetXmlBuilder.ToString());
                    }

                    // 添加工作簿关系XML
                    var workbookRelsEntry = archive.CreateEntry("xl/_rels/workbook.xml.rels");
                    using (var writer = new StreamWriter(workbookRelsEntry.Open(), Encoding.UTF8))
                    {
                        writer.Write(@"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>
<Relationships xmlns=""http://schemas.openxmlformats.org/package/2006/relationships"">
    <Relationship Id=""rId1"" Type=""http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"" Target=""worksheets/sheet1.xml""/>
</Relationships>");
                    }

                    // 添加工作簿XML，补充r命名空间声明
                    var workbookEntry = archive.CreateEntry("xl/workbook.xml");
                    using (var writer = new StreamWriter(workbookEntry.Open(), Encoding.UTF8))
                    {
                        writer.Write(@"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>
<workbook xmlns=""http://schemas.openxmlformats.org/spreadsheetml/2006/main"" xmlns:r=""http://schemas.openxmlformats.org/officeDocument/2006/relationships"">
    <sheets>
        <sheet name=""Sheet1"" sheetId=""1"" r:id=""rId1""/>
    </sheets>
</workbook>");
                    }

                    // 添加根关系XML
                    var rootRelsEntry = archive.CreateEntry("_rels/.rels");
                    using (var writer = new StreamWriter(rootRelsEntry.Open(), Encoding.UTF8))
                    {
                        writer.Write(@"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>
<Relationships xmlns=""http://schemas.openxmlformats.org/package/2006/relationships"">
    <Relationship Id=""rId1"" Type=""http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument"" Target=""xl/workbook.xml""/>
</Relationships>");
                    }

                    // 添加内容类型XML
                    var contentTypeEntry = archive.CreateEntry("[Content_Types].xml");
                    using (var writer = new StreamWriter(contentTypeEntry.Open(), Encoding.UTF8))
                    {
                        writer.Write(@"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>
<Types xmlns=""http://schemas.openxmlformats.org/package/2006/content-types"">
    <Default Extension=""rels"" ContentType=""application/vnd.openxmlformats-package.relationships+xml""/>
    <Default Extension=""xml"" ContentType=""application/xml""/>
    <Override PartName=""/xl/workbook.xml"" ContentType=""application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml""/>
    <Override PartName=""/xl/worksheets/sheet1.xml"" ContentType=""application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml""/>
</Types>");
                    }
                }

                memoryStream.Position = 0;
                return controller.File(memoryStream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
            }
        }

        throw new InvalidOperationException("数据无法转换为IEnumerable<T>类型");
    }
}

关键修复点 #

• 补充命名空间：在workbook.xml的<workbook>标签中添加xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"，解决r:id属性未声明命名空间的问题。
• XML转义处理：使用System.Security.SecurityElement.Escape()方法对所有文本内容进行转义，避免特殊字符破坏XML结构。
• 清理多余引号：移除XML字符串中重复的双引号，确保XML格式完全符合规范。
• 统一行号逻辑：调整数据行起始索引为2，与表头行的行号保持对应，保证单元格引用的一致性。

内容的提问来源于stack exchange，提问作者Nowlights