Silverlight将Excel导入到SQLserver数据库

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

最近纠结于读取Excel模板数据，将数据导入SQLServer的Silverlight实现，本文将实现代码贴出，作为一个简单的例子，方便各位：

1.先设计前台界面新建Silverlight5.0应用程序，出现MainPage.xaml，代码如下所示：

其效果图，如下所示：

其后台MainPage.xaml.cs代码,如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using System.IO;namespace Excel导入SQLServer数据库
{
public partial class MainPage : UserControl
{
//在此先定义一个List;
List<FileInfo> filesToUpload;
public MainPage()
{
InitializeComponent();
} /// <summary>
/// 确认上传
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void UploadButton_Click(object sender, RoutedEventArgs e)
{
try
{
if (filesToUpload == null)
{
return;
}
foreach (FileInfo file in filesToUpload)
{
//Define the Url object for the Handler
UriBuilder handlerUrl = new UriBuilder("http://localhost:5952/UploadFileHandler.ashx");//自己的端口
//Set the QueryString
handlerUrl.Query = "InputFile=" + file.Name;
FileStream FsInputFile = file.OpenRead();
//Define the WebClient for Uploading the Data
WebClient webClient = new WebClient();
//Now make an async class for writing the file to the server
//Here I am using Lambda Expression
webClient.OpenWriteCompleted += (s, evt) =>
{
UploadFileData(FsInputFile, evt.Result);
evt.Result.Close();
FsInputFile.Close();
MessageBox.Show("上传成功！");
listBox1.ItemsSource = ""; };
webClient.OpenWriteAsync(handlerUrl.Uri);
} }
catch (System.Exception)
{ throw;
}
} private void UploadFileData(Stream inputFile, Stream resultFile)
{
byte[] fileData = new byte[];
int fileDataToRead;
while ((fileDataToRead = inputFile.Read(fileData, , fileData.Length)) != )
{
resultFile.Write(fileData, , fileDataToRead);
}
} /// <summary>
/// 打开文件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void OpenButton_Click(object sender, RoutedEventArgs e)
{
try
{
OpenFileDialog fileDialog = new OpenFileDialog();
fileDialog.Multiselect = false;
//这里只写了Excel有关格式，可以根据需要添加其他格式
fileDialog.Filter = "Excel Files(*.xls,*.xlsx)|*.xls";
bool? result = fileDialog.ShowDialog();
if (result != null)
{
if (result == true)
{
filesToUpload = fileDialog.Files.ToList();
listBox1.ItemsSource = filesToUpload;
}
else
return;
}
}
catch (System.Exception)
{ throw;
}
}
}
}

注意：将其中的端口号跟自己的程序相对应，如下图：

接着，在.web目录下，新建FilesServer文件夹，和UploadFileHandler.ashx的一般处理程序，如下图：

其中，UploadFileHandler.ashx.cs中内容如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using System.IO;namespace Excel导入SQLServer数据库.Web
{
/// <summary>
/// UploadFileHandler 的摘要说明
/// </summary>
public class UploadFileHandler : IHttpHandler
{ public void ProcessRequest(HttpContext context)
{
try
{
string filename = context.Request.QueryString["InputFile"].ToString();
using (FileStream fileStream = File.Create(context.Server.MapPath("~/FilesServer/" + filename)))
{
byte[] bufferData = new byte[];
int bytesToBeRead;
while ((bytesToBeRead = context.Request.InputStream.Read(bufferData, , bufferData.Length)) != )
{
fileStream.Write(bufferData, , bytesToBeRead);
}
fileStream.Close(); }
//===========用于对上传的EXCEL文件插入到SQL数据库中=============== string strPath = context.Server.MapPath("~/FilesServer/" + filename);
//string mystring = "Provider = Microsoft.Jet.OleDb.4.0 ; Data Source = '" + strPath + "';Extended Properties=Excel 8.0";//之前版本链接格式
string mystring = "Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source = '" + strPath + "';Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";//office2010链接格式
OleDbConnection cnnxls = new OleDbConnection(mystring);
if (cnnxls.State == ConnectionState.Closed)
{
cnnxls.Open();
}
OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [Sheet1$]", cnnxls);
DataSet myDs = new DataSet();
myDa.Fill(myDs);
string ConnStr = "Data Source=WIN-FKM3JDGK01I\\MYSQLR2;Initial Catalog=CDDB;Persist Security Info=True;User ID=sa;Password=123456";
SqlConnection MyConn = new SqlConnection(ConnStr);
MyConn.Open();
//读取Excel中的数据
string xuehao = myDs.Tables[].Rows[][].ToString();
string xingming = myDs.Tables[].Rows[][].ToString();
string strSQL = "insert into CDDB.dbo.Student(XUEHAO,NAME) values ('" + xuehao + "','" + xingming + "')";
SqlCommand myComm1 = new SqlCommand(strSQL, MyConn);
myComm1.ExecuteNonQuery();
MyConn.Close();
cnnxls.Close();
}
catch (Exception)
{ throw;
} } public bool IsReusable
{
get
{
return false;
}
}
}
}