gridview数据导出到word和excel以及excel的导入

using System;
using System.Collections.Generic;
using
System.Linq;
using System.Web;
using System.Web.UI;
using
System.Web.UI.WebControls;
using System.Data.SqlClient;
using
ProductSystem.Sqlserver;
using Microsoft.Office;
using
Microsoft.Office.Interop;
using System.Data.OleDb;
using
System.Data;
namespace WordExcelTest
{
public partial class
_Default : System.Web.UI.Page
{
protected void
Page_Load(object sender, EventArgs e)
{
string
sql_select_pingjia = "select * from PingJia";

List groupinfoList = new List();

using (SqlDataReader rdr = SqlHelper.ExecuteReader(sql_select_pingjia))

{
while (rdr.Read())
{

pingjiaBLL gf = new pingjiaBLL();//
gf.groupId = int.Parse(rdr["groupId"].ToString());

// gf.groupName = (rdr["groupName"]).ToString();

gf.ID = int.Parse(rdr["ID"].ToString());
gf.CompanyName = rdr["CompanyName"].ToString();
gf.COnstrue= rdr["Construe"].ToString();gf.Specimen =
rdr["Specimen"].ToString();gf.DemOnstrate=
rdr["Demonstrate"].ToString();gf.EngineeringSupport =
rdr["EngineeringSupport"].ToString();
gf.DeliveryCircumstance = rdr["DeliveryCircumstance"].ToString();

groupinfoList.Add(gf);
}
}

this.db_gridview.DataSource = groupinfoList;

this.db_gridview.DataBind();

Response.Write(db_gridview.Columns.Count);

Response.Write(db_gridview.Rows.Count);
}
///

/// 导出到excel
///

///
/// name="e">
protected void btExcel_Click(object
sender, EventArgs e)
{


Microsoft.Office.Interop.Excel.Application excel = new
Microsoft.Office.Interop.Excel.Application();

excel.Application.Workbooks.Add(true);
excel.Visible = true;

//将第一行标题导入
for (int i = 0; i <
this.db_gridview.Rows[0].Cells.Count; i++)
{

excel.Cells[1, i + 1] = this.db_gridview.HeaderRow.Cells[i].Text;


}

//从第二行开始每列导入数据
//excel.Cells[i,j]标识excel中sheet的i行j列
for (int i = 0; i <
this.db_gridview.Rows.Count - 1; i++)
{
for
(int j = 0; j
{excel.Cells[i + 2, j + 1] =
this.db_gridview.Rows[i].Cells[j].Text.ToString();
}

}
}
///

/// 导出到word

///

/// name="sender">
/// name="e">
protected void btWord_Click(object sender,
EventArgs e)
{
Microsoft.Office.Interop.Word.Document
mydoc = new Microsoft.Office.Interop.Word.Document();

Microsoft.Office.Interop.Word.Table mytable;

Microsoft.Office.Interop.Word.Selection mysel;
Object myobj;

//建立Word对象
Microsoft.Office.Interop.Word.Application word =
new Microsoft.Office.Interop.Word.Application();
myobj =
System.Reflection.Missing.Value;
mydoc = word.Documents.Add(ref
myobj, ref myobj, ref myobj, ref myobj);
word.Visible = true;

mydoc.Select();
mysel = word.Selection;

//将数据生成Word表格文件
mytable = mydoc.Tables.Add(mysel.Range,
this.db_gridview.Rows.Count, this.db_gridview.Rows[0].Cells.Count, ref myobj,
ref myobj);
//设置列宽
mytable.Columns.SetWidth(30,
Microsoft.Office.Interop.Word.WdRulerStyle.wdAdjustNone);

//输出列标题数据
for (int i = 0; i i++)
{
mytable.Cell(1, i +
1).Range.InsertAfter(this.db_gridview.HeaderRow.Cells[i].Text);

}
//输出控件中的记录
for (int i = 0; i 1; i++)
{
for (int j = 0; j <
this.db_gridview.Rows[0].Cells.Count; j++)
{

mytable.Cell(i + 2, j +
1).Range.InsertAfter(this.db_gridview.Rows[i].Cells[j].Text.ToString());

}
}
}
///

/// 导入excel到gridview
///

///

/// name="e">
protected void bt_importExcel_Click(object
sender, EventArgs e)
{
string xlsFilePath =
"C:\\Users\\HUXU\\Desktop\\Sheet1.xlsx";
string
connectionString;
cOnnectionString=
"Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + xlsFilePath + ";Extended
Properties=‘Excel 8.0;HDR=False;IMEX=1‘";
OleDbConnection cOnn=
new OleDbConnection(connectionString);
String strQuery = "SELECT
* FROM [Sheet1$]"; //可以更改工作表名称
OleDbDataAdapter da = new
OleDbDataAdapter(strQuery, conn);
DataSet ds = new DataSet();

da.Fill(ds, "Sheet1");
DataTable dt =
ds.Tables[0];
db_gridview.DataSource = null;

db_gridview.DataSource = dt;
db_gridview.DataBind();

conn.Close();
}

}
}