Sending ouput to Excel from R

Introduction

I will review some features available to send output from R (dataframes) to Excel. We may want to do it because we feel more comfortable filtering or sorting data in excel, because we need to provide the output in excel to end users or as an input to other processes. There is not so much material available, compared to reading excel files. The overview is not exhaustive but restricted to some practical examples which we have handled in Regacc.

Packages

There are a few packages available but we will focus on {writexl} and {openxlsx}. {writexls} is the preferred option for exporting un-formatted data as it is faster and creates smaller files. We will use {openxlsx} if we want some customization.

## @Manual{R-openxlsx,
##   title = {openxlsx: Read, Write and Edit xlsx Files},
##   author = {Philipp Schauberger and Alexander Walker},
##   year = {2021},
##   note = {R package version 4.2.5},
##   url = {https://CRAN.R-project.org/package=openxlsx},
## }
## 
## @Manual{R-writexl,
##   title = {writexl: Export Data Frames to Excel xlsx
##     Format},
##   author = {Jeroen Ooms},
##   year = {2021},
##   note = {R package version 1.4.0},
##   url = {https://CRAN.R-project.org/package=writexl},
## }

Simple export

Exporting to excel a dataframe. We will use a csv file of eurobase table nama_10r_2gdp as the starting point of the example.

library(tidyverse)
library(writexl)

df<- rio::import("data/gdp2v.csv") %>% 
  mutate(country = str_sub(geo,1,2), 
       NUTS= as.factor(str_length(geo)-2)) %>% 
  filter(country == "ES" & unit %in% c("EUR_HAB", "PPS_HAB_EU27_2020")) %>% 
  select(country,geo,NUTS,unit,obs_value) 

writexl::write_xlsx(df, "output/gdp2.xlsx")

Creating a temporary file

For day to day use we can create an un-named temporary file. The best is to create a function that we can add to our rstudio snippets and call it when needed instead of typing it every time we need to do it. Taken from https://twitter.com/brodriguesco/status/1447468259725434886

show_in_excel <- function(.data){
  tmp <- paste0(tempfile(), ".xlsx")
  writexl::write_xlsx(.data,tmp)
  browseURL(tmp)
}

show_in_excel(df)

Exporting as an excel table

If we want to avoid having to manually filter the first row in Excel after opening the file for sorting/filtering we can export directly the dataframe as an Excel table.

library(openxlsx)

write.xlsx(df, "output/gdp2.xlsx", overwrite = TRUE, asTable = TRUE)

Further customisation options

If we want to customise more the output, add a name to the worksheet, table style, font, number format, colours, etc. there are endless possibilities (https://cran.r-project.org/web/packages/openxlsx/vignettes/Formatting.html).

wb <- createWorkbook() 
    modifyBaseFont(wb, fontSize = 12, fontName = "Calibri Light")
    options(openxlsx.numFmt = "#,##0.00")
    addWorksheet(wb, "GDP_ES")
    writeDataTable(wb, "GDP_ES", df, tableStyle = "TableStyleMedium13")
    
saveWorkbook(wb,"output/gdp2.xlsx", overwrite = TRUE)

Modifying an existing file

We can open an existing file and further specify where to place the data, insert plots, etc.

Here we calculate the mean by region and create a chart showing the mean as a bar and the observations as points. We will place the table with the mean in a worksheet called Summary on the third row and column and the chart in a Woeksheet named Chart with some defined height and width.

temp<- df %>% 
  group_by(geo,unit,NUTS) %>% 
  summarise(avg = round(mean(obs_value, na.rm = TRUE)))

p<- ggplot()+
  geom_col(data=temp %>% filter(NUTS!=1),aes(avg, reorder(geo,avg), fill= NUTS))+
  geom_point(data=df%>% filter(NUTS!=1), aes(obs_value,geo))+
  facet_wrap(~unit, scales = "free_x")+
  scale_x_continuous(scales::pretty_breaks(n=4), labels = scales::label_number())+
  ggthemes::theme_fivethirtyeight()+
  ggthemes::scale_fill_tableau()+
  theme(legend.position = "none")

wb <- loadWorkbook("output/gdp2.xlsx")
addWorksheet(wb, "Summary")
writeDataTable(wb, sheet="Summary", temp, startCol = 3, startRow = 3,tableStyle = "TableStyleLight13")
 print(p)

 addWorksheet(wb, "Chart")
wb %>% insertPlot(sheet="Chart",startCol =1, startRow=1,width=12,height = 9, dpi=600)
saveWorkbook(wb, "output/gdp2_new.xlsx", overwrite = TRUE)

Split the dataframe and write each group to a different worksheet

Sometimes we may want to split a big dataframe into several worksheets using the groups of a specific column. This is helpful to organise the output and avoid the number of rows limit of 1.000.000.

df_unit <- split(df,df$unit)
write.xlsx(df_unit, file = "output/gdp2_unit.xlsx", overwrite = TRUE)

Creating a custom function

We can create a custom function, write_countries with a single argument (x which is the country) that would create the same file. We just need to add a filter to the dataset with the variable that also will be used for naming the files and worksheets. We do that using the base function paste0.

write_countries <- function(x){
  
  temp<-df %>% 
    filter(country == x)
  
  summ<- temp %>%
  group_by(geo,unit,NUTS) %>% 
  summarise(avg = round(mean(obs_value, na.rm = TRUE)))

p<- ggplot()+
  geom_col(data=summ %>% filter(NUTS!=1),aes(avg, reorder(geo,avg), fill= NUTS))+
  geom_point(data=temp %>% filter(NUTS!=1), aes(obs_value,geo))+
  facet_wrap(~unit, scales = "free_x")+
  scale_x_continuous(scales::pretty_breaks(n=4), labels = scales::label_number())+
  ggthemes::theme_fivethirtyeight()+
  ggthemes::scale_fill_tableau()+
  theme(legend.position = "none")

  wb <- createWorkbook() 
    modifyBaseFont(wb, fontSize = 12, fontName = "Calibri Light")
    options(openxlsx.numFmt = "#,##0.00")
    addWorksheet(wb, paste0("GDP_",x))
    writeDataTable(wb, paste0("GDP_",x), temp, tableStyle = "TableStyleMedium13")
    addWorksheet(wb, "Summary")
    writeDataTable(wb, sheet="Summary", summ, startCol = 3, startRow = 3,tableStyle = "TableStyleLight13")
     print(p)
 addWorksheet(wb, "Chart")
wb %>% insertPlot(sheet="Chart",startCol =1, startRow=1,width=12,height = 9, dpi=600)

saveWorkbook(wb,paste0("output/",x,"_gdp2.xlsx"), overwrite = TRUE)}

write_countries("ES")

Iterating the creation of files

Finally, we can automatise the creation of the files by creating a list of countries and applying the function to each element of the list. The use the function walk from the package {purrr} to do that.

df<- rio::import("data/gdp2v.csv") %>% 
  mutate(country = str_sub(geo,1,2), 
       NUTS= as.factor(str_length(geo)-2)) %>% 
  select(country,geo,NUTS,unit,obs_value) %>% 
  filter(unit %in% c("EUR_HAB", "PPS_HAB_EU27_2020"))

list_countries<- c("NL","SK","BE", "AT", "CZ")

walk(list_countries, write_countries)

LS0tDQp0aXRsZTogIlNlbmRpbmcgb3VwdXQgdG8gRXhjZWwgZnJvbSBSIg0KZGF0ZTogIkphbnVhcnkgMjAyMiINCmF1dGhvcjogTHVpcyBCaWVkbWENCm91dHB1dDoNCiAgcm1kZm9ybWF0czo6cmVhZHRoZWRvd246DQogICAgaGlnaGxpZ2h0OiBrYXRlDQogICAgY29kZV9kb3dubG9hZDogdHJ1ZQ0KLS0tDQpgYGB7ciBzZXR1cCwgaW5jbHVkZSA9IEZBTFNFfQ0KIyMgR2xvYmFsIG9wdGlvbnMNCmtuaXRyOjpvcHRzX2NodW5rJHNldCgNCgltZXNzYWdlID0gRkFMU0UsDQoJd2FybmluZyA9IEZBTFNFLA0KCWNhY2hlID0gRkFMU0UsDQoJZmlnLmhlaWdodD05LCANCglmaWcud2lkdGg9MTINCikNCmBgYA0KDQojIyBJbnRyb2R1Y3Rpb24gDQoNCkkgd2lsbCByZXZpZXcgc29tZSBmZWF0dXJlcyBhdmFpbGFibGUgdG8gc2VuZCBvdXRwdXQgZnJvbSBSIChkYXRhZnJhbWVzKSB0byBFeGNlbC4gV2UgbWF5IHdhbnQgdG8gZG8gaXQgYmVjYXVzZSB3ZSBmZWVsIG1vcmUgY29tZm9ydGFibGUgZmlsdGVyaW5nIG9yIHNvcnRpbmcgZGF0YSBpbiBleGNlbCwgYmVjYXVzZSB3ZSBuZWVkIHRvIHByb3ZpZGUgdGhlIG91dHB1dCBpbiBleGNlbCB0byBlbmQgdXNlcnMgb3IgYXMgYW4gaW5wdXQgdG8gb3RoZXIgcHJvY2Vzc2VzLiBUaGVyZSBpcyBub3Qgc28gbXVjaCBtYXRlcmlhbCBhdmFpbGFibGUsIGNvbXBhcmVkIHRvIHJlYWRpbmcgZXhjZWwgZmlsZXMuIFRoZSBvdmVydmlldyBpcyBub3QgZXhoYXVzdGl2ZSBidXQgcmVzdHJpY3RlZCB0byBzb21lIHByYWN0aWNhbCBleGFtcGxlcyB3aGljaCB3ZSBoYXZlIGhhbmRsZWQgaW4gUmVnYWNjLg0KDQojIyBQYWNrYWdlcw0KDQpUaGVyZSBhcmUgYSBmZXcgcGFja2FnZXMgYXZhaWxhYmxlIGJ1dCB3ZSB3aWxsICBmb2N1cyBvbiBge3dyaXRleGx9YCBhbmQgYHtvcGVueGxzeH1gLiBgIHt3cml0ZXhsc31gIGlzIHRoZSBwcmVmZXJyZWQgb3B0aW9uIGZvciBleHBvcnRpbmcgdW4tZm9ybWF0dGVkIGRhdGEgYXMgaXQgaXMgZmFzdGVyIGFuZCBjcmVhdGVzIHNtYWxsZXIgZmlsZXMuIFdlIHdpbGwgdXNlIGB7b3Blbnhsc3h9YCBpZiB3ZSB3YW50IHNvbWUgY3VzdG9taXphdGlvbi4NCg0KYGBge3IgZWNobz1GQUxTRX0NCmtuaXRyOjp3cml0ZV9iaWIoYygid3JpdGV4bCIsICJvcGVueGxzeCIpLCB3aWR0aCA9IDYwKQ0KYGBgDQoNCg0KIyMgU2ltcGxlIGV4cG9ydA0KDQpFeHBvcnRpbmcgdG8gZXhjZWwgYSBkYXRhZnJhbWUuIFdlIHdpbGwgdXNlIGEgY3N2IGZpbGUgb2YgZXVyb2Jhc2UgdGFibGUgbmFtYV8xMHJfMmdkcCBhcyB0aGUgc3RhcnRpbmcgcG9pbnQgb2YgdGhlIGV4YW1wbGUuDQoNCmBgYHtyfQ0KbGlicmFyeSh0aWR5dmVyc2UpDQpsaWJyYXJ5KHdyaXRleGwpDQoNCmRmPC0gcmlvOjppbXBvcnQoImRhdGEvZ2RwMnYuY3N2IikgJT4lIA0KICBtdXRhdGUoY291bnRyeSA9IHN0cl9zdWIoZ2VvLDEsMiksIA0KICAgICAgIE5VVFM9IGFzLmZhY3RvcihzdHJfbGVuZ3RoKGdlbyktMikpICU+JSANCiAgZmlsdGVyKGNvdW50cnkgPT0gIkVTIiAmIHVuaXQgJWluJSBjKCJFVVJfSEFCIiwgIlBQU19IQUJfRVUyN18yMDIwIikpICU+JSANCiAgc2VsZWN0KGNvdW50cnksZ2VvLE5VVFMsdW5pdCxvYnNfdmFsdWUpIA0KDQp3cml0ZXhsOjp3cml0ZV94bHN4KGRmLCAib3V0cHV0L2dkcDIueGxzeCIpDQpgYGANCg0KIyMgQ3JlYXRpbmcgYSB0ZW1wb3JhcnkgZmlsZQ0KDQpGb3IgZGF5IHRvIGRheSB1c2Ugd2UgY2FuIGNyZWF0ZSBhbiB1bi1uYW1lZCB0ZW1wb3JhcnkgZmlsZS4gVGhlIGJlc3QgaXMgdG8gY3JlYXRlIGEgZnVuY3Rpb24gdGhhdCB3ZSBjYW4gYWRkIHRvIG91ciByc3R1ZGlvIHNuaXBwZXRzIGFuZCBjYWxsIGl0IHdoZW4gbmVlZGVkIGluc3RlYWQgb2YgdHlwaW5nIGl0IGV2ZXJ5IHRpbWUgd2UgbmVlZCB0byBkbyBpdC4gVGFrZW4gZnJvbSA8aHR0cHM6Ly90d2l0dGVyLmNvbS9icm9kcmlndWVzY28vc3RhdHVzLzE0NDc0NjgyNTk3MjU0MzQ4ODY+DQoNCmBgYHtyfQ0Kc2hvd19pbl9leGNlbCA8LSBmdW5jdGlvbiguZGF0YSl7DQogIHRtcCA8LSBwYXN0ZTAodGVtcGZpbGUoKSwgIi54bHN4IikNCiAgd3JpdGV4bDo6d3JpdGVfeGxzeCguZGF0YSx0bXApDQogIGJyb3dzZVVSTCh0bXApDQp9DQoNCnNob3dfaW5fZXhjZWwoZGYpDQpgYGANCg0KIyMgRXhwb3J0aW5nIGFzIGFuIGV4Y2VsIHRhYmxlDQoNCklmIHdlIHdhbnQgdG8gYXZvaWQgaGF2aW5nIHRvIG1hbnVhbGx5IGZpbHRlciB0aGUgZmlyc3Qgcm93IGluIEV4Y2VsIGFmdGVyIG9wZW5pbmcgdGhlIGZpbGUgZm9yIHNvcnRpbmcvZmlsdGVyaW5nIHdlIGNhbiBleHBvcnQgZGlyZWN0bHkgdGhlIGRhdGFmcmFtZSBhcyBhbiBFeGNlbCB0YWJsZS4NCg0KYGBge3J9DQpsaWJyYXJ5KG9wZW54bHN4KQ0KDQp3cml0ZS54bHN4KGRmLCAib3V0cHV0L2dkcDIueGxzeCIsIG92ZXJ3cml0ZSA9IFRSVUUsIGFzVGFibGUgPSBUUlVFKQ0KYGBgDQoNCiMjIEZ1cnRoZXIgY3VzdG9taXNhdGlvbiBvcHRpb25zDQoNCklmIHdlIHdhbnQgdG8gY3VzdG9taXNlIG1vcmUgdGhlIG91dHB1dCwgYWRkIGEgbmFtZSB0byB0aGUgd29ya3NoZWV0LCB0YWJsZSBzdHlsZSwgZm9udCwgbnVtYmVyIGZvcm1hdCwgY29sb3VycywgZXRjLiB0aGVyZSBhcmUgZW5kbGVzcyBwb3NzaWJpbGl0aWVzICg8aHR0cHM6Ly9jcmFuLnItcHJvamVjdC5vcmcvd2ViL3BhY2thZ2VzL29wZW54bHN4L3ZpZ25ldHRlcy9Gb3JtYXR0aW5nLmh0bWw+KS4NCg0KYGBge3J9DQp3YiA8LSBjcmVhdGVXb3JrYm9vaygpIA0KICAgIG1vZGlmeUJhc2VGb250KHdiLCBmb250U2l6ZSA9IDEyLCBmb250TmFtZSA9ICJDYWxpYnJpIExpZ2h0IikNCiAgICBvcHRpb25zKG9wZW54bHN4Lm51bUZtdCA9ICIjLCMjMC4wMCIpDQogICAgYWRkV29ya3NoZWV0KHdiLCAiR0RQX0VTIikNCiAgICB3cml0ZURhdGFUYWJsZSh3YiwgIkdEUF9FUyIsIGRmLCB0YWJsZVN0eWxlID0gIlRhYmxlU3R5bGVNZWRpdW0xMyIpDQogICAgDQpzYXZlV29ya2Jvb2sod2IsIm91dHB1dC9nZHAyLnhsc3giLCBvdmVyd3JpdGUgPSBUUlVFKQ0KYGBgDQoNCiMjIE1vZGlmeWluZyBhbiBleGlzdGluZyBmaWxlDQoNCldlIGNhbiBvcGVuIGFuIGV4aXN0aW5nIGZpbGUgYW5kIGZ1cnRoZXIgc3BlY2lmeSB3aGVyZSB0byBwbGFjZSB0aGUgZGF0YSwgaW5zZXJ0IHBsb3RzLCBldGMuDQoNCkhlcmUgd2UgY2FsY3VsYXRlIHRoZSBtZWFuIGJ5IHJlZ2lvbiBhbmQgY3JlYXRlIGEgY2hhcnQgc2hvd2luZyB0aGUgbWVhbiBhcyBhIGJhciBhbmQgdGhlIG9ic2VydmF0aW9ucyBhcyBwb2ludHMuIFdlIHdpbGwgcGxhY2UgdGhlIHRhYmxlIHdpdGggdGhlIG1lYW4gaW4gYSB3b3Jrc2hlZXQgY2FsbGVkICpTdW1tYXJ5KiBvbiB0aGUgdGhpcmQgcm93IGFuZCBjb2x1bW4gYW5kIHRoZSBjaGFydCBpbiBhIFdvZWtzaGVldCBuYW1lZCAqQ2hhcnQqIHdpdGggc29tZSBkZWZpbmVkIGhlaWdodCBhbmQgd2lkdGguIA0KDQpgYGB7cn0NCnRlbXA8LSBkZiAlPiUgDQogIGdyb3VwX2J5KGdlbyx1bml0LE5VVFMpICU+JSANCiAgc3VtbWFyaXNlKGF2ZyA9IHJvdW5kKG1lYW4ob2JzX3ZhbHVlLCBuYS5ybSA9IFRSVUUpKSkNCg0KcDwtIGdncGxvdCgpKw0KICBnZW9tX2NvbChkYXRhPXRlbXAgJT4lIGZpbHRlcihOVVRTIT0xKSxhZXMoYXZnLCByZW9yZGVyKGdlbyxhdmcpLCBmaWxsPSBOVVRTKSkrDQogIGdlb21fcG9pbnQoZGF0YT1kZiU+JSBmaWx0ZXIoTlVUUyE9MSksIGFlcyhvYnNfdmFsdWUsZ2VvKSkrDQogIGZhY2V0X3dyYXAofnVuaXQsIHNjYWxlcyA9ICJmcmVlX3giKSsNCiAgc2NhbGVfeF9jb250aW51b3VzKHNjYWxlczo6cHJldHR5X2JyZWFrcyhuPTQpLCBsYWJlbHMgPSBzY2FsZXM6OmxhYmVsX251bWJlcigpKSsNCiAgZ2d0aGVtZXM6OnRoZW1lX2ZpdmV0aGlydHllaWdodCgpKw0KICBnZ3RoZW1lczo6c2NhbGVfZmlsbF90YWJsZWF1KCkrDQogIHRoZW1lKGxlZ2VuZC5wb3NpdGlvbiA9ICJub25lIikNCg0Kd2IgPC0gbG9hZFdvcmtib29rKCJvdXRwdXQvZ2RwMi54bHN4IikNCmFkZFdvcmtzaGVldCh3YiwgIlN1bW1hcnkiKQ0Kd3JpdGVEYXRhVGFibGUod2IsIHNoZWV0PSJTdW1tYXJ5IiwgdGVtcCwgc3RhcnRDb2wgPSAzLCBzdGFydFJvdyA9IDMsdGFibGVTdHlsZSA9ICJUYWJsZVN0eWxlTGlnaHQxMyIpDQogcHJpbnQocCkNCiBhZGRXb3Jrc2hlZXQod2IsICJDaGFydCIpDQp3YiAlPiUgaW5zZXJ0UGxvdChzaGVldD0iQ2hhcnQiLHN0YXJ0Q29sID0xLCBzdGFydFJvdz0xLHdpZHRoPTEyLGhlaWdodCA9IDksIGRwaT02MDApDQpzYXZlV29ya2Jvb2sod2IsICJvdXRwdXQvZ2RwMl9uZXcueGxzeCIsIG92ZXJ3cml0ZSA9IFRSVUUpDQpgYGANCg0KIyMgU3BsaXQgdGhlIGRhdGFmcmFtZSBhbmQgd3JpdGUgZWFjaCBncm91cCB0byBhIGRpZmZlcmVudCB3b3Jrc2hlZXQNCg0KU29tZXRpbWVzIHdlIG1heSB3YW50IHRvIHNwbGl0IGEgYmlnIGRhdGFmcmFtZSBpbnRvIHNldmVyYWwgd29ya3NoZWV0cyB1c2luZyB0aGUgZ3JvdXBzIG9mIGEgc3BlY2lmaWMgY29sdW1uLiBUaGlzIGlzIGhlbHBmdWwgdG8gb3JnYW5pc2UgdGhlIG91dHB1dCBhbmQgYXZvaWQgdGhlIG51bWJlciBvZiByb3dzIGxpbWl0IG9mIDEuMDAwLjAwMC4NCg0KYGBge3J9DQpkZl91bml0IDwtIHNwbGl0KGRmLGRmJHVuaXQpDQp3cml0ZS54bHN4KGRmX3VuaXQsIGZpbGUgPSAib3V0cHV0L2dkcDJfdW5pdC54bHN4Iiwgb3ZlcndyaXRlID0gVFJVRSkNCmBgYA0KDQojIyBDcmVhdGluZyBhIGN1c3RvbSBmdW5jdGlvbg0KDQpXZSBjYW4gY3JlYXRlIGEgY3VzdG9tIGZ1bmN0aW9uLCBgd3JpdGVfY291bnRyaWVzYCAgd2l0aCBhIHNpbmdsZSBhcmd1bWVudCAoYHhgIHdoaWNoIGlzIHRoZSBjb3VudHJ5KSB0aGF0IHdvdWxkIGNyZWF0ZSB0aGUgc2FtZSBmaWxlLiBXZSBqdXN0IG5lZWQgdG8gYWRkIGEgZmlsdGVyIHRvIHRoZSBkYXRhc2V0IHdpdGggdGhlIHZhcmlhYmxlIHRoYXQgYWxzbyAgd2lsbCBiZSB1c2VkIGZvciBuYW1pbmcgdGhlIGZpbGVzIGFuZCB3b3Jrc2hlZXRzLiBXZSBkbyB0aGF0IHVzaW5nIHRoZSBiYXNlIGZ1bmN0aW9uIGBwYXN0ZTBgLg0KDQpgYGB7cn0NCndyaXRlX2NvdW50cmllcyA8LSBmdW5jdGlvbih4KXsNCiAgDQogIHRlbXA8LWRmICU+JSANCiAgICBmaWx0ZXIoY291bnRyeSA9PSB4KQ0KICANCiAgc3VtbTwtIHRlbXAgJT4lDQogIGdyb3VwX2J5KGdlbyx1bml0LE5VVFMpICU+JSANCiAgc3VtbWFyaXNlKGF2ZyA9IHJvdW5kKG1lYW4ob2JzX3ZhbHVlLCBuYS5ybSA9IFRSVUUpKSkNCg0KcDwtIGdncGxvdCgpKw0KICBnZW9tX2NvbChkYXRhPXN1bW0gJT4lIGZpbHRlcihOVVRTIT0xKSxhZXMoYXZnLCByZW9yZGVyKGdlbyxhdmcpLCBmaWxsPSBOVVRTKSkrDQogIGdlb21fcG9pbnQoZGF0YT10ZW1wICU+JSBmaWx0ZXIoTlVUUyE9MSksIGFlcyhvYnNfdmFsdWUsZ2VvKSkrDQogIGZhY2V0X3dyYXAofnVuaXQsIHNjYWxlcyA9ICJmcmVlX3giKSsNCiAgc2NhbGVfeF9jb250aW51b3VzKHNjYWxlczo6cHJldHR5X2JyZWFrcyhuPTQpLCBsYWJlbHMgPSBzY2FsZXM6OmxhYmVsX251bWJlcigpKSsNCiAgZ2d0aGVtZXM6OnRoZW1lX2ZpdmV0aGlydHllaWdodCgpKw0KICBnZ3RoZW1lczo6c2NhbGVfZmlsbF90YWJsZWF1KCkrDQogIHRoZW1lKGxlZ2VuZC5wb3NpdGlvbiA9ICJub25lIikNCg0KICB3YiA8LSBjcmVhdGVXb3JrYm9vaygpIA0KICAgIG1vZGlmeUJhc2VGb250KHdiLCBmb250U2l6ZSA9IDEyLCBmb250TmFtZSA9ICJDYWxpYnJpIExpZ2h0IikNCiAgICBvcHRpb25zKG9wZW54bHN4Lm51bUZtdCA9ICIjLCMjMC4wMCIpDQogICAgYWRkV29ya3NoZWV0KHdiLCBwYXN0ZTAoIkdEUF8iLHgpKQ0KICAgIHdyaXRlRGF0YVRhYmxlKHdiLCBwYXN0ZTAoIkdEUF8iLHgpLCB0ZW1wLCB0YWJsZVN0eWxlID0gIlRhYmxlU3R5bGVNZWRpdW0xMyIpDQogICAgYWRkV29ya3NoZWV0KHdiLCAiU3VtbWFyeSIpDQogICAgd3JpdGVEYXRhVGFibGUod2IsIHNoZWV0PSJTdW1tYXJ5Iiwgc3VtbSwgc3RhcnRDb2wgPSAzLCBzdGFydFJvdyA9IDMsdGFibGVTdHlsZSA9ICJUYWJsZVN0eWxlTGlnaHQxMyIpDQogICAgIHByaW50KHApDQogYWRkV29ya3NoZWV0KHdiLCAiQ2hhcnQiKQ0Kd2IgJT4lIGluc2VydFBsb3Qoc2hlZXQ9IkNoYXJ0IixzdGFydENvbCA9MSwgc3RhcnRSb3c9MSx3aWR0aD0xMixoZWlnaHQgPSA5LCBkcGk9NjAwKQ0KDQpzYXZlV29ya2Jvb2sod2IscGFzdGUwKCJvdXRwdXQvIix4LCJfZ2RwMi54bHN4IiksIG92ZXJ3cml0ZSA9IFRSVUUpfQ0KDQpgYGANCg0KDQpgYGB7cn0NCg0Kd3JpdGVfY291bnRyaWVzKCJFUyIpDQpgYGANCg0KDQojIyBJdGVyYXRpbmcgdGhlIGNyZWF0aW9uIG9mIGZpbGVzIA0KDQpGaW5hbGx5LCB3ZSBjYW4gYXV0b21hdGlzZSB0aGUgY3JlYXRpb24gb2YgdGhlIGZpbGVzIGJ5IGNyZWF0aW5nIGEgbGlzdCBvZiBjb3VudHJpZXMgYW5kIGFwcGx5aW5nIHRoZSBmdW5jdGlvbiB0byBlYWNoIGVsZW1lbnQgb2YgdGhlIGxpc3QuIFRoZSB1c2UgdGhlIGZ1bmN0aW9uIGB3YWxrYCBmcm9tIHRoZSBwYWNrYWdlIGB7cHVycnJ9YCB0byBkbyB0aGF0Lg0KDQpgYGB7cn0NCg0KZGY8LSByaW86OmltcG9ydCgiZGF0YS9nZHAydi5jc3YiKSAlPiUgDQogIG11dGF0ZShjb3VudHJ5ID0gc3RyX3N1YihnZW8sMSwyKSwgDQogICAgICAgTlVUUz0gYXMuZmFjdG9yKHN0cl9sZW5ndGgoZ2VvKS0yKSkgJT4lIA0KICBzZWxlY3QoY291bnRyeSxnZW8sTlVUUyx1bml0LG9ic192YWx1ZSkgJT4lIA0KICBmaWx0ZXIodW5pdCAlaW4lIGMoIkVVUl9IQUIiLCAiUFBTX0hBQl9FVTI3XzIwMjAiKSkNCg0KbGlzdF9jb3VudHJpZXM8LSBjKCJOTCIsIlNLIiwiQkUiLCAiQVQiLCAiQ1oiKQ0KDQp3YWxrKGxpc3RfY291bnRyaWVzLCB3cml0ZV9jb3VudHJpZXMpDQpgYGANCg==