gridview等控件数据导出excel

 

　　最近因为项目需求，将gridview等控件的数据导出excel。我找了两种解决方案，和大家分享一下。

　　1.第一种比较简单。

　　

Response.Clear();
            Response.Charset = "GB2312";
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            Response.ContentType = "application/ms-excel";
            string strSaveFileName = "对账单明细.xls";
            Response.AppendHeader("Content-Disposition", "attachment; filename=" + strSaveFileName);
            
            this.EnableViewState = false;
            string colHeaders = "", Is_item = "";
            
            List<object> CustBillLine = DispatchHelper.GetCustBillLineByCRecNo(txtCRecNo.Text.Trim());
            //将list泛型集合转换成datatable
            DataTable result = new DataTable();
            if (CustBillLine.Count > 0)
            {
                PropertyInfo[] propertys = CustBillLine[0].GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    Type colType = pi.PropertyType;
                     //解决非基本类型值为nullable的问题
                    if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                    {
                        colType = colType.GetGenericArguments()[0];
                    }
                    result.Columns.Add(new DataColumn(pi.Name, colType));
                }
                for (int i = 0; i < CustBillLine.Count; i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys)
                    {
                        object obj = pi.GetValue(CustBillLine[i], null);
                        tempList.Add(obj);
                    }
                    object[] array = tempList.ToArray();
                    result.LoadDataRow(array, true);
                }
            }
            string[] arrTitle = { --这里输入数据的标题，如姓名，年龄等-- };
            for (int i = 0; i < result.Columns.Count; i++)
            {
                colHeaders += arrTitle[i] + "\t";
            }
            colHeaders += "\n";
            Response.Write(colHeaders);
            DataRow[] myRow = result.Select("");
            foreach (DataRow row in myRow)                
            {                   
                //在当前行中，逐列取得数据，数据之间以\t分割，结束时加回车符\n                    
                for (int i = 0; i < result.Columns.Count; i++)                    
                {                        
                    Is_item += row[i].ToString() + "\t";                   
                }                   
                Is_item += "\n";
                Response.Write(Is_item);                   
                Is_item = "";               
            }
            Response.End();

就是将数据和标题各自获取之后，然后循环遍历显示出来。
这种方式的导出，并不是真正意义上excel文件，他只是兼容excel的html文件。如果仅仅是导出excel作为显示，这种方式比较方便，代码也简洁，这种方式导出的文件WPS（金山的office）也能打开。如果打开文件，选择另存为，以.xls保存，也能转换成真正的excel文件。

你复制完代码，点击输出，居然发现页面不仅仅控件的数据导出来，连button都导出来了，这是嘛回事？原来少加了一个方法

public override void VerifyRenderingInServerForm(Control control)
    {

    }

每一个服务器控件都会执行这个方法，来检查当前控件是否在ServerForm标记内（调试过，确实如此）。很明显，我们输出的文档类型是" application/excel " ，而非默认的"text/html"类型，控件当然不在Form之中，因此也会抛出异常(控件不在Form中的异常)，结果，系统又调用默认的输出流，把整个页都导出来了，所以在页面中重写VerifyRenderingInServerForm方法，阻止系统调用默认的VerifyRenderingInServerForm方法。

这里插播个和导出excel无关的问题。大家可以看见上面list泛型转换成datatable类型的代码。其中有一句话

if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
{
          colType = colType.GetGenericArguments()[0];
}

这句话是防止获取的数据类型不是基础的类型，但是值为nullable的问题。如果你的泛型里有非基础类型的数据，就要添加这句话，否则就会报出异常。

2.第二种稍微复杂一点。

前提：必须安装了微软公司的office产品，否则。。。虽然能导出。。。但是WPS打开显示的是乱码。

首先，大家看一下创建excel等的方法。

    /// <summary>
    /// 创建Excel实例
    /// </summary>
    /// <param name="excelApp">Excel实例</param>
    /// <param name="wookBooks">工作簿</param>
    /// <param name="wookBook">第一个工作簿</param>
    /// <param name="wookSheets">工作表</param>
    /// <param name="wookSheet">第一个工作表</param>
    /// <param name="proIntPtr">句柄</param>
    /// <param name="isExport">导出：True；导入：False</param>
    /// <param name="strFileName">导入的文件路径</param>
    protected void CreateExcelInstance(ref object excelApp, ref object wookBooks, ref object wookBook, ref object wookSheets, ref IntPtr proIntPtr, bool isExport, string strFileName)
    {
        //创建Excel实例.
        Type objClassType = Type.GetTypeFromProgID("Excel.Application");
        excelApp = Activator.CreateInstance(objClassType);

        //获取进程号，关闭进程时使用。
        object objPt = excelApp.GetType().InvokeMember("Hwnd", BindingFlags.GetProperty, null, excelApp, null);
        proIntPtr = new IntPtr((Int32)objPt);

        //获取workbooks.
        wookBooks = excelApp.GetType().InvokeMember("Workbooks",
        BindingFlags.GetProperty, null, excelApp, null);

        if (isExport)
        {
            //添加新的workbook.
            wookBook = wookBooks.GetType().InvokeMember("Add",
                BindingFlags.InvokeMethod, null, wookBooks, new object[] { 1 });
        }
        else
        {
            //打开workbook.
            wookBook = wookBooks.GetType().InvokeMember("Open",
                BindingFlags.InvokeMethod, null, wookBooks, new object[] { strFileName });
        }

        //获取worksheets.
        wookSheets = wookBook.GetType().InvokeMember("Worksheets",
            BindingFlags.GetProperty, null, wookBook, null);
    }

    /// <summary>
    /// 获取单元格范围
    /// </summary>
    /// <param name="sheet">当前工作表</param>
    /// <param name="cellStartRow">开始单元格行号</param>
    /// <param name="cellStartColumn">开始单元格列号</param>
    /// <param name="cellEndRow">结束单元格行号</param>
    /// <param name="cellEndColumn">结束单元格列号</param>
    /// <returns>单元格范围</returns>
    protected object GetExcelCellRange(object sheet, int cellStartRow, int cellStartColumn, int cellEndRow, int cellEndColumn)
    {
        //根据起始单元格取得范围
        Object[] Parameters = new Object[2];
        Parameters[0] = GetExcelCell(sheet, cellStartRow, cellStartColumn);
        Parameters[1] = GetExcelCell(sheet, cellEndRow, cellEndColumn);
        return sheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, sheet, Parameters);
    }

    /// <summary>
    /// 获取单元格范围
    /// </summary>
    /// <param name="sheet">当前工作表</param>
    /// <param name="cellStart">开始单元格</param>
    /// <param name="cellEnd">结束单元格</param>
    /// <returns>单元格范围</returns>
    private object GetExcelCellRange(object sheet, object cellStart, object cellEnd)
    {
        //根据起始单元格取得范围
        Object[] Parameters = new Object[2];
        Parameters[0] = cellStart;
        Parameters[1] = cellEnd;
        return sheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, sheet, Parameters);
    }
    /// <summary>
    /// 根据行号列号获取单元格
    /// </summary>
    /// <param name="sheet">工作表</param>
    /// <param name="row">行号</param>
    /// <param name="column">列号</param>
    /// <returns>单元格</returns>
    private object GetExcelCell(object sheet, object row, object column)
    {
        //根据行号列号获取单元格
        return sheet.GetType().InvokeMember("Cells",
            BindingFlags.GetProperty, null, sheet, new object[2] { row, column });
    }
    /// <summary>
    /// 根据行号列号获取单元格
    /// </summary>
    /// <param name="sheet">工作表</param>
    /// <param name="param">行列数组</param>
    /// <returns>单元格</returns>
    private object GetExcelCell(object sheet, object[] param)
    {
        //根据行号列号获取单元格
        return sheet.GetType().InvokeMember("Cells",
            BindingFlags.GetProperty, null, sheet, param);
    }
    /// <summary>
    /// 设置标题样式
    /// </summary>
    /// <param name="range">标题行范围</param>
    private void SetSheetHeaderStyle(ref object range)
    {
        //设置标题格式为居中对齐 
        range.GetType().InvokeMember("HorizontalAlignment", BindingFlags.SetProperty,
            null, range, new object[] { -4108 });//Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter

        //设置标题颜色为浅黄色，共计有56种
        object objInt = range.GetType().InvokeMember("Interior", BindingFlags.GetProperty,
              null, range, null);
        objInt.GetType().InvokeMember("ColorIndex", BindingFlags.SetProperty,
              null, objInt, new object[] { 19 });

        //设置标题字体
        object objFont = range.GetType().InvokeMember("Font", BindingFlags.GetProperty,
              null, range, null);
        objFont.GetType().InvokeMember("Bold", BindingFlags.SetProperty,
              null, objFont, new object[] { true });
        objFont.GetType().InvokeMember("Size", BindingFlags.SetProperty,
              null, objFont, new object[] { 10 });
    }
    /// <summary>
    /// 销毁Excel实例
    /// </summary>
    /// <param name="excelApp">Excel实例</param>
    /// <param name="wookBooks">工作簿</param>
    /// <param name="wookBook">第一个工作簿</param>
    /// <param name="wookSheets">工作表</param>
    /// <param name="wookSheet">第一个工作表</param>
    /// <param name="proIntPtr">句柄</param>
    protected void DisposeExcelInstance(ref object excelApp, ref object wookBooks, ref object wookBook, ref object wookSheets, ref object wookSheet)
    {
        //退出Excel
        excelApp.GetType().InvokeMember("Quit", BindingFlags.InvokeMethod, null, excelApp, null);

        //释放Com对象
        if (wookSheet != null) Marshal.ReleaseComObject(wookSheet);
        if (wookSheets != null) Marshal.ReleaseComObject(wookSheets);
        if (wookBook != null) Marshal.ReleaseComObject(wookBook);
        if (wookBooks != null) Marshal.ReleaseComObject(wookBooks);
        if (excelApp != null) Marshal.ReleaseComObject(excelApp);

        wookSheet = null;
        wookSheets = null;
        wookBook = null;
        wookBooks = null;
        excelApp = null;
    }
    /// <summary>
    /// 设置外框样式
    /// </summary>
    /// <param name="sheet">当前工作表</param>
    /// <param name="range">选定范围</param>
    /// <param name="rowsCount">数据行数</param>
    /// <param name="columnsCount">数据列数</param>
    private void SetSheetBorderStyle(ref object sheet, ref object range, int rowsCount, int columnsCount)
    {
        //取得所有开始单元格
        range = GetExcelCellRange(sheet, 1, 1, 1 + rowsCount, columnsCount);
        //行高为15
        range.GetType().InvokeMember("RowHeight", BindingFlags.SetProperty,
            null, range, new object[] { 15 });
        //列宽为10
        range.GetType().InvokeMember("ColumnWidth", BindingFlags.SetProperty,
                null, range, new object[] { 12 });
        //绘制边框
        object objBorders = range.GetType().InvokeMember("Borders", BindingFlags.GetProperty,
              null, range, null);
        objBorders.GetType().InvokeMember("LineStyle", BindingFlags.SetProperty,
              null, objBorders, new object[] { 1 });

        //===============================
        //取得最左边开始单元格
        range = GetExcelCellRange(sheet, 1, 1, 1 + rowsCount, 1);
        //设置左边线加粗 
        object objLeftBor = range.GetType().InvokeMember("Borders", BindingFlags.GetProperty,
            null, range, new object[] { 7 });//Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft 7
        objLeftBor.GetType().InvokeMember("Weight", BindingFlags.SetProperty,
            null, objLeftBor, new object[] { 4 });// Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick 4

        //===============================
        //取得最上边开始单元格
        range = GetExcelCellRange(sheet, 1, 1, 1, columnsCount);
        //设置上边线加粗 
        object objTopBor = range.GetType().InvokeMember("Borders", BindingFlags.GetProperty,
            null, range, new object[] { 8 });//Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop 8
        objTopBor.GetType().InvokeMember("Weight", BindingFlags.SetProperty,
            null, objTopBor, new object[] { 4 });// Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick 4

        //===============================
        //取得最右边开始单元格
        range = GetExcelCellRange(sheet, 1, columnsCount, 1 + rowsCount, columnsCount);
        //设置右边线加粗 
        object objRightBor = range.GetType().InvokeMember("Borders", BindingFlags.GetProperty,
            null, range, new object[] { 10 });//Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight 10
        objRightBor.GetType().InvokeMember("Weight", BindingFlags.SetProperty,
            null, objRightBor, new object[] { 4 });// Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick 4

        //===============================
        //取得最下边开始单元格
        range = GetExcelCellRange(sheet, 1 + rowsCount, 1, 1 + rowsCount, columnsCount);
        //设置下边线加粗 
        object objBottomBor = range.GetType().InvokeMember("Borders", BindingFlags.GetProperty,
            null, range, new object[] { 9 });//Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom 9
        objBottomBor.GetType().InvokeMember("Weight", BindingFlags.SetProperty,
            null, objBottomBor, new object[] { 4 });// Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick 4

    }
    //这段必须  不可删除
    public override void VerifyRenderingInServerForm(Control control)
    {
        //base.VerifyRenderingInServerForm(control);
    }

大家仔细看一下每个方法的作用，注释很详细的，别直接粘贴，那样对个人成长没帮助。。。

下面就是导出excel的方法

List<object> head = DispatchHelper.GetDispatchHeadByFRecNo(FRecNo, FCreator, dateFrom, dateTo);
        
        DataTable dt = new DataTable();
        //结果集放进datatable
        if (gv.Rows.Count > 0)
        {
            PropertyInfo[] propertys = head[0].GetType().GetProperties();
            foreach (PropertyInfo pi in propertys)
            {
                Type colType = pi.PropertyType;
                if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                {
                    colType = colType.GetGenericArguments()[0];
                }
                dt.Columns.Add(new DataColumn(pi.Name, colType));
            }
            for (int i = 0; i < head.Count; i++)
            {
                ArrayList tempList = new ArrayList();
                foreach (PropertyInfo pi in propertys)
                {
                    object obj = pi.GetValue(head[i], null);
                    tempList.Add(obj);
                }
                object[] array = tempList.ToArray();
                dt.LoadDataRow(array, true);
            }
        }
        else
        {
            Response.Write("没有数据");
        }
            object excel = null;//excel
            object xBk = null;//Workbooks
            object objBooks_Late = null;//Workbook
            object objSheets_Late = null;//Worksheets
            object xSt = null;//Worksheet
            object objRange_Late;//获取的范围
            object[] Parameters;//参数集合

            //gv.AllowPaging = false;
            //gv.AllowSorting = false;
            IntPtr pt = new IntPtr();//进程号
            try
            {
                //创建Excel实例,并获得工作簿和进程句柄等
                CreateExcelInstance(ref excel, ref objBooks_Late, ref xBk, ref objSheets_Late, ref pt, true, null);
                //获取第一个worksheet.
                Parameters = new Object[1];
                Parameters[0] = 1;
                xSt = objSheets_Late.GetType().InvokeMember("Item",
                    BindingFlags.GetProperty, null, objSheets_Late, Parameters);

                int rowsCount = dt.Rows.Count;// dt.Rows.Count;
                int columnsCount = dt.Columns.Count;// dt.Columns.Count;

                //设置excel文件的标题
                objRange_Late = GetExcelCellRange(xSt, 1, 1, 1, 1);
                Parameters = new Object[1];
                Parameters[0] = FileName;
                //excel文件的标题赋值
                objRange_Late.GetType().InvokeMember("Value", BindingFlags.SetProperty,
                     null, objRange_Late, Parameters);

                //===================================
                //下面的标题，就是数据对应的名称
                //设置标题
                object[] objHeaders = new Object[columnsCount];
                //取得标题开始单元格                
                object CellsStart = GetExcelCell(xSt, 2, 1);
                //取得标题结束单元格               
                object CellsEnd = GetExcelCell(xSt, 2, columnsCount);
                //取得标题单元格范围
                objRange_Late = GetExcelCellRange(xSt, CellsStart, CellsEnd);
                //获取标题内容
                for (int i = 0; i < columnsCount; i++)
                {
                    if (dt.Columns[i].ToString().ToLower().IndexOf("res") == -1)
                    {
                        objHeaders[i] = dt.Columns[i].ToString();
                        if (objHeaders[i].Equals("FRecNo"))
                        {
                            objHeaders[i] = "对账单号";
                        }
                    }
                    else
                    {

                        string strResID = dt.Columns[i].ToString().ToLower().Replace("res_", "");
                        int intResID = 0;
                        if (!int.TryParse(strResID, out intResID))
                        {
                            intResID = 0;
                        }
                    }
                }

                Parameters = new Object[1];
                Parameters[0] = objHeaders;
                //标题赋值
                objRange_Late.GetType().InvokeMember("Value", BindingFlags.SetProperty,
                    null, objRange_Late, Parameters);
                //设置标题样式
                SetSheetHeaderStyle(ref objRange_Late);
                //设置单元格格式
                if (rowsCount > 0)
                {
                    for (int ii = 0; ii < columnsCount; ii++)
                    {
                        //设置日期单元格格式
                        if (dt.Columns[ii].GetType()== System.Type.GetType("System.DateTime"))
                        {
                            //取得日期列
                            objRange_Late = GetExcelCellRange(xSt, 2, ii + 1, rowsCount + 1, ii + 1);
                            objRange_Late.GetType().InvokeMember("NumberFormat", BindingFlags.SetProperty, null, objRange_Late, new object[] { "yyyy-MM-dd" });
                            continue;
                        }
                    }
                }
                //获取列表内容
                Object[,] MyData = new Object[rowsCount, columnsCount];
                for (int ll = 0; ll < rowsCount; ll++)
                {
                    for (int ii = 0; ii < columnsCount; ii++)
                    {
                        if (dt.Rows[ll][ii] is DBNull)
                        {
                            MyData[ll, ii] = string.Empty;
                            continue;
                        }
                        if (dt.Columns[ii].DataType == System.Type.GetType("System.DateTime"))
                        {
                            MyData[ll, ii] = Convert.ToDateTime(dt.Rows[ll][ii]).ToString("yyyy-MM-dd");
                        }
                        else if (dt.Columns[ii].DataType == System.Type.GetType("System.String"))
                        {
                            MyData[ll, ii] = "'" + dt.Rows[ll][ii].ToString();
                        }
                        else if (dt.Columns[ii].DataType == System.Type.GetType("System.Double"))
                        {
                            MyData[ll, ii] = Convert.ToDouble(dt.Rows[ll][ii]).ToString("#,##0.00");
                        }
                        else
                        {
                            MyData[ll, ii] = "'" + dt.Rows[ll][ii].ToString();
                        }
                    }
                }

                //取得内容开始单元格 
                object CellsContStart = GetExcelCell(xSt, 3, 1);
                //取得内容结束单元格
                if (rowsCount > 0)
                {
                    Parameters = new Object[2] { 2 + rowsCount, columnsCount };
                }
                else
                {
                    Parameters = new Object[2] { 3, columnsCount };
                }
                object CellsContEnd = GetExcelCell(xSt, Parameters);
                //取得内容单元格范围
                objRange_Late = GetExcelCellRange(xSt, CellsContStart, CellsContEnd);
                //数据行赋值
                Parameters = new object[1];
                Parameters[0] = MyData;
                objRange_Late.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, objRange_Late, Parameters);

                //设置外框样式===========================================
                SetSheetBorderStyle(ref xSt, ref objRange_Late, rowsCount + 1, columnsCount);

                String strTempFolderPara = "";
                String strTempFolder = Request.PhysicalApplicationPath + (strTempFolderPara == "" ? "." : strTempFolderPara);
                String strSaveFileName = "对账单.xls";

                string path = strTempFolder + @"\" + strSaveFileName;

                //以某些格式保存时需要交互。将此属性设置为 False 后，Excel 会全部使用默认值
                excel.GetType().InvokeMember("DisplayAlerts",
                 BindingFlags.SetProperty, null, excel, new object[] { false });
                //原来的代码保存方式不好，在2007环境下保存的xls文件在2003下打不开，现以2003与2007兼容的格式保存
                xBk.GetType().InvokeMember("SaveAs",
                    BindingFlags.InvokeMethod, null, xBk, new object[] {path,-4143,Missing.Value , Missing.Value,//Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal -4143; Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet 46 
                false, false,1, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value});// Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange 1

                //释放Com对象
                DisposeExcelInstance(ref excel, ref objBooks_Late, ref xBk, ref objSheets_Late, ref xSt);

                #region 下载文件
                string strFullPath = strTempFolder + "\\" + strSaveFileName;

                Response.Clear();
                Response.Charset = "GB2312";
                Response.Buffer = true;
                this.EnableViewState = false;
                Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
                Response.AppendHeader("Content-Disposition", "attachment;filename=" + strSaveFileName);

                //    指定返回的是一个不能被客户端读取的流，必须被下载 
                Response.ContentType = "application/ms-excel";
                Response.WriteFile(strFullPath);
                Response.Flush();

                Response.Close();
                Response.End();

请注意最后输出的字体格式，我用的是GB2312，各位根据自己的需求选择。

这里的rowcount  columncount都是获取的gridview的，如果gridview前面还有选择框，删除等项，程序就会报错，因为获取标题会把它们获取进去，导致标题和数据对应时，标题多出来。所以这时你可以获取datatable的rowcount和columncount

这种方法，是通过创建一个excel的进程实例，然后导入数据。利用的反射机制，InvokeMember（）方法，这个方法。。。。我不怎么懂，总之很强大。导出的excel文件边框等都可以设置，颜色，字体等等也都可以设置，这种方法比较好。当然他对软件运行环境有要求，必须装office。

别的还有一些方法，有一些专门导出的插件等等，但是自己没试过，有兴趣的朋友可以自己去试试。在此希望大家能有所收获！

转载于:https://www.cnblogs.com/geduo/archive/2013/02/20/2918350.html