Silverlight将Excel导入到SQLserver数据库

最近纠结于读取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="" Height="">

    <Grid x:Name="LayoutRoot" Background="White">

        <ListBox Name="listBox1" HorizontalAlignment="Right" VerticalAlignment="Center" Width="" Height="" Margin="0,9,11,47">

        </ListBox>

        <Button x:Name="UploadButton" Content="确认上传" Click="UploadButton_Click" Width="" Height="" HorizontalAlignment="Right" Margin="0,37,12,18" />

        <Button x:Name="OpenButton" Content="选择本地Excel文件" Click="OpenButton_Click" Width="" Height="" 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[];

            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;

            }

        }

    }

}

注意事项，参考下图：

如此，可以将Excel中的数据写入SQLserver数据库中，经测试，可行，附上代码，仅供参考！

附：Excel导入SQLserver示例下载

巴特西

Silverlight将Excel导入到SQLserver数据库

最新文章

热门文章