Spaces:
Sleeping
Sleeping
| import pandas as pd | |
| import xlsxwriter | |
| from prediction_engine import TRAIN_TOWNS, TRAIN_PROP_TYPES | |
| def generate_excel_template(filename="template_input.xlsx"): | |
| """ | |
| Membuat template Excel dengan Validasi Data (Dropdown) untuk kolom 'Town' dan 'Property_Residential'. | |
| Memudahkan user agar tidak typo saat input data manual via Excel. | |
| """ | |
| # Buat Pandas Excel writer menggunakan engine XlsxWriter | |
| writer = pd.ExcelWriter(filename, engine='xlsxwriter') | |
| # Buat dataframe dengan header saja | |
| df = pd.DataFrame(columns=['Town', 'Property_Residential', 'List Year', 'Assessed Value']) | |
| df.to_excel(writer, sheet_name='Sheet1', index=False) | |
| # Ambil objek workbook dan worksheet dari xlsxwriter | |
| workbook = writer.book | |
| worksheet = writer.sheets['Sheet1'] | |
| # --- KONFIGURASI DROPDOWN --- | |
| # List validasi Excel tidak boleh lebih dari 255 karakter jika diketik langsung. | |
| # Karena daftar kota sangat panjang, kita simpan di sheet tersembunyi ('Lists') | |
| # lalu kita referensikan range-nya. | |
| # Tambahkan sheet tersembunyi untuk referensi list | |
| worksheet_lists = workbook.add_worksheet('Lists') | |
| worksheet_lists.hide() | |
| # Tulis Daftar Kota (Towns) ke sheet tersembunyi | |
| worksheet_lists.write(0, 0, "Towns") | |
| for i, town in enumerate(sorted(TRAIN_TOWNS)): | |
| worksheet_lists.write(i + 1, 0, town) | |
| # Tulis Daftar Tipe Properti ke sheet tersembunyi | |
| worksheet_lists.write(0, 1, "Property Types") | |
| for i, prop in enumerate(sorted(TRAIN_PROP_TYPES)): | |
| worksheet_lists.write(i + 1, 1, prop) | |
| # Hitung range cell (Excel formula style: Sheet!A1:A100) | |
| town_len = len(TRAIN_TOWNS) | |
| prop_len = len(TRAIN_PROP_TYPES) | |
| town_range = f'=Lists!$A$2:$A${town_len+1}' | |
| prop_range = f'=Lists!$B$2:$B${prop_len+1}' | |
| # Terapkan Validasi Data ke Kolom 'Town' (Kolom A -> baris 2 s/d 1000) | |
| worksheet.data_validation('A2:A1000', { | |
| 'validate': 'list', | |
| 'source': town_range, | |
| 'input_title': 'Select Town', | |
| 'input_message': 'Please select a town from the list.', | |
| 'error_title': 'Invalid Town', | |
| 'error_message': 'Please select a valid town from the dropdown list.' | |
| }) | |
| # Terapkan Validasi Data ke Kolom 'Property_Residential' (Kolom B) | |
| worksheet.data_validation('B2:B1000', { | |
| 'validate': 'list', | |
| 'source': prop_range, | |
| 'input_title': 'Select Property Type', | |
| 'input_message': 'Please select a property type from the list.' | |
| }) | |
| # Validasi Tahun (Kolom C): Integer antara 2000 - 2030 | |
| worksheet.data_validation('C2:C1000', { | |
| 'validate': 'integer', | |
| 'criteria': 'between', | |
| 'minimum': 2000, | |
| 'maximum': 2030, | |
| 'input_title': 'Enter Year', | |
| 'input_message': 'Enter a year between 2000 and 2030.' | |
| }) | |
| # Validasi Assessed Value (Kolom D): Desimal > 0 | |
| worksheet.data_validation('D2:D1000', { | |
| 'validate': 'decimal', | |
| 'criteria': '>', | |
| 'value': 0, | |
| 'input_title': 'Assessed Value', | |
| 'input_message': 'Enter a value greater than 0.' | |
| }) | |
| # Lebarkan kolom agar header terbaca jelas | |
| worksheet.set_column('A:A', 20) | |
| worksheet.set_column('B:B', 20) | |
| worksheet.set_column('C:C', 10) | |
| worksheet.set_column('D:D', 15) | |
| writer.close() | |
| return filename | |