asp.net中读取Excel文件并插入Sqlserver数据库

于 2007-08-29 13:16:00 发布
阅读量2.4k 收藏 3
点赞数
CC 4.0 BY-SA版权
分类专栏： Asp.net内容 文章标签： sqlserver asp.net excel dataset string object
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/andylaudotnet/article/details/1763600

客户端代码：

<%@ Page language="c#" Codebehind="BaseData.aspx.cs" AutoEventWireup="false" Inherits="BaseDataImport.BaseData" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
 <HEAD>
  <title>BaseData</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">
  <LINK href="AYBTStyle.css" type="text/css" rel="stylesheet">
 </HEAD>
 <body MS_POSITIONING="GridLayout">
  <form id="Form1" method="post" runat="server">
   <table cellSpacing="0" cellPadding="0" width="100%" align="center" border="0">
    <tr>
     <td class="whitebg" align="center">
      <table cellSpacing="0" cellPadding="0" width="100%" border="0">
       <tr>
        <td class="searchboxtitle" align="center">基础信息导入</td>
       </tr>
      </table>
      <table cellSpacing="0" cellPadding="0" width="100%" border="0">
       <TR>
        <TD>选择上传文件：<input id="myFile" style="WIDTH: 496px; HEIGHT: 22px" type="file" size="63" name="myFile"
          runat="server"></TD>
       </TR>
       <TR>
        <TD>选择上传类型：<select id="sltType" name="sltType" runat="server">
          <option value="" selected>没选择</option>
          <option value="Inter_AirArea">区域</option>
          <option value="Inter_AirCountry">国家</option>
          <option value="Inter_AirCity">城市</option>
          <option value="Inter_AirPort">机场</option>
          <option value="Inter_AirLine">航空公司</option>
         </select>
        </TD>
       </TR>
       <tr>
        <td align="center"><br>
         &nbsp;&nbsp; <INPUT id="btnShow" οnclick="return upload();" type="button" size="10" value="上传并显示" name="btnShow"
          runat="server">&nbsp;&nbsp; <INPUT id="btnParser" disabled type="button" size="10" value="分析数据并入库" name="btnParser"
          runat="server">
        </td>
       </tr>
       <tr>
        <td align="left"><br>
         &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
         <asp:label id="LblShowMsg" runat="server" ForeColor="red"></asp:label></td>
       </tr>
      </table>
     </td>
    </tr>
   </table>
   <br>
   <TABLE id="ResultTable" cellSpacing="0" cellPadding="0" width="800" border="0" runat="server">
    <tr>
     <td align="center" class="whitebg"  width="100%">
     <asp:datagrid id="dgExcel" runat="server" CellPadding="5"  BorderColor="DodgerBlue" Width="100%"
       HeaderStyle-BackColor="Silver"></asp:datagrid></td>
    </tr>
   </TABLE>
  </form>
  <script language="javascript">
 function upload()
 { 
  if(document.getElementById("myFile").value=="")
  {
   alert("请选择要上传的文件！");
   document.getElementById("myFile").focus();
   return false;      
  }
  else if(document.getElementById("myFile").value!="")
  {
   var p=document.getElementById("myFile").value.lastIndexOf('.');
   if (document.getElementById("myFile").value.substring(p)!=".xls")
   {
    alert("请选择正确的文件！");
    document.getElementById("myFile").focus();
    return false; 
   }      
  }
  
  if(Form1.sltType.value=="")
  {
   alert("选择上传类型！");
   document.getElementById("sltType").focus();
   return false; 
  }
  
  __doPostBack('btnShow','');
    return true;
  
 }
 
 
 function settrbgA(strId,newClass)
 {
  //document.getElementById(strId).style.cursor="hand";
  document.getElementById(strId).className=newClass;
 }
 
 
 function resettrbgA(strId,oldClass)
 {
  document.getElementById(strId).className=oldClass;
 }
  </script>
 </body>
</HTML>

服务器端代码：

using System;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Text;
using System.Web.UI;
using System.Data.OleDb;

using System.Configuration;

using System.Text.RegularExpressions;

namespace BaseDataImport
{
 /// <summary>
 /// BaseData 的摘要说明。
 /// </summary>
 public class BaseData : System.Web.UI.Page
 {
  protected System.Web.UI.WebControls.Label LblShowMsg;
  protected System.Web.UI.HtmlControls.HtmlInputFile myFile;
  protected System.Web.UI.HtmlControls.HtmlSelect sltType;
  protected System.Web.UI.HtmlControls.HtmlInputButton btnShow;
  protected System.Web.UI.HtmlControls.HtmlInputButton btnParser;
  protected System.Web.UI.HtmlControls.HtmlInputButton btnImport;
  protected System.Web.UI.WebControls.DataGrid dgExcel;
  protected System.Web.UI.HtmlControls.HtmlTable ResultTable;
 
  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.btnShow.ServerClick += new System.EventHandler(this.btnShow_ServerClick);
   this.btnParser.ServerClick += new System.EventHandler(this.btnParser_ServerClick);
   this.Load += new System.EventHandler(this.Page_Load);

  }
  #endregion

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

    LblShowMsg.Text = "正在加载数据......";

    string tmpFilePathName="";
    string tmpFileName="";
    string tmpExtendedName="";

    tmpFilePathName=myFile.PostedFile.FileName;
    string sUrl = HttpContext.Current.Request.Url.AbsoluteUri;
    System.IO.FileInfo fi = new System.IO.FileInfo(tmpFilePathName);
    tmpFileName=fi.Name;
    tmpExtendedName=fi.Extension;

    string sServerPath=Server.MapPath("UploadFile/");
    System.IO.DirectoryInfo di = new System.IO.DirectoryInfo(sServerPath);
    if (!di.Exists)
    {
     di.Create();
    }

    myFile.PostedFile.SaveAs(sServerPath+ tmpFileName);

    string sTName = sltType.Value;
    DataSet ds=this.GetDataFromExcel(sServerPath+ tmpFileName,sTName);

    dgExcel.DataSource=ds;
    dgExcel.DataBind();

    this.btnParser.Disabled=false;
    
   }
   catch(Exception ex)
   {    
    LblShowMsg.Text = ex.Message;
   }

   LblShowMsg.Text = "加载成功！";
  }

 

  /// <summary>
  /// 获取Excel文档后，转换为DataSet
  /// </summary>
  /// <param name="sExcelFilePath">Excel文件</param>
  /// <returns></returns>
  public DataSet GetDataFromExcel(string sExcelFilePath,string sTableName)
  {
   DataSet myDataSet = new DataSet();
   string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source="+ sExcelFilePath +";"+ "Extended Properties=Excel 8.0;";
   OleDbConnection conn = new OleDbConnection(strConn);

   try
   {
    conn.Open();
    //DataTable SchemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables ,new object[]{null}); 
    OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM ["+sTableName+"$]", strConn);
    myCommand.Fill(myDataSet);
   }
   catch(Exception Ex)
   {    
    LblShowMsg.Text = Ex.Message;
   }
   finally
   {
    conn.Close();
    conn.Dispose();
   }

   return myDataSet;
  }

 

 

  /// <summary>
  /// 分析数据
  /// </summary>
  /// <param name="sender"></param>
  /// <param name="e"></param>
  private void btnParser_ServerClick(object sender, System.EventArgs e)
  {
   SqlConnection sc;
   SqlCommand cmd ;
   try
   {
    sc =new SqlConnection(ConfigurationSettings.AppSettings["ConnStr_Local"].ToString());
    sc.Open();
    cmd = sc.CreateCommand();
    //------先清空表中数据------
    cmd.CommandText = "TrunCate Table "+sltType.Value;
    cmd.ExecuteNonQuery();
    //------先清空表中数据------
   }
   catch
   {
    LblShowMsg.Text = "无法操作数据库，操作被终止。";
    return;
   }

   bool bTag = true;
   foreach( DataGridItem item in dgExcel.Items )
   {
    foreach( TableCell  cell in item.Cells )
    {
     if(cell.Text == "&nbsp;")
     {
      bTag = false;
      cell.BackColor=Color.Red ;
      break;
     }

     if(cell.Text=="中文") cell.Text="1";
     if(cell.Text=="英文") cell.Text="2"; 
    }
    
    if(!bTag) break;
    if(!this.ImportDataToTable(item,sltType.Value,cmd,sltType.Value))
    {
     bTag=false;
     break;
    }
   }


   if(bTag==false)
   {
    LblShowMsg.Text = "数据分析不正确，导入终止。";
    this.btnParser.Disabled=true;    
   }
   else
   {
    LblShowMsg.Text = "数据正确，并已入库！";
    this.btnParser.Disabled=true;
   }

   cmd.Dispose();
   sc.Close();
   sc.Dispose();

  }

 

  /// <summary>
  /// 数据入库
  /// </summary>
  /// <param name="Dgi"></param>
  /// <param name="sTable"></param>
  /// <param name="cmd"></param>
  private bool ImportDataToTable(DataGridItem Dgi,string sTable,SqlCommand cmd,string sTableName)
  {
   try
   {
    string sSQL = "";
    //区域
    if (sTableName=="Inter_AirArea")        
     sSQL = "INSERT INTO Inter_AirArea(AreaName, LangCode, IsValid) VALUES (N'"+Dgi.Cells[1].Text+"',"+Dgi.Cells[2].Text+",1) ";      
    //国家
    if (sTableName=="Inter_AirCountry")
    {
     string sAreaID = "0";
     cmd.CommandText = "select AreaID from Inter_AirArea where AreaName=N'"+Dgi.Cells[3].Text.Trim()+"' and LangCode="+Dgi.Cells[2].Text;
     object obj = cmd.ExecuteScalar();
     if(obj==null)
     {
      Dgi.Cells[3].BackColor = Color.Red ;
      return false;
     }
      
     sAreaID = obj.ToString();

     sSQL = " INSERT INTO Inter_AirCountry(CountryCode,CountryName,LangCode,AreaID) VALUES "+
      "(upper(N'"+Dgi.Cells[0].Text+"'),N'"+Dgi.Cells[1].Text+"',"+Dgi.Cells[2].Text+","+sAreaID+") "; 
     Dgi.Cells[3].Text = sAreaID;

    }
    //城市
    if (sTableName=="Inter_AirCity")
    {
     string sCountryName = "";
     cmd.CommandText = "select CountryName from Inter_AirCountry where CountryCode=N'"+Dgi.Cells[3].Text.Trim()+"' and LangCode="+Dgi.Cells[2].Text;
     object obj = cmd.ExecuteScalar();
     if(obj==null)
     {
      Dgi.Cells[3].BackColor = Color.Red ;
      return false;
     }
      
     sCountryName = obj.ToString(); 
    
     
     sSQL = " INSERT INTO Inter_AirCity(CityCode,CityName,LangCode,CountryCode) VALUES "+
      "(upper(N'"+Dgi.Cells[0].Text+"'),N'"+Dgi.Cells[1].Text+"',"+Dgi.Cells[2].Text+",'"+Dgi.Cells[3].Text+"') ";

     Dgi.Cells[3].Text = sCountryName;
    }

    //机场
    if (sTableName=="Inter_AirPort")
    {
     string sCityName = "";
     cmd.CommandText = "select CityName from Inter_AirCity where CityCode=N'"+Dgi.Cells[3].Text.Trim()+"' and LangCode="+Dgi.Cells[2].Text;
     object obj = cmd.ExecuteScalar();
     if(obj==null)
     {
      Dgi.Cells[3].BackColor = Color.Red ;
      return false;
     }
      
     sCityName = obj.ToString(); 
    
     
     sSQL = " INSERT INTO Inter_AirPort(AirPortCode,AirPortName,LangCode,CityCode) VALUES "+
      "(upper(N'"+Dgi.Cells[0].Text+"'),N'"+Dgi.Cells[1].Text+"',"+Dgi.Cells[2].Text+",'"+Dgi.Cells[3].Text+"') ";

     Dgi.Cells[3].Text = sCityName;
    }


    //航空公司
    if (sTableName=="Inter_AirLine")
    {
     string sCountryName = "";
     cmd.CommandText = "select CountryName from Inter_AirCountry where CountryCode=N'"+Dgi.Cells[4].Text.Trim()+"' and LangCode="+Dgi.Cells[3].Text;
     object obj = cmd.ExecuteScalar();
     if(obj==null)
     {
      Dgi.Cells[4].BackColor = Color.Red ;
      return false;
     }
      
     sCountryName = obj.ToString(); 
    
     sSQL = " INSERT INTO Inter_AirLine(AirLineCode,AirLineName,AirLineShortName,LangCode,CountryCode) VALUES "+
      "(upper(N'"+Dgi.Cells[0].Text+"'),N'"+Dgi.Cells[1].Text+"','"+Dgi.Cells[2].Text+"',"+Dgi.Cells[3].Text+",'"+Dgi.Cells[4].Text+"') ";            
     
     Dgi.Cells[4].Text = sCountryName;
    }


    if(sSQL!="")
    {
     cmd.CommandText = sSQL;
     cmd.ExecuteNonQuery();
    }

            }
   catch
   {
    return false;
   }

   return true;

  }


 }
}

 

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值