Excel数据导入到数据库中

来源：互联网发布：淘宝卖家怎么改会员名编辑：程序博客网时间：2024/06/05 02:02

.aspx:

<%@ Page Language="C#" AutoEventWireup="true" Codebehind="Default.aspx.cs" Inherits="导入数据._Default" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head runat="server">    <title>无标题页</title>    <script type="text/javascript">        function check(){            if( document.getElementById("fuUploadingFile").value == ""){                alert("文件不能为空!");                return false;            }            return true;        }    </script></head><body>    <form id="form1" runat="server">        <div>            <asp:FileUpload ID="fuUploadingFile" runat="server" /><asp:Button ID="btnSubmit"                runat="server" Text="上传" OnClientClick="return check()" OnClick="btnSubmit_Click" />        </div>    </form></body></html>

.CS

using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.IO;using System.Data.OleDb;using System.Data.SqlClient;namespace 导入数据{    public partial class _Default : System.Web.UI.Page    {        SqlConnection con = null;        SqlCommand cmd = null;        string constr = @"Data Source=.;Initial Catalog=MyDB;Integrated Security=True";        protected void Page_Load(object sender, EventArgs e)        {        }        /// <summary>        /// 上传文件        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        protected void btnSubmit_Click(object sender, EventArgs e)        {            if (fuUploadingFile.HasFile)            {                //有文件                string fileName = fuUploadingFile.FileName;                string serverPath = Server.MapPath("./UpExcelFile/");                //检查文件路径                CheckFileMapPath(fileName, serverPath);                //上传至服务器                fuUploadingFile.SaveAs(serverPath + fileName);                //获取Excel内容                GetExcel(fileName, serverPath);            }        }        /// <summary>        /// 检查目录        /// </summary>        /// <param name="fileName"></param>        /// <param name="serverPath"></param>        protected void CheckFileMapPath(string fileName, string serverPath)        {            if (!Directory.Exists(serverPath))            {                //不存在路径                Directory.CreateDirectory(serverPath);            }            if (File.Exists(serverPath + "\\" + fileName))            {                //同路径下有同名文件                File.Delete(serverPath + "\\" + fileName);            }        }        /// <summary>        /// 读取Excel文件内容        /// </summary>        /// <param name="fileName"></param>        /// <param name="serverPath"></param>        protected void GetExcel(string fileName, string serverPath)        {            string olbcn = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =  " + serverPath + fileName + ";Extended Properties=Excel 8.0";            OleDbConnection ocon = new OleDbConnection(olbcn);            ocon.Open();            OleDbDataAdapter oda = new OleDbDataAdapter("select * from [Sheet1$]", ocon);            DataSet ds = new DataSet();            oda.Fill(ds);            ocon.Close();            SaveSQL(ds);        }        /// <summary>        /// Excel数据导入到数据库中        /// </summary>        /// <param name="ds"></param>        protected void SaveSQL(DataSet ds)        {            int sum = 0;            if (ds.Tables[0].Rows.Count <= 0)            {                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script>alert('没有数据')</script>");            }            else            {                using (con = new SqlConnection(constr))                {                    con.Open();                    cmd = con.CreateCommand();                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)                    {                        string sqlstr = "insert into T_MingPieces (FDepartment,Name,Photo,HandPhone,Email) values(";                        sqlstr += "'" + ds.Tables[0].Rows[i][0].ToString() + "',";                        sqlstr += "'" + ds.Tables[0].Rows[i][1].ToString() + "',";                        sqlstr += "'" + ds.Tables[0].Rows[i][2].ToString() + "',";                        sqlstr += "'" + ds.Tables[0].Rows[i][3].ToString() + "',";                        sqlstr += "'" + ds.Tables[0].Rows[i][4].ToString() + "')";                        cmd.CommandText = sqlstr;                        sum += cmd.ExecuteNonQuery();                    }                }                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script>alert('共导入" + sum + "条数据！')</script>");            }        }    }}

0 0