Extract data via condition


_images/xlrd_xlwt_ex01_01.PNG

First, we created 10 rows, 10 columns of data with random values between 0 and 1 (excel_ex.xls).

Of these 10×10 data, let’s just import data with a value of at least 0.5 and create a new Excel file.


Example

import xlrd, xlwt

wb = xlrd.open_workbook('excel_ex.xls')
sheets = wb.sheets()
nsheets = wb.nsheets

wbwt = xlwt.Workbook(encoding='utf-8')
ws = wbwt.add_sheet('Sheet1', cell_overwrite_ok=True)

nrows = sheets[0].nrows
ncols = sheets[0].ncols

for i in range(nrows):
    for j in range(ncols):
        if sheets[0].cell_value(i, j) > 0.5:
            ws.write(i, j, sheets[0].cell_value(i, j))

wbwt.save('excel_ex_result.xls')

With this simple code, you can read the Excel file and import only the data that meets the condition and create a new file.


Description

import xlrd, xlwt

wb = xlrd.open_workbook('excel_ex.xls')
sheets = wb.sheets()
nsheets = wb.nsheets

First of all, import the xlrd, xlwt module.

Read the file ‘excel_ex.xls’ using the open_workbook(). open_workbook() returns an instance of the ‘Book’ class.

sheets() in this workbook(wb) returns a list of all sheets.

Use sheet_by_index() or sheet_by_name() if you want to import by the sheet’s index or name. Enter the index number or sheet name, respectively.

nsheets are the number of sheets.


wbwt = xlwt.Workbook(encoding='utf-8')
ws = wbwt.add_sheet('Sheet1', cell_overwrite_ok=True)

Use the Workbook class to create a workbook and set the encoding to ‘utf-8’.

Use the add_sheet() method to create a sheet.

Specify the name of the sheet (‘Sheet1’) for the first parameter.

Set cell_overwrite_ok to True to allow overwrite. If set to False, no more than one write is allowed.

add_sheet() method returns the added sheet, so ws represents this added sheet.


nrows = sheets[0].nrows
ncols = sheets[0].ncols

sheets have all the sheets of the Excel you’ve read. sheets[0].nrows, sheets[0].ncols are the number of rows and columns on the first sheet (sheets[0] ), respectively.


for i in range(nrows):
    for j in range(ncols):
        if sheets[0].cell_value(i, j) > 0.5:
            ws.write(i, j, sheets[0].cell_value(i, j))

For all data, if the value is greater than 0.5 (sheets[0].cell_value(i, j) > 0.5), write the data using write() method.

Place the row and column to be written to the first and second parameters of the write() method, and insert the data to be written to the third parameter (sheets[0].cell_value(i, j)).

sheets[0].cell_value(i, j) is the value in i-th row, j-th column of the first sheet of the Excel file you have read.


wbwt.save('excel_ex_result.xls')

Finally, save the newly created workbook (wbwt) as the name ‘excel_ex_result.xls’.


Results

_images/xlrd_xlwt_ex01_02.PNG

When you open the excel_ex_result.xls file, only data with a value of 0.5 or higher remains.

Prev