none
导出到Excel的代码，有时导出不正确 RRS feed

 > 

  • 问题

  • Question
    登录进行投票
    0
    登录进行投票

            下面的代码是我用来导出sql 数据库内容到excel的代码。可是我遇到了一个奇怪的现象。前台我有一个DropDownList_BelongToCompany和Button_SQLToExcel，用来选择导出哪个公司的报表。总共有三个公司，其他两个都好，但我一旦选"九州分公司"导出，导出的文字都看不懂了。。。如图1所示。我搞不懂问题在哪里，导出代码都是一样的啊，难道是sql的问题？   
        代码1是后台导出到Excel的代码，代码2是数据库选择的代码，测试数据库链接在简介下方。
        这个问题好难，请大家帮我看看，3Q

    ---------------------代码1---------------------

    private void ExportExcel()
    {
        HttpContext curContext = System.Web.HttpContext.Current;
        System.IO.StringWriter strWriter = new StringWriter();
        System.Web.UI.HtmlTextWriter htmlWriter = new HtmlTextWriter(strWriter);
        curContext.Response.ContentType = "application/vnd.ms-excel";
        curContext.Response.ContentEncoding = Encoding.GetEncoding("GB2312");
        curContext.Response.Charset = "GB2312";
        GridView GV = new GridView();//一个无分页的GridView
        GV.DataSource = SqlDataSource_Search_Excel;
        GV.AllowPaging = false;
        GV.DataBind();
        GV.RenderControl(htmlWriter);
        curContext.Response.Write(strWriter.ToString());
        curContext.Response.End();
    }

    -----------------------代码2------------------------

    SELECT PRID,ROW_NUMBER ()OVER(ORDER BY receivablestate desc , PropertyCRAR asc), ManageProjectR,PropertyCRAR, OppositeSideR, LeaseAssureMoneyR09, YearRent,MonthRent,
ISNULL ( FormerOweRentTotal,'0')+ISNULL ( MonthRentR01 ,'0')-ISNULL (MonthRentPaid01 ,'0')+ISNULL (MonthRentR02,'0')-ISNULL ( MonthRentPaid02,'0')+ISNULL (MonthRentR03,'0')-ISNULL (MonthRentPaid03,'0')+ISNULL (MonthRentR04,'0')-ISNULL (MonthRentPaid04,'0')+ISNULL (MonthRentR05,'0')-ISNULL (MonthRentPaid05,'0')+ISNULL (MonthRentR06,'0')-ISNULL(MonthRentPaid06,'0')+ISNULL(MonthRentR07,'0')-ISNULL (MonthRentPaid07 ,'0')+ISNULL (MonthRentR08,'0')-ISNULL (MonthRentPaid08,'0'),
MonthRentR09,
ISNULL ( FormerOweRentTotal,'0')+ISNULL ( MonthRentR01 ,'0')-ISNULL (MonthRentPaid01 ,'0')+ISNULL (MonthRentR02,'0')-ISNULL ( MonthRentPaid02,'0')+ISNULL (MonthRentR03,'0')-ISNULL (MonthRentPaid03,'0')+ISNULL (MonthRentR04,'0')-ISNULL (MonthRentPaid04,'0')+ISNULL (MonthRentR05,'0')-ISNULL (MonthRentPaid05,'0')+ISNULL (MonthRentR06,'0')-ISNULL(MonthRentPaid06,'0')+ISNULL(MonthRentR07,'0')-ISNULL (MonthRentPaid07 ,'0')+ISNULL (MonthRentR08,'0')-ISNULL (MonthRentPaid08,'0')+ISNULL (MonthRentR09,'0'),
MonthRentPaid09, 
ISNULL ( FormerOweRentTotal,'0')+ISNULL ( MonthRentR01 ,'0')-ISNULL (MonthRentPaid01 ,'0')+ISNULL (MonthRentR02,'0')-ISNULL ( MonthRentPaid02,'0')+ISNULL (MonthRentR03,'0')-ISNULL (MonthRentPaid03,'0')+ISNULL (MonthRentR04,'0')-ISNULL (MonthRentPaid04,'0')+ISNULL (MonthRentR05,'0')-ISNULL (MonthRentPaid05,'0')+ISNULL (MonthRentR06,'0')-ISNULL(MonthRentPaid06,'0')+ISNULL(MonthRentR07,'0')-ISNULL (MonthRentPaid07 ,'0')+ISNULL (MonthRentR08,'0')-ISNULL (MonthRentPaid08,'0')+ISNULL (MonthRentR09,'0')-ISNULL ( MonthRentPaid09,'0'),
Income01, Income02, Income03, Income04, Income05, Income06, Income07, Income08, Income09, Income10, Income11, Income12, NextYearIncome ,ISNULL(Income01, '0') + ISNULL(Income02, '0') + ISNULL(Income03, '0') + ISNULL(Income04, '0') + ISNULL(Income05, '0') + ISNULL(Income06, '0') + ISNULL(Income07, '0') + ISNULL(Income08, '0') + ISNULL(Income09, '0') + ISNULL(Income10, '0') + ISNULL(Income11, '0') + ISNULL(Income12, '0')+ISNULL (NextYearIncome,'0')  ,
 
ISNULL(Income10, '0') + ISNULL(Income11, '0') + ISNULL(Income12, '0') +ISNULL ( NextYearIncome,'0'),
ContractRemarksR, ReceivableReMarks, MonthRentPaidDate12 
FROM ReceivableTable 
WHERE (ISNULL(@ReceivableID, N'') = '-1' OR ReceivableID = @ReceivableID) AND (ISNULL(@BelongToCompanyR, N'') = '未选择' OR BelongToCompanyR = @BelongToCompanyR) AND (ISNULL(@PropertyCityR, N'') = '未选择' OR PropertyCityR = @PropertyCityR) AND (ISNULL(@PropertyRoadR, N'') = '未选择' OR PropertyRoadR = @PropertyRoadR) AND (ISNULL(@PropertyAddressR, N'') = '未选择' OR PropertyAddressR = @PropertyAddressR) AND (ISNULL(@OppositeSideR, N'') = '-1' OR ISNULL(OppositeSideR, N'') LIKE '%' + @OppositeSideR + '%')   and (ISNULL(@ReceivableState, N'') = '-1' OR ReceivableState = @ReceivableState)   ORDER BY   ReceivableState desc, PropertyCRAR asc

    ----------------------------图1------------------------------

    https://skydrive.live.com/?cid=10ae47740a646d6e#cid=10AE47740A646D6E&id=10AE47740A646D6E%21111&v=3

    -------------------------------------------------------------------

    测试数据库

    https://skydrive.live.com/?cid=10AE47740A646D6E&id=10AE47740A646D6E%21105

    C# 菜鸟中的雏鸟！提的问题也许很幼稚，但我是认真的。希望看在党国的面子上拉兄弟一把！

    2013年11月5日 7:36
    |

答案

  • Question
    登录进行投票
    1
    登录进行投票

    你好，

       你能尝试一下下面这段代码,注意加粗部分的代码：

     private void ExportExcel()
        {
            Response.Buffer = true;
            Response.Clear();
            Response.ClearContent();
            Response.ClearHeaders();
            HttpContext curContext = System.Web.HttpContext.Current;
            System.IO.StringWriter strWriter = new StringWriter();
            System.Web.UI.HtmlTextWriter htmlWriter = new HtmlTextWriter(strWriter);
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = Encoding.GetEncoding("GB2312");
            curContext.Response.Charset = "GB2312";
            Response.Write("<meta http-equiv=Content-Type content=text/html;charset=GB2312>");
            GridView GV = new GridView();//一个无分页的GridView
            GV.DataSource = SqlDataSource_Search_Excel;
            GV.AllowPaging = false;
            GV.DataBind();
            GV.RenderControl(htmlWriter);
            curContext.Response.Write(strWriter.ToString());           
            curContext.Response.End();
        }
    希望可以帮你解决这个乱码问题。


    2013年11月6日 2:03
    |
    版主