@Villeroy , that’s an interesting approach!
I’m using an alternative “traditional” approach to displaying the same data in an ODS document.
You need to run the import_user_archive macro (Python) and select the user_archive... zip file downloaded from the forum in the file selection dialog.
The number in the URL column indicates the post’s sequence number in the thread.
# coding: utf-8
import zipfile
import csv
from io import TextIOWrapper
from datetime import datetime, timedelta
import uno
from com.sun.star.lang import Locale
def import_user_atchive(pyPath=None):
if pyPath == None:
ctx = XSCRIPTCONTEXT.getComponentContext()
smgr = ctx.getServiceManager()
file_picker = smgr.createInstanceWithContext(
"com.sun.star.ui.dialogs.FilePicker", ctx
)
file_picker.appendFilter("user atchive", "user_archive*.zip")
result = file_picker.execute()
if result != 1:
return None
pyPath = file_picker.getSelectedFiles()[0]
cols_title = [
"Date",
"URL",
"Topic",
"Post",
"Categories",
"PM",
"Likes",
"Replies",
]
cols_width = [3, 1.5, 5, 15, 2, 1.5, 2, 2] # centimeter
formula_array = parse_user_archive(uno.fileUrlToSystemPath(pyPath), cols_title)
doc = XSCRIPTCONTEXT.getDesktop().loadComponentFromURL(
"private:factory/scalc", "_default", 0, ()
)
sheet = doc.Sheets.getByIndex(0)
range = sheet.getCellRangeByPosition(
0, 0, len(cols_title) - 1, len(formula_array) - 1
)
for i, value in enumerate(cols_width):
col = range.getColumns().getByIndex(i)
col.Width = int(value * 1000)
if i == 0: # Date
col.NumberFormat = doc.getNumberFormats().addNewConverted(
"YYYY-MM-DD HH:MM", Locale(Country="US", Language="en"), Locale()
)
elif i == 1: # Url
col.CellStyle = "Hyperlink"
elif i == 2 or i == 3: # Topic, Post
col.IsTextWrapped = True
range.CharLocale = Locale(Language="zxx")
range.setFormulaArray(formula_array)
range.getRows().getByIndex(0).CellStyle = "Accent 3" # header row
disp = smgr.createInstanceWithContext("com.sun.star.frame.DispatchHelper", ctx)
frame = doc.CurrentController.Frame
for uno_cmd in ("DataFilterAutoFilter", "FreezePanesRow"):
disp.executeDispatch(frame, f".uno:{uno_cmd}", "", 0, ())
sheet.setName("user_archive")
doc.setTitle(pyPath.rsplit("/", 1)[1].rsplit(".", 1)[0])
def parse_user_archive(path, cols_title):
with zipfile.ZipFile(path, "r") as zip_file:
with zip_file.open("user_archive.csv") as csv_file:
text_file = TextIOWrapper(csv_file, encoding="utf-8")
csv_reader = csv.reader(text_file, delimiter=",", quotechar='"')
out = [cols_title]
# csv file header row:
# topic_title,categories,is_pm,post,like_count,reply_count,url,created_at
# [0] [1] [2] [3] [4] [5] [6] [7]
for row in csv_reader:
if csv_reader.line_num > 1:
url = row[6]
num_post = url.rsplit("/", 1)[1]
dt = date_to_excel_date(datetime.fromisoformat(row[7][:-1]))
out.append(
[
dt,
f'=HYPERLINK("{url}";"{num_post}")',
row[0],
row[3],
row[1],
1 if row[2] == "Yes" else "",
row[4],
row[5],
]
)
return out
def date_to_excel_date(pydate, nulldate=datetime(1899, 12, 30)):
return (pydate - nulldate) / timedelta(days=1)
g_exportedScripts = (import_user_atchive,)
UserArchive.ods (10.9 KB)
