[SOLVED] Write Excel - how to write data to the same workbook but different shee

rowan_g

Hi,

I've got a couple of loop files operators reading excel and CSV files from within a zip file.
One of the steps in my process is writing the data into different sheets in Excel
My problem is that the write excel operator writes over the excel file rather than adding a sheet with a different name.

Any help would be greatly appreciated.

Cheers,


Edit: Solved - http://rapid-i.com/rapidforum/index.php/topic,6474.0.html

Mickey_Ellis

I have the same problem, but the link that directs to the solution is missing.  Can someone provide the solution?  Thanks in advance!

Marco_Boeck

Hi,

 

this one may help: http://community.rapidminer.com/t5/RapidMiner-Studio/Write-Excel-generate-quot-excel-file-name-quot-quot-sheet-name/m-p/23654#M17559

 

Regards,

Marco

sylviashen

Hi,

I got the same problem. But the link doesn't work now.
Anyone knows how to solve the problem? Many Thanks!

Regards,
Sylvia

kayman

Hi @sylviashen, if you are not afraid of python you can easily achieve this with xlsxwriter.

Find below an example using 2 imports (can be as many as you want) where each input is written to a different sheet

  <?xml version="1.0" encoding="UTF-8"?><process version="9.0.003">

  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="9.0.003" expanded="true" name="Process">
    <process expanded="true">
      <operator activated="true" class="python_scripting:execute_python" compatibility="8.2.000" expanded="true" height="124" name="Execute Python (2)" width="90" x="246" y="85">
        <parameter key="script" value="import pandas as pd&#10;import xlsxwriter&#10;&#10;def rm_main(data1, data2):&#10;&#10;    writer = pd.ExcelWriter('my_file.xlsx', engine='xlsxwriter')&#10;&#10;    # Write your DataFrame to a file   &#10;    data1.to_excel(writer, 'Page 1')  &#10;    data2.to_excel(writer, 'Page 2')&#10;&#10;    # Save the result &#10;    writer.save()&#10;&#10;    return"/>
      </operator>
      <connect from_port="input 1" to_op="Execute Python (2)" to_port="input 1"/>
      <connect from_port="input 2" to_op="Execute Python (2)" to_port="input 2"/>
      <portSpacing port="source_input 1" spacing="0"/>
      <portSpacing port="source_input 2" spacing="0"/>
      <portSpacing port="source_input 3" spacing="0"/>
      <portSpacing port="sink_result 1" spacing="0"/>
    </process>
  </operator>
</process>

Telcontar120

The Advanced Reporting Extension (available in the RapidMiner marketplace from @land)has some nice options for writing to Excel files, where you can specify not only the sheet but also specific cells for your output.  This is really handy if you have some reporting that is already built in Excel and you simply need to update the relevant data tables.  You might want to check it out.