用gridview显示来自excel表格里的数据

来源：互联网发布：怎样安装app软件编辑：程序博客网时间：2024/05/01 10:54

在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代码:

<%@ 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.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<title>操作Excel</title>

</head>

<body>

<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另一条思路,具体案例具体开
发!!!!!!!