读取 Excel 数据并保存到服务器数据库

来源：互联网发布：医药行业erp软件编辑：程序博客网时间：2024/05/16 05:03

1、前台

<asp:FileUpload ID="flChengChe" runat="server" Width="400px"/> 
<asp:Button ID="btnImport" runat="server" Text="导入数据" onclick="btnImport_Click" />

2、后台

private DataTable GetExcelTabel()
        {
            string sArgs;
            string filePath = "";
            string fileExtend = "";//文件扩展名
            int fileSize = 0;//文件大小

filePath = this.flChengChe.PostedFile.FileName.ToLower().Trim();

            //取得上传前的文件(存在于客户端)的文件或文件夹的名称,组成数组.例如:C:aaaa.txt,那么Names.lenth就为3
            string[] names = filePath.Split('\\');
            //取得文件名
            string name = names[names.Length - 1];
            string serverPath = this.Server.MapPath("\\");//获得服务器端的根目录

            //判断是否有该目录
            if (!Directory.Exists(serverPath + "Excelfile"))
            {
                Directory.CreateDirectory(serverPath + "Excelfile");
                serverPath = serverPath + "Excelfile";
            }
            else
            {
                serverPath = serverPath + "Excelfile";
            }
            filePath = serverPath + "\\" + name;

this.flChengChe.PostedFile.SaveAs(filePath);

            //得到文件的大小
            fileSize = this.flChengChe.PostedFile.ContentLength;
            //得到扩展名
            fileExtend = filePath.Substring(filePath.IndexOf("."));
            if (fileSize == 0)
            {
                sArgs = @" <script language=javascript>window.alert( '找不到该文件！' ); </script>";
                Page.Response.Write(sArgs);
                return null;
            }
            if (fileExtend != ".xls")
            {
                sArgs = @" <script language=javascript>window.alert( '请确认您所导入的文件是否EXCEL文件！！' ); </script>";
                Page.Response.Write(sArgs);
                return null;
            }
            DataSet ds = new DataSet();

            try
            {
                OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + filePath);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                else
                {
                    sArgs = @" <script language=javascript>window.alert( 'EXCEL文件正在使用！' ); </script>";
                    Page.Response.Write(sArgs);
                    return null;
                }
                OleDbCommand com = conn.CreateCommand();
                com.CommandText = "select * from [sheet1$]";
                com.CommandType = CommandType.Text;
                OleDbDataAdapter adapter = new OleDbDataAdapter(com);
                adapter.Fill(ds);
                adapter.Dispose();
                conn.Close();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

return ds.Tables[0];
}