MS Excel is a feature-rich program that lets you keep and organize data in tabular form. Moreover, it allows you to store the data in multiple worksheets. In addition to the data organization, you can perform sorting, graph plotting, mathematical computations and etc. JSON, on the other hand, is a widely used format to store and transmit the data in the form of key-value pairs. In certain cases, you may need to import data from JSON files to Excel worksheets programmatically. In accordance with that, this article covers how to convert JSON data to Excel XLSX/XLS using Python.
- Python API for JSON to Excel Conversion
- Import Data from JSON to Excel Worksheet
- Apply Cell Formatting in JSON to Excel Conversion
Python API for JSON to Excel Conversion
In order to perform JSON to Excel conversion, 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.
pip install aspose-cells
Import Data from 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
[{"nodeId":1,"reputation":1134},{"nodeId":2,"reputation":547},{"nodeId":3,"reputation":1703},{"nodeId":4,"reputation":-199},{"nodeId":5,"reputation":-306},{"nodeId":6,"reputation":-49},{"nodeId":7,"reputation":1527},{"nodeId":8,"reputation":1223}]'Output Excel Worksheet
Import JSON to Excel with 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.
- 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
Get a Free License
You can use Aspose.Cells for Python via Java without evaluation limitations using a temporary license.
Conclusion
JSON and Excel files are extensively used to store and share the data. Accordingly, in this article, you have learned how to import data from JSON to Excel XLSX/XLS using Python. In addition, you have seen how to apply formatting in JSON to Excel conversion. 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.
