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

rowan_growan_g Member Posts: 47 Contributor II
edited July 2019 in Help
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
Tagged:

Answers

  • Mickey_EllisMickey_Ellis Member Posts: 1 Contributor I

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

  • Marco_BoeckMarco_Boeck Administrator, Moderator, Employee, Member, University Professor Posts: 1,993 RM Engineering
  • sylviashensylviashen RapidMiner Certified Analyst, Member Posts: 3 Contributor I
    Hi,

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

    Regards,
    Sylvia
  • kaymankayman Member Posts: 662 Unicorn
    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>

  • Telcontar120Telcontar120 Moderator, RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 1,635 Unicorn
    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.  
    Brian T.
    Lindon Ventures 
    Data Science Consulting from Certified RapidMiner Experts
Sign In or Register to comment.