Permalink
Fetching contributors…
Cannot retrieve contributors at this time
417 lines (317 sloc) 10.7 KB

Recipes

Warning

The pyexcel DOES NOT consider Fonts, Styles and Charts at all. In the resulting excel files, fonts, styles and charts will not be transferred.

These recipes give a one-stop utility functions for known use cases. Similar functionality can be achieved using other application interfaces.

Update one column of a data file

Suppose you have one data file as the following:

example.xls

Column 1 Column 2 Column 3
1 4 7
2 5 8
3 6 9
.. testcode::
   :hide:

   >>> import pyexcel
   >>> # please make sure pyexcel-xls is pip-installed
   >>> data = [
   ...      ["Column 1", "Column 2", "Column 3"],
   ...      [1, 4, 7],
   ...      [2, 5, 8],
   ...      [3, 6, 9]
   ...  ]
   >>> s = pyexcel.Sheet(data)
   >>> s.save_as("example.xls")


And you want to update Column 2 with these data: [11, 12, 13]

Here is the code:

>>> from pyexcel.cookbook import update_columns
>>> custom_column = {"Column 2":[11, 12, 13]}
>>> update_columns("example.xls", custom_column, "output.xls")

Your output.xls will have these data:

Column 1 Column 2 Column 3
1 11 7
2 12 8
3 13 9
.. testcode::
   :hide:

   >>> import os
   >>> os.unlink("example.xls")
   >>> os.unlink("output.xls")

Update one row of a data file

Suppose you have the same data file:

example.xls

Row 1 1 2 3
Row 2 4 5 6
Row 3 7 8 9
.. testcode::
   :hide:

   >>> import pyexcel
   >>> data = [
   ...      ["Row 1", 1, 2, 3],
   ...      ["Row 2", 4, 5, 6],
   ...      ["Row 3", 7, 8, 9]
   ... ]
   >>> s = pyexcel.Sheet(data)
   >>> s.save_as("example.xls")

And you want to update the second row with these data: [7, 4, 1]

Here is the code:

>>> from pyexcel.cookbook import update_rows
>>> custom_row = {"Row 1":[11, 12, 13]}
>>> update_rows("example.xls", custom_row, "output.xls")
>>> pyexcel.get_sheet(file_name="output.xls")
pyexcel sheet:
+-------+----+----+----+
| Row 1 | 11 | 12 | 13 |
+-------+----+----+----+
| Row 2 | 4  | 5  | 6  |
+-------+----+----+----+
| Row 3 | 7  | 8  | 9  |
+-------+----+----+----+
.. testcode::
   :hide:

   >>> os.unlink("example.xls")
   >>> os.unlink("output.xls")

Merge two files into one

Suppose you want to merge the following two data files:

example.csv

Column 1 Column 2 Column 3
1 4 7
2 5 8
3 6 9

example.xls

Column 4 Column 5
10 12
11 13
.. testcode::
   :hide:

   >>> data = [
   ...      ["Column 1", "Column 2", "Column 3"],
   ...      [1, 2, 3],
   ...      [4, 5, 6],
   ...      [7, 8, 9]
   ...  ]
   >>> s = pyexcel.Sheet(data)
   >>> s.save_as("example.csv")
   >>> data = [
   ...      ["Column 4", "Column 5"],
   ...      [10, 12],
   ...      [11, 13]
   ...  ]
   >>> s = pyexcel.Sheet(data)
   >>> s.save_as("example.xls")


The following code will merge the tow into one file, say "output.xls":

>>> from pyexcel.cookbook import merge_two_files
>>> merge_two_files("example.csv", "example.xls", "output.xls")

The output.xls would have the following data:

Column 1 Column 2 Column 3 Column 4 Column 5
1 4 7 10 12
2 5 8 11 13
3 6 9    
.. testcode::
   :hide:

   >>> os.unlink("example.csv")
   >>> os.unlink("example.xls")
   >>> os.unlink("output.xls")

Select candidate columns of two files and form a new one

Suppose you have these two files:

example.ods

Column 1 Column 2 Column 3 Column 4 Column 5
1 4 7 10 13
2 5 8 11 14
3 6 9 12 15

example.xls

Column 6 Column 7 Column 8 Column 9 Column 10
16 17 18 19 20
>>> data = [
...      ["Column 1", "Column 2", "Column 3", "Column 4", "Column 5"],
...      [1, 4, 7, 10, 13],
...      [2, 5, 8, 11, 14],
...      [3, 6, 9, 12, 15]
...  ]
>>> s = pyexcel.Sheet(data)
>>> s.save_as("example.csv")
>>> data = [
...      ["Column 6", "Column 7", "Column 8", "Column 9", "Column 10"],
...      [16, 17, 18, 19, 20]
...  ]
>>> s = pyexcel.Sheet(data)
>>> s.save_as("example.xls")

And you want to filter out column 2 and 4 from example.ods, filter out column 6 and 7 and merge them:

Column 1 Column 3 Column 5 Column 8 Column 9 Column 10
1 7 13 18 19 20
2 8 14      
3 9 15      

The following code will do the job:

>>> from pyexcel.cookbook import merge_two_readers
>>> sheet1 = pyexcel.get_sheet(file_name="example.csv", name_columns_by_row=0)
>>> sheet2 = pyexcel.get_sheet(file_name="example.xls", name_columns_by_row=0)
>>> del sheet1.column[1, 3, 5]
>>> del sheet2.column[0, 1]
>>> merge_two_readers(sheet1, sheet2, "output.xls")
.. testcode::
   :hide:

   >>> sheet3 = pyexcel.get_sheet(file_name="output.xls", name_columns_by_row=0)
   >>> [str(name) for name in sheet3.colnames]
   ['Column 1', 'Column 3', 'Column 5', 'Column 8', 'Column 9', 'Column 10']
   >>> sheet3.column["Column 8"]
   [18, '', '']
   >>> os.unlink("example.csv")
   >>> os.unlink("example.xls")
   >>> os.unlink("output.xls")

Merge two files into a book where each file become a sheet

Suppose you want to merge the following two data files:

example.csv

Column 1 Column 2 Column 3
1 4 7
2 5 8
3 6 9

example.xls

Column 4 Column 5
10 12
11 13
>>> data = [
...      ["Column 1", "Column 2", "Column 3"],
...      [1, 2, 3],
...      [4, 5, 6],
...      [7, 8, 9]
...  ]
>>> s = pyexcel.Sheet(data)
>>> s.save_as("example.csv")
>>> data = [
...      ["Column 4", "Column 5"],
...      [10, 12],
...      [11, 13]
...  ]
>>> s = pyexcel.Sheet(data)
>>> s.save_as("example.xls")

The following code will merge the tow into one file, say "output.xls":

>>> from pyexcel.cookbook import merge_all_to_a_book
>>> merge_all_to_a_book(["example.csv", "example.xls"], "output.xls")

The output.xls would have the following data:

example.csv as sheet name and inside the sheet, you have:

Column 1 Column 2 Column 3
1 4 7
2 5 8
3 6 9

example.ods as sheet name and inside the sheet, you have:

Column 4 Column 5
10 12
11 13
.. testcode::
   :hide:

   >>> book = pyexcel.get_book(file_name="output.xls")
   >>> [str(name) for name in book.sheet_names()]
   ['example.csv', 'example.xls']
   >>> os.unlink("example.csv")
   >>> os.unlink("example.xls")
   >>> os.unlink("output.xls")

Merge all excel files in directory into a book where each file become a sheet

The following code will merge every excel files into one file, say "output.xls":

from pyexcel.cookbook import merge_all_to_a_book
import glob


merge_all_to_a_book(glob.glob("your_csv_directory\*.csv"), "output.xls")

You can mix and match with other excel formats: xls, xlsm and ods. For example, if you are sure you have only xls, xlsm, xlsx, ods and csv files in your_excel_file_directory, you can do the following:

from pyexcel.cookbook import merge_all_to_a_book
import glob


merge_all_to_a_book(glob.glob("your_excel_file_directory\*.*"), "output.xls")

Split a book into single sheet files

.. testcode::
   :hide:

    >>> content = {
    ...     'Sheet 1':
    ...         [
    ...             [1.0, 2.0, 3.0],
    ...             [4.0, 5.0, 6.0],
    ...             [7.0, 8.0, 9.0]
    ...         ],
    ...     'Sheet 2':
    ...         [
    ...             ['X', 'Y', 'Z'],
    ...             [1.0, 2.0, 3.0],
    ...             [4.0, 5.0, 6.0]
    ...         ],
    ...     'Sheet 3':
    ...         [
    ...             ['O', 'P', 'Q'],
    ...             [3.0, 2.0, 1.0],
    ...             [4.0, 3.0, 2.0]
    ...         ]
    ... }
    >>> book = pyexcel.Book(content)
    >>> book.save_as("megabook.xls")

Suppose you have many sheets in a work book and you would like to separate each into a single sheet excel file. You can easily do this:

>>> from pyexcel.cookbook import split_a_book
>>> split_a_book("megabook.xls", "output.xls")
>>> import glob
>>> outputfiles = glob.glob("*_output.xls")
>>> for file in sorted(outputfiles):
...     print(file)
...
Sheet 1_output.xls
Sheet 2_output.xls
Sheet 3_output.xls

for the output file, you can specify any of the supported formats

.. testcode::
   :hide:

   >>> os.unlink("Sheet 1_output.xls")
   >>> os.unlink("Sheet 2_output.xls")
   >>> os.unlink("Sheet 3_output.xls")

Extract just one sheet from a book

Suppose you just want to extract one sheet from many sheets that exists in a work book and you would like to separate it into a single sheet excel file. You can easily do this:

>>> from pyexcel.cookbook import extract_a_sheet_from_a_book
>>> extract_a_sheet_from_a_book("megabook.xls", "Sheet 1", "output.xls")
>>> if os.path.exists("Sheet 1_output.xls"):
...     print("Sheet 1_output.xls exists")
...
Sheet 1_output.xls exists

for the output file, you can specify any of the supported formats

.. testcode::
   :hide:

   >>> os.unlink("Sheet 1_output.xls")
   >>> os.unlink("megabook.xls")