Data connections have been disabled & full path of file displaying in formula range

Aspose.Cells Product Family
1

I’m exporting to an already existing Excel (xls) spread sheet from ASP.NET 4.0 and am using the lastest version of Aspose.Cells. What’s happening is, after the export, I hit a button that runs my VBA code (can this be done in code behind?), then I get an error because my formulas are failing. Upon further investigation, in one of my worksheets, my formulated cells, that refer to my worksheet that contains the exported data, is preceeded by the full path of the open spreadsheet (temp directory). Here are some other details that might give a hint about what’s going on:

  1. What’s strange is if I save the spreadsheet and rerun my VBA code everything works.

  2. This appears to only happen on specific senerios. Everything appears to work correctly in ie9/windows7, firefox/XP, but is failing in ie8/XP.

  3. Upon the spreadsheet opening after export I get a “Data connections have been disabled” warning that requires a button click to continue.

Is there a way I can avoid having the full path inserted into my formula range AND skip the “Data connections have been disabled” message/click? This full path in my formula is why it won’t work.

Thanks for your help. Here is my code.

SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

Workbook wbook = new Workbook(strTemplateFileName);
Worksheet sheet = wbook.Worksheets[0];

Cells cells = sheet.Cells;
Aspose.Cells.Cell urlCol = cells.FindString("image_url", null); //null means it will search the string from the start (A1 cell).
Aspose.Cells.Cell dateCol = cells.FindString("Date", null);

Aspose.Cells.Style style = null;
StyleFlag flag = null;
flag = new StyleFlag();
style = wbook.Styles[wbook.Styles.Add()];
style.Custom = "mm/dd/yyyy";
flag.NumberFormat = true;
sheet.Cells.ApplyColumnStyle(dateCol.Column, style, flag);

//Import the datareader object to the sheet cells 
sheet.Cells.ImportDataReader(dr, false, 1, 0, true, "mm/dd/yyyy", true);
dr.Close();

sheet.ActiveCell = "A1";
sheet.AutoFitColumns();

int index = 0;
for (int i = 1; i <= sheet.Cells.MaxDataRow; i++)
{
index = sheet.Hyperlinks.Add(i, urlCol.Column, 1, 1, sheet.Cells[i, urlCol.Column].StringValue);
Aspose.Cells.Hyperlink hlink = sheet.Hyperlinks[index];

if (sheet.Cells[i, urlCol.Column].StringValue.Trim() != string.Empty || sheet.Cells[i, urlCol.Column].StringValue.IndexOf("> Form Image ") > 3 || sheet.Cells[i, urlCol.Column].StringValue.IndexOf("href=") > 5)
{

hlink.ScreenTip = "Click to open an image of the form associated with this record.";
hlink.Address = "http://www.blahblah.com" + sheet.Cells[i, urlCol.Column].StringValue;
hlink.TextToDisplay = "Form Image";
}
else
{
hlink.ScreenTip = string.Empty;
hlink.Address = string.Empty;
hlink.TextToDisplay = string.Empty;
}
}

MemoryStream stream = new MemoryStream();

wbook.Save(stream, SaveFormat.Excel97To2003);

int filesize = Convert.ToInt32(stream.Length);
byte[] byteArray = new byte[filesize];

byteArray = stream.ToArray();

stream.Flush();
stream.Close();

Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=" + exportFilename);
Response.AddHeader("Content-Length", byteArray.Length.ToString());
Response.ContentType = "application/octet-stream";
Response.BinaryWrite(byteArray);
Response.Flush();
Response.End();
2

Hi,

Thanks for your posting and using Aspose.Cells for .NET

Please change the following code

MemoryStream stream = new MemoryStream();

wbook.Save(stream, SaveFormat.Excel97To2003);

int filesize = Convert.ToInt32(stream.Length);
byte[] byteArray = new byte[filesize];

byteArray = stream.ToArray();

stream.Flush();
stream.Close();

Response.Clear();
Response.AddHeader(“Content-Disposition”, “attachment; filename=” + exportFilename);
Response.AddHeader(“Content-Length”, byteArray.Length.ToString());
Response.ContentType = “application/octet-stream”;
Response.BinaryWrite(byteArray);
Response.Flush();
Response.End();

into this code.

wbook.Save(HttpContext.Current.Response, “output.xls”, ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
HttpContext.Current.Response.End();

It should resolve your issue.

If the issue still occurs, then please provide us your simple sample project replicating this issue using the latest version: Aspose.Cells for .NET (Latest Version)

Please also note: Aspose.Cells cannot run or modify your macros (VBA code). It just preserves the macros in existing excel files.

Please also provide us your current output xls file and the expected output xls file and the screenshots highlighting your problems with red circles around them.

It will help us look into your issue and we will help you asap.


3

I’m still encountering the problem. Problem detailed: When clicking “Open” (rather than “Save” and then opening the resulting file), the pivot tables contain in addition to the worksheet name and cell range (e.g., Data!$D$1:$D$65536) a path which refers to the actual file that is already open (in one of IE’s temporary internet files folders). Because Excel thinks this is an external link, it tries to open the referenced file, but this is actually the file that is currently open, and since it’s already open, Excel can’t open it, generating the error (“Cannot open pivot table source file…”).Problem: When clicking “Open” (rather than “Save” and then opening the resulting file), the pivot tables contain in addition to the worksheet name and cell range (e.g., Data!$D$1:$D$65536) a path which refers to the actual file that is already open (in one of IE’s temporary internet files folders). Because Excel thinks this is an external link, it tries to open the referenced file, but this is actually the file that is currently open, and since it’s already open, Excel can’t open it, generating the error (“Cannot open pivot table source file…”).

I’ve attached some screens shots of the problem, a sample project, and I’ve published the sample to the web (http://dev1.datainfoportal.com/FineSource/admin/Reports/TestExportToExcel).

Keep in mind the following:

  1. This appears to only be failing in ie8/XP/office 2010, unfortunately our biggest customer base is using this senerio.

  2. Screenshot1: In cell A4, D4, G4, & J4 is where the sheets full path (opened from temp directory since it was downloaded from web) is appearing.

  3. Screenshot2: This is the error message I’m getting because of what is occuring in #2.

Thanks for your help with this.

Rand Douglas

4

Hi,

Thanks for your files and screenshot.

We will look into your issue and provide you a solution or workaround asap.

If we require any further information or help at your end, we will let you know asap.

5

Hi, I was wondering if you were able to find the cause of my problem? Any help would be appreciated.

6

Hi,

Thanks for your posting and using Aspose.Cells.

Please spare us some time to look into this issue and give you advice.

Please also keep the web published, it will be helpful in investigating this issue

(http://dev1.datainfoportal.com/FineSource/admin/Reports/TestExportToExcel/).

We have logged this issue in our database. Once there is some update for you, we will let you know asap.

This issue has been logged as CELLSNET-40986.

7

It's been a long time since I've posted this problem. I was hoping you have found a solution. Any progress? Even if I had to pay extra to get this problem solved I'd like to do so. I don't mean to sound pushy but his is very urgent on my end. Thanks in advance, great product.

Can you grant me access to Aspose Nanjing JIRAAspose Nanjing JIRA? Is there where I monitor the progress of the ticket?

8

Hi,

Thanks for your posting and using Aspose.Cells.

You cannot access Nanjing JIRA because it is private database and is meant for our own internal communication and keep tracking/fixing the reported or logged issue.

However, you can request us about the status anytime you like.

We have looked into your issue and we found that without using Aspose, if you open this file using filestream without click saving first, you will still find this issue, it’s not relating to aspose.

Please try this sample code to refresh the pivottable:

C#


foreach (Worksheet ws in wb.Worksheets)

{

foreach (PivotTable t in ws.PivotTables)

{

t.RefreshData();

t.CalculateData();

}

}

9

I placed the code right after the sheet.AutoFitColumns(); and it still did not work. Did placing this code in the project I sent you work in XP & IE8? Have you ever seen this before? If you understand how the data is exported to the file and how the file downloads to the client, then do you know why this is happening? This only happens on XP using IE 8… Can I pay Aspose to figure this out? Do you have consultant services? Y’all are the best right? Please help.

10

Hi,

Thanks for your feedback.

We have found that this bug is not related to Aspose.Cells, you can find it as we have explained in this post: 406978

In order to refresh the pivot tables, please use the above code and place it before AutoFit columns.

If you want to escalate this issue, you can consider purchasing Priority Support. Once, you will buy a priority support, we will log your issue with Critical Priority.

11

You are correct. This isn’t Aspose causing this. I found the cause here: http://answers.officewriter.com/error-cannot-open-pivottable-source-file-filenamexxlssourcedata

Considering that Aspose.Cells is a throughly tested product and is used by so many, I’m sure you’ve encountered this before? Has this not been resolved in the past?

But still if you can ensure me priority support can resolve this I will gladly purchase the plan.

Thanks for your continued patience with me and my issue.

12

Hi,

Thanks for sharing your feedback and considering Aspose.Cells.

I am afraid, Priority Support does not guarantee a fix, it just escalates your issue.

Developers put more efforts in resolving Priority Support issues than normal issues.

For more information regarding Priority Support, please post your queries on Aspose.Purchase forum.