将 GridView 导出到多个 Excel 工作表 [英] Export GridView to multiple Excel sheet
本文介绍了将 GridView 导出到多个 Excel 工作表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的 Web 应用程序中有两个 Gridview.我需要在单击 (ExcelExpot) 按钮时将值导出到 Excel 中,相应地,Sheet1 和 Sheet2.
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...
}
推荐答案
我同意@Andrew Burgess 的观点,并已将他的代码实施到我的一个项目中.只是为了记录,代码中有一些小错误会导致一些 COM 异常.更正后的代码如下(问题是 Excel 从 1 到 n 对工作表、行、列进行编号,而不是从零开始).
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_OUTPUTEXCEL_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屋!
查看全文