Problem with reading excel

Developers
,
1

Hello everyone, I’m having a bit of an issue with reading and printing the content of an Excel file. The problem is that after running the code below, the values of each cell in the Excel file are being printed out on separate lines. However, I want to print the content exactly as it appears in the Excel file (with clear rows and columns). Please help me modify the code to achieve this. Thank you.

def ReadExcelFile(path):
    # Instantiate the Excel Application
    ex = Excel.ApplicationClass()
    # Make it visible (optional)
    ex.Visible = False
    # Disable alerts
    ex.DisplayAlerts = False
    
    # Open workbook
    workbook = ex.Workbooks.Open(path)
    # Select the first worksheet
    ws = workbook.Worksheets[1]
    
    # Find the used range of cells
    used_range = ws.UsedRange
    # Get all values in the used range
    all_data = used_range.Value2
    
    # Close workbook and Excel application
    workbook.Close(False)
    ex.Quit()
    
    # Release COM objects
    Marshal.ReleaseComObject(used_range)
    Marshal.ReleaseComObject(ws)
    Marshal.ReleaseComObject(workbook)
    Marshal.ReleaseComObject(ex)
    
    return all_data


#Import Excel
# excelFilePath = forms.pick_excel_file()
excelFilePath = "C:\\Users\\PC\\Desktop\\Add Shared Param\\Add Shared Param.xlsx"
excelData = ReadExcelFile(excelFilePath)

for row in excelData:
    print (row)

image
image1850×574 142 KB

2

Hi,

try this version


import clr
import sys
import System
clr.AddReference("System.Numerics")
from System import Array
from System.Collections.Generic import *

from System.Runtime.InteropServices import Marshal

class ExcelEnum:
    """
    values Enum from API Interop Mircrosoft Doc
    """
    XlCellType_xlCellTypeLastCell  = 11

def ReadExcelFile(path):
    # Instantiate the Excel Application
    ex = System.Activator.CreateInstance(System.Type.GetTypeFromProgID("Excel.Application", True))
    # Make it visible (optional)
    ex.Visible = False
    # Disable alerts
    ex.DisplayAlerts = False
    
    # Open workbook
    workbook = ex.Workbooks.Open(path)
    # Select the first worksheet
    ws = workbook.Worksheets[1]
    # get the last cell
    last = ws.Cells.SpecialCells(ExcelEnum.XlCellType_xlCellTypeLastCell, System.Type.Missing)
    rowCountF = last.Row
    colCountF = last.Column
    # Find the used range of cells
    used_range = ws.UsedRange
    # Get all values in the used range
    fullvalue = list(used_range.Value2)
    #split list into sublist with number of colum
    n = colCountF                    
    datas = list(fullvalue[i:i+n] for i in range(0, len(fullvalue ), n))
    
    # Close workbook and Excel application
    workbook.Close(False)
    ex.Quit()
    
    # Release COM objects
    Marshal.ReleaseComObject(used_range)
    Marshal.ReleaseComObject(ws)
    Marshal.ReleaseComObject(workbook)
    Marshal.ReleaseComObject(ex)
    
    return datas


#Import Excel
file_path = IN[0]
excelData = ReadExcelFile(file_path)
OUT = excelData

Alternatively, you can use:

  • openpyxl or/and pandas (CPython3 Engine)
  • Microsoft OpenXML (CPython3 or IronPython Engine)
4 Likes
3

Fantastic, it’s exactly what I want. Thank you very much.

image
image899×114 3.11 KB