Tree @69aa864 (Download .tar.gz)
- ..
- _static
- _templates
- generated
- iapi
- showcases
- uml
- answers.rst
- api.rst
- architecture.rst
- attributes.rst
- bigdata.rst
- book.rst
- capability.rst
- changelog.rst
- coffee.csv
- conf.py
- cookbook.rst
- database.rst
- design.rst
- entity-relationship-diagram.dia
- entity-relationship-diagram.png
- guide.rst
- iapi.rst
- index.rst
- logging.rst
- migration_guide.rst
- note_on_pypy.rst
- plugin_howto.rst
- pyinstaller.rst
- quickstart.rst
- renderers.rst
- sheet.rst
- sources.rst
- spelling_wordlist.txt
- tutorial.rst
- tutorial02.rst
- tutorial03.rst
- tutorial04.rst
- tutorial05.rst
- tutorial06.rst
- tutorial_data_conversion.rst
- tutorial_file.rst
- two-liners.rst
- webdev.rst
tutorial_file.rst @69aa864 — view markup · raw · history · blame
Work with excel files
Warning
The pyexcel DOES NOT consider Fonts, Styles, Formulas and Charts at all. When you load a stylish excel and update it, you definitely will lose all those styles.
Open a csv file
Read a csv file is simple:
>>> import pyexcel as p >>> sheet = p.get_sheet(file_name="example.csv") >>> sheet example.csv: +---+---+---+ | 1 | 4 | 7 | +---+---+---+ | 2 | 5 | 8 | +---+---+---+ | 3 | 6 | 9 | +---+---+---+
The same applies to a tsv file:
>>> sheet = p.get_sheet(file_name="example.tsv") >>> sheet example.tsv: +---+---+---+ | 1 | 4 | 7 | +---+---+---+ | 2 | 5 | 8 | +---+---+---+ | 3 | 6 | 9 | +---+---+---+
Meanwhile, a tab separated file can be read as csv too. You can specify a delimiter parameter.
>>> with open('tab_example.csv', 'w') as f: ... unused = f.write('I\tam\ttab\tseparated\tcsv\n') # for passing doctest ... unused = f.write('You\tneed\tdelimiter\tparameter\n') # unused is added >>> sheet = p.get_sheet(file_name="tab_example.csv", delimiter='\t') >>> sheet tab_example.csv: +-----+------+-----------+-----------+-----+ | I | am | tab | separated | csv | +-----+------+-----------+-----------+-----+ | You | need | delimiter | parameter | | +-----+------+-----------+-----------+-----+
Add a new row to an existing file
Suppose you have one data file as the following:
And you want to add a new row:
12, 11, 10
Here is the code:
>>> import pyexcel as pe >>> sheet = pe.get_sheet(file_name="example.xls") >>> sheet.row += [12, 11, 10] >>> sheet.save_as("new_example.xls") >>> pe.get_sheet(file_name="new_example.xls") pyexcel_sheet1: +----------+----------+----------+ | Column 1 | Column 2 | Column 3 | +----------+----------+----------+ | 1 | 4 | 7 | +----------+----------+----------+ | 2 | 5 | 8 | +----------+----------+----------+ | 3 | 6 | 9 | +----------+----------+----------+ | 12 | 11 | 10 | +----------+----------+----------+
Update an existing row to an existing file
Suppose you want to update the last row of the example file as:
['N/A', 'N/A', 'N/A']
Here is the sample code:
.. code-block:: python
>>> import pyexcel as pe >>> sheet = pe.get_sheet(file_name="example.xls") >>> sheet.row[3] = ['N/A', 'N/A', 'N/A'] >>> sheet.save_as("new_example1.xls") >>> pe.get_sheet(file_name="new_example1.xls") pyexcel_sheet1: +----------+----------+----------+ | Column 1 | Column 2 | Column 3 | +----------+----------+----------+ | 1 | 4 | 7 | +----------+----------+----------+ | 2 | 5 | 8 | +----------+----------+----------+ | N/A | N/A | N/A | +----------+----------+----------+
Add a new column to an existing file
And you want to add a column instead:
["Column 4", 10, 11, 12]
Here is the code:
>>> import pyexcel as pe >>> sheet = pe.get_sheet(file_name="example.xls") >>> sheet.column += ["Column 4", 10, 11, 12] >>> sheet.save_as("new_example2.xls") >>> pe.get_sheet(file_name="new_example2.xls") pyexcel_sheet1: +----------+----------+----------+----------+ | Column 1 | Column 2 | Column 3 | Column 4 | +----------+----------+----------+----------+ | 1 | 4 | 7 | 10 | +----------+----------+----------+----------+ | 2 | 5 | 8 | 11 | +----------+----------+----------+----------+ | 3 | 6 | 9 | 12 | +----------+----------+----------+----------+
Update an existing column to an existing file
Again let's update "Column 3" with:
[100, 200, 300]
Here is the sample code:
>>> import pyexcel as pe >>> sheet = pe.get_sheet(file_name="example.xls") >>> sheet.column[2] = ["Column 3", 100, 200, 300] >>> sheet.save_as("new_example3.xls") >>> pe.get_sheet(file_name="new_example3.xls") pyexcel_sheet1: +----------+----------+----------+ | Column 1 | Column 2 | Column 3 | +----------+----------+----------+ | 1 | 4 | 100 | +----------+----------+----------+ | 2 | 5 | 200 | +----------+----------+----------+ | 3 | 6 | 300 | +----------+----------+----------+
Alternatively, you could have done like this:
>>> import pyexcel as pe >>> sheet = pe.get_sheet(file_name="example.xls", name_columns_by_row=0) >>> sheet.column["Column 3"] = [100, 200, 300] >>> sheet.save_as("new_example4.xls") >>> pe.get_sheet(file_name="new_example4.xls") pyexcel_sheet1: +----------+----------+----------+ | Column 1 | Column 2 | Column 3 | +----------+----------+----------+ | 1 | 4 | 100 | +----------+----------+----------+ | 2 | 5 | 200 | +----------+----------+----------+ | 3 | 6 | 300 | +----------+----------+----------+
How about the same alternative solution to previous row based example? Well, you'd better to have the following kind of data:
And then you want to update "Row 3" with for example:
[100, 200, 300]
These code would do the job:
>>> import pyexcel as pe >>> sheet = pe.get_sheet(file_name="row_example.xls", name_rows_by_column=0) >>> sheet.row["Row 3"] = [100, 200, 300] >>> sheet.save_as("new_example5.xls") >>> pe.get_sheet(file_name="new_example5.xls") pyexcel_sheet1: +-------+-----+-----+-----+ | Row 1 | 1 | 2 | 3 | +-------+-----+-----+-----+ | Row 2 | 4 | 5 | 6 | +-------+-----+-----+-----+ | Row 3 | 100 | 200 | 300 | +-------+-----+-----+-----+