在asp.net 2.0中,如何用gridview显示来自建立好的EXCEL文件的内容呢,其实是很简单的,下面给出简单代码片断
protected void Page_Load(object sender, EventArgs e)
{
OleDbConnection DBConnection = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" +
Server.MapPath("~/App_Data/demo1.xls") + ";" + "Extended
Properties=/"Excel 8.0;HDR=Yes/"");
DBConnection.Open();
string SQLString = "SELECT * FROM [Sheet1$]";
OleDbCommand DBCommand = new OleDbCommand(SQLString, DBConnection);
IDataReader DBReader = DBCommand.ExecuteReader();
GridView1.DataSource = DBReader;
GridView1.DataBind();
DBReader.Close();
DBConnection.Close();
}
其中,把demo1.xls放在app_data目录下,这里用select * from [Sheet1$]来将sheet1的内容取出来。
2............................把Excel文件中的数据读入到DataGrid中
使用Excel文件做为DataGrid的数据源是非常简单的,一旦数据被装载进来,就可以把数据再保存进SQL Server或XML中。我们只需要简单地使用OLE DB Provider 来访问Excel文件,然后返回DataSet即可。
下面是要显示的Excel数据contact.xls:
姓名
|
性别
|
地址
|
net_lover | Male | amxh@21cn.com |
amxh | Male | amxh@21cn.com |
孟子 E 章 | Male | amxh@21cn.com |
只需要指定Excel路径,并用[]选择一个工作表即可。
完整代码如下:
<%@ Page Language="C#" Debug="true" %> <%@ Import Namespace="System.Data"%> <%@ Import Namespace="System.Data.OleDb"%> <script runat="server"> private DataSet CreateDataSource(){ string strConn; strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=C://Inetpub//wwwroot//contacts.xls;"+ "Extended Properties=Excel 8.0;"; OleDbConnection conn = new OleDbConnection(strConn); OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [ContactList$]", strConn); DataSet myDataSet = new DataSet(); myCommand.Fill(myDataSet); return myDataSet; } public void Page_Load(Object sender, EventArgs e){ if (!IsPostBack) { mygrid.DataSource = CreateDataSource(); mygrid.DataBind(); } } </script> <center> <form runat="server"> <asp:datagrid runat="server" AutoGenerateColumns="false" width="500" id="mygrid"> <HeaderStyle BorderColor="White" BackColor="black" ForeColor="White" Font-Bold="True" Font-Name="Arial" Font-Size="9" HorizontalAlign="Center"/> <ItemStyle BorderColor="" BackColor="#FFFFF0" ForeColor="Black" Font-Name="Arial" Font-Size="8" Font-Bold="False" HorizontalAlign="Center"/> <Columns> <asp:BoundColumn HeaderText="姓名" ReadOnly="true" DataField="姓名"/> <asp:BoundColumn HeaderText="性别" ReadOnly="true" DataField="性别"/> <asp:BoundColumn HeaderText="Email" ReadOnly="true" DataField="地址"/> </Columns> </asp:datagrid> </form>
假设你的Excel文件的存放路径为"D:/2.xls",表名为1,那么可以这么写代码;
OleDbConnection cn = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source=D://2.xls;Extend Properties=Excel 8.0");
OleDbDataAdapter da = new OleDbDataAdapter("select * from [1$]"cn);
DataSet ds= new DataSet();
cn.Open();
da.Fill(ds,"newtabel");
da.Dispose();
cn.Close();
定义sql连接sqlcn,命令sqlcmd,代码我就不写了
sqlcn.Open()
for(int i=0;i<ds.Tabels["newtabel"].Rows.Count;i++)
{
cmd=new SqlCommand("insert into monthdata (目标字段,自己写)values('"+ds.Tabels["newtabel"].Rows[i][0]+"'.......)",sqlcn);//这里注意对应关系,以及数据类型就可以了
sqlcmd.ExecuteNoQuery();
}
sqlcmd.Dispose();
sqlcn.Close();
以下仅供参考
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D://2.xls;Extended Properties=Excel 8.0";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [1$]", strConn);
DataSet ds = new DataSet();
myCommand.Fill(ds,"newtabel");
DataGrid1.DataBind();
SqlConnection sqlcn=DB.createCon();
sqlcn.Open();
for(int i=0;i<ds.Tables ["newtabel"].Rows.Count;i++)
{
SqlCommand MyAdd=new SqlCommand("insert into monthdata (DateTypeID,DataTitle,DataName,DanWei,MonthTol,LastMonthTol,Amount,BFB,DataMonth)values('"+ds.Tables ["newtabel"].Rows [i][0]+"','"+ds.Tables["newtabel"].Rows[i][1]+"','"+ds.Tables["newtabel"].Rows[i][2]+"','"+ds.Tables["newtabel"].Rows[i][3]+"','"+ds.Tables["newtabel"].Rows[i][4]+"','"+ds.Tables["newtabel"].Rows[i][5]+"','"+ds.Tables["newtabel"].Rows[i][6]+"','"+ds.Tables["newtabel"].Rows[i][7]+"','"+ds.Tables["newtabel"].Rows[i][8]+"')",sqlcn);//这里注意对应关系,以及数据类型就可以了
MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][0].ToString (),SqlDbType.VarChar,20));
MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][1].ToString (),SqlDbType.VarChar,50));
MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][2].ToString (),SqlDbType.VarChar,20));
MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][3].ToString (),SqlDbType.VarChar,20));
MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][4].ToString (),SqlDbType.Decimal ,9));
MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][5].ToString (),SqlDbType.Decimal ,9));
MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][6].ToString (),SqlDbType.Decimal ,9));
MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][7].ToString (),SqlDbType.Decimal ,9));
MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][8].ToString (),SqlDbType.VarChar,20));
MyAdd.ExecuteNonQuery ();
}
下面的代码展示了如何将Excel内容显示到GridView中:
aspx代码:
![](https://i-blog.csdnimg.cn/blog_migrate/0be121fa5b8988fbabbbc526af3b0fc0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/0be121fa5b8988fbabbbc526af3b0fc0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/b854634c0904529d4018c4c3336be836.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
.cs代码:
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/0be121fa5b8988fbabbbc526af3b0fc0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/b854634c0904529d4018c4c3336be836.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4fd96b3cf02f4c7b5c8964ac8167f7af.gif)
5.........................................导入-导出Excel表格
简单的导出
/// <summary>
/// <本方法是以Page或DataGrid为媒介导出Excel表格或者Word文档>
/// </summary>
public void ToExcel(System.Web.UI.Control ctl)
{
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=Excel.xls"); //把filename里的.xls换成.doc就可以导出Word文档
HttpContext.Current.Response.Charset ="GB2312";
HttpContext.Current.Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");
HttpContext.Current.Response.ContentType ="application/ms-excel"; //image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
ctl.Page.EnableViewState =false;
System.IO.StringWriter tw = new System.IO.StringWriter() ;
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw);
ctl.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
}
简单的显示
/// <summary>
/// Path:Excel 的路径
/// ss:Excel 的 表 名
/// </summary>
public DataSet ExcelToDS(string Path,string ss)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel="select * from ["+ss +"$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds,"tablevalue");
if (conn != null)
{
conn.Close();
}
return ds;
}
//导入excel
private void Button1_Click(object sender, System.EventArgs e)
{
string aa=System.Web.HttpContext.Current.Server.MapPath(TextBox2.Text.Trim()); //得到服务器位置
string pathsave=System.Web.HttpContext.Current.Server.MapPath(TextBox1.Text.Trim()); //得到服务器位置
DataSet ds= ExcelToDS(aa,"a");//取得绑定的DataSet 可改DataSet为你的DataSet了.
Excel.Application ExcelApp=new Excel.ApplicationClass();
Excel.Workbook ExcelWorkBook=ExcelApp.Application.Workbooks.Add(true);
Excel.Worksheet ExcelWorkSheet=(Excel.Worksheet)ExcelWorkBook.Worksheets[1];
int rowcount,columncount;
rowcount=(int)ds.Tables["tablevalue"].Rows.Count;
columncount=(int)ds.Tables["tablevalue"].Columns.Count;
//开始填充 //随意写内容了...
int ii,jj;
ExcelApp.Cells[1,1]="公司";
for ( ii=0;ii<rowcount;ii++)
{
for ( jj=0;jj<columncount;jj++)
{ ExcelApp.Cells[ii+4,jj+1]=ds.Tables["tablevalue"].Rows[ii][jj].ToString().Trim(); }
}
ExcelWorkBook.SaveCopyAs(pathsave); ExcelWorkSheet=null;
ExcelWorkBook.Close(false,null,null);
ExcelApp.Quit();
System.GC.Collect();
Label6.Text="导入完成!!!";
Button2.Visible=true;
}
6..................................... 把Excel文件内容转换为dataset
把Excel文件内容转换为dataset
/// <summary>
/// 传入Excel路径,然后把Excel文件内容转换为dataset
/// </summary>
/// <param name="Path">string Path</param>
/// <returns>DataSet</returns>
public DataSet ExcelToDS(string Path)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Path + ";" +
"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcelPSID = "";
string strExcelBox = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcelPSID="select * from [Sheet1$]"; //Excel表Sheet1
myCommand = new OleDbDataAdapter(strExcelPSID, strConn);
ds = new DataSet();
myCommand.Fill(ds,"Sheet1");
strExcelBox="select * from [Sheet2$]"; //Excel表Sheet1
myCommand = new OleDbDataAdapter(strExcelBox, strConn);
conn.Close();
myCommand.Fill(ds,"Sheet2");
return ds;
}
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ P
ath +";"+"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.O
leDbSchemaGuid.Tables,null);
string tableName=schemaTable.Rows[0][2].ToString().Trim();
Excel文件的写入
public void DSToExcel(string Path,DataSet oldds)
{
//先得到汇总EXCEL的DataSet 主要目的是获得EXCEL在DataSet中的结构
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source ="+p
ath1+";Extended Properties=Excel 8.0" ;
OleDbConnection myConn = new OleDbConnection(strCon) ;
string strCom="select * from [Sheet1$]";
myConn.Open ( ) ;
OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom, myConn ) ;
ystem.Data.OleDb.OleDbCommandBuilder builder=new OleDbCommandBuilder(my
Command);
//QuotePrefix和QuoteSuffix主要是对builder生成InsertComment命令时使用。
builder.QuotePrefix="["; //获取insert语句中保留字符(起始位置)
builder.QuoteSuffix="]"; //获取insert语句中保留字符(结束位置)
DataSet newds=new DataSet();
myCommand.Fill(newds ,"Table1") ;
for(int i=0;i<oldds.Tables[0].Rows.Count;i++)
{
//在这里不能使用ImportRow方法将一行导入到news中,因为ImportRow将保留原
来DataRow的所有设置(DataRowState状态不变)。在使用ImportRow后newds内有值,
但不能更新到Excel中因为所有导入行的DataRowState!=Added
DataRow nrow=aDataSet.Tables["Table1"].NewRow();
for(int j=0;j<newds.Tables[0].Columns.Count;j++)
{
nrow[j]=oldds.Tables[0].Rows[i][j];
}
newds.Tables["Table1"].Rows.Add(nrow);
}
myCommand.Update(newds,"Table1");
myConn.Close();
}
以上只是一个示例只能给大家提供一个关于操作EXCEL另一条思路,具体案例具体开
发!!!!!!!