CODEWITHSUNDEEP
phpexcelcodeigniterphpexcel
deemi-D-nadeem
deemi-D-nadeem

Reputation: 2514

Convert excelsheet columns format in php not working

I am working on staff attendance. User can upload an attendance sheet and my system will give the view of attendance excel sheet to user and then he can save it to database. The values from excel sheet saving successfully in database.

The problem is user can add record in columns of excel sheet in any format but when I get in PHP the date format goes wrong.

e.g. when user add attendance date in column, the format of column is date and when he can save in out time it may be number format and when user upload this excel sheet, the date preview gone in unix time stamp.

Here is examples in images

  1. Date in Excel sheet enter image description here

    1. Time in and out in excel sheet enter image description here

    2. When user go to site and upload this excel sheet enter image description here

    3. Then a popup window open enter image description here

    4. Then user browses the excel sheet and then I show the excel sheet view, that viewing wrong enter image description here

You can see in above image that the date is being converted in unix time stamp and time in and out in numeric.

I want this view when a user can upload an excel sheet.

enter image description here

So hope you understand my issue and what I want.

Here is my upload and view excel sheet code:

$excel_file = $_FILES['file']['name'];
$target = SERVERPATH . 'assets/attendance_excel/';
$ext = pathinfo($excel_file, PATHINFO_EXTENSION);

$filename = rand() . date("YmdHis");

move_uploaded_file($_FILES['file']['tmp_name'], $target . $filename . '.' . $ext);

include(EXCEL_CLASS);

$inputFileName = $target . $filename . '.' . $ext;
//            chmod($inputFileName,0777);

try {
    $objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
} catch (Exception $e) {
    die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage());
}

$objWorksheet = $objPHPExcel->getActiveSheet();
//            $objWorksheet->getStyle('A1:E433')->getNumberFormat()->setFormatCode('Text');

$highestRow = $objWorksheet->getHighestRow(); // e.g. 10
$highestColumn = $objWorksheet->getHighestDataRow();

$highestColumn = $objWorksheet->getHighestColumn(); // e.g 'F'

$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); // e.g. 5


$td_data = '';
$row_no = 0;

for ($row = 1; $row <= $highestRow; ++$row) {
    $check_empty_column = 0;

    for ($col = 0; $col <= $highestColumnIndex; ++$col) {
        if ($objWorksheet->getCellByColumnAndRow($col, $row)->getValue() == null) {
           $check_empty_column++;
      }
    }

    if ($check_empty_column < $highestColumnIndex) {
       for ($col = 0; $col < $highestColumnIndex; ++$col) {

           $td_data .= '<td>' . $objWorksheet->getCellByColumnAndRow($col, $row)->getValue() . '</td>';

       }
    }

    $tr_data[$row_no] = $td_data;
    $td_data = '';
    $row_no++;
}

$data_all = array(
     'data' => $tr_data,
     't_col' => $highestColumnIndex,
     'name' => $filename . '.' . $ext
);
echo json_encode($data_all);

I also search a lot about this and found something like this setFormatCode, PHPExcel_Style_NumberFormat::FORMAT_GENERAL etc but its not working for me. I try my best.

So please give me solution for this problem.

Note: I can not restrict a user that he convert the columns and than add data.

Hope you understand my question

Upvotes: 1

Views: 454

Answers (1)

Mark Baker
Mark Baker

Reputation: 212412

MS Excel stores dates as a float value, a serialized timestamp, the number of days since 1st January 1900 (or 1st January 1904 if the file is using the Mac Calendar), and it is the number format mask for the cell that displays it as a formatted date.

PHPExcel provides a series of methods for converting between that MS Exce serialized timestamp and unix timestamps or PHP DateTime objects

$unixTimestamp = PHPExcel_Shared_Date::ExcelToPHP($excelTimestampValue);

will convert the MS Excel timestamp to a Unix timestamp

$phpDateTimeObject = PHPExcel_Shared_Date::ExcelToPHPObject($excelTimestampValue);

will convert the MS Excel timestamp to a PHP DateTime object

And you can then use PHP's standard date() function or the DateTime object's format() method to format that value however you like.

$excelTimestamp = PHPExcel_Shared_Date::ExcelToPHPObject($phpDateTimeObject);
$excelTimestamp = PHPExcel_Shared_Date::ExcelToPHPObject($unixTimestamp);

will convert either a unix timestamp or a PHPP DateTime object to an MS Excel serialized timestamp value

If you want to change the way that an MS Excel serialized timestamp is displayed in a spreadsheet, then you need to use an Excel format mask that corresponds too a date/time mask.... PHPExcel_Style_NumberFormat::FORMAT_GENERAL is not a format mask for dates/times

Upvotes: 2

Related Questions