从Infragistics UltraWebGrid 导出数据到Excel

在 Infragistics UltraWebGrid 所在的页面上点击鼠标右键，可以在右键菜单中选择“导出到Microsoft Office Excel(X)”来实现将Infragistics UltraWebGrid 中的数据导出到Excel中，但是对于复合表头或者有隐藏行、隐藏列的情况，用这种方法处理起来就不太方便，会给客户增加一些不必要的处理麻烦。为了能方便的处理将有复合表头、隐藏行、隐藏列的Infragistics UltraWebGrid 中的数据导出到Excel中，我编写了以下的导出类，可以实现导出功能。由于时间有限，对于导出后背景颜色等细节的处理，没有添加进去。代码如下：

using  System;
using  System.Collections.Generic;
using  System.Text;
using  System.Data;

// 添加引用：Com -> Microsoft Excel 11.0 Object Library 
using  Microsoft.Office.Core;
using  Microsoft.Office.Interop.Excel;
using  Infragistics.WebUI.UltraWebGrid;
using  System.Web;
using  System.Web.UI;
using  System.Web.UI.WebControls;
using  System.IO;
using  System.Diagnostics;

namespace  ExportToExcel
... {
    /**//// <summary>
    /// 导出数据到Excel的类
    /// </summary>
    public class Export
    ...{
        private Microsoft.Office.Interop.Excel.Application xlApp;
        private Microsoft.Office.Interop.Excel.Workbook workbook;
        private object missing = System.Reflection.Missing.Value;
        private byte[] Buffer;
       
        //导出文件的路径（长名）
        private string Report = "";

        //导入到Excel时的行开始位置
        private int rowStartIndex = 1;

        //导入到Excel时的列开始位置
        private int colStartIndex = 1;

        //是否显示标题
        bool isShowTitle = true;

        //是否显示边框线
        bool isShowGridLine = true;

        //表格标题字体大小
        private int titleFontSize = 14;

        //表格内容字体大小
        private int tableFontSize = 12;

        /**//// <summary>
        /// 获取或设置导入到Excel时在Excel中行的开始位置(大于0的整数)
        /// </summary>
        public int RowStartIndex
        ...{
            get ...{ return rowStartIndex; }
            set 
            ...{
                if (value > 0)
                ...{
                    rowStartIndex = value;
                }
                else
                ...{
                    rowStartIndex = 1;
                }
            }
        }

        /**//// <summary>
        /// 获取或设置导入到Excel时在Excel中列的开始位置(大于0的整数)
        /// </summary>
        public int ColStartIndex
        ...{
            get ...{ return colStartIndex; }
            set 
            ...{
                if (value > 0)
                ...{
                    colStartIndex = value;
                }
                else
                ...{
                    colStartIndex = 1;
                }
            }
        }

        /**//// <summary>
        /// 获取或设置是否显示表格标题
        /// </summary>
        public bool IsShowTitle
        ...{
            get ...{ return isShowTitle; }
            set ...{ isShowTitle = value; }
        }

        /**//// <summary>
        /// 获取或设置是否显示表格的边框和格线
        /// </summary>
        public bool IsShowGridLine
        ...{
            get ...{ return isShowGridLine; }
            set ...{ isShowGridLine = value; }
        }

        /**//// <summary>
        /// 获取或设置表格标题字体大小(大于0的整数)
        /// </summary>
        public int TitleFontSize
        ...{
            get ...{ return titleFontSize; }
            set
            ...{
                if (value > 0)
                ...{
                    titleFontSize = value;
                }
                else
                ...{
                    titleFontSize = 14;
                }
            }
        }

        /**//// <summary>
        /// 获取或设置表格内容字体大小(大于0的整数)
        /// </summary>
        public int TableFontSize
        ...{
            get ...{ return tableFontSize; }
            set
            ...{
                if (value > 0)
                ...{
                    tableFontSize = value;
                }
                else
                ...{
                    tableFontSize = 12;
                }
            }
        }

       /**//// <summary>
        /// 构造函数
       /// </summary>
        /// <param name="tempDirectory">存放临时文件的目录</param>
        public Export(string tempDirectory)
        ...{
            this.xlApp = new Application();
            this.xlApp.DisplayAlerts = false;
            this.workbook = xlApp.Workbooks.Add(true);
            this.Report = this.Report = System.IO.Path.Combine(tempDirectory, DateTime.Now.ToString("yyyyMMdd-HHmmss") + ".xls");
        }

        /**//// <summary>
        /// 析构函数
        /// </summary>
        ~Export()
        ...{
            CloseExcle();
        }

        /**//// <summary>
        /// 将UltraWebGrid中的内容导入到Excel文档中
        /// </summary>
        /// <param name="grid">导出数据的UltraWebGrid的ID</param>
        /// <param name="title">导出的表格的标题</param>
        /// <param name="isShowHiddenRow">标志是否显示隐藏的行</param>
        /// <param name="isShowHiddenCol">标志是否显示隐藏的列</param>
        /// <param name="Response">封装来自ASP.NET操作的HTTP相应信息</param>
        public void UltraWebGridExportToExcel(UltraWebGrid grid, string title, bool isShowHiddenRow, bool isShowHiddenCol, HttpResponse Response)
        ...{
            //注意：Excel的行列序均从1开始
            try
            ...{
                Infragistics.WebUI.UltraWebGrid.HeadersCollection dt = grid.Bands[0].HeaderLayout;

                画表前的预处理#region 画表前的预处理
                //清除掉被覆盖的表头
                for (int i = 0; i < dt.Count; i++)
                ...{
                    if (!dt[i].HasRowLayoutColumnInfo)
                    ...{
                        dt.RemoveAt(i);
                        i--;
                    }
                }

                //如果不显示隐藏列的话，先删除隐藏的列对应的表头,然后清除隐藏列
                List<int> list = new List<int>();//用来记录那些列是被隐藏了
                if (!isShowHiddenCol)
                ...{
                    for (int i = 0; i < grid.Columns.Count; i++)
                    ...{
                        if (grid.Columns[i].Hidden)
                        ...{
                            list.Add(i);
                            //删除表头
                            for (int j = 0; j < dt.Count; j++)
                            ...{
                                if (dt[j].RowLayoutColumnInfo.OriginX == i)
                                ...{
                                    dt.RemoveAt(j);
                                    j--;
                                }
                            }
                        }
                    }
                    //把删除的列的后一列的表头往前串
                    for (int i = 0; i < list.Count; i++)
                    ...{
                        for (int j = 0; j < dt.Count; j++)
                        ...{
                            if (dt[j].RowLayoutColumnInfo.OriginX == list[i] + 1)
                            ...{
                                dt[j].RowLayoutColumnInfo.OriginX = list[i];
                            }
                        }
                    }
                    //删除列
                    for (int i = 0; i < grid.Columns.Count; i++)
                    ...{
                        if (grid.Columns[i].Hidden)
                        ...{
                            grid.Columns.RemoveAt(i);
                            i--;
                        }
                    }
                }
                #endregion

                画表过程#region 画表过程
                //记录当前画到哪行
                int rowIndex = rowStartIndex;
                //记录当前画到哪列
                int colIndex = colStartIndex;
                //画表格标题
                if (isShowTitle)
                ...{
                    Microsoft.Office.Interop.Excel.Range rangeTitle = xlApp.get_Range(xlApp.Cells[rowStartIndex, colStartIndex], xlApp.Cells[rowStartIndex, colStartIndex + grid.Columns.Count - 1]);
                    rangeTitle.MergeCells = true;
                    rangeTitle.Font.Size = titleFontSize;
                    rangeTitle.Font.Bold = true;
                    xlApp.Cells[rowStartIndex, colStartIndex] = title;

                    rowIndex++;
                }

                //开始画表头
                for (int i = 0; i < dt.Count; i++)
                ...{
                    string text = dt[i].Caption.ToString();
                    //在webgrid中的坐标
                    int x1 = dt[i].RowLayoutColumnInfo.OriginX;
                    int y1 = dt[i].RowLayoutColumnInfo.OriginY;
                    int x2 = x1 + dt[i].RowLayoutColumnInfo.SpanX;
                    int y2 = y1 + dt[i].RowLayoutColumnInfo.SpanY;
                    //在excel中的坐标
                    int cellx1;
                    int cellx2;
                    if (isShowTitle)
                    ...{
                        cellx1 = y1 + 1 + rowStartIndex + 1 - 1;
                        cellx2 = y2 + rowStartIndex + 1 - 1;
                    }
                    else
                    ...{
                        cellx1 = y1 + 1 + rowStartIndex - 1;
                        cellx2 = y2 + rowStartIndex - 1;
                    }

                    int celly1 = x1 + 1 + colStartIndex - 1;
                    int celly2 = x2 + colStartIndex - 1;

                    Microsoft.Office.Interop.Excel.Range range = xlApp.get_Range(xlApp.Cells[cellx1, celly1], xlApp.Cells[cellx2, celly2]);
                    range.MergeCells = true;
                    range.Font.Size = tableFontSize;
                    range.Font.Bold = true;
                    //表格线
                    if (isShowGridLine)
                    ...{
                        range.Borders.LineStyle = 1;
                    }
                    xlApp.Cells[cellx1, celly1] = text;

                    //修改标志
                    if (rowIndex < cellx1)
                    ...{
                        rowIndex = cellx1;
                    }
                    if (rowIndex < cellx2)
                    ...{
                        rowIndex = cellx2;
                    }
                }

                //画数据
                for (int i = 0; i < grid.Rows.Count; i++)
                ...{
                    if (!isShowHiddenRow && grid.Rows[i].Hidden)
                    ...{
                        continue;
                    }

                    rowIndex++;
                    colIndex = colStartIndex;
                    for (int j = 0; j < grid.Columns.Count; j++)
                    ...{
                        xlApp.Cells[rowIndex, colIndex] = grid.Rows[i].Cells[j].Text;
                        //表格线
                        if (isShowGridLine)
                        ...{
                            xlApp.get_Range(xlApp.Cells[rowIndex, colIndex], xlApp.Cells[rowIndex, colIndex]).Borders.LineStyle = 1;
                        }
                        xlApp.get_Range(xlApp.Cells[rowIndex, colIndex], xlApp.Cells[rowIndex, colIndex]).Font.Size = tableFontSize;
                        colIndex++;
                    }
                }
                #endregion

                xlApp.Cells.EntireColumn.AutoFit();
                xlApp.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                xlApp.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;

                //保存临时文件到服务器端
                workbook.SaveAs(this.Report, XlFileFormat.xlTemplate, missing, missing, missing, missing, XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
                //发送文件到客户端
                SendFileToClient(Response);
            }
            catch (Exception ex)
            ...{
                throw (ex);
            }
        }

        //产生下载效果导出Excel
        private void SendFileToClient(HttpResponse Response)
        ...{
            try
            ...{
                //删除服务器端的临时文件
                DeleteExcelFile();

                Response.Clear();
                Response.Buffer = true;
                Response.Charset = "GB2312";
                Response.AppendHeader("Content-Disposition", "attachment;filename=ExportDataTable.xls");
                Response.ContentEncoding = System.Text.Encoding.UTF7;
                Response.ContentType = "application/ms-excel";
                Response.BinaryWrite(Buffer);
                Response.Flush();
                Response.Close();
                Response.End();
            }
            catch (Exception ex)
            ...{
                throw (ex);
            }
        }

        
        //清除内存中的Excle进程
        private void CloseExcle()
        ...{
            if (this.workbook != null)
            ...{
                System.Runtime.InteropServices.Marshal.ReleaseComObject(this.workbook);
                this.workbook = null;
            }
            if (this.xlApp != null)
            ...{
                System.Runtime.InteropServices.Marshal.ReleaseComObject(this.xlApp);
                this.xlApp = null;
            }
            
            GC.Collect();
        }

        //删除生成的Excel临时文件
        private void DeleteExcelFile()
        ...{
            this.workbook.Save();
            this.workbook.Close(missing, missing, missing);
            this.xlApp.Quit();
            CloseExcle();

            FileStream MyFileStream = new FileStream(this.Report, FileMode.Open);
            long FileSize = MyFileStream.Length;
            Buffer = new byte[(int)FileSize];
            MyFileStream.Read(Buffer, 0, (int)FileSize);
            MyFileStream.Close();

            FileInfo mode = new FileInfo(this.Report);
            try
            ...{
                mode.Delete();
            }
            catch (Exception ex)
            ...{
                throw (ex);
            }
        }
    }
}

对类的调用方式：

在UltraWebGrid所在页面添加一个导出按钮（如Button1），在该按钮的单击事件中加入如下代码：

   protected   void  Button1_Click( object  sender, EventArgs e)
     ... {
       //在应用程序的根目录下建立一个临时文件夹
        string path = Server.MapPath("~/Temp/");
        ExportToExcel.Export ex = new ExportToExcel.Export(path);
        ex.RowStartIndex = 3;
        ex.ColStartIndex = 3;
        ex.IsShowGridLine = true;
        ex.IsShowTitle = true;
        ex.TableFontSize = 16;
        ex.TitleFontSize = 18;

        ex.UltraWebGridExportToExcel(UltraWebGrid1, "导出数据", false, false, Response);
    }

这样就可以导出数据到Excel，并发送到客户端，然后把服务器端的临时文件自动删除。

以上只是其中的一种方法，如有其它更佳方法，欢迎探讨！