i want (and i have but dont work!) to do this
1. user can upload excel file
2. check if the file is excel
3. of the file is excel this be visible on html page
4. the user can edit the excel file
5. all edits i want to saved in real time and on excel file
i have create on xampp\\htdocs a file with name index
on xampp\\htdocs\\index i have create
3 php files:
index.php
save_excel.php
upload_excel.php
1 json file:
composer.json
1 file with name upload
and i have download the https://github.com/PHPOffice/PhpSpreadsheet and i have the the file "src"
i have download the Composer-Setup.exe and i have run on cmd two order composer init and composer require phpoffice/phpspreadsheet.
My problem is that this i want to do, dont did it.
Down i give you and my code to tell me if i have something wrong.
What I have tried:
index.php:
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Edit Excel File</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script>
$(document).ready(function(){
$('td').attr('contenteditable', true);
$('td').on('focus', function(){
$(this).data('before', $(this).html());
});
$('td').on('blur', function(){
if ($(this).data('before') !== $(this).html()) {
var row = $(this).parent().index() + 1;
var col = $(this).index() + 1;
var value = $(this).text();
$.ajax({
url: 'save_excel.php',
type: 'post',
data: {row: row, col: col, value: value},
success: function(response){
console.log(response);
},
error: function(xhr){
console.log(xhr.responseText);
}
});
}
});
});
</script>
</head>
<body>
<h1>Edit Excel File</h1>
<form method="post" action="upload_excel.php" enctype="multipart/form-data">
<input type="file" name="file">
<input type="submit" name="submit" value="Upload">
</form>
<?php
if(isset($_SESSION['filename'])) {
echo '<table>';
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($_SESSION['filename']);
$worksheet = $spreadsheet->getActiveSheet();
foreach($worksheet->getRowIterator() as $row) {
echo '<tr>';
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
foreach($cellIterator as $cell) {
echo '<td>' . $cell->getValue() . '</td>';
}
echo '</tr>';
}
echo '</table>';
}
?>
</body>
</html>
save_excel.php:
<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
$row = $_POST['row'];
$col = $_POST['col'];
$value = $_POST['value'];
$filename = 'example.xlsx';
$spreadsheet = IOFactory::load($filename);
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->setCellValueByColumnAndRow($col, $row, $value);
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save($filename);
echo 'Cell updated successfully!';
?>
upload_excel.php:
<?php
if(isset($_FILES['file'])) {
$allowed_extensions = array('xls', 'xlsx');
$extension = pathinfo($_FILES['file']['name'], PATHINFO_EXTENSION);
if(in_array($extension, $allowed_extensions)) {
$tmp_name = $_FILES['file']['tmp_name'];
$filename = basename($_FILES['file']['name']);
$file_path = 'uploads/' . $filename;
move_uploaded_file($tmp_name, $file_path);
session_start();
$_SESSION['filename'] = $file_path;
header('Location: index.php');
exit;
} else {
header('Location: index.php?error=invalid_file_type');
exit;
}
}
?>
composer.json:
{
"require": {
"phpoffice/phpspreadsheet": "^1.18.0"
}
}