如何使用 ONLYOFFICE 宏将列复制到单独的电子表格中

2024年07月19日作者: Alina

ONLYOFFICE 宏拥有无限可能性，将需要数小时才能完成的复杂任务，用它只需单击一下即可完成。最近，我们收到了一位 ONLYOFFICE 用户的请求，要求提供一种宏，用复制数据来自动化工作流程。

阅读本文，深入了解具体细节，以及如何开发宏来满足这个需求。

Use ONLYOFFICE macros to copy columns into separate spreadsheets

构建宏

  const oWorksheet = Api.GetActiveSheet();
  const oRange = oWorksheet.GetSelection();

首先，我们获取活动工作表以及 oWorksheet 和 oRange 变量中的选择。

构成此宏的 3 个函数有：getColHeader（）、copyColumns（） 和 copyValues（）。

首先是 getColHeader（） 函数。

  const getColHeader = (colNumber) => {
    const targetColumn = oRange.GetCols(colNumber);
    const colHeader = targetColumn.GetValue()[0][0];
    return colHeader !== null && colHeader !== "" ? colHeader : -1;
  };

getColHeader（） 函数采用单个参数 colNumber，该参数指定列号。此函数检查指定列的第一行中的有效值。如果存在有效值，则 returns 该值，否则，它将 returns -1。

接下来看看 copyColumns（） 函数。

  const copyColumns = () => {
    let end = false;
    let i = 2;

    while (!end) {
      i++;
      const sheetName = getColHeader(i);
      if (sheetName === -1) {
        end = true;
        break;
      } else if (sheetName) {
        const columns = oRange.GetCols(i);
        columns.ForEach((cell) => {
          copyValues(cell, sheetName, 1);
        });

        for (let j = 1; j <= 2; j++) {
          const col = oRange.GetCols(j);
          col.ForEach((cell) => {
            copyValues(cell, sheetName);
          });
        }
      } else {
        console.error("Fatal error in copy horizontal logic.");
        end = true;
        break;
      }
    }
  };

copyColumns（） 函数从第三列开始遍历当前工作表的列。对于每一列，它使用 getColHeader（） 函数检索列标题。如果标题有效，则使用 copyValues（） 函数将该列中的值和格式复制到标题指定的目标工作表。

此外，它还确保前两列（被视为常见列）也被复制到每个目标工作表。该过程将继续进行，直到遇到无效的标头，此时循环终止。这是宏的主要函数，并调用其他辅助函数。

注：在此代码中，我们假设有两个公共列，并且从第三列开始，要分别复制到每个工作表的特定列。这些值可以根据您的特定用例进行调整。

下面是 copyValues（） 函数。

const copyValues = (range, sheetName, copyType = 0) => {

copyValues（） 函数将源范围的值和格式复制到目标工作表。它采用三个参数：range（包含单元格数据的范围对象）、sheetName（目标工作表的名称）和 copyType（指定要复制的列是默认列还是特定列）。

    const oValue = range.GetValue();
    const oCharacters = range.GetCharacters(0, 2);
    const oFont = oCharacters.GetFont();
    const fontName = oFont.GetName();
    const oSize = oFont.GetSize();
    const isBold = oFont.GetBold();
    const isItalic = oFont.GetItalic();
    const rowNo = range.GetRow() - 1;
    let colNo;

首先，该函数检索源单元格的值和格式（字体名称、大小、粗体、斜体）。

const targetSheet = Api.GetSheet(sheetName);

    if (!targetSheet) {
      console.error(`Sheet with name ${sheetName} not found.`);
      return;
    }
    if (copyType === 1) {
      colNo = 2;
    } else {
      colNo = range.GetCol() - 1;
    }

接下来，使用从 getColHeader（）函数获取的 sheetName 变量检索目标工作表。

如果目标工作表不存在，则会记录错误，并且会中止宏执行。

    if (oValue === null || oValue === "") {
      targetSheet.GetRangeByNumber(rowNo, colNo).SetValue(" ");
    } else {
      oFont.SetName(fontName);
      targetSheet.GetRangeByNumber(rowNo, colNo).SetValue(oValue);
      targetSheet.GetRangeByNumber(rowNo, colNo).SetFontName(fontName);
      targetSheet.GetRangeByNumber(rowNo, colNo).SetFontSize(oSize);

      if (isBold) {
        targetSheet.GetRangeByNumber(rowNo, colNo).SetBold(true);
      }
      if (isItalic) {
        targetSheet.GetRangeByNumber(rowNo, colNo).SetItalic(true);
      }
    }
  };

最后，如果一切正常，并且没有发生错误，则 copyValues（） 函数会在目标工作表的相应单元格中设置值和格式。

copyColumns();

最后，我们通过调用 copyColumns（） 函数来结束宏，该函数是宏的入口点。

完整的宏代码

以下是整个宏的代码：

// Macro Workflow and Info
// This macro automates the process of copying specific columns to designated sheets
// and 'common columns' to all sheets. It ensures data and formatting consistency
// across multiple sheets. You can specify the common columns, which must start from the left and be continuous.
// For specific columns, as long as you have a column apart from the common ones with a valid header and a sheet with the same name exists, the macro will take that column into consideration/.
//If you have a secific column for which a sheet doesnt exist, the console will give you an error.
// For any other problems, be sure to check the console logs.
// Example Scenario:
// Suppose we have columns 'Name', 'Type', 'Calcium', and 'Magnesium' in a sheet called 'FOOD'.
// We also have sheets named 'Calcium' and 'Magnesium'.
// Selecting all data in the 'FOOD' sheet and running the macro will:
// 1. Copy columns 'Name' and 'Type' to both 'Calcium' and 'Magnesium' sheets.
// 2. Copy the 'Calcium' column to the 'Calcium' sheet.
// 3. Copy the 'Magnesium' column to the 'Magnesium' sheet.

(function () {
  const oWorksheet = Api.GetActiveSheet();
  const oRange = oWorksheet.GetSelection();

  /**
   * Gets the value in the first row of the Column
   *
   * @param {number} colNumber
   * @returns {string}

   */
  const getColHeader = (colNumber) => {
    const targetColumn = oRange.GetCols(colNumber);
    const colHeader = targetColumn.GetValue()[0][0];

    return colHeader !== null && colHeader !== "" ? colHeader : -1;
  };

  /**
   * Iterates through all valid columns and copies them to target sheet.
   */
  const copyColumns = () => {
    let end = false;
    let i = 2;

    while (!end) {
      i++;
      const sheetName = getColHeader(i);
      if (sheetName === -1) {
        end = true;
        break;
      } else if (sheetName) {
        const columns = oRange.GetCols(i);
        columns.ForEach((cell) => {
          copyValues(cell, sheetName, 1);
        });

        // Copy the common rows in every sheet.
        for (let j = 1; j <= 2; j++) { const col = oRange.GetCols(j); col.ForEach((cell) => {
            copyValues(cell, sheetName);
          });
        }
      } else {
        console.error("Fatal error in copy horizontal logic.");
        end = true;
        break;
      }
    }
  };

  /**
   * Copies the values and formatting from the source range to the target sheet.
   * @param {object} range - The range object containing the cell data.
   * @param {string} sheetName - The name of the target sheet to copy the values to.
   * @param {number} copyType - Indicates type of copy operation. 1 for fixed column copy, default or dyanamic.
   */
  const copyValues = (range, sheetName, copyType = 0) => {
    const oValue = range.GetValue();
    const oCharacters = range.GetCharacters(0, 2);
    const oFont = oCharacters.GetFont();
    const fontName = oFont.GetName();
    const oSize = oFont.GetSize();
    const isBold = oFont.GetBold();
    const isItalic = oFont.GetItalic();
    const rowNo = range.GetRow() - 1;
    let colNo;

    const targetSheet = Api.GetSheet(sheetName);

    if (!targetSheet) {
      console.error(`Sheet with name ${sheetName} not found.`);
      return;
    }

    if (copyType === 1) {
      colNo = 2;
    } else {
      colNo = range.GetCol() - 1;
    }

    if (oValue === null || oValue === "") {
      targetSheet.GetRangeByNumber(rowNo, colNo).SetValue(" ");
    } else {
      oFont.SetName(fontName);
      targetSheet.GetRangeByNumber(rowNo, colNo).SetValue(oValue);
      targetSheet.GetRangeByNumber(rowNo, colNo).SetFontName(fontName);
      targetSheet.GetRangeByNumber(rowNo, colNo).SetFontSize(oSize);

      if (isBold) {
        targetSheet.GetRangeByNumber(rowNo, colNo).SetBold(true);
      }

      if (isItalic) {
        targetSheet.GetRangeByNumber(rowNo, colNo).SetItalic(true);
      }
    }
  };

  copyColumns();
})();