leer - php excel example

Cómo exportar datos a un archivo excel usando PHPExcel

Question

He tomado el código fuente de limesurvey y he agregado la biblioteca PHPExcel a mi código de limesurvey para exportar datos a un archivo de Excel después de hacer clic en un enlace. Actualmente, el archivo de Excel se abre con algunos datos ficticios sin problemas. Necesito poder agregar datos dinámicamente desde el servidor web después de que un usuario ingrese la información de la encuesta. He buscado en algunos sitios que he encontrado, pero no he tenido mucha suerte. ¿Puede alguien ayudarme?

EDITAR

<?php 
$dbhost= "mysql"; //your MySQL Server 
$dbuser = "survey"; //your MySQL User Name 
$dbpass = "password"; //your MySQL Password 
$dbname = "database"; 
//your MySQL Database Name of which database to use this 
$tablename = "questions"; //your MySQL Table Name which one you have to create excel file 
// your mysql query here , we can edit this for your requirement 
$sql = "Select * from $table "; 
//create  code for connecting to mysql 
$Connect = @mysql_connect($dbhost, $dbuser, $dbpass) 
or die("Couldn''t connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno()); 
//select database 
$Db = @mysql_select_db($dbname, $Connect) 
or die("Couldn''t select database:<br>" . mysql_error(). "<br>" . mysql_errno()); 
//execute query 
$result = @mysql_query($sql,$Connect) 
or die("Couldn''t execute query:<br>" . mysql_error(). "<br>" . mysql_errno()); 

error_reporting(E_ALL);

 require_once ''../Classes/PHPExcel.php'';
 $objPHPExcel = new PHPExcel();

 // Set the active Excel worksheet to sheet 0 

$objPHPExcel->setActiveSheetIndex(0);  

// Initialise the Excel row number 

$rowCount = 1;  


//start of printing column names as names of MySQL fields  

 $column = ''A'';

for ($i = 1; $i < mysql_num_fields($result); $i++)  

{
    $objPHPExcel->getActiveSheet()->setCellValue($column.$rowCount, mysql_field_name($result,$i));
    $column++;
}

//end of adding column names  
//start while loop to get data  

$rowCount = 2;  

while($row = mysql_fetch_row($result))  

{  
    $column = ''A'';

   for($j=1; $j<mysql_num_fields($result);$j++)  
    {  
        if(!isset($row[$j]))  

            $value = NULL;  

        elseif ($row[$j] != "")  

            $value = strip_tags($row[$j]);  

        else  

            $value = "";  


        $objPHPExcel->getActiveSheet()->setCellValue($column.$rowCount, $value);
        $column++;
    }  

    $rowCount++;
} 

// Redirect output to a client’s web browser (Excel5) 
header(''Content-Type: application/vnd.ms-excel''); 
header(''Content-Disposition: attachment;filename="results.xls"''); 
header(''Cache-Control: max-age=0''); 
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ''Excel5''); 
$objWriter->save(''php://output'');

Answer 1

Actualmente utilizo esta función en mi proyecto después de una serie de búsquedas en Google para descargar el archivo excel de la declaración SQL

    // $sql = sql query e.g "select * from mytablename"
    // $filename = name of the file to download 
        function queryToExcel($sql, $fileName = ''name.xlsx'') {
                // initialise excel column name
                // currently limited to queries with less than 27 columns
        $columnArray = array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z");
                // Execute the database query
                $result =  mysql_query($sql) or die(mysql_error());

                // Instantiate a new PHPExcel object
                $objPHPExcel = new PHPExcel();
                // Set the active Excel worksheet to sheet 0
                $objPHPExcel->setActiveSheetIndex(0);
                // Initialise the Excel row number
                $rowCount = 1;
    // fetch result set column information
                $finfo = mysqli_fetch_fields($result);
// initialise columnlenght counter                
$columnlenght = 0;
                foreach ($finfo as $val) {
// set column header values                   
  $objPHPExcel->getActiveSheet()->SetCellValue($columnArray[$columnlenght++] . $rowCount, $val->name);
                }
// make the column headers bold
                $objPHPExcel->getActiveSheet()->getStyle($columnArray[0]."1:".$columnArray[$columnlenght]."1")->getFont()->setBold(true);

                $rowCount++;
                // Iterate through each result from the SQL query in turn
                // We fetch each database result row into $row in turn

                while ($row = mysqli_fetch_array($result, MYSQL_NUM)) {
                    for ($i = 0; $i < $columnLenght; $i++) {
                        $objPHPExcel->getActiveSheet()->SetCellValue($columnArray[$i] . $rowCount, $row[$i]);
                    }
                    $rowCount++;
                }
// set header information to force download
                header(''Content-type: application/vnd.ms-excel'');
                header(''Content-Disposition: attachment; filename="'' . $fileName . ''"'');
                // Instantiate a Writer to create an OfficeOpenXML Excel .xlsx file        
                // Write the Excel file to filename some_excel_file.xlsx in the current directory                
                $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); 
                // Write the Excel file to filename some_excel_file.xlsx in the current directory
                $objWriter->save(''php://output'');
            }

Answer 2

Prueba el siguiente ejemplo completo para el mismo

<?php
  $objPHPExcel = new PHPExcel();
  $query1 = "SELECT * FROM employee";
  $exec1 = mysql_query($query1) or die ("Error in Query1".mysql_error());
  $serialnumber=0;
  //Set header with temp array
  $tmparray =array("Sr.Number","Employee Login","Employee Name");
  //take new main array and set header array in it.
  $sheet =array($tmparray);

  while ($res1 = mysql_fetch_array($exec1))
  {
    $tmparray =array();
    $serialnumber = $serialnumber + 1;
    array_push($tmparray,$serialnumber);
    $employeelogin = $res1[''employeelogin''];
    array_push($tmparray,$employeelogin);
    $employeename = $res1[''employeename''];
    array_push($tmparray,$employeename);   
    array_push($sheet,$tmparray);
  }
   header(''Content-type: application/vnd.ms-excel'');
   header(''Content-Disposition: attachment; filename="name.xlsx"'');
  $worksheet = $objPHPExcel->getActiveSheet();
  foreach($sheet as $row => $columns) {
    foreach($columns as $column => $data) {
        $worksheet->setCellValueByColumnAndRow($column, $row + 1, $data);
    }
  }

  //make first row bold
  $objPHPExcel->getActiveSheet()->getStyle("A1:I1")->getFont()->setBold(true);
  $objPHPExcel->setActiveSheetIndex(0);
  $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ''Excel2007'');
  $objWriter->save(str_replace(''.php'', ''.xlsx'', __FILE__));
?>

Answer 3

Si has copiado esto directamente, entonces:

->setCellValue(''B2'', Ackermann'') 

debiera ser

->setCellValue(''B2'', ''Ackermann'') 

En respuesta a tu pregunta:

Obtenga los datos que desee de limesurvey y use setCellValue () para almacenar esos valores de datos en las celdas donde desea almacenarlos.

El archivo de ejemplo Quadratic.php en / Tests podría ayudar como punto de partida: toma datos de un formulario de entrada y los establece en celdas de un libro de Excel.

EDITAR

Un ejemplo extremadamente simplista:

// Create your database query
$query = "SELECT * FROM myDataTable";  

// Execute the database query
$result = mysql_query($query) or die(mysql_error());

// Instantiate a new PHPExcel object
$objPHPExcel = new PHPExcel(); 
// Set the active Excel worksheet to sheet 0
$objPHPExcel->setActiveSheetIndex(0); 
// Initialise the Excel row number
$rowCount = 1; 
// Iterate through each result from the SQL query in turn
// We fetch each database result row into $row in turn
while($row = mysql_fetch_array($result)){ 
    // Set cell An to the "name" column from the database (assuming you have a column called name)
    //    where n is the Excel row number (ie cell A1 in the first row)
    $objPHPExcel->getActiveSheet()->SetCellValue(''A''.$rowCount, $row[''name'']); 
    // Set cell Bn to the "age" column from the database (assuming you have a column called age)
    //    where n is the Excel row number (ie cell A1 in the first row)
    $objPHPExcel->getActiveSheet()->SetCellValue(''B''.$rowCount, $row[''age'']); 
    // Increment the Excel row counter
    $rowCount++; 
} 

// Instantiate a Writer to create an OfficeOpenXML Excel .xlsx file
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); 
// Write the Excel file to filename some_excel_file.xlsx in the current directory
$objWriter->save(''some_excel_file.xlsx''); 

EDITAR # 2

Usando su código existente como base

// Instantiate a new PHPExcel object 
$objPHPExcel = new PHPExcel();  
// Set the active Excel worksheet to sheet 0 
$objPHPExcel->setActiveSheetIndex(0);  
// Initialise the Excel row number 
$rowCount = 1;  

//start of printing column names as names of MySQL fields  
$column = ''A'';
for ($i = 1; $i < mysql_num_fields($result); $i++)  
{
    $objPHPExcel->getActiveSheet()->setCellValue($column.$rowCount, mysql_field_name($result,$i));
    $column++;
}
//end of adding column names  

//start while loop to get data  
$rowCount = 2;  
while($row = mysql_fetch_row($result))  
{  
    $column = ''A'';
    for($j=1; $j<mysql_num_fields($result);$j++)  
    {  
        if(!isset($row[$j]))  
            $value = NULL;  
        elseif ($row[$j] != "")  
            $value = strip_tags($row[$j]);  
        else  
            $value = "";  

        $objPHPExcel->getActiveSheet()->setCellValue($column.$rowCount, $value);
        $column++;
    }  
    $rowCount++;
} 


// Redirect output to a client’s web browser (Excel5) 
header(''Content-Type: application/vnd.ms-excel''); 
header(''Content-Disposition: attachment;filename="Limesurvey_Results.xls"''); 
header(''Cache-Control: max-age=0''); 
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ''Excel5''); 
$objWriter->save(''php://output'');

Answer 4

Trabajo 100%. Tal vez no esté relacionado con la respuesta del creador, pero lo comparto para que los usuarios tengan un problema con la exportación de consultas de MySQL para sobresalir con phpexcel. Buena suerte.

require(''../phpexcel/PHPExcel.php'');

require(''../phpexcel/PHPExcel/Writer/Excel5.php'');

$filename = ''userReport''; //your file name

    $objPHPExcel = new PHPExcel();
    /*********************Add column headings START**********************/
    $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue(''A1'', ''username'')
                ->setCellValue(''B1'', ''city_name'');

    /*********************Add data entries START**********************/
//get_result_array_from_class**You can replace your sql code with this line.
$result = $get_report_clas->get_user_report();
//set variable for count table fields.
$num_row = 1;
foreach ($result as $value) {
  $user_name = $value[''username''];
  $c_code = $value[''city_name''];
  $num_row++;
        $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue(''A''.$num_row, $user_name )
                ->setCellValue(''B''.$num_row, $c_code );
}

    /*********************Autoresize column width depending upon contents START**********************/
    foreach(range(''A'',''B'') as $columnID) {
        $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
    }
    $objPHPExcel->getActiveSheet()->getStyle(''A1:B1'')->getFont()->setBold(true);



//Make heading font bold

        /*********************Add color to heading START**********************/
        $objPHPExcel->getActiveSheet()
                    ->getStyle(''A1:B1'')
                    ->getFill()
                    ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
                    ->getStartColor()
                    ->setARGB(''99ff99'');

        $objPHPExcel->getActiveSheet()->setTitle(''userReport''); //give title to sheet
        $objPHPExcel->setActiveSheetIndex(0);
        header(''Content-Type: application/vnd.ms-excel'');
        header("Content-Disposition: attachment;Filename=$filename.xls");
        header(''Cache-Control: max-age=0'');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ''Excel5'');
        $objWriter->save(''php://output'');

Answer 5

$this->load->library(''excel'');
$file_name = ''Demo'';
$arrHeader = array(''Name'', ''Mobile'');
$arrRows = array(0=>array(''Name''=>''Jayant'',''Mobile''=>54545), 1=>array(''Name''=>''Jayant1'', ''Mobile''=>44454), 2=>array(''Name''=>''Jayant2'',''Mobile''=>111222), 3=>array(''Name''=>''Jayant3'', ''Mobile''=>99999));
$this->excel->getActiveSheet()->fromArray($arrHeader,'''',''A1'');
$this->excel->getActiveSheet()->fromArray($arrRows);
header(''Content-Type: application/vnd.ms-excel''); //mime type
header(''Content-Disposition: attachment;filename="''.$file_name.''"''); //tell browser what''s the file name
header(''Cache-Control: max-age=0''); //no cache
$objWriter = PHPExcel_IOFactory::createWriter($this->excel, ''Excel5'');  
$objWriter->save(''php://output'');