Python processing Excel table

For the operation of Excel, you need to XLRD/XLWT these two modules, the following recommended a systematic study of the Web site:

Python operations Excel Read-write-using XLRD

Official documents

Python uses XLRD/XLWT to manipulate Excel

Read and write Excel files in Python

1 Download XLWT and XLRD under Windows

2. Unzip the xlrd-0.9.2.tar.gz to the specified folder

3. Under CMD console switch to the specified folder path above, enter command python setup.py install to complete the installation

Install Python under Linux or install PIP installation XLWT with PIP

Second, actual combat

Help a friend to deal with an Excel, fortunately the amount of data is small, tens of thousands of data. The requirements are as follows:

Specify three columns and then pick and choose both to satisfy >=1 or <=-1 all of their data into a new CSV table

The procedure is as follows:

#!/usr/bin/env python

#-*-Coding:utf-8-*-

# @Date: 2014-04-10 21:47:56

# @Function: Specify to select three columns and then pick and choose both to satisfy >=1 or <=-1 all of their data into a new CSV table

# @Author: Beginman

Import OS

Import string

Import xlrd

Import XLWT

Def get_data ():

"" Gets the Excel data source "" "

File = R ' C:\Users\Administrator\Desktop\pytool\xlrd\initial_log_data.xls ' # changed to its own path

filepath = raw_input (U ' please paste the xls file path in, if the program has already specified the file, press ENTER to continue: ')

Is_valid = False # Validate file

Try

filepath = [File, Filepath][filepath! = ']

Print filepath

# Determine if the path given is not in XLS format

If Os.path.isfile (filepath):

filename = os.path.basename (filepath)

If Filename.split ('. ') [1] = = ' xls ':

Is_valid = True

data = None

If Is_valid:

data = Xlrd.open_workbook (filepath)

Except Exception, E:

Print U ' you operation error:%s '%e

Return None

Return data

Def handle_data ():

"" "Processing Data" ""

data = Get_data ()

If data:

Col_format = [' B ', ' C ', ' D '] # specified column

INP = raw_input (U ' Please select the specified three columns, separated by commas, default is b,c,d (comma, case-insensitive), if selected by default press ENTER to continue: \ n ')

Try

INP = Inp.split (', ')

Col_format = [Col_format,inp][len ([i-I in INP if I in string.letters]) = = 3]

Col_format = [I.upper () for i in Col_format] # converted to uppercase

                        table = data.sheet_by_index (0)                                                            # Select the first workspace

                        nrows = table.nrows                                                                                       # Number of lines

                        Ncols = table.ncols                                                                                       # Number of columns

Str_upcase = [I for i in String.uppercase] # all uppercase letters

I_upcase = Range (len (str_upcase)) # corresponds to the number

Ncols_dir = dict (Zip (str_upcase,i_upcase)) # format into a dictionary

Col_index = [Ncols_dir.get (i) for i in Col_format] # gets the index corresponding to the specified column

# whether the selected three columns satisfy >=1 at the same time or <=-1

Print U ' is retrieving ... '

Count = 0

result = []

For I in Xrange (nrows):

Cell_0 = Table.cell (I,col_index[0]). Value

Cell_1 = Table.cell (I,col_index[1]). Value

cell_2 = Table.cell (i,col_index[2]). Value

if (Cell_0>=1 and Cell_1>=1 and Cell_2>=1) or (Cell_0<=-1 and Cell_1<=-1 and Cell_2<=-1):

Result.append (Table.row_values (i)) # Add a line that meets your requirements

Count + = 1

Print U ' The file%s row,%s column, which satisfies the condition of total%s bar data '% (nrows, Ncols, Count)

Print U ' is writing data ... '

Col_name = col_format[0]+col_format[1]+col_format[2]

If Write_data (result, col_name):

Print U ' Write succeeded! ‘

Except Exception, E:

Print U ' you operation error:%s '%e

Return None

Else

Print u ' operation failed '

Return None

def write_data (data, name):

"" "writes the data, data is a list of eligible lists, and name indicates which three columns are specified to name" ""

File = XLWT. Workbook ()

Table = File.add_sheet (name,cell_overwrite_ok=true)

L = 0 # indicates line

For line in data:

c = 0 # Indicates the number of columns under a row

For Col on line:

Table.write (L,c,line[c])

c + = 1

L + = 1

Defatul_f = R ' C:\Users\Administrator\Desktop\pytool\xlrd ' # Default Path

f = raw_input (U ' Please select the path to save the file: press ENTER to skip: ')

F_name = R ' \%s.xls '% name

filepath = [Defatul_f+f_name, F+f_name][f! = ']

File.save (filepath)

Return True

def main ():

Handle_data ()

if __name__ = = ' __main__ ':

Main ()

The results of the operation are as follows:

Source data

The data after processing

Python processing Excel table