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<pingjiaBLL> groupinfoList = new List<pingjiaBLL>();
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);
}
/// <summary>
/// 导出到excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
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 < this.db_gridview.Rows[0].Cells.Count; j++)
{
excel.Cells[i + 2, j + 1] = this.db_gridview.Rows[i].Cells[j].Text.ToString();
}
}
}
/// <summary>
/// 导出到word
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
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 < this.db_gridview.Rows[0].Cells.Count; i++)
{
mytable.Cell(1, i + 1).Range.InsertAfter(this.db_gridview.HeaderRow.Cells[i].Text);
}
//输出控件中的记录
for (int i = 0; i < db_gridview.Rows.Count - 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());
}
}
}
/// <summary>
/// 导入excel到gridview
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
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();
}
}
}