# Library 
library(ggplot2)
library(openxlsx) #install.packages("openxlsx")
library(gridExtra)  #install.packages("gridExtra")

# File location
path <- "myfile.xlsx"

# Getting data from sheets
sheets <- openxlsx::getSheetNames(path)
data_frame <- lapply(sheets, openxlsx::read.xlsx, xlsxFile = path)

# Assigning names to data frame
names(data_frame) <- sheets
#data_frame

# Define variables for each country
Bangladesh <- as.data.frame(data_frame['bd'])
Nepal <- as.data.frame(data_frame['np'])
SriLanka <- as.data.frame(data_frame['sl'])
Bhutan <- as.data.frame(data_frame['bh'])
Laos <- as.data.frame(data_frame['ls'])
PNG <- as.data.frame(data_frame['pn'])
TL <- as.data.frame(data_frame['tl'])


# Bangladesh Stacked plot
bd_chart <- ggplot(Bangladesh, aes(x = bd.year, y = bd.value, fill = bd.type)) + 
  ggtitle("Bangladesh") + xlab("Years") + ylab("Percentage of data volume (%)") +
  theme(legend.position="bottom", legend.title = element_blank()) + geom_col()

# Nepal Stacked plot
np_chart <- ggplot(Nepal, aes(x = np.year, y = np.value, fill = np.type)) + 
  ggtitle("Nepal") + xlab("Years") + ylab("Percentage of data volume (%)") +
  theme(legend.position="bottom", legend.title = element_blank()) + geom_col()

# Sri Lanka Stacked plot
sl_chart <- ggplot(SriLanka, aes(x = sl.year, y = sl.value, fill = sl.type)) + 
  ggtitle("Sri Lanka") + xlab("Years") + ylab("Percentage of data volume (%)") +
  theme(legend.position="bottom", legend.title = element_blank()) + geom_col()


# Bhutan Stacked plot
bht_chart <- ggplot(Bhutan, aes(x = bh.year, y = bh.value, fill = bh.type)) + 
  ggtitle("Bhutan") + xlab("Years") + ylab("Percentage of data volume (%)") +
  theme(legend.position="bottom", legend.title = element_blank()) + geom_col()

# Laos Stacked plot
laos_chart <- ggplot(Laos, aes(x = ls.year, y = ls.value, fill = ls.type)) + 
  ggtitle("Laos") + xlab("Years") + ylab("Percentage of data volume (%)") +
  theme(legend.position="bottom", legend.title = element_blank()) + geom_col()

# PNG Stacked plot
png_chart <- ggplot(PNG, aes(x = pn.year, y = pn.value, fill = pn.type)) + 
  ggtitle("PNG") + xlab("Years") + ylab("Percentage of data volume (%)") +
  theme(legend.position="bottom", legend.title = element_blank()) + geom_col()

# TL Stacked plot
tl_chart <- ggplot(TL, aes(x = tl.year, y = tl.value, fill = tl.type)) + 
  ggtitle("TL") + xlab("Years") + ylab("Percentage of data volume (%)") +
  theme(legend.position="bottom", legend.title = element_blank()) + geom_col()

grid.arrange(bd_chart, np_chart, sl_chart, bht_chart, laos_chart, png_chart, tl_chart, ncol= 4, nrow = 2)