Omit Excel hidden rows

Revit
,
9

A solution using openpyxl

image
image747×566 58.2 KB

import sys
import clr
from openpyxl import load_workbook

xls_path = IN[0]
sheet_name = IN[1]

wb = load_workbook(xls_path) 
ws = wb[sheet_name]
data = []

for row in ws: 
    if ws.row_dimensions[row[0].row].hidden == False:
      data.append([cell.value for cell in row])
      
OUT = data

Another solution if you are comfortable with OpenXml

import clr
import sys
import System
from System import Array
from System.Collections.Generic import *

clr.AddReference('DocumentFormat.OpenXml')
import DocumentFormat
from DocumentFormat.OpenXml import *
from DocumentFormat.OpenXml.Packaging import *
from DocumentFormat.OpenXml.Spreadsheet import *


def ReadExcel(filepath, sheetName="", read_hidden_rows=True):
    lst_rows = []
    fileStream = System.IO.FileStream(filepath, System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.ReadWrite)
    spreadsheetDoc = SpreadsheetDocument.Open(fileStream, False)
    try:
        workbookPart = spreadsheetDoc.WorkbookPart
        if System.String.IsNullOrEmpty(sheetName):
            sheet = workbookPart.Workbook.Sheets.GetFirstChild[Sheet]()
        else:
            sheet = next((s for s in workbookPart.Workbook.Sheets if s.Name.Value == sheetName), None)
        print(sheet)
        print([s for s in spreadsheetDoc.WorkbookPart.Workbook.Sheets])
        # get all sheet Name
        sheetsName = [s.Name.Value for s in spreadsheetDoc.WorkbookPart.Workbook.Sheets]
        print(sheetsName)
        worksheet = spreadsheetDoc.WorkbookPart.GetPartById(sheet.Id.Value).Worksheet
        #
        rows = worksheet.GetFirstChild[SheetData]().Elements[Row]()
        if read_hidden_rows :
            lst_rows = [row for row in rows ]
        else:
            lst_rows = [row for row in rows if not (row.Hidden is not None and row.Hidden.Value) ]
    except Exception as ex:
        print(ex)
        lst_rows = []
    finally:
        spreadsheetDoc.Close()
        fileStream.Dispose()
    # REST of code to be completed to read cell values
    # TODO
    # TODO 
    return [x.GetType().ToString() for x in lst_rows]
    
xls_file_path = IN[0]
sheet_name = IN[1]

OUT = ReadExcel(xls_file_path, sheet_name, read_hidden_rows=False)
4 Likes