从Excel中读取数据并导入数据库

最新推荐文章于 2024-09-16 09:47:29 发布

water6891

最新推荐文章于 2024-09-16 09:47:29 发布

阅读量445

点赞数

文章标签： excel dataset string session server components

本文链接：https://blog.csdn.net/water6891/article/details/6698437

版权

前台页面

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="test2.aspx.cs" Inherits="test2" %>

<!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>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:FileUpload ID="fileDataInput" runat="server" Width="400px" Height="25px" />
            <asp:Button ID="btn_DataInPut" runat="server" Text="导入数据" Height="25px"
            OnClick="btn_DataInPut_Click" />
     </div>
    </form>
</body>
</html>
<%=Session["Msg"] %>

后台cs

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

using System.IO;
using System.Data.OleDb;
using yueue.ADOKeycap;
using System.Data.Common;

public partial class test2 : System.Web.UI.Page
{
    private Database dbSys = DataBase.GetData();
    private System.IO.Ports.SerialPort serialPort1;
    private System.ComponentModel.IContainer components;
    private OleDbConnection conn = null;

    protected void Page_Load(object sender, EventArgs e)
    {

    }
    //打开Excel连接，读取sheet页数据
    private DataSet ExcelToDataBase(string Path)
    {
        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties='Excel 8.0;IMEX=1;'";
        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, "[sheet1]");
        return ds;
    }
    //关闭连接
    private void CloseConn()
    {
        conn.Close();
    }

    protected void btn_DataInPut_Click(object sender, EventArgs e)
    {
        string filePath = fileDataInput.PostedFile.FileName;
        string ExcelPath = filePath;
        if (ExcelPath.Length > 0)
        {
            DataSet ds = ExcelToDataBase(ExcelPath);
            CloseConn();
            int rowsCount = ds.Tables[0].Rows.Count;
            int columnsCount = ds.Tables[0].Columns.Count;
            if (rowsCount > 0)     // 有数据存在
            {
                if (columnsCount > 0)
                {
                    GetDataFromExcel(ds, rowsCount); //读取Excel表中的数据，组合成Sql语句，并插入到数据库
                }
                else
                {
                    Session["Msg"] = "<script>alert('Excel文件中数据格式有问题，请核查后再导入数据！')</script>";
                    return;
                }
            }
            Session["Msg"] = "<script>alert('数据导入成功！')</script>";
            return;
        }
        else
        {
            Response.Write("<script>alert('读取Excel文件时出现异常错误');</script>");
            return;
        }
        //System.IO.FileInfo fi = new FileInfo(ExcelPath);
        //StreamReader sr = fi.OpenText();
        //string strSql = sr.ReadToEnd(); //全读出来
        //if (strSql.Length > 0)
        //{
        //    if (dbSys.ExecuteNonQuery(strSql) > 0)
        //    {
        //        Session["Msg"] = "<script>alert('数据导入成功！')</script>";
        //        return;
        //    }
        //}
        //else
        //{
        //    Session["Msg"] = "<script>alert('导入失败！')</script>";
        //    return;
        //}

    }


    // 读取Excel表中的数据，组合成Sql语句，并插入到数据库
    private void GetDataFromExcel(DataSet ds, int rowsCount)
    {
        string strSql = "";
        try
        {
            for (int m = 0; m < rowsCount; m++)
            {
                //strSql += "update t_sys_Menu set ";
                //strSql += "FileFolder = '" + ds.Tables[0].Rows[m][5].ToString().Trim() + "'";
                strSql += "FileName = '" + ds.Tables[0].Rows[m][5].ToString().Trim() + "',";
                strSql += "WSMC = '" + ds.Tables[0].Rows[m][13].ToString().Trim() + "',";
                strSql += "nWidth = '" + ds.Tables[0].Rows[m][14].ToString().Trim() + "',";
                strSql += "nHeight = '" + ds.Tables[0].Rows[m][15].ToString().Trim() + "',";
                strSql += "JBXX = '" + ds.Tables[0].Rows[m][16].ToString().Trim() + "'";
                //strSql += "where id='"+ ds.Tables[0].Rows[m][1].ToString().Trim() +"'";
                string strtempp1 = ds.Tables[0].Rows[m][1].ToString().Trim();//221205
                string strtempp5 = ds.Tables[0].Rows[m][5].ToString().Trim();
                string strXZDJ = "";
                string strLDLX = "";
                string strSSZD = "";
                string strSSDD = "";
                strSql += " insert into T_DLDM(ID,DLDM,DLMC,DLLX,DLXX,LMJG,XZDJ,LDLX,FFSS,SSZD,XZQH,SSDD) values ( ";
                strSql += " '" + Common.GetNo() + "','" + ds.Tables[0].Rows[m][0].ToString().Trim() + "','" + ds.Tables[0].Rows[m][1].ToString().Trim() + "','" + ds.Tables[0].Rows[m][2].ToString().Trim() + "','" + ds.Tables[0].Rows[m][3].ToString().Trim() + "',";
                strSql += " '" + ds.Tables[0].Rows[m][4].ToString().Trim() + "','" + strXZDJ + "','" + strLDLX + "','" + ds.Tables[0].Rows[m][5].ToString().Trim() + "','" + strSSZD + "','" + ds.Tables[0].Rows[m][6].ToString().Trim() + "','" + strSSDD + "')";
            }
            if (strSql.Length > 0)
            {
                dbSys.ExecuteNonQuery(strSql);
            }
        }
        catch
        {
        }
    }

    private void InitializeComponent()
    {
        this.components = new System.ComponentModel.Container();
        this.serialPort1 = new System.IO.Ports.SerialPort(this.components);

    }
}