Django таблицы как в excel
If your company has embedded pyexcel and its components into a revenue generating product, please support me on patreon to maintain the project and develop it further.
If you are an individual, you are welcome to support me too on patreon and for however long you feel like. As a patreon, you will receive early access to pyexcel related contents.
And your issues will get prioritized if you would like to become my patreon as pyexcel pro user .
With your financial support, I will be able to invest a little bit more time in coding, documentation and writing interesting posts.
Known constraints
Fonts, colors and charts are not supported.
Introduction
Here is a typical conversation between the developer and the user:
The idea originated from the common usability problem: when an excel file driven web application is delivered for non-developer users (ie: team assistant, human resource administrator etc). The fact is that not everyone knows (or cares) about the differences between various excel formats: csv, xls, xlsx are all the same to them. Instead of training those users about file formats, this library helps web developers to handle most of the excel file formats by providing a common programming interface. To add a specific excel file format type to you application, all you need is to install an extra pyexcel plugin. Hence no code changes to your application and no issues with excel file formats any more. Looking at the community, this library and its associated ones try to become a small and easy to install alternative to Pandas.
- excel data import into and export from databases
- turn uploaded excel file directly into Python data structure
- pass Python data structures as an excel file download
- provide data persistence as an excel file in server side
- supports csv, tsv, csvz, tsvz by default and other formats are supported via the following plugins:
In order to manage the list of plugins installed, you need to use pip to add or remove a plugin. When you use virtualenv, you can have different plugins per virtual environment. In the situation where you have multiple plugins that does the same thing in your environment, you need to tell pyexcel which plugin to use per function call. For example, pyexcel-ods and pyexcel-odsr, and you want to get_array to use pyexcel-odsr. You need to append get_array(…, library=’pyexcel-odsr’).
This library makes information processing involving various excel files as easy as processing array, dictionary when processing file upload/download, data import into and export from SQL databases, information analysis and persistence. It uses pyexcel and its plugins:
I want to export the information display in the template under table tag to excel. I already tried to implement code but it is now exporting the information.
This is my template:
Above is my updated assisted by Marcell. Ive managed to export the required data. My question is: am I allowed to use if-else statement in my views.py? What i am trying to do is, to convert the mark to percentage. If students have 200 marks, then it will display 100%, if 100 marks then 90% or so.
It looks like you already have some code doing the right thing (well, possibly not in the most straightforward way but), so what's your question actually ? NB : please do not answer "it doesn't work" - if it "doesn't work" then you have to explain exactly HOW it doesn't work (and if you have an exception, post the exact exception message AND the full traceback).
You can use django-tables2 to easily export data in a couple of formats, apart from other userful features. I can write you a snippet if you're interested in this package.
Currently, when i cleck the link: export-data, it just refreshed the page and no excel file is downloaded. So i m not sure what is the problem with it as it doesnt prompt any error. Just not downloading the content of the table into excel sheet @brunodesthuilliers
3 Answers 3
You can use django-tables2. Install it and add it to INSTALLED_APPS . You'll also need to install tablib for the export feature. Create a tables.py file under your app folder:
Then use the SingleTableView class with ExportMixin in your views.py :
Finally your student_list.html template should look like this:
There's a lot more you can do with django-tables2 , this is just a basic implementation. You can also combine it with django-filter.
First of all the report_line dictionary is referencing a student variable that does not exists in the scope of the method. Based on your question here I presume you want to export the data displayed in your template.
Also I would recommend updating current question with the complete code set.
In order to achieve that you can do something like this:
Your url will look something like this:
In your template:
This is for exporting data to csv file. Looking at your file name extension it seems this is the behaviour you are looking for. If you want to export to excel file I would recommend looking at a third party library like xlwt
I have an HTML table that I'd like to be able to export to an Excel file. I already have an option to export the table into an IQY file, but I'd prefer something that didn't allow the user to refresh the data via Excel. I just want a feature that takes a snapshot of the table at the time the user clicks the link/button.
I'd prefer it if the feature was a link/button on the HTML page that allows the user to save the query results displayed in the table. It would also be nice if the formatting from the HTML/CSS could be retained. Is there a way to do this at all? Or, something I can modify with the IQY?
I can try to provide more details if needed. Thanks in advance.
3 Answers 3
You can use the excellent xlwt module. It is very easy to use, and creates files in xls format (Excel 2003).
Here is an (untested!) example of use for a Django view:
Use CSV. There's a module in Python ("csv") to generate it, and excel can read it natively.
From a user interface standpoint, how would I present the CSV file to the user? The user would already be looking at the page, so would they click a link that then goes back to some view function and generates the CSV file, and then.
Right. With CSV the results are not that good looking; it is not only a matter of styles, but even the column width is just the standard one. As there are ways to produce native Excel files, I would not recommend using CSV.
But the principle is the important: you're not converting the HTML table to Excel, you're getting the view function to output the same data in a different format.
Excel support opening an HTML file containing a table as a spreadsheet (even with CSS formatting).
You basically have to serve that HTML content from a django view, with the content-type application/ms-excel as Roberto said.
Or if you feel adventurous, you could use something like Downloadify to prepare the file to be downloaded on the client side.
Here is a typical conversation between the developer and the user:
The idea originated from the common usability problem: when an excel file driven web application is delivered for non-developer users (ie: team assistant, human resource administrator etc). The fact is that not everyone knows (or cares) about the differences between various excel formats: csv, xls, xlsx are all the same to them. Instead of training those users about file formats, this library helps web developers to handle most of the excel file formats by providing a common programming interface. To add a specific excel file format type to you application, all you need is to install an extra pyexcel plugin. Hence no code changes to your application and no issues with excel file formats any more. Looking at the community, this library and its associated ones try to become a small and easy to install alternative to Pandas.
- excel data import into and export from databases
- turn uploaded excel file directly into Python data structure
- pass Python data structures as an excel file download
- provide data persistence as an excel file in server side
- supports csv, tsv, csvz, tsvz by default and other formats are supported via the following plugins:
Plugin shopping guide¶
Since 2020, all pyexcel-io plugins have dropped the support for python version lower than 3.6. If you want to use any python verions, please use pyexcel-io and its plugins version lower than 0.6.0.
Except csv files, xls, xlsx and ods files are a zip of a folder containing a lot of xml files
The dedicated readers for excel files can stream read
In order to manage the list of plugins installed, you need to use pip to add or remove a plugin. When you use virtualenv, you can have different plugins per virtual environment. In the situation where you have multiple plugins that does the same thing in your environment, you need to tell pyexcel which plugin to use per function call. For example, pyexcel-ods and pyexcel-odsr, and you want to get_array to use pyexcel-odsr. You need to append get_array(…, library=’pyexcel-odsr’).
Package name | Supported file formats | Dependencies | Python versions |
---|---|---|---|
pyexcel-text | write only:rst, mediawiki, html, latex, grid, pipe, orgtbl, plain simple read only: ndjson r/w: json | tabulate | 2.6, 2.7, 3.3, 3.4 3.5, 3.6, pypy |
pyexcel-handsontable | handsontable in html | handsontable | same as above |
pyexcel-pygal | svg chart | pygal | 2.7, 3.3, 3.4, 3.5 3.6, pypy |
pyexcel-sortable | sortable table in html | csvtotable | same as above |
pyexcel-gantt | gantt chart in html | frappe-gantt | except pypy, same as above |
[1] | zipped csv file |
[2] | zipped tsv file |
This library makes information processing involving various excel files as easy as processing array, dictionary when processing file upload/download, data import into and export from SQL databases, information analysis and persistence. It uses pyexcel and its plugins:
- to provide one uniform programming interface to handle csv, tsv, xls, xlsx, xlsm and ods formats.
- to provide one-stop utility to import the data in uploaded file into a database and to export tables in a database as excel files for file download.
- to provide the same interface for information persistence at server side: saving a uploaded excel file to and loading a saved excel file from file system.
Given the existence of pyexcel, what is the reason for django-excel? 1. Speedy file uploads. django-excel help you access the uploaded excel file directly using ExcelMemoryFileUploadHandler and TemporaryExcelFileUploadHandler. MemoryFileUploadHandler holds the uploaded file in memory and django-excel reads the excel data from this memory buffer without caching it onto file system. Meanwhile, TemporaryExcelFileUploadHandler holds the uploaded file in file system and django-excel reads directly from this stream-to-file without extra function calls. 2. Import excel data into database. django-excel uses bulk_insert to import your excel data into your django Model, which is very efficient.
Aidas Bendoraitis on development with Django, Python, and JavaScript.
2019-02-15
How to Export Data to XLSX Files
A while ago I wrote an article about exporting data to different spreadsheet formats. As recently I was reimplementing export to Excel for the 1st things 1st project, I noticed that the API changed a little, so it's time to blog about that again.
For Excel export I am using the XLSX file format which is a zipped XML-based format for spreadsheets with formatting support. XLSX files can be opened with Microsoft Excel, Apache OpenOffice, Apple Numbers, LibreOffice, Google Drive, and a handful of other applications. For building the XLSX file I am using openpyxl library.
Installing openpyxl
You can install openpyxl to your virtual environment the usual way with pip:
Simplest Export View
To create a function exporting data from a QuerySet to XLSX file, you would need to create a view that returns a response with a special content type and file content as an attachment. Plug that view to URL rules and then link it from an export button in a template.
Probably the simplest view that generates XLSX file out of Django QuerySet would be this:
If you try this, you will notice, that there is no special formatting in it, all columns are of the same width, the value types are barely recognized, the header is displayed the same as the content. This is enough for further data export to CSV or manipulation with pandas. But if you want to present the data for the user in a friendly way, you need to add some magic.
Creating More Worksheets
By default, each Excel file has one worksheet represented as a tab. You can access it with:
If you want to create tabs dynamically with data from the database of Python structures, you can at first delete the current tab and add the others with:
Although not all spreadsheet applications support this, you can set the background color of the worksheet tab with:
Working with Cells
Each cell can be accessed by its 1-based indexes for the rows and for the columns:
Styles and formatting are applied to individual cells instead of rows or columns. There are several styling categories with multiple configurations for each of them. You can find some available options from the documentation, but even more by exploring the source code.
If you are planning to have multiple styled elements, instantiate the font, alignment, border, fill options upfront and then assign the instances to the cell attributes. Otherwise, you can get into memory issues when you have a lot of data entries.
Setting Column Widths
If you want to have some wider or narrower width for some of your columns, you can do this by modifying column dimensions. They are accessed by column letter which can be retrieved using a utility function:
The units here are some relative points depending on the width of the letters in the specified font. I would suggest playing around with the width value until you find what works for you.
When defining column width is not enough, you might want to wrap text into multiple lines so that everything can be read by people without problems. This can be done with the alignment setting for the cell as follows:
Data Formatting
Excel automatically detects text or number types and aligns text to the left and numbers to the right. If necessary that can be overwritten.
There are some gotchas on how to format cells when you need a percentage, prices, or time durations.
Percentage
For percentage, you have to pass the number in float format from 0.0 till 1.0 and style should be 'Percent' as follows:
Currency
For currency, you need values of Decimal format, the style should be 'Currency', and you will need a special number format for currency other than American dollars, for example:
Durations
For time duration, you have to pass timedelta as the value and define special number format:
This number format ensures that your duration can be greater than '23:59', for example, '140:00'.
Freezing Rows and Columns
In Excel, you can freeze rows and columns so that they stay fixed when you scroll the content vertically or horizontally. That's similar to position: fixed in CSS.
To freeze the rows and columns, locate the top-left cell that is below the row that you want to freeze and is on the right from the column that you want to freeze. For example, if you want to freeze one row and one column, the cell would be 'B2'. Then run this:
Fully Customized Export View
So having the knowledge of this article now we can build a view that creates separate sheets for each movie category. Each sheet would list movies of the category with titles, descriptions, length in hours and minutes, rating in percent, and price in Euros. The tabs, as well as the headers, can have different background colors for each movie category. Cells would be well formatted. Titles and descriptions would use multiple lines to fully fit into the cells.
Читайте также: