楼主,参考一下using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Reflection;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Win32;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}//定义方法GetData(),返回一个数据表
protected DataSet GetData()
{ //建立连接
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["HRSConnect"].ConnectionString);
con.Open();
string sqlstr = "select people_id ID,people_name ,people_age ,people_home ,people_salary from HR_People order by people_id desc";
//SqlCommand cmd = new SqlCommand(sqlstr,con);
SqlDataAdapter sda = new SqlDataAdapter(sqlstr,con);
DataSet ds = new DataSet();
sda.Fill(ds,"excel");
con.Close();
return ds;
}//导出到Excel按钮
protected void Button1_Click(object sender, System.EventArgs e)
{
ExportExcel();
}//生成Excel文件的代码 protected void ExportExcel()
{
Excel.Application excel = new Excel.Application();
Excel.Workbook wb = excel.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); // 创建工作簿
Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1]; // 创建工作页
DataSet ds = GetData();
int iMaxRow = ds.Tables["Excel"].Rows.Count;
int iMaxCol = ds.Tables["Excel"].Columns.Count;
// 设置格式
ws.get_Range(ws.Cells[1, 1], ws.Cells[1, iMaxCol]).Font.Name = "黑体";
ws.get_Range(ws.Cells[1, 1], ws.Cells[1, iMaxCol]).Font.Bold = true;
ws.get_Range(ws.Cells[1, 1], ws.Cells[iMaxRow + 1, iMaxCol]).Borders.LineStyle = 1;
// 设置标题
excel.Cells[1, 1] = "编号";
excel.Cells[1, 2] = "姓名";
excel.Cells[1, 3] = "年龄";
excel.Cells[1, 4] = "籍贯";
excel.Cells[1, 5] = "薪资"; // 填充数据
for (int iRow = 0; iRow < iMaxRow; iRow++)
{
for (int iCol = 0; iCol < iMaxCol; iCol++)
{
excel.Cells[iRow + 2, iCol + 1] = ds.Tables["Excel"].Rows[iRow][iCol].ToString();
}
}
// 保存Excel
excel.Save("人员基本信息资料.xls");
// 打开Excel
excel.Visible = true;
}
}
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Reflection;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Win32;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}//定义方法GetData(),返回一个数据表
protected DataSet GetData()
{ //建立连接
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["HRSConnect"].ConnectionString);
con.Open();
string sqlstr = "select people_id ID,people_name ,people_age ,people_home ,people_salary from HR_People order by people_id desc";
//SqlCommand cmd = new SqlCommand(sqlstr,con);
SqlDataAdapter sda = new SqlDataAdapter(sqlstr,con);
DataSet ds = new DataSet();
sda.Fill(ds,"excel");
con.Close();
return ds;
}//导出到Excel按钮
protected void Button1_Click(object sender, System.EventArgs e)
{
ExportExcel();
}//生成Excel文件的代码 protected void ExportExcel()
{
Excel.Application excel = new Excel.Application();
Excel.Workbook wb = excel.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); // 创建工作簿
Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1]; // 创建工作页
DataSet ds = GetData();
int iMaxRow = ds.Tables["Excel"].Rows.Count;
int iMaxCol = ds.Tables["Excel"].Columns.Count;
// 设置格式
ws.get_Range(ws.Cells[1, 1], ws.Cells[1, iMaxCol]).Font.Name = "黑体";
ws.get_Range(ws.Cells[1, 1], ws.Cells[1, iMaxCol]).Font.Bold = true;
ws.get_Range(ws.Cells[1, 1], ws.Cells[iMaxRow + 1, iMaxCol]).Borders.LineStyle = 1;
// 设置标题
excel.Cells[1, 1] = "编号";
excel.Cells[1, 2] = "姓名";
excel.Cells[1, 3] = "年龄";
excel.Cells[1, 4] = "籍贯";
excel.Cells[1, 5] = "薪资"; // 填充数据
for (int iRow = 0; iRow < iMaxRow; iRow++)
{
for (int iCol = 0; iCol < iMaxCol; iCol++)
{
excel.Cells[iRow + 2, iCol + 1] = ds.Tables["Excel"].Rows[iRow][iCol].ToString();
}
}
// 保存Excel
excel.Save("人员基本信息资料.xls");
// 打开Excel
excel.Visible = true;
}
}
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货