我的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_OUTPUT\EXCEL_TEST.xlsx";
workbook.SaveAs( outfile,Excel.XlSaveAsAccessMode.xlNoChange,Type.Missing);
workbook.Close();
excel.Quit();
