68 lines
2.3 KiB
Python
68 lines
2.3 KiB
Python
import openpyxl
|
|
|
|
class XL:
|
|
def __init__(self, file_path: str):
|
|
"""
|
|
Initialize the class with the path to the Excel file.
|
|
"""
|
|
self.file_path = file_path
|
|
self.workbook = None
|
|
|
|
def __enter__(self):
|
|
"""
|
|
Open the workbook when entering the context.
|
|
"""
|
|
self.workbook = openpyxl.load_workbook(self.file_path)
|
|
return self
|
|
|
|
def __exit__(self, exc_type, exc_val, exc_tb):
|
|
"""
|
|
Close the workbook when exiting the context.
|
|
"""
|
|
if self.workbook:
|
|
self.workbook.close()
|
|
|
|
def sheet_to_dict(self, sheet_name: str):
|
|
"""
|
|
Convert a sheet's data to a dictionary, removing rows with None values.
|
|
"""
|
|
if not self.workbook:
|
|
raise ValueError("Workbook is not loaded. Ensure you use 'with' to open the file.")
|
|
|
|
if sheet_name not in self.workbook.sheetnames:
|
|
raise ValueError(f"Sheet '{sheet_name}' not found in the workbook.")
|
|
|
|
sheet = self.workbook[sheet_name]
|
|
headers = [cell.value for cell in sheet[1] if cell.value is not None]
|
|
|
|
data_list = []
|
|
for row in sheet.iter_rows(min_row=2, values_only=True):
|
|
row_dict = {headers[i]: cell for i, cell in enumerate(row) if i < len(headers)}
|
|
# Remove keys with None values
|
|
row_dict = {key: value for key, value in row_dict.items() if value is not None}
|
|
if row_dict: # Only include non-empty rows
|
|
data_list.append(row_dict)
|
|
|
|
return data_list
|
|
|
|
def json_to_sheet(self, data:list[dict]):
|
|
"""
|
|
Convert a json to a sheet.
|
|
"""
|
|
workbook = openpyxl.Workbook()
|
|
sheet = workbook.active
|
|
|
|
headers = []
|
|
for item in data:
|
|
for key in item:
|
|
if key not in headers:
|
|
headers.append(key)
|
|
|
|
for col_index, header in enumerate(headers):
|
|
sheet.cell(row=1, column=col_index+1, value=header)
|
|
|
|
for row_index, row_data in enumerate(data):
|
|
for col_index, key in enumerate(headers):
|
|
sheet.cell(row=row_index+2, column=col_index+1, value=row_data.get(key, ""))
|
|
workbook.save(self.file_path)
|