Not able to read/open workbook which contains chart

Aspose.Cells Product Family
1

Hi Team,

In my requirement, I want to read a temporary excel file which has charts in many sheets.(Chart is prepated in Ms excel itself).
Now when I am trying to open that excel file using workbook object my cursor is hang on that particular line neither I am not getting any error nor any success response. But same file is running properly if I removed chart from it. I have aspose.cells license(ver 7.3.4.0).log.zip (10.8 KB)

Below is my code. Also uploaded a excel file for your reference
public string SaveExcel()
{
var tempPath = @“D:\log.xlsx”;
Aspose.Cells.License licenseWord = new Aspose.Cells.License();
licenseWord.SetLicense(CommonBL.GenerateStreamFromString(CommonBL.GenerateStringFromBytes(Properties.Resources.Aspose_Total)));

        Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
        Aspose.Cells.Workbook wtemp = new Aspose.Cells.Workbook(tempPath);
        Aspose.Cells.Worksheet sheetTemp = wtemp.Worksheets["sheet1"];
        var t = sheetTemp.Charts.Count;
        return "";

    }
2

@Coder365,

Thanks for the sample file.

Since you are using an older version (ver 7.3.4.0) of Aspose.Cells for .NET, so we cannot evaluate your issue or figure it out. Please upgrade to and try our latest version/fix: Aspose.Cells for .NET v22.12 (Releases | NuGet). I have tested using your template file and it works absolutely fine and I don’t find your mentioned issue.

3

Thanks @Amjad_Sahi for quick support,
But I have one query,
Did you find chart count when you tried this code? in below line
var t = sheetTemp.Charts.Count;
More on that I want to copy a worksheet which has chart to another workbook’s worksheet.
is there any methods/process using which I can accomplish this?

Thanks in adavance.

4

Yes, it gives me 1 as charts count.

See the document with example code for your reference.

5

Thanks @Amjad_Sahi for quick help.
My query is resolved.

6

@Coder365,

Good to know that your issue is resolved now. Feel free to write us back if you have further queries or comments.

7

Hi @Amjad_Sahi,

I have successfully read the charts through new version but now new issue is coming. whenever I save the workbook which contains the charts through workbook.save()log.zip (37.0 KB)
method( I am saving workbook into my onedrive folder) it is saved. But, when I read the charts from worksheet through Microsoft Graph Api, It gives only first chart data for all charts.
For ex in attached sheet there are two charts chart1 and chart 2, but when I read the graphs through graph Api it show below response. Same thing work properly without using Aspose saving method.
Please suggest/guide if I am missing anything here.
Below is my saving code.
Aspose.Cells.Workbook wtemp = null;
File.WriteAllBytes(tempPath, wData.bytes);
wtemp = new Aspose.Cells.Workbook(tempPath);
if(!File.Exists(Path1 + docName + “.xlsx”))
{
using (File.Create(Path1 + docName + “.xlsx”)) ;
}
wtemp.Save(Path1 + docName + “.xlsx”);
path1 is my actual location where I am saving the file.

{

"@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users('123')/drive/items('1234')/workbook/worksheets('Date')/charts",

"value": [

    {

        "@odata.id": "/users('ABC')/drive/items('123')/workbook/worksheets(%27%7B18B4B96F-BD18-4632-A153-8C7D8BD06E79%7D%27)/charts(%27%7B00000000-0000-0000-0000-000000000000%7D%27)",

        "height": 204.75,

        "id": "{00000000-0000-0000-0000-000000000000}",

        "top": 1232.25,

        "left": 282,

        "name": "Chart 1",

        "width": 360.75

    },

    {

        "@odata.id": "/users('ABC')/drive/items('123')/workbook/worksheets(%27%7B18B4B96F-BD18-4632-A153-8C7D8BD06E79%7D%27)/charts(%27%7B00000000-0000-0000-0000-000000000000%7D%27)",

        "height": 204.75,

        "id": "{00000000-0000-0000-0000-000000000000}",

        "top": 1232.25,

        "left": 282,

        "name": "Chart 1",

        "width": 360.75

    }

]

}

8

@Coder365,

I opened your log file into MS Excel 2016 but could not evaluate charts as the worksheet is protected. Moreover, we do not know much about Microsoft Graph APIs and how the APIs work.
We appreciate if you could create a separate and standalone console VS.NET application (source code without compilation errors), zip the project and post us with all the resource files and libraries. We will check your issue soon.

9

https://irisindiacloud-my.sharepoint.com/personal/chetan_kotwal_irisbusiness_com/_layouts/15/onedrive.aspx?id=%2Fpersonal%2Fchetan%5Fkotwal%5Firisbusiness%5Fcom%2FDocuments%2FGraphApi%2Ezip&parent=%2Fpersonal%2Fchetan%5Fkotwal%5Firisbusiness%5Fcom%2FDocumentslog.zip (37.0 KB)
Hi @Amjad_Sahi,

As you said I have attached .Net application zip file. Also mention the required thing in comments which you can refer to understand the code. As I have no rights to give you my one drive credential, I have mentioned in comment what is required for one drive access.

For Graph Api you can use below link.

10

@Coder365,

I tried to get the attachment(s) but it requires credentials (id and password). In short, I cannot download your attachment(s). Please upload to some free file sharing service (Google drive, dropbox, etc.) and share your sample project/app seamlessly (without needing us to put credentials). We will download your sample and then evaluate your issue. Please upload standalone (runnable) VS.NET application, so we don’t need to add libraries (external or third party) to execute your solution.

11

Please find the link
Thanks in advance

12

Hi @Coder365
I ran your program initially and encountered some problems:

1, line14, var tempPath = @“D:\NetCore\Problems\2022\log.xlsx”; //c:\chartTemp.xlsx
I changed “c:\chartTemp.xlsx” to “log.xlsx”, which is the file you shared (37.0KB), it is right?

2, Line 18, save file to “chartcreated.xlsx”, You mean that “chartcreated.xlsx” is wrong, right? I need to upload “chartcreated.xlsx” and the result is not correct, but if I upload “log.xlsx”, the result is correct?

3, line 38, how can I get the “onedrive login session key”, is it a key of OFFICE365 or Azure or Microsoft Graph API? Can you show me a page or steps that I can get the key, This key seems to be very important for the verification we are about to perform. Without it we can’t confirm the feedback from the cloud service.

13

@Coder365,

I also tested your scenario/case using your sample project with your template file (you previously shared). But I could not evaluate your issue using your sample project due to unauthorized access and got “OfficeSessionExpired” error as I have no rights to access/use Microsoft Graph APIs. I then evaluated your issue by adding the following lines of code after the line wtemp.Save(path1); in your sample application:

Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(path1);
Console.WriteLine("Chart count: " + workbook.Worksheets[0].Charts.Count);//2 -Ok
string outText = "";
string fullText = "";
foreach (Aspose.Cells.Charts.Chart chart in workbook.Worksheets[0].Charts)
{

outText = "Chart name: " + chart.Name + Environment.NewLine + "Chart type: " + chart.Type + Environment.NewLine + "Series values: " + chart.NSeries[0].Values + Environment.NewLine + "Category data: " + chart.NSeries.CategoryData + Environment.NewLine + Environment.NewLine;
fullText = fullText + outText;                               

}

// Create a file to write to.
File.WriteAllText("testFile.txt", fullText);
........

Both charts are read fine with relevant data. Please find attached the text file, which is fine tuned.
testFile.zip (282 Bytes)

14

Thanks @leoluo and @Amjad_Sahi,
you can refer below link for access token which is session key in my case.

Thanks again

15

@Coder365,

Thanks for link to get access token.

We will try to check your scenario/case. But could you please pay attention and test to what we have tested/confirmed so far (please check my previous reply).

I then evaluated your issue by adding the following lines of code after the line wtemp.Save(path1); in your sample application:

Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(path1);
Console.WriteLine("Chart count: " + workbook.Worksheets[0].Charts.Count);//2 -Ok
string outText = "";
string fullText = "";
foreach (Aspose.Cells.Charts.Chart chart in workbook.Worksheets[0].Charts)
{

outText = "Chart name: " + chart.Name + Environment.NewLine + "Chart type: " + chart.Type + Environment.NewLine + "Series values: " + chart.NSeries[0].Values + Environment.NewLine + "Category data: " + chart.NSeries.CategoryData + Environment.NewLine + Environment.NewLine;
fullText = fullText + outText;                               

}

// Create a file to write to.
File.WriteAllText("testFile.txt", fullText);
........

Both charts are read fine with relevant data. Please find attached the text file, which is fine tuned.

Apparently, the issue might be with using or accessing Microsoft Graph to read saved Excel file (by Aspose.Cells) in your environment, so you may look into it by yourselves.

16

Yes I checked your text file.
In that I have one small doubt. For second chart why categorydata is showing empty?
More on that, can you suggest what should I do here to achieve this functionality because Graph Api team saying there is no issue from there end? Is that other way in aspose for saving where we can maintain metadata of file?

Thanks

17

@Coder365,

I checked it for the second chart in MS Excel manually and found there is no horizontal category axis range (set), it is also blank. I guess MS Excel sets (1,2,3,4) as default data when there is no range (of cells) set.

18

Hi @Coder365
Please take a look at my Screenshot, I think I’m getting close to the environment you’re describing, sorry for the localization style in the screenshot, this is a Microsoft page display automatically, I can’t adjust it.
I tried to put some of the ids in the screenshots into your code, after“Bearer,” but so far all I’ve got is“HttpStatusCode.Unauthorized.”

Could you point out what id I should use, please. Or did I choose the wrong registration page?20221228.png (50.6 KB)

19

Hi @leoluo,
Please find below link in that there is one thread where it shows how to get access token.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/751009f2-f82f-47b8-93b5-5ea2630ec1bd/upload-file-gt-4mb-to-onedrive-using-graph-api?forum=csharpgeneral

20

@Coder365,

Thanks for sharing the thread link.

We will go through the thread and then do the needful if we could overpass the issue and get the access token.

We will get back to you soon.