Unable to modify xlsm file in asp.net when hosted in IIS

Question

1.00/5 (1 vote)

See more:

, +

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();

}

}

Posted 23-Sep-15 4:46am

manish.communityhub

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2015-09-23T05:02:00

You should read the following Microsoft knowledgebase article:

Considerations for server-side Automation of 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.

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: