Upload Excel Files and Parse Images

Background

Xiaomei, a tester at a technology company, needs to regularly synchronize Excel files containing system bug statistics provided by clients to the Informat system. These Excel files include descriptions, recording times, and screenshots.

Implementation Steps

We can implement this function through the following methods:

• First, create a "Data Table" and "Website and Resource Hosting Module" in the application design

• Upload the Excel template to the newly created "Website and Resource Hosting Module"
• Copy the preview address + resource file name
• Open the data table application design and go to "Data Table >> Toolbar >> Add Control"
• Select "Call Automation" for the operation to be executed in the control
• Define file upload in the automation
• Set to accept only Excel format
• Set the Excel template link
• Use scripts to read Excel content

Read Excel and Store to Data Table

/**
 * Get Excel data
 * @param filePath File path
 * @returns {*[]}
 */
const getExcelRowDataList = (filePath) => {
  const workbook = informat.excel.openExistFile(filePath);
  const rowDataList = [];
  // Default read first sheet
  const sheet = workbook.getSheetAt(0);
  // Return all cell data
  const firstRowNum = sheet.getFirstRowNum();
  for (let rowIndex = firstRowNum; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
    const rowData = [];
    const row = sheet.getRow(rowIndex);
    for (let colIndex = row.getFirstCellNum(); colIndex < row.getLastCellNum(); colIndex++) {
      let value = null;
      const cell = row.getCell(colIndex);
      if (cell) {
        // Get cell content
        value = cell.getValue();
      }
      rowData.push(value);
    }
    rowDataList.push(rowData);
  }
  const excelPictureMap = new Map();
  /**
   * sheet.getPictures() This method can get all pictures
   * After reading pictures, you can use picture's save(localPath), saveStorage(path), saveAttachment(tableKey, fieldKey, fieldName)
   * to store file content to local sandbox or shared storage
   */
  sheet.getPictures().forEach((picture) => {
    const row = picture.getRow();
    const column = picture.getColumn();
    let integerExcelPictureMap;
    if (!excelPictureMap.has(row)) {
      integerExcelPictureMap = new Map();
      excelPictureMap.set(row, integerExcelPictureMap);
    } else {
      integerExcelPictureMap = excelPictureMap.get(row);
    }
    let pictureList = integerExcelPictureMap.get(column);
    if (!pictureList) {
      pictureList = [];
      integerExcelPictureMap.set(column, pictureList);
    }
    pictureList.push(picture);
  });

  excelPictureMap.forEach((rowMap, row) => {
    // Cannot exceed maximum row
    if (row > rowDataList.length - 1) {
      return;
    }
    const colList = rowDataList[row];
    rowMap.forEach((col, colIdx) => {
      if (colIdx > colList.length - 1) {
        return;
      }
      colList.splice(colIdx, 1, col);
    });
  });
  return rowDataList;
};

/**
 * Return object array based on mapping relationship from all rows and columns
 * @param filePath File path
 * @param mappings Data column key mapping relationship
 * @param dataStartIdx Start index
 * @returns {*[]|*}
 */
const parseExcelRowDataList = (filePath, mappings, dataStartIdx) => {
  // Default start from second row
  if (undefined === dataStartIdx || null === dataStartIdx) {
    dataStartIdx = 1;
  }
  if (!filePath || !mappings) {
    return [];
  }
  const excelRowDatList = getExcelRowDataList(filePath);
  if (0 === excelRowDatList.length) {
    return excelRowDatList;
  }
  const rowList = [];
  for (let i = dataStartIdx; i < excelRowDatList.length; i++) {
    const row = excelRowDatList[i];
    const data = {};
    for (let mappingsKey in mappings) {
      const name = mappings[mappingsKey];
      data[name] = row[mappingsKey];
    }
    rowList.push(data);
  }
  return rowList;
};

// Get the file list returned by the [Upload File] step
const fileList = automatic.getVar("fileList");
// Get file ID
const fileId = fileList[0].id;
// Download the file to local storage
informat.storage.download("automatic/" + fileId, fileId);

// Define column mapping identifiers
const fieldNameMappings = {
  0: "module_name", // Module name
  1: "priority", // Priority
  2: "expected_performance_date", // Expected completion date
  3: "photos", // Pictures
  4: "remark", // Remarks
};
// Local path
const filePath = fileId;

/**
 * Get all rows and columns through mapping
 */
const rowDataList = parseExcelRowDataList(filePath, fieldNameMappings, 1);

// Upload all pictures
rowDataList.forEach((row) => {
  const photos = row.photos;
  if (photos) {
    const attachmentList = [];
    for (let i = 0; i < photos.length; i++) {
      const photo = photos[i];
      const attachment = photo.saveAttachment("tab", "photos", "Image" + i + ".png");
      attachmentList.push(attachment);
    }
    row.photos = attachmentList;
  }
  informat.table.insert("tab", row);
});

Effect