I'm working on the project that uses a lot of mysql data importing into excel files. So far I've been writing queries in my controller and then simply exporting the result data as an excel file. But now I have to make a filter that can customize the exported data. For example, on export button click, I'll display a multi select form containing all available columns that normally would be exported to excel, but with the help of that select form, only the column names selected by the user would be exported as an excel file instead of the entire query. So for instance, If I normally would have columns : name, age, gender - If the user only selects name, only name would be exported. This is my code right now:
public function exportExcelAction()
{
ini_set('memory_limit', '-1');
ini_set('max_execution_time', 1800);
ini_set('max_input_time', 1800);
$this->view->disable();
$spreadsheet = new Spreadsheet();
$Excel_writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$sheetName = 'reportHistory';
$spreadsheet->setActiveSheetIndex(0);
$activeSheet = $spreadsheet->getActiveSheet()->setTitle($sheetName);
$activeSheet->setCellValue('A1', 'name');
$activeSheet->setCellValue('B1', 'age');
$activeSheet->setCellValue('C1', 'gender');
$activeSheet->getStyle('A1:C1')->getFont()->setBold(true);
$builder = $this->modelsManager->createBuilder()
->columns([
'name' => 'a.name',
'age' => 'a.age',
'gender => 'a.gender'
])
->addFrom(Admins::class, 'a');
$result = $builder->getQuery()->execute();
if (!empty($result)) {
$i = 2;
foreach ($result as $value) {
$activeSheet->setCellValue('A' . $i, $value->name);
$activeSheet->setCellValue('B' . $i, $value->age);
$activeSheet->setCellValue('C' . $i, $value->gender);
$i++;
}
}
foreach (range('A', 'C') as $columnID) {
$activeSheet->getColumnDimension($columnID)
->setAutoSize(true);
}
$filename = 'reportHistory_' . date('Y-m-d_H-i-s') . '.xlsx';
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename=' . $filename);
header('Cache-Control: max-age=0');
$Excel_writer->save('php://output');
}
I was thinking of getting the values from the select form as cell values, like A or B, so If the user selects only 'name', i'd give it a value of A and based on that somehow filter the columns displayed but I'm having a hard time coming up with ideas. I'd appreciate any tips on how to solve this problem, thank you !!
This code dynamically builds the Excel header and data based on the selected columns. Adjust the way you retrieve the selected columns based on your form implementation. The $columnIndex is used to calculate the corresponding column letter (A, B, C, ...). The resulting Excel file will only include the columns selected by the user.
public function exportExcelAction()
{
ini_set('memory_limit', '-1');
ini_set('max_execution_time', 1800);
ini_set('max_input_time', 1800);
$this->view->disable();
$selectedColumns = ['name', 'age', 'gender']; // Default columns
// For example, if you have a multi-select form element named 'selectedColumns', you can get it like this:
// $selectedColumns = $this->request->getPost('selectedColumns');
$spreadsheet = new Spreadsheet();
$Excel_writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$sheetName = 'reportHistory';
$spreadsheet->setActiveSheetIndex(0);
$activeSheet = $spreadsheet->getActiveSheet()->setTitle($sheetName);
$columnIndex = 0;
foreach ($selectedColumns as $columnName) {
$columnLetter = chr(65 + $columnIndex); // A, B, C, ...
$activeSheet->setCellValue($columnLetter . '1', $columnName);
$columnIndex++;
}
$builder = $this->modelsManager->createBuilder()
->columns($selectedColumns)
->addFrom(Admins::class, 'a');
$result = $builder->getQuery()->execute();
if (!empty($result)) {
$rowIndex = 2;
foreach ($result as $value) {
$columnIndex = 0;
foreach ($selectedColumns as $columnName) {
$columnLetter = chr(65 + $columnIndex); // A, B, C, ...
$activeSheet->setCellValue($columnLetter . $rowIndex, $value->{$columnName});
$columnIndex++;
}
$rowIndex++;
}
}
foreach (range('A', chr(65 + $columnIndex - 1)) as $columnID) {
$activeSheet->getColumnDimension($columnID)->setAutoSize(true);
}
$filename = 'reportHistory_' . date('Y-m-d_H-i-s') . '.xlsx';
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename=' . $filename);
header('Cache-Control: max-age=0');
$Excel_writer->save('php://output');
}
Update
Since you've requested to explain how to change your code If you have multiple table joins, I prepared a sample solution for you.
If you have multiple table joins and want to select columns from those joined tables, you need to adjust the column names in the selectedColumns array and also modify the query accordingly.
public function exportExcelAction()
{
ini_set('memory_limit', '-1');
ini_set('max_execution_time', 1800);
ini_set('max_input_time', 1800);
$this->view->disable();
// Get selected columns from the form (you need to adapt this part based on your form implementation)
$selectedColumns = ['a.name', 'a.age', 'a.gender', 's.somethingColumn']; // Adjust column names based on your tables
// For example, if you have a multi-select form element named 'selectedColumns', you can get it like this:
// $selectedColumns = $this->request->getPost('selectedColumns');
$spreadsheet = new Spreadsheet();
$Excel_writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$sheetName = 'reportHistory';
$spreadsheet->setActiveSheetIndex(0);
$activeSheet = $spreadsheet->getActiveSheet()->setTitle($sheetName);
// Set header based on selected columns
$columnIndex = 0;
foreach ($selectedColumns as $columnName) {
$columnLetter = chr(65 + $columnIndex); // A, B, C, ...
// Extract column alias or name from the selected column (e.g., 'a.name' or 'name')
$columnNameParts = explode('.', $columnName);
$activeSheet->setCellValue($columnLetter . '1', end($columnNameParts));
$columnIndex++;
}
// Fetch data based on selected columns
$builder = $this->modelsManager->createBuilder()
->columns($selectedColumns)
->addFrom(Admins::class, 'a')
->innerJoin(Something::class, 's.id = a.somethingID', 's');
$result = $builder->getQuery()->execute();
if (!empty($result)) {
$rowIndex = 2;
foreach ($result as $value) {
$columnIndex = 0;
foreach ($selectedColumns as $columnName) {
$columnLetter = chr(65 + $columnIndex); // A, B, C, ...
// Extract column alias or name from the selected column (e.g., 'a.name' or 'name')
$columnNameParts = explode('.', $columnName);
$activeSheet->setCellValue($columnLetter . $rowIndex, $value->{$columnNameParts[1]});
$columnIndex++;
}
$rowIndex++;
}
}
// Set column width
foreach (range('A', chr(65 + $columnIndex - 1)) as $columnID) {
$activeSheet->getColumnDimension($columnID)->setAutoSize(true);
}
$filename = 'reportHistory_' . date('Y-m-d_H-i-s') . '.xlsx';
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename=' . $filename);
header('Cache-Control: max-age=0');
$Excel_writer->save('php://output');
}
Make sure to adjust the column names in the selectedColumns array to match the actual column names from your tables. The explode('.', $columnName) is used to extract the column alias or name from the selected column, considering it might be in the format 'table.column'.