C#：excel导入导出

转载

mb5fed440247fb5 2018-03-18 10:04:00

资源：excelService 服务

排列问题

导出时，数字和字符的排列格式默认不一样，数字靠右，字符靠左，想让排列一致，有两个办法：

1 在绑定时，就做固定格式

2 导出时，将数字那一列转换为文本格式，例如以下

protected void myGridView_RowDataBound(object sender, GridViewRowEventArgs e)  
{  
            if (e.Row.RowType == DataControlRowType.DataRow)  
            {  
//让数字以文本形式表示   
                e.Row.Cells[4].Attributes.Add("style", "vnd.ms-excel.numberformat:@");  
}  
}

转换函数

        /// <summary>
        /// 导出button
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnExport_Click(object sender, System.Web.UI.ImageClickEventArgs e)
        {
            //导出文件名称
            string strFileName = "报表.xls";

            //取原始数据。并绑定
            this.dagInfo.AllowPaging = false;
            this.dagInfo.AllowSorting = false;
            DataTable dt = BingData();
            this.dagInfo.DataSource = dt;
            this.dagInfo.DataBind();
            //使用本页面直接输出
            WHPT_WebApp.Statistics.GridStyleReport.ToExcel(this.dagInfo
                , strFileName,
                delegate(System.Web.UI.HtmlTextWriter writer)
                {
                    writer.Write(string.Format("<table width=100%><tr><td align=center colspan=5>{0}</td></tr></table>", "报表"));
                }
                , null);

            //恢复分页数据
            this.dagInfo.AllowPaging = true;
            this.dagInfo.AllowSorting = true;
            BindInfo(1, "");
            return;
        }

        #endregion

        /// <summary>
        /// 转换成EXCEL
        /// </summary>
        /// <param name="ctl"></param>
        /// <param name="file_name"></param>
        public static void ToExcel(System.Web.UI.Control ctl, string file_name,RenderCallBack beforeRende,RenderCallBack afterRender)
        {
            //将控件数据导出成Excel文件
            if (string.IsNullOrEmpty(file_name))
                return;
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(file_name, System.Text.Encoding.UTF8));
            HttpContext.Current.Response.Charset = "GB2312"; //"UTF-8";       
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
            HttpContext.Current.Response.ContentType = "application/ms-excel";//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
            ctl.Page.EnableViewState = true;
            System.IO.StringWriter tw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
            if (beforeRende != null)
                beforeRende(hw);
            ctl.RenderControl(hw);
            if (afterRender != null)
                afterRender(hw);
            HttpContext.Current.Response.Write(GetHtmlHeadText(tw.ToString()));
            HttpContext.Current.Response.End();

        }