xxxxxxxxxx
import pandas as pd
# Assuming the JSON data is stored in a file called 'data.json'
json_file = 'data.json'
# Load the JSON data into a pandas DataFrame
data_frame = pd.read_json(json_file)
# Convert the DataFrame to an Excel file
excel_file = 'data.xlsx'
data_frame.to_excel(excel_file, index=False)
xxxxxxxxxx
import pandas as pd
# Sample JSON data
json_data = [
{"Name": "John", "Age": 28, "City": "New York"},
{"Name": "Alice", "Age": 35, "City": "Los Angeles"},
{"Name": "Bob", "Age": 42, "City": "Chicago"}
]
# Convert JSON to a pandas DataFrame
df = pd.DataFrame(json_data)
# Export DataFrame to Excel
excel_file = 'output.xlsx'
df.to_excel(excel_file, index=False)
print("JSON data exported to Excel successfully.")
xxxxxxxxxx
import pandas as pd
# read the Excel file
df = pd.read_excel('input.xlsx')
# convert DataFrame to JSON
json_data = df.to_json(orient='records')
print(json_data)
xxxxxxxxxx
import pandas as pd
# Replace 'path_to_excel_file' with the actual path to your Excel file
data_frame = pd.read_excel('path_to_excel_file.xlsx')
# Convert the data frame to JSON and specify the orientation as 'records'
json_data = data_frame.to_json(orient='records')
print(json_data)
xxxxxxxxxx
Data(Top bar)>>Get Data >> From File>> From JSON
Select File>>Import
(You can do changes now)
To Table>>(optional: Choose options)>>Ok
>>Choose the required columns and check use original name as prefix(if needed)>>Ok
>>close & load
https://www.howtogeek.com/775651/how-to-convert-a-json-file-to-microsoft-excel/
xxxxxxxxxx
<p>
Excel to JSON is an add-in offered by Microsoft to
convert the Excel sheet data to JSON format. When the data
is converted to the JSON format, Excel sheet columns are
changed to object keys in JSON. It costs no charge from
the user to download and use it.
</p>
<p>
You can download this add-in from the Office store.
</p>
xxxxxxxxxx
public override object ConvertTo(ITypeDescriptorContext context,
CultureInfo culture, object value, Type destinationType)
{
Length length;
object result;
result = null;
length = value as Length;
if (length != null)
{
if (destinationType == typeof(string))
result = length.ToString();
else if (destinationType == typeof(InstanceDescriptor))
{
ConstructorInfo constructorInfo;
constructorInfo = typeof(Length).GetConstructor(new[] { typeof(float), typeof(Unit) });
result = new InstanceDescriptor(constructorInfo, new object[] { length.Value, length.Unit });
}
}
return result ?? base.ConvertTo(context, culture, value, destinationType);
}
xxxxxxxxxx
function main(workbook: ExcelScript.Workbook): TableData[] {
// Get the first table in the "WithHyperLink" worksheet.
// If you know the table name, use `workbook.getTable('TableName')` instead.
const table = workbook.getWorksheet('WithHyperLink').getTables()[0];
// Get all the values from the table as text.
const range = table.getRange();
// Create an array of JSON objects that match the row structure.
let returnObjects: TableData[] = [];
if (table.getRowCount() > 0) {
returnObjects = returnObjectFromValues(range);
}
// Log the information and return it for a Power Automate flow.
console.log(JSON.stringify(returnObjects));
return returnObjects
}
function returnObjectFromValues(range: ExcelScript.Range): TableData[] {
let values = range.getTexts();
let objectArray : TableData[] = [];
let objectKeys: string[] = [];
for (let i = 0; i < values.length; i++) {
if (i === 0) {
objectKeys = values[i]
continue;
}
let object = {}
for (let j = 0; j < values[i].length; j++) {
// For the 4th column (0 index), extract the hyperlink and use that instead of text.
if (j === 4) {
object[objectKeys[j]] = range.getCell(i, j).getHyperlink().address;
} else {
object[objectKeys[j]] = values[i][j];
}
}
objectArray.push(object as TableData);
}
return objectArray;
}
interface TableData {
"Event ID": string
Date: string
Location: string
Capacity: string
"Search link": string
Speakers: string
}