pyexcel.get_sheet¶
-
pyexcel.
get_sheet
(**keywords)[source]¶ Get an instance of
Sheet
from an excel sourceExamples on start_row, start_column
Let’s assume the following file is a huge csv file:
>>> import datetime >>> import pyexcel as pe >>> data = [ ... [1, 21, 31], ... [2, 22, 32], ... [3, 23, 33], ... [4, 24, 34], ... [5, 25, 35], ... [6, 26, 36] ... ] >>> pe.save_as(array=data, dest_file_name="your_file.csv")
And let’s pretend to read partial data:
>>> pe.get_sheet(file_name="your_file.csv", start_row=2, row_limit=3) your_file.csv: +---+----+----+ | 3 | 23 | 33 | +---+----+----+ | 4 | 24 | 34 | +---+----+----+ | 5 | 25 | 35 | +---+----+----+
And you could as well do the same for columns:
>>> pe.get_sheet(file_name="your_file.csv", start_column=1, column_limit=2) your_file.csv: +----+----+ | 21 | 31 | +----+----+ | 22 | 32 | +----+----+ | 23 | 33 | +----+----+ | 24 | 34 | +----+----+ | 25 | 35 | +----+----+ | 26 | 36 | +----+----+
Obvious, you could do both at the same time:
>>> pe.get_sheet(file_name="your_file.csv", ... start_row=2, row_limit=3, ... start_column=1, column_limit=2) your_file.csv: +----+----+ | 23 | 33 | +----+----+ | 24 | 34 | +----+----+ | 25 | 35 | +----+----+
The pagination support is available across all pyexcel plugins.
Note
No column pagination support for query sets as data source.
Formatting while transcoding a big data file
If you are transcoding a big data set, conventional formatting method would not help unless a on-demand free RAM is available. However, there is a way to minimize the memory footprint of pyexcel while the formatting is performed.
Let’s continue from previous example. Suppose we want to transcode “your_file.csv” to “your_file.xls” but increase each element by 1.
What we can do is to define a row renderer function as the following:
>>> def increment_by_one(row): ... for element in row: ... yield element + 1
Then pass it onto save_as function using row_renderer:
>>> pe.isave_as(file_name="your_file.csv", ... row_renderer=increment_by_one, ... dest_file_name="your_file.xlsx")
Note
If the data content is from a generator, isave_as has to be used.
We can verify if it was done correctly:
>>> pe.get_sheet(file_name="your_file.xlsx") your_file.csv: +---+----+----+ | 2 | 22 | 32 | +---+----+----+ | 3 | 23 | 33 | +---+----+----+ | 4 | 24 | 34 | +---+----+----+ | 5 | 25 | 35 | +---+----+----+ | 6 | 26 | 36 | +---+----+----+ | 7 | 27 | 37 | +---+----+----+
Not all parameters are needed. Here is a table
source parameters loading from file file_name, sheet_name, keywords loading from string file_content, file_type, sheet_name, keywords loading from stream file_stream, file_type, sheet_name, keywords loading from sql session, table loading from sql in django model loading from query sets any query sets(sqlalchemy or django) loading from dictionary adict, with_keys loading from records records loading from array array loading from an url url Parameters
- file_name :
- a file with supported file extension
- file_content :
- the file content
- file_stream :
- the file stream
- file_type :
- the file type in file_content or file_stream
- session :
- database session
- table :
- database table
- model:
- a django model
- adict:
- a dictionary of one dimensional arrays
- url :
- a download http url for your excel file
- with_keys :
- load with previous dictionary’s keys, default is True
- records :
- a list of dictionaries that have the same keys
- array :
- a two dimensional array, a list of lists
- sheet_name :
- sheet name. if sheet_name is not given, the default sheet at index 0 is loaded
- start_row : int
- defaults to 0. It allows you to skip rows at the begginning
- row_limit: int
- defaults to -1, meaning till the end of the whole sheet. It allows you to skip the tailing rows.
- start_column : int
- defaults to 0. It allows you to skip columns on your left hand side
- column_limit: int
- defaults to -1, meaning till the end of the columns. It allows you to skip the tailing columns.
- skip_row_func:
It allows you to write your own row skipping functions.
The protocol is to return pyexcel_io.constants.SKIP_DATA if skipping data, pyexcel_io.constants.TAKE_DATA to read data, pyexcel_io.constants.STOP_ITERATION to exit the reading procedure
- skip_column_func:
It allows you to write your own column skipping functions.
The protocol is to return pyexcel_io.constants.SKIP_DATA if skipping data, pyexcel_io.constants.TAKE_DATA to read data, pyexcel_io.constants.STOP_ITERATION to exit the reading procedure
- skip_empty_rows: bool
- Defaults to False. Toggle it to True if the rest of empty rows are useless, but it does affect the number of rows.
- row_renderer:
- You could choose to write a custom row renderer when the data is being read.
- auto_detect_float :
- defaults to True
- auto_detect_int :
- defaults to True
- auto_detect_datetime :
- defaults to True
- ignore_infinity :
- defaults to True
- library :
- choose a specific pyexcel-io plugin for reading
- source_library :
- choose a specific data source plugin for reading
- parser_library :
- choose a pyexcel parser plugin for reading
- skip_hidden_sheets:
- default is True. Please toggle it to read hidden sheets
Parameters related to csv file format
for csv, fmtparams are accepted
- delimiter :
- field separator
- lineterminator :
- line terminator
- encoding:
- csv specific. Specify the file encoding the csv file. For example: encoding=’latin1’. Especially, encoding=’utf-8-sig’ would add utf 8 bom header if used in renderer, or would parse a csv with utf brom header used in parser.
- escapechar :
- A one-character string used by the writer to escape the delimiter if quoting is set to QUOTE_NONE and the quotechar if doublequote is False.
- quotechar :
- A one-character string used to quote fields containing special characters, such as the delimiter or quotechar, or which contain new-line characters. It defaults to ‘”’
- quoting :
- Controls when quotes should be generated by the writer and recognised by the reader. It can take on any of the QUOTE_* constants (see section Module Contents) and defaults to QUOTE_MINIMAL.
- skipinitialspace :
- When True, whitespace immediately following the delimiter is ignored. The default is False.
- pep_0515_off :
- When True in python version 3.6, PEP-0515 is turned on. The default is False
- Parameters related to xls file format:
- Please note the following parameters apply to pyexcel-xls.
more details can be found in
xlrd.open_workbook()
- logfile:
- An open file to which messages and diagnostics are written.
- verbosity:
- Increases the volume of trace material written to the logfile.
- 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.
- encoding_override:
- Used to overcome missing or bad codepage information in older-version files.
- formatting_info:
The default is False, which saves memory.
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.
- 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 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 row_len() method.