ASP.NET Excel导入

最新推荐文章于 2021-01-26 10:52:28 发布

阿尔法狼

最新推荐文章于 2021-01-26 10:52:28 发布

阅读量479

点赞数

分类专栏： ASP.NET 文章标签： excel asp.net string upload file server

本文链接：https://blog.csdn.net/namespacefeng/article/details/7175852

版权

ASP.NET 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

前台：
<table width="100%" bgcolor="#71B1F8" border="0" cellpadding="0" cellspacing="1"
            style="border-style: double; border-width: 5px;">
            <tr>
                <td height="25" width="15%" background="../images/kuang_11.gif" bgcolor="#F7F7FF"
                    style="padding-left: 5px">
                    <strong>Excel文件：</strong>
                </td>
                <td background="../images/kuang_11.gif" width="75%" style="text-align: left" bgcolor="#F7F7FF">
                    <asp:FileUpload ID="fuInsertDistribute" runat="server" Width="400px" />
                </td>
            </tr>
            <tr>
                <td background="../images/kuang_11.gif" bgcolor="#F7F7FF" colspan="2" align="right">
                    <asp:ImageButton ID="ImageButton1" ImageUrl="~/images/SignBtn/ok.gif" runat="server"
                        OnClick="ImageButton1_Click" style="height: 23px" />
                    <%--<asp:Image ID="imgOk" runat="server" Style="cursor: hand; border: 0" ImageUrl="~/images/SignBtn/ok.gif"
                        οnclick="InExcel()" />--%>
                </td>
            </tr>
        </table>
后台：

    protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
    {
        if (fuInsertDistribute.PostedFile.FileName == "")
        {
            return;
        }
        //   /获取文件信息
        string FileName = fuInsertDistribute.PostedFile.FileName;
        string file_str = "文件名称：" + FileName + "<br>";
        file_str = "文件类型：" + fuInsertDistribute.PostedFile.ContentType + "<br>";
        file_str = "文件长度：" + fuInsertDistribute.PostedFile.ContentLength.ToString() + "KB<br>";
        DirectoryInfo upDir = new DirectoryInfo(Server.MapPath("../JS"));
        if (!upDir.Exists)
        {
            upDir.Create();
        }
        //上传文件到服务器
        FileName = FileName.Substring(FileName.LastIndexOf("\\") + 1);// 取出文件名的路径（不包括文件的名称）
        string endFileName = DateTime.Now.ToString("yyyyMMddHHmmss") + "_" + FileName;
        string upload_file = Server.MapPath("../JS/") + endFileName;
        //取出服务器虚拟路径,存储上传文件
        fuInsertDistribute.PostedFile.SaveAs(upload_file);//开始上传文件
        string docmentName = FileName;
        string updateTime = DateTime.Now.ToString();

string msg = "";

DataTable dt = ReadExcelData(upload_file, out msg);

}

private static DataTable ReadExcelData(string fileFullName, out string ErrorMassage)
    {
        string excelConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\"";
        string excelSqlStr = "select * from [{0}]";
        OleDbConnection oleCn = new OleDbConnection();
        try
        {
            string currConnStr = string.Format(excelConnStr, fileFullName);//连接字符串

            oleCn.ConnectionString = currConnStr;
            DataTable dt = new DataTable();
            oleCn.Open();
            string excelBookName = oleCn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null).Rows[0][2].ToString();
            if (excelBookName != string.Empty && excelBookName.Trim() != "")
            {
                string currSqlStr = string.Format(excelSqlStr, excelBookName);//sql字符串

                OleDbCommand oleCmd = new OleDbCommand(currSqlStr, oleCn);
                OleDbDataAdapter oleDda = new OleDbDataAdapter(oleCmd);
                oleDda.Fill(dt);
            }
            oleCn.Close();
            ErrorMassage = string.Empty;
            return dt;
        }
        catch (Exception ex)
        {
            string errorMsg = "Excel文件格式不对,请尝试用excel打开该文件检查内容并另存一下,确保成为正确的excel文件,再重新上传";
            ErrorMassage = errorMsg + " " + ex.Message;
            oleCn.Close();
            return null;
        }
    }