EXCEL：将多个EXCEL文件输出到同一SQL SERVER数据库

来源：互联网发布：网络电视港澳台的apk 编辑：程序博客网时间：2024/05/12 01:52

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="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>读取文本文件到Excel并对数据列进行格式化</title>    <style type="text/css">        .style1        {            border: medium double #E7E7E7;            font-size: 10pt;        }    </style></head><body style="font-size: 10pt">    <form id="form1" runat="server">    <div>        <table class="style1">            <tr>                <td>多个Excel文件</td>                <td>                    <asp:ListBox ID="ListBox1" runat="server" Width="300px"></asp:ListBox>                </td>            </tr>            <tr>                <td colspan="2">                <table width="100%" class="style1">                <caption>SQL Server数据库连接设置</caption>                    <tr>                        <td>服务器</td>                        <td colspan="3">                            <asp:TextBox ID="txtServer" runat="server" Width="100%">MRLYF\MRLYF2005</asp:TextBox>                        </td>                    </tr>                    <tr>                        <td colspan="4">                            <asp:RadioButton ID="rdoWindows" runat="server" Text="Windows身份验证"                                 ValidationGroup="618" />                        </td>                    </tr>                    <tr>                        <td colspan="4">                            <asp:RadioButton ID="rdoSQLServer" runat="server" Text="SQL Server身份验证"                                 Checked="True" ValidationGroup="618" />                        </td>                    </tr>                    <tr>                        <td>用户名</td>                        <td>                            <asp:TextBox ID="txtUserName" runat="server">sa</asp:TextBox>                        </td>                        <td>密码</td>                        <td>                            <asp:TextBox ID="txtPassword" runat="server"></asp:TextBox>                        </td>                    </tr>                    <tr>                        <td>选择数据库</td>                        <td colspan="3">                            <asp:DropDownList ID="ddlDatabase" runat="server">                            </asp:DropDownList>                            <asp:Button ID="btnRefresh" runat="server" Text="刷新"                                 onclick="btnRefresh_Click" />                        </td>                    </tr>                </table>                </td>            </tr>            <tr>                <td colspan="2" align="center">                    <asp:Button ID="btnExport" runat="server" Text="导出"                         Width="80px" onclick="btnExport_Click" />                </td>            </tr>        </table>    </div>    </form></body></html>

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using Word = Microsoft.Office.Interop.Word;using System.Threading;using office = Microsoft.Office.Core;using System.Reflection;using System.IO;using System.Text.RegularExpressions;using System.Text;using System.Data;using System.Data.OleDb;using System.Data.SqlClient;public partial class _Default : System.Web.UI.Page {    protected void Page_Load(object sender, EventArgs e)    {        if (!IsPostBack)        {            BindListBox();        }    }    private void BindListBox()    {         ListBox1.Items.Clear();        string path = Server.MapPath("~/File");        string[] files = System.IO.Directory.GetFiles(path);        foreach (string f in files)        {             string fileName = System.IO.Path.GetFileName(f);            ListItem li = new ListItem(fileName,f);            ListBox1.Items.Add(li);        }    }    protected void btnRefresh_Click(object sender, EventArgs e)    {        //定义SQL语句        string P_str_Con = "Data Source=" + txtServer.Text + ";Database=master;Uid=" + txtUserName.Text + ";Pwd=" + txtPassword.Text + ";";        ddlDatabase.DataSource = GetTable(P_str_Con);//为下拉列表指定数据源        ddlDatabase.DataTextField = "name";//设置下拉列表中显示的字段名称        ddlDatabase.DataValueField = "name";//设置下拉列表中显示的值名称        ddlDatabase.DataBind();        if (ddlDatabase.Items.Count > 0)//如果下拉列表中有项            ddlDatabase.SelectedIndex = 0;//设置默认选择第一项    }    protected void btnExport_Click(object sender, EventArgs e)    {        string[] P_str_Names = new string[ListBox1.Items.Count];//存储所有选择的Excel文件名        for (int i = 0; i < ListBox1.Items.Count; i++)        {            P_str_Names[i] = ListBox1.Items[i].Value;        }        string P_str_Name = "";//存储遍历到的Excel文件名        List<string> P_list_SheetNames = new List<string>();//实例化泛型集合对象，用来存储工作表名称        for (int i = 0; i < P_str_Names.Length; i++)//遍历所有选择的Excel文件名        {            P_str_Name = P_str_Names[i];//记录遍历到的Excel文件名            P_list_SheetNames = GetSheetName(P_str_Name);//获取Excel文件中的所有工作表名            for (int j = 0; j < P_list_SheetNames.Count; j++)//遍历所有工作表            {                if (rdoWindows.Checked)//如果用Windows身份验证登录Sql Server                    ImportDataToSql(P_str_Name, P_list_SheetNames[j], "Data Source=" + txtServer.Text + ";Initial Catalog =" + ddlDatabase.SelectedValue + ";Integrated Security=SSPI;");//将工作表内容导出到Sql Server                else if (rdoSQLServer.Checked)//如果用Sql Server身份验证登录Sql Server                    ImportDataToSql(P_str_Name, P_list_SheetNames[j], "Data Source=" + txtServer.Text + ";Database=" + ddlDatabase.SelectedValue + ";Uid=" + txtUserName.Text + ";Pwd=" + txtPassword.Text + ";");//将工作表内容导出到Sql Server            }        }        Response.Write("<script>alert('已经将所有选择的Excel工作表导入到了Sql Server数据库中！');</script>");    }    private DataTable GetTable(string P_str_Sql)//获取指定服务器中的所有数据库    {        try        {            SqlConnection sqlcon = new SqlConnection(P_str_Sql);//实例化数据库连接对象            SqlDataAdapter sqlda = new SqlDataAdapter("select name from sysdatabases ", sqlcon);//实例化数据桥接器对象            DataTable DTable = new DataTable("sysdatabases");//实例化DataTable对象            sqlda.Fill(DTable);//填充DataTable数据表            return DTable;//返回DataTable数据表        }        catch        {            return null;//返回null        }    }    private List<string> GetSheetName(string P_str_Excel)//获取所有工作表名称    {        List<string> P_list_SheetName = new List<string>();//实例化泛型集合对象        //连接Excel数据库        OleDbConnection olecon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + P_str_Excel + ";Extended Properties=Excel 8.0");        olecon.Open();//打开数据库连接        System.Data.DataTable DTable = olecon.GetSchema("Tables");//实例化表对象        DataTableReader DTReader = new DataTableReader(DTable);//实例化表读取对象        while (DTReader.Read())//循环读取        {            string P_str_Name = DTReader["Table_Name"].ToString().Replace('$', ' ').Trim();//记录工作表名称            if (!P_list_SheetName.Contains(P_str_Name))//判断泛型集合中是否已经存在该工作表名称                P_list_SheetName.Add(P_str_Name);//将工作表名添加到泛型集合中        }        DTable = null;//清空表对象        DTReader = null;//清空表读取对象        olecon.Close();//关闭数据库连接        return P_list_SheetName;//返回得到的泛型集合    }    public void ImportDataToSql(string P_str_Excel, string P_str_SheetName, string P_str_SqlCon)//将工作表内容导出到Sql Server    {        DataSet myds = new DataSet();//实例化数据集对象        try        {            //获得全部数据                string P_str_OledbCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + P_str_Excel + ";Extended Properties=Excel 8.0;";            OleDbConnection oledbcon = new OleDbConnection(P_str_OledbCon);//实例化Oledb数据库连接对象            string P_str_ExcelSql = "";//定义变量，用来记录要执行的Excel查询语句            OleDbDataAdapter oledbda = null;//实例化Oledb数据桥接器对象            P_str_ExcelSql = string.Format("select * from [{0}$]", P_str_SheetName);//记录要执行的Excel查询语句            oledbda = new OleDbDataAdapter(P_str_ExcelSql, P_str_OledbCon);//使用数据桥接器执行Excel查询            oledbda.Fill(myds, P_str_SheetName);//填充数据            string P_str_CreateSql = string.Format("create table {0}(", P_str_SheetName);//定义变量，用来记录创建表的SQL语句            foreach (DataColumn c in myds.Tables[0].Columns)//遍历数据集中的所有行            {                P_str_CreateSql += string.Format("[{0}] text,", c.ColumnName);//在表中创建字段            }            P_str_CreateSql = P_str_CreateSql + ")";//完善创建表的SQL语句            using (SqlConnection sqlcon = new SqlConnection(P_str_SqlCon))//实例化SQL数据库连接对象            {                sqlcon.Open();//打开数据库连接                SqlCommand sqlcmd = sqlcon.CreateCommand();//实例化SqlCommand执行命令对象                sqlcmd.CommandText = P_str_CreateSql;//指定要执行的SQL语句                sqlcmd.ExecuteNonQuery();//执行操作                sqlcon.Close();//关闭数据连接            }            using (SqlBulkCopy bcp = new SqlBulkCopy(P_str_SqlCon))//用bcp导入数据             {                bcp.BatchSize = 100;//每次传输的行数                    bcp.DestinationTableName = P_str_SheetName;//定义目标表                    bcp.WriteToServer(myds.Tables[0]);//将数据写入Sql Server数据表            }        }        catch        {            Response.Write("<script>alert('Sql Server数据库中已经存在');</script>");        }    }}

0 0