DEV Community: bperez3237

Import Xlsx into React

bperez3237 — Fri, 16 Sep 2022 00:08:05 +0000

For my latest react web application, I wanted to create an app to assist a workflow at my construction project. Because '.xlsx' files are very common on the project, I needed to be able to work with data from these files. My plan was to import data from excel sheet to create objects in the back end that would show up in my app.

I found a javascript package xlsx that would allow me to read the xlsx data once I selected the file. To setup the module:

In the command line:
npm install xlsx

At the top of the project:
var xlsx = require("xlsx")

With this, I would now be able to call xlsx.read() which would convert the raw file data into javascript code.

So I need a react component that can upload the file, and parse the data into usable js code. This js code would be used to POST objects to the backend.

First, to upload the file, my basic react component would be:

function Upload() {
     function handleFile(e) {
          e.preventDefault()
          if (e.target.files) {
          // read xlsx data
          }

     }

     return(
          <form>
               <input type="file" onChange={}></input>
          </form>
     )
}

To read the file data, I used FileReader() to access the contents of the files selected with the input.

const reader = new FileReader()

reader.onload = (e) => {
     const data = e.target.result
     const workbook = xlsx.read(data, {type: "array})
     // organize xlsx data into desired format

}
reader.readAsArrayBuffer(e.target.files[0])

The workbook has many ways to parse the information. I iterate through the sheets:

workbook.SheetNames.forEach((sheet)=>{
     const worksheet = workbook.Sheets[sheet]
     // format object
})

There are many ways to go from here: worksheet will return an object with keys for all the cells in the xlsx file. These cell labels are based on the x and y position: A,B,C... for the x axis and 1,2,3... for the y axis.

To get the value of cell 'B1' in the worksheet, call:
worksheet.B1.v
or
worksheet['B1']['v']

With this kind of data structure, it's possible to go through the xlsx sheet dynamically. A nested for loop to go through the data row by row or column by column.

for (let row = 1; row < lastRow ; row++) {
     for (let column = 'A'; column < lastColumn; column++) {
          cellValue = worksheet[`${column}${row}`]['v']
          // do something with cell value
     }
}

While this sounds easy, the code block above wouldn't work for a couple reasons:

lastRow and lastColumn aren't known values
column++ does not work since the increment operator doesn't work on 'A'

Taking a look at number 1 first:

Getting the value of the lastRow and lastColumn could be done using a custom max function. Javascript can interpret string comparisons like:
'B' > 'A' ==> true
'A' > 'B' ==> false
'A1' > 'D1' ==> false
'D2' > 'D1' ==> true

With this logic, a maxCell function could be created:

function maxCell(cell1, cell2) {
    if (cell1 > cell2) {
        return cell1
    } else {
        return cell2
    } 
}

And to extend this over an array:

function maxArray(array) {
        var max = ''
        array.forEach((elem)=>{
            max = maxCell(max, elem)
        })
        return max
    }

Since the keys of worksheet are all the cell values, calling:
maxArray(Object.keys(worksheet))

would return the largest cell label. And using the largest cell label, the string can be spliced to get the last row and column:

const largestCell = 'E8'
const lastRow = largestCell.slice(1)
const lastColumn = largestCell.slice(0,1)

Taking a look at number 2:

While it isn't as simple as 'A' + 1 = 'B', using the String object methods this is pretty easy. Strings have a method charCodeAt() that will return their UTF-16 character code. So I just need to convert to the character code, increment by 1, and then convert back to a string.

function nextChar(letter) {
        return String.fromCharCode(letter.charCodeAt(0) + 1);
    }

When I was originally building out my project, this was the thought process I followed. And I thought it would work until I realized this still has a couple bugs. I didn't realize at first, but javascript's string comparison works left to right. If the first left-most value is greater, then the comparison always returns true, and vice versa. For example:
'10' > '9' ==> false
'8' > '20' ==> true
'AA1' > 'B1' ==> false

And remember, column labels in xlsx increment X, Y, Z, AA, AB, AC, etc... Because of this logic, the maxCell function written before wouldn't always work correctly.

While I'm sure it's possible to modify maxCell() to factor in these constraints, to achieve a working function as desired, but at this point I felt like I was spending too much time on a small feature of my project. There had to be a less involved method to parse the data, or I had to place some constraints on my uploaded xlsx file.

By limiting the maxColumn to 'Z', I could avoid 'Z' > 'AA'. And then, since the column label is limited to one character, I can convert the row label to an integer and properly compare the row values.

function maxCell(cell1, cell2) {
    var row1 = cell1.slice(1)
    var column1 = parseInt(cell1.slice(0,1))
    var row2 = cell2.slice(1)
    var column2 = parseInt(cell2.slice(0,1))

    var maxColumn = (column1 > column2) ? column1 : column 2
    var maxRow = (row1 > row2) ? row1.toString() : row2.toString()

    return `${maxColumn}${maxRow}`
}

Finally, circling back to the nested for loop I originally wrote, I now have the helper functions to iterate over the xlsx values like originally intended. From here it was easy to format into my database table object and send a POST request.

Active Record Sessions Additional Functionality

bperez3237 — Fri, 29 Jul 2022 20:46:00 +0000

For my phase 4 project, my idea was to have a social-media-like app where the user logged in from a specific location. A user's interacting network would be limited to only the location they are logged in from. Because location was part of the login process, the session needed to store location data as well as user data. And then passing this to the client side becomes more difficult since two objects are passed.

To start from the basics, I setup an auto-login route based on the session. I knew this would need to be a custom route, given my output would need to return user data and location data, so I opted to create a custom controller function instead of the standard show function. My GET route in ruby would be:

get '/me', to:'users#auto_login'

And my client side would run a useEffect to fetch this route. Now, if I only needed user data, my auto_login function could be:

def auto_login user = User.find_by(id: session[:user_id]) if user render json: user else render json: { error: 'User not found'}, status: :unauthorized end end

How could I get the location of the last session for the auto-login to function? I'm not passing any parameters at the moment... Location is tied to the User through relationships, but this would give me all locations, not the one of the last session. Currently, my function works because I am able to get the user id from the session data. If I stored the location id to session, I could do the same thing. So I go back to my sessions controller create method.

Nothing too complicated, the same way I stored my user to the session, I will store the location to the session. If the password is authenticated, it will stored the location id from params to session[:location_id].

def create user = User.find_by(username: params[:username]) location = Location.find(params[:location_id]) if user&.authenticate(params[:password]) session[:user_id] = user.id session[:location_id] = location.id render json: user, status: :created else render json: { errors: "Invalid username or password" }, status: :unauthorized end end

Now, going back to my UsersController where I have my auto_login method, I am able to get the session location by running:

Location.find_by(id: session[:location_id])

Now I just need to return both objects to the client. I think the cleanest way would be to create a new, nested object, and then render as json. My final auto_login function is:

def auto_login user = User.find_by(id: session[:user_id]) location = Location.find_by(id: session[:location_id]) if user result = {user: user, location: location} render json: result else render json: { error: 'User not found'}, status: :unauthorized end end

Sinatra-React Project Server-Side Cool Features

bperez3237 — Thu, 09 Jun 2022 02:04:30 +0000

I ran into an interesting issue while working on my Sinatra-react project that opened my eyes to some of the benefits of server side coding.

Just for some context into my project, my idea was to create a project schedule planner and cost tracker. The user would be able to view the schedule of activities on one page and view all the cost transactions on another page. Each cost belongs to one activity, and each activity has many costs. I wanted the activities to have full CRUD capabilities so someone could change the schedule as things change in the project. The activities have “order” as a parameter so the schedule can sort the activity elements in the correct order.

In creating the “DELETE” request, I realized multiple changes needed to be made to my program for everything to keep making sense. In addition to updating the activities state variable, I would need to update the costs and order of activities. Deleting any activity would change the order of the activities after it. And if I deleted an activity, it would affect any costs with that activity_id.

Initially I thought to do this with multiple fetch requests. I would complete the "DELETE" request, then do a "GET" for cost and a "GET" for activities.

function handleDelete(id) {
        fetch(`http://localhost:9292/activities/${id}`,{
            method: "DELETE",
        })
        const updatedActivities=activities.filter((activity)=> activity.id !==id)
        setActivities(updatedActivities)
        setToggleInfo(!toggleInfo)

        fetch('http://localhost:9292/costs')
            .then((r)=>r.json())
            .then((data)=>setCosts(data))

        fetch('http://localhost:9292/activities')
            .then((r)=>r.json())
            .then((data)=>setActivities(data))
    }

This actually isn't too bad considering it seems ridiculous to do in multiple fetch requests. It wasn't until the next day the idea occurred to me to modify the return data from the "DELETE" request, and update the activities and costs on the backend. If I returned a hash from the delete request I could cleanly update both cost and activities states. My code simplified to:

in ruby:

delete '/activities/:id' do
    activity = Activity.find(params[:id])
    activity.costs.each {|cost| cost.destroy}

    start = activity.order - 1
    Activity.all.sort_order[start..].each do |act|
      act.update(order: act.order-1)
    end
    activity.destroy

    res = {'activities': Activity.all, 'costs': Cost.all}
    res.to_json
end

and in js:

function handleDelete(id) {
        fetch(`http://localhost:9292/activities/${id}`,{
            method: "DELETE",
            headers: {
                'Content-Type': 'application/json'
            }
        })
            .then((r)=>r.json())
            .then((obj)=> {
                setActivities(obj.activities)
                setCosts(obj.costs)
            })
        setToggleInfo(!toggleInfo)
    }

Maybe not a drastic change in number of lines of code, but the logic makes so much more sense now. It then gave me another idea.

I had completed the "PATCH" request feature a couple days before... and it was a complete mess.

On the server-side, I had essentially nothing. My "PATCH" request would update the order and return the newly update activity object. I would then have to do another "PATCH" to update the order on the activity it was swapping with. All while keeping the state properly updated.

So, instead of that mess of code, I was going to update the order of both activities from the server side, and then send all the activities so all orders would be kept up to date. Using ActiveRecord, I could make these same changes with less code, and less work updating state on the frontend. I was able to simplify my code to this:

in ruby:

patch "/activities/:id" do
    activity = Activity.find(params[:id])
    swap_activity = Activity.find_by(order: params[:order])
    difference = params[:order] - activity.order

    unless params[:order] < 1 || params[:order] > Activity.all.length
      activity.update(order: params[:order])
      swap_activity.update(order: swap_activity.order - difference)
    end
    activities = Activity.all
    activities.to_json
  end

and in js:

 function handleOrderChange(e, order, id) {
        e.preventDefault()
        const newOrder = parseInt(order)+parseInt(e.target.value)
        fetch(`http://localhost:9292/activities/${id}`, {
            method: "PATCH",
            headers: {
                'Content-Type': 'application/json',
            },
            body: JSON.stringify({order:newOrder}),
        })
            .then((r)=>r.json())
            .then((activities)=> {
                setCurrentActivity(activities.find((activity)=> activity.id===id))
                setActivities(activities)
            })
    }

Between ruby and js its about half as many lines of code as before!

Improving Upon My React Application "Postr"

bperez3237 — Fri, 06 May 2022 03:20:23 +0000

Tasked with creating a React application that features client-side routing, I wanted to mimic a typical social media style news feed. As an avid Twitter user, you'll see this is heavily "inspired" by twitter's format.

My goal (within the confines and time constraints of this project) was to get down the basic login and post features. Therefore, there are so many possibilities for additional features that could be added.

Firstly, I wanted to focus on features I could've added given my current skillset. Since I was only required to use GET and POST, I opted not to use PATCH and DELETE requests. My component structure is as follows:

App
└───Login
or
├───NavBar
└───Home
    └───Post
├───Account
└───UserList

Once simple way to incorporate PATCH and DELETE requests would be like buttons and delete buttons on each individual post. Since I would put these two buttons onto my Post component, I think it makes sense to code these two requests within the Post component.

In addition to interacting with the server, I need to update my feed state variable in my App and Home components. Given my current destructured props in my Post Component:

I would need to add feed and setFeed as props to Post. This would also allow me to add more stats to the Account page such as # of like or # of deletes.

Currently there is not much info on the Account page, but if this application required a real login, password, email, cell-phone, etc. this would be the location for it.

Lastly, the third component UserList could be modified to search posts instead of usernames. This is the code for my current UserList component:

It takes in the feed variable from App and uses it to get a non-duplicated array of usernames. I could also switch this to search for posts instead of usernames.

Simply by switching the conditional in the if statement, i can check the text of the posts (turns out to be easier the way I originally did it):

const matchingPosts = feed.map((post)=> {
        if (post.text.includes(search)) {
            return <li key={post.id}>{post.text}</li>
        }
    })

I could also return a Post component and it might look more like a real application. There could be a toggle for filtering by username or post content. CSS could use some work. Lots and lots of options.

Theres still endless possibilities this could be taken but I think for now, I'm content with where its at.

Story-time: Tackling No Double Favorite Cat

bperez3237 — Wed, 13 Apr 2022 02:01:20 +0000

I wanna talk about a feature I struggled with implementing on my recent project to use a public API to create a single page application.

So the idea for my project was to create an application to help someone pick their ideal cat to "adopt". The user could search from a list of cat breeds available on the https://thecatapi.com/, and the search would return a few bullet points of information. If the user likes a cat, they can "favorite" that breed, and the image and name will be stored in the favorites div while the user searches more cat breeds.

Before diving into the coding, I thought the difficult part of this project would have been the DOM manipulation combined with the asynchronous behavior of the GET request. But surprisingly... I found myself stuck trying to perfect the "favoriting" feature for a good chunk of time.

So this is a section of what the basic application looks like after searching a cat breed:

If the favorite button is clicked, a small image with the breed name underneath would appear in the empty favorites div. The only constraints I wanted were:

Maximum 3 favorites
Can't favorite one breed twice

Ok... easy enough. First I tackled doing maximum 3 favorites. This would be called within a function handling the "favorite!" click event listener. newFavorite is an html div element with the cats image and breed name inside:

function checkFavorites(newFavorite) {
    const favorites = document.getElementById('favorites')

    if (favorites.children.length === 3) {
        favorites.removeChild(favorites.children[0])
        favorites.appendChild(newFavorite)
    }
    else {
        favorites.appendChild(newFavorite)
    }

I test ran it, and it worked fine. Simple, great! Ok-- next part.

The first problem I had: how was I going to check if the newFavorite cat breed I was passing into my function was already favorited? The favorites are all added into the "favorites" div using appendChild(newFavorite). This is the HTML after one cat breed is added to favorites:

So I could check all the children of the "favorites" div element. Probably easiest to iterate through favorites.children and check if the breed name in newFavorites matches a child of favorites. If I was to code this by itself, without the 3 cat limit, this is what I came up with:

function checkFavorites(newFavorite) {
    const favorites = document.getElementById('favorites')
    const currentBreedName = newFavorite.getElementsByTagName('p')[0].textContent

    let checker = true
    for (i=0;i < favorites.children.length ;i++) {
        if (favorites.children[i].getElementsByTagName('p')[0].textContent === currentBreedName) {
            checker = false
        }
    }

    if (checker === true) {
        favorites.appendChild(newFavorite)
    }
    else {
        alert('AREADY IN FAVORITES')
    }

The for loop iterates through all the current children of favorites and uses an if statement to check if the name matches the one I am trying to add. I can't use appendChild within my for loop, or else I could append more than once. So I create a 'checker' variable and use a second if statement to action the appendChild after the for loop is done.

Took me a little bit to figure that one out, but sure enough, this code works by itself. Next step would be to combine it with the code I made to limit to 3 favorites. If combined in the right order, this should work right?

I'm not exactly sure why I decided to cap it at 3 cats or even at all. Maybe to help the user with decision paralysis or make it aesthetically pleasing or... Anyways...

If I copy paste my first code block and put it right after my second code block... it doesn't work. If you favorite the same breed twice, it will alert you it is "ALREADY IN FAVORITES", but when it passes the second code block, since it hasn't reached 3 children, it appends the double-favorited cat.

And vice versa: if I order the two code blocks the other way, I get the same behavior. So still not working. Maybe if I put code block 1 in the middle of code block 2? Code block 1 inside the for loop of code block 2? A nested if statement within a for loop within an if statement???

I wasn't expecting this to stump me but I was little low on morale at the time. Maybe I should change my constraints to make the coding easier?

Somehow-- it eventually came to me. If I combined the two if statements using &&, I could get the elements appended/removed in the right order to get the behavior I was looking for. And much easier to read than some of the other options I was thinking of trying.

This is the final version of the function:

function checkFavorites(newFavorite) {
    let favorites = document.getElementById('favorites')
    let currentBreedName = newFavorite.getElementsByTagName('p')[0].textContent

    let checker = true
    for (i=0; i < favorites.children.length ;i++) {
        if (favorites.children[i].getElementsByTagName('p')[0].textContent === currentBreedName) {
            checker = false
        }
    }
    if (favorites.children.length != 3 && checker === true) {
        favorites.appendChild(newFavorite)
    }
    else if (favorites.children.length === 3 && checker === true) {
        favorites.removeChild(favs.children[0])
        favorites.appendChild(newFavorite)
    }
    else {
        alert('AREADY IN FAVORITES')
    }
}

Combining (favorites.children.length != 3) boolean with (checker === true) allows me to appendChild only once, and making sure I am following both my constraints at once. Alternatively, I could have done the same thing with else if statements, but I think it would have affected the readability.

Simple enough-- I thought. Anyone have a better solution?