How to create and put data in excel file in Robot Framework?

Question

I want to create an Excel file and add input data into it using Robot Framework. So I added the Excel Library. Here's the code I use, I got an error message saying

IndexError: list index out of range

So I commented the 3rd line and got the column and row count which are 0.

Create Excel Workbook   NewExcelSheet               
Save Excel  NewExcel.xls    default=True            
Open Excel  NewExcel.xls    default=True            
Put Number To Cell  NewExcelSheet   1   1   90  
${Col}= Get Column Count    NewExcelSheet           
${Row}= Get Row Count   NewExcelSheet           
Log ${Col}              
Log ${Row}  

Why am I getting 0 as values for the columns and rows and why am I not able to add values in it?

Answer 1

From the documentation it appears as though no function in ExcelLibrary creates an excel sheet with any rows or columns. That's why you are getting a row count and column count of 0. It's also why you are unable to change [1,1] to 90.

The raised exception (use the robot framework argument --loglevel=DEBUG) indicates that "Put Number To Cell" does not dynamically create the needed rows and columns.

Traceback (most recent call last):
  File "c:\python27_32\lib\site-packages\ExcelLibrary\ExcelLibrary.py", line 370, in put_number_to_cell
    cell = self.wb.get_sheet(my_sheet_index).cell(int(row), int(column))
  File "c:\python27_32\lib\site-packages\xlrd\sheet.py", line 397, in cell
    xfx = self.cell_xf_index(rowx, colx)
  File "c:\python27_32\lib\site-packages\xlrd\sheet.py", line 423, in cell_xf_index
    xfx = self._cell_xf_indexes[rowx][colx]

I would recommend looking into a python library and creating your own excel adapter module.

For example using OpenPyXl:

make_excel.py:

from openpyxl import Workbook, load_workbook
from contextlib import closing

def make_excel_file(file_name):
    with closing(Workbook()) as wb:
        wb.save(file_name)

def add_value(file_name, cell_cords, value):
    with closing(load_workbook(filename=file_name)) as wb:
        ws = wb.active
        ws[cell_cords] = value
        wb.save(file_name)

make_excel.robot:

*** Settings *** 
Library  make_excel

*** Test Cases ***
Test
    Make Excel File   NewExcel.xlsx  1000
    Add Value  NewExcel.xlsx  A1  90