Add large data from Excel sheet by Copy & Paste

Tips submitted by PHPMaker users
Post Reply
rembo
User
Posts: 227

Add large data from Excel sheet by Copy & Paste

Post by rembo »

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 ...


mobhar
User
Posts: 11694

Post by mobhar »

I have followed all the instructions above, but after visiting the "Add new Employs exel sheet" page, I saw this error message from Console section of browser:

Uncaught ReferenceError: Handsontable is not defined
    at addemploysexelsheet:227:13

It seems you need to modify the instruction #7, since you use npm package from Tools -> npm package, then the package should be located under node_modules folder, so the code should be:

<link rel="stylesheet" href="<?= $basePath ?>node_modules/handsontable/dist/handsontable.full.min.css" />
<script type="text/javascript" src="<?= $basePath ?>node_modules/handsontable/dist/handsontable.full.min.js"></script>

rembo
User
Posts: 227

Post by rembo »

That's right. And you may remove isLoggedIn() in if statement in Api_Action function in this example, since it's just example so no need to login to post.
Thanx


mobhar
User
Posts: 11694

Post by mobhar »

Exactly, because if you don't remove isLoggedIn() part, then the message return from API will be 401 - unauthorized.


Post Reply