JSON转excel方案--透过现象看本质

张春泉（Team） 2024-04-18 11:02:04 分享明道云excel解决方案收藏

16 / 1249

现象：

Excel 内容是文件流写入
Excel 软件能打开 CSV 文件，部分软件支持 Excel 的“从文本”或“从 JSON”功能。来导入数据源
部分 txt 软件能打开 Excel 文件

本质

那么 Excel 文件的内容有什么特殊性或者本质性的东西吗？
有那就是换行符(\n)和制表符(\t)，这两个常见的字符，它们在单元格中起着不同的作用。换行符用于将文本分成多行，而制表符则用于对齐文本和数据，也就是我们说的列。

解决方案

步骤 1.添加代码块节点

var list=JSON.parse(input.obj||'[]')
var str1 =`域账号	姓名	邮箱	工号	手机
`
for(var i=0;i<list.length;i++){
var it=list[i];
str1 =str1 +it.A+`	`+it.B+`	`+it.C+`	`+it.D+`	`+it.E+`
`
}
function encrypt(str) {
  return new Buffer.from(str).toString('base64')
}
const encryptStr = encrypt(str1)

output = {'str1':str1,'encryptStr':encryptStr }

步骤 2.发送 API 请求
使用工作表 API，附件 base64 写入方法。参考：https://bbs.mingdao.net/topic/668

结果：

此方案无引入其他类库，公有私有都适用。

总之，现象和本质是事物存在的两个方面，它们之间相互联系、相互影响。透过现象看本质，可以帮助我们更好地认识和理解事物/问题的内在规律和本质属性。同时，我们也应该意识到，对本质的认识是一个不断深入和完善的过程，需要保持开放的心态和持续的学习。
加油吧明道人 🚀️

方伟辛 2024-04-18 11:02:04 回复

徐衡 2023-08-10 15:51:09

用 ChatGPT 写了一段 js 分享给大家，可以把 JSON 转化为 Excel，存到腾讯云对象存储里（需要提前配好 bucket、id/key、权限），并返回一个可以下载的 url；用户在入参里可以定义文件名、JSON 里保留哪些字段，这些字段在 Excel 中显示的名字，以及这些字段显示的顺序（整数，越大越右）等。

const XLSX = require('xlsx');
const COS = require('cos-nodejs-sdk-v5');

// JSON data to be converted to XLSX
var rawJsonData = JSON.parse(input.rows);
var cols = JSON.parse(input.cols);

// Process and rearrange columns without changing the row order
const rearrangedJsonData = rawJsonData.map(item => {
const newItem = {};

cols.sort((colA, colB) => colA.placedOrder - colB.placedOrder)
.forEach(col => {
newItem[col.nameDisplayed] = item[col.column];
});

return newItem;
});

// Get the desired file name from input, or use default "data"
const fileName = input.fileName || 'data';

// If input.fileName is provided, concatenate it with the current datetime
const finalFileName = input.fileName
? `${fileName}_${new Date().toISOString().replace(/[:.]/g, '')}`
: fileName;

// Create a new XLSX workbook and worksheet
const wb = XLSX.utils.book_new();
const ws = XLSX.utils.json_to_sheet(rearrangedJsonData);

// Adjust column widths based on content
const columnWidths = cols.map(col => ({
wch: Math.max(
col.nameDisplayed.length,
...rearrangedJsonData.map(item => String(item[col.nameDisplayed]).length)
)
}));

ws['!cols'] = columnWidths;

// Add the worksheet to the workbook
XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');

// Generate XLSX file in memory
const xlsxBuffer = XLSX.write(wb, { bookType: 'xlsx', type: 'buffer' });

// Tencent COS configuration
const cosConfig = {
SecretId: input.id,
SecretKey: input.key,
Bucket: input.bucket,
Region: input.region
};

const cos = new COS(cosConfig);

// 同步方式基于 JavaScript 的 async 和 await
async function upload() {
const cos = new COS(cosConfig);
try {
var data = await cos.putObject(
{
Bucket: cosConfig.Bucket,
Region: cosConfig.Region,
Key: `${finalFileName}.xlsx`,
Body: xlsxBuffer
});
return { err: null, data: data }
} catch (err) {
return { err: err, data: null };
}
}

// 可以同步拿到请求的返回值,这里举例说明,实际返回的数据格式可以自定义
var uploadResult = await upload();
var url = "";
if (uploadResult.err) {
url = uploadResult.err;
} else {
url = uploadResult.data.Location;
}

output = {url: "https://" + url};

我这私有部署也装了 XLSX 库，你这块代码很多还看不太懂，但大部分拿来主义后，最后也调试成功了，非常感谢！

方伟辛 2024-04-03 16:44:40 回复

在张工和几位前辈的指引下，又结合网上的一些资料，终于做成了可用 Excel 直接打开的 xls 文件。
只是将头部和尾部的拼接内容变了一下，虽然直接点击还是报-82，但下载后或者工作流转入邮件附件中用 Excel 就可以直接打开。
这样做的好处是可以控制将某列全数字的内容显示为文本格式，比如电话号码等。
主体如下：

var html='<html xmlns:o=\'urn:schemas-microsoft-com:office:office\' xmlns:x=\'urn:schemas-microsoft-com:office:excel\' xmlns=\'http://www.w3.org/TR/REC-html40\'><meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8"><meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>sheet1</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table><tr><td>号</td><td>姓名</td><td>项目</td><td>最后日</td><td>金额</td><td>备注</td></tr>';/*标题行*/

var Str_Code=JSON.parse(input.Str_Code?input.Str_Code:'[]');
var Str_Name=JSON.parse(input.Str_Name?input.Str_Name:'[]');
var Str_Item=JSON.parse(input.Str_Item?input.Str_Item:'[]');
var Str_Lastdate=JSON.parse(input.Str_Lastdate?input.Str_Lastdate:'[]');
var Str_Money=JSON.parse(input.Str_Money?input.Str_Money:'[]');
var Str_Result=JSON.parse(input.Str_Result?input.Str_Result:'[]');

for( var key in Str_Code ){
    html+='<tr><td x:str>'+Str_Code[key]+'</td><td x:str>'+Str_Name[key]+'</td><td x:str>'+Str_Item[key]+'</td><td>'+Str_Lastdate[key]+'</td><td>'+Str_Money[key]+'</td><td x:str>'+Str_Result[key]+'</td></tr>';         
}

html+='</table></body></html>';

方伟辛 2024-03-26 16:58:46 回复

张春泉（Team） 2024-03-26 16:43:56

私有支持自己安装对应的类库即可，公有云不支持；上述是偷懒的通用

我将制表符(\t)都改为”,“了，但生成的附件用 Excel 打开其中的中文会乱码，哪里需要再调整下呢？

张春泉（Team） 2024-03-26 16:43:56 回复

方伟辛 2024-03-26 16:42:20

好的，那暂时只能 CSV 了，但 CSV 不能自动设文本列，希望能继续研究出直接生成 Excel 格式的方法。

私有支持自己安装对应的类库即可，公有云不支持；上述是偷懒的通用

方伟辛 2024-03-26 16:42:20 回复

张春泉（Team） 2024-03-26 16:18:52

Excel 打开有问题电话，代码修改下，转为 CSV 参考下面的回复

好的，那暂时只能 CSV 了，但 CSV 不能自动设文本列，希望能继续研究出直接生成 Excel 格式的方法。

张春泉（Team） 2024-03-26 16:18:52 回复

方伟辛 2024-03-26 16:16:16

不行啊，导出后用 EXCEL2016 打开时还是报格式不对，然而用记事本强行打开看里面的内容都是正常的，咋回事呢？

Excel 打开有问题电话，代码修改下，转为 CSV 参考下面的回复

方伟辛 2024-03-26 16:16:16 回复

张春泉（Team） 2023-08-08 16:55:25

是因为缺少工作表名 sheet 部分，下载下来使用正常使用。

不行啊，导出后用 EXCEL2016 打开时还是报格式不对，然而用记事本强行打开看里面的内容都是正常的，咋回事呢？

徐衡 2023-08-10 15:52:19 回复

需要提前装两个 js 库：xlsx、cos-nodejs-sdk-v5

徐衡 2023-08-10 15:51:09 回复

const XLSX = require('xlsx');
const COS = require('cos-nodejs-sdk-v5');

// JSON data to be converted to XLSX
var rawJsonData = JSON.parse(input.rows);
var cols = JSON.parse(input.cols);

// Process and rearrange columns without changing the row order
const rearrangedJsonData = rawJsonData.map(item => {
const newItem = {};

cols.sort((colA, colB) => colA.placedOrder - colB.placedOrder)
.forEach(col => {
newItem[col.nameDisplayed] = item[col.column];
});

return newItem;
});

// Get the desired file name from input, or use default "data"
const fileName = input.fileName || 'data';

// If input.fileName is provided, concatenate it with the current datetime
const finalFileName = input.fileName
? `${fileName}_${new Date().toISOString().replace(/[:.]/g, '')}`
: fileName;

// Create a new XLSX workbook and worksheet
const wb = XLSX.utils.book_new();
const ws = XLSX.utils.json_to_sheet(rearrangedJsonData);

// Adjust column widths based on content
const columnWidths = cols.map(col => ({
wch: Math.max(
col.nameDisplayed.length,
...rearrangedJsonData.map(item => String(item[col.nameDisplayed]).length)
)
}));

ws['!cols'] = columnWidths;

// Add the worksheet to the workbook
XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');

// Generate XLSX file in memory
const xlsxBuffer = XLSX.write(wb, { bookType: 'xlsx', type: 'buffer' });

// Tencent COS configuration
const cosConfig = {
SecretId: input.id,
SecretKey: input.key,
Bucket: input.bucket,
Region: input.region
};

const cos = new COS(cosConfig);

// 同步方式基于 JavaScript 的 async 和 await
async function upload() {
const cos = new COS(cosConfig);
try {
var data = await cos.putObject(
{
Bucket: cosConfig.Bucket,
Region: cosConfig.Region,
Key: `${finalFileName}.xlsx`,
Body: xlsxBuffer
});
return { err: null, data: data }
} catch (err) {
return { err: err, data: null };
}
}

// 可以同步拿到请求的返回值,这里举例说明,实际返回的数据格式可以自定义
var uploadResult = await upload();
var url = "";
if (uploadResult.err) {
url = uploadResult.err;
} else {
url = uploadResult.data.Location;
}

output = {url: "https://" + url};

方棱 2023-08-09 09:17:05 回复

若文本字段里有回车符就没辙了。

还是用标准的 CSV 格式吧，这里写得很详细，实现难度也不高。
https://blog.csdn.net/woaixiaoyu520/article/details/78455650

听人劝吃饱饭 2023-08-09 08:42:06 回复

张春泉（Team） 2023-08-08 17:41:18

埋的伏笔（\t \n）被你发现并写出来了，善思考善执行 👍

本质就是被（强行）改了后缀的 TXT👀️

张春泉（Team） 2023-08-08 17:41:18 回复

听人劝吃饱饭 2023-08-08 17:37:19

需要的可以试试，经测试，预览不了，但是可是作为附件传输 👍 👍 👍

var list=JSON.parse(input.data||'[]')
//表头
var str1 ="name"+"\t"+"code"
//数据行
for(var i=0;i<list.length;i++){
var it=list[i];
str1 =str1+"\n" +it.name+"\t"+it.code
}
function encrypt(str) {
  return new Buffer.from(str).toString('base64')
}
const encryptStr = encrypt(str1)

output = {'str1':str1,'encryptStr':encryptStr }

埋的伏笔（\t \n）被你发现并写出来了，善思考善执行 👍

听人劝吃饱饭 2023-08-08 17:37:19 回复

需要的可以试试，经测试，预览不了，但是可是作为附件传输 👍 👍 👍

var list=JSON.parse(input.data||'[]')
//表头
var str1 ="name"+"\t"+"code"
//数据行
for(var i=0;i<list.length;i++){
var it=list[i];
str1 =str1+"\n" +it.name+"\t"+it.code
}
function encrypt(str) {
  return new Buffer.from(str).toString('base64')
}
const encryptStr = encrypt(str1)

output = {'str1':str1,'encryptStr':encryptStr }

张春泉（Team） 2023-08-08 16:55:25 回复

听人劝吃饱饭 2023-08-08 16:44:01

预览附件错误代码-82

是因为缺少工作表名 sheet 部分，下载下来使用正常使用。

听人劝吃饱饭 2023-08-08 16:44:01 回复

预览附件错误代码-82

Q 2023-08-08 14:54:34 回复

哲学贴无疑！👍

发表话题

张春泉（Team）

积分
2613
注册排名
264