导入Excel文件

Default.aspx

<p>Excel文件：<asp:FileUpload ID="fupExcel" runat="server" /></p> 
    <p>工作表名称：<asp:TextBox ID="txtSheetName" runat="server"></asp:TextBox></p>
    <p><asp:Button ID="btnExcel" runat="server" Text="导入Excel" onclick="btnExcel_Click" /></p>
    <asp:GridView ID="gvwData" runat="server" Width="100%">
    </asp:GridView>

Default.aspx.cs

        //上传Excel
        protected void btnExcel_Click(object sender, EventArgs e)
        {
            string fileSavePath = "";
            if (uploadExcel(fupExcel, out fileSavePath))
            {
                gvwData.DataSource = importExcelToDT(fileSavePath, txtSheetName.Text);
                gvwData.DataBind();
            }
        }
        //读取Excel
        private DataSet importExcelToDT(string FilePath, string sheetName)
        {
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath + ";Extended Properties='Excel 8.0; IMEX=1'";
            OleDbConnection conn = new OleDbConnection(strConn);
            OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [" + sheetName + "$]", strConn);
            DataSet myDataSet = new DataSet();
            try
            {
                myCommand.Fill(myDataSet);
                // 取出数据后删除文件
                // 删除已存在的文件
                if (File.Exists(FilePath))
                {
                    File.Delete(FilePath);
                }
            }
            catch
            {
                conn.Close();
            }
            return myDataSet;
        }

        //导入Excel 
        private bool uploadExcel(FileUpload fupName, out string fileSavePath)
        {
            if (fupName.PostedFile.FileName != "")
            {
                string fileContentType = fupName.PostedFile.ContentType;    //获取MIME类型
                if (fileContentType == "application/vnd.ms-excel")
                {
                    //定义上传路径(在当前目录下的uploadfile文件下）
                    string uploadpath = Server.MapPath("uploadfile");
                    //取得文件名
                    string tmpfilename = fupName.PostedFile.FileName;
                    //文件名
                    string filename = tmpfilename.Substring(tmpfilename.LastIndexOf("\\") + 1);
                    //求取后缀名
                    string suffix = filename.Substring(filename.LastIndexOf("."));
                    //重命名：DateTime
                    Random ro = new Random();
                    filename = string.Format("{0}{1}{2}", DateTime.Now.ToString("yyyyMMddHHmmssff"), ro.Next(1000, 9999), suffix);
                    //重命名：GUID（全球唯一标识符）推荐！！！
                    //filename = string.Format("{0}{1}", Guid.NewGuid().ToString("N"), suffix);
                    //原文件的保存路径
                    fileSavePath = uploadpath + "\\" + filename;
                    //保存文件            
                    fupName.SaveAs(fileSavePath);
                    return true;
                }
                else
                {
                    fileSavePath = "";
                    Response.Write("系统暂时只支持.xls格式文件");
                    return false;
                }
            }  
            else
            {
                fileSavePath = "";
                Response.Write("请选择导入文件");
                return false;
            }
        }      

 

转载于:https://www.cnblogs.com/linyongqin/articles/3919098.html