如何将OpenXml.Sepsheet转换为带有C#的XML

Question

问题：我想用DocumentFormat.OpenXml.Sepsheet类(请参阅下面的示例代码(。我不是直接将内容写入文件，而是使用MemoryStream将数据保存在内存中。毕竟，数据在工作簿中，我打算"；串行化"；将其转换为平面XML(参见下面由excel生成的示例(。

问题：如何使用C#将MemoryStream中的工作簿转换为与Excel生成的相同的XML格式？我的解决方案方法是否正确，或者是否有更简单的解决方案？

我的意图是将XML字符串与WebAPI结合使用(作为GET回复(。

使用OpenXML:创建spreatsheet

using DocumentFormat.OpenXml.Spreadsheet;
private void CreateSpreadsheet()
{
using var mem = new MemoryStream();
var spreadsheetDocument = SpreadsheetDocument.Create(mem, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
var workbookPart = spreadsheetDocument.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook.
var sheets = spreadsheetDocument.WorkbookPart!.Workbook.AppendChild<Sheets>(new Sheets());

// Add some content
var worksheet = new Worksheet();
var sheetData = new SheetData();
var row = new Row();
var cell = new Cell()
{
CellReference = "A1",
DataType = CellValues.String,
CellValue = new CellValue("Microsoft")
};
row.Append(cell);
sheetData.Append(row);
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
// Append a new worksheet and associate it with the workbook.
var sheet1 = new Sheet()
{ Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet1" };
sheets.Append(sheet1);

workbookPart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();

// --> How to convert the MemoryStream to the desired XML-format?
}

Excel生成的示例XML:

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>Autor</Author>
<LastAuthor>Autor</LastAuthor>
<Created>2022-03-10T08:37:48Z</Created>
<LastSaved>2022-03-10T08:40:32Z</LastSaved>
<Company>Organization</Company>
<Version>16.00</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<AllowPNG/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>16980</WindowHeight>
<WindowWidth>-13936</WindowWidth>
<WindowTopX>0</WindowTopX>
<WindowTopY>0</WindowTopY>
<ActiveSheet>1</ActiveSheet>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Arial" x:Family="Swiss" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s62">
<NumberFormat ss:Format="0%"/>
</Style>
</Styles>
<Worksheet ss:Name="DataSet1">
<Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="5" x:FullColumns="1"
x:FullRows="1">
<Row>
<Cell ss:Index="4"><Data ss:Type="String">VCD3</Data></Cell>
<Cell><Data ss:Type="String">XCD6</Data></Cell>
<Cell><Data ss:Type="String">AFG</Data></Cell>
</Row>
<Row>
<Cell ss:Index="4"><Data ss:Type="Number">109.78827487289099</Data></Cell>
<Cell><Data ss:Type="Number">123.00725769310399</Data></Cell>
<Cell><Data ss:Type="Number">115.917908055864</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">ABC1</Data></Cell>
<Cell ss:Formula="=SUM(R2C4:R2C42)-SUM(RC[2]:RC[40])"><Data ss:Type="Number">1.4542479670879516</Data></Cell>
<Cell ss:StyleID="s62"><Data ss:Type="Number">1</Data></Cell>
<Cell><Data ss:Type="Number">109.555181207937</Data></Cell>
<Cell><Data ss:Type="Number">122.74918217899599</Data></Cell>
<Cell><Data ss:Type="Number">114.95482926783799</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">CBA2</Data></Cell>
<Cell ss:Formula="=SUM(R2C4:R2C42)-SUM(RC[2]:RC[40])"><Data ss:Type="Number">8.1808815475339429</Data></Cell>
<Cell ss:StyleID="s62"><Data ss:Type="Number">1</Data></Cell>
<Cell><Data ss:Type="Number">106.139988953288</Data></Cell>
<Cell><Data ss:Type="Number">118.97265998247801</Data></Cell>
<Cell><Data ss:Type="Number">115.419910138559</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">DEA3</Data></Cell>
<Cell ss:Formula="=SUM(R2C4:R2C42)-SUM(RC[2]:RC[40])"><Data ss:Type="Number">3.4102360956978828E-2</Data></Cell>
<Cell ss:StyleID="s62"><Data ss:Type="Number">1</Data></Cell>
<Cell><Data ss:Type="Number">109.780888636361</Data></Cell>
<Cell><Data ss:Type="Number">122.999088939155</Data></Cell>
<Cell><Data ss:Type="Number">115.899360685386</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>56</ActiveRow>
<ActiveCol>6</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="DataSet2">
<Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="5" x:FullColumns="1"
x:FullRows="1">
<Row>
<Cell ss:Index="4"><Data ss:Type="String">VCD3</Data></Cell>
<Cell><Data ss:Type="String">XCD6</Data></Cell>
<Cell><Data ss:Type="String">AFG</Data></Cell>
</Row>
<Row>
<Cell ss:Index="4"><Data ss:Type="Number">109.78827487289099</Data></Cell>
<Cell><Data ss:Type="Number">123.00725769310399</Data></Cell>
<Cell><Data ss:Type="Number">115.917908055864</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">ABC1</Data></Cell>
<Cell ss:Formula="=SUM(R2C4:R2C42)-SUM(RC[2]:RC[40])"><Data ss:Type="Number">1.4542479670879516</Data></Cell>
<Cell ss:StyleID="s62"><Data ss:Type="Number">1</Data></Cell>
<Cell><Data ss:Type="Number">109.555181207937</Data></Cell>
<Cell><Data ss:Type="Number">122.74918217899599</Data></Cell>
<Cell><Data ss:Type="Number">114.95482926783799</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">CBA2</Data></Cell>
<Cell ss:Formula="=SUM(R2C4:R2C42)-SUM(RC[2]:RC[40])"><Data ss:Type="Number">8.1808815475339429</Data></Cell>
<Cell ss:StyleID="s62"><Data ss:Type="Number">1</Data></Cell>
<Cell><Data ss:Type="Number">106.139988953288</Data></Cell>
<Cell><Data ss:Type="Number">118.97265998247801</Data></Cell>
<Cell><Data ss:Type="Number">115.419910138559</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">DEA3</Data></Cell>
<Cell ss:Formula="=SUM(R2C4:R2C42)-SUM(RC[2]:RC[40])"><Data ss:Type="Number">3.4102360956978828E-2</Data></Cell>
<Cell ss:StyleID="s62"><Data ss:Type="Number">1</Data></Cell>
<Cell><Data ss:Type="Number">109.780888636361</Data></Cell>
<Cell><Data ss:Type="Number">122.999088939155</Data></Cell>
<Cell><Data ss:Type="Number">115.899360685386</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>31</ActiveRow>
<ActiveCol>11</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>

Answer 1

XLSX文件是一个包含多个xml文件的zip归档文件(只需将excelfile重命名为.zip并打开它即可查看结构(。因此，您可以使用System.IO.Compression.ZipArchive打开MemoryStream并提取您感兴趣的xml文件。但是，由于内容分布在多个文件中(例如，每个工作表有一个文件(，您必须自己将这些文件合并到所需的xml输出结构中。