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

  • 问题

  •         下面的代码是我用来导出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

答案

  • 你好,

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

     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
    版主