asp.net – 将GridView导出到多个Excel工作表

发布时间：2020-12-16 04:20:50 所属栏目：asp.Net 来源：网络整理

导读：我的Web应用程序中有两个Gridview.我需要在单击(ExcelExpot)按钮时将值导出到Excel中相应的Sheet1和Sheet2. protected void ExportToExcel() { this.GridView1.EditIndex = -1; Response.Clear(); Response.Buffer = true; string connectionString = (strin

我的Web应用程序中有两个Gridview.我需要在单击(ExcelExpot)按钮时将值导出到Excel中相应的Sheet1和Sheet2.

protected void ExportToExcel()
    {

        this.GridView1.EditIndex = -1;
        Response.Clear();
        Response.Buffer = true;
        string connectionString = (string)ConfigurationSettings.AppSettings["ConnectionString"];
        SqlConnection sqlconnection = new SqlConnection(connectionString);
        String sqlSelect = "select * from login";
        sqlconnection.Open();
        SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(sqlSelect,connectionString);
        //DataTable dt1
        DataTable dt1 =new DataTable();
        mySqlDataAdapter.Fill(dt1);

        //LinQ Query for dt2
        var query = (from c in dt.AsEnumerable()
        select new {id= c.Field<string>("id"),name=c.Field<string>("name"),city=c.Field<string>("city")}) ;
        DataTable dt2 = new DataTable();
        d2=query.CopyToDatatable();

        DataSet ds=new DataSet();
        ds.Tabls.Add(dt1);
        ds.Tabls.Add(dt2);
        Excel.Application excelHandle1 = PrepareForExport(ds);
        excelHandle1.Visible = true;

    } 
  // code for PrepareForExport(ds);
         PrepareForExport(ds)
             {

                two tables in two worksheets of Excel...

              }

解决方法

我同意@Andrew Burgess并将他的代码实现到我的一个项目中.只是为了记录,代码中的一些小错误将导致一些COM异常.更正后的代码如下(问题是Excel编号表,行,列从1到n不是从零开始).

using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.IO;

        //Print using Ofice InterOp
        Excel.Application excel = new Excel.Application();

        var workbook = (Excel._Workbook)(excel.Workbooks.Add(Missing.Value));

        for (var i = 0; i < dataset.Tables.Count; i++)
        {

                if (workbook.Sheets.Count <= i)
                {
                    workbook.Sheets.Add(Type.Missing,Type.Missing,Type.Missing);
                }

                //NOTE: Excel numbering goes from 1 to n
                var currentSheet = (Excel._Worksheet)workbook.Sheets[i + 1]; 

                for (var y = 0; y < dataset.Tables[i].Rows.Count; y++)
                {
                    for (var x = 0; x < dataset.Tables[i].Rows[y].ItemArray.Count(); x++)
                    {
                        currentSheet.Cells[y+1,x+1] = dataset.Tables[i].Rows[y].ItemArray[x];
                    }
                }
        }

        string outfile = @"C:APP_OUTPUTEXCEL_TEST.xlsx";

        workbook.SaveAs( outfile,Excel.XlSaveAsAccessMode.xlNoChange,Type.Missing);

        workbook.Close();
        excel.Quit();

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!