EXCEL CLONE -- CELL FUNCTIONALITY

16 December 2023 18:55

Two binding interactivity

-> changes made in data should be reflected on ui as well
For eg -> bold in data -> reflected to ui as bold in ui and also bold icon highlighted

STORAGE KE LIYE USE MATRIX

Ek bada array -- then row ke containers [array] uske andar

JAVASCRIPT OBJECT -> [KEY, VARIABLE ] form

Cell ki properties ko asani se store kiya ja sakta hai via js objects
All property values corresponding to a particular cell

code Page 1
COLOR WALA ELEMENT //BUILDING UI PART --
Agar sift input tag mein dalte ho to side me ati hai
But we want it when we click on the A or fill so ham
Visibility 0 karenge , aur absolute display mein height full kardenge to poore div mein color element
hoga par dikhega icon hi -- matlab no icon ko over shadow nahi karega

///making basic structure

SHEET DB BANAO
100 Sheet row banao aur append kardo in sheetDB

Now cell prop banao jismein sari prop hogi har cell phir is cell prop ko sheet row mein add kardo

////defining properties

///special identification of each cell row id and col id

code Page 2
///////////////selectors for cell property

///adding clickListner to all properties

click karne ke baad cell ko access karo
Uske baad - cell mein changes bananen ke liye grid ko access karo //via [Link] -
isse JS CELL KO ACCESS KARPAEGI
Phir cell prop mein changes ke liye storage ko access karo // ye storage mien changes kardega
changes ko
Iske baad ui mein bhi changes reflect hojaenge

///CHANGES PEHLE DATA MEIN HONGE

Active cell ka Address kese access karein -- directly address-bar se -> encoded hai "a1"-- use decode
karo to row, id and cell id

code Page 3
//// taki koi null value ka error na aie

This is done in [Link]

///// COMING BACK TO [Link]

///conversion logic

///adress decoding
///charCodeAt(index)

CHARCODE AT -> STRING X ADRESS PE

[Link]

//// acessing cell and storage

let cell = [Link](`.cell[rid="${rid}"][cid="${cid}"]`);

///pehle DATA CHANGE TAKI WE CAN BE SURE KI PEHLE DATA CHANGE HO CHUKA HAI
///storage mein change karo then ui mein change karo

Bold button ko bhi active inactive dikhana hai

Also define active inactive pehle hi

code Page 4
Also define active inactive pehle hi

////FOR FONTSIZE / FONT STYLE --> CHANGE INSTEAD OF CLICK

+"px" concatinate karna hota hai taki fontsize evaluate karpae

///// ALIGNMENT

For ui change 2 Switch used taki bus ek hi part active rahe

///JIS CELL MEIN CHANGES AI HAI USI KI PROPERTIES CHANGE HO

code Page 5
Excel clone --> formula sheet
18 December 2023 14:34

//EVENT LISTENER BLUR/FOCUS

//building blur - functionality

Select cells // in general cell naam ki entity ko selet karrte hai {icon vese cell}
Put eventlistner of blur
//us particular cell ka address lere hai jaha pe hai
Address lo ui + db access karo address ke through

Us adress mein jo value daali hai use lo

Value ko data mein dalo

Blur islie lie use liya kyu focus gets triggers as soon as click happens -- uske baad ka jo bhi hai it
doesn’t care
Email sahi hai ya nahi etc ke liye blur use karna hota hai
Focus is just to see -- oh okay field empty nahi hai

We want is last change to be filled in value

For the same reasons as focus click use nahi kiya -- kyuki ye bhi atmost 1st value deta

////////////////formulaBar manipulation

Key down eventlistener -> agar enter press hota hai and formula bar ki value null nahi hai evaluate
function

//function evaluation ke liye - eval()

Expression leta hai - apne app hi evaluate karke value return kardeta hai

Formula baar mein jaake dekhna hai -- 10+10 enter formula baar mein press karna hai
Enter trigger hua + Formula bar value hai ? [-> pass formula to evalualteformula() that contians eval ]
-> evaluated formula value lo -> ui update , db update

code Page 6
///////////////////
Handling dependence expressions

//adressdecode karke {jesa adress bar ke liye kiya tha } cell prop mein jake value field ko lena hai
phir evaluation easy hai

//FORMULA MUST BE SPACE SEPERATED

In the evaluateFormula section

Take fomula then split it wrt space("")
Check if sting ka index is ascii use asciivalue -- if 65 to 90 -> aski hai

Direct value hoti to say '650' i=0 pe to single digit hi asakta hai [0 to 9]
To i=[0,1,2] hota then [6 5 0]
But agar 'a' hoga to value 65 ho karti hai

code Page 7
=======> HANDLING DYNAMIC CHANGING OF DATA + CHILDREN MEIN BHI USE REFLECT KARNA
<========
/////AGAR KUCH BHI CHANGE HOTA HAI TO IT SOULD BE REFLECTED TO CHILDREN CELLS TOO
/////DATA UPDATE INBETWEEN
-> PROBLEM CAN BE TACKLED VIA RECURSION
-> DEPENDENCE DEfINED NAHI HAI

Har cell ko batana padega ki uske pass children hai -- cellProp mein children array

Achieved via recursion

FUNCTION USED ADD CHILD TO PARENT

-------> function will be similar to ascii value check wala

-------> agar formula ata hai 10+10 -> clean hai no need for checking dependency
------> if formula for B1 is (A1 + 10) then check for ascii-> if ascii then -> extract (A1 ka) address -> put
B1 as a child in A1 ki child array

//////////PROBLEM 2 -> WHAT IF FORMULA HI EDIT KARDIYA? -> PAR CHILD RELATIONSHIP BHI TO
CHANGE HOGI
----> jese hi formula change -purane par child rel ko tordo naye formula ko dekhke firse banao

code Page 8
// purana parent kese milega -> soln -> Old formula given in function
//what if dobara jo formula dala hai wo same hi ho -> check if formula has been changed or not

Inputformula -> naya formula

[Link] -> purana formula jo cell prop mein formula mein save hua hua hai

Old formula given taki pura relation mil sake aur use tora ja sake

Pehle relationship tootega phir evaluate hoga

///RCP MEIN A1 KA SIRF EK CHILD HI REMOVE KARNA HAI -> B1 baaki saare to wahi rahenge to
index dhoondna padega

//indexof //splice

/// UPDATING CHILDREN CELLS VIA RECURSION

//parent adressed passed jis bhi cell ne khud ko change kiya use pass karo

Access kro parent pe jo function lage hai unko

Aur children pe lagate jao --- re cursively

code Page 9
///jab cel ki values change karein vo bhi formulae mein reflect honi chaie

code Page 10
Cycle detection
19 December 2023 12:12

-----> uni-directional only parent to child hi relation hai , child vapar parent pe nahi jata

---------> cycle detection

code Page 11
---------> cycle detection

///// working

code Page 12
5 not visited since visited array mein 5 already visited hai so no calls made

code Page 13
5 not visited since visited array mein 5 already visited hai so no calls made

code Page 14
/////////cyclic mein path remains the same ----- agar clyclic nahia hai to path 2 hote hia -- same path
nahi rehta hai

3456 wala cyclic nahi hai -- u can see the arrows too they don’t form a cycle

///storage 2D matrix hogi jimein cellprop ki jagah - children ke naam honge

code Page 15
///storage 2D matrix hogi jimein cellprop ki jagah - children ke naam honge

///child representation in form of decoded [rid, cid]

code Page 16
////

function addChildToGraphComponent(inputFormula, childAdress)

Formula ke thru relationship identify

childAddress dalre hai kyuki uski ko decode karke formula mien dalna hai

/////////////in [Link]

//// agar graph cyclic nikla to addChild wale relationship ko break bhi karna
padega

////////////// VISITED ARRAY AND 2DVISITED ARRAY JO EXPLAINATION KE VAKATH 1D

KE FROM MEIN THEY ARE IN 2D FORM NOW SINCE CELL=[RID][CID] CELL->[[CHILD1],
[CHILD2]] SO HAR NODE 2D KE FORM MEIN HAI
ISILIE storage bhi 2d form

Initial dependency of all false

code Page 17
Initial dependency of all false

26 - 26 false bhardo har row mein

Sab 1 ke through visit nahi honge --> as done in eg 7,8,9 alag component that
was not visited thru 1
Islei for loop from 1 to 9 to visit all nodes

//component alag alag par graph same -- any component cyclic => graph cyclic

//SRCR,SRCC -> gives A1(that containes all children info A1=[[0,1][0,2]....]

code Page 18
Function
Step 1) Set bith vis and dfs vis true of that particular cell
Step 2) checking graph connections till the depth and checking cyclic
condition at every point
Step 3) i)dfs stack se false kardo -- jo end ke baad visited wale hai
ii)Return false if no true condition found in step 3

code Page 19
Cycle color
19 December 2023 21:54

/// take source point jaha cycle bani thi

Some changes to [Link]

code Page 20
//CELL COLORING

-> CELL NIKALO

let cell = [Link](`.cell[rid="${srcr}" [cid="${srcc}"]`);
-> colour the cell
[Link] = "lightblue";
->

After returning 1 ek karke call del hoti jaengi

code Page 21
Main cycle point dhondo - color it light salmon
Return karte samay transparent

Phir baki cells ko bhi trasparnetn

///////////////color done

///NOW SET DELAY

Even after this no color shown

code Page 22
Even after this no color shown
Reason -- async in JS

SETTIMEOUT -> ASYNCHRONOUS HAI -> not in sync with code

To convert async to sync -> promises is required
Wrap set time put in promises

Jo await sirf dfscycdettrp mein hi kaam karra hai ie bas isi function mein wait hoga
Baki code to run karra hi ho

So to implement it across code

Wait lagane ki liye simply wrap in promise

code Page 23
Jo source hia uske baad jo orange hota hai uske just pehle wala cell blue hi reh jata hai kyuki

Ab baki sare to tranparent ho jaenge handled hai par src srcc ko tranparent karne keliey end mein bhi tenasparent lagana
padega

code Page 24
Excel clone - sheet
20 December 2023 20:32

Creating sheet dynamically

Sheet button mien click listener daalo //dynamic sheet div

Click event pe sheet bano creation
Give these sheets a clss //isolated data sheet for
//give every sheet an id too every sheet
(sheetfolder ko access karo take its length) //isolated graph
As sheets add sheet-folder length increase components matrix for
Now - innerHTML of sheet numbering ke liye every sheet
//handling sheet togling
//now add this sheet to the folder etc
//handel sheet-cell
properties -> bold etc
aur is hisab se tab mein
bhi change

//now style this

Give space between sheets , pointer cursor

///sheet data and ui

Ui ko change karni ki zaroorat nahi --- Keep the ui same but keep changing the data ---
Soln : same ui mein sheet ki data override kardo - pehle sheet 1 active phir sheet 2 like
this

Data isolation:
Ek bara array bano jismein har ek sheet ki data push hoti jae (Data -> 2 matrix)

Make a sheet db array in cell prop js --

Comment the properties code at cell properties
Create a function that creates sheet dbs and adds sheet db to this big sheet db

code Page 25
Create a function that creates sheet dbs and adds sheet db to this big sheet db

//ALAG ALAG SHEET KELIYE alag alag DBD create ki --- alag graph matrix bhi create karni
hogi

//take a big collectedgraph component matrix

Usmein keed adding har sheet ka graph component matrix

//HANDLIND SHEET TOGGLIG ETC

Sheet pe click handler lago
Sheet ka data access hosakta hai via -> ID get it and display it on the ui

// HANDLE SHEET CELL PROPERTIES

Since UI esa design kiya tha kji har cell mein event listener laga hua hai to as you click
You get all the cell properties aur tab pe bhi reflect karjata hai
To jese hi nayi sheet aiegi Naya db aiega just click on every cell taki Har cell ki properties
reflect hojae
The working done earlier

1) Make a function
2) Access cell via [][]
3) Click on all cells

code Page 26
//ek sheet by default -- dikhni chaie

//active sheet button ui function

Access all sheets from allsheets from allsheets
If no access via dom then no style change
Iterate and make all non selected sheets transparent
Selcted sheet ka bgcolor set kardo

////////handliing sheet removal

Right click ke thru handel karna hai -> write event listner for this
In that event listner
If not right click return
o/w move fw
Access allsheetsfolder -> take the length -> if only 1 alert dikhao ki you need atleast 1
sheet to work with and return
o/w move fw
NOW FOR SHEET REMOVAL -> TAKE INDEX -> USE SPLICE TO REMOVE PARTICUALR
INDEX FROM collectedsheetdb and graphdb

code Page 27
Now remove sheet ui and show default sheet ie sheet 1

Sheet rmove ke baad indexing bhi sahi karni padegi 1,2,3 -> 2 removed -> now rearrage
1 ,3 as 1,2

code Page 28
Excel clone - cut copy paste upload download
21 December 2023 11:09

1) Control key toggling

Use keydown / keyup eventlistener in the document
-> u don’t need to find whether it is the control key or not -> BUS STATE PATA KARNI HAI

2) Selecting range

i) To add an eventlistner get the cells via dom first

ii) Call handel selcted cell
iii) Store the 2 range cells in an array
iv) Add click event listner to the selected cell Add eventlistener to all cells for handel cell purpose
only [single cell can have more than 1 event listner]
v) Constraint 1 - if control is not clicked return
vi) Constraint 2 - if already 2 cells present return
vii) Fix ui -> add border

code Page 29
vii) Fix ui -> add border
viii) Get the rid, cid of the cell - push it in range storage

3) Range length wala point improve karke -> agar karoge to 2 karoge varna -> karogehi nhai
Since baar baar different range select ho sakte hai
If more than 2 selected -> remove the previous 2 from the storage
Function -> when 3rd cell is selected autimatically -> 2 cells earlier -> remove selected ->empty range
storage

4) Cut copy paster btn

/////pasting

code Page 30
 1) Select button - add a click listener
2) Calc row difference and col difference
3) Take target address via address bar
4) Implement a loop for pasting data

5) Access the cell + apply constraint -> agar cell limit over to return karo
6) sheetDB change karo by taking info from copyDB
7) Change the data in sheetDB [since new cell mein data jara hai]
8) Cellprop mein value etc copy karo from copyDB [yahi cells ka sara data copy hora hai]
9) UI change -> simply click since cell pe click listner hai in [Link] that changes the ui
according to cell properties in db on click

Improvement in copy
-> start hote hi make a new copy[] so that har baar click hone pe it gets empty and only new info
exists
-> copy only when a range is given

///cut
1) Same paste wala loop chalake Access each cell
2) Then set all cellprop to default
3) Click to make ui changes
4) Remove green box via defaultSelectedCellsUI

code Page 31
code Page 32
Excel clone open save
21 December 2023 18:06

Json form chosen since it is easier to tackel data in this manner

Simple google search done to know the method

S1) stingify json file data, data -> graph + sheetDB

Convert json to data
let jsonData = [Link]([sheetDB, graphComponentMatrix]);

//BLOB() -> search online FILE JESI DATA iske through download upload etc karna is easy

// similarly google how to read a file in javascript for upload wala task

code Page 33