Hi i am trying to fetch the records of excel sheet and store in my database. I am success fully add the excel first sheet but not able to get second sheet of excel and its records.
i am using this code and get the first sheet `$this -> load -> library('Excel');
$objPHPExcel = PHPExcel_IOFactory::load($load_file);
$cell_collection = $objPHPExcel->getActiveSheet()->getCellCollection();
`
Suppose in image its 3 worksheets in one excel. HOw can i access to second and third worksheet?? Please give me some help. Thanks
Try using PHPExcel on reading your excel file.
function read_excel(){
//Load library plugin
$this->load->library('excel');
/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
//Open excel file
$objPHPExcel = PHPExcel_IOFactory::load(FCPATH."file.xls");
// go through your worksheet if you have multiple worksheet inside the excel file
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
//read each row of the worksheet
foreach ($worksheet->getRowIterator() as $row) {
//get cells
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
foreach ($cellIterator as $cell) {
//get cell values
$cell->getCalculatedValue();
}
}
}
}
tweak this code in your need.
When i am Exploring PHPExcel.php i get the solution that i am going to share with other too.
$target_file = $path . basename($_FILES["files"]["name"]);// storing the excel file to folder
$file_id = basename($_FILES["files"]["name"]);
$FileType = pathinfo($target_file, PATHINFO_EXTENSION);
move_uploaded_file($_FILES["files"]["tmp_name"], $target_file);
$load_file = $target_file;
$update4 = array('file_id' => $file_id,);
$this -> load -> library('Excel');
$objPHPExcel = PHPExcel_IOFactory::load($load_file);
$sheet_count = $objPHPExcel->getSheetCount();// this function give me worksheets count.
for($s=0; $s<$sheet_count; $s++){// using for lop to get specific worksheet data
$cell_collection= $objPHPExcel->getSheet($s)->getCellCollection();
foreach ($cell_collection as $cell) {
$column = $objPHPExcel -> getSheet() -> getCell($cell) -> getColumn();
$row = $objPHPExcel -> getSheet() -> getCell($cell) -> getRow();
$data_value = $objPHPExcel -> getSheet() -> getCell($cell) -> getValue();
if ($row == 1) {// if you have header in your excel or want to store then store in header.
$header[$s][$row][$column] = $data_value;
} else {// i get my meaningful data from here :)
$arr_data[$s][$row][$column] = $data_value;
}
}
}// end of for loop for getting the work sheet