出口GridView控件到多个Excel工作表 [英] Export GridView to multiple Excel sheet

问题描述

我有两个在GridView的我的Web application.I的需要，同时点击（ExcelExpot）按钮的值是出口在Excel工作表Sheet1因此和sheet2。

 保护无效ExportToExcel（）
    {        this.GridView1.EditIndex = -1;
        Response.Clear（）;
        将Response.Buffer =真;
        字符串的connectionString =（字符串）ConfigurationSettings.AppSettings [的ConnectionString];
        的SqlConnection的SqlConnection =新的SqlConnection（的connectionString）;
        字符串的SQLSelect =选择登录*;
        sqlconnection.Open（）;
        SqlDataAdapter的MySqlDataAdapter的=新SqlDataAdapter的（一个SQLSelect，的connectionString）;
        // DataTable的DT1
        DataTable的DT1 =新的DataTable（）;
        mySqlDataAdapter.Fill（DT1）;        // LINQ查询的DT2
        VAR的查询=（从dt.AsEnumerable C（）
        选择新的{ID = c.Field＆LT;串＆GT;（ID），名字= c.Field＆LT;串＆GT;（名称），城市= c.Field＆LT;串＆GT;（城市）}）;
        数据表DT2 =新的DataTable（）;
        D2 = query.CopyToDatatable（）;        DataSet的DS =新的DataSet（）;
        ds.Tabls.Add（DT1）;
        ds.Tabls.Add（DT2）;
        Excel.Application excelHandle1 = prepareForExport（DS）;
        excelHandle1.Visible =真;    }
  // code为prepareForExport（DS）;
         prepareForExport（DS）
             {                两个表中的Excel两个工作表...              }
 

解决方案

我同意@Andrew Burgess和已实施了code到我的项目之一。只是为了记录那里有在code的几个小错误，这将导致一些COM例外。修正后code是低于（问题是，Excel中的数字表，行，从1到n不是从零列）。

 使用Excel =的Microsoft.Office.Interop.Excel;
使用的System.Reflection;
使用System.IO;        //使用Ofice互操作打印
        Excel.Application的Excel =新Excel.Application（）;        变种簿=（Excel._Workbook）（excel.Workbooks.Add（Missing.Value））;        对于（VAR I = 0; I＆LT; dataset.Tables.Count;我++）
        {                如果（workbook.Sheets.Count＆下; =ⅰ）
                {
                    workbook.Sheets.Add（Type.Missing，Type.Missing，Type.Missing，
                                        Type.Missing）;
                }                //注意：Excel那张编号从1到n
                变种currentSheet =（Excel._Worksheet）workbook.Sheets第[i + 1];                对于（VAR Y = 0; Y＆LT; dataset.Tables [I] .Rows.Count; Y ++）
                {
                    对于（VAR X = 0; X＆LT; dataset.Tables [I] .Rows [Y] .ItemArray.Count（）; X ++）
                    {
                        currentSheet.Cells [Y + 1，X + 1] = dataset.Tables [I] .Rows [Y] .ItemArray [X];
                    }
                }
        }        字符串OUTFILE = @C：\\ APP_OUTPUT \\ EXCEL_TEST.xlsx        workbook.SaveAs（OUTFILE，Type.Missing，Type.Missing，Type.Missing，
                        Type.Missing，Type.Missing，Excel.XlSaveAsAccessMode.xlNoChange，
                        Type.Missing，Type.Missing，Type.Missing，Type.Missing，
                        Type.Missing）;        workbook.Close（）;
        excel.Quit（）;
 

I have two Gridview in my Web application.I need ,while clicking the (ExcelExpot) button the values to be Export in Excel Accordingly Sheet1 and 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...

              }

解决方案

I agree with @Andrew Burgess and have implemented his code into one of my projects. Just for the record theres a few small errors in the code which will cause some COM Exceptions. The corrected code is below (the issue was that Excel numbers sheets, rows, columns from 1 to n not from zero).

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,
                                        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_OUTPUT\EXCEL_TEST.xlsx";

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

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

这篇关于出口GridView控件到多个Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！