关于gridview导出到Excel的问题！

Question

这是网上Copy的一个代码：
    private void Export(string FileType, string FileName)
    {
        //以下三行可选，如果没有的话导出的只是当前页数据，没有其他页数据    
        this.GridView1.AllowPaging = false;
        this.GridView1.AllowSorting = false;
        bind();         //这里是你绑定gridview的方法  
        this.GridView1.Columns[7].Visible = false;  //导出时隐藏操作列    
        this.GridView1.BottomPagerRow.Visible = true;  //隐藏分页行            //string style = @"<style> .text { mso-number-format:\@; } </script> ";    
        Response.Clear();
        Response.Buffer = true;
        //Response.Charset = "GB2312";    
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); // 中文    
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
        // 如果设置为 GetEncoding("GB2312");导出的文件将会出现乱码！！！    
        //Response.ContentEncoding = System.Text.Encoding.UTF7;    
        Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。    
        System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
        this.GridView1.RenderControl(oHtmlTextWriter);
        //Response.Write(style);    
        Response.Output.Write(oStringWriter.ToString());
        Response.Flush();
        Response.End();    }
但是导出的excel表格 是这样的：
看能不能把其他没有字段的格格也显示出来。或者提供个别的导出到excel表的方法 。代码

Answer 1

        /// <summary>
        /// 公共标准Excel格式表格
        /// 创建Excel文件以流方式输出浏览器下载
        /// </summary>
        /// <param name="dv">数据视图</param>
        /// <param name="str">标题</param>
        /// <param name="fileName"></param>
        /// <param name="fileType"></param>
        public void OutputExcel(DataView dv, string strTitle,string fileName)
        {
            try
            {
                //dv为要输出到Excel的数据，str为标题名称
                GC.Collect();
                Application excel;
                int rowIndex = 4;
                int colIndex = 1;
                _Workbook xBk;
                _Worksheet xSt;
                excel = new ApplicationClass();
                xBk = excel.Workbooks.Add(true);
                xSt = (_Worksheet)xBk.ActiveSheet;
                //
                //取得标题
                //
                foreach (DataColumn col in dv.Table.Columns)
                {
                    colIndex++;
                    excel.Cells[4, colIndex] = col.ColumnName;
                    xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐
                }                #region 取得表格中的数据
                foreach (DataRowView row in dv)
                {
                    rowIndex++;
                    colIndex = 1;
                    foreach (DataColumn col in dv.Table.Columns)
                    {
                        colIndex++;
                        if (col.DataType == System.Type.GetType("System.DateTime"))
                        {
                            excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString() == "" ? DateTime.Now + "" : row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
                            xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
                        }
                        else
                            if (col.DataType == System.Type.GetType("System.String"))
                            {
                                excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
                                xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
                            }
                            else
                            {
                                excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                            }
                    }
                }
                #endregion
                #region 设置
                //
                //加载一个合计行
                //
                int rowSum = rowIndex + 1;
                int colSum = 2;
                excel.Cells[rowSum, 2] = "合计";
                xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
                //
                //设置选中的部分的颜色
                //
                xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
                xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Interior.ColorIndex = 19;//设置为浅黄色，共计有56种
                //
                //取得整个报表的标题
                //
                excel.Cells[2, 2] = strTitle;
                //
                //设置整个报表的标题格式
                //
                xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Bold = true;
                xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Size = 22;
                //
                //设置报表表格为最适应宽度
                //
                xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select();
                xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit();
                //
                //设置整个报表的标题为跨列居中
                //
                xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Select();
                xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
                //
                //绘制边框
                //
                xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
                xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;//设置左边线加粗
                xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;//设置上边线加粗
                xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//设置右边线加粗
                xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;//设置下边线加粗
                //
                //显示效果
                //
                excel.Visible = true;                //xSt.Export(Server.MapPath(".")+"\\"+this.xlfile.Text+".xls",SheetExportActionEnum.ssExportActionNone,Microsoft.Office.Interop.OWC.SheetExportFormat.ssExportHTML);
                xBk.SaveCopyAs(System.Web.HttpContext.Current.Server.MapPath(".") + "\\" + fileName + ".xls");
                #endregion
                dv = null;
                xBk.Close(false, null, null);                excel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
                xBk = null;
                excel = null;
                xSt = null;
                GC.Collect();
                //string path = System.Web.HttpContext.Current.Server.MapPath(fileName + ".xls");
                string path = System.Web.HttpContext.Current.Server.MapPath("~/TemplateFile/Import/" + fileName + ".csv");
                System.IO.FileInfo file = new System.IO.FileInfo(path);
                System.Web.HttpContext.Current.Response.Clear();
                System.Web.HttpContext.Current.Response.Charset = "GB2312";
                System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
                // 添加头信息，为"文件下载/另存为"对话框指定默认文件名
                System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpContext.Current.Server.UrlEncode(file.Name));
                // 添加头信息，指定文件大小，让浏览器能够显示下载进度
                System.Web.HttpContext.Current.Response.AddHeader("Content-Length", file.Length.ToString());
                // 指定返回的是一个不能被客户端读取的流，必须被下载
                System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";
                // 把文件流发送到客户端
                System.Web.HttpContext.Current.Response.WriteFile(file.FullName);
                // 停止页面的执行
                System.Web.HttpContext.Current.Response.End();
            }
            catch (Exception)
            {
            }
        } 

Answer 2

你在GridView1的绑定事件里面。判断Cell[x].Text是否为空，为空就加上空格就可以； 

Answer 3

4因为是新人 ，刚开始上的csdn请原谅。

Answer 4

 Application excel;
            int rowIndex = 4;
            int colIndex = 1;
            _Workbook xBk;
            _Worksheet xSt;
            excel = new ApplicationClass();能详细说明下嘛。

Answer 5

今天都是第三次看到这样的问题了，，  /// <summary>
        /// 以datatable 导出Excel文件   
        /// </summary>
        /// <param name="dt"></param>
        public void ExportExcel(DataGridView dgv)
        {
            if (dgv.Rows.Count == 0) return;
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得Sheet1
            Microsoft.Office.Interop.Excel.Range range;
            long totalCount = dgv.Rows.Count;            long rowRead = 0;
            float percent = 0;            //worksheet.Cells[1, 1] = "报表标题";            //写入字段
            for (int i = 0; i < dgv.Columns.Count; i++)
            {
                worksheet.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
                range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
                range.Interior.ColorIndex = 15;
                range.Font.Bold = true;
            }
            //写入数值
            for (int r = 0; r < dgv.Rows.Count; r++)
            {
                for (int i = 0; i < dgv.Columns.Count; i++)
                {
                    //worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];dgv
                    if (dgv[i, r].ValueType == typeof(string))
                    {
                        worksheet.Cells[r + 2, i + 1] = "'" + dgv[i, r].Value.ToString();
                    }
                    else
                    {
                        worksheet.Cells[r + 2, i + 1] = dgv[i, r].Value.ToString();
                    }                }
                rowRead++;
                percent = ((float)(100 * rowRead)) / totalCount;
            }
            xlApp.Visible = true;
        }