Python-excel detailed

2. Installation
   Pip install xlrd # Read XLS
   Pip Install XLWT # write XLS
   Pip install xlutils # rewrite XLS
   
   
3. Reading Excel files
   

    fromMmapImportMmap,access_read
   # mmap is a memory-mapped file method that maps a file or other object to the address space of a process, implementing a one by one-mapping relationship between a file disk address and a virtual address in a process virtual address space.
   # access_read Read File fromXlrdImportOpen_workbook
   # xlrd returned by Open_workbook. The book object contains all the things that are needed for the workbook and can be used to get independent sheet in the workbook. Print(Open_workbook ('Simple.xls') ) with open ('Simple.xls','RB') as F:Print(Open_workbook (file_contents=mmap (F.fileno (), 0,access=Access_read))) 
   Astring= Open ('Simple.xls','RB'). Read ()Print(Open_workbook (file_contents=astring)) 

   Read

   In the context of a simple code:

   Import xlrd

   # Open XLS file

   Book = Xlrd.open_workbook ("Test.xls")

   Print "Number of forms:", book.nsheets

   Print "Form name:", Book.sheet_names ()

   # Get a 1th form

   SH = book.sheet_by_index (0)

   Print U "form%s total%d rows%d columns"% (Sh.Name, sh.nrows, Sh.ncols)

   Print "Second row, third column:", Sh.cell_value (1, 2)

   # Traverse All Forms

   For S in Book.sheets ():

   for R in range (S.nrows):

   # outputs the specified line

   Print S.row (R)

   Test file:

   Output Result:

   Number of forms: 2

   Form name: [u ' group.a ', U ' group.b ']

   Form GROUP.A Total 7 rows 3 columns

   Second row, third column: 15.0

   [Text:u ' Rank ', text:u ' Team ', text:u ' Points ']

   [number:1.0, Text:u ' Brazil ', number:15.0]

   [number:2.0, Text:u ' Russia ', number:12.0]

   ...

   Common methods:

   • Open_workbook Open File

   • Sheet_by_index Get a form

   • Sheets getting all Forms

   • Cell_value gets the data for the specified cell

   Write

   Or look at the code:

   Import XLWT

   # Create XLS file object

   WB = XLWT. Workbook ()

   # Add a new form

   SH = wb.add_sheet (' A Test sheet ')

   # Add data by location

   Sh.write (0, 0, 1234.56)

   Sh.write (1, 0, 8888)

   Sh.write (2, 0, ' hello ')

   Sh.write (2, 1, ' World ')

   # Save File

   Wb.save (' Example.xls ')

   Generate File:

   Common methods:

   • Workbook Creating a File Object

   • Add_sheet Add a form

   • Write writes data to the specified cell

   Modify

   Unfortunately, there is no way to directly modify the XLS file. It is common practice to read and remove files, copy a piece of data, modify it, and then save it.

   When copying, you need to use the method in Xlutils.

   From XLRD import Open_workbook

   From xlutils.copy Import copy

   # Open File

   RB = Open_workbook ("Example.xls")

   # Copy

   WB = Copy (RB)

   # Select Form

   s = Wb.get_sheet (0)

   # Write Data

   S.write (0, 1, ' new data ')

   # Save

   Wb.save (' Example.xls ')

   Modified file:

   It is important to note that when you choose to read the form, you use Sheet_by_index, and you use Get_sheet when you choose to write the form. Don't ask me why, I would also like to know the purpose of this set ...

   Time Conversion

   If the form has time-formatted data, after processing, you will find that the time data has gone awry.

   Output cell contents:

   [number:8888.0, xldate:42613.0]

   Because here Xldate has its own format definition. If you want to use the correct format, you must convert:

   New_date = Xlrd.xldate.xldate_as_datetime (date, Book.datemode)

   Date is the data for the corresponding cell, and book is the open file object.

   In addition, when you open a file, add the parameter formatting_info=true to ensure that the time data remains as it was in copy.

   Write time data, you can create a time object for Excel by this method:

   Xlrd.xldate.xldate_from_datetime_tuple

   Or specify the time format by XLWT.EASYXF:

   style = XLWT.EASYXF (num_format_str= ' d-mmm-yy ')

   Ws.write (1, 0, DateTime.Now (), style)

   Specific details and more features are not described here.

   These are some of the basic ways that Python operates Excel files. You will always remember two words when you encounter problems in the actual use process or need to know more features:

   RTFM,stfw :)

Python-excel detailed