Chart Data Source is corrupted after merge

Aspose.Cells Product Family
1
<!–[if gte mso 10]> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:Arial; mso-bidi-theme-font:minor-bidi;}

<![endif]–>

Hi,

In our code we merging excel files into one file. One of the files contains the chart and after merge this chart gets wrong data source. Please take a look on attached files before merge and also take a look on code:

private void Button_Click_3(object sender, RoutedEventArgs e)

{

Workbook1 workbook = null;

List<string> paths = new List<string>{"1.xlsx","2.xlsx"};

foreach (var excelPath in paths)

{

if (excelPath != null && File.Exists(excelPath))

{

if (workbook == null)

workbook = new Workbook1(excelPath);

else

{

var opened = new Workbook1(excelPath);

foreach (Worksheet ws in opened.Worksheets)

{

string uniqueName = ws.Name;

workbook.Worksheets.Add(uniqueName);

workbook.Worksheets[uniqueName].Copy(ws);

}

}

}

}

if (workbook != null)

{

if (workbook.Worksheets.Count > 0)

workbook.Worksheets[0].IsSelected = true;

workbook.Save("Merged.xlsx",SaveFormat.Xlsx);

}

}

2

Hi,


I am not sure what do you mean by Workbook1 workbook in your code. I have adjusted your code a bit and tried, it works fine I think with your files.

I am using the latest version/fix. Please download and try it: Aspose.Cells for .NET v7.0.3.2

Sample code:

Workbook workbook1 = null;
List paths = new List { “e:\test2\1.xlsx”, “e:\test2\2.xlsx” };
foreach (var excelPath in paths)
{

if (excelPath != null && File.Exists(excelPath))
{
if (workbook1 == null)
workbook1 = new Workbook(excelPath);
else
{
var opened = new Workbook(excelPath);

foreach (Worksheet ws in opened.Worksheets)
{
string uniqueName = ws.Name;

workbook1.Worksheets.Add(uniqueName);

workbook1.Worksheets[uniqueName].Copy(ws);
}
}
}
}


if (workbook1 != null)
{
if (workbook1.Worksheets.Count > 0)
workbook1.Worksheets[0].IsSelected = true;

workbook1.Save(“e:\test2\Merged.xlsx”, SaveFormat.Xlsx);
}

The output file is fine having content and charts.

If you find the issue, kindly give us screen shots where is the issue and also you may correct the above code accordingly, so that we could find the issue on our end.

thank you.

3

Thank you for the answer. Indeed this version of Aspose dll works fine and we had the version 7.0.2.0, so probably this issue was fixed in newer version.

4
<!–[if gte mso 10]> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:Arial; mso-bidi-theme-font:minor-bidi;}

<![endif]–>

Hi,
I've re-checked this issue and it looks like there is a bug.

We run the version 7.0.3.2 and there are two attached Excel files. Please don’t open and save them in MS Office Excel before running in your code (cause it fixes somehow the issue).

After running the code above and merging into a new file you will see the empty chart (it doesn’t have the Data Source set). If you open these files in MS Office first, then save them and run the code everything is fine.


Kind Regards,

Yevgeny

5
Hi,

Thanks for the files.
Now, I can find the issue using your newly attached files with the sample code. If I open/save the files before using the code, it works fine as you mentioned. The chart is blank in the merged file.

Sample code:

Workbook workbook1 = null;
List paths = new List { "e:\\test2\\1_new.xlsx", "e:\\test2\\2_new.xlsx" };
foreach (var excelPath in paths)
{

if (excelPath != null && File.Exists(excelPath))
{
if (workbook1 == null)
workbook1 = new Workbook(excelPath);
else
{
var opened = new Workbook(excelPath);


foreach (Worksheet ws in opened.Worksheets)
{
string uniqueName = ws.Name;

workbook1.Worksheets.Add(uniqueName);

workbook1.Worksheets[uniqueName].Copy(ws);
}
}
}
}


if (workbook1 != null)
{
if (workbook1.Worksheets.Count > 0)
workbook1.Worksheets[0].IsSelected = true;

workbook1.Save("e:\\test2\\Merged_new.xlsx", SaveFormat.Xlsx);
}

I have logged a ticket with an id: CELLSNET-40130. We will look into it soon.

Thank you.
6

Hi,

After further evaluation of your issue, we come to know that before copying chart, you should copy chart data source first. For this case, you should copy “data_BrandShield Result Summa” sheet (this sheet is hidden) before “BrandShield Result Summary” sheet. One bypass solution can be: in 2_new.xlsx, move “data_BrandShield Result Summa” sheet before “BrandShield Result Summary” sheet. It will work fine.

Thank you.

7

Hi,

If you want to merge the files, please use Workbook.Combine method,see following codes:

foreach (var excelPath in paths)
{
if (excelPath != null && File.Exists(excelPath))
{
if (workbook1 == null)
workbook1 = new Workbook(excelPath);
else
{
var opened = new Workbook(excelPath);
workbook1.Combine(opened);
//foreach (Worksheet ws in opened.Worksheets)
//{
// string uniqueName = ws.Name;
// workbook1.Worksheets.Add(uniqueName);
// workbook1.Worksheets[uniqueName].Copy(ws);
//}
}
}
}

In MS Excel, if you copy a worksheet with a chart to another file, the data source of the chart will be external link and link to the source file. We do not support it now.

8

Hi,

You have a bug in Combine code when you combine sheets with the same name, it doesn’t keep names correctly.
Code:

using Workbook1 = Aspose.Cells.Workbook;



 private void Button_Click_6(object sender, RoutedEventArgs e)

{

List<string> paths = new List<string> { @“C:\temp\AsposeCombine\1.xlsx”, @“C:\temp\AsposeCombine\2.xlsx”, @“C:\temp\AsposeCombine\3.xlsx” };

        <span style="color:#2b91af;">Workbook1</span> workbook1 = <span style="color:blue;">null</span>;

        <span style="color:blue;">foreach</span> (<span style="color:blue;">var</span> excelPath <span style="color:blue;">in</span> paths)
        {
            <span style="color:blue;">if</span> (excelPath != <span style="color:blue;">null</span> && <span style="color:#2b91af;">File</span>.Exists(excelPath))
            {
                <span style="color:blue;">if</span> (workbook1 == <span style="color:blue;">null</span>)
                    workbook1 = <span style="color:blue;">new</span> <span style="color:#2b91af;">Workbook1</span>(excelPath);
                <span style="color:blue;">else</span>
                {
                    <span style="color:blue;">var</span> opened = <span style="color:blue;">new</span> <span style="color:#2b91af;">Workbook1</span>(excelPath);
                    workbook1.Combine(opened);
                }
            }
        }

        workbook1.Save(<span style="color:#a31515;">@"C:\temp\AsposeCombine\Combined.xlsx"</span>);
    }</pre>Please see attached files.<br>
9

Hi,


How could you have worksheets with the same in a workbook. I think this is not a big deal, you may try to use your own code and use worksheet.Name to change/set your desired names accordingly for those worksheets.

Thank you.