一.读取Excel数据，并显示
1.配置文件
<configuration>
<system.web>
<compilation debug="true" targetFramework="4.0" />
</system.web>
<appSettings>
<!--连接03版本的-->
<!--<add key="SQLCONNECTIONSTRING" value="Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source="/>
<add key="DBPATH" value="App_Data\username.xls"/>-->
<!--连接07版本的-->
<add key="SQLCONNECTIONSTRING" value="Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0 Xml;Data Source=" />
<add key="DBPATH" value="App_Data\usernames.xlsx"/>
<!--连接07access数据库-->
<add key="OLEDBCONNECTIONSTRING" value="Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Access 12.0 Xml;Data Source=" />
<!--<add key="DBPATH" value="App_Data\username.accdb"/>-->
<!--使用.net生成Excel表格-->
<add key="SQLCONNECTIONSTR" value="data source=.;uid=sa;pwd=123456;database=students;pooling=true"/>
</appSettings>
</configuration>
2.
using System;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
namespace WebApplication
{
public partial class MyExcelToDG : System.Web.UI.Page
{
private readonly string SQLCONNECTIONSTRING = ConfigurationManager.AppSettings["SQLCONNECTIONSTRING"].ToString();
private readonly string DBPATH = ConfigurationManager.AppSettings["DBPATH"].ToString();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GridView1.DataSource = CreateDataSource();
GridView1.DataBind();
}
}
private DataSet CreateDataSource()
{
//设置Excel的文件访问地址
String ExcelDBPath = SQLCONNECTIONSTRING + Server.MapPath(DBPATH) + ";";
//定义访问Excel文件的连接
OleDbConnection conn = new OleDbConnection(ExcelDBPath);
//OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM[username$]",conn);
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM students", conn);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
}
}
二.将sqlserver中的数据导出到Excel中
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace WebApplication
{
public partial class MyDataToExcel : System.Web.UI.Page
{
private readonly string SQLCONNECTIONSTR = ConfigurationManager.AppSettings["SQLCONNECTIONSTR"].ToString();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//创建Excel文件
try
{
CreateExcelTable();
Response.Write("<script>alert('成功导出!')</script>");
}
catch (Exception)
{
Response.Write("<script>alert('发生错误!')</script>");
}
}
}
//获取数据
private DataSet GetData()
{
//从数据库中获取数据
String cmdText = "Select * from Student_Info";
using (SqlConnection conn = new SqlConnection(SQLCONNECTIONSTR))
{
SqlDataAdapter da = new SqlDataAdapter(cmdText, conn);
conn.Open();
DataSet ds = new DataSet();
da.Fill(ds);
return (ds);
}
}
//创建Excel文件
private void CreateExcelTable()
{
//从数据库获取数据
DataSet ds = GetData();
//创建Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//设置行和列的索引
int rowIndex = 1;
int collndex = 0;
//添加Excel对象的WorkBooks
excel.Application.Workbooks.Add(true);
System.Data.DataTable table = ds.Tables[0];
//将所得到的表的列名，赋给单元格
foreach (DataColumn col in table.Columns)
{
//添加列名
collndex++;
excel.Cells[1, collndex] = col.ColumnName;
}
//同样的方法处理数据
foreach(DataRow row in table.Rows)
{
rowIndex++;
collndex = 0;
foreach (DataColumn col in table.Columns)
{
collndex++;
excel.Cells[rowIndex, collndex] = row[col.ColumnName].ToString();
}
}
//不可见，即后台处理
excel.Visible = false;
excel.DisplayAlerts = false;
//保存刚才创建的Excel表格
excel.Save(MapPath("App_Data/ExcelTable.xls"));
excel.Application.Workbooks.Close();
excel.Application.Quit();
excel.Quit();
//释放使用的Excel对象
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
GC.Collect();
}
}
}