最近纠结于读取Excel模板数据，将数据导入SQLServer的Silverlight实现，本文将实现代码贴出，作为一个简单的例子，方便各位：
1.先设计前台界面新建Silverlight5.0应用程序，出现MainPage.xaml，代码如下所示：
<UserControl x:Class="Excel导入SQLServer数据库.MainPage" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:d="http://schemas.microsoft.com/expression/blend/2008" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="d" Width="400" Height="117"> <Grid x:Name="LayoutRoot" Background="White"> <ListBox Name="listBox1" HorizontalAlignment="Right" VerticalAlignment="Center" Width="239" Height="23" Margin="0,9,11,47"> </ListBox> <Button x:Name="UploadButton" Content="确认上传" Click="UploadButton_Click" Width="75" Height="23" HorizontalAlignment="Right" Margin="0,37,12,18" /> <Button x:Name="OpenButton" Content="选择本地Excel文件" Click="OpenButton_Click" Width="116" Height="23" HorizontalAlignment="Right" Margin="0,8,258,47" /> </Grid> </UserControl>
其效果图，如下所示：
其后台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[4096]; int fileDataToRead; while ((fileDataToRead = inputFile.Read(fileData, 0, fileData.Length)) != 0) { resultFile.Write(fileData, 0, 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[4096]; int bytesToBeRead; while ((bytesToBeRead = context.Request.InputStream.Read(bufferData, 0, bufferData.Length)) != 0) { fileStream.Write(bufferData, 0, 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[0].Rows[0][0].ToString(); string xingming = myDs.Tables[0].Rows[0][1].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; } } } }
注意事项，参考下图：
如此，可以将Excel中的数据写入SQLserver数据库中，经测试，可行，附上代码，仅供参考！