|
#!/usr/bin/env python3
|
|
# -*- coding: utf-8 -*-
|
|
"""
|
|
Slack JSON 메시지를 날짜별로 재정리하여 Excel 파일로 변환하는 스크립트
|
|
각 메시지를 타임스탬프 기준으로 올바른 날짜 시트에 배치합니다.
|
|
"""
|
|
|
|
import json
|
|
import os
|
|
import re
|
|
from datetime import datetime
|
|
from pathlib import Path
|
|
from collections import defaultdict
|
|
from openpyxl import Workbook
|
|
from openpyxl.styles import Font, PatternFill, Alignment
|
|
from openpyxl.utils import get_column_letter
|
|
|
|
def clean_text_for_excel(text):
|
|
"""Excel에서 허용하지 않는 문자 제거"""
|
|
if not text:
|
|
return ''
|
|
text = str(text)
|
|
# Excel에서 허용하지 않는 제어 문자와 zero-width 문자 제거
|
|
illegal_chars = re.compile(r'[\x00-\x08\x0B\x0C\x0E-\x1F\u200B-\u200D\uFEFF]')
|
|
text = illegal_chars.sub('', text)
|
|
# Excel 셀 최대 길이 제한 (32,767자)
|
|
if len(text) > 32767:
|
|
text = text[:32767]
|
|
return text
|
|
|
|
def parse_username(username):
|
|
"""username에서 정보 추출: [사용자ID][전화번호][메시지유형]"""
|
|
if not username:
|
|
return '', '', ''
|
|
|
|
# [xxx][yyy][zzz] 형식 파싱
|
|
parts = username.split('][')
|
|
if len(parts) >= 3:
|
|
user_id = parts[0].strip('[')
|
|
phone = parts[1]
|
|
msg_type = parts[2].strip(']')
|
|
return user_id, phone, msg_type
|
|
elif username == 'incoming-webhook':
|
|
return 'incoming-webhook', '', ''
|
|
else:
|
|
return username, '', ''
|
|
|
|
def convert_timestamp(ts):
|
|
"""Unix 타임스탬프를 한국 시간으로 변환"""
|
|
if not ts:
|
|
return ''
|
|
try:
|
|
timestamp = float(ts)
|
|
dt = datetime.fromtimestamp(timestamp)
|
|
return dt.strftime('%Y-%m-%d %H:%M:%S')
|
|
except:
|
|
return ts
|
|
|
|
def get_message_date(ts):
|
|
"""타임스탬프에서 날짜만 추출 (YYYY-MM-DD)"""
|
|
if not ts:
|
|
return None
|
|
try:
|
|
timestamp = float(ts)
|
|
dt = datetime.fromtimestamp(timestamp)
|
|
return dt.strftime('%Y-%m-%d')
|
|
except:
|
|
return None
|
|
|
|
def collect_all_messages():
|
|
"""모든 JSON 파일에서 메시지를 수집하고 날짜별로 그룹화"""
|
|
current_dir = Path('.')
|
|
json_files = sorted(current_dir.glob('*.json'))
|
|
|
|
if not json_files:
|
|
print("JSON 파일을 찾을 수 없습니다.")
|
|
return None
|
|
|
|
print(f"총 {len(json_files)}개의 JSON 파일 발견")
|
|
print("모든 메시지 수집 중...\n")
|
|
|
|
# 날짜별로 메시지를 그룹화: {날짜: [메시지들]}
|
|
messages_by_date = defaultdict(list)
|
|
total_messages = 0
|
|
file_stats = {}
|
|
|
|
for i, json_file in enumerate(json_files, 1):
|
|
file_date = json_file.stem # 파일명에서 날짜 추출
|
|
|
|
try:
|
|
with open(json_file, 'r', encoding='utf-8') as f:
|
|
data = json.load(f)
|
|
|
|
original_count = len(data)
|
|
moved_count = 0
|
|
kept_count = 0
|
|
|
|
for msg in data:
|
|
total_messages += 1
|
|
|
|
# 메시지의 실제 날짜 확인
|
|
msg_date = get_message_date(msg.get('ts'))
|
|
|
|
if msg_date:
|
|
# 날짜별 딕셔너리에 메시지 추가
|
|
messages_by_date[msg_date].append(msg)
|
|
|
|
# 통계 수집
|
|
if msg_date == file_date:
|
|
kept_count += 1
|
|
else:
|
|
moved_count += 1
|
|
else:
|
|
# 타임스탬프가 없으면 파일 날짜 사용
|
|
messages_by_date[file_date].append(msg)
|
|
kept_count += 1
|
|
|
|
file_stats[file_date] = {
|
|
'original': original_count,
|
|
'kept': kept_count,
|
|
'moved': moved_count
|
|
}
|
|
|
|
if moved_count > 0:
|
|
print(f"[{i}/{len(json_files)}] {file_date}: {original_count}개 메시지 → {kept_count}개 유지, {moved_count}개 다른 날짜로 이동")
|
|
else:
|
|
print(f"[{i}/{len(json_files)}] {file_date}: {original_count}개 메시지 (모두 올바른 날짜)")
|
|
|
|
except Exception as e:
|
|
print(f"오류 발생 - {file_date}: {str(e)}")
|
|
continue
|
|
|
|
print(f"\n총 {total_messages:,}개의 메시지 수집 완료")
|
|
print(f"재정리된 날짜 그룹: {len(messages_by_date)}개\n")
|
|
|
|
return messages_by_date, file_stats
|
|
|
|
def create_header_row(ws):
|
|
"""헤더 행 생성 및 스타일 적용"""
|
|
headers = ['시간', '메시지유형', '사용자ID', '전화번호',
|
|
'메시지타입', '메시지내용', '봇ID']
|
|
|
|
# 헤더 스타일
|
|
header_font = Font(bold=True, color="FFFFFF")
|
|
header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
|
|
header_alignment = Alignment(horizontal="center", vertical="center")
|
|
|
|
for col_num, header in enumerate(headers, 1):
|
|
cell = ws.cell(row=1, column=col_num)
|
|
cell.value = header
|
|
cell.font = header_font
|
|
cell.fill = header_fill
|
|
cell.alignment = header_alignment
|
|
|
|
return headers
|
|
|
|
def write_messages_to_sheet(ws, messages):
|
|
"""메시지들을 워크시트에 작성"""
|
|
row_num = 2 # 헤더 다음 행부터 시작
|
|
|
|
for msg in messages:
|
|
user_id, phone, msg_type = parse_username(msg.get('username', ''))
|
|
|
|
ws.cell(row=row_num, column=1, value=convert_timestamp(msg.get('ts')))
|
|
ws.cell(row=row_num, column=2, value=clean_text_for_excel(msg.get('subtype', '')))
|
|
ws.cell(row=row_num, column=3, value=clean_text_for_excel(user_id))
|
|
ws.cell(row=row_num, column=4, value=clean_text_for_excel(phone))
|
|
ws.cell(row=row_num, column=5, value=clean_text_for_excel(msg_type))
|
|
ws.cell(row=row_num, column=6, value=clean_text_for_excel(msg.get('text', '')))
|
|
ws.cell(row=row_num, column=7, value=clean_text_for_excel(msg.get('bot_id', '')))
|
|
|
|
row_num += 1
|
|
|
|
return row_num - 2 # 메시지 개수 반환
|
|
|
|
def adjust_column_widths(ws):
|
|
"""열 너비 자동 조정"""
|
|
column_widths = {
|
|
'A': 20, # 시간
|
|
'B': 15, # 메시지유형
|
|
'C': 20, # 사용자ID
|
|
'D': 15, # 전화번호
|
|
'E': 12, # 메시지타입
|
|
'F': 80, # 메시지내용
|
|
'G': 15, # 봇ID
|
|
}
|
|
|
|
for col, width in column_widths.items():
|
|
ws.column_dimensions[col].width = width
|
|
|
|
def main():
|
|
"""메인 함수"""
|
|
# 1단계: 모든 메시지 수집 및 날짜별 그룹화
|
|
result = collect_all_messages()
|
|
if result is None:
|
|
return
|
|
|
|
messages_by_date, file_stats = result
|
|
|
|
# 2단계: Excel 워크북 생성
|
|
print("Excel 파일 생성 중...\n")
|
|
wb = Workbook()
|
|
|
|
# 기본 시트 제거
|
|
if 'Sheet' in wb.sheetnames:
|
|
wb.remove(wb['Sheet'])
|
|
|
|
# 3단계: 날짜별로 정렬된 시트 생성
|
|
sorted_dates = sorted(messages_by_date.keys())
|
|
total_messages_written = 0
|
|
|
|
for i, date in enumerate(sorted_dates, 1):
|
|
messages = messages_by_date[date]
|
|
|
|
# Excel 시트명 제한 (31자)
|
|
sheet_name = date[:31]
|
|
|
|
print(f"시트 생성 중 [{i}/{len(sorted_dates)}]: {sheet_name} - {len(messages):,}개 메시지")
|
|
|
|
# 새 워크시트 생성
|
|
ws = wb.create_sheet(title=sheet_name)
|
|
|
|
# 헤더 행 생성
|
|
create_header_row(ws)
|
|
|
|
# 메시지 데이터 입력
|
|
message_count = write_messages_to_sheet(ws, messages)
|
|
total_messages_written += message_count
|
|
|
|
# 열 너비 조정
|
|
adjust_column_widths(ws)
|
|
|
|
# 첫 행 고정 (스크롤 시 헤더 유지)
|
|
ws.freeze_panes = 'A2'
|
|
|
|
# 4단계: Excel 파일 저장
|
|
# 현재 시간을 파일명에 추가
|
|
now = datetime.now()
|
|
timestamp = now.strftime('%Y%m%d_%H%M')
|
|
output_file = f'mjon메시지_날짜별_재정리_{timestamp}.xlsx'
|
|
wb.save(output_file)
|
|
|
|
print(f"\n변환 완료!")
|
|
print(f"출력 파일: {output_file}")
|
|
print(f"총 시트 수: {len(sorted_dates)}개")
|
|
print(f"총 메시지 수: {total_messages_written:,}개")
|
|
|
|
# 5단계: 재정리 통계 출력
|
|
print("\n=== 재정리 통계 ===")
|
|
total_moved = sum(stats['moved'] for stats in file_stats.values())
|
|
print(f"다른 날짜로 이동된 메시지: {total_moved:,}개")
|
|
|
|
if __name__ == '__main__':
|
|
main()
|
