本文介绍了C#导出到Excel UTF-8问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正试图将som数据导出到 xls ,但似乎对UTF-8编码有问题.它可以很好地导出,并且我可以看到Excel文件,但是在插入的数据包含特殊字符的情况下,在这种情况下,丹麦字符为æøå,则文件已损坏.当我尝试在Excel中打开它时,我收到一条消息,指出在读取文件时出现问题,并在设置点播机中提及了该问题.

I'm tring to export som data to xls, but seem to have a problem with the UTF-8 encoding.It exports fine and I get the Excel file looking at it should, but when the inserted data has special characters, in this case, danish characters as æ, ø and å, then the file is somewhat currupted. When I try to open it in Excel, I get a message that a problem occured in reading the file, and mentions the problem as Setting of the spreadseet.

我在少数通过验证的在线验证服务中对结果XML进行了验证.当我在Notepad ++ XML验证程序中运行它时,我在第一个发现特殊字符(为Å)的错误中发现了一个错误,该字符表示:在第XX行出现 XML解析错误:输入不正确的UTF -8,指示编码!

I ran validation of the resulted XML in few online validation services which passed the validation. When I ran it in the Notepad++ XML validator, I got an error in the først found special char (which was Å) which says: XML Parsing error at line XX: Input is not proper UTF-8, Indicate encoding!

有人可以指出其含义吗?我应该在哪里指示编码?

Can someone point out what that meens?where should I indicate the encoding?

以下是生成的XML的相关部分:

Here are the relevant parts of the resulting 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,结果:

I now changed the export code to the following where I encode the string as UTF-8, getting no good results:

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建议),但仍然相同:

Changes again to this (suggested by @Luaan), but still the same:

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));

推荐答案

<?xml version="1.0"?>

应该是

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

代替-并确保XML实际上是UTF-8.设置Content-Encoding HTTP标头将无济于事.

instead - and make sure the XML is actually in UTF-8. Setting the Content-Encoding HTTP header will not help you.

编辑:您的更新未编码任何内容(实际上,您可能会以这种方式引入更多问题),您将其重新发送为字符串.

EDIT: Your update doesn't encode anything (in fact, you're likely to introduce even more problems that way), you're sending it as string again.

相反,您要编写byte[]:

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

这篇关于C#导出到Excel UTF-8问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-01 05:13