Перевести json в excel python

JSON to excel converter

On subsequent rows the array might contain more items. The library reacts by adjusting the number of columns in the layout and raising LinearizationError as previous rows might be already output.

Converter.convert_streaming just raises this exception — it is the responsibility of caller to take the right action.

Converter.convert captures this error and restarts the processing. In case of CSV this means truncating the output file to 0 bytes and processing the data again. XLSX writer caches all the data before writing them to excel so the restart just means discarding the cache.

If you know the size of the array in advance, you should pass it in options. Then no processing restarts are required and LinearizationError is not raised.

XLSX Formatting

Cell format

XLSX writer enables you to format the header and data by passing an array of header_formatters or data_formatters. Take these from json_excel_converter.xlsx.formats package or create your own.

See https://xlsxwriter.readthedocs.io/format.html for details on formats in xlsxwriter

Column widths

Pass the required column widths to writer:

Width of nested data can be specified as well:

To set the default column width, pass it as DEFAULT_COLUMN_WIDTH property:

Row heights can be specified via the row_heights writer option:

To render url, pass a function that gets data of a row and returns url to options

Note: this will only be rendered in XLSX output, CSV output will silently ignore the link.

Custom cell rendering

Override the write_cell method. The method receives cell_data (instance of json_excel_converter.Value ) and data (the original data being written to this row). Note that this method is used both for writing header and rows — for header the data parameter is None.

Источник

Convert JSON to Excel in Python

MS Excel is a useful tool for storing and organizing data in tables. It has many features, such as multiple worksheets and functions for sorting, graphing, and doing math. JSON is another way to store and share data using key-value pairs. Sometimes, you might need to import JSON data into Excel sheets in a Python application. Following that, this article covers how to convert JSON data to Excel XLS in Python.

Python JSON to Excel Converter#

To convert JSON data to Excel, we’ll use Aspose.Cells for Python via Java. It is a spreadsheet manipulation API that lets you create, modify or convert Excel files using Python. You can either download the API or install it using the following pip command.

Convert JSON to Excel in Python#

The following are the steps to import data from JSON to Excel in Python:

• Create a new Workbook object.
• Get reference of the desired worksheet using Workbook.getWorksheets().get(index) method.
• Create an object of JsonLayoutOptions class to set additional options.
• Import data from JSON to Excel using JsonUtility.importData() method.
• Save the Excel file using Workbook.save(string, SaveFormat) method.

The following code sample shows how to import data from JSON to Excel.

Input JSON Data#

Output Excel Worksheet#

Import JSON Data to Excel — Formatting Styles#

Aspose.Cells also allows you to apply styles in JSON to Excel conversion. For example, you can set font, color, alignment, border styles, etc. The following are the steps to apply styles while importing data from JSON to Excel in Java.

• Create a new Workbook object.
• Get reference of the desired worksheet using Workbook.getWorksheets().get(index) method.
• Create an object of CellsFactory class and retreive reference of Style using CellsFactory.createStyle() method.
• Set desired styling options.
• Create an object of JsonLayoutOptions class to set additional options.
• Import data from JSON to Excel using JsonUtility.importData() method.
• Save the Excel file using Workbook.save(string, SaveFormat) method.

The following code sample applies different styles when importing data from JSON to Excel in Python.

Output Excel Worksheet#

Import JSON Data to Excel Online#

Use our free online JSON to Excel converter, which is based on Aspose.Cells. No need to create an account or subscribe to anything.

Free Python JSON to Excel Converter#

You can convert JSON files to Excel formats without evaluation limitations using a temporary license.

Conclusion#

JSON and Excel files are extensively used to store and share data. Accordingly, in this article, you have learned how to convert JSON data to Excel XLS in Python. In addition, you have seen how to apply formatting in JSON to Excel import in Python. We have also provided you with an online JSON to Excel converter that you can use anywhere at any time only having an internet connection.

You can also explore other features of Aspose.Cells for Python via Java using the documentation. In case you would have any queries, you can post on our forum.

See Also#

Источник

Saved searches

Use saved searches to filter your results more quickly

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

A python library to convert an array or stream of JSONs into CSV or Excel. Currently beta, use at your own risk

License

oarepo/json-excel-converter

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Sign In Required

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

A package that converts json to CSV, excel or other table formats

the generated CSV/excel is:

[ < "col1": "val1", "col2": < "col21": "val21", "col22": "val22" > > ]

the generated CSV/excel is (in excel, col2 spans two cells horizontally):

col1 col2 col21 col22 ================================= val1 val21 val22 

[ < "col1": "val1", "col2": [ < "col21": "val21" >, < "col21": "val22" > ] > ]

the generated CSV/excel is (in excel, col2 spans two cells horizontally):

col1 col2 col21 col21 ================================= val1 val21 val22 

pip install json-excel-converter[extra]

from json_excel_converter import Converter from json_excel_converter.xlsx import Writer data = [ 'a': [1], 'b': 'hello'>, 'a': [1, 2, 3], 'b': 'world'> ] conv = Converter() conv.convert(data, Writer(file='/tmp/test.xlsx'))

Streaming usage with restarts

from json_excel_converter import Converter, LinearizationError from json_excel_converter.csv import Writer conv = Converter() writer = Writer(file='/tmp/test.csv') while True: try: data = get_streaming_data() # custom function to get iterator of data conv.convert_streaming(data, writer) break except LinearizationError: pass

When the first row is processed, the library guesses the columns layout. In case of arrays, a column (or more columns if the array contains json objects) is created for each of the items in the array, as shown in the example above.

On subsequent rows the array might contain more items. The library reacts by adjusting the number of columns in the layout and raising LinearizationError as previous rows might be already output.

Converter.convert_streaming just raises this exception — it is the responsibility of caller to take the right action.

Converter.convert captures this error and restarts the processing. In case of CSV this means truncating the output file to 0 bytes and processing the data again. XLSX writer caches all the data before writing them to excel so the restart just means discarding the cache.

If you know the size of the array in advance, you should pass it in options. Then no processing restarts are required and LinearizationError is not raised.

from json_excel_converter import Converter, Options from json_excel_converter.xlsx import Writer data = [ 'a': [1]>, 'a': [1, 2, 3]> ] options = Options() options['a'].cardinality = 3 conv = Converter(options=options) writer = Writer(file='/tmp/test.xlsx') conv.convert(data, writer) # or conv.convert_streaming(data, writer) # no exception occurs here

XLSX writer enables you to format the header and data by passing an array of header_formatters or data_formatters. Take these from json_excel_converter.xlsx.formats package or create your own.

from json_excel_converter import Converter from json_excel_converter.xlsx import Writer from json_excel_converter.xlsx.formats import LastUnderlined, Bold, \ Centered, Format data = [ 'a': 'Hello'>, 'a': 'World'> ] w = Writer('/tmp/test3.xlsx', header_formats=( Centered, Bold, LastUnderlined, Format(< 'font_color': 'red' >)), data_formats=( Format(< 'font_color': 'green' >),) ) conv = Converter() conv.convert(data, w)

Pass the required column widths to writer:

w = Writer('/tmp/test3.xlsx', column_widths=< 'a': 20 >)

Width of nested data can be specified as well:

data = [ 'a': 'b': 1, 'c': 2>> ] w = Writer('/tmp/test3.xlsx', column_widths=< 'a.b': 20, 'a.c': 30, >)

To set the default column width, pass it as DEFAULT_COLUMN_WIDTH property:

w = Writer('/tmp/test3.xlsx', column_widths=< DEFAULT_COLUMN_WIDTH: 20 >)

Row heights can be specified via the row_heights writer option:

w = Writer('/tmp/test3.xlsx', row_heights=< DEFAULT_ROW_HEIGHT: 20, # a bit taller rows 1: 40 # extra tall header >)

To render url, pass a function that gets data of a row and returns url to options

data = [ 'a': 'https://google.com'>, ] options = Options() options['a'].url = lambda data: data['a'] conv = Converter(options) conv.convert(data, w)

Note: this will only be rendered in XLSX output, CSV output will silently ignore the link.

Override the write_cell method. The method receives cell_data (instance of json_excel_converter.Value ) and data (the original data being written to this row). Note that this method is used both for writing header and rows — for header the data parameter is None.

class UrlWriter(Writer): def write_cell(self, row, col, cell_data, cell_format, data): if cell_data.path == 'a' and data: self.sheet.write_url(row, col, 'https://test.org/' + data['b'], string=cell_data.value) else: super().write_cell(row, col, cell_data, cell_format, data)

About

A python library to convert an array or stream of JSONs into CSV or Excel. Currently beta, use at your own risk

Источник