<!–[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);
}
}
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.
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.
<!–[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
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.
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.
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.
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>
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.