excel data read and split with php

Question

i am having an excel file which has about hundres emails that are in first column..

its like this,

Ameerali Uncle. <seenameer@gmail.com>
Afkaar (Habeeb). <afkar.mam@gmail.com>
Ameerali Uncle. <seenameer@gmail.com>

i wanted to take break this from <> sign and put it in to a new column and leave the first column with name. so after doing this, it will be like

column 1         column 2
Afkaar (Habeeb)  afkar.mam@gmail.com

is this a possible thing to do with php ? can somebody suggest me a good mechanism to do this.

thanks

EDIT 1

My sample excel file is as below.

enter image description here

read excel content into php, manipulate the string content (regex might be necessary), and then recreate the excel file. — ihsan, Oct 13 '13 at 09:33
check out [PHP-ExcelReader](http://sourceforge.net/projects/phpexcelreader/) — gwillie, Oct 13 '13 at 09:40

score 3 · Accepted Answer · edited May 23 '17 at 10:32

You can use PHPExcel library to read/write from/on excel files. (download and documentation are available at http://phpexcel.codeplex.com/). Then use RegExs to separate strings.

EDIT 1

The code will be something like this :

//  Include PHPExcel_IOFactory
include 'PHPExcel/IOFactory.php';

$inputFileName = './example1.xlsx';
$outputFileName = './output.xlsx';

//  Read your Excel workbook
try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load($inputFileName);

    $outputObj = new PHPExcel();
} catch(Exception $e) {
    die('Error loading file: '. $e->getMessage());
}

//  Get worksheet dimensions
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();

$outputObj->setActiveSheetIndex(0);
$outSheet = $outputObj->getActiveSheet();

//  Loop through each row of the worksheet in turn
for ($row = 2; $row <= $highestRow; $row++){ // As row 1 seems to be header
    //  Read cell A2, A3, etc.
    $line = $sheet->getCell('A' . $row)->getValue();
    preg_match("|([^\.]+)\. <([^>]+)>|", $line, $data);
    // $data[1] will be name & $data[2] will be email
    $outSheet->setCellValue('A' . $row, $data[1]);
    $outSheet->setCellValue('B' . $row, $data[2]);
}

// write new data into a .xlsx file
$objWriter = new PHPExcel_Writer_Excel2007($outputObj);
$objWriter->save($outputFileName);

Sources: Stackoverflow Question, PHPExcel Example

tnx a lot for this, btw is possible to show this example with the usage of PHPExcel library pls? — dev1234, Oct 13 '13 at 09:59
@SignNaL89 pls check the edited question above. i have put a picture. — dev1234, Oct 13 '13 at 10:04

excel data read and split with php

1 Answers1