PROBLEM: script does not translate into excel format properly, excel rejects filters and table formatting.
Long explanation the bot didn't like: So i have a .csv file that has a bunch of data in it that i do not need. I have to pull this report daily, format into several tables and filter several columns to create several tables and send it out for work. I wrote this program that allows you to select the .csv file, convert to xlxs, creates 4 sheets, with 4 tables, it gives each sheet a name, creates the header, removes several uneeded columns, and converts each sheets data into a table, also, sheet 1 is hidden on purpose, filters a few columns on each sheet in a different manner, and gives each sheet its own theme..
Get no errors from terminal or debug. It Creates the file.
Get this error from Excel as it forces a repair on the sheet.
Removed Feature: AutoFilter from /xl/tables/table1.xml part (Table)
Removed Feature: Table from /xl/tables/table1.xml part (Table)
Removed Feature: AutoFilter from /xl/tables/table2.xml part (Table)
Removed Feature: Table from /xl/tables/table2.xml part (Table)
Removed Feature: AutoFilter from /xl/tables/table3.xml part (Table)
Removed Feature: Table from /xl/tables/table3.xml part (Table)
Excel sheet snapshot*
*
Here is my wacky code:
from PyQt5.QtWidgets import QApplication, QWidget, QVBoxLayout, QPushButton, QFileDialog
from pandasgui import show
import pandas as pd
import sys
import openpyxl
class DiscoSnakeGUI(QWidget):
def __init__(self):
super().__init__()
self.initUI()
def initUI(self):
self.setWindowTitle("The Disco Snake")
self.setGeometry(100, 100, 800, 600)
self.layout = QVBoxLayout(self)
self.open_button = QPushButton("Open .csv file")
self.open_button.clicked.connect(self.load_csv)
self.layout.addWidget(self.open_button)
def load_csv(self):
csv_file, _ = QFileDialog.getOpenFileName(self, "Open CSV", "", "CSV Files (*.csv)")
if csv_file:
print(f"CSV file path: {csv_file}")
self.process_csv(csv_file)
def process_csv(self, csv_file):
try:
with open(csv_file, 'r') as file:
lines = file.readlines()
start_index = None
for index, line in enumerate(lines):
if line.strip() == 'Devices:':
start_index = index + 1
break
df = pd.read_csv(csv_file, skiprows=range(start_index), header=None, engine='python')
except pd.errors.ParserError as e:
print(f"Error parsing CSV file: {e}")
return
column_headers = [
"Server Name", "Device Name", "Make", "Model", "Location", "Logical ID",
"Device ID", "Camera ID String", "IP Address", "MAC Address", "Firmware Version",
"Required Firmware Version", "Serial Number", "Connected", "Visible", "Error Flags",
"Status", "Bitrate", "Resolution", "Quality", "Image Rate", "Encryption",
"Retention", "Appearance Search:", "Face Recognition:", " "
]
df.columns = df.columns.astype(str)
df.columns = column_headers
excel_file = "output.xlsx"
df.to_excel(excel_file, index=False)
columns_to_remove = ['Logical ID', 'Device ID', 'Camera ID String',
'Firmware Version', 'Required Firmware Version', 'Error Flags',
'Encryption', 'Retention', 'Appearance Search:', 'Face Recognition:']
df = df.drop(columns=columns_to_remove)
writer = pd.ExcelWriter(excel_file, engine='xlsxwriter')
df.to_excel(writer, sheet_name='MASTER', index=False)
df.to_excel(writer, sheet_name='AVIGILON DOWN', index=False)
df.to_excel(writer, sheet_name='LEGACY DOWN', index=False)
df.to_excel(writer, sheet_name='LEGACY UP', index=False)
writer.close()
workbook = openpyxl.load_workbook(excel_file)
for sheet in workbook.sheetnames:
ws = workbook[sheet]
if sheet == 'MASTER':
workbook[sheet].sheet_state = 'hidden'
elif sheet == 'AVIGILON DOWN':
table = openpyxl.worksheet.table.Table(displayName="Table1", ref=ws.dimensions)
style = openpyxl.worksheet.table.TableStyleInfo(name="TableStyleMedium3")
table.tableStyleInfo = style
ws.add_table(table)
make_column_index = df.columns.get_loc("Make") + 1
ws.auto_filter.ref = ws.dimensions
ws.auto_filter.add_filter_column(make_column_index, ['Avigilon'])
connected_column_index = df.columns.get_loc("Connected") + 1
ws.auto_filter.add_filter_column(connected_column_index, ['FALSE'])
elif sheet == 'LEGACY DOWN':
table = openpyxl.worksheet.table.Table(displayName="Table2", ref=ws.dimensions)
style = openpyxl.worksheet.table.TableStyleInfo(name="TableStyleMedium3")
table.tableStyleInfo = style
ws.add_table(table)
make_column_index = df.columns.get_loc("Make") + 1
ws.auto_filter.ref = ws.dimensions
ws.auto_filter.add_filter_column(make_column_index, [])
ws.auto_filter.add_filter_column(make_column_index, ['Avigilon'], blank=True)
connected_column_index = df.columns.get_loc("Connected") + 1
ws.auto_filter.add_filter_column(connected_column_index, ['FALSE'])
elif sheet == 'LEGACY UP':
table = openpyxl.worksheet.table.Table(displayName="Table3", ref=ws.dimensions)
style = openpyxl.worksheet.table.TableStyleInfo(name="TableStyleMedium2")
table.tableStyleInfo = style
ws.add_table(table)
make_column_index = df.columns.get_loc("Make") + 1
ws.auto_filter.ref = ws.dimensions
ws.auto_filter.add_filter_column(make_column_index, [])
ws.auto_filter.add_filter_column(make_column_index, ['Avigilon'], blank=True)
connected_column_index = df.columns.get_loc("Connected") + 1
ws.auto_filter.add_filter_column(connected_column_index, ['TRUE'])
workbook.save(excel_file)
blank_header_columns = [col for col in df.columns if 'Unnamed' in col]
print(blank_header_columns)
print(f"Excel file saved: {excel_file}")
if __name__ == '__main__':
app = QApplication(sys.argv)
gui = DiscoSnakeGUI()
gui.show()
sys.exit(app.exec_())
Thanks to whoever solves this puzzle!