Here is a brief tutorial that explains how to convert JSON to Excel in Python by loading the source JSON file from the disk into a string variable and then saving it as an XLSX file. During this process, a new workbook can be created or an existing workbook can be loaded for importing the JSON data to a specified worksheet starting from the user-designated cell. Using Python JSON to Excel conversion can be controlled using the JsonLayoutOptions class object that allows a number of customizations for the conversion process.
Steps to Convert JSON to Excel in Python
- Setup the necessary environment to run Aspose.Cells for Python via Java in your application
- Load the sample JSON file from the disk
- Create an empty Workbook class instance for importing JSON data
- Get a reference to the desired worksheet where JSON data is to be copied
- Create a JsonLayoutOptions class object to customize the JSON import process
- Import data to the desired worksheet using the JsonUtility.importData
- Save the resultant workbook as XLSX after auto-fitting columns
To convert JSON to Excel Python language environment settings and steps are shared to perform this task such that first create or load a Workbook and get access to its target worksheet. In the next step, create a JsonLayoutOptions class object for customization of the conversion process like writing data in the form of a table where headings are given at the top and data is present in the next lines. Finally, the JSON string data is imported into the selected worksheet starting from a specified cell and saved as XLSX.
Code to Convert JSON to Excel using Python
| import jpype | |
| import asposecells | |
| # Start JVM | |
| jpype.startJVM() | |
| from asposecells.api import License, Workbook, JsonLayoutOptions, JsonUtility, SaveFormat | |
| # Initialize the license before conversion of JSON to Excel to avoid | |
| # trial version watermark in the output Excel file | |
| licenseJsonToExcel = License() | |
| licenseJsonToExcel.setLicense("Aspose.Cells.lic") | |
| # load JSON data from the file on the disk | |
| with open('SampleData.json') as f: | |
| jsonSourceString = f.read() | |
| # Instantiate an empty Excel workbook to import JSON data | |
| ExcelFromJson = Workbook() | |
| # Get a reference to the desired worksheet from the default worksheets collection in the workbook | |
| jsonWorksheet = ExcelFromJson.getWorksheets().get(0) | |
| # Instantiate the JsonLayoutOptions class object to customize the JSON import data operation | |
| layoutOptionsForJsonToExcel = JsonLayoutOptions() | |
| layoutOptionsForJsonToExcel.setArrayAsTable(True) | |
| # Import data into Excel file from the JSON file at a specified cell in the selected worksheet | |
| JsonUtility.importData(jsonSourceString, jsonWorksheet.getCells(), 0, 0, layoutOptionsForJsonToExcel) | |
| # Autofit all the columns based on contents in the worksheet | |
| jsonWorksheet.autoFitColumns() | |
| # Save the workbook as an XLSX file after importing JSON data | |
| ExcelFromJson.save("output.xlsx", SaveFormat.XLSX) | |
| # Shutdown the JVM | |
| jpype.shutdownJVM() |
For writing JSON to Excel converter Python code is shared above that imports required libraries and classes at the start and then reads the source JSON data from a file however you can fill the string with JSON data from some other external source or provide a hardcoded JSON string. The JsonLayoutOptions class object contains a lot of properties to control the conversion process like setting the title style by providing background/foreground color and fonts, setting the number and date formats, ignoring the null values in JSON, and so on.
This article has described how to convert JSON to Excel in Python. If you want to learn the process to convert Excel to HTML, refer to the article on how to convert Excel to HTML using Python.
