asp.net - 该进程无法访问 file.xlsx'，因为它正被另一个进程使用。将数据从 ASPX 导出到 EXCEL 文件时

Question

当我尝试导出时，我得到一个异常，它说“进程无法访问文件“Mypath\Filename.xlsx”，因为它正被另一个进程使用”当我尝试导出时抛出异常，它被追溯到Line byte[] content = File.ReadAllBytes(fileName); 所以我不明白这个问题。我没有使用文件流来读取数据

这是我使用的代码

protected void BtnExport_Click(object sender, EventArgs e)
    {
        using (MydatabaseEntities dc = new MydatabaseEntities())
        {
            List<EmployeeMaster> emList = dc.EmployeeMasters.ToList();
            StringBuilder sb = new StringBuilder();
            if (emList.Count > 0)
            {
                string fileName = Path.Combine(Server.MapPath("~/ImportDocument"), DateTime.Now.ToString("ddMMyyyyhhmmss") + ".xlsx");
                string conString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + fileName + "; Extended Properties='Excel 12.0;HDR=yes'";
                using (OleDbConnection con = new OleDbConnection(conString))
                {
                    string strCreateTab = "Create table EmployeeData(" +
                    "[Employee ID] varchar(50)," +
                    "[Company Name] varchar(200)," +
                    "[Contact Name] varchar(200)," +
                    "[Contact Title] varchar(200)," +
                    "[Employee Address] varchar(200)," +
                    "[Postal Code] varchar(50))";
                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }
                    OleDbCommand cmd = new OleDbCommand(strCreateTab, con);
                    cmd.ExecuteNonQuery();
                    string StrInsert = "Insert into EmployeeData([Employee ID],[Company Name]," + "[Contact Name],[Contact Title],[Employee Address], [Postal Code]" +
                    ")values(?,?,?,?,?,?)";
                    OleDbCommand cmdIns = new OleDbCommand(StrInsert, con);
                    cmdIns.Parameters.Add("?", OleDbType.VarChar, 50);
                    cmdIns.Parameters.Add("?", OleDbType.VarChar, 200);
                    cmdIns.Parameters.Add("?", OleDbType.VarChar, 200);
                    cmdIns.Parameters.Add("?", OleDbType.VarChar, 200);
                    cmdIns.Parameters.Add("?", OleDbType.VarChar, 200);
                    cmdIns.Parameters.Add("?", OleDbType.VarChar, 50);
                    foreach (var i in emList)
                    {
                        cmdIns.Parameters[0].Value = i.EmployeeId;
                        cmdIns.Parameters[1].Value = i.CompanyName;
                        cmdIns.Parameters[2].Value = i.ContactName;
                        cmdIns.Parameters[3].Value = i.ContactTitle;
                        cmdIns.Parameters[4].Value = i.EmployeeAddress;
                        cmdIns.Parameters[5].Value = i.PostalCode;
                        cmdIns.ExecuteNonQuery();
                    }
                    //create Downloadable File
                    byte[] content = File.ReadAllBytes(fileName);
                    HttpContext context = HttpContext.Current;
                    context.Response.BinaryWrite(content);
                    context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    context.Response.AppendHeader("Content-Disposition", "attachment; filename=EmployeeData.xlsx");
                    context.Response.End();
                    con.Close();
                }
            }
        }
    }
}

所以请帮助我

标签： asp.net

Answer 1

我真的很高兴能够通过我尝试的一个简单技巧来解决我的问题。我得到的错误是文件已经打开，是由我在创建可下载文件之前没有关闭的连接引起的！！这是我在粗体中所做的更改。:-)

protected void BtnExport_Click(object sender, EventArgs e)
    {
        using (MydatabaseEntities dc = new MydatabaseEntities())
        {
            List<EmployeeMaster> emList = dc.EmployeeMasters.ToList();
            StringBuilder sb = new StringBuilder();
            if (emList.Count > 0)
            {
                string fileName = Path.Combine(Server.MapPath("~/ImportDocument"), DateTime.Now.ToString("ddMMyyyyhhmmss") + ".xlsx");
                string conString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + fileName + "; Extended Properties='Excel 12.0;HDR=yes'";
                using (OleDbConnection con = new OleDbConnection(conString))
                {
                    string strCreateTab = "Create table EmployeeData(" +
                    "[Employee ID] varchar(50)," +
                    "[Company Name] varchar(200)," +
                    "[Contact Name] varchar(200)," +
                    "[Contact Title] varchar(200)," +
                    "[Employee Address] varchar(200)," +
                    "[Postal Code] varchar(50))";
                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }
                    OleDbCommand cmd = new OleDbCommand(strCreateTab, con);
                    cmd.ExecuteNonQuery();
                    string StrInsert = "Insert into EmployeeData([Employee ID],[Company Name]," + "[Contact Name],[Contact Title],[Employee Address], [Postal Code]" +
                    ")values(?,?,?,?,?,?)";
                    OleDbCommand cmdIns = new OleDbCommand(StrInsert, con);
                    cmdIns.Parameters.Add("?", OleDbType.VarChar, 50);
                    cmdIns.Parameters.Add("?", OleDbType.VarChar, 200);
                    cmdIns.Parameters.Add("?", OleDbType.VarChar, 200);
                    cmdIns.Parameters.Add("?", OleDbType.VarChar, 200);
                    cmdIns.Parameters.Add("?", OleDbType.VarChar, 200);
                    cmdIns.Parameters.Add("?", OleDbType.VarChar, 50);
                    foreach (var i in emList)
                    {
                        cmdIns.Parameters[0].Value = i.EmployeeId;
                        cmdIns.Parameters[1].Value = i.CompanyName;
                        cmdIns.Parameters[2].Value = i.ContactName;
                        cmdIns.Parameters[3].Value = i.ContactTitle;
                        cmdIns.Parameters[4].Value = i.EmployeeAddress;
                        cmdIns.Parameters[5].Value = i.PostalCode;
                        cmdIns.ExecuteNonQuery();
                    }
                    //create Downloadable File
                    **con.Close();**
                    byte[] content = File.ReadAllBytes(fileName);
                    HttpContext context = HttpContext.Current;
                    context.Response.BinaryWrite(content);
                    context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    context.Response.AppendHeader("Content-Disposition", "attachment; filename=EmployeeData.xlsx");
                    context.Response.End();

                }
            }
        }
    }
}