"Export Excel with multiple sheets"

swasswas Member Posts: 2 Learner I
edited June 2019 in Help

Hello,

i can't find a solution for my problem. So i hope you guys could help me.

I'm creating two (or more) different results with rapidminer and i'd like them to write into one excel file in different sheets.

 

I'd really appreciate a hint how i could achieve this.

 

Kind regards,

swas

 

Edit:

I'm currently experimenting with macros and loops. But i'm unsure if that's the right way to do it.

I'd be also curious if it's possible to get the name of an retrieved dataset.

Tagged:

Answers

  • Telcontar120Telcontar120 Moderator, RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 1,635 Unicorn

    The Advanced Reporting Extension, which is available in the marketplace, allows you to do this. It is a paid extension but the cost is very reasonable.  If there is a way to do it with the basic RapidMiner operators, I don't know how.

     

     

    Brian T.
    Lindon Ventures 
    Data Science Consulting from Certified RapidMiner Experts
  • sgenzersgenzer Administrator, Moderator, Employee, RapidMiner Certified Analyst, Community Manager, Member, University Professor, PM Moderator Posts: 2,959 Community Manager

    hi @swas - so @Telcontar120 is correct. It should be easy but it's just not possible with the current tools. If you're really adventurous, you can see how I unzipped an XLSX file to do the opposite of what you're asking to get sheet names; it should work for your purposes: https://community.rapidminer.com/t5/RapidMiner-Studio-Forum/Extract-Sheet-name-from-an-Excel-file/td-p/44747

     

    Scott

     

     

  • lionelderkrikorlionelderkrikor Moderator, RapidMiner Certified Analyst, Member Posts: 1,195 Unicorn

    Hi again @swas,

     

    Here a process where each exampleset is copied in a different excel file using the Write Excel operator.

    It's not exactly what you want, but you can in Excel right click on a sheet and select export and then export the sheet to the excel file

    where you want to store all your example sets.

    Here the process : 

    <?xml version="1.0" encoding="UTF-8"?><process version="8.0.001">
    <context>
    <input/>
    <output/>
    <macros/>
    </context>
    <operator activated="true" class="process" compatibility="8.0.001" expanded="true" name="Process">
    <process expanded="true">
    <operator activated="true" class="operator_toolbox:create_exampleset_from_doc" compatibility="0.7.000" expanded="true" height="68" name="1" width="90" x="45" y="340">
    <parameter key="Input Csv" value="Att1,Att2&#10;1,4&#10;2,5&#10;3,6"/>
    </operator>
    <operator activated="true" class="operator_toolbox:create_exampleset_from_doc" compatibility="0.7.000" expanded="true" height="68" name="2" width="90" x="45" y="544">
    <parameter key="Input Csv" value="Att1,Att2&#10;1,8&#10;2,10&#10;3,12"/>
    </operator>
    <operator activated="true" class="subprocess" compatibility="8.0.001" expanded="true" height="124" name="Subprocess" width="90" x="380" y="442">
    <process expanded="true">
    <operator activated="true" class="operator_toolbox:get_source_of_object_as_macro" compatibility="0.7.000" expanded="true" height="68" name="Extract Last Modifying Operator (2)" width="90" x="112" y="34">
    <parameter key="macro name" value="sheetNumber"/>
    </operator>
    <operator activated="true" class="operator_toolbox:get_source_of_object_as_macro" compatibility="0.7.000" expanded="true" height="68" name="Extract Last Modifying Operator (3)" width="90" x="179" y="238">
    <parameter key="macro name" value="sheetNumber"/>
    </operator>
    <operator activated="true" class="collect" compatibility="8.0.001" expanded="true" height="103" name="Collect" width="90" x="380" y="136"/>
    <operator activated="true" class="loop_collection" compatibility="8.0.001" expanded="true" height="82" name="Loop Collection" width="90" x="514" y="136">
    <parameter key="set_iteration_macro" value="true"/>
    <parameter key="macro_name" value="sheetNumber"/>
    <process expanded="true">
    <operator activated="true" class="write_excel" compatibility="8.0.001" expanded="true" height="82" name="Write Excel" width="90" x="447" y="34">
    <parameter key="excel_file" value="C:\Users\Lionel\Documents\Formations_DataScience\Rapidminer\Tests_Rapidminer\Export_RM_%{sheetNumber}.xlsx"/>
    <parameter key="sheet_name" value="%{sheetNumber}"/>
    </operator>
    <operator activated="false" class="rmx_adv_reporting:open_report_excel" compatibility="2.1.693" expanded="true" height="103" name="Open Report (2)" width="90" x="447" y="187">
    <parameter key="form_template_file" value="C:\Users\Lionel\Documents\Formations_DataScience\Rapidminer\Tests_Rapidminer\Export_RM.xlsx"/>
    <parameter key="target_file" value="C:\Users\Lionel\Documents\Formations_DataScience\Rapidminer\Tests_Rapidminer\Export_RM_%{sheetNumber}.xlsx"/>
    <process expanded="true">
    <operator activated="true" class="rmx_adv_reporting:write_data_entry_excel" compatibility="2.1.693" expanded="true" height="68" name="Write Data Entry (2)" width="90" x="514" y="34">
    <parameter key="sheet" value="%{sheetNumber}"/>
    <parameter key="fit_to_range" value="true"/>
    <parameter key="ignore_names" value="false"/>
    </operator>
    <connect from_port="input 1" to_op="Write Data Entry (2)" to_port="example set"/>
    <connect from_op="Write Data Entry (2)" from_port="exampe set" to_port="output 1"/>
    <portSpacing port="source_input 1" spacing="0"/>
    <portSpacing port="source_input 2" spacing="0"/>
    <portSpacing port="sink_output 1" spacing="0"/>
    <portSpacing port="sink_output 2" spacing="0"/>
    </process>
    </operator>
    <connect from_port="single" to_op="Write Excel" to_port="input"/>
    <connect from_op="Write Excel" from_port="through" to_port="output 1"/>
    <portSpacing port="source_single" spacing="0"/>
    <portSpacing port="sink_output 1" spacing="0"/>
    <portSpacing port="sink_output 2" spacing="0"/>
    </process>
    </operator>
    <connect from_port="in 1" to_op="Extract Last Modifying Operator (2)" to_port="through"/>
    <connect from_port="in 2" to_op="Extract Last Modifying Operator (3)" to_port="through"/>
    <connect from_op="Extract Last Modifying Operator (2)" from_port="through" to_op="Collect" to_port="input 1"/>
    <connect from_op="Extract Last Modifying Operator (3)" from_port="through" to_op="Collect" to_port="input 2"/>
    <connect from_op="Collect" from_port="collection" to_op="Loop Collection" to_port="collection"/>
    <connect from_op="Loop Collection" from_port="output 1" to_port="out 1"/>
    <portSpacing port="source_in 1" spacing="0"/>
    <portSpacing port="source_in 2" spacing="0"/>
    <portSpacing port="source_in 3" spacing="0"/>
    <portSpacing port="sink_out 1" spacing="0"/>
    <portSpacing port="sink_out 2" spacing="0"/>
    <portSpacing port="sink_out 3" spacing="0"/>
    <portSpacing port="sink_out 4" spacing="0"/>
    </process>
    </operator>
    <connect from_op="1" from_port="output" to_op="Subprocess" to_port="in 1"/>
    <connect from_op="2" from_port="output" to_op="Subprocess" to_port="in 2"/>
    <connect from_op="Subprocess" from_port="out 1" to_port="result 3"/>
    <connect from_op="Subprocess" from_port="out 2" to_port="result 4"/>
    <connect from_op="Subprocess" from_port="out 3" to_port="result 5"/>
    <portSpacing port="source_input 1" spacing="0"/>
    <portSpacing port="sink_result 1" spacing="0"/>
    <portSpacing port="sink_result 2" spacing="0"/>
    <portSpacing port="sink_result 3" spacing="0"/>
    <portSpacing port="sink_result 4" spacing="0"/>
    <portSpacing port="sink_result 5" spacing="0"/>
    <portSpacing port="sink_result 6" spacing="0"/>
    </process>
    </operator>
    </process>

    NB : I try to do the same thing and "to tinker" with the Advanced Reporting Extension (the free features), but in fine

    RapidMiner raise an error or only the last excel file is created (the precedent are crushed).

     

    I hope this process (to adapt to your own project) will help you despite all.

     

    Regards,

     

    Lionel

  • SGolbertSGolbert RapidMiner Certified Analyst, Member Posts: 344 Unicorn

    One option would be to save multiple CSV files, and then combine them directly with excel. There are some macros going around that are fit for the task, take a look at:

     

    https://stackoverflow.com/questions/12162477/importing-multiple-csv-to-multiple-worksheet-in-a-single-workbook

     

    There also some plugins for Excel that can do it, but I don't know if they are free or not.

  • lionelderkrikorlionelderkrikor Moderator, RapidMiner Certified Analyst, Member Posts: 1,195 Unicorn

    Hi @swas,

     

    @SGolbert inspire me a solution to automatically import the sheets of the created Excel files by RapidMiner (process I share above) in one single Excel file :

     

    You can download this Excel file with macros by clicking on the following link : 

     

    https://drive.google.com/file/d/1W5gLA2J3xUh8W-sTMxN5KXgt1kZDez5g/view?usp=sharing

     

     

    1. You have first to select the path where your Excel files have been saved.

    Warning : All you excel file(s) have to be stored in the same path (which is normally the case after running the RapidMiner process) and no other Excel file(s) should be stored in this path

    2. You click on the 'Import Excel sheets' button

    3. Normally, the sheets of the saved Excel file(s) are imported in the Excel file.

     

    Regards,

     

    Lionel

     

    NB : We're getting away from RapidMiner but it's for a good cause

     

     

     

Sign In or Register to comment.