Extract data via condition¶
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¶
When you open the excel_ex_result.xls file, only data with a value of 0.5 or higher remains.