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

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

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

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  |
+-------+----+----+----+

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

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    

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")

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

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

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

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