python第三方模块_xlrd、xlwt

一、excel操作模块 xlrd xlwt

1.xlrd 模块用来读excel

import xlrd  #导入读excel的模块
book = xlrd.open_workbook('d_product.xls')
sheet = book.sheet_by_index(0)  #根据下标获取sheet页，下标从0开始，即第一个sheet页的下标为0
#sheet = book.sheet_by_name('sheet1') #根据名字获取sheet页数据
#读单元格内容
print(book.nsheets)#获取电子表格中sheet页的个数,返回的是一个int类型的数字

print(book.sheet_names())   #获取电子表格sheet页的所有名字，放在一个list当中

# 返回的类型是class 'xlrd.sheet.Cell'，如果sheet页为空会报错IndexError: list index out of range
print(sheet.cell(0,0))
print(sheet.cell(0,0).value)   #获取单元格内容 #获取一整行内容 sheet.row_values() #print(sheet.row_values(0)) #获取第1行的内容，结果存放在一个list当中 #获取一整列内容 sheet.col_values() #print(sheet.col_values(0)) #获取第1列的内容，结果存放在一个list当中 #获取所有的行数 #print(sheet.nrows) #获取到excel当中有多少行 #获取所有的列数 #print(sheet.ncols) #获取到excel中的有多少列 #循环获取到每行的数据 for i in range(sheet.nrows): print( sheet.row_values(i)) #循环获取到每列的数据 # for i in range(sheet.ncols): # print( sheet.col_values(i))

方法解析

1）xlrd.open_workbook('c:\abc.xls')  #打开一个电子表格用来提取数据

def open_workbook(filename=None,
    logfile=sys.stdout,
    verbosity=0,
    use_mmap=USE_MMAP,
    file_contents=None,
    encoding_override=None,
    formatting_info=False,
    on_demand=False,
    ragged_rows=False,
    ):
    """
    Open a spreadsheet file for data extraction.

    :param filename: The path to the spreadsheet file to be opened.

    :param logfile: An open file to which messages and diagnostics are written.

    :param verbosity: Increases the volume of trace material written to the
                      logfile.

    :param use_mmap:

      Whether to use the mmap module is determined heuristically.
      Use this arg to override the result.

      Current heuristic: mmap is used if it exists.

    :param file_contents:

      A string or an :class:`mmap.mmap` object or some other behave-alike
      object. If ``file_contents`` is supplied, ``filename`` will not be used,
      except (possibly) in messages.

    :param encoding_override:

      Used to overcome missing or bad codepage information
      in older-version files. See :doc:`unicode`.

    :param formatting_info:

      The default is ``False``, which saves memory.
      In this case, "Blank" cells, which are those with their own formatting
      information but no data, are treated as empty by ignoring the file's
      ``BLANK`` and ``MULBLANK`` records.
      This cuts off any bottom or right "margin" of rows of empty or blank
      cells.
      Only :meth:`~xlrd.sheet.Sheet.cell_value` and
      :meth:`~xlrd.sheet.Sheet.cell_type` are available.

      When ``True``, formatting information will be read from the spreadsheet
      file. This provides all cells, including empty and blank cells.
      Formatting information is available for each cell.
      
      Note that this will raise a NotImplementedError when used with an
      xlsx file.

    :param on_demand:

      Governs whether sheets are all loaded initially or when demanded
      by the caller. See :doc:`on_demand`.

    :param ragged_rows:

      The default of ``False`` means all rows are padded out with empty cells so
      that all rows have the same size as found in
      :attr:`~xlrd.sheet.Sheet.ncols`.

      ``True`` means that there are no empty cells at the ends of rows.
      This can result in substantial memory savings if rows are of widely
      varying sizes. See also the :meth:`~xlrd.sheet.Sheet.row_len` method.

    :returns: An instance of the :class:`~xlrd.book.Book` class.
    """

2.xlwt用来写excel

import xlwt
book = xlwt.Workbook()
sheet = book.add_sheet('sheet1',cell_overwrite_ok=True)
#book.get_sheet()
#第一个为行号，第二个参数列号，第三列单元格内容
sheet.write(0,0)
sheet.write(0,1,'姓名')
book.save('stu.xls') #结尾一定要以xls结尾

方法解析

1）book.add_sheet()  #在电子表格中创建一个sheet页，默认单元格覆盖写为False，如果改为True允许覆盖写单元格

def add_sheet(self, sheetname, cell_overwrite_ok=False):
    """
    This method is used to create Worksheets in a Workbook.

    :param sheetname:

      The name to use for this sheet, as it will appear in the
      tabs at the bottom of the Excel application.

    :param cell_overwrite_ok:

      If ``True``, cells in the added worksheet will not raise an
      exception if written to more than once.

    :return:

      The :class:`~xlwt.Worksheet.Worksheet` that was added.

    """

2）sheet.write(r,c,label='') #往单元格写内容，如果label的默认参数是空，如果不填写则单元格默认为空

def write(self, r, c, label="", style=Style.default_style):
    """
    This method is used to write a cell to a :class:`Worksheet`.

    :param r:

       The zero-relative number of the row in the worksheet to which
       the cell should be written.

    :param c:

       The zero-relative number of the column in the worksheet to which
       the cell should be written.

    :param label:

       The data value to be written.

       An :class:`int`, :class:`long`, or
       :class:`~decimal.Decimal` instance is converted to :class:`float`.

       A :class:`unicode` instance is written as is. A :class:`bytes`
       instance is converted to :class:`unicode` using the
       encoding, which defaults to ``ascii``, specified when the
       :class:`Workbook` instance was created.

       A :class:`~datetime.datetime`, :class:`~datetime.date` or
       :class:`~datetime.time` instance is converted into Excel date format
       (a float representing the number of days since (typically)
       ``1899-12-31T00:00:00``, under the pretence that
       1900 was a leap year).

       A :class:`bool` instance will show up as ``TRUE`` or ``FALSE`` in
       Excel.

       ``None`` causes the cell to be blank: no data, only formatting.

       An :class:`xlwt.Formula` instance causes an Excel formula to be
       written.

    :param style:

       A style, also known as an XF (extended format), is an
       :class:`~xlwt.Style.XFStyle` object, which encapsulates the
       formatting applied to the cell and its contents.

       :class:`~xlwt.Style.XFStyle` objects are best set up using the
       :func:`~xlwt.Style.easyxf` function. They may also be set up by
       setting attributes in :class:`Alignment`, :class:`Borders`,
       :class:`Pattern`, :class:`Font` and :class:`Protection` objects then
       setting those objects and a format string as attributes of an
       :class:`~xlwt.Style.XFStyle` object.
    """
    self.row(r).write(c, label, style)

3.book.save()    #将电子表格保存成一个excel文件

def save(self, filename_or_stream):
    """
    This method is used to save the Workbook to a file in native Excel
    format.

    :param filename_or_stream:
      This can be a string containing a filename of
      the file, in which case the excel file is saved to disk using the name
      provided. It can also be a stream object with a write method, such as
      a :class:`~io.StringIO`, in which case the data for the excel
      file is written to the stream.
    """
    from . import CompoundDoc

    doc = CompoundDoc.XlsDoc()
    doc.save(filename_or_stream, self.get_biff_data())