Setting data format for XLSX download

Either I don't know how to do this, or I'm describing an enhancement request.

I can export data from the following table view to a CSV file:

DESCRIBE `PhotosToReshoot`;

GraveID	int(11)	NO		0		
PhotoID	bigint(20)	YES		NULL		
PhotoPageURL	varchar(3000)	YES		NULL		
Location	varchar(50)	YES		NULL		
LastName	varchar(50)	NO				
FirstName	varchar(50)	NO				
DOB	varchar(10)	YES		NULL		
DOD	varchar(10)	YES		NULL		
DOI	varchar(10)	YES		NULL		
LocSort	varchar(35)	YES		NULL

Here is a sample of what the CSV looks like, with a semicolon separator.

Location;"Last Name";"First Name";"Date of Birth";"Date of Death";"Date of Interment"
BOND-2,1,42;Walton;"Mary Ann (Scanlon)";1874-01-14;1930-10-22;1930-10-28
BOND-5,1,52;Pinch;"Gerald G. (Vet, US Navy)";1934-12-25;2005-08-23;2005-08-26
BOND-5,1,52A;Pinch;"Shirley A. (Brumfield)";1936-12-29;2014-06-01;2014-06-04
BOND-7,1,13;Brown;"John H. (Vet, Army Air Force, WWII)";1921-09-13;1975-04-15;1975-04-17
BOND-9,1,5;Dittmer;Christopher;1844-01-22;1930-02-26;1930-03-01
BOND-10,1,9;Evick;"Chester M.";0000-00-00;0000-00-00;0000-00-00
BOND-12,1,14;Whetstone;Charles;1852-01-17;1905-03-03;1905-03-05
BOND-12,1,15;Whetstone;"Magdalena Madeline (Forler)";1855-10-29;1910-11-08;1910-11-12
CITY-4,4,5A;Walker;"Karl Otto";1873-11-28;1874-03-05;1874-03-07
CITY-5,1,3;Tillotson;"Carolyn P.";1870-00-00;1909-11-17;1909-11-19
CITY-5,1,4;Tillotson;"Carrie H. (Bowen)";1840-02-01;1916-05-13;1916-05-15

When I try to do the same thing as an XLSX export, the Date-looking fields are ugly.  What I would prefer is that they are of format type Text instead of General or Date.  For now, I've told my users to use the Excel import wizard to set any date fields to text.

What I would like to see in the XLSX download is the ability to set the columns to a data format.

I don't know if this can be done with CF7 or CF8.

did you manage to solve this ?

No, I'm just living with the semicolon-separated CSV file. I've put this issue on the back burner for now.

I found a way to use the PhpSpreadsheet library to generate an excel file instead of a csv file.  It has a load of functionality around the formats and you'll be able to format the date column if you have a look at the many examples.

Install the library with composer.  You will need shell access to your hosting to do this.

composer require phpoffice/phpspreadsheet

Once installed, you can add some PHP code in your form:

require_once JPATH_ROOT.'/../vendor/autoload.php';
chdir(JPATH_ROOT.'/media/excel');

$fname = "output.xlsx";

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$data = [
    ['Name', 'Age', 'Country'],
    ['John', 25, 'USA'],
    ['Jane', 28, 'UK'],
];

$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();

foreach ($data as $rowNum => $rowData) {
    $worksheet->fromArray($rowData, null, 'A' . ($rowNum + 1));
}

$writer = new Xlsx($spreadsheet);

$writer->save($fname);
print("<P>\n<a href=\"/media/excel/{$fname}\">Download file</a>");

Note that the ../ in the require_once is needed to take you one level above the Joomla Root folder to get to where the libraries are installed by Composer.

This will generate the excel file in the /media/excel directory (which you will need to create in your hosting file manager, or just use /media as it will already exist) with the filename defined in $fname and give you a download link on the page.