<%@ 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="刷新"
οnclick="btnRefresh_Click" />
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<asp:Button ID="btnExport" runat="server" Text="导出"
Width="80px" οnclick="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>");
}
}
}