导出Excel设置 格式

/// <summary> 
    
/// 将DataTable数据导出到EXCEL,调用该方法后自动返回可下载的文件流 
    
/// </summary> 
    
/// <param name="dtData">要导出的数据源</param> 
    public static void DataTable1Excel(System.Data.DataTable dtData)
     {
         System.Web.UI.WebControls.GridView gvExport = null;
         // 当前对话 
        System.Web.HttpContext curContext = System.Web.HttpContext.Current;
         // IO用于导出并返回excel文件 
        System.IO.StringWriter strWriter = null;
         System.Web.UI.HtmlTextWriter htmlWriter = null;
 
        if (dtData != null)
         {
             // 设置编码和附件格式 
            curContext.Response.ContentType = "application/vnd.ms-excel";
             curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
             curContext.Response.Charset = "utf-8";
 
            // 导出excel文件 
            strWriter = new System.IO.StringWriter();
             htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
             // 为了解决gvData中可能进行了分页的情况,需要重新定义一个无分页的GridView 
            gvExport = new System.Web.UI.WebControls.GridView();
             gvExport.DataSource = dtData.DefaultView;
             gvExport.AllowPaging = false;
             gvExport.DataBind();
 
            // 返回客户端 
            gvExport.RenderControl(htmlWriter);
             curContext.Response.Write("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=gb2312\" />" + strWriter.ToString());
             curContext.Response.End();
         }
     }
 

  
 
    /// <summary>
     
/// 直接输出Excel
     
/// </summary>
     
/// <param name="dtData"></param>
     public static void DataTable2Excel(System.Data.DataTable dtData)
     {
           System.Web.UI.WebControls.DataGrid dgExport = null;
       // 当前对话
       System.Web.HttpContext curContext = System.Web.HttpContext.Current;
       // IO用于导出并返回excel文件
       System.IO.StringWriter strWriter = null;
       System.Web.UI.HtmlTextWriter htmlWriter = null;
 
      if (dtData != null)
       {
         // 设置编码和附件格式
         curContext.Response.ContentType = "application/vnd.ms-excel";
         curContext.Response.ContentEncoding =System.Text.Encoding.UTF8;
         curContext.Response.Charset = "";
                  
        // 导出excel文件
         strWriter = new System.IO.StringWriter();
         htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
 
        // 为了解决dgData中可能进行了分页的情况,需要重新定义一个无分页的DataGrid
         dgExport = new System.Web.UI.WebControls.DataGrid();          
        dgExport.DataSource = dtData.DefaultView;
         dgExport.AllowPaging = false;
         dgExport.DataBind();
 
        // 返回客户端
         dgExport.RenderControl(htmlWriter);  
         curContext.Response.Write(strWriter.ToString());
         curContext.Response.End();
       }
     }
 
 
 
    /// <summary>
     
/// dtData是要导出为Excel的DataTable,FileName是要导出的Excel文件名(不加.xls)
     
/// </summary>
     
/// <param name="dtData"></param>
     
/// <param name="FileName"></param>
     private void DataTable3Excel(System.Data.DataTable dtData, String FileName)
     {
         System.Web.UI.WebControls.GridView dgExport = null;
         //当前对话 
        System.Web.HttpContext curContext = System.Web.HttpContext.Current;
         //IO用于导出并返回excel文件 
        System.IO.StringWriter strWriter = null;
         System.Web.UI.HtmlTextWriter htmlWriter = null;
 
        if (dtData != null)
         {
             //设置编码和附件格式 
            
//System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8)作用是方式中文文件名乱码
             curContext.Response.AddHeader("content-disposition""attachment;filename=" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls");
             curContext.Response.ContentType = "application nd.ms-excel";
             curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
             curContext.Response.Charset = "GB2312";
 
            //导出Excel文件 
            strWriter = new System.IO.StringWriter();
             htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
 
            //为了解决dgData中可能进行了分页的情况,需要重新定义一个无分页的GridView 
            dgExport = new System.Web.UI.WebControls.GridView();
             dgExport.DataSource = dtData.DefaultView;
             dgExport.AllowPaging = false;
             dgExport.DataBind();
 
            //下载到客户端 
            dgExport.RenderControl(htmlWriter);
             curContext.Response.Write(strWriter.ToString());
             curContext.Response.End();
         }
     }
 
    /// <summary>
     
/// Datatable to Excel带自定文件名的导出
     
/// </summary>
     
/// <param name="dt"></param>
     
/// <param name="FileName"></param>
     private void DataTable4Excel(System.Data.DataTable dt, string FileName)
     {
         System.IO.StringWriter stringWriter = new System.IO.StringWriter();
         HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);
         DataGrid excel = new DataGrid();
         //System.Web.UI.WebControls.TableItemStyle AlternatingStyle = new TableItemStyle();
         System.Web.UI.WebControls.TableItemStyle headerStyle = new TableItemStyle();
         //System.Web.UI.WebControls.TableItemStyle itemStyle = new TableItemStyle();
         
//AlternatingStyle.BackColor = System.Drawing.Color.LightGray;
         headerStyle.BackColor = System.Drawing.Color.LightGray;
         headerStyle.Font.Bold = true;
         headerStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center;
         //itemStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center; ;
 
        
//excel.AlternatingItemStyle.MergeWith(AlternatingStyle);
         excel.HeaderStyle.MergeWith(headerStyle);
         //excel.ItemStyle.MergeWith(itemStyle);
         excel.GridLines = GridLines.Both;
         excel.HeaderStyle.Font.Bold = true;
         excel.DataSource = dt.DefaultView;   //输出DataTable的内容
         excel.DataBind();
 
        //for (int i = 0; i < excel.Items.Count; i++)
         
//{
         
//    //excel.Columns[3].ItemStyle.CssClass = "xlsText";
         
//    //excel.Items[i].Cells[3].Style.Add("mso-number-format", "\"@\"");
         
//    for (int j = 0; j < aColTxtFormat.Length; j++)
         
//    {
         
//        int nCol = aColTxtFormat[j];
         
//        excel.Items[i].Cells[nCol].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
         
//    }
         
//}
         excel.RenderControl(htmlWriter);
 
        HttpContext.Current.Response.AppendHeader("Content-Disposition""attachment;filename='"+FileName+".xls'");
         //HttpContext.Current.Response.Charset = "UTF-8";
         HttpContext.Current.Response.Charset = "UTF-8";
         HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
         //HttpContext.Current.Response.ContentType=".xls/.txt/.doc";image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword 
        HttpContext.Current.Response.ContentType = ".xls";
         HttpContext.Current.Response.Write(stringWriter.ToString());
         HttpContext.Current.Response.End();
 
    }
posted @ 2012-09-15 12:19  hishanghai  阅读(688)  评论(0编辑  收藏  举报