asp.net Excel导入 sql2000,搜索了好几天才整合出来的，我容易嘛！

最新推荐文章于 2024-07-11 15:59:41 发布

laoyingisme

最新推荐文章于 2024-07-11 15:59:41 发布

阅读量3k

点赞数 1

文章标签： excel asp.net sql dataset string properties

本文链接：https://blog.csdn.net/laoyingisme/article/details/1999020

版权

1.(.html)
<%@ Page language="c#" Codebehind="insertexcel.aspx.cs" AutoEventWireup="false" Inherits="addmodel.insertexcel" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
  <title>insertexcel</title>
  <meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
  <meta content="C#" name="CODE_LANGUAGE">
  <meta content="JavaScript" name="vs_defaultClientScript">
  <meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
</HEAD>
<body MS_POSITIONING="GridLayout">
  <form id="Form1" method="post" runat="server">
   <tr>
    <td align="right"><asp:datagrid id="dgExportProject" style="Z-INDEX: 101; LEFT: 208px; POSITION: absolute; TOP: 192px"
      runat="server" Width="456px" BorderWidth="1px" BackColor="White" BorderColor="#CC9966" BorderStyle="None"
      CellPadding="4">
      <FooterStyle ForeColor="#330099" BackColor="#FFFFCC"></FooterStyle>
      <SelectedItemStyle Font-Bold="True" ForeColor="#663399" BackColor="#FFCC66"></SelectedItemStyle>
      <ItemStyle ForeColor="#330099" BackColor="White"></ItemStyle>
      <HeaderStyle Font-Bold="True" ForeColor="#FFFFCC" BackColor="#990000"></HeaderStyle>
      <PagerStyle HorizontalAlign="Center" ForeColor="#330099" BackColor="#FFFFCC"></PagerStyle>
     </asp:datagrid><INPUT id="File1" style="Z-INDEX: 102; LEFT: 328px; POSITION: absolute; TOP: 128px" type="file"
      size="15" name="File1" runat="server"><asp:button id="btnSave" style="Z-INDEX: 103; LEFT: 672px; POSITION: absolute; TOP: 120px" runat="server"
      Width="64px" Text="导入" Height="30px"></asp:button><asp:button id="btnReadData" style="Z-INDEX: 104; LEFT: 576px; POSITION: absolute; TOP: 120px"
      runat="server" Width="64px" Text="读取" Height="30px"></asp:button>
     <DIV style="Z-INDEX: 100; LEFT: 184px; WIDTH: 144px; POSITION: absolute; TOP: 128px; HEIGHT: 19px"
      ms_positioning="FlowLayout">
     请选择Excel文档：</td>
    <TD></TD>
   </tr>
   <TR>
    <TD align="center" valign="top"></TD>
   </TR>
   </DIV>
   <DIV style="DISPLAY: inline; Z-INDEX: 106; LEFT: 352px; WIDTH: 320px; POSITION: absolute; TOP: 32px; HEIGHT: 40px"
    ms_positioning="FlowLayout"><FONT face="宋体" size="6"><STRONG>Excel文档导入数据库</STRONG></FONT></DIV>
  </form>
</body>
</HTML>

2.behindcode(.cs)
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;

using System.Configuration;

namespace addmodel
{
/// <summary>
/// insertexcel 的摘要说明。
/// </summary>
public class insertexcel : System.Web.UI.Page
{
  protected System.Web.UI.WebControls.Button btnReadData;
  protected System.Web.UI.WebControls.Button btnSave;
  protected System.Web.UI.WebControls.DataGrid dgExportProject;
  protected System.Web.UI.HtmlControls.HtmlInputFile File1;

  private void Page_Load(object sender, System.EventArgs e)
  {
   // 在此处放置用户代码以初始化页面

  }

  #region Web 窗体设计器生成的代码
  override protected void OnInit(EventArgs e)
  {
   //
   // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
   //
   InitializeComponent();
   base.OnInit(e);
  }

  /// <summary>
  /// 设计器支持所需的方法 - 不要使用代码编辑器修改
  /// 此方法的内容。
  /// </summary>
  private void InitializeComponent()
  {
   this.btnSave.Click += new System.EventHandler(this.btnSave_Click);
   this.btnReadData.Click += new System.EventHandler(this.btnReadData_Click);
   this.Load += new System.EventHandler(this.Page_Load);

}
#endregion

  private void btnReadData_Click(object sender, System.EventArgs e)
  {
   if(File1.Value.Length>1)
   {
    string   fileName   =   System.IO.Path.GetFileName(File1.PostedFile.FileName);
    string   filePath   =   "";
    if(this.File1.Value   ==   "")
    {
     // Comm.Jscript.Alert("请先选择您要导入的文件！");
    }
    else
    {
     int   index   =   fileName.LastIndexOf(".");
     if(index   >   0)
     {
      if(fileName.Substring(index)   ==   ".xls")
      {
       DateTime   now   =   DateTime.Now;
       fileName   =   now.ToShortDateString()   +   now.ToLongTimeString();
       fileName   =   fileName.Replace("-","").Replace(":","").Replace("   ","");
       filePath   =   @"../uploads/"   +   fileName   +   ".xls";
       this.File1.PostedFile.SaveAs(Server.MapPath(filePath));

      }
      else
      {
       //Comm.Jscript.Alert("读入的文件不是XLS");
      }
     }
    }
    if(filePath   !=   "")
    {
     fileName   =   Request.MapPath(filePath);
     // string    strConn ="Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + Dir + "//"+fileName + ";Extended Properties=/"Excel 8.0;HDR=Yes;IMEX=1;/"
     string   strConn   =   "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ fileName   +";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
     string   query   =   "SELECT   *   FROM   [Sheet1$]";
     OleDbCommand   oleCommand   =   new   OleDbCommand(query,   new   OleDbConnection(strConn));
     OleDbDataAdapter   oleAdapter   =   new   OleDbDataAdapter(oleCommand);
     DataSet   myDataSet   =   new   DataSet();

     //   将   Excel   的[Sheet1]表内容填充到   DataSet   对象
     try
     {
      oleAdapter.Fill(myDataSet,"[Sheet1$]");
      //   数据绑定
      this.dgExportProject.DataSource   =   myDataSet;
      this.dgExportProject.DataMember   =   "[Sheet1$]";
      this.dgExportProject.DataBind();

      this.dgExportProject.Visible   =   true;
      this.btnSave.Visible   =   true;
     }
     catch(Exception   exx)
     {
      Response.Write(exx.Message);
      //Comm.Jscript.Alert("注意：请用默认的Sheet1$页名称！");
     }
     finally
     {
      if(File.Exists(filePath))
      {
       File.Delete(filePath);
      }
     }
    }

   }
   else
   {
    Response.Write("<script>alert('请选择正确的路径！')</script>");
   }

  }

  private void btnSave_Click(object sender, System.EventArgs e)
  {


      string conn=ConfigurationSettings.AppSettings["datasource"];
       SqlConnection   sqlConn   =   new   SqlConnection(conn);
       {
        try
        {
         if(sqlConn.State   ==   ConnectionState.Closed)
         {
          sqlConn.Open();
         }

         foreach(DataGridItem   item   in   this.dgExportProject.Items)
         {
          if(item.Cells.Count   ==   2)
          {

           string wldm= item.Cells[0].Text.Trim().Replace(" "," ");
           string cgjg=item.Cells[1].Text.Trim().Replace(" "," ");

           string sSQL   =   "Insert Into wljg (wldm,cgjg) Values ('"+wldm +"','" + cgjg+ "')";
           SqlCommand cmd = new SqlCommand(sSQL,sqlConn);
           cmd.CommandType = CommandType.Text;
           try
           {
            cmd.ExecuteNonQuery();
            Response.Write("<script>alert('导入数据成功!')</script>");

           }
           catch
           {
            Response.Write("<script>alert('导入数据出错,请联系管理员!')</script>");

           }

           cmd.Dispose();

          }
          else
          {
           //Comm.Jscript.Alert("注意：请按照模板格式导入项目信息！");
           return;
          }
         }

        }
        catch(Exception   exx)
        {
         Response.Write(exx.Message);
        }
        finally
        {
         if(sqlConn.State   ==   ConnectionState.Open)
         {
          sqlConn.Close();
         }
        }
       }
       this.dgExportProject.Visible   =   false;
       this.btnSave.Visible   =   false;
      }
}
}

3.(web.config)
<appsettings>
<add key="DataSource" value="uid=sa;PWD=;DATA SOURCE=B1D094D6230A423;INITIAL CATALOG=databasename" />
</appsettings>

laoyingisme

关注

1
点赞
踩
6

收藏

觉得还不错? 一键收藏
6
评论
asp.net Excel导入 sql2000,搜索了好几天才整合出来的，我容易嘛！

1.(.html) insertexcel http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema"> runat="server" Width="456px" BorderWidth="1px" BackColor="White" BorderColor="#CC
复制链接

扫一扫