JSON to Excel 3.0.0 is a major update that expands the original Excel add-in into a full-fledged web application. You can now use it directly in your browser without installing any plugins. All conversions are performed in the browser. After previewing, you can click the download button to export the file in xlsx format. Of course, we still recommend using it as an Excel add-in for better integration with other Excel features.
Recently, while working with data, I encountered a challenge: converting data from an Excel spreadsheet into JSON format. During my search for a solution, I discovered the product Excel-to-JSON by WTSolutions, which turned out to be the lifesaver I had been desperately looking for! Today, I’d like to share my experience using this product.
Purpose of the Product
In modern data processing and development, JSON (JavaScript Object Notation) has become a highly popular data interchange format. It is concise, readable, and easy to transmit and parse across different programming languages and systems. On the other hand, Excel is the most commonly used spreadsheet software in our daily work, facilitating data entry, organization, and analysis.
The primary purpose of Excel-to-JSON is to quickly and accurately convert data from Excel spreadsheets into JSON format. This is incredibly useful for developers, as they can easily use the converted JSON data in scenarios like web development, API integration, and data analysis. For data analysts, converting Excel data to JSON also simplifies data processing and visualization.
System Requirements and How to Obtain
System Requirements
Excel-to-JSON boasts excellent compatibility, supporting Excel 2013 (or later), Excel Online, Office 365, and Mac versions of Excel. If you’re using it in a web browser, all you need is a JavaScript-enabled browser like Google Chrome, Mozilla Firefox, Safari, or Microsoft Edge.
How to Obtain
Getting this add-in is straightforward. In Excel 2013/2016 or later, Excel Online, or Office 365, open a new worksheet, select the Insert tab or Home tab > Add-ins, and search for “Excel-to-JSON” in the add-in search box. Click the add-in to launch it. Afterward, you’ll see the Excel-to-JSON button in the Home tab, and you can start using the plugin.
Sideloading in Excel (Recommended): In Excel 2013/2016, Excel Online, or Office 365, open a new datasheet, select the Home tab or Insert tab > Add-ins, and type “Excel to JSON” in the search box. Follow the on-screen instructions to install the add-in. Once installed, you’ll see an Excel to JSON button in the Home tab. Click Home > Excel to JSON > Convert to start using it.
Preparing the Excel Spreadsheet
Prepare your Excel spreadsheet, ensuring you select at least two rows of data, with the first row treated as the header. There are two ways to load Excel data:
Loading in a Web Browser: Copy and paste Excel data into the text area. You can copy and paste data from Excel, Google Sheets, or any other Excel-compatible software, with data separated by tabs. Alternatively, you can paste comma-separated CSV data.
Sideloading in Excel: Directly select your data from the Excel worksheet.
Performing the Conversion
Select the data to convert and choose a mode: Flat or Nested JSON mode. If you’ve subscribed to Pro Features, you can also configure additional options. Finally, click the Start button, and shortly after, you’ll see the converted JSON below the button.
Saving the JSON
Once the conversion is complete, there are several ways to save the generated JSON to your local computer:
Copy and Paste: After JSON generation, you’ll see the output in the text area. You can simply copy and paste it anywhere you need.
Copy to Clipboard: After JSON generation, locate the Copy to Clipboard button. Click it, and the JSON will be copied to your clipboard.
Save to File: (Not available for Excel for Mac users) After JSON generation, find the Save As button. Click it, and you’ll be prompted to save the JSON to a file.
Key Features
Conversion Modes
Flat JSON Mode: Converts Excel data into a flat JSON format. For example, consider the following Excel table:
Name
Age
Company
David
27
WTSolutions
Ton
26
WTSolutions
Kitty
30
Microsoft
Linda
30
Microsoft
Joe
40
Github
Using Flat JSON Mode, the converted JSON would look like this:
Nested JSON Mode: First converts the Excel data into Flat JSON, then uses the “Flat” package to unflatten keys with separators. By default, Excel-to-JSON calls the unflatten() function with the separator set to . and the overwrite parameter as true. If you’ve subscribed to Pro Features, you can customize the separator. For example, consider the following Excel table:
ID
Student.Name
Student.Surname
Student.Age
1
Meimei
Han
12
2
Lily
Jaskson
15
3
Elon
Mask
18
Using Flat JSON Mode, the converted JSON would look like this:
Empty Cell Handling: Three options for empty cells: empty string "", JSON null, or exclusion.
Boolean Formatting: Three boolean conversion formats: JSON true/false, string "true"/"false", or numeric 1/0.
Single Object JSON Output: When only one row of data exists, choose whether to output as a single JSON object or an array.
Date Formatting: Two options for date conversion: days since 1990-01-01 or ISO 8601 string format.
Custom Filename: Customize the filename when using the Save As feature.
Conclusion
Overall, Excel-to-JSON is an incredibly practical tool that has significantly improved my efficiency in converting Excel data to JSON format. It’s user-friendly, highly compatible, and packed with features, making it a product worth recommending to both developers and data analysts. If you frequently need to convert Excel data to JSON, give Excel-to-JSON a try—I’m sure it’ll pleasantly surprise you!
JSON to Excel 3.0.0 是一个重大更新版本,将原有的Excel插件扩展为完整的Web应用。现在您可以直接在浏览器中使用它,无需安装任何插件。所有的转换在浏览器中完成,预览后,可点击下载按钮,导出成xlsx格式文件。当然,我们依然是推荐您把它当作一个Excel插件使用,以便更好地与其他Excel功能集成。