在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> 3....................................asp.net2.0将EXCEL导入到MS Sql server2000 假设你的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 (); } 4...........................下面的代码展示了如何将Excel内容显示到GridView中:aspx代码 下面的代码展示了如何将Excel内容显示到GridView中: aspx代码: .aspx <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="TCQA.Web._Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www./TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www./1999/xhtml" > <head runat="server"> <title>操作Excel</title> </head> <body> <form id="form1" runat="server"> <input type="file" id="f_path" runat="server" /> <asp:Button ID="btnImport" runat="server" Text="导入" OnClick="btnImport_Click" /> <asp:GridView ID="gv_list" runat="Server" AutoGenerateColumns="true"> </asp:GridView> </form> </body> </html> .cs代码: protected void btnImport_Click(object sender, EventArgs e) { string Path = f_path.Value; 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 [sheet1$]"; myCommand = new OleDbDataAdapter(strExcel, strConn); ds = new DataSet(); myCommand.Fill(ds, "table1"); gv_list.DataSource = ds; gv_list.DataBind(); } 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; } 对于EXCEL中的表即sheet([sheet1$])如果不是固定的可以使用下面的方法得到 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另一条思路,具体案例具体开 发!!!!!!! 本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/zhanghefu/archive/2007/01/17/1485423.aspx
|