Excel Web Services and external Links in Workbooks

My question on MSDN forum

When using Excel webservices in combination with Sharepoint to manage the excel files, you are able to show graphics from excel files managed by sharepoint. But Excel web service can't handle external links to other workbooks.

So we need to use the BreakLink method in the Office Excel Interop… easy, you think?

not so difficult after some research:

Office 2007 Primary Interop Assembly

Use the Office PIA to create a reference to the Microsoft.Office.Interop.Excel namespace

Code snippet to open the Excel File:

protected void Page_Load(object sender, EventArgs e)
{
ID = Guid.NewGuid();
filename = "test.xlsx";
savepath = Environment.GetEnvironmentVariable("TMP") + @"\" + ID + "_" + filename;

file = new FileInfo(MapPath(filename));
copy = new FileInfo(savepath);

RemoveAllLinksFrom(file);

copy.Delete();
Response.Write("


" + copy.FullName + " has been deleted.
");
}

private void RemoveAllLinksFrom(FileInfo FileToProcess)
{
Response.Write("


" + FileToProcess.FullName + " starts to process.
");

//Retrieve the COM
//Make sure that the ASPNET(XP)/INTERACTIVE(SERVER2003) has read/write permissions
//SITE: http://blog.crowe.co.nz/archive/2006/03/02/589.aspx
//--------------------------------------------------------------------------------
Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
Workbook ObjWorkBook;
CultureInfo info = Thread.CurrentThread.CurrentCulture;
try
{
//Generates problems with PIA, set Culture info to en-us and it works
//------------------------------------------------------------------------
Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("en-US");
ObjWorkBook = ObjExcel.Workbooks.Open(FileToProcess.FullName, false, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Object list = ObjWorkBook.LinkSources(XlLinkType.xlLinkTypeExcelLinks);
List listfailed = new List();
foreach (string link in (ICollection)list){
try
{
ObjWorkBook.BreakLink(link, XlLinkType.xlLinkTypeExcelLinks);
Response.Write("External link to " + link + " removed" + "
");
}
catch (Exception ex) {
Response.Write("

External link to " + link + " failed" + "

");
listfailed.Add(link);
}
}

//Save the file to the temp directory.
//Make sure that the ASPNET(XP)/INTERACTIVE(SERVER2003) has read/write permissions
//--------------------------------------------------------------------------------
ObjExcel.ActiveWorkbook.SaveCopyAs(savepath);
ObjWorkBook.Saved = true;
ObjWorkBook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlDoNotSaveChanges, Type.Missing, Type.Missing);
ObjExcel.Quit();
Thread.CurrentThread.CurrentCulture = info;
}
catch (Exception ex)
{
Response.Write("


" + ex.ToString() + "
");
}
finally
{
Marshal.FinalReleaseComObject(ObjExcel);
}
}
}

Points of interest:

* As you are using ASP.NET to open the Excel com Object take a look at this site:
http://blog.crowe.co.nz/archive/2006/03/02/589.aspx new=true]http://blog.crowe.co.nz/archive/2006/03/02/589.aspx[/url]

* Check the credentials for you TEMP directory.

* Save the Excel file in the TEMP directory otherwise Excel deadlocks while trying to open the file directly out sharepoint.

* Set the CurrentThread CultureInfo to en-US otherwise you get the message that the excel file can't be openened.

* create a FileInfo Object from the excel file in the TEMP dir and open the RemoveAllLinksFrom() method.

After the RemoveAllLinks method has been processed you can upload the file back in to sharepoint using the SPFILE object so that Excel Web Service doesn't give an error anymore.

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)