read - cómo usar phpexcel para leer datos e insertarlos en la base de datos?

phpexcel documentation

Question

Tengo una aplicación php en la que deseo leer datos de excel, Insertar en la base de datos y luego generar informes en PDF para usuarios específicos. Busqué mucho en internet, pero no se especifica nada sobre ambas cosas. si alguien pudiera proporcionar un tutorial o algo así, sería una verdadera ayuda.

Answer 1

Aquí hay una respuesta muy reciente a esta pregunta del archivo: 07reader.php

<?php
error_reporting(E_ALL);
ini_set(''display_errors'', TRUE);
ini_set(''display_startup_errors'', TRUE);
define(''EOL'',(PHP_SAPI == ''cli'') ? PHP_EOL : ''<br />'');
date_default_timezone_set(''Europe/London'');
/** Include PHPExcel_IOFactory */
require_once ''../Classes/PHPExcel/IOFactory.php'';
if (!file_exists("05featuredemo.xlsx")) {
exit("Please run 05featuredemo.php first." . EOL);
}
echo date(''H:i:s'') , " Load from Excel2007 file" , EOL;
$callStartTime = microtime(true);
$objPHPExcel = PHPExcel_IOFactory::load("05featuredemo.xlsx");
$callEndTime = microtime(true);
$callTime = $callEndTime - $callStartTime;
echo ''Call time to read Workbook was '' , sprintf(''%.4f'',$callTime) , " seconds" , EOL;
// Echo memory usage
echo date(''H:i:s'') , '' Current memory usage: '' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;
echo date(''H:i:s'') , " Write to Excel2007 format" , EOL;
$callStartTime = microtime(true);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ''Excel2007'');
$objWriter->save(str_replace(''.php'', ''.xlsx'', __FILE__));
$callEndTime = microtime(true);
$callTime = $callEndTime - $callStartTime;
echo date(''H:i:s'') , " File written to " , str_replace(''.php'', ''.xlsx'', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
echo ''Call time to write Workbook was '' , sprintf(''%.4f'',$callTime) , " seconds" , EOL;
// Echo memory usage
echo date(''H:i:s'') , '' Current memory usage: '' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;
// Echo memory peak usage
echo date(''H:i:s'') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL;
// Echo done
echo date(''H:i:s'') , " Done writing file" , EOL;
echo ''File has been created in '' , getcwd() , EOL;

Answer 2

Para leer los datos de microsoft excel 2007 por codeigniter simplemente cree una función auxiliar excel_helper.php y agregue lo siguiente en:

 require_once APPPATH.''libraries/phpexcel/PHPExcel.php'';
require_once APPPATH.''libraries/phpexcel/PHPExcel/IOFactory.php'';
in controller add the following code to read spread sheet by active sheet
//initialize php excel first
ob_end_clean();
//define cachemethod
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array(''memoryCacheSize'' => ''20MB'');
//set php excel settings
PHPExcel_Settings::setCacheStorageMethod(
$cacheMethod,$cacheSettings
);
$arrayLabel = array("A","B","C","D","E");
//=== set object reader
$objectReader = PHPExcel_IOFactory::createReader(''Excel2007'');
$objectReader->setReadDataOnly(true);
$objPHPExcel = $objectReader->load("./forms/test.xlsx");
$objWorksheet = $objPHPExcel->setActiveSheetIndexbyName(''Sheet1'');
$starting = 1;
$end = 3;
for($i = $starting;$i<=$end; $i++)
{
for($j=0;$j<count($arrayLabel);$j++)
{
//== display each cell value
echo $objWorksheet->getCell($arrayLabel[$j].$i)->getValue();
}
}
//or dump data
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
var_dump($sheetData);
//see also the following link
http://blog.mayflower.de/561-Import-and-export-data-using-PHPExcel.html
----------- import in another style around 5000 records ------
$this->benchmark->mark(''code_start'');
//=== change php ini limits. =====
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
$cacheSettings = array( '' memoryCacheSize '' => ''50MB'');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
//==== create excel object of reader
$objReader = PHPExcel_IOFactory::createReader(''Excel2007'');
//$objReader->setReadDataOnly(true);
//==== load forms tashkil where the file exists
$objPHPExcel = $objReader->load("./forms/5000records.xlsx");
//==== set active sheet to read data
$worksheet = $objPHPExcel->setActiveSheetIndexbyName(''Sheet1'');
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$highestColumn = $worksheet->getHighestColumn(); // e.g ''F''
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$nrColumns = ord($highestColumn) - 64;
$worksheetTitle = $worksheet->getTitle();
echo "<br>The worksheet ".$worksheetTitle." has ";
echo $nrColumns . '' columns (A-'' . $highestColumn . '') '';
echo '' and '' . $highestRow . '' row.'';
echo ''<br>Data: <table border="1"><tr>'';
//----- loop from all rows -----
for ($row = 1; $row <= $highestRow; ++ $row)
{
echo ''<tr>'';
echo "<td>".$row."</td>";
//--- read each excel column for each row ----
for ($col = 0; $col < $highestColumnIndex; ++ $col)
{
if($row == 1)
{
// show column name with the title
//----- get value ----
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$val = $cell->getValue();
//$dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
echo ''<td>'' . $val ."(".$row." X ".$col.")".''</td>'';
}
else
{
if($col == 9)
{
//----- get value ----
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$val = $cell->getValue();
//$dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
echo ''<td>zone '' . $val .''</td>'';
}
else if($col == 13)
{
$date = PHPExcel_Shared_Date::ExcelToPHPObject($worksheet->getCellByColumnAndRow($col, $row)->getValue())->format(''Y-m-d'');
echo ''<td>'' .dateprovider($date,''dr'') .''</td>'';
}
else
{
//----- get value ----
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$val = $cell->getValue();
//$dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
echo ''<td>'' . $val .''</td>'';
}
}
}
echo ''</tr>'';
}
echo ''</table>'';
$this->benchmark->mark(''code_end'');
echo "Total time:".$this->benchmark->elapsed_time(''code_start'', ''code_end'');
$this->load->view("error");

Answer 3

Usando la biblioteca PHPExcel , el siguiente código funcionará.

require_once dirname(__FILE__) . ''/../Classes/PHPExcel/IOFactory.php'';
$objReader = PHPExcel_IOFactory::createReader(''Excel2007'');
$objReader->setReadDataOnly(true); //optional
$objPHPExcel = $objReader->load(__DIR__.''/YourExcelFile.xlsx'');
$objWorksheet = $objPHPExcel->getActiveSheet();
$i=1;
foreach ($objWorksheet->getRowIterator() as $row) {
$column_A_Value = $objPHPExcel->getActiveSheet()->getCell("A$i")->getValue();//column A
//you can add your own columns B, C, D etc.
//inset $column_A_Value value in DB query here
$i++;
}

Answer 4

Usar la biblioteca de PHPExcel para leer un archivo de Excel y transferir los datos a una base de datos

// Include PHPExcel_IOFactory
include ''PHPExcel/IOFactory.php'';
$inputFileName = ''./sampleData/example1.xls'';
// Read your Excel workbook
try {
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
} catch(Exception $e) {
die(''Error loading file "''.pathinfo($inputFileName,PATHINFO_BASENAME).''": ''.$e->getMessage());
}
// Get worksheet dimensions
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
// Loop through each row of the worksheet in turn
for ($row = 1; $row <= $highestRow; $row++){
// Read a row of data into an array
$rowData = $sheet->rangeToArray(''A'' . $row . '':'' . $highestColumn . $row,
NULL,
TRUE,
FALSE);
// Insert row data array into your database of choice here
}

Cualquier cosa más se vuelve muy dependiente de su base de datos, y cómo quiere que los datos estén estructurados en ella

Answer 5

 if($query)
{
// try to export to excel the whole data ---
//initialize php excel first
ob_end_clean();
//--- create php excel object ---
$objPHPExcel = new PHPExcel();
//define cachemethod
ini_set(''memory_limit'', ''3500M'');
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array(''memoryCacheSize'' => ''800MB'');
//set php excel settings
PHPExcel_Settings::setCacheStorageMethod(
$cacheMethod,$cacheSettings
);
$objPHPExcel->getProperties()->setTitle("export")->setDescription("none");
$objPHPExcel->setActiveSheetIndex(0);
// Field names in the first row
$fields = $query->list_fields();
$col = 0;
foreach ($fields as $field)
{
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $field);
$col++;
}
// Fetching the table data
$row = 2;
foreach($query->result() as $data)
{
$col = 0;
foreach ($fields as $field)
{
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $data->$field);
$col++;
}
$row++;
}
$objPHPExcel->setActiveSheetIndex(0);
//redirect to cleint browser
header(''Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'');
header(''Content-Disposition: attachment;filename=Provinces.xlsx'');
header(''Cache-Control: max-age=0'');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ''Excel2007'');
$objWriter->save(''php://output'');
}

Answer 6

 if($this->mng_auth->get_language()==''en'')
{
$excel->getActiveSheet()->setRightToLeft(false);
}
else
{
$excel->getActiveSheet()->setRightToLeft(true);
}
$styleArray = array(
''borders'' => array(
''allborders'' => array(
''style'' => PHPExcel_Style_Border::BORDER_THIN,
''color'' => array(''argb'' => ''00000000''),
),
),
);
//SET property
$objPHPExcel->getActiveSheet()->getStyle(''A1:M10001'')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle(''A1:M10001'')->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle(''A1:''.chr(65+count($fields)-1).$query->num_rows())->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle(''A1:''.chr(65+count($fields)-1).$query->num_rows())->getAlignment()->setWrapText(true);