Skip to content

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 ​

javascript
/**
 * 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 ​