Python 操作 Excel 表格

旧城等待， 2021-09-09 04:26 527阅读 0赞

## Python 操作 Excel 表格 ##

### 文章目录 ###

*   *  Python 操作 Excel 表格
     *   *  一、xlrd
         *  二、xlwt

python处理excel主要使用两个模块，一个是xlrd  
另一个是xlwt

### 一、xlrd ###

这个模块主要是实现读取文件的功能

这个模块最主要的一个函数是：  
**open\_workbook**

它的用法如下所示：

open_workbook(filename=None, logfile=<_io.TextIOWrapper name='<stdout>' mode='w' encoding='utf-8'>, verbosity=0, use_mmap=True, file_contents=None, encoding_override=None, formatting_info=False, on_demand=False, ragged_rows=False, ignore_workbook_corruption=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.
    
    
            :param ignore_workbook_corruption:
    
              This option allows to read corrupted workbooks.
              When ``False`` you may face CompDocError: Workbook corruption.
              When ``True`` that exception will be ignored.
    
            :returns: An instance of the :class:`~xlrd.book.Book` class.

一般只需要写第一个参数就可以了，就是写出文件的路径

最后这个函数返回的是一个xlrd.book.Book对象，我们接下来就可以对这个对象进行操作了

对于这个对象，可以用索引或者名字来获取表单

xls_0 = xlrd.open_workbook("filename")
    
    # 读取表格文件
    
    new_sheet = xls_0.sheet_by_index(0)
    
    # 获取表单

获取表单以后，可以获取表单中的每一个元素，我们直接用索引就可以了

代码是：

xls_0 = xlrd.open_workbook("filename")
    
    # 读取表格文件
    
    new_sheet = xls_0.sheet_by_index(0)
    
    # 获取表单
    
    value_0 = new_sheet.cell(rowx, colx).value
    
    # 获取元素的值

到此，这个模块就介绍的差不多了

### 二、xlwt ###

xlwt是写入excel表格的模块，主要操作入下：

wb = xlwt.Workbook()  
    
    # 创建 excel 表格
    
    sh = wb.add_sheet("sheetname")  
    
    # 创建一个 表单
    
    sh.write(rowx, colx, content)
    
    # 在文件中进行写入的操作
    
    wb.save(filename_or_stream)
    
    # 保存文件

这些即就是xlwt最主要的操作了啦

以上是一些表格的简单操作，适合于入门学习的简单介绍，希望对大家有一定的帮助了啦。