如何使用ExportToExceldll在asp.net中创建excel

Question

我正在使用ExportToExcel dll。在第一次迭代中，我的数据集包含低于记录的

KeyName English Hindi
Name    engval  hival
And second iteration my dataset conatin below record
KeyName English Hindi
Name    engval  hival

我必须在运行时一个接一个地创建xsl文件，如下所示。如何在上面应用样式看起来不错。

hindi.xsl    
KeyName English Hindi
Name    engval  hival
punjabi.xsl
KeyName English panjabi
Name    engval  punjabival

编辑：使用GridViewExportUtil

我正在尝试逐个创建xsl。下面是我的逻辑

for(int i=0;i<2;i++)
{   
//
****logic here to get the record from db and fill the record into dsexcel***
//
gvruntime.DataSource = dsExcel;
gvruntime.DataBind();
string headername = string.Empty;
headername = i+".xls";
GridViewExportUtil.Export(headername, this.gvruntime);  
}

在这种情况下，只有最后一个文件被上一个文件覆盖。。请告诉我如何解决

Answer 1

请尝试此代码，此代码将把网格视图数据导出到excel。您需要首先将数据集绑定到gridview，然后调用此函数

     GridViewExportUtil.Export("hindi.xsl", this.grdhindi);
     GridViewExportUtil.Export("punjabi.xsl", this.grdpunjabi);

// calss code
  public class GridViewExportUtil
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="gv"></param>
        public static void Export(string fileName, GridView gv)
        {
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.ClearHeaders();
            HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
            //HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);
            HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.Private);
            HttpContext.Current.Response.ContentType = "application/ms-excel";
            using (StringWriter sw = new StringWriter())
            {
                using (HtmlTextWriter htw = new HtmlTextWriter(sw))
                {
                    //  Create a form to contain the grid
                    Table table = new Table();
                    table.GridLines = gv.GridLines;
                    //  add the header row to the table
                    if (gv.HeaderRow != null)
                    {
                        //gv.HeaderRow.Font.Bold = true;
                        GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
                        table.Rows.Add(gv.HeaderRow);
                    }
                    //  add each of the data rows to the table
                    foreach (GridViewRow row in gv.Rows)
                    {
                        GridViewExportUtil.PrepareControlForExport(row);
                        table.Rows.Add(row);
                    }
                    //  add the footer row to the table
                    if (gv.FooterRow != null)
                    {
                        GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
                        table.Rows.Add(gv.FooterRow);
                    }
                    //  render the table into the htmlwriter
                    table.RenderControl(htw);
                    byte[] s = Encoding.UTF8.GetBytes(sw.ToString());
                    HttpContext.Current.Response.AddHeader("Content-Length", s.Length.ToString());
                    HttpContext.Current.Response.BinaryWrite(s);
                    //  render the htmlwriter into the response
                    HttpContext.Current.Response.Write(sw.ToString());
                    HttpContext.Current.Response.End();
                }
            }
        }
        /// <summary>
        /// Replace any of the contained controls with literals
        /// </summary>
        /// <param name="control"></param>
        private static void PrepareControlForExport(Control control)
        {
            for (int i = 0; i < control.Controls.Count; i++)
            {
                Control current = control.Controls[i];
                if (current is LinkButton)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
                }
                else if (current is ImageButton)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
                }
                else if (current is HyperLink)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
                }
                else if (current is DropDownList)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
                }
                else if (current is CheckBox)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
                }
                if (current.HasControls())
                {
                    GridViewExportUtil.PrepareControlForExport(current);
                }
            }
        }
    }

或者您可以使用不同的方式导出网格数据以超越

 for(int i=0;i<2;i++)
    {   

    gvruntime.DataSource = dsExcel.Table[i];
    gvruntime.DataBind();
    string headername = string.Empty;
    headername = i+".xls";

 // render the DataGrid control to a file
    using(StreamWriter sw = new StreamWriter("c:\"+headername+".xls"))
    {
        using(HtmlTextWriter hw = new HtmlTextWriter(sw))
        {
            gvruntime.RenderControl(hw);
        }
    } 
    }