C#导出到Excel UTF-8问题

本文关键字：UTF-8 问题 Excel | 更新日期: 2023-09-27 18:05:02

我正在字符串将som数据导出到xls，但UTF-8编码似乎有问题。它导出得很好，我让Excel文件查看它应该是什么样子，但当插入的数据有特殊字符时，在这种情况下，丹麦字符为æ、øpreadsheet的设置。

我在少数通过验证的在线验证服务中运行了结果XML的验证。当我在Notepad++XML验证器中运行它时，我在først find特殊字符(Å(中得到了一个错误，它说：第XX行的XML解析错误：输入不是正确的UTF-8，指示编码

有人能指出那是什么吗？我应该在哪里指示编码？

以下是生成的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">
  <Created>2006-09-25T13:34:53Z</Created>
  <LastSaved>2014-05-20T18:04:40Z</LastSaved>
  <Version>12.00</Version>
 </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <RemovePersonalInformation/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>7875</WindowHeight>
  <WindowWidth>14895</WindowWidth>
  <WindowTopX>360</WindowTopX>
  <WindowTopY>300</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
 ...
 </Styles>
  <Worksheet ss:Name="Ark1">
  <Names>
   <NamedRange ss:Name="GIS34_Skade_V1_alle_koder" ss:RefersTo="=Ark1!R5C2:R28C2"/>
  </Names>
  <Table ss:ExpandedColumnCount="16" ss:ExpandedRowCount="34" x:FullColumns="1" x:FullRows="1" ss:StyleID="s16" ss:DefaultRowHeight="15">
   <Column ss:StyleID="s16" ss:AutoFitWidth="0" ss:Width="30"/>
   <Column ss:StyleID="s16" ss:AutoFitWidth="0" ss:Width="135" ss:Span="2"/>
   <Column ss:Index="5" ss:StyleID="s19" ss:AutoFitWidth="0" ss:Width="30"/>
   <Column ss:StyleID="s16" ss:AutoFitWidth="0" ss:Width="135"/>
   <Column ss:StyleID="s19" ss:AutoFitWidth="0" ss:Width="30"/>
   <Column ss:StyleID="s16" ss:AutoFitWidth="0" ss:Width="135"/>
   <Column ss:StyleID="s19" ss:AutoFitWidth="0" ss:Span="3"/>
   <Row ss:AutoFitHeight="0" ss:Height="30" ss:StyleID="s18">
    <Cell ss:StyleID="s33"/>
    <Cell ss:StyleID="s126"><Data ss:Type="String">LE34</Data></Cell>
    <Cell ss:StyleID="s87"><Data ss:Type="String">Kunde:</Data></Cell>
    <Cell ss:StyleID="s32"><Data ss:Type="String">Assens Kommune</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0" ss:Height="30" ss:StyleID="s18">
    <Cell ss:StyleID="s33"/>
    <Cell ss:StyleID="s128"><Data ss:Type="String">GIS34</Data></Cell>
    <Cell ss:StyleID="s87"><Data ss:Type="String">Kodeliste:</Data></Cell>
    <Cell ss:StyleID="s32"><Data ss:Type="String">Arkil Århus V2</Data></Cell> <!-- Error occures in this line first because of the **Å** char -->
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell ss:StyleID="s33"/>
    <Cell ss:MergeAcross="6" ss:StyleID="s37"><Data ss:Type="String">Udformning af lagkontrol: </Data></Cell>
    <Cell ss:StyleID="s16"/>
    <Cell ss:StyleID="s16"/>
    <Cell ss:StyleID="s16"/>
    <Cell ss:StyleID="s16"/>
   </Row>
   ...
   <Row ss:AutoFitHeight="0">
    <Cell ss:Index="2" ss:StyleID="s29"><NamedCell ss:Name="GIS34_Skade_V1_alle_koder"/></Cell>
    <Cell ss:StyleID="s30"><Data ss:Type="String">Billede 3 (filename)</Data></Cell>
    <Cell ss:StyleID="s30"/>
    <Cell ss:StyleID="s16"/>
    <Cell ss:StyleID="s20"/>
    <Cell ss:StyleID="s16"/>
    <Cell ss:StyleID="s20"/>
    <Cell ss:StyleID="s16"/>
    <Cell ss:StyleID="s16"/>
    <Cell ss:StyleID="s16"/>
    <Cell ss:StyleID="s16"/>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.31496062992125984"/>
    <Footer x:Margin="0.31496062992125984"/>
    <PageMargins x:Bottom="0.74803149606299213" x:Left="0.70866141732283472" x:Right="0.70866141732283472" x:Top="0.74803149606299213"/>
   </PageSetup>
   <Print>
    <ValidPrinterInfo/>
    <PaperSizeIndex>9</PaperSizeIndex>
    <HorizontalResolution>600</HorizontalResolution>
    <VerticalResolution>600</VerticalResolution>
    <Gridlines/>
   </Print>
   <Selected/>
   <FreezePanes/>
   <FrozenNoSplit/>
   <SplitHorizontal>7</SplitHorizontal>
   <TopRowBottomPane>7</TopRowBottomPane>
   <ActivePane>2</ActivePane>
   <Panes>
    <Pane>
     <Number>3</Number>
    </Pane>
    <Pane>
     <Number>2</Number>
     <ActiveRow>26</ActiveRow>
     <ActiveCol>13</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
  <QueryTable xmlns="urn:schemas-microsoft-com:office:excel">
   <Name>GIS34 Skade V1_alle koder</Name>
   <AutoFormatFont/>
   <AutoFormatPattern/>
   <QuerySource>
    <QueryType>Text</QueryType>
    <TextWizardSettings>
     <Name x:HRef="C:'Temp'dummy.txt"/>
     <Decimal>,</Decimal>
     <ThousandSeparator>.</ThousandSeparator>
     <TrailingMinusNumbers/>
     <FormatSettings>
      <FieldType>AutoFormat</FieldType>
     </FormatSettings>
     <Delimiters>
      <Tab/>
     </Delimiters>
    </TextWizardSettings>
    <VersionLastEdit>3</VersionLastEdit>
    <VersionLastRefresh>3</VersionLastRefresh>
   </QuerySource>
  </QueryTable>
 </Worksheet>
</Workbook>

以下是我的导出方式：

context.Response.ContentType = "application/excel";
context.Response.AddHeader("Content-disposition", "attachment; filename='"report.xls'"");
context.Response.ContentEncoding = new System.Text.UTF8Encoding();
context.Response.Write(excel);

更新1:

现在，我将导出代码更改为以下代码，将字符串编码为UTF-8，得到no良好结果：

string excel = header.ToString() + styles.ToString() + title.ToString() + content.ToString() + footer.ToString();
context.Response.ContentType = "application/excel";
context.Response.AddHeader("Content-disposition", "attachment; filename='"report.xls'"");
//context.Response.ContentEncoding = new System.Text.UTF8Encoding();
byte[] bytes = Encoding.Default.GetBytes(excel);
context.Response.Write(Encoding.UTF8.GetString(bytes));

更新2:

再次对此进行更改(由@Luaan建议(，但仍然相同：

string excel = header.ToString() + styles.ToString() + title.ToString() + content.ToString() + footer.ToString();
context.Response.ContentType = "application/excel";
context.Response.AddHeader("Content-disposition", "attachment; filename='"report.xls'"");
HttpContext.Current.Response.BinaryWrite(UTF8Encoding.UTF8.GetBytes(excel));

C#导出到Excel UTF-8问题

<?xml version="1.0"?>

应该是

<?xml version="1.0" encoding="utf-8"?>

相反，请确保XML实际上是UTF-8格式的。设置Content-Encoding HTTP标头对您没有帮助。

编辑：您的更新没有编码任何内容(事实上，这样可能会带来更多问题(，您将再次以字符串形式发送它。

相反，您想要编写byte[]:

HttpContext.Current.Response.BinaryWrite(UTF8Encoding.UTF8.GetBytes(excel));

解决这个问题的简单方法是在HttpContext.Current.Response中设置字符集，如下所示：

            HttpContext.Current..Response.Charset = "UTF-8";

代码的最完整部分：

            curContext.Response.Clear();
            curContext.Response.Buffer = true;
            curContext.Response.Charset = "UTF-8";
            curContext.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            curContext.Response.AddHeader("content-disposition", "attachment;filename=" + FileName + ".xlsx");

尝试使用一些可以正确执行的库，这样可以节省大量时间，例如

  • http://closedxml.codeplex.com/
  • http://freenetexcel.codeplex.com/
  • http://epplus.codeplex.com/
  • http://npoi.codeplex.com/
相关文章：