Add Comments to Excel with RStudio and openxlsx package [4k]

welcome back to cradle to grave r my name is mark gingras today we're going to talk about how to add comments to an excel file through using r and open xlsx package but we're also going to add styles and multiple styles with one comment so it's really kind of a uh a cool little technique to use because i don't even think you can do this within excel very easily without using some vba so that being said let's try it out let's check it out follow along if you haven't um already looked at the previous open xls xlsx tutorials there are about five or six right at this point so please just check that playlist out and start from the beginning so we're going to begin for us we're going to start with opening up the library open xlsx we're going to run that and then we'll create our workbook like we always do right just what we do is we create a workbook name that's going to be meaningful like my daily report would be meaningful hey this is what i produce every day it's a workbook that i'm going to try to upload to the to the sharepoint or whatever you got to do so now it's simple as create workbook create capital workbook and then that workbook object has been created once you've created that workbook we have to add a worksheet so we're just gonna do um the function add worksheet and then add it to what my daily report workbook my you can have multiple workbooks at the same time you can have tons and tons of them if you'd like we'll call it um sales so our daily sales data gets put into this worksheet called sales that's what we're pretending and it's as simple as just writing a comment like comment one we're gonna create a variable we're gonna call it uh we're gonna call the function called create comment so it's a little different than just writing data to a cell so just pay attention to like the differences so create comment and then we just put in our comment say this is our comment it's as simple as that but now we've created a basically a comment object that we're going to insert into our worksheet that's in our workbook right so to speak so now we just have to write that comment so we can do write comment and then when we write the comment we just tell it which workbook to write it to we want it in our daily d a uh oh my daily my daily report um you have to specify the sheet name which would be sheet equals sales and then it's asking me for my column in row so maybe i want it in column one row let's go row ten just for fun and then we'll add the comment comment one right so ran that no errors now let's just double check so in order to do that we have to actually save the workbook yeah the workbook's in memory but it's not really visible or usable outside of r so let's let's just save that workbook so save workbook and which workbook the daily uh report that's what i think my daily report my daily report simple as that and we'll just call it i think file equals daily report for you dot xlsx let's just try this check it out see what it looks like so let's get the working directory here get wd okay i'm under documents test so let me just load that up uh documents test i'll bring it over so i have daily report for you that's just been created let's open that up and we see a comment here in uh a10 which is the first column tenth row and there's the comment now it's visible it's showing so that's pretty cool that's exactly what i wanted now within excel i suppose you can do bold for part of it and you can change the size and colors so that's cool i didn't even know you can do that honestly and there's probably a way to make it so your comments are not visible if you don't want them to be i'm not 100 sure yet uh we'll play with that some other time but for now let's just create a couple of new styles so i'm going to keep the save workbook down below and we'll just create two styles style one we'll just do create style we can create styles for anything like the font font size let's make it uh 14 and then there's font color font color equals and we'll just call it i think lowercase blue let's add one more attribute or parameter um there's something called text decoration that's like your bold in fact let me redo that so that that yellow thing pops up text decoration now i let it go bold strikeout italic underline and underline two so those are your options so let's just create it and make it bold um and you can actually do multiple i think so we have to put it in a vector format so we'll just do um i think lowercase bold would do it and then close the parentheses actually it's uppercase bold because that's what the actual directions just said oops close the other parentheses and there's our style one let's create another style while we're here so create style again and we will do let's do a smaller font size equals let's do 10 and then we'll do just a font color equals green on this one now i'm showing you this because i'm going to separate out and have multiple styles within the same comment all right so in order to do that we have to write into the comments we can do write comment again and this time we want to write the comment to yes our my daily report i'm going to write this comment in row or column 4 row 6. it doesn't really matter make it up of course you'd want to do it programmatically to where you exactly want this comment think about programming and how you can make this foolproof and scalable and if you add rows or how do you keep track of that you have to think about all that stuff we're doing this as an example to get you started all right so we're just going to add a comment and it's going to be um ah we didn't create the second comment yet so before we do that let's create it so we have comment one on line seven let's create comment two using these two styles that would make more sense so okay let's jump back to [Music] uh we'll just call it c2 and we'll do create comment again create comment and this time what we're going to do is we're going to say the comment is equal to and we're going to make it equal to multiple things we're going to make it equal to a vector of comments right so c and then we put another set of parentheses and we're going to call this this is the first style s-t-i-l-e then we can do some backslashes put some spaces in there for like a return care character and then we'll say this is our second style and i will put this on a new line right here just to be a little bit more aesthetically pleasing to the eyeballs and so we've created the comment now that we have comment c2 which is a a vector of comments which is fine we can actually specify when we write the comment and i don't know why there's so much emphasis on making sure the comments have all this flexibility but if you can do it in excel you should be able to do it in open xlsx correct so let's do it so then we just say comment is equal to uh c2 and that should write this workbook only has one sheet ah forgot the sheet so comma and we can do sheet equals sales again we only have one sheet sales now we're good now i can rewrite this to uh to hard disk space so save that file already exists uh overwrite equals true remember that one so comma i could do over i think overwrite equals true that should do the trick run that error uh it's in spanish sabinci fuera de los limites hmm well i guess i shouldn't have put my uh computer on spanish that would not make it easy for this save workbook file equals oh if you add multiple comments you have to add multiple styles to it so comma and then you would put styles equal to and then another vector of styles style 1 and style 2. that makes sense all right sorry about that little hiccup and i'm sorry that it's in spanish unless you speak spanish then i'm not sorry ah so i ran the whole thing just now and it did work so it was definitely that i'm sorry that i had that little hiccup let's let's check to make sure this works all right this is the first style this is the first style it looks like oh and there's a second style it just didn't show up till i clicked on it so because this is not big enough so we have a little bit of an issue but you get the idea um i can zoom in on this so that you can see it so you get the idea but um i don't know how to make those boxes different sizes i have no idea how to do that yet so but this will get you started and i think that's about it for this tutorial um i will revert back to english on my keyboard in english i hate to do that because i'm trying to learn spanish and it really helps but i didn't know that the actual error report will come back in spanish so that being said hey guys i appreciate all you guys subscribing and um commenting and some of you are joining the discord i wish that i could help a lot more but time is limited so please help each other out the our community is so big and you guys definitely know that the resources are out there you know where to find them keep uh keep me in in mind when you're sharing this on social media uh i appreciate all that thanks [Music] you

Channel: CradleToGraveR

Views: 316

Rating: 5 out of 5

Keywords: openxlsx, excel, microsoft excel, rstudio and excel, r and excel, rprogramming, r tutorials, how to use excel through R, cradletograver, mark gingrass, gingrass

Id: JVeIVk8hymM

Length: 10min 47sec (647 seconds)

Published: Mon Sep 07 2020