TinyButStrong - the PHP Template Engine
Categories > OpenTBS with PPTX >

Embedded Excel in powerpoint for a chart

The forum is closed. Please use Stack Overflow for submitting new questions. Use tags: tinybutstrong , opentbs

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");