File "bulk_upload_template.php"
Full path: /home2/santhos2/public_html/bharath-coe.firstmatrix.in.net/api/bulk_upload_template.php
File size: 9.42 KiB (9651 bytes)
MIME-type: text/x-php
Charset: utf-8
<?php
require_once __DIR__ . '/../config/config.php';
require_once __DIR__ . '/../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Font;
// Create spreadsheet for template
$spreadsheet = new Spreadsheet();
$spreadsheet->getProperties()
->setCreator('Bulk Upload System')
->setTitle('Sample Project Bulk Upload Template')
->setSubject('Bulk Upload Template')
->setDescription('Template for bulk uploading sample project data');
// Create the main template sheet
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Sample Projects Template');
// Define columns and their properties
$columns = [
'A' => ['name' => 'project_title', 'header' => 'Project Title', 'required' => true, 'sample' => 'Machine Learning Research'],
'B' => ['name' => 'student_name', 'header' => 'Student Name', 'required' => true, 'sample' => 'John Doe'],
'C' => ['name' => 'email', 'header' => 'Email', 'required' => true, 'sample' => 'john.doe@example.com'],
'D' => ['name' => 'address', 'header' => 'Address', 'required' => true, 'sample' => '123 Main Street, Chennai'],
'E' => ['name' => 'phone', 'header' => 'Phone', 'required' => false, 'sample' => '9876543210'],
'F' => ['name' => 'department', 'header' => 'Department', 'required' => false, 'sample' => 'Computer Science'],
'G' => ['name' => 'year', 'header' => 'Year', 'required' => false, 'sample' => '2024'],
'H' => ['name' => 'project_description', 'header' => 'Project Description', 'required' => false, 'sample' => 'Research on AI algorithms and machine learning techniques'],
'I' => ['name' => 'status', 'header' => 'Status', 'required' => false, 'sample' => 'active']
];
// Set headers with styling
$row = 1;
foreach ($columns as $col => $column) {
$sheet->setCellValue($col . $row, $column['header']);
// Style headers
$headerStyle = [
'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '4CAF50']],
'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => '000000']]],
'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER]
];
$sheet->getStyle($col . $row)->applyFromArray($headerStyle);
}
// Add required field indicators and sample data
$row = 2;
foreach ($columns as $col => $column) {
$sheet->setCellValue($col . $row, $column['sample']);
// Style cells based on required status
if ($column['required']) {
$cellStyle = [
'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'FFE6E6']], // Light red background
'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => '000000']]],
'font' => ['color' => ['rgb' => 'DC3545']] // Red text
];
} else {
$cellStyle = [
'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'E8F5E8']], // Light green background
'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => '000000']]],
'font' => ['color' => ['rgb' => '28A745']] // Green text
];
}
$sheet->getStyle($col . $row)->applyFromArray($cellStyle);
}
// Add more sample rows
$additionalSamples = [
['Web Development Project', 'Jane Smith', 'jane.smith@example.com', '456 Oak Avenue, Chennai', '9876543211', 'Information Technology', '2024', 'Full-stack web application development', 'active'],
['Data Analytics Study', 'Bob Johnson', 'bob.johnson@example.com', '789 Pine Road, Chennai', '9876543212', 'Computer Science', '2023', 'Big data analysis and visualization project', 'pending'],
['Mobile App Development', 'Alice Brown', 'alice.brown@example.com', '321 Elm Street, Chennai', '9876543213', 'Information Technology', '2024', 'Cross-platform mobile application', 'active'],
['IoT Smart Home', 'Charlie Wilson', 'charlie.wilson@example.com', '654 Maple Drive, Chennai', '9876543214', 'Electronics', '2023', 'Smart home automation system', 'inactive'],
['AI Chatbot', 'Diana Prince', 'diana.prince@example.com', '987 Cedar Lane, Chennai', '9876543215', 'Computer Science', '2024', 'Conversational AI chatbot development', 'active']
];
$row = 3;
foreach ($additionalSamples as $sample) {
$colIndex = 0;
foreach ($columns as $col => $column) {
$value = $sample[$colIndex] ?? '';
$sheet->setCellValue($col . $row, $value);
// Apply same styling as row 2
if ($column['required']) {
$cellStyle = [
'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'FFE6E6']],
'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => '000000']]]
];
} else {
$cellStyle = [
'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'E8F5E8']],
'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => '000000']]]
];
}
$sheet->getStyle($col . $row)->applyFromArray($cellStyle);
$colIndex++;
}
$row++;
}
// Add instructions sheet
$instructionsSheet = $spreadsheet->createSheet();
$instructionsSheet->setTitle('Instructions');
// Instructions content
$instructions = [
['BULK UPLOAD INSTRUCTIONS'],
[''],
['📋 FIELD DESCRIPTIONS:'],
[''],
['Project Title:', 'The title/name of the project (Required)'],
['Student Name:', 'Full name of the student (Required)'],
['Email:', 'Student email address (Required, must be unique)'],
['Address:', 'Complete address of the student (Required)'],
['Phone:', 'Contact phone number (Optional)'],
['Department:', 'Academic department (Optional)'],
['Year:', 'Academic year (Optional)'],
['Project Description:', 'Detailed description of the project (Optional)'],
['Status:', 'Project status: active, inactive, or pending (Optional, defaults to pending)'],
[''],
['🎨 FORMATTING GUIDE:'],
[''],
['🔴 Red Background:', 'Required fields - must be filled'],
['🟢 Green Background:', 'Optional fields - can be left empty'],
[''],
['⚠️ IMPORTANT NOTES:'],
[''],
['1. Do not modify the header row (first row)'],
['2. Fill data starting from row 2 onwards'],
['3. Email addresses must be unique across all records'],
['4. Leave optional fields empty if not applicable'],
['5. Status values: active, inactive, or pending only'],
['6. Maximum file size: 10MB'],
['7. Supported formats: .xlsx and .xls'],
[''],
['📊 VALIDATION RULES:'],
[''],
['• Email format must be valid (user@domain.com)'],
['• Phone numbers should contain only digits'],
['• Project titles cannot be empty'],
['• Student names cannot be empty'],
['• Addresses cannot be empty'],
['• Status must be one of: active, inactive, pending'],
[''],
['🔄 UPLOAD PROCESS:'],
[''],
['1. Fill in your data in this template'],
['2. Save the file'],
['3. Go to Bulk Upload page'],
['4. Select and upload your file'],
['5. Review the results and error reports'],
['6. Correct any errors and re-upload if needed']
];
$row = 1;
foreach ($instructions as $instruction) {
if (is_array($instruction)) {
$instructionsSheet->setCellValue('A' . $row, $instruction[0]);
if (isset($instruction[1])) {
$instructionsSheet->setCellValue('B' . $row, $instruction[1]);
}
} else {
$instructionsSheet->setCellValue('A' . $row, $instruction);
}
$row++;
}
// Style instructions sheet
$instructionsSheet->getStyle('A1')->applyFromArray([
'font' => ['bold' => true, 'size' => 16, 'color' => ['rgb' => 'FFFFFF']],
'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '4CAF50']],
'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER]
]);
$instructionsSheet->mergeCells('A1:B1');
// Style section headers
$sectionHeaders = [3, 11, 19, 27, 35]; // Row numbers with section headers
foreach ($sectionHeaders as $headerRow) {
$instructionsSheet->getStyle('A' . $headerRow)->applyFromArray([
'font' => ['bold' => true, 'color' => ['rgb' => '1976D2']],
'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'F3E5F5']]
]);
}
// Auto-size columns in instructions sheet
$instructionsSheet->getColumnDimension('A')->setWidth(30);
$instructionsSheet->getColumnDimension('B')->setWidth(50);
// Auto-size columns in template sheet
foreach (range('A', 'I') as $columnID) {
$sheet->getColumnDimension($columnID)->setAutoSize(true);
}
// Set active sheet back to template
$spreadsheet->setActiveSheetIndex(0);
// Generate filename
$filename = 'sample_project_bulk_upload_template.xlsx';
// Save to output
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
exit;
?>