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

最新推荐文章于 2013-03-14 13:57:52 发布

andylaudotnet

最新推荐文章于 2013-03-14 13:57:52 发布

阅读量2.4k

点赞数

分类专栏： Asp.net内容文章标签： sqlserver asp.net excel dataset string object

本文链接：https://blog.csdn.net/andylaudotnet/article/details/1763600

版权

Asp.net内容专栏收录该内容

12 篇文章 0 订阅

订阅专栏

客户端代码：

<%@ 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>
            <INPUT id="btnShow" οnclick="return upload();" type="button" size="10" value="上传并显示" name="btnShow"
          runat="server">   <INPUT id="btnParser" disabled type="button" size="10" value="分析数据并入库" name="btnParser"
          runat="server">
        </td>
       </tr>
       <tr>
        <td align="left"><br>
                  
         <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 == " ")
     {
      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;

}

}
}

andylaudotnet

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
asp.net中读取Excel文件并插入Sqlserver数据库

客户端代码： BaseData http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema"> 基础信息导入选择上传文件： runat="
复制链接

扫一扫

专栏目录