|
By: Alejandro
Date: 2012-10-24
Time: 21:25
|
Embedded Excel in powerpoint for a chart
Hi, I'm continuing to use your Great product, but now I'm stuck again.
I have a Powepoint template that include a couple barcharts made from an Excel File.
So I've opened the embedded excel file in powerpont, replace the data with [onshow.xxx] variables, close it, and try it, but it didn't work.
Checking the inside of the pptx file, I've found that under the folder ppt there is a subfolder called embeddings and, inside that folder there is a .xlsx file with the data.
So I decided to put a
$TBS->LoadTemplate('#ppt/embeddings/Hoja_de_c_lculo_de_Microsoft_Excel1.xlsx');
|
but still doesnt work
How can I make this work?
Thx on advance for your help
Regards
Alejandro |
|
By: Alejandro
Date: 2012-10-25
Time: 20:17
|
Re: Embedded Excel in powerpoint for a chart
Hi, I keep working on this.
I've test that I can generate a proper xlsx file, so I decided to do the following:
Manully extracted the xlsx file in the powepoint and called hoja.xlsx
Use TBS to create a new temp file with the xlsx file updated.
Replace the xlsx file inside the powerpoint with the recently created temp file.
This is my code:
$archive_name="#ppt/embeddings/Hoja_de_c_lculo_de_Microsoft_Excel1.xlsx";
$temp_file="/tmp/tmp.xlsx";
$TBS->LoadTemplate('hoja.xlsx');
$TBS->LoadTemplate('#xl/sharedStrings.xml');
$TBS->Show(OPENTBS_FILE,$temp_file);
unset($TBS);
$TBS = new clsTinyButStrong();
$TBS->Plugin(TBS_INSTALL, OPENTBS_PLUGIN);
$TBS->LoadTemplate("Plantilla_org.pptx");
$TBS->Plugin(OPENTBS_DELETEFILE, $archive_name,$temp_file, $DataType=OPENTBS_FILE, $Compress=true);
$TBS->Show(OPENTBS_DOWNLOAD, "test.pptx");
|
Well, when I get to OPENTBS_DELETEFILE it says that
TinyButStrong Error OpenTBS Plugin: File "#ppt/embeddings/Hoja_de_c_lculo_de_Microsoft_Excel1.xlsx" is not found in the Central Directory. The process is ending, unless you set NoErr property to true.
|
And I promise it's there!!!
To verify I've done:
$archive_name="#ppt/embeddings/Hoja_de_c_lculo_de_Microsoft_Excel1.xlsx";
$temp_file="/tmp/tmp.xlsx";
$TBS->LoadTemplate('hoja.xlsx');
$TBS->LoadTemplate('#xl/sharedStrings.xml');
$TBS->Show(OPENTBS_FILE,$temp_file);
unset($TBS);
$TBS = new clsTinyButStrong();
$TBS->Plugin(TBS_INSTALL, OPENTBS_PLUGIN);
$TBS->LoadTemplate("Plantilla_org.pptx");
echo "Test 1\n";
$TBS->LoadTemplate($archive_name);
echo "Test 2\n";
$TBS->Plugin(OPENTBS_DELETEFILE, $archive_name,$temp_file, $DataType=OPENTBS_FILE, $Compress=true);
echo "Test 3\n";
$TBS->Show(OPENTBS_DOWNLOAD, "test.pptx");
|
And this is the output:
Test 1
Test 2
TinyButStrong Error OpenTBS Plugin: File "#ppt/embeddings/Hoja_de_c_lculo_de_Microsoft_Excel1.xlsx" is not found in the Central Directory. The process is ending, unless you set NoErr property to true.
|
Any clue?
Thx Again |
|
By: Skrol29
Date: 2012-10-26
Time: 03:12
|
Re: Embedded Excel in powerpoint for a chart
Hi Alejandro,
They are several mistakes.
First the name of the sub file is not "#ppt/embeddings/Hoja_de_c_lculo_de_Microsoft_Excel1.xlsx" but "ppt/embeddings/Hoja_de_c_lculo_de_Microsoft_Excel1.xlsx" (without the #).
The # prefix is only for saying method LoadTemplate() to load a sub-file.
Also: it is not OPENTBS_DELETEFILE but OPENTBS_REPLACEFILE. The manual does contains an error abut this. I just fixed it in the beta released.
Also (the most important) : the embedded XLSX file is not the actual data displayed by the chart. This file is only a commodity for editing data. Actual data are in the sub-file "ppt/charts/chart1.xml".
But you can use command OPENTBS_CHART as for DOCX documents. This command merges the data in the chart, and cut the relation with the XLSX file.
I've tested with OpenTBS 1.8.0-beta, it works. It may work with the previous version.
|
|
By: Alejandro
Date: 2012-10-26
Time: 09:38
|
Re: Embedded Excel in powerpoint for a chart
thx, I'm going to test it rigth away
|
|
By: Alejandro
Date: 2012-10-27
Time: 23:59
|
Re: Embedded Excel in powerpoint for a chart
OK, I've test both things.
I've removed the "#" in the file name, and now it works, but when I open powerpoint it says it's a "corrupted file". If I click on "correct the file" It will open the powerpoint and, as you said, the chart is not updated. If I click on the chart, then it gets updated. Ugly, but works :)
So, now I'm trying to use de CHART functions but once again I'm stuck. (sorry).
The chart in my powerpoint its a stacked bar chart that representes percentajes (the full bar si splited in 2 series that sums 100)
I'm able to pass one value for the bar, but, how can I pass the other two values??
I've tried this
$ser1=array("cat1" =>"20","cat2"=>"60","cat3"=>"50");
$ser2=array("cat1" =>80,"cat2"=>40,"cat3"=>50);
$TBS->PlugIn(OPENTBS_CHART, "chart1", 1, $ser1, $leg);
$TBS->PlugIn(OPENTBS_CHART, "chart1", 2, $ser2, $leg);
|
but it will only use values on $ser1
Any clue...
Thx again |
|
By: Skrol29
Date: 2012-10-28
Time: 10:22
|
Re: Embedded Excel in powerpoint for a chart
> but it will only use values on $ser1
Do you mean that only series 1 is merged ?
Your snippet should work. Can you send to me the sub-file "ppt/charts/chart1.xml" corresponding to your chart ?
|
|
By: Alejandro
Date: 2012-10-29
Time: 12:59
|
Re: Embedded Excel in powerpoint for a chart
Sure. This is my full code (just in case)
$ser1=array("cat1" =>20,"cat2"=>60,"cat3"=>50);
$ser2=array("cat1" =>70,"cat2"=>20,"cat3"=>25);
$ser3=array("cat1" =>0,"cat2"=>20,"cat3"=>25);
$leg=array("Ok","KO","Unfinished");
$TBS = new clsTinyButStrong();
$TBS->Plugin(TBS_INSTALL, OPENTBS_PLUGIN);
$TBS->LoadTemplate("Plantilla_org.pptx");
$TBS->PlugIn(OPENTBS_CHART, "chart1", 1, $ser1, $leg);
$TBS->PlugIn(OPENTBS_CHART, "chart1", 2, $ser2, $leg);
$TBS->PlugIn(OPENTBS_CHART, "chart1", 3, $ser3, $leg);
$TBS->Show(OPENTBS_DOWNLOAD, "test.pptx");
|
And this is chart1.xml
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
- <c:chartSpace xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<c:date1904 val="0" />
<c:lang val="es-ES" />
<c:roundedCorners val="0" />
- <mc:AlternateContent xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006">
- <mc:Choice Requires="c14" xmlns:c14="http://schemas.microsoft.com/office/drawing/2007/8/2/chart">
<c14:style val="102" />
</mc:Choice>
- <mc:Fallback>
<c:style val="2" />
</mc:Fallback>
</mc:AlternateContent>
<c:clrMapOvr bg1="lt1" tx1="dk1" bg2="lt2" tx2="dk2" accent1="accent1" accent2="accent2" accent3="accent3" accent4="accent4" accent5="accent5" accent6="accent6" hlink="hlink" folHlink="folHlink" />
- <c:chart>
<c:autoTitleDeleted val="0" />
- <c:plotArea>
<c:layout />
- <c:barChart>
<c:barDir val="bar" />
<c:grouping val="stacked" />
<c:varyColors val="0" />
- <c:ser>
<c:idx val="0" />
<c:order val="0" />
- <c:tx>
- <c:strRef>
<c:f>Disp1!$B$10</c:f>
- <c:strCache>
<c:ptCount val="1" />
- <c:pt idx="0">
<c:v>Finalizadas</c:v>
</c:pt>
</c:strCache>
</c:strRef>
</c:tx>
- <c:spPr>
- <a:solidFill>
- <a:schemeClr val="accent3">
<a:lumMod val="75000" />
</a:schemeClr>
</a:solidFill>
</c:spPr>
<c:invertIfNegative val="0" />
- <c:dLbls>
<c:showLegendKey val="0" />
<c:showVal val="1" />
<c:showCatName val="0" />
<c:showSerName val="0" />
<c:showPercent val="0" />
<c:showBubbleSize val="0" />
<c:showLeaderLines val="0" />
</c:dLbls>
- <c:cat>
- <c:strRef>
<c:f>Disp1!$C$9:$E$9</c:f>
- <c:strCache>
<c:ptCount val="3" />
- <c:pt idx="0">
<c:v>Oficinas</c:v>
</c:pt>
- <c:pt idx="1">
<c:v>Puestos</c:v>
</c:pt>
- <c:pt idx="2">
<c:v>Pruebas</c:v>
</c:pt>
</c:strCache>
</c:strRef>
</c:cat>
- <c:val>
- <c:numRef>
<c:f>Disp1!$C$10:$E$10</c:f>
- <c:numCache>
<c:formatCode>0.0%</c:formatCode>
<c:ptCount val="3" />
- <c:pt idx="0">
<c:v>0.01</c:v>
</c:pt>
- <c:pt idx="1">
<c:v>0.04</c:v>
</c:pt>
- <c:pt idx="2">
<c:v>7.0000000000000007E-2</c:v>
</c:pt>
</c:numCache>
</c:numRef>
</c:val>
</c:ser>
- <c:ser>
<c:idx val="1" />
<c:order val="1" />
- <c:tx>
- <c:strRef>
<c:f>Disp1!$B$11</c:f>
- <c:strCache>
<c:ptCount val="1" />
- <c:pt idx="0">
<c:v>Iniciadas</c:v>
</c:pt>
</c:strCache>
</c:strRef>
</c:tx>
- <c:spPr>
- <a:solidFill>
<a:schemeClr val="accent1" />
</a:solidFill>
</c:spPr>
<c:invertIfNegative val="0" />
- <c:dLbls>
<c:showLegendKey val="0" />
<c:showVal val="1" />
<c:showCatName val="0" />
<c:showSerName val="0" />
<c:showPercent val="0" />
<c:showBubbleSize val="0" />
<c:showLeaderLines val="0" />
</c:dLbls>
- <c:cat>
- <c:strRef>
<c:f>Disp1!$C$9:$E$9</c:f>
- <c:strCache>
<c:ptCount val="3" />
- <c:pt idx="0">
<c:v>Oficinas</c:v>
</c:pt>
- <c:pt idx="1">
<c:v>Puestos</c:v>
</c:pt>
- <c:pt idx="2">
<c:v>Pruebas</c:v>
</c:pt>
</c:strCache>
</c:strRef>
</c:cat>
- <c:val>
- <c:numRef>
<c:f>Disp1!$C$11:$E$11</c:f>
- <c:numCache>
<c:formatCode>0.0%</c:formatCode>
<c:ptCount val="3" />
- <c:pt idx="0">
<c:v>0.02</c:v>
</c:pt>
- <c:pt idx="1">
<c:v>0.05</c:v>
</c:pt>
- <c:pt idx="2">
<c:v>0.08</c:v>
</c:pt>
</c:numCache>
</c:numRef>
</c:val>
</c:ser>
- <c:ser>
<c:idx val="2" />
<c:order val="2" />
- <c:tx>
- <c:strRef>
<c:f>Disp1!$B$12</c:f>
- <c:strCache>
<c:ptCount val="1" />
- <c:pt idx="0">
<c:v>Sin Comenzar</c:v>
</c:pt>
</c:strCache>
</c:strRef>
</c:tx>
- <c:spPr>
- <a:solidFill>
<a:srgbClr val="B96563" />
</a:solidFill>
</c:spPr>
<c:invertIfNegative val="0" />
- <c:dLbls>
- <c:dLbl>
<c:idx val="0" />
- <c:layout>
- <c:manualLayout>
<c:x val="4.7222222222222221E-2" />
<c:y val="1.1759259259259259E-2" />
</c:manualLayout>
</c:layout>
<c:showLegendKey val="0" />
<c:showVal val="1" />
<c:showCatName val="0" />
<c:showSerName val="0" />
<c:showPercent val="0" />
<c:showBubbleSize val="0" />
</c:dLbl>
- <c:dLbl>
<c:idx val="1" />
- <c:layout>
- <c:manualLayout>
<c:x val="6.1111111111111109E-2" />
<c:y val="0" />
</c:manualLayout>
</c:layout>
<c:showLegendKey val="0" />
<c:showVal val="1" />
<c:showCatName val="0" />
<c:showSerName val="0" />
<c:showPercent val="0" />
<c:showBubbleSize val="0" />
</c:dLbl>
- <c:dLbl>
<c:idx val="2" />
- <c:layout>
- <c:manualLayout>
<c:x val="6.3888888888888884E-2" />
<c:y val="0" />
</c:manualLayout>
</c:layout>
<c:showLegendKey val="0" />
<c:showVal val="1" />
<c:showCatName val="0" />
<c:showSerName val="0" />
<c:showPercent val="0" />
<c:showBubbleSize val="0" />
</c:dLbl>
<c:showLegendKey val="0" />
<c:showVal val="1" />
<c:showCatName val="0" />
<c:showSerName val="0" />
<c:showPercent val="0" />
<c:showBubbleSize val="0" />
<c:showLeaderLines val="0" />
</c:dLbls>
- <c:cat>
- <c:strRef>
<c:f>Disp1!$C$9:$E$9</c:f>
- <c:strCache>
<c:ptCount val="3" />
- <c:pt idx="0">
<c:v>Oficinas</c:v>
</c:pt>
- <c:pt idx="1">
<c:v>Puestos</c:v>
</c:pt>
- <c:pt idx="2">
<c:v>Pruebas</c:v>
</c:pt>
</c:strCache>
</c:strRef>
</c:cat>
- <c:val>
- <c:numRef>
<c:f>Disp1!$C$12:$E$12</c:f>
- <c:numCache>
<c:formatCode>0.0%</c:formatCode>
<c:ptCount val="3" />
- <c:pt idx="0">
<c:v>0.03</c:v>
</c:pt>
- <c:pt idx="1">
<c:v>0.06</c:v>
</c:pt>
- <c:pt idx="2">
<c:v>0.09</c:v>
</c:pt>
</c:numCache>
</c:numRef>
</c:val>
</c:ser>
- <c:dLbls>
<c:showLegendKey val="0" />
<c:showVal val="0" />
<c:showCatName val="0" />
<c:showSerName val="0" />
<c:showPercent val="0" />
<c:showBubbleSize val="0" />
</c:dLbls>
<c:gapWidth val="150" />
<c:overlap val="100" />
<c:axId val="81490688" />
<c:axId val="81492224" />
</c:barChart>
- <c:catAx>
<c:axId val="81490688" />
- <c:scaling>
<c:orientation val="minMax" />
</c:scaling>
<c:delete val="0" />
<c:axPos val="l" />
<c:majorTickMark val="out" />
<c:minorTickMark val="none" />
<c:tickLblPos val="nextTo" />
<c:crossAx val="81492224" />
<c:crosses val="autoZero" />
<c:auto val="1" />
<c:lblAlgn val="ctr" />
<c:lblOffset val="100" />
<c:noMultiLvlLbl val="0" />
</c:catAx>
- <c:valAx>
<c:axId val="81492224" />
- <c:scaling>
<c:orientation val="minMax" />
<c:max val="1" />
</c:scaling>
<c:delete val="0" />
<c:axPos val="b" />
- <c:majorGridlines>
- <c:spPr>
- <a:ln>
<a:prstDash val="dash" />
</a:ln>
</c:spPr>
</c:majorGridlines>
<c:numFmt formatCode="0.0%" sourceLinked="1" />
<c:majorTickMark val="out" />
<c:minorTickMark val="none" />
<c:tickLblPos val="nextTo" />
<c:crossAx val="81490688" />
<c:crosses val="autoZero" />
<c:crossBetween val="between" />
</c:valAx>
</c:plotArea>
- <c:legend>
<c:legendPos val="b" />
<c:layout />
<c:overlay val="0" />
</c:legend>
<c:plotVisOnly val="1" />
<c:dispBlanksAs val="gap" />
<c:showDLblsOverMax val="0" />
</c:chart>
- <c:externalData r:id="rId2">
<c:autoUpdate val="0" />
</c:externalData>
</c:chartSpace>
|
I'm specting to get somthing like this:
http://imageshack.us/photo/my-images/820/stackedchart.png/
thx again!
|
|
By: Skrol29
Date: 2012-10-29
Time: 19:30
|
Re: Embedded Excel in powerpoint for a chart
Hi,
I have problems to rebuild the pptx.
Can you send the full pptx it to me ? At least with one slide containing the slide.
|
|
By: Alejandro
Date: 2012-10-29
Time: 20:27
|
Re: Embedded Excel in powerpoint for a chart
Sure, I'll send it by email.
|
|
By: Skrol29
Date: 2012-10-29
Time: 21:56
|
Re: Embedded Excel in powerpoint for a chart
Hi Alejandro,
Thanks for your email. I've teste the PPTX and I've seen where are the mistakes.
First, the series caption must not be an array but a string.
Secondly, your data are formated as percentage but the inner values are coefficients smaller than 1.
So if yu simply change like the fallowing, it should work:
$ser1=array("cat1" =>0.20,"cat2"=>0.60,"cat3"=>0.50);
$ser2=array("cat1" =>0.70,"cat2"=>0.20,"cat3"=>0.25);
$ser3=array("cat1" =>0.0,"cat2"=>0.20,"cat3"=>0.25);
$TBS->PlugIn(OPENTBS_CHART, "chart1", 1, $ser1, "my series 1");
$TBS->PlugIn(OPENTBS_CHART, "chart1", 2, $ser2, "my series 2");
$TBS->PlugIn(OPENTBS_CHART, "chart1", 3, $ser3, "my series 3");
|
|
|
Posting in progress.
Please wait...
|
