WPS Office JS宏实现Excel转换为JSON格式数据

news/2024/7/21 1:19:55 标签: office, excel, Excel JS宏, json

        通过Excel JS宏，将表格中的数据以”列“形式导出为JSON数据格式。

        我们在整理文档时，产品会通过Excel将功能点和功能描述分层级整理出来，有时需要将此数据导入到系统中，Web端对Json这类数据比较友好，所以这里试着使用Excel JS宏将数据导出为JSON数据。

图1-1 

        如上图，我们将此信息生成为树结构的JSON数据格式的文本文件。

一、宏创建

1.1 定义宏

        首先，打开Excel中的宏编辑器，在模块中定义宏，代码如下：

/**
 * 创建将Excel数据转换为JSON格式的数据宏
 */
function 数据转换为JSON数据(){
	
}

        如果对JS宏还不了解朋友，或不知道在哪创建宏的，可以看下前面写的一篇，地址：

WPS Office JS宏实现批量处理Word中的表格样式-CSDN博客

1.2 获取内容

       Sheets 对象指定的或活动工作簿中所有工作表的集合。Sheets 集合可以包含 Chart 或 Worksheet 对象。使用 Sheets(index)（其中 index 是工作表名称或索引号）可返回一个 Chart 或 Worksheet 对象。

        所以通过Sheets对象获取Sheet 1中的数据内容，UsedRange返回一个 Range 对象，该对象表示指定工作表上所使用的区域。代码如下：

/**
 * 创建将Excel数据转换为JSON格式的数据宏
 */
function 数据转换为JSON数据(){
	// 获取Sheet 1内容
	let wbContent = Sheets("Sheet1").UsedRange;
    
}

        Sheets()可以使用ThisWorkbook.Sheets()调用，这里可以省略ThisWorkbook。

1.3 获取行数据  

        图1-1中，每列对应一个字段，所以在读取数据时，我们需要一行一行读取，读取每行数据后，再通过列关联上对应数据。

        这里将通过Rows对象获取到每行数据集，以及通过Cells获取每行中对应列的单元格数据，代码如下：

/**
 * 创建将Excel数据转换为JSON格式的数据宏
 */
function 数据转换为JSON数据(){
	// 获取Sheet 1内容
	let wbContent = Sheets("Sheet1").UsedRange;
	// 获取所有行数据
	let dataRows = wbContent.Rows;
	// 定义变量存储json数据
	let JsonData = [];
	// 循环输出数据
	for(let rows of dataRows){
		// 循环出每列数据
		for(let column of rows.Cells){
			console.log('column', column.Column, column.Text);
		}
	}
}

        如上代码执行后，获取结果如下图：

1.4 组装数据

        数据获取到后，如何将数据组装成JSON格式树状数据，需要通过一些简单逻辑判断即可，由1.3中输出结果可以看出，第1、2两列会出现空数据，所以由此可以判断出对应的一级和二级数据，代码如下：

/**
 * 创建将Excel数据转换为JSON格式的数据宏
 */
function 数据转换为JSON数据(){
	// 获取Sheet 1内容
	let wbContent = Sheets("Sheet1").UsedRange;
	// 获取所有行数据
	let dataRows = wbContent.Rows;
	// 定义变量存储json数据
	let JsonData = [];
	// 存储索引
	let JsonFirstIndex = -1,		//一级目录
		JsonSecondIndex = -1;		//二级目录
	// 循环输出数据
	for(let rows of dataRows){
		// 循环出每列数据
		for(let column of rows.Cells){
			switch(column.Column){
				// 列1
				case 1: 
					// 判断是否为一级栏目,并且文本不为空创建对象
					if(column.Text){
						JsonFirstIndex++;
						// 创建一级对象
						JsonData[JsonFirstIndex] = {
							name: column.Text,
							children: []
						};
					}
					break;
				// 列2
				case 2: 
					// 判断是否为二级栏目，并且文本不为空创建对象
					if(column.Text){
						let secondArr = JsonData[JsonFirstIndex]['children'];
						// 如果一级children为数组，则自递索引，新增二级数据
						if(Array.isArray(secondArr)){
							JsonSecondIndex++;
							secondArr[JsonSecondIndex] = {
								name: column.Text,
								children: []
							}
						}
					}
					break;
				// 列3
				case 3: 
					let threeArr = JsonData[JsonFirstIndex].children[JsonSecondIndex]['children'];
					// 如果二级children为数组，则追加数据，列3为末级功能，列4为功能描述，都在此组装
					if(Array.isArray(threeArr)){
						threeArr.push({
							name: rows.Cells.Item(3).Text,
							description: rows.Cells.Item(4).Text
						});
					}
					break;
			}
		}
	}
	
	console.log(JSON.stringify(JsonData));
}

        此时，Json数据则组装完成了，输出后结果如下：

二、输出Json数据

2.1 JSON.stringify()

语法：JSON.stringify(value, replacer , space)

属性

名称描述
value将要序列化成 一个JSON 字符串的值。
replace（可选）如果该参数是一个函数，则在序列化过程中，被序列化的值的每个属性都会经过该函数的转换和处理；如果该参数是一个数组，则只有包含在这个数组中的属性名才会被序列化到最终的 JSON 字符串中；如果该参数为null或者未提供，则对象所有的属性都会被序列化。
space（可选）指定缩进用的空白字符串，用于美化输出（pretty-print）；如果参数是个数字，它代表有多少的空格；上限为10。该值若小于1，则意味着没有空格；如果该参数为字符串(字符串的前十个字母)，该字符串将被作为空格；如果该参数没有提供（或者为null）将没有空格。

2.2 输出序列化json数据

/**
 * 创建将Excel数据转换为JSON格式的数据宏
 */
function 数据转换为JSON数据(){
	// 获取Sheet 1内容
	let wbContent = Sheets("Sheet1").UsedRange;
	// 获取所有行数据
	let dataRows = wbContent.Rows;
	// 定义变量存储json数据
	let JsonData = [];
	// 存储索引
	let JsonFirstIndex = -1,		//一级目录
		JsonSecondIndex = -1;		//二级目录
	// 循环输出数据
	for(let rows of dataRows){
		// 循环出每列数据
		for(let column of rows.Cells){
			switch(column.Column){
				// 列1
				case 1: 
					// 判断是否为一级栏目,并且文本不为空创建对象
					if(column.Text){
						JsonFirstIndex++;
						// 创建一级对象
						JsonData[JsonFirstIndex] = {
							name: column.Text,
							children: []
						};
					}
					break;
				// 列2
				case 2: 
					// 判断是否为二级栏目，并且文本不为空创建对象
					if(column.Text){
						let secondArr = JsonData[JsonFirstIndex]['children'];
						// 如果一级children为数组，则自递索引，新增二级数据
						if(Array.isArray(secondArr)){
							JsonSecondIndex++;
							secondArr[JsonSecondIndex] = {
								name: column.Text,
								children: []
							}
						}
					}
					break;
				// 列3
				case 3: 
					let threeArr = JsonData[JsonFirstIndex].children[JsonSecondIndex]['children'];
					// 如果二级children为数组，则追加数据，列3为末级功能，列4为功能描述，都在此组装
					if(Array.isArray(threeArr)){
						threeArr.push({
							name: rows.Cells.Item(3).Text,
							description: rows.Cells.Item(4).Text
						});
					}
					break;
			}
		}
	}
	
	// 将数据转换为JSON格式字符串
	let jsonStringData = JSON.stringify(JsonData, null, 2);
	console.log(jsonStringData);
}

通过JSON.stringify()将json数据进行序列化后，并缩进2位显得更为美观，输出结果如下图：

2.3 保存json数据

        从数据的读取，分析，以及json数据组装工作已完成，现在咱们则将最终的数据导出为json格式文件，代码如下：

/**
 * 创建将Excel数据转换为JSON格式的数据宏
 */
function 数据转换为JSON数据(){
	// 获取Sheet 1内容
	let wbContent = Sheets("Sheet1").UsedRange;
	// 获取所有行数据
	let dataRows = wbContent.Rows;
	// 定义变量存储json数据
	let JsonData = [];
	// 存储索引
	let JsonFirstIndex = -1,		//一级目录
		JsonSecondIndex = -1;		//二级目录
	// 循环输出数据
	for(let rows of dataRows){
		// 循环出每列数据
		for(let column of rows.Cells){
			switch(column.Column){
				// 列1
				case 1: 
					// 判断是否为一级栏目,并且文本不为空创建对象
					if(column.Text){
						JsonFirstIndex++;
						// 创建一级对象
						JsonData[JsonFirstIndex] = {
							name: column.Text,
							children: []
						};
					}
					break;
				// 列2
				case 2: 
					// 判断是否为二级栏目，并且文本不为空创建对象
					if(column.Text){
						let secondArr = JsonData[JsonFirstIndex]['children'];
						// 如果一级children为数组，则自递索引，新增二级数据
						if(Array.isArray(secondArr)){
							JsonSecondIndex++;
							secondArr[JsonSecondIndex] = {
								name: column.Text,
								children: []
							}
						}
					}
					break;
				// 列3
				case 3: 
					let threeArr = JsonData[JsonFirstIndex].children[JsonSecondIndex]['children'];
					// 如果二级children为数组，则追加数据，列3为末级功能，列4为功能描述，都在此组装
					if(Array.isArray(threeArr)){
						threeArr.push({
							name: rows.Cells.Item(3).Text,
							description: rows.Cells.Item(4).Text
						});
					}
					break;
			}
		}
	}
	
	// 将数据转换为JSON格式字符串
	let jsonStringData = JSON.stringify(JsonData, null, 2);

	// 创建新的工作簿并保存为JSON文件
	let filePath = "D:\\workspace\\work\\office-study\\data\\data.json";
	
	// 将JSON数据写入文件
	// 打开文件
	Open(filePath, '#1', jsOutput, jsWrite, jsLockWrite, jsonStringData.length*2);
	// 写入数据
	Write('#1', jsonStringData);
	// 关闭流
	Close('#1');
	// 提示
	MsgBox("数据保存成功~", jsInformation, "提示");
}

        此时点击运行按钮，则文件保存到filePath指定的位置了，如下图：

        data.json内容如下图：

        细心朋友此时会发现导出的json数据开头和结尾，会多出一个双引号。这里查了下VBA文档：

  • Write #和 Input #旨在与“结构化”文件 (.csv) 一起使用，并使用文本限定数据 - 任何 String或 Variant带有字符串子类型的双引号转义为 "" , 整个事情都用引号括起来。
  • Print #和 Input(number, #filenumber)旨在与“非结构化”文件一起使用。

        在WPS中没找到Print对象，这里就先手动删除，有朋友在WPS发现有和Print对象一样功能的，欢迎留言告知。


http://www.niftyadmin.cn/n/5255674.html

相关文章

保证消息顺序性：Kafka 的策略与挑战

保证消息顺序性：Kafka 的策略与挑战

目录 1. 为什么消息顺序性很重要？ 2. Kafka 的消息顺序性挑战 2.1 分区与并行性 2.2 生产者与网络延迟 2.3 消费者群组 3. 保证消息顺序性的策略 3.1 单分区单线程 3.2 顺序 ID 3.3 单一消费者 4. 最佳实践与注意事项 4.1 合理的分区设计 4.2 避免重分区…
阅读更多...
数据结构（C语言）

数据结构（C语言）

链表 链表的基本能操作 #include <stdbool.h> #include <stdio.h> #include <stdlib.h>//链表的接口 typedef struct node_s{int val;struct node_s*next; } Node; typedef struct linkedlist_s{Node* head;Node* tail;int size; }LinkedList;//创建空链表…
阅读更多...
0012Java安卓程序设计-ssm记账app

0012Java安卓程序设计-ssm记账app

文章目录 **摘要**目 录系统设计5.1 APP端&#xff08;用户功能&#xff09;5.2后端管理员功能模块开发环境 编程技术交流、源码分享、模板分享、网课分享 企鹅&#x1f427;裙&#xff1a;776871563 摘要 网络的广泛应用给生活带来了十分的便利。所以把记账管理与现在网络相…
阅读更多...
痤疮分割 实验心路历程

痤疮分割 实验心路历程

数据集的制作 将labelme生成的标注文件记普通的json文件转成coco数据集格式的json文件 图像分辨率过大 如果不做任何调整&#xff1a; 会出现“killed”的报错&#xff0c;表示图片像素过大&#xff0c;显卡内存不够&#xff0c;无法支撑训练 显卡 换成更高性能的显卡&am…
阅读更多...
Android 顶部对齐宽度撑满高度等比例缩放及限制最大最小高度

Android 顶部对齐宽度撑满高度等比例缩放及限制最大最小高度

一 示例 二 代码 <?xml version"1.0" encoding"utf-8"?> <FrameLayout xmlns:android"http://schemas.android.com/apk/res/android"android:layout_width"match_parent"android:layout_height"match_parent&qu…
阅读更多...
创建一个有阴影的threejs三维几何体

创建一个有阴影的threejs三维几何体

import * as THREE from "three" import { OrbitControls } from three/examples/jsm/controls/OrbitControlsconst width window.innerWidth; const height window.innerHeight;//创建场景 const scene new THREE.Scene(); scene.background new THREE.Color(#6…
阅读更多...
k8s之身份认证与权限

k8s之身份认证与权限

Kubernetes 中提供了良好的多租户认证管理机制&#xff0c;如 RBAC、ServiceAccount 还有各种策略等。 通过该文件可以看到已经配置了 RBAC 访问控制 /usr/lib/systemd/system/kube-apiserver.service 1.1 认证 所有 Kubernetes 集群有两类用户&#xff1a;由 Kubernetes 管理的…
阅读更多...
《C++新经典设计模式》之第12章 状态模式

《C++新经典设计模式》之第12章 状态模式

《C新经典设计模式》之第12章 状态模式 状态模式.cpp 状态模式.cpp #include <iostream> #include <memory> using namespace std;// 用类表示状态, 一般用于有限状态机 // 状态机3部分组成&#xff1a;状态&#xff08;State&#xff09;、事件&#xff08;Event…
阅读更多...
最新文章

Copyright @ 2022~2023