将多个网格视图导出到多个Excel选项卡（工作表） [英] Export multiple gridviews to multiple excel tabs (sheets)

问题描述

我的网站中有6个网格视图，我需要导出到excel，但是每个都在一个不同的表单中。将GridView导出到多个Excel工作表使用的东西非常相似，但他正在使用数据集，我不是。我是C＃的新手，所以我不能改变它来适应我的解决方案。

我的代码将所有网格视图导出到同一张表。我创建了一个循环来运行我的网格视图，现在我需要一个代码来生成每个excel表格。

  protected void btExcel_Click（object sender，EventArgs e）
 {
 Response.Clear（）; 
 Response.Buffer = true; 
 Response.AddHeader（content-disposition，attachment; filename = FARPOP_Mensal_+ txDt.Text +.xls）; 
 Response.ContentEncoding = System.Text.Encoding.GetEncoding（Windows-1252）; 
 Response.Charset =ISO-8859-1; 
 Response.ContentType =application / vnd.ms-excel; 
 
 using（StringWriter sw = new StringWriter（））
 {
 HtmlTextWriter hw = new HtmlTextWriter（sw）; 
 GridView [] gvExcel = new GridView [] {gvAnual，gvPorUF，gvPorFarmacia，gvPorMunicipio，gvPorPV，gvPorCNPJ}; （int i = 0; i< gvExcel.Length; i ++）
 {
 GridView gv = gvExcel [i]; 
 
 
 // 
 //需要重定向到每个工作表？ 
 // 
 
 gvExcel [i] .HeaderRow.BackColor = Color.White; 
 gvExcel [i] .UseAccessibleHeader = false; 
 gvExcel [i] .AllowPaging = false; （int x = 0; x< gvExcel [i] .Columns.Count; x ++）
 {
 gvExcel [i] .Columns [x] .Visible = true; 
} 
 
 gvExcel [i] .DataBind（）; 
 foreach（gvExcel中的TableCell单元格[i] .HeaderRow.Cells）
 {
 cell.BackColor = Color.CadetBlue; 
 cell.Enabled = false; 
} 
 
 foreach（gvExcel [i] .Rows中的GridViewRow行）
 {
 row.BackColor = Color.White; 
 foreach（row.Cell中的TableCell单元格）
 {
 
 cell.Controls.Clear（）; 
 if（row.RowIndex％2 == 0）
 {
 cell.BackColor = Color.White; 
} 
 else 
 {
 cell.BackColor = Color.LightBlue; 
} 
 cell.CssClass =textmode; 
} 
} 
 
 gvExcel [i] .RenderControl（hw）; 
} 
 
 //将数字格式化为字符串的样式
 string style = @< style> .textmode {}< / style>; 
 Response.Write（style）; 
 Response.Output.Write（sw.ToString（））; 
 Response.Flush（）; 
 Response.End（）; 
} 
} 
  

解决方案

遵循 maniak1982 评论，找到了一个很好的解决方案。

所以，如果您需要使用.NET多次工作表进行多个网格浏览，则需要下载 ClosedXML 以及 DocumentFormat.OpenXml.dll 。

引用dll后，使用以下代码：

  protected void btExcel_Click（object sender，EventArgs e）
 {
 XLWorkbook wb = new XLWorkbook（）; 
 GridView [] gvExcel = new GridView [] {GridView1，GridView2，GridView3，GridView4，GridView5，GridView6}; 
 string [] name = new string [] {Name1，Name2，Name3，Name4，Name5，Name6}; （int i = 0; i< gvExcel.Length; i ++）
 {
 if（gvExcel [i] .Visible）
 {
 gvExcel [i ] .AllowPaging = false; 
 gvExcel [i] .DataBind（）; 
 DataTable dt = new DataTable（name [i] .ToString（））; （int z = 0; z< gvExcel [i] .Columns.Count; z ++）
 {
 dt.Columns.Add（gvExcel [i] .Columns [z]。的HeaderText）; 
} 
 
 foreach（gvExcel [i] .Rows中的GridViewRow行）
 {
 dt.Rows.Add（）; code 
 
 dt.Rows [dt.Rows.Count  -  1] [c] = row.Cells并[c]。文本; 
} 
} 
 
 wb.Worksheets.Add（dt）; 
 gvExcel [i] .AllowPaging = true; 
 
} 
 
} 
 Response.Clear（）; 
 Response.Buffer = true; 
 Response.Charset =; 
 Response.ContentType =application / vnd.openxmlformats-officedocument.spreadsheetml.sheet; 
 Response.AddHeader（content-disposition，attachment; filename = Workbook_Name.xlsx）; 
使用（MemoryStream MyMemoryStream = new MemoryStream（））
 {
 wb.SaveAs（MyMemoryStream）; 
 MyMemoryStream.WriteTo（Response.OutputStream）; 
 Response.Flush（）; 
 Response.End（）; 
} 
} 
  

花了我几天的搜索...但它是工作完美
开心

I have 6 gridviews in my website that I need to export to excel, but each one in one different sheet.

This link Export GridView to multiple Excel sheet uses something quite similar, but he is using datasets and I am not. I am new to C#, so I couldnt change it to fit my solution.

My code exports all gridviews to the same sheet. I created a loop to run throught my gridviews, now I need to put a code to generate each one to each excel sheet.

protected void btExcel_Click(object sender, EventArgs e)
{
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment;filename=FARPOP_Mensal_" + txDt.Text + ".xls");
    Response.ContentEncoding = System.Text.Encoding.GetEncoding("Windows-1252");
    Response.Charset = "ISO-8859-1";
    Response.ContentType = "application/vnd.ms-excel";

    using (StringWriter sw = new StringWriter())
    {
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        GridView[] gvExcel = new GridView[] { gvAnual, gvPorUF, gvPorFarmacia, gvPorMunicipio, gvPorPV, gvPorCNPJ };
        for (int i = 0; i < gvExcel.Length; i++)
        {
            GridView gv = gvExcel[i];

            // 
            // Need to redirect to each sheet here?
            //

            gvExcel[i].HeaderRow.BackColor = Color.White;
            gvExcel[i].UseAccessibleHeader = false;
            gvExcel[i].AllowPaging = false;
            for (int x = 0; x < gvExcel[i].Columns.Count; x++)
            {
                gvExcel[i].Columns[x].Visible = true;
            }

            gvExcel[i].DataBind();
            foreach (TableCell cell in gvExcel[i].HeaderRow.Cells)
            {
                cell.BackColor = Color.CadetBlue;
                cell.Enabled = false;
            }

            foreach (GridViewRow row in gvExcel[i].Rows)
            {
                row.BackColor = Color.White;
                foreach (TableCell cell in row.Cells)
                {

                    cell.Controls.Clear();
                    if (row.RowIndex % 2 == 0)
                    {
                        cell.BackColor = Color.White;
                    }
                    else
                    {
                        cell.BackColor = Color.LightBlue;
                    }
                    cell.CssClass = "textmode";
                }
            }

            gvExcel[i].RenderControl(hw);
        }

        //style to format numbers to string
        string style = @"<style> .textmode { } </style>";
        Response.Write(style);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }
}

解决方案

I followed maniak1982 comments and found a good solution.

So, if you need multiple gridviews to multiples worksheets, using .NET, you will need to download ClosedXML and also DocumentFormat.OpenXml.dll.

After referencing the dll, use the following code:

 protected void btExcel_Click(object sender, EventArgs e)
{
    XLWorkbook wb = new XLWorkbook();
    GridView[] gvExcel = new GridView[] { GridView1, GridView2, GridView3, GridView4, GridView5, GridView6};
    string[] name = new string[] { "Name1", "Name2", "Name3", "Name4", "Name5", "Name6" };
    for (int i = 0; i < gvExcel.Length; i++)
    {
        if (gvExcel[i].Visible)
        {
            gvExcel[i].AllowPaging = false;
            gvExcel[i].DataBind();
            DataTable dt = new DataTable(name[i].ToString());
            for (int z = 0; z < gvExcel[i].Columns.Count; z++)
            {
                dt.Columns.Add(gvExcel[i].Columns[z].HeaderText);
            }

            foreach (GridViewRow row in gvExcel[i].Rows)
            {
                dt.Rows.Add();
                for (int c = 0; c < row.Cells.Count; c++)
                {
                    dt.Rows[dt.Rows.Count - 1][c] = row.Cells[c].Text;
                }
            }

            wb.Worksheets.Add(dt);
            gvExcel[i].AllowPaging = true;

        }

    }
    Response.Clear();
    Response.Buffer = true;
    Response.Charset = "";
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AddHeader("content-disposition", "attachment;filename=Workbook_Name.xlsx");
    using (MemoryStream MyMemoryStream = new MemoryStream())
    {
        wb.SaveAs(MyMemoryStream);
        MyMemoryStream.WriteTo(Response.OutputStream);
        Response.Flush();
        Response.End();
    }
}

It took me days of searching... But it's working perfectly. Be happy

这篇关于将多个网格视图导出到多个Excel选项卡（工作表）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！