If your problem is in getting empty columns that go after real data, and you would like to avoid these, you could do something like this:
$maxCell = $sheet->getHighestRowAndColumn();
$data = $sheet->rangeToArray('A1:' . $maxCell['column'] . $maxCell['row']);
This will return array representing only the area containing real data.
Is your problem in getting the date from MS SQL as a date/time, or setting the Excel date?
There is a whole section of the PHPExcel documentation that explains the use of the PHPExcel_Shared_Date::PHPToExcel($PHPDate)
and PHPExcel_Shared_Date::FormattedPHPToExcel($year, $month, $day, $hours=0, $minutes=0, $seconds=0)
helper methods for converting PHP dates to an Excel datetime stamp value that you set as the cell value, and then you apply a number format mask of one of the date masks such as PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2
to that cell
Instead of
$t_year = substr($xls_column_datas["colname"],0,4);
$t_month = substr($xls_column_datas["colname"],5,2);
$t_day = substr($xls_column_datas["colname"],8,2);
$t_format = '=date('.$t_format.')';
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($data_column_num, $data_row_num, $t_format );
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($data_column_num, $data_row_num)->getNumberFormat()->setFormatCode('[$-C09]d mmm yyyy;@');
try setting
$t_year = substr($xls_column_datas["colname"],0,4);
$t_month = substr($xls_column_datas["colname"],4,2); // Fixed problems with offsets
$t_day = substr($xls_column_datas["colname"],6,2);
$t_date = PHPExcel_Shared_Date::FormattedPHPToExcel($t_year, $t_month, $t_day);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(
$data_column_num, $data_row_num, $t_date
);
$objPHPExcel->getActiveSheet()
->getStyleByColumnAndRow($data_column_num, $data_row_num)
->getNumberFormat()->setFormatCode(
PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX14
);
Best Answer