ASP.NET中GridView数据导出EXCEL小结

本周按业务部门的要求，做一个客户拜访登记管理的小程序（B/S模式），在这里面用到了GridView来显示根据条件查询到的数据，但业务部门同时要求数据要导出为EXCEL方面使用，在网上查找了很多资料，汇总并根据自己的实际情况编写使用，效果不错，现记下以备忘并与大家分享。（开发环境：Windows 2003/VS 2005/SQL SERVER 2005）

导出为EXCEL的代码网上有很多，分析后选择了最简单的直接用流方法导出，文件类型为XLS文件，但实际上是一个文本文件（HTML格式），这样的优点是1、服务器本身不需要安装OFFICE组件，有很好的通用性；2、导出文件为直接生成直接下载，不需要预先保存在服务器上，对空间大小没有需求；3、控制简单，代码容易理解。缺点是有一些EXCEL特有的功能可能无法实现吧。

先将代码贴出，再一步步的讲一下当时遇到的问题和处理方法吧：

 

    Protected Sub btn_export_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_export.Click
        Response.Clear()
        Response.Buffer = True
        Response.Charset = "utf-8"
        Dim FileName As String
        If Session("branch_no") = "" Then
            lbl_inputlist.Text = "登录已超时，请重新登录后再导出！"
            Exit Sub
        End If
        FileName = Session("branch_no") & "_" & DateTime.Now.ToString("yyyyMMddHHmmssfff") & ".xls"
        'FileName = Session("branch_no") & Path.GetRandomFileName() & ".xls"
        Response.AddHeader("Content-Disposition", "attachment;filename=" & FileName)
        Response.ContentType = "application/ms-excel"
        Dim strWriter As New StringWriter()
        Dim htw As New HtmlTextWriter(strWriter)
        GV_list.BackColor = System.Drawing.Color.White
        GV_list.ForeColor = System.Drawing.Color.Black
        GV_list.BorderWidth = 1
        GV_list.RowStyle.BackColor = System.Drawing.Color.White
        GV_list.RowStyle.ForeColor = System.Drawing.Color.Black
        GV_list.AlternatingRowStyle.BackColor = System.Drawing.Color.White
        GV_list.BorderStyle = BorderStyle.Solid
        GV_list.BorderWidth = 1
        GV_list.AlternatingRowStyle.ForeColor = System.Drawing.Color.Black
        GV_list.HeaderStyle.BackColor = Drawing.Color.White
        GV_list.HeaderStyle.ForeColor = Drawing.Color.Black
        GV_list.AllowPaging = False
        GV_list.AllowSorting = False
        GV_list.DataBind()
        GV_list.RenderControl(htw)
        Response.Write(strWriter.ToString)
        Response.End()
        GV_list.AllowPaging = True
        GV_list.DataBind()
    End Sub
    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

    End Sub

    Protected Sub GV_list_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GV_list.RowDataBound
        If e.Row.RowType = DataControlRowType.DataRow Then
            e.Row.Cells(0).Attributes.Add("style", "vnd.ms-excel.numberformat:@;")
            e.Row.Cells(4).Attributes.Add("style", "vnd.ms-excel.numberformat:@;")
            e.Row.Cells(5).Attributes.Add("style", "vnd.ms-excel.numberformat:@;")
        End If
    End Sub

 

 

 

遇到的问题：

一、将网上找到的代码放到相应的地方后，直接运行，程序中断在“GV_list.RenderControl(htw)”处，提示“类型“GridView”的控件“GV_list”必须放在具有 runat=server 的窗体标记内。”，查看了半天也没有找到原因，再次上网搜索，得到结果，在代码中要加上一个阻止系统自动调用检查的过程才能避免这种情况，将过程加上后，果然正常。分析后认为出现问题的原理是这样的，当点击导出按钮时，因为已将返回的文件类型改成了EXCEL文件而非标准的ASPX返回文件（Response.ContentType = "application/ms-excel"），调用VerifyRenderingInServerForm方法进行检查的时候，控件当然不可能在任何一个Form的runat=server中了，因此报错，加上这个空过程后正常。

 

    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
        '空过程
    End Sub

二、导出的EXCEL表，打开后会根据GridView的设定，有不同的前景色和背景色，不利于查看、打印。分析GirdView的各参数，将表格、行、列及表头标题行的颜色全部设为黑白的，解决了这个问题。

 

'表格外观(前景色、背景色)
GV_list.BackColor = System.Drawing.Color.White
GV_list.ForeColor = System.Drawing.Color.Black
'表格线
GV_list.BorderWidth = 1
'数据行外观
GV_list.RowStyle.BackColor = System.Drawing.Color.White
GV_list.RowStyle.ForeColor = System.Drawing.Color.Black
'数据交替行外观
GV_list.AlternatingRowStyle.BackColor = System.Drawing.Color.White
GV_list.AlternatingRowStyle.ForeColor = System.Drawing.Color.Black
'控件外观
GV_list.BorderStyle = BorderStyle.Solid
GV_list.BorderWidth = 1
'表头标题行外观
GV_list.HeaderStyle.BackColor = Drawing.Color.White
GV_list.HeaderStyle.ForeColor = Drawing.Color.Black

 

 

三、数据较多时，GridView设定了分页查看及排序，但在导出时，只导出第一页的内容及页码链接，标题行还带有排序链接，实际上在需要导出数据的时候，我们需要的是一次获得所有数据，EXCEL也有自己的排序等功能，只需要导出一个纯粹的数据就可以了，因此，需要将分页及排序功能屏蔽。

 

'屏蔽分页
GV_list.AllowPaging = False
'屏蔽排序
GV_list.AllowSorting = False

四、一开始，我的导出文件的文件名是固定的，后来发现在实际使用过程中，如果一个操作人员做了多次导出操作的话，如果不小心可能会将原来的导出文件覆盖，因此就使用当前时间加操作机构号的方式来得到一个唯一文件名，防止这种情况发生，在实际使用中，还可以再加上报表的名称。

微软自己也为这种情况提供了一个方法，获取一个随机的文件名（Path.GetRandomFileName()），但我感觉这种方法可能用于生成临时文件更好一些，而下载文件的话，用日期时间做文件名最好。

 

        Dim FileName As String
        FileName = Session("branch_no") & "_" & DateTime.Now.ToString("yyyyMMddHHmmssfff") & ".xls"
        '下面这种方法用来获取临时文件名比较合适
        'FileName = Session("branch_no") & Path.GetRandomFileName() & ".xls"
        Response.AddHeader("Content-Disposition", "attachment;filename=" & FileName)

五、在导出后，发现在生成的文件中，大于11位的数字都被EXCEL显示成了科学计数法，如果是校验位不为X的18位身份证号码的话，最后三位还会被设为“0”，手机号虽然可以正确显示，但如果是座机的话，区号的第一位0也会被自动抹掉，使用起来十分的不方便。

以前在使用一些程序时也遇到过这种情况，当时开发人员的处理办法是在这些字段前面或最后加一个空格、下划线或者单引号等等，但这样也会造成不便，比如在EXCEL表中对这些数据对比、排序、操作时也会有问题，如果将空格或下划线删除，又会马上变为科学计数法，十分不方便。

感谢互联网，呵呵，马上找到了合适的办法，在导出时就指定了该列为文本格式，这样的话，用EXCEL打开文件也就是正常的了。

在下面的代码中，我的0、4、5列分别是工号（14位数字）、身份证号码、联系电话，已被指定为文本格式，在实际使用中，可根据自己的数据情况进行修改，但要记住第一列是Cells(0)，以此类推。

 

    Protected Sub GV_list_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GV_list.RowDataBound
        If e.Row.RowType = DataControlRowType.DataRow Then
            e.Row.Cells(0).Attributes.Add("style", "vnd.ms-excel.numberformat:@;")
            e.Row.Cells(4).Attributes.Add("style", "vnd.ms-excel.numberformat:@;")
            e.Row.Cells(5).Attributes.Add("style", "vnd.ms-excel.numberformat:@;")
        End If
    End Sub

如果数据量很大，而你又是一个注重细节的人（在乎文件大小和数据流量的人）的话，也可以定义一个style为文本格式，然后将相应的列指定为此style，下面就是一个例子，而且我对日期格式也进行了指定，这样的话日期看起来也好看一些。

 

'下面的代码是在导出事件中的
GV_list.RenderControl(htw)
'将css代码加在此处
response.Write("<style> .text { vnd.ms-excel.numberformat:@; } .cndate {vnd.ms-excel.numberformat:yyyy年m月d日} </style>")
Response.Write(strWriter.ToString)

'下面是一个单独的过程
   Protected Sub GV_list_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GV_list.RowDataBound
        If e.Row.RowType = DataControlRowType.DataRow Then
            e.Row.Cells(0).Attributes.Add("class", "text")
            e.Row.Cells(4).Attributes.Add("class", "text")
            e.Row.Cells(5).Attributes.Add("class", "text")
            e.Row.Cells(6).Attributes.Add("class", "cndate")
        End If
    End Sub

边学边做，花了半天的时间，将整个GridView导出数据到EXCEL的功能了解了一个大概，现汇总一下和大家一起分享，因为这次的数据基本可以肯定没有超过六万条的可能，就没有考虑OFFICE2003无法打开的情况，而且如果数据超过了最大允许条数，因为生成并下载的实际上文本格式，也可以直接使用OFFICE2007来打开操作，关于多个工作表导出及导出为“真正”的EXCEL文件的，下一步慢慢来处理吧，呵呵。