Importing Spreadsheets with PHPExcel

PHPExcel is an awesome class for working with spreadsheets, you can download it here: https://phpexcel.codeplex.com/

It makes for very easy importing regardless of the format’s that are uploaded from a user. Below you can see a simple class with a public function for importing a spreadsheet; you will likely need to update some translation logic based on the spreadsheet you are working with.

<?php
require 'PHPExcel/IOFactory.php';
class Spreadsheet {
    public function import($file_name) {
        try {
            $file_type   = PHPExcel_IOFactory::identify($file_name);
            $objReader   = PHPExcel_IOFactory::createReader($file_type);
            $objPHPExcel = $objReader->load($file_name);
        } catch(Exception $e) {
            die('Error loading file "'.pathinfo($file_name,PATHINFO_BASENAME).'": '.$e->getMessage());
        }
        /*
            Get worksheet dimensions
        */
        $sheet         = $objPHPExcel->getSheet(0);
        $highestRow    = $sheet->getHighestRow();
        $highestColumn = $sheet->getHighestColumn();
        for ($row = 1; $row <= $highestRow; $row++) {
            //  Read a row of data into an array
            $data = $sheet->rangeToArray(
                'A' . $row . ':' . $highestColumn . $row,
                NULL,
                TRUE,
                FALSE
            );
            /*
                Convert date to something human readable
                If you have dates in your spreadsheet it converts
                it to a floating number
            */
            $date     = date('Y-m-d',PHPExcel_Shared_Date::ExcelToPHP($sheet->getCellByColumnAndRow(4, $row)->getValue()));
            $fullname = $data[0][0];
            $email    = $data[0][2];
            $dept     = $data[0][3];
            $supe     = $data[0][5];
            $user     = explode(' ', $fullname);
            $user     = strtolower($user[0][0] . end($user));
            $this->add_new($fullname, $user, $email, $date, $dept, $supe);
        }
    }
}
?>

 

Write a Comment

Your email address will not be published. Required fields are marked *