英文:
How to change the type of columns of excel files for exported SheetJS xlsx?
问题
我想将一个表格转换成xlsx文件。我正在使用SheetJS。它可以成功获取数据，创建文件并下载。
问题出在Excel的数据类型上。在Excel中，所有列都被视为简单的字符串，例如23,0€不会被识别为货币，因此无法进行基本的Excel计算，比如sum：
要在Excel中解决这个问题，你应该启用修改，尝试编辑然后按Enter键，让它被识别为货币单元格：
我的代码在这里（我无法直接添加代码段到问题中，因为它需要下载）。
我所有的Excel操作：
const worksheet = XLSX.utils.table_to_sheet(document.getElementById("myTable"));
const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, worksheet, "Worksheet");
XLSX.writeFile(workbook, "Export.xlsx");
我应该如何在SheetJS中更改类型以让Excel识别数值呢？
英文:
I want to convert a table into an xlsx file. I'm using SheetJS. It success to get data, create the file and download it.
The issue comes from excel types. For excel, all columns are simple string, and for example 23,0€ isn't recognize as money, and I can't do basic excel calcul as sum :
To fix it in excel, I should enable modification, try to edit then use enter to let it be recognized as money cell :
My code is here (I can't add direct snippet into question as it require download).
All my excel manipulation:
const worksheet = XLSX.utils.table_to_sheet(document.getElementById("myTable"));
const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, worksheet, "Worksheet");
XLSX.writeFile(workbook, "Export.xlsx");
How can I change type in SheetJS to let excel recognize values?
答案1
得分: 0
- 获取单元格类型。在创建真正的Excel文件（没有问题）后，你可以使用以下代码来获取单元格类型：
var XLSX = require("xlsx");
var wb = XLSX.readFile("export.xlsx", {cellNF: true});
var ws = wb.Sheets[wb.SheetNames[0]];
console.log(ws["A1"].z);
你将看到单元格的类型。在我的情况下，它是 '#,##0.00\ "€";[Red]-#,##0.00\ "€"'。
- 对于每个
<td>，你应该 添加给定的格式，如下所示：
<td data-v="100" data-t="n" data-z='#,##0.00\ "€";[Red]-#,##0.00\ "€"'><p>100,00€</p></td>
data-v包含值，例如，在我的情况下是整数（不包括€）data-t设置为 "n"，因为该值是一个数字data-z是之前获得的单元格格式
- 使用相同的代码来导出内容，一切都会正常工作。
英文:
I found the answer. I should override set the cell-type myself, but as it's not a general format, it's different. (example of the default one)
- Get the cell type. After making the real excel file (without issue) you can use:
var XLSX = require("xlsx");
var wb = XLSX.readFile("export.xlsx", {cellNF: true})
var ws = wb.Sheets[wb.SheetNames[0]]
console.log(ws["A1"].z)
And you will see the type. In my case, it's '#,##0.00\\ "€";[Red]\\-#,##0.00\\ "€"'.
- For each
<td>, you should add the given format everything as :
<td data-v="100" data-t="n" data-z='#,##0.00\ "€";[Red]\-#,##0.00\ "€"'><p>100,00€</p></td>
data-vwill contains the value as, in my case, integer (without€)data-nis set as "n" because the value is a numberdata-zis what we get before: the formatting of the cell
- Use same code to export the content, and everything will works.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论