使用Office Excel将Excel工作簿保存为单独的CSV文件

u0njafvf  于 12个月前  发布在  其他
关注(0)|答案(1)|浏览(139)

我想自动化一个消耗大量时间的日常任务。有一个Excel文件包含许多工作簿。每个工作簿都需要保存为单独的CSV文件,以便我能够将其上传到我们的平台。
我创建了以下脚本:

function saveWorksheetsAsCSV() {
  let workbook = ExcelScript.Workbook.getActiveWorkbook();
  let worksheets = workbook.getWorksheets();

  // Get the user's downloads folder path
  let downloadsFolderPath = OfficeExtension.configurableWebSystemService.getDownloadsFolderPath();

  // Get the current date and time
  let currentDate = new Date();
  let formattedDate = currentDate.getFullYear() + "-" + padNumber(currentDate.getMonth() + 1) + "-" + padNumber(currentDate.getDate());
  let formattedTime = padNumber(currentDate.getHours()) + "-" + padNumber(currentDate.getMinutes()) + "-" + padNumber(currentDate.getSeconds());

  worksheets.forEach(function (worksheet) {
    // Create a unique filename based on the worksheet name, date, and time
    let filename = "DiscountPrograms_" + worksheet.getName() + "_" + formattedDate + "_" + formattedTime + ".csv";
    let filePath = downloadsFolderPath + "\\" + filename;

    // Save the worksheet as CSV with semicolon delimiter
    worksheet.saveAs(filePath, ExcelScript.SaveAsFileType.CommaSeparatedValue, { delimiter: ";" });
  });
}

function padNumber(number) {
  return number.toString().padStart(2, '0');
}

字符串
然而,当我运行它时,我只得到这些错误:

[13, 32] Parameter 'worksheet' implicitly has an 'any' type, but a better type may be inferred from usage.
[23, 20] Parameter 'number' implicitly has an 'any' type, but a better type may be inferred from usage.
[2, 30] Property 'Workbook' does not exist on type 'typeof ExcelScript'.
[6, 29] Cannot find name 'OfficeExtension'.
[19, 44] Property 'SaveAsFileType' does not exist on type 'typeof ExcelScript'.
[1, 10] No function of name 'main' found. Ensure the function exists and is not nested in anything.
[2, 7] Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.
[3, 7] Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.
[6, 7] Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.
[23, 20] Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.


你能帮我修改一下吗?这是我第一次创作剧本。谢谢!

tcomlyy6

tcomlyy61#

可能为时已晚,但看看这篇文章在电源自动化社区:https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Save-XLSX-Excel-all-worksheets-as-csv/td-p/923157
来文方建议:
1.创建Office脚本
1.从Power Automate Flow调用此功能
创建Office脚本。这是在Excel for Web中完成的。在新文件中创建新脚本。

function main(workbook: ExcelScript.Workbook) {
// declare variables
let wbook = workbook;
// the workbook filename
let sheetnames = new Array({});
// worksheets in the work book
let sheets = wbook.getWorksheets();
// array of items
let items = new Array({});

// get each worksheet data set
for (let i = 0; i < sheets.length; i++) {
// try catch - errors when a sheet is blank and other possible errors
try {
items[i] = sheets[i].getUsedRange(true).getTexts();
sheetnames[i] = sheets[i].getName();
}
catch (any) { }
}

// return the worksheets array
return [items,sheetnames];
}

字符串
然后保存此文件。
调用表单Power Automate Flow。该流程将具有以下步骤:
Picture of the Flow form the post
顺便说一句-我仍然在网上搜索一个Office的答案,只是保存为CSV(我只需要保存一个工作表-但找不到它)

相关问题