How can read local excel from designer?

Ignition
1 
import sys
import os
from java.io import FileInputStream
from org.apache.poi.ss.usermodel import WorkbookFactory, CellType
from org.apache.poi.xssf.usermodel import XSSFWorkbook
from org.apache.poi.hssf.usermodel import HSSFWorkbook

def log_error(message, exception=None):

    logger = system.util.getLogger("ExcelReader")
    logger.error(message)
    
    if exception:

        if hasattr(system, 'util') and hasattr(system.util, 'getStackTrace'):
            logger.error("stack error: %s" % system.util.getStackTrace(exception))
        else:

            logger.error("error info: %s" % str(exception))

def get_cell_value(cell):

    if not cell:
        return ""
    
    try:
        cell_type = cell.getCellType()
        
        if cell_type == CellType.STRING:
            return cell.getStringCellValue() or ""
        elif cell_type == CellType.NUMERIC:

            if cell.getCellStyle().getDataFormatString() in [
                'm/d/yy', 'm/d/yyyy', 'mm/dd/yyyy', 'yyyy-mm-dd'
            ]:
                return str(cell.getDateCellValue())
            else:

                num_val = cell.getNumericCellValue()
                if abs(num_val - round(num_val)) < 1e-6:
                    return str(int(num_val))
                else:
                    return str(num_val)
        elif cell_type == CellType.BOOLEAN:
            return str(cell.getBooleanCellValue())
        elif cell_type == CellType.BLANK or cell_type == CellType._NONE:
            return ""
        else:
            return str(cell.toString())
    except:
        return ""

def read_excel_poi(file_path, sheet_name=0, header_row=0):

    try:
  
        if not os.path.exists(file_path):
            log_error("empty : %s" % file_path)
            return []
        

 
        fis = FileInputStream(file_path)
        if file_path.lower().endswith('.xlsx'):
            workbook = XSSFWorkbook(fis)
        else:
            workbook = HSSFWorkbook(fis)
 
        sheet_idx = sheet_name if isinstance(sheet_name, int) else workbook.getSheetIndex(sheet_name)
        if sheet_idx == -1:

            workbook.close()
            fis.close()
            return []
        
        sheet = workbook.getSheetAt(sheet_idx)
        
        data = []
        headers = []
        

        max_rows = sheet.getPhysicalNumberOfRows()
        if header_row < max_rows:
            header_row_obj = sheet.getRow(header_row)
            if header_row_obj:
                num_cols = header_row_obj.getLastCellNum()
                headers = [get_cell_value(header_row_obj.getCell(c)) for c in range(num_cols)]
                headers = [h.strip() for h in headers if h.strip()] 
        

        for row_idx in range(header_row + 1, max_rows):
            row = sheet.getRow(row_idx)
            if row:
                row_data = [get_cell_value(row.getCell(c)) for c in range(row.getLastCellNum())]
                

                if headers:

                    while len(row_data) < len(headers):
                        row_data.append("")
                    row_dict = dict(zip(headers, row_data))
                else:
                    row_dict = {"Col{i+1}": row_data[i] for i in range(len(row_data))}
                
                data.append(row_dict)
        

        workbook.close()
        fis.close()
        

            
        return data
        
    except Exception as e:
        error_msg = "Excel error: %s" % str(e)
        log_error(error_msg, e)
        print(error_msg)
        return []

I run this script in button event,but can’t get the data

2

Are you sure posted the entire script? The last line I see is:
sheet = workbook.getSheetAt(sheet_idx)
What are you doing with sheet?

3

have posted all

4

Could you provide more insight than “can’t get the data”?

What have you tried to solve it?
Does the code return an error?
Which parts of the code do work perform as expected? You use a logger for errors. Do they not write anything useful to the logs?

Concider using the logger to e.g.:

  • Log the max_rows value to see whether reading the workbook went successfully
  • Log the headers, to see whether you have the right amount of column
1 Like
5

actually ,it can execute in console

image
image1185Ă—178 11.5 KB

but when I run it in button script,the error shows can’t find the file

image
image771Ă—350 10.6 KB

6

Thanks for sharing this insight.

Try running the code from this forum page in your script console.
It will tell you whether your script is executed in the Client scope (1), designer scope (2) or gateway scope (0).

If you’re running code in the gateway scope, the console is reading the file on the gateway’s PC, and your designer script is reading the file on your local PC.

7

It’s executed in designer scope,so when it is executed in button events, it’s in gateway scope?

8

I assumed your Script Console scope would be gateway, and your button would be designer/client. My assumption was wrong. Just to test, you could try this code in a button as well, and see if the scopes are the same.

I see you have blurred out your filepath. This thread is > 10 years old, but might be relevant.

Option 1:

Option 2: