中文输出至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#