Hello ..
I was work on a project so my boss told me he want to add so much large data from his exel sheet without using import feature just by copy and paste specific data from that exel sheet.
Any way i found away to do that in PHPMaker , And i would love to share this way with you ,So may help some one :) .
First of all we get started with handsontable Lib
Installation With PHPMaker Steps :
1- Now we create our example SQL database Named : 'employs_system' ,With tables :
'employs_id'
'employs_name'
'employs_phone'
'employs_salary'
You may import directly this SQL to your created database 'employs_system' :
-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Feb 16, 2024 at 09:44 AM
-- Server version: 10.4.28-MariaDB
-- PHP Version: 8.2.4
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `employs_system`
--
-- --------------------------------------------------------
--
-- Table structure for table `employs_table`
--
CREATE TABLE `employs_table` (
`employs_id` int(11) NOT NULL,
`employs_name` varchar(255) NOT NULL,
`employs_phone` varchar(100) NOT NULL,
`employs_salary` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `employs_table`
--
ALTER TABLE `employs_table`
ADD PRIMARY KEY (`employs_id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `employs_table`
--
ALTER TABLE `employs_table`
MODIFY `employs_id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;2- Now open your PHPMaker and start new project ,Then start connect and load our created database.
3- After loading the project we going to load handsontable npm packge in PHPMaker go to tools->npm packages , then type in the search field handsontable and press on Add button on the right side then specifiy the npm version to 14.0 ,And then click ok to save .
4- after loading the handsontable npm packge to your PHPMaker project now we going to make our Custom File from left side to start code our adding page .
The custom file settings :
file name : addEmploysExelSheet.php
Caption : Add new Employs exel sheet
include common files : checked 5- After adding our custom file select on it and make it the Default Page by check on Default in the Table Tap ,And then go to the Code Tap from the top then go to Custom Templates -> Table-Specific -> Custom File -> Content , Then add the page content code in the coding white area :
<button id="add_button" onClick="saveDataToServer()" class="btn btn-primary mt-2" type="button">
<span id="add_challang_spinner" class="spinner-border spinner-border-sm" role="status" aria-hidden="true" style="display:none;"></span>
Add
</button>
<br><br>
<div id="example"></div>
<script>
const container = document.querySelector('#example');
const hot = new Handsontable(container, {
data: [],
dataSchema: { employsName: null, employsPhone:null, employsSalary: null},
startRows: 5,
startCols: 4,
colHeaders: ["employsName","employsPhone","employsSalary"],
height: '400',
width: 'auto',
columns: [
{ data: 'employsName' },
{ data: 'employsPhone' },
{ data: 'employsSalary' }
],
minSpareRows: 1,
autoWrapRow: true,
autoWrapCol: true,
licenseKey: 'non-commercial-and-evaluation'
});
function getDataFromHandsontable() {
return hot.getData();
}
</script>
<script>
window.saveDataToServer = function() {
var dataGet = getDataFromHandsontable();
const loadingElement = document.getElementById("add_challang_spinner");
loadingElement.style.display = "inline-block";
const addgButton = document.getElementById("add_button");
addgButton.setAttribute("disabled", "");
var newArray = []
dataGet.forEach(element => {
var elm = {employsName: element[0], employsPhone:element[1], employsSalary: element[2]}
newArray.push(elm);
});
let dataG = JSON.stringify(newArray);
$.ajax({
url:"<?= BasePath() ?>/api/addNewEmploySheet",
method:"POST",
dataType:"json",
data:{dataSet:dataG},
success:function(data, textStatus, xhr){
if(xhr.status == 200){
addgButton.removeAttribute("disabled");
console.log('Success:', data);
loadingElement.style.display = "none";
window.location.href = "./"
}else{
alert("Error: Please add 1 or more recoreds !!");
addgButton.removeAttribute("disabled");
loadingElement.style.display = "none";
}
},
error: function (xhr, ajaxOptions, thrownError) {
alert("Status: " + xhr.status + ", Error: " + thrownError);
addgButton.removeAttribute("disabled");
loadingElement.style.display = "none";
}
});
}
</script>6- After Coding your addEmploysExelSheet.php Page ,Now from same Code Tap we get to Server Events -> Global ->All Pages -> Api_Action . To start add our Api action to the project.
Inside the Api_Action function we add :
$app->post('/addNewEmploySheet', function ($request, $response, $args) {
$body = $request->getParsedBodyParam('dataSet');
if (isLoggedIn() && $body != null) {
$userID = CurrentUserID();
$data = json_decode($body, true);
$isArray = is_array($data);
$currentDate = CurrentDate();
$i = 0;
if(!$isArray) {
return $response->withJson(["success" => false ,"error" => "Not allowed or is not an array data !!"])->withStatus(401);
}
foreach ($data as $row) {
if($row['employsName'] != null && $row['employsPhone'] != null && $row['employsSalary'] != null) {
$queryBuilder = Insert("employs_table");
$queryBuilder->setValue("employs_name", $queryBuilder->createPositionalParameter($row['employsName']))
->setValue("employs_phone", $queryBuilder->createPositionalParameter($row['employsPhone']))
->setValue("employs_salary", $queryBuilder->createPositionalParameter($row['employsSalary']))
->executeStatement(); // Insert
$i++;
}
}
if($i > 0) {
return $response->withJson([ "success" => true])->withStatus(200);
}
return $response->withJson([ "success" => false,"error" => "Please add 1 or more recoreds !!"])->withStatus(201);
}
return $response->withJson([ "success" => false,"error" => "You are not logeadIn !!"])->withStatus(401);
});7- After adding your Api route action , Now we Add this java script Code to Server Event -> Page_Head Section :
<link rel="stylesheet" href="<?= $basePath ?>handsontable/dist/handsontable.full.min.css" />
<script type="text/javascript" src="<?= $basePath ?>handsontable/dist/handsontable.full.min.js"></script>8- Generate your project and then explore it in your browser ---> Nice try to add your data by copy and post it in the feilds then press on add Button.
Now you should see the record data has been added to the employs table.
That's all , Thanx ...