问题描述
导入excel文件时我收到以下错误。
'/ Test / APMS'应用程序中的服务器错误。
---------------------------------------------- ----------------------------------
Microsoft Office Excel无法访问该文件' C：\inetpub\wwwroot\Test\APMS\AdaptivePortfolio\Attachments\Excel2SqlRead\ChecklistsDetails.xls'。有几种可能的原因：
•文件名或路径不存在。
•该文件正由另一个程序使用。
•您尝试保存的工作簿与当前打开的工作簿具有相同的名称。
描述：执行当前Web请求期间发生了未处理的异常。请查看堆栈跟踪以获取有关错误及其源自代码的位置的更多信息。
异常详细信息：System.Runtime.InteropServices.COMException：Microsoft Office Excel中无法访问该文件C：\inetpub\wwwroot\Test\APMS\AdaptivePortfolio\Attachments\Excel2SqlRead \ChecklistsDetails.xls'。有几种可能的原因：
•文件名或路径不存在。
•该文件正由另一个程序使用。
•您尝试保存的工作簿与当前打开的工作簿具有相同的名称。
源错误：
在执行当前Web请求期间生成了未处理的异常。可以使用下面的异常堆栈跟踪来识别有关异常的起源和位置的信息。
堆栈跟踪：
[COMException（0x800a03ec）：Microsoft Office Excel无法访问文件'C：\inetpub \ www.root \Test \ APMS\AdaptivePortfolio\Attachments\Excel2SqlRead\ChecklistsDetails.xls'。有几种可能的原因：
•文件名或路径不存在。
•该文件正由另一个程序使用。
•您尝试保存的工作簿与当前打开的工作簿具有相同的名称。]
Microsoft.Office.Interop.Excel.Workbooks.Open（String Filename，Object UpdateLinks，Object ReadOnly，Object Format ，对象密码，对象WriteResPassword，Object IgnoreReadOnlyRecommended，Object Origin，Object Delimiter，Object Editable，Object Notify，Object Converter，Object AddToMru，Object Local，Object CorruptLoad）+0
PrjMgmtComps.ObjectFactory.Excel2Sql.Excel2SqlChecklistDetails（String FileName ，Int58 LoginID）在D：\ kamalsTFSApp \AdaptiveComponents \ ADP \PrjMgmtComps \ObjectFactory \ Excel2Sql.cs：7348
ADPSolutions.Excel2Sql.btnUpload_ServerClick（Object sender，EventArgs e）+3790
System.Web.UI.WebControls.Button.OnClick（EventArgs e）+9752490
System.Web.UI.WebControls.Button.RaisePostBackEvent（String eventArgument）+196
System.Web.UI.WebControls .Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent（String eventArgument）+10
System.Web.UI.Page.RaisePostBackEvent（IPostBackEventHandler sourceControl，String eventArgument）+13
System.Web.UI.Page.RaisePostBackEvent（NameValueCollection postData）+35
System.Web.UI。 Page.ProcessRequestMain（布尔includeStagesBeforeAsyncPoint，布尔includeStagesAfterAsyncPoint）+1724
---------------------- -------------------------------------------------- --------
版本信息：Microsoft .NET Framework版本：4.0.30319; ASP.NET版本：4.0.30319.18446
我的代码在这里---
'pre>＃地区清单详细
＃地区Excel2SqlChecklistDetails
公共字符串Excel2SqlChecklistDetails（字符串文件名，登录ID INT）//，INT实体）
$ { b $ b string Message =;
//读取清单工作表中的详细信息
CDataManager.CDataManager objchklistdetails = new PrjMgmtComps.CDataManager.CDataManager（）;
DataSet dsChecklistDetails = new DataSet（）;
dsChecklistDetails = objchklistdetails.GetChecklistName（）;
DataTable dtChecklistDetails = new DataTable（）;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application（）;
Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Open（ConfigurationSettings.AppSettings [FilePath] +// Excel2SqlRead //+ FileName，Missing.Value，Missing.Value，Missing。 Value，Missing.Value，Missing.Value，Missing.Value，Missing.Value，Missing.Value，Missing.Value，Missing.Value，Missing.Value，Missing.Value，Missing.Value，Missing.Value）;
//字典<字符串，文件名> dict = new Dictionary< string，FileName>（）;
StringBuilder sb = new StringBuilder（）;
foreach（wb.Worksheets中的Microsoft.Office.Interop.Excel.Worksheet工作表）
{
sb.Append（worksheet.Name.ToString（）+，） ;
}
foreach（DataRow dr in dsChecklistDetails.Tables [0] .Rows）
{
if（sb.ToString（）。Contains（dr [Name ] .ToString（）+，））
{
dtChecklistDetails = ReadExcelDataForChecklistDetails（FileName，dr [Name]。ToString（））;
string ChecklistId =;
ChecklistId = dr [ID]。ToString（）;
if（dtChecklistDetails.Rows.Count> 0）
{
Message = checkChecklistDetailsCol（dtChecklistDetails）;
if（Message ==）
{
Message = checkRequiredRowsNUllOrNotForChklistDetails（dtChecklistDetails）;
if（Message ==）
{
try
{
Message = SaveChecklistDetails（dtChecklistDetails，LoginID ，ChecklistId）;
}
catch（SqlException ex）
{
Message = ex.Message;
}
}
}
}
其他
{
消息=无法从中读取数据Excel，预期工作表名称：+ dr [Name]。ToString（）;
}
}
}
wb.Close（false，Type.Missing，Type.Missing）;
excel.Quit（）;
GC.Collect（）;
Marshal.FinalReleaseComObject（wb）;
返回消息;
//Marshal.FinalReleaseComObject(wb);
}
#endregion
#region SaveChecklistDetails
private string SaveChecklistDetails（ DataTable dtExcelData，int LoginID，string ChecklistId）
{
string Message =;
CDataManager.CDataManager _Obj = new PrjMgmtComps.CDataManager.CDataManager（）;
DataSet _dsDBData = _Obj.GetChecklistsDetailsFromDB（ChecklistId）;
_dsDBData.EnforceConstraints = true;
dtExcelData.Columns.Add（PrimaryKeyID，typeof（int））;
if（_dsDBData.Tables [0] .Rows.Count> 0）
{
dtExcelData = UpdateExistingChecklistDetails（dtExcelData，_dsDBData.Tables [0]，ChecklistId）;
}
int [] lIntResult = {0};
if（Message ==）
{
_dsDBData.Tables [0] .BeginLoadData（）;
int _cnt = 0;
for（int lIntCount = 0;
lIntCount< dtExcelData.Rows.Count;
lIntCount ++）
{
object [] lObjCreate = new object [7];
if（dtExcelData.Rows [lIntCount] [PrimaryKeyID]！= System.DBNull.Value）
{
lObjCreate [0] = dtExcelData.Rows [lIntCount] [PrimaryKeyID] ;
}
其他
{
lObjCreate [0] = --_ cnt;
}
如果（dtExcelData.Rows [lIntCount] [ 子清单]。的ToString（）！= ）
{
lObjCreate [1] = dtExcelData.Rows [ lIntCount] [Child Checklists]。ToString（）;
}
else
{
lObjCreate [1] = System.DBNull.Value;
}
if（dtExcelData.Rows [lIntCount] [Description]。ToString（）！=）
{
lObjCreate [2] = dtExcelData.Rows [lIntCount ] [ 描述]的ToString（）;
}
else
{
lObjCreate [2] = System.DBNull.Value;
}
if（dtExcelData.Rows [lIntCount] [要寻找的东西] .ToString（）！=）
{
lObjCreate [3] = dtExcelData。行[lIntCount] [要寻找的东西]。ToString（）;
}
else
{
lObjCreate [3] = System.DBNull.Value;
}
if（dtExcelData.Rows [lIntCount] [Weightage]。ToString（）！=）
{
lObjCreate [4] = dtExcelData.Rows [lIntCount ] [ 权重]的ToString（）;
}
else
{
lObjCreate [4] = System.DBNull.Value;
}
if（dtExcelData.Rows [lIntCount] [Remarks]。ToString（）！=）
{
lObjCreate [5] = dtExcelData.Rows [lIntCount ] [ 备注]的ToString（）;
}
else
{
lObjCreate [5] = System.DBNull.Value;
}
if（ChecklistId！=）
{
lObjCreate [6] = ChecklistId;
}
else
{
lObjCreate [6] = System.DBNull.Value;
}
// lObjCreate [16] = EntityID;
_dsDBData.Tables [0] .LoadDataRow（lObjCreate，false）;
}
_dsDBData.Tables [0] .EndLoadData（）;
lIntResult = _Obj.SetExcelChecklistDetails（_dsDBData）;
}
返回消息;
}
#endregion
#region UpdateExistingChecklistDetails
private DataTable UpdateExistingChecklistDetails（DataTable dtExcelData，DataTable dtDbData，string ChecklistId ）
{
foreach（dtExcelData.Rows中的DataRow drExcel）
{
foreach（dtDbData.Rows中的DataRow drDb）
{
if（（drDb） [DashBoard_ID]。ToString（）== ChecklistId）&&（drExcel [Child Checklists]。ToString（）== drDb [Name]。ToString（）））
{
drExcel [PrimaryKeyID] = drDb [ID];
休息;
}
}
}
返回dtExcelData;
}
#endregion
//由kamal添加以检查要求字段是否填写？
＃地区checkRequiredRowsNUllOrNotForChklistDetails
公共字符串checkRequiredRowsNUllOrNotForChklistDetails（数据表dtChecklistDetails）
{
串消息= ;
for（int i = 0; i< dtChecklistDetails.Rows.Count; i ++）
{
if（dtChecklistDetails.Rows [i ] [Child Checklists] == System.DBNull.Value）
{
Message + =（1 + i）+Child Checklists+，;
}
}
if（Message！=）
{
Message =以下字段在Excel中为空+：+信息;
}
返回消息;
}
#endregion
＃地区checkChecklistDetailsCol
私人字符串checkChecklistDetailsCol（数据表dtTask）
{
string Message =;
if（！dtTask.Columns.Contains（Id））
{
Message + =Id，;
}
if（！dtTask.Columns.Contains（Child Checklists））
{
Message + =Child Checklists，;
}
if（！dtTask.Columns.Contains（Description））
{
Message + =Description，;
}
if（！dtTask.Columns.Contains（要寻找的东西））
{
消息+ =要寻找的东西;
}
if（！dtTask.Columns.Contains（Weightage））
{
Message + =Weightage，;
}
if（！dtTask.Columns.Contains（Remarks））
{
Message + =Remarks，;
}
if（Message！=）
{
Message =在上传的Excel工作表中缺少以下列：+消息;
}
返回消息;
}
#endregion
＃地区ReadExcelDataForChecklistDetails
公共静态数据表ReadExcelDataForChecklistDetails（字符串文件名，字符串表）
{
string strConn =Provider = Microsoft.ACE.OLEDB.12.0; Data Source =+
ConfigurationSettings.AppSettings [FilePath] +// Excel2SqlRead //+ FileName +
;扩展属性= Excel 12.0;;
OleDbDataAdapter da = new OleDbDataAdapter（SELECT * FROM [+ WorkSheet +$]，strConn）;
DataTable dtExceldata = new DataTable（）;
da.Fill（dtExceldata）;
//File.Delete(ConfigurationSettings.AppSettings[\"FilePath] +// Excel2SqlRead //+ FileName）;
//da.Dispose（）;
//GC.SuppressFinalize（da）;
for（int r = 0; r< dtExceldata.Rows.Count; r ++）
{
bool delrow = true;
for（int i = 0; i< dtExceldata.Rows.Count; i ++）
{
if（dtExceldata.Rows [r] [i] .ToString（）== ）
{
delrow = false;
休息;
}
}
if（delrow == false）
{
dtExceldata.Rows.RemoveAt（r）;
r--;
}
//返回dtExceldata;
}
//dtExceldata.Rows.RemoveAt(5）;
返回dtExceldata;
}
#endregion
#endregion
Hi, While importing excel file i am getting this below error. Server Error in '/Test/APMS' Application. -------------------------------------------------------------------------------- Microsoft Office Excel cannot access the file 'C:\inetpub\wwwroot\Test\APMS\AdaptivePortfolio\Attachments\Excel2SqlRead\ChecklistsDetails.xls'. There are several possible reasons: • The file name or path does not exist. • The file is being used by another program. • The workbook you are trying to save has the same name as a currently open workbook. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Runtime.InteropServices.COMException: Microsoft Office Excel cannot access the file 'C:\inetpub\wwwroot\Test\APMS\AdaptivePortfolio\Attachments\Excel2SqlRead\ChecklistsDetails.xls'. There are several possible reasons: • The file name or path does not exist. • The file is being used by another program. • The workbook you are trying to save has the same name as a currently open workbook. Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [COMException (0x800a03ec): Microsoft Office Excel cannot access the file 'C:\inetpub\wwwroot\Test\APMS\AdaptivePortfolio\Attachments\Excel2SqlRead\ChecklistsDetails.xls'. There are several possible reasons: • The file name or path does not exist. • The file is being used by another program. • The workbook you are trying to save has the same name as a currently open workbook.] Microsoft.Office.Interop.Excel.Workbooks.Open(String Filename, Object UpdateLinks, Object ReadOnly, Object Format, Object Password, Object WriteResPassword, Object IgnoreReadOnlyRecommended, Object Origin, Object Delimiter, Object Editable, Object Notify, Object Converter, Object AddToMru, Object Local, Object CorruptLoad) +0 PrjMgmtComps.ObjectFactory.Excel2Sql.Excel2SqlChecklistDetails(String FileName, Int32 LoginID) in D:\kamalsTFSApp\AdaptiveComponents\ADP\PrjMgmtComps\ObjectFactory\Excel2Sql.cs:7348 ADPSolutions.Excel2Sql.btnUpload_ServerClick(Object sender, EventArgs e) +3790 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9752490 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +196 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +35 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1724 -------------------------------------------------------------------------------- Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.18446
My Code Here---
#region Checklist Details
#region Excel2SqlChecklistDetails
public string Excel2SqlChecklistDetails(string FileName, int LoginID) //, int Entity)
{
string Message = "";
// Read checklist Details from worksheet
CDataManager.CDataManager objchklistdetails = new PrjMgmtComps.CDataManager.CDataManager();
DataSet dsChecklistDetails = new DataSet();
dsChecklistDetails = objchklistdetails.GetChecklistName();
DataTable dtChecklistDetails = new DataTable();
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Open(ConfigurationSettings.AppSettings["FilePath"] + "//Excel2SqlRead//" + FileName , Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
// Dictionary<string, FileName> dict = new Dictionary<string, FileName>();
StringBuilder sb = new StringBuilder();
foreach (Microsoft.Office.Interop.Excel.Worksheet worksheet in wb.Worksheets)
{
sb.Append(worksheet.Name.ToString() + ",");
}
foreach (DataRow dr in dsChecklistDetails.Tables[0].Rows)
{
if (sb.ToString().Contains(dr["Name"].ToString()+ ","))
{
dtChecklistDetails = ReadExcelDataForChecklistDetails(FileName, dr["Name"].ToString());
string ChecklistId = "";
ChecklistId = dr["ID"].ToString();
if (dtChecklistDetails.Rows.Count > 0)
{
Message = checkChecklistDetailsCol(dtChecklistDetails);
if (Message == "")
{
Message = checkRequiredRowsNUllOrNotForChklistDetails(dtChecklistDetails);
if (Message == "")
{
try
{
Message = SaveChecklistDetails(dtChecklistDetails, LoginID, ChecklistId);
}
catch (SqlException ex)
{
Message = ex.Message;
}
}
}
}
else
{
Message = "Unable to read data from Excel , Expected Workesheet Name:" + dr["Name"].ToString();
}
}
}
wb.Close(false, Type.Missing, Type.Missing);
excel.Quit();
GC.Collect();
Marshal.FinalReleaseComObject(wb);
return Message;
//Marshal.FinalReleaseComObject(wb);
}
#endregion
#region SaveChecklistDetails
private string SaveChecklistDetails(DataTable dtExcelData, int LoginID, string ChecklistId)
{
string Message = "";
CDataManager.CDataManager _Obj = new PrjMgmtComps.CDataManager.CDataManager();
DataSet _dsDBData = _Obj.GetChecklistsDetailsFromDB(ChecklistId);
_dsDBData.EnforceConstraints = true;
dtExcelData.Columns.Add("PrimaryKeyID", typeof(int));
if (_dsDBData.Tables[0].Rows.Count > 0)
{
dtExcelData = UpdateExistingChecklistDetails(dtExcelData, _dsDBData.Tables[0], ChecklistId);
}
int[] lIntResult = { 0 };
if (Message == "")
{
_dsDBData.Tables[0].BeginLoadData();
int _cnt = 0;
for (int lIntCount = 0;
lIntCount < dtExcelData.Rows.Count;
lIntCount++)
{
object[] lObjCreate = new object[7];
if (dtExcelData.Rows[lIntCount]["PrimaryKeyID"] != System.DBNull.Value)
{
lObjCreate[0] = dtExcelData.Rows[lIntCount]["PrimaryKeyID"];
}
else
{
lObjCreate[0] = --_cnt;
}
if (dtExcelData.Rows[lIntCount]["Child Checklists"].ToString() != "")
{
lObjCreate[1] = dtExcelData.Rows[lIntCount]["Child Checklists"].ToString();
}
else
{
lObjCreate[1] = System.DBNull.Value;
}
if (dtExcelData.Rows[lIntCount]["Description"].ToString() != "")
{
lObjCreate[2] = dtExcelData.Rows[lIntCount]["Description"].ToString();
}
else
{
lObjCreate[2] = System.DBNull.Value;
}
if (dtExcelData.Rows[lIntCount]["Things To Look For"].ToString() != "")
{
lObjCreate[3] = dtExcelData.Rows[lIntCount]["Things To Look For"].ToString();
}
else
{
lObjCreate[3] = System.DBNull.Value;
}
if (dtExcelData.Rows[lIntCount]["Weightage"].ToString() != "")
{
lObjCreate[4] = dtExcelData.Rows[lIntCount]["Weightage"].ToString();
}
else
{
lObjCreate[4] = System.DBNull.Value;
}
if (dtExcelData.Rows[lIntCount]["Remarks"].ToString() != "")
{
lObjCreate[5] = dtExcelData.Rows[lIntCount]["Remarks"].ToString();
}
else
{
lObjCreate[5] = System.DBNull.Value;
}
if (ChecklistId != "")
{
lObjCreate[6] = ChecklistId;
}
else
{
lObjCreate[6] = System.DBNull.Value;
}
// lObjCreate[16] = EntityID;
_dsDBData.Tables[0].LoadDataRow(lObjCreate, false);
}
_dsDBData.Tables[0].EndLoadData();
lIntResult = _Obj.SetExcelChecklistDetails(_dsDBData);
}
return Message;
}
#endregion
#region UpdateExistingChecklistDetails
private DataTable UpdateExistingChecklistDetails(DataTable dtExcelData, DataTable dtDbData, string ChecklistId)
{
foreach (DataRow drExcel in dtExcelData.Rows)
{
foreach (DataRow drDb in dtDbData.Rows)
{
if ((drDb["DashBoard_ID"].ToString() == ChecklistId) && (drExcel["Child Checklists"].ToString() == drDb["Name"].ToString()))
{
drExcel["PrimaryKeyID"] = drDb["ID"];
break;
}
}
}
return dtExcelData;
}
#endregion
//Added By kamal to check Require Fields are filled or not?
#region checkRequiredRowsNUllOrNotForChklistDetails
public string checkRequiredRowsNUllOrNotForChklistDetails(DataTable dtChecklistDetails)
{
string Message = "";
for (int i = 0; i < dtChecklistDetails.Rows.Count; i++)
{
if (dtChecklistDetails.Rows[i]["Child Checklists"] == System.DBNull.Value)
{
Message += (1 + i) + " row of Child Checklists" + ",";
}
}
if (Message != "")
{
Message = "Following fields are empty in Excel" + " : " + Message;
}
return Message;
}
#endregion
#region checkChecklistDetailsCol
private string checkChecklistDetailsCol(DataTable dtTask)
{
string Message = "";
if (!dtTask.Columns.Contains("Id"))
{
Message += "Id,";
}
if (!dtTask.Columns.Contains("Child Checklists"))
{
Message += " Child Checklists,";
}
if (!dtTask.Columns.Contains("Description"))
{
Message += " Description,";
}
if (!dtTask.Columns.Contains("Things To Look For"))
{
Message += " Things To Look For,";
}
if (!dtTask.Columns.Contains("Weightage"))
{
Message += " Weightage,";
}
if (!dtTask.Columns.Contains("Remarks"))
{
Message += " Remarks,";
}
if (Message != "")
{
Message = "Following Columns are Missing in the Uploaded Excel Sheet: " + Message;
}
return Message;
}
#endregion
#region ReadExcelDataForChecklistDetails
public static DataTable ReadExcelDataForChecklistDetails(string FileName, string WorkSheet)
{
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
ConfigurationSettings.AppSettings["FilePath"] + "//Excel2SqlRead//" + FileName +
";Extended Properties=Excel 12.0;";
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [" + WorkSheet + "$] ", strConn);
DataTable dtExceldata = new DataTable();
da.Fill(dtExceldata);
//File.Delete(ConfigurationSettings.AppSettings["FilePath"] + "//Excel2SqlRead//" + FileName);
//da.Dispose();
//GC.SuppressFinalize(da);
for (int r = 0; r < dtExceldata.Rows.Count; r++)
{
bool delrow = true;
for (int i = 0; i < dtExceldata.Rows.Count; i++)
{
if (dtExceldata.Rows[r][i].ToString() == "")
{
delrow = false;
break;
}
}
if (delrow == false)
{
dtExceldata.Rows.RemoveAt(r);
r--;
}
// return dtExceldata;
}
//dtExceldata.Rows.RemoveAt(5);
return dtExceldata;
}
#endregion
#endregion这篇关于在我的应用程序中导入Excel文件时，我感到很恐怖的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！