Autifit excel column width
Parag Verma
8th July, 2022
Introduction
Not all columns in the excel are of equal width and there are situations where we have to manually set the column width so that the details are not hidden.In this blog we will look at how we can set the column width using openxlsx library
package.name<-c("dplyr","openxlsx")
for(i in package.name){
if(!require(i,character.only = T)){
install.packages(i)
}
library(i,character.only = T)
}Step 1: Import the iris dataset
data(iris)
head(iris) Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1 5.1 3.5 1.4 0.2 setosa
2 4.9 3.0 1.4 0.2 setosa
3 4.7 3.2 1.3 0.2 setosa
4 4.6 3.1 1.5 0.2 setosa
5 5.0 3.6 1.4 0.2 setosa
6 5.4 3.9 1.7 0.4 setosairis$Species<-as.character(iris$Species)Step 2: Adding a lengthy string
iris[1,"Species"]<-"Dummy_Value_For_Testing_Purpose"
head(iris) Sepal.Length Sepal.Width Petal.Length Petal.Width
1 5.1 3.5 1.4 0.2
2 4.9 3.0 1.4 0.2
3 4.7 3.2 1.3 0.2
4 4.6 3.1 1.5 0.2
5 5.0 3.6 1.4 0.2
6 5.4 3.9 1.7 0.4
Species
1 Dummy_Value_For_Testing_Purpose
2 setosa
3 setosa
4 setosa
5 setosa
6 setosaStep 3: Writing the file without adjusting the column width
write.xlsx(iris, "iris_data1.xlsx")Step 4: Checking Output excel file
We can see that the first row for the Species column overflows into the next column.Hence we need to adjust the column width before wiriting the data into an excel file.
Step 5: Adjusting the column width
write.xlsx(iris, "iris_data2.xlsx",colWidths = rep("auto",dim(iris)[2]))Step 6: Checking Output excel file
Now the column width of Species column has been adjusted to accommodate the maximum length value in the column.
In the next few blogs, we will look at how to format values within an excel file.
