In the process of data exchange and Web development, Json is used as a standard data format, and Excel is used as a mainstream tool for commercial data storage and processing. Batch conversion of Excel data into Json format is usually used in common scenarios such as API development, data migration and front-end development. It takes time and effort to manually convert manually, and there is a risk of error if there is still a large amount of data in Xlsx. Here is a detailed introduction to five methods of converting Excel worksheet to Json. Each method specifically analyzes the advantages and disadvantages to help everyone choose their own suitable method for conversion. Let's learn together!
Under what circumstances will Excel tables be used to convert Json data text?
- After Excel is converted to Json format, it is convenient for the front end to use data for rendering and dynamic interaction when developing web pages or applications.
- Json format as a lightweight data exchange format, in the system data docking, can make Excel data faster import database.
- In data analysis and machine learning, converting Excel to Json format can make it easier for scripting languages such as Python and Java to process data and conduct automated analysis.
Preview of Excel Batch Conversion to Json Format
before treatment:
after treatment:
method 1: Use HeSoft Doc Batch Tool Convert Excel to Json Text
recommend index:★★★★★
Advantages:
- it supports batch conversion of a large number of Excel tables, and the conversion speed is fast, and the effect is good after conversion.
- All files are processed locally and do not involve uploading properties to protect user privacy.
Disadvantages:
- only software can be installed in the computer.
Operation steps:
1. Open 【 HeSoft Doc Batch Tool ], select [Excel Tools]-[Excel to Json]].
2. Select one of the two addition methods in [Add File] or [Import File from Folder] to add the Excel form that needs to be converted into Json text. You can also directly drag the file into the bottom. After confirming that the imported file is correct, click Next. Then click Browse to select the location to save the converted Json text.
3. After the processing is completed, click the red path in the red box to open the folder to view the successfully converted Json file.
Method 2: Use online tools to convert Xlsx files to Json format
recommend index:★★★☆ ☆
advantages:
- without installing any software, open the browser can be converted.
- Windows, Mac and Linux systems can be used.
Disadvantages:
- you need to upload files to the server, which may cause data leakage.
- When the network environment is poor, the speed of conversion will be limited.
Operation steps:
1. Open the web page to search for Excel to convert to JSON online, select Excel to Json format, and then add files below.
2. Click the conversion below after the addition is completed.
Method 3: Install Excel to Json plug-in to complete format conversion
recommend index:★★★☆ ☆
advantages:
- keep the original data type in Excel and deeply integrate with Excel.
- Dynamic updates are supported, and data changes can be regenerated.
Disadvantages:
- A newer version of Office is required.
- Batch processing of multiple Excel files is not convenient.
Operation steps:
1. After opening Excel, click File-Options-Add-ins.
2. After searching and installing the Excel to Json plug-in in the application store, select the data area to be converted, and then set the processing options in the plug-in panel.
3. Click Convert to generate Json format, and the results will automatically appear in the new worksheet.
Method 4: Use Python script to convert Excel table to Json format
recommend index:★★☆ ☆ ☆
advantages:
- can handle millions of rows of large data in Excel and support a variety of complex structures.
- Windows native support, full control over the logic of the conversion, can automate the process.
Disadvantages:
- basic Python programming knowledge is required and learning costs are high.
- The threshold requirement of hardware configuration is high.
Operation steps:
1. Install Pyhton and execute the command.
pip install pandas openpyxl
2. Create the conversion script excel_to_json.py.
import pandas as pd
df = pd.read_excel("input.xlsx", sheet_name="Sheet1")
df.to_json("output.json", orient="records", indent=2)
method Five: Convert Excel Worksheet to Json Text with Node.js Script
recommend index:★★☆ ☆ ☆
advantages:
- seamless integration with the modern web development ecosystem.
- Supports streaming a large number of files.
Disadvantages:
- need to JavaScript the basics of programming.
- Error handling also requires additional coding.
Operation steps:
1. Initialize the Node project.
npm init -y
npm install xlsx
2. Create the conversion script concert.js.
const XLSX = require("xlsx");
const workbook = XLSX.readFile("input.xlsx");
const jsonData = XLSX.utils.sheet_to_json(workbook.Sheets[workbook.SheetNames[0]]);
require("fs").writeFileSync("output.json", JSON.stringify(jsonData, null, 2));
3. Run the script.
node convert.js