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

Your output.xls will have these data:

Column 1	Column 2	Column 3
7	4	1
2	5	8
3	6	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