Education center

In the previous tutorial, i have been posted about how to export data from mysql database to excel file using phpExcel. Now i want to share how to import data from excel to mysql database using php (PhpExcel Library). This code very useful if we have more data in excel file, because we have save more time to input it to database.



Here step by step how to import data from excel to database:

Example we have data in excel like this



and we have table in mysql database like this


CREATE TABLE IF NOT EXISTS `data` (
  `iddata` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `address` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`iddata`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Here i use codeigniter, this is a function in controller file

public function index()
{
//load library phpExcel
$this->load->library("PHPExcel");
//here i used microsoft excel 2007
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
//set to read only
$objReader->setReadDataOnly(true);
//load excel file
$objPHPExcel = $objReader->load("data.xlsx");
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
//load model
$this->load->model("User_model");
//loop from first data until last data
for($i=2; $i<=77; $i++){
$name = $objWorksheet->getCellByColumnAndRow(0,$i)->getValue();
$address = $objWorksheet->getCellByColumnAndRow(1,$i)->getValue();
$data_user = array(
"name" => $name,
"username" => $address );
$this->User_model->add_data($data_user);
}
}

Here a function in model file

function add_data($datauser)
{
$this->db->insert('data',$datauser);
return $this->db->insert_id();
} 

If you have questions, let us discussion.