"Export Excel with multiple sheets"

swas

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.

Telcontar120

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.

 

 

sgenzer

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

 

 

lionelderkrikor

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

SGolbert

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.

lionelderkrikor

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