Spaces:
Sleeping
Sleeping
File size: 3,363 Bytes
415ff3b 656590a 415ff3b 656590a 415ff3b 656590a 415ff3b 656590a 415ff3b 656590a 415ff3b 656590a 415ff3b 656590a 415ff3b 656590a 415ff3b 656590a 415ff3b 656590a 415ff3b 656590a 415ff3b 656590a 415ff3b 656590a 415ff3b 656590a 415ff3b | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 | 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
|