Невозможно импортировать файлы xls / xlsx

Question

Невозможно импортировать файлы xls / xlsx

Я импортирую файл Excel в MySQL, используя PHP. Следующий код работает для файлов csv, но не для форматов файлов xls / xlsx.

<html>
<head>
<meta charset="utf-8">
<title>Import Excel to MySQL using PHP </title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
</head>
<body style="padding-top:50px;">

<div class="container"><!-- container class is used to centered  the body of the browser with some decent width-->
<div class="row"><!-- row class is used for grid system in Bootstrap-->
<div class="col-md-4 col-md-offset-4"><!--col-md-4 is used to create the no of colums in the grid also use for medimum and large devices-->
<div class="login-panel panel panel-success">
<div class="panel-heading">
<h3 class="panel-title">Import here</h3>
</div>
<div class="panel-body">

<form method="post" action="import.php" enctype="multipart/form-data">
<fieldset>
<div class="form-group">
<input type="file" name="file"/>
</div>
<input class="btn btn-success" type="submit" name="submit_file" value="Submit"/>
</fieldset>
</form>

</div>
</div>
</div>
</div>
</div>
</body>
</html>

Import.php

<?php
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
/*
Developer: Ehtesham Mehmood
Site:      PHPCodify.com
Script:    Import Excel to MySQL using PHP and Bootstrap
File:      import.php
*/

// Including database connections
require_once 'db_con.php';

if(isset($_POST["submit_file"]))
{
$file = $_FILES["file"]["tmp_name"];

$file_open = fopen($file,"r");
while(($csv = fgetcsv($file_open, 1000, ",")) !== false)
{
$employee_name = $csv[0];
$employee_designation = $csv[1];
$employee_salary = $csv[2];

$stmt = $DBcon->prepare("INSERT INTO employee(employee_name,employee_designation,employee_salary) VALUES(:employee_name,:employee_designation,:employee_salary)");

$stmt->bindparam(':employee_name', $employee_name);
$stmt->bindparam(':employee_designation', $employee_designation);
$stmt->bindparam(':employee_salary', $employee_salary);
$stmt->execute();
}
}

echo "Imported Successfully";
?>

0

excel import php

Решение

Другие решения

Это связано с тем, что файлы Excel, xls или xlsx, не являются файлами CSV.

Существует 3 разных формата файлов, которые требуют 3 разных подходов.
Вам нужно найти библиотеку Excel для PHP, как EasyXLS например:

$workbook = new COM("EasyXLS.ExcelDocument");
//for xls file
$rows = $workbook->easy_ReadXLSActiveSheet_AsList("file.xls");
//or
//for xlsx file
$rows = $workbook->easy_ReadXLSXActiveSheet_AsList("file.xlsx");
for ($rowIndex=0; $rowIndex<$rows->size(); $rowIndex++)
{
$row = $rows->elementAt($rowIndex);
$employee_name = $row->elementAt(0);
$employee_designation = $row->elementAt(1);
$employee_salary = $row->elementAt(2);

$stmt = $DBcon->prepare("INSERT INTO employee(employee_name,employee_designation,employee_salary) VALUES(:employee_name,:employee_designation,:employee_salary)");

$stmt->bindparam(':employee_name', $employee_name);
$stmt->bindparam(':employee_designation', $employee_designation);
$stmt->bindparam(':employee_salary', $employee_salary);
$stmt->execute();
}

Больше о импорт файла Excel в MySQL с использованием PHP.

0

Источник

Accepted Answer

Разные примеры XLS файлы в PHP. Здесь я прилагаю пример кода.

Разбор строк:

$xlsx = new SimpleXLSX('File_For_Import.xlsx');
list($num_cols, $num_rows) = $xlsx->dimension();
$f = 0;
foreach ($xlsx->rows() as $r) {
// Ignore the inital name row of excel file
if ($f == 0) {
$f++;
continue;
}
//sample column-names for your understanding.
for ($i = 0; $i < $num_cols; $i++) {
if ($i == 0)
$data['employee_name'] = $r[$i];
else if($i == 1)
$data['employee_designation'] = $r[$i];
else if ($i == 2)
$data['employee_salary'] = $r[$i];
//write code according to your DB.
}
$data['class_id'] = $this->input->post('class_id');
$this->db->insert('student', $data);
//print_r($data);
}

0