Processing and creating Excel documents using Python

Excel sheets may be a boon to store and access data but can become a burden, especially when the amount of data to manage goes beyond human limits and the job is mundane. Why not automate our tasks? Let’s look at a way using python. Other applications are processing a job for a lot of entries without any manual intervention

The libraries to look at are xlrd and xlwt-
xlrd helps to read data from spreadsheets
xlwt helps to write/format data

First, we will start with the glossary- An excel document is a Workbook, a sheet inside an excel document is called a Worksheet. Rows and columns are numbered from 0, ie the row and column number in these libraries will be 1 less than the actual number in the spreadsheet software e.g. Microsoft Word

Let’s first start with xlrd

  import xlrd

Now let’s open a workbook and a worksheet

  Book = xlrd.open_workbook( 'foobar.xls' )
  WorkSheet = Book.sheet_by_name( 'Sheet 1' )

We can get the total number of filled rows using

  num_row = WorkSheet.nrows-1

Note that I am using a -1 from the number of rows since the first row starts from 0

Now, we can iterate over the rows and get the desired data:

  row = 0
  while row < num_row:
      value = WorkSheet.cell_value( row, 2 )  //Get the 3rd column
      row += 1
      // Do something here

Now, let’s go to editing or processing documents.

  import xlwt

Open a new workbook and add a new sheet to it

  outbook = xlwt.Workbook()
  sheet = outbook.add_sheet( "test" )

Now, we can write to the sheet using

  sheet.write( row, col, value )

We can also add some style to the cell. This is how to add a foreground color to the cell:

  style = xlwt.easyxf( 'pattern: pattern solid, fore_colour red;' )
  sheet.write( row, col, value ,style )

I faced this error while applying styles

   ValueError: More than 4094 XFs (styles)
   

Here is a quick fix. Initialize the workbook using

  outbook = xlwt.Workbook( style_compression = 2 )

Finally save this sheet using

  outbook.save( "foo_out.xls" )

Have a great day 🙂