在IIS中托管时无法在asp.net中修改xlsm文件 [英] Unable to modify xlsm file in asp.net when hosted in IIS
问题描述
我想打开 xlsm 文件并对其进行修改然后保存。
我在4.0框架中使用vs 2010并且它在
但是当在 IIS 中运行时，它无法保存它。请帮助我。
private void GenerateExcel（string Station，string Unit，string TempExcelFileName，string workSheetName，DataTable dtRawData，string EntryDate ，string EntryTime）
{
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft。 Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection .Missing.Value;
string ActualFilePath = Server.MapPath（@Excels \\+ TempExcelFileName +。xlsm）;
string CopyFilePath = Server。 MapPath（@TempExcels\\+ TempExcelFileName +。xlsm）;
File.Copy（ActualFilePath，CopyFilePath，true）;
System.IO.FileInfo file = new System.IO.FileInfo（CopyFilePath）;
string password = ConfigurationManager.AppSettings [pass]。ToString（）;
if （file.Exists）
{
xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass（）;
// xlApp = new Excel.ApplicationClass（）;
xlApp.DisplayAlerts = false;
xlWorkBook = xlApp.Workbooks.Open（ActualFilePath，
Type.Missing， Type.Missing，Type.Missing，Type.Missing，
Type.Missing，Type.Missing，Type.Missing，Type.Missing，
Type.Missing，Type。缺少，Type.Missing，Type.Missing，
Type.Missing，Type.Missing）;
xlWorkSheet =（Microsoft.Office.Interop.Excel.Worksheet）xlWorkBook.Sheets.get_Item（1）;
Microsoft.Office.Interop.Excel.Sheets wbSheet =（Microsoft.Office.Interop.Excel.Sheets）xlWorkBook.Sheets;
#region输入
// ------------------------------------------------ --------------输入----------------------------------- ---------
if（Station ==BBGS&& （单位==1||单位==2））
{
workSheetName =原始数据;
xlWorkSheet =（Microsoft.Office.Interop.Excel.Worksheet）xlWorkBook.Sheets.get_Item（1）;
xlWorkSheet = FindExcelATPWorksheet（workSheetName，xlWorkSheet，xlWorkBook）;
if（workSheetName.Trim（）==原始数据）
{
for（int i = 19; i< = 98; i ++）
{
xlWorkSheet.get_Range（F+ i.ToString（）， F+ i.ToString（））。Value2 = dtRawData.Rows [i - 19] [ParameterValues]。ToString（）;
}
xlWorkSheet.get_Range（C9，C9）。Value2 = EntryDate;
xlWorkSheet.get_Range（F9，F9）。Value2 = EntryTime;
xlWorkSheet.get_Range（C 11，C11）。Value2 =单位;
xlWorkSheet.get_Range（F11，F11）。Value2 = Station;
}
}
if if（Station ==BBGS&&单位==3）
{
workSheetName =原始数据;
xlWorkSheet =（Microsoft.Office.Interop.Excel。工作表）xlWorkBook.Sheets.get_Item（1）;
xlWorkSheet = FindExcelATPWorksheet（workSheetName，xlWorkSheet，xlWorkBook）;
if（workSheetName.Trim（）==原始数据）
{
DataView dv;
for（int i = 19; i< = 91; i ++）
{
dv = new DataView（dtRawData）;
dv.RowFilter =slno =+ xlWorkSheet.get_Range（B+ i.ToString（），B+ i.ToString（））。Value2;
if（dv.Count> 0）
xlWorkSheet.get_Range（F+ i.ToString（），F+ i.ToString（））。Value2 = dv [0] [ParameterValues]。ToString（）;
}
xlWorkSheet.get_Range（C8，C8）。Value2 = EntryDate;
xlWorkSheet.get_Range（F8， F8）。Value2 = EntryTime;
xlWorkSheet.get_Range（C10，C10）。Value2 = Unit;
xlWorkSheet.get_Range（F10，F10）。Value2 = Station;
dtRawData.Columns.Remove（slno）;
}
}
string SaveAsPath = CopyFilePath;
if（File.Exists（SaveAsPath））
{
File.Delete（SaveAsPath）;
}
/ /xlWorkSheet.Protect(SaveAsPath，false，true，false，false，true，true，true，true，true，true，true，true，true，true，true）;
// xlWorkBook。保护（（对象）密码，Type.Missing，Type.Missing）;
Micr osoft.Office.Interop.Excel.Sheets excelSheets = xlWorkBook.Sheets;
for（int i = 1;我< = excelSheets.Count; i ++）
{
xlWorkSheet =
（Microsoft.Office.Interop.Excel.Worksheet）excelSheets.get_Item（（object）i）;
xlWorkSheet.Protect（密码，假，真，假，假，真，真，真，真，真，真，真，真，真，真，真）;
}
xlWorkBook.SaveAs（SaveAsPath，Type.Missing，Type.Missing，Type.Missing，Type.Missing， Type.Missing，Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing）;
xlWorkBook.Close（false，ActualFilePath，Type.Missing）;
// ------------------------------------------ -------------------------------------------------- ---------
#endregion
＃region输出
// ------------------- -------------------------------------------输出------ -------------------------------------------------- -------------------------
xlWorkBook = xlApp.Workbooks.Open（SaveAsPath，
Type.Missing，Type.Missing，Type.Missing，Type.Missing，
Type.Missing，Type.Missing，Type.Missing，Type.Missing，
类型.Missing，Type.Missing，Type.Missing，Type.Missing，
Type.Missing，Type.Missing）;
xlWorkSheet = （Microsoft.Office.Interop.Excel.Worksheet）xlWorkBook.Sheets.get_Item（1）;
Microsoft.Office.Interop.Excel.Sheets wbSheetNew =（Microsoft.Office.Interop.Excel.Sheets） xlWorkBook.Sheets;
string [] ExcelCells = {J，M，Q，R};
十进制TS_Actual，TS_Design，HP_Actual，HP_Design，IP_Actual，IP_Design;
workSheetName =TG Heat rate&缸效率;
#region TG加热速率和气缸效率
// ----------- ---------------------------涡轮系统--------------- ---------------------------------
xlWorkSheet =（Microsoft.Office.Interop。 Excel.Worksheet）xlWorkBook.Sheets.get_Item（1）;
xlWorkSheet = FindExcelATPWorksheet（workSheetName，xlWorkSheet，xlWorkBook）;
TS_Actual = Convert.ToDecimal（xlWorkSheet.get_Range（J17，J17）。Value2.ToString（））;
TS_Design = Convert.ToDecimal（xlWorkSheet.get_Range（M17，M17）） .Value2）;
HP_Actual = Convert.ToDecimal（xlWorkSheet.get_Range（Q10，Q10）。Value2）;
HP_Design = Convert.ToDecimal（xlWorkSheet。 get_Range（R10，R10）。Value2）;
IP_Actual = Convert.ToDecimal（xlWorkSheet.get_Range（Q16，Q16）。Value2）;
IP_Design = Convert.ToDecimal（xl WorkSheet.get_Range（R16，R16）。Value2）;
//// ------ -------------------------------- End Turbine System --------------- ---------------------------------------
#endregion
workSheetName =加热器性能;
#region加热器性能
DataTable dtPerformance = new DataTable（）;
if（Station ==BBGS&& （单位==1||单位==2））
{
// -------------- ------------------------加热器性能------------------------ ---------------------------------
xlWorkSheet =（Microsoft.Office.Interop.Excel.Worksheet）xlWorkBook.Sheets.get_Item（1）;
xlWorkSheet = FindExcelATPWorksheet（workSheetName，xlWorkSheet，xlWorkBook）;
string [ ] ExcelCellPerformance = {E，F，G，H，I，J，K，L，M，N};
dtPerformance.Columns.Add（HP7_Actual）;
dtPerformance.Columns.Add（HP7_Design）;
dtPerformance.Columns.Add（HP6_Actual）;
dtPerformance.Columns.Add（HP6_Design）;
dtPerformance.Columns.Add （LP4_Actual）;
dtPerformance.Columns.Add（LP4_Design）;
dtPerformance.Columns.Add（LP3_Actual）;
dtPerformance.Columns.Add（LP3_Design）;
dtPerformance.Columns.Add（LP2_Actual）;
dtPerformance.Columns.Add（LP2_Design）;
dtPerformance = GetWorkSheetCell（xlWorkSheet，ExcelCellPerformance，dtPerformance，11,14）;
dtPerformance.Columns.Add（HP5_Actual）;
dtPerformance.Columns.Add（HP5_Design）;
dtPerformance.Columns.Add （ParameterID）;
dtPerformance.Columns [ParameterID]。SetOrdinal（0）;
dtPerformance.Columns [HP5_Actual ]。SetOrdinal（5）;
dtPerformance.Columns [HP5_Design]。SetOrdinal（6）;
}
else if（Station ==BBGS&&单位==3）
{
xlWorkSheet =（Microsoft.Office.Interop.Excel.Worksheet）xlWorkBook.Sheets.get_Item（1）;
xlWorkSheet = FindExcelATPWorksheet（workSheetName，xlWorkSheet，xlWorkBook）;
string [] ExcelCellPerformance = {E，F，G，H，I， J，K，L};
dtPerformance.Columns.Add（ HP6_Actual）;
dtPerformance.Columns.Add（HP6_Design）;
dtPerformance.Columns.Add（HP5_Actual）;
dtPerformance.Columns.Add（HP5_Design）;
dtPerformance.Columns.Add（LP3_Actual）;
dtPerformance.Columns .Add（LP3_Design）;
dtPerformance.Columns.Add（LP2_Actual）;
dtPerformance.Columns.Add（LP2_Design）;
dtPerforma nce = GetWorkSheetCell（xlWorkSheet，ExcelCellPerformance，dtPerformance，11,14）;
dtPerformance.Columns.Add（ParameterID）;
dtPerformance.Columns.Add（HP7_Actual）;
dtPerformance.Columns.Add（HP7_Design）;
dtPerformance.Columns.Add（LP4_Actual）;
dtPerformance.Columns.Add（LP4_Design）;
dtPerformance.Columns [ParameterID]。SetOrdinal（0）;
dtPerformance.Columns [HP7_Actual]。SetOrdinal（1）;
dtPerformance.Columns [HP7_Design]。SetOrdinal（2）;
dtPerformance.Columns [LP4_Actual]。SetOrdinal（7）;
dtPerformance.Columns [LP4_Design]。SetOrdinal（8）;
int count = 0;
foreach（dtPerformance.Rows中的DataRow dr）
{
count + = 1;
dr [ParameterID] = count;
foreach（dtPerformance.Columns中的DataColumn dc）
{
if（dr [dc.ColumnName] .ToString（）。Trim（）。Length == 0）
{
dr [dc。 ColumnName] = null;
}
}
}
} < br $>
#endregion
xlWorkBook.Close（）;
#endregion
xlApp.Quit （）;
var fileInfo = new System.IO.FileInfo（file.FullName）;
Response.ContentType =application / octet -stream;
Response.AddHeader（Content-Disposition，String.Fo rmat（attachment; filename = \{0} \，file.FullName））;
Response.AddHeader（Content-Length，fileInfo.Length.ToString（）） ;
Response.WriteFile（file.FullName）;
Response.End（）;
}
}
I am want to open the xlsm file and modify it and then save it.
I am using vs 2010 in 4.0 framework and it is working well in dev sever.
But when run in IIS it is unable to save it.Please help me.
private void GenerateExcel(string Station, string Unit, string TempExcelFileName, string workSheetName, DataTable dtRawData, string EntryDate, string EntryTime)
{
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
string ActualFilePath = Server.MapPath(@"Excels\\" + TempExcelFileName + ".xlsm");
string CopyFilePath = Server.MapPath(@"TempExcels\\" + TempExcelFileName + ".xlsm");
File.Copy(ActualFilePath, CopyFilePath, true);
System.IO.FileInfo file = new System.IO.FileInfo(CopyFilePath);
string password = ConfigurationManager.AppSettings["pass"].ToString();
if (file.Exists)
{
xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
//xlApp =new Excel.ApplicationClass();
xlApp.DisplayAlerts = false;
xlWorkBook = xlApp.Workbooks.Open(ActualFilePath,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Sheets.get_Item(1);
Microsoft.Office.Interop.Excel.Sheets wbSheet = (Microsoft.Office.Interop.Excel.Sheets)xlWorkBook.Sheets;
#region Input
//--------------------------------------------------------------Input--------------------------------------------
if (Station == "BBGS" && (Unit == "1" || Unit == "2"))
{
workSheetName = "Raw Data";
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Sheets.get_Item(1);
xlWorkSheet = FindExcelATPWorksheet(workSheetName, xlWorkSheet, xlWorkBook);
if (workSheetName.Trim() == "Raw Data")
{
for (int i = 19; i <= 98; i++)
{
xlWorkSheet.get_Range("F" + i.ToString(), "F" + i.ToString()).Value2 = dtRawData.Rows[i - 19]["ParameterValues"].ToString();
}
xlWorkSheet.get_Range("C9", "C9").Value2 = EntryDate;
xlWorkSheet.get_Range("F9", "F9").Value2 = EntryTime;
xlWorkSheet.get_Range("C11", "C11").Value2 = Unit;
xlWorkSheet.get_Range("F11", "F11").Value2 = Station;
}
}
else if (Station == "BBGS" && Unit == "3")
{
workSheetName = "Raw Data";
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Sheets.get_Item(1);
xlWorkSheet = FindExcelATPWorksheet(workSheetName, xlWorkSheet, xlWorkBook);
if (workSheetName.Trim() == "Raw Data")
{
DataView dv;
for (int i = 19; i <= 91; i++)
{
dv = new DataView(dtRawData);
dv.RowFilter = "slno=" + xlWorkSheet.get_Range("B" + i.ToString(), "B" + i.ToString()).Value2;
if (dv.Count > 0)
xlWorkSheet.get_Range("F" + i.ToString(), "F" + i.ToString()).Value2 = dv[0]["ParameterValues"].ToString();
}
xlWorkSheet.get_Range("C8", "C8").Value2 = EntryDate;
xlWorkSheet.get_Range("F8", "F8").Value2 = EntryTime;
xlWorkSheet.get_Range("C10", "C10").Value2 = Unit;
xlWorkSheet.get_Range("F10", "F10").Value2 = Station;
dtRawData.Columns.Remove("slno");
}
}
string SaveAsPath = CopyFilePath;
if (File.Exists(SaveAsPath))
{
File.Delete(SaveAsPath);
}
//xlWorkSheet.Protect(SaveAsPath, false, true, false, false, true, true, true, true, true, true, true, true, true, true, true);
//xlWorkBook.Protect((object)password, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Sheets excelSheets = xlWorkBook.Sheets;
for (int i = 1; i <= excelSheets.Count; i++)
{
xlWorkSheet =
(Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item((object)i);
xlWorkSheet.Protect(password, false, true, false, false, true, true, true, true, true, true, true, true, true, true, true);
}
xlWorkBook.SaveAs(SaveAsPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlWorkBook.Close(false, ActualFilePath, Type.Missing);
//-----------------------------------------------------------------------------------------------------
#endregion
#region Output
//--------------------------------------------------------------Output---------------------------------------------------------------------------------
xlWorkBook = xlApp.Workbooks.Open(SaveAsPath,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Sheets.get_Item(1);
Microsoft.Office.Interop.Excel.Sheets wbSheetNew = (Microsoft.Office.Interop.Excel.Sheets)xlWorkBook.Sheets;
string[] ExcelCells = { "J", "M", "Q", "R" };
decimal TS_Actual, TS_Design, HP_Actual, HP_Design, IP_Actual, IP_Design;
workSheetName = "TG Heat rate & cylinder eff";
#region TG Heat rate & cylinder eff
//--------------------------------------Turbine System------------------------------------------------------
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Sheets.get_Item(1);
xlWorkSheet = FindExcelATPWorksheet(workSheetName, xlWorkSheet, xlWorkBook);
TS_Actual = Convert.ToDecimal(xlWorkSheet.get_Range("J17", "J17").Value2.ToString());
TS_Design = Convert.ToDecimal(xlWorkSheet.get_Range("M17", "M17").Value2);
HP_Actual = Convert.ToDecimal(xlWorkSheet.get_Range("Q10", "Q10").Value2);
HP_Design = Convert.ToDecimal(xlWorkSheet.get_Range("R10", "R10").Value2);
IP_Actual = Convert.ToDecimal(xlWorkSheet.get_Range("Q16", "Q16").Value2);
IP_Design = Convert.ToDecimal(xlWorkSheet.get_Range("R16", "R16").Value2);
////--------------------------------------End Turbine System------------------------------------------------------
#endregion
workSheetName = "Heater performance";
#region Heater performance
DataTable dtPerformance = new DataTable();
if (Station == "BBGS" && (Unit == "1" || Unit == "2"))
{
//--------------------------------------Heater performance---------------------------------------------------------
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Sheets.get_Item(1);
xlWorkSheet = FindExcelATPWorksheet(workSheetName, xlWorkSheet, xlWorkBook);
string[] ExcelCellPerformance = { "E", "F", "G", "H", "I", "J", "K", "L", "M", "N" };
dtPerformance.Columns.Add("HP7_Actual");
dtPerformance.Columns.Add("HP7_Design");
dtPerformance.Columns.Add("HP6_Actual");
dtPerformance.Columns.Add("HP6_Design");
dtPerformance.Columns.Add("LP4_Actual");
dtPerformance.Columns.Add("LP4_Design");
dtPerformance.Columns.Add("LP3_Actual");
dtPerformance.Columns.Add("LP3_Design");
dtPerformance.Columns.Add("LP2_Actual");
dtPerformance.Columns.Add("LP2_Design");
dtPerformance = GetWorkSheetCell(xlWorkSheet, ExcelCellPerformance, dtPerformance, 11, 14);
dtPerformance.Columns.Add("HP5_Actual");
dtPerformance.Columns.Add("HP5_Design");
dtPerformance.Columns.Add("ParameterID");
dtPerformance.Columns["ParameterID"].SetOrdinal(0);
dtPerformance.Columns["HP5_Actual"].SetOrdinal(5);
dtPerformance.Columns["HP5_Design"].SetOrdinal(6);
}
else if (Station == "BBGS" && Unit == "3")
{
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Sheets.get_Item(1);
xlWorkSheet = FindExcelATPWorksheet(workSheetName, xlWorkSheet, xlWorkBook);
string[] ExcelCellPerformance = { "E", "F", "G", "H", "I", "J", "K", "L" };
dtPerformance.Columns.Add("HP6_Actual");
dtPerformance.Columns.Add("HP6_Design");
dtPerformance.Columns.Add("HP5_Actual");
dtPerformance.Columns.Add("HP5_Design");
dtPerformance.Columns.Add("LP3_Actual");
dtPerformance.Columns.Add("LP3_Design");
dtPerformance.Columns.Add("LP2_Actual");
dtPerformance.Columns.Add("LP2_Design");
dtPerformance = GetWorkSheetCell(xlWorkSheet, ExcelCellPerformance, dtPerformance, 11, 14);
dtPerformance.Columns.Add("ParameterID");
dtPerformance.Columns.Add("HP7_Actual");
dtPerformance.Columns.Add("HP7_Design");
dtPerformance.Columns.Add("LP4_Actual");
dtPerformance.Columns.Add("LP4_Design");
dtPerformance.Columns["ParameterID"].SetOrdinal(0);
dtPerformance.Columns["HP7_Actual"].SetOrdinal(1);
dtPerformance.Columns["HP7_Design"].SetOrdinal(2);
dtPerformance.Columns["LP4_Actual"].SetOrdinal(7);
dtPerformance.Columns["LP4_Design"].SetOrdinal(8);
int count = 0;
foreach (DataRow dr in dtPerformance.Rows)
{
count += 1;
dr["ParameterID"] = count;
foreach (DataColumn dc in dtPerformance.Columns)
{
if (dr[dc.ColumnName].ToString().Trim().Length == 0)
{
dr[dc.ColumnName] = null;
}
}
}
}
#endregion
xlWorkBook.Close();
#endregion
xlApp.Quit();
var fileInfo = new System.IO.FileInfo(file.FullName);
Response.ContentType = "application/octet-stream";
Response.AddHeader("Content-Disposition", String.Format("attachment;filename=\"{0}\"", file.FullName));
Response.AddHeader("Content-Length", fileInfo.Length.ToString());
Response.WriteFile(file.FullName);
Response.End();
}
}
推荐答案
你应阅读以下Microsoft知识库文章：
You should read the following Microsoft knowledgebase article:
Microsoft目前不建议也不支持从任何无人参与的非交互式客户端应用程序或组件（包括ASP，ASP.NET，DCOM）自动化Microsoft Office应用程序和NT服务），因为Office可能表现出不稳定的行为和/或死区k当Office在这种环境中运行时。
Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.
文章记录了你必须跳过的各种箍，试图让它在服务器。但是，无法保证您能够使其正常工作，并且无法保证它将来不会在某个随机点停止工作。
您尚未提及错误消息。最可能的是80040154，表示Office未安装在服务器上，或者是拒绝访问消息，试图将工作簿保存在自身上，表明存在NTFS权限问题。如果Excel尝试在服务器上显示任何类型的交互式消息，您的代码也会挂起。
有多种方法可以在服务器上创建Excel电子表格而无需使用Office互操作。例如：
- EPPlus [ ^ ];
- ClosedXML [ ^ ];
- OpenXML SDK [ ^ ];
- EPPlus[^];
- ClosedXML[^];
- The OpenXML SDK[^];
The article documents the various hoops you'll have to jump through to try to make it work on the server. However, there is no guarantee that you'll be able to make it work, and no guarantee that it won't stop working at some random point in the future.
You haven't mentioned an error message. The most likely ones would be 80040154, indicating that Office is not installed on the server, or an "access denied" message trying to save the workbook over itself, indicating an NTFS permissions problem. Your code could also hang if Excel tries to display any kind of interactive message on the server.
There are various ways to create Excel spreadsheets on the server without using Office interop. For example:
这篇关于在IIS中托管时无法在asp.net中修改xlsm文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
