Reference to other XLS Files

[Stefan Wasinger]

Hello,

we are using a XLS file (aka Definition) which gets the data from other XLS files (aka Sources). These have a special name to determine the datasource, if you upload the file (Definition) onto our web page the I have to parse this file and store the references.

Now, my problem is, that I only get #REF as tab name instead of
Source_2010_1_...

So, my question is, how do I get the names of the referenced files?
Is this even possible?

regards
Stefan Wasinger

[Sathish Babu R]

Hi Stefan,

Thank you for using Syncfusion Products.

We are afraid that, we were not able to understand your requirement clearly. Could you please provide us further details on what you are trying to achieve?

Do you want the XlsIO to store the hyperlink to the excel file and extract it from the same file, if so please see the below attached sample and let us know if this helps you.

http://help.syncfusion.com/samples/XlsIO.Windows/Supports/F94306.zip

Please let me know if you have any queries.

Regards,
Sathish

[Stefan Wasinger]

Thanks for the reply,

I thought my explanation wouldn't be enough, but it was worth a try. ;)

I uploaded a simple example, WorkbookA has references to Cells of WorkbookB, it would be necessary to retrieve the Workbook Name in the brackets ([]) for our application.

Regards, Stefan



Example_33ae5a35.zip

[Sathish Babu R]

Hi Stefan,

Thank you for your interest in Syncfusion Products.

As you have reported in your last update, we have created a sample to generate the formula which refer the cell of other workbook, could you please get the attached sample from the below link and let us know if this helps you.

http://help.syncfusion.com/samples/XlsIO.Windows/Supports/F94306.zip

Please let me know if you have any queries.

Regards,
Sathish

[Stefan Wasinger]

Hello,

I think you have misunderstood the problem.

WorkbookA in my example has the reference to WorkbookB, the next step is to upload only WorkbookA.
Then I parse the cells and save the formula [WorkbookB.xls]Table1!A1 because I need the information which is included in the filename (it looks actually like this: Source_selected_selected_2010_1_EUR.xls).

So the formula should look like this:
[Source_selected_selected_2010_1_EUR.xls]Stock!I37
but is looks like this
#REF!I37

So the filename is gone.

Here a codesnippet of what I actually do:

foreach (IWorksheet worksheet in XlsWorkbook.Worksheets)
{
foreach (IRange cell in worksheet.Cells)
{
if (cell.Formula != null && cell.Formula.Contains("[Source_"))
{
object[] dataRow = GetNewDataRow(cell.Formula, column, row);
if (dataRow != null)
{
sourceData.Rows.Add(dataRow);
cell.Formula = dataRow[5].ToString();
row++;
}
}
}
column = IncreaseColumn(column);
row = 1;
}

[Sathish Babu R]

Hi Stefan,

Thank you for your interest in Syncfusion products.

We were not able to reproduce the reported issue at our side. We have created a web sample to parse the uploaded workbook formula cell which refers the cell of other workbook and also we recorded the video of sample testing for your reference. Could you please get the attached sample and video and let us know if this helps you. If still you can able to reproduce the issue, please update us with a simplified issue reproducing sample.

http://help.syncfusion.com/samples/XlsIO.Windows/Supports/F94306.zip

Please let me know if you have any queries.

Regards,
Sathish

[Stefan Wasinger]

Thank you for your time, I really appreciate your help.

I have used your sample, with the 2 attached files, also I have made a video.

Unfortunately the result is still the same.
We are using SyncFusion 7.4.0.20 in our project, do you think it is necessary to upgrade our version to the latest?

Just to make sure, I have checked the exe-Video with Kaspersky AntiVirus, so there won't be any harm.

best regards, Stefan



CellRef_4b9711aa.zip

[Stefan Wasinger]

Me again,

maybe it's a problem, that we store the XLS file in the database and open it from a memorystream when we need it.

But the example uses a physical file and didn't work either, I really don't have any idea to solve this problem :(

regards, Stefan

[Sathish Babu R]

Hi Stefan,

Thank you for your interest in Syncfusion Products.

We hope that the mentioned issue could reproduce due to the Regional settings. So we attached the sample with the “US-English” culture support, could you please test the sample at your side and still if you can able to reproduce the issue, please update us with your regional settings which helps us for further investigation on this issue.

http://help.syncfusion.com/samples/XlsIO.Windows/Supports/F94306.zip

Please let me know if you have any queries.

Regards,
Sathish

[Stefan Wasinger]

Hello again,

I've tried the sample, but there is no change in behaviour.

Normally, the sample runs at 'de-AT' culture settings, but in our application there will be different culture settings, as our application is designed to be multilanguage. But mostly it'll be used with 'en-US' settings.

BUT, I've tried with a newer Version of XLSIO and it did work!
So, all we have to do is to check if everything else is working ok (we are using SyncFusion heavily in our project).

I have to thank you for your time and best regards
Stefan

[Sathish Babu R]

Hi Stefan,

Thank you very much for the update.

Regards,
Sathish