当前位置:编程学习 > C#/ASP.NET >>

中文输出至Excel乱码问题

从数据服务器倒资料入Excel,支持多数据库,但目前碰到这样一个情况

数据库A,排序规则为Latin1_General_BIN,倒出该数据库的是资料时,中文内容为乱码,
当数据库B,排序规则为Chinese_PRC_CI_AS,倒出该数据库的是资料时,中文内容为却正常,
目前,此两数据库均使用多年,所以没有可能去对服务器做任何的变更,所以唯一的寄望就是客户端如何写:

在此叩谢各位的帮忙!

以下是我的代码:
string sSQL="";
string sWhere="";
try
{
    string CONN_STRING1="";
    sWhere=Request["sSelectFormula"].Replace("|","'");
    sWhere=sWhere.Replace("~","%");
    string sRptID=Request["Rpt_ID"];

    string sSqlDb=ReportBuild.GetSQLByDatabase(sRptID).Trim().ToString();
    if (sSqlDb=="NRHWEB")
    { CONN_STRING1=Components.Database.CONN_STRING1;
    }

    if (sSqlDb=="FSDBST")
    {
        CONN_STRING1=Components.Database.CONN_STRING2;
    }

    sSQL=ReportBuild.GetSQLByRptID(sRptID).Trim().ToString();
    sSQL=sSQL.Replace("|","'");
    if (sWhere!="")
    {
        sSQL=sSQL+" Where "+sWhere;
    }

    DataSet ds=new DataSet();    
    SqlConnection sqlCn=new SqlConnection(CONN_STRING1);
    SqlDataAdapter sqlAdapt=new SqlDataAdapter(sSQL,sqlCn);
    sqlAdapt.Fill(ds,sRptID);
    int rowscount = ds.Tables[sRptID].Rows.Count; 
    int colscount = ds.Tables[sRptID].Columns.Count;
    System.IO.StringWriter sw=new System.IO.StringWriter(); 
    string sHeader="";
    for (int c=0;c<colscount;c++)
    {
    if (sHeader=="")
    {
    sHeader=sHeader+ds.Tables[sRptID].Columns[c].ColumnName.ToString().Trim();
    }
    else
    {
    sHeader=sHeader+"\t"+ds.Tables[sRptID].Columns[c].ColumnName.ToString().Trim();
    }
    }
    sw.WriteLine(sHeader.ToString()); 
    for (int i=0;i<rowscount;i++)
    {
        string sDetail="";
        for (int c=0;c<colscount;c++)
        {
            if (sDetail=="")
            {
                sDetail=sDetail+ds.Tables[sRptID].Rows[i][c].ToString().Trim();
            }
            else
            {
                sDetail=sDetail+"\t"+ds.Tables[sRptID].Rows[i][c].ToString().Trim();
             }
            }
    sw.WriteLine(sDetail.ToString());
    }
    sw.Close();  
    Response.AddHeader("Content-Disposition", "attachment; filename="+System.Web.HttpUtility.UrlEncode(sRptID,System.Text.Encoding.UTF8)+".xls"); 
    Response.ContentType = "application/ms-excel"; 
    Response.ContentEncoding=System.Text.Encoding.GetEncoding("gb2312"); 
    Response.Write(sw); 
    Response.End();        
    }
    catch
    {
    this.footer1.sErrorMsg="数据导出时发生错误,请检查条件框的内容格式是否正确 !";
    return ;
    }

--------------------编程问答-------------------- need your help --------------------编程问答-------------------- Response.ContentEncoding=System.Text.Encoding.UTF7; 
--------------------编程问答-------------------- Response.Clear();
        Response.AddHeader("content-disposition", "attachment;filename=BudgetVersionCompare.xls");
        Response.Charset = "gb2312";
        Response.ContentType = "application/vnd.xls";
        System.IO.StringWriter stringWrite = new System.IO.StringWriter();

        System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);    
        Response.Write(stringWrite.ToString());
        Response.End();
--------------------编程问答-------------------- Response.ContentEncoding=System.Text.Encoding.UTF7;  

这样子是也是乱码!保存在数据库是简体中文 --------------------编程问答-------------------- Response.Charset = "gb2312";

这个跟Response.ContentEncoding=System.Text.Encoding.GetEncoding("gb2312"); 效果一样
--------------------编程问答-------------------- up,我也遇到这情况了,导出时,有的表正常,有的表却乱码,啥回事呢?
下面是偶的代码:
Response.Clear();
                Response.Buffer = true;
                Response.Charset = "GB2312"; //设置了类型为中文防止乱码的出现 
                Response.AppendHeader("Content-Disposition", "attachment;filename=" + TextBox1.Text + ".xls"); //定义输出文件和文件名
                Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
                Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 
                this.EnableViewState = false;
                System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true);
                System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
                System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
                this.DataGrid1.RenderControl(oHtmlTextWriter);
                Response.Write(oStringWriter.ToString()); --------------------编程问答-------------------- DataSet ds = (DataSet )Session["MyDataSet"];
StringWriter sw = new StringWriter();
sw.WriteLine("编号\t评价项目");
foreach (DataRow dr in ds.Tables[0].Rows)
{
sw.WriteLine(dr["idxNum"].ToString() + "\t" + dr["idxItem"].ToString()
}
sw.Close();
Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + ".xls");
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.Write(sw);
Response.End(); --------------------编程问答-------------------- jf --------------------编程问答-------------------- 真不知道说是Response.ContentEncoding=System.Text.Encoding.UTF7;自己又没试过,总是喜欢误导别人。 --------------------编程问答-------------------- Response.Write("<html><head><meta http-equiv=Content-Type content=\"text/html; charset=utf-8\">");
Response.Write(sw); 
Response.Write("</body></html>");
Response.End();
应该这样改,乱码问题便能解决,希望对楼主有所帮助。 --------------------编程问答-------------------- Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
             --------------------编程问答-------------------- string _xmlStr = @"<?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>aa</Author>
  <LastAuthor>aa</LastAuthor>
  <Created>2007-12-07T06:54:38Z</Created>
  <Company>WZKJ</Company>
  <Version>11.5606</Version>
  </DocumentProperties>
  <ExcelWorkbook xmlns='urn:schemas-microsoft-com:office:excel'>
  <WindowHeight>5970</WindowHeight>
  <WindowWidth>7395</WindowWidth>
  <WindowTopX>480</WindowTopX>
  <WindowTopY>60</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
  </ExcelWorkbook>
  <Styles>
  <Style ss:ID='Default' ss:Name='Normal'>
  <Alignment ss:Vertical='Center'/>
  <Borders/>
  <Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/>
  <Interior/>
  <NumberFormat/>
  <Protection/>
  </Style>
  <Style ss:ID='s21'>
  <Alignment ss:Vertical='Center' ss:WrapText='1'/>
  </Style>
  </Styles>
  <Worksheet ss:Name='Sheet1'>
  $Table$
  <WorksheetOptions xmlns='urn:schemas-microsoft-com:office:excel'>
  <Selected/>
  <Panes>
  <Pane>
  <Number>3</Number>
  <ActiveRow>2</ActiveRow>
  <ActiveCol>3</ActiveCol>
  </Pane>
  </Panes>
  <ProtectObjects>False</ProtectObjects>
  <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
  </Worksheet>
    
  </Workbook>";
    
  string sqlStr = String.Format("select * from table where ID in({0})",IDStr);

  int recordCount = 0;//记录数
  string outputStr = "";//保存要输出的数据部分
  using (SqlDataReader dr = conn.GetDataReader(sqlStr))
  {
  StringBuilder sb = new StringBuilder();
  sb.Append("<Column ss:Index='1' ss:AutoFitWidth='0' ss:Width='33.75'/>");
  sb.Append("<Column ss:Index='2' ss:AutoFitWidth='0' ss:Width='93.75'/>");
  sb.Append("<Column ss:Index='3' ss:AutoFitWidth='0' ss:Width='153.75'/>");
  sb.Append("<Column ss:Index='4' ss:AutoFitWidth='0' ss:Width='273.75'/>");
  sb.Append("<Column ss:Index='5' ss:AutoFitWidth='0' ss:Width='63.75'/>");
  sb.Append("<Column ss:Index='6' ss:AutoFitWidth='0' ss:Width='93.75'/>");
  sb.Append("<Column ss:Index='7' ss:AutoFitWidth='0' ss:Width='93.75'/>");
  sb.Append("<Column ss:Index='8' ss:AutoFitWidth='0' ss:Width='63.75'/>");
  sb.Append("<Column ss:Index='9' ss:AutoFitWidth='0' ss:Width='153.75'/>");
  sb.Append("<Column ss:Index='10' ss:AutoFitWidth='0' ss:Width='153.75'/>");
  sb.Append("<Column ss:Index='11' ss:AutoFitWidth='0' ss:Width='153.75'/>");
  sb.Append("<Row>");
  sb.Append("<Cell><Data ss:Type='String'>序号</Data></Cell>");
  sb.Append("<Cell><Data ss:Type='String'>列名</Data></Cell>");
  sb.Append("<Cell><Data ss:Type='String'>列名</Data></Cell>");
  sb.Append("<Cell><Data ss:Type='String'>列名</Data></Cell>");
  sb.Append("<Cell><Data ss:Type='String'>列名导</Data></Cell>");
  sb.Append("<Cell><Data ss:Type='String'>列名</Data></Cell>");
  sb.Append("<Cell><Data ss:Type='String'>列名</Data></Cell>");
  sb.Append("<Cell><Data ss:Type='String'>列名</Data></Cell>");
  sb.Append("<Cell><Data ss:Type='String'>列名</Data></Cell>");
  sb.Append("<Cell><Data ss:Type='String'>列名</Data></Cell>");
  sb.Append("<Cell><Data ss:Type='String'>列名</Data></Cell>");
  sb.Append("</Row>");
  while (dr.Read())
  {
  recordCount = recordCount + 1;
  sb.Append("<Row ss:StyleID='s21'>");
  sb.Append("<Cell><Data ss:Type='String'>" + recordCount.ToString() + "</Data></Cell>");
  sb.Append("<Cell><Data ss:Type='String'>" + dr["字段"].ToString() + "</Data></Cell>");
  sb.Append("<Cell><Data ss:Type='String'>" + dr["字段"].ToString() + "</Data></Cell>");
  sb.Append("<Cell><Data ss:Type='String'>" + dr["字段"].ToString() + "</Data></Cell>");
  sb.Append("<Cell><Data ss:Type='String'>" + dr["字段"].ToString() + "</Data></Cell>");
  sb.Append("<Cell><Data ss:Type='String'>" + dr["字段"].ToString() + "</Data></Cell>");
  sb.Append("<Cell><Data ss:Type='String'>" + dr["字段"].ToString() + "</Data></Cell>");
  sb.Append("<Cell><Data ss:Type='String'>" + dr["字段"].ToString() + "</Data></Cell>");
  sb.Append("<Cell><Data ss:Type='String'>" + dr["字段"].ToString() + "</Data></Cell>");
  sb.Append("<Cell><Data ss:Type='String'>" + dr["字段"].ToString() + "</Data></Cell>");
  sb.Append("<Cell><Data ss:Type='String'>" + dr["字段"].ToString() + "</Data></Cell>");
  sb.Append("</Row>");
  }
  outputStr = sb.ToString();
  }

  string topString = "<Table ss:ExpandedColumnCount='11' ss:ExpandedRowCount='" + Convert.ToString(recordCount + 1) + "' x:FullColumns='1' x:FullRows='1' ss:DefaultColumnWidth='54'>";
  string bottomStr = "</Table>";
  string ExcelXmlStr = _xmlStr.Replace("$Table$", topString + outputStr + bottomStr);

  Response.Clear();
  Response.Buffer = true;
  Response.AddHeader("Content-Disposition", "attachment;filename=Work_Report.xls");
  Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
  Response.ContentType = "application/ms-excel";
  Response.Output.Write(ExcelXmlStr);
  Response.Flush();
  Response.End();

看看这个吧,对你应该有帮助的,用Excel2007和Excel2003打开都没问题,主要是要对Excel的XML熟悉一下就可以了 --------------------编程问答-------------------- 之前我也出现过乱码,只记得改下编码就ok了 国标2312 你试试吧
补充:.NET技术 ,  C#
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,