o4-mini-2025-04-16· OpenAI
LMArena Human Preference 140K
Please write a script that uses openxlsx in R to create an Excel report with a cover…
- User
Please write a script that uses openxlsx in R to create an Excel report with a cover page, a table of contents, a table tab, and a tab with a chart.
- Assistant
Here’s a self‐contained R script using openxlsx that will generate an Excel report with:
- ▶️ Cover Page
- ▶️ Table of Contents (with clickable links)
- ▶️ Data Table
- ▶️ Chart
Just copy‐paste into your R console (or RStudio) and run!
# install.packages("openxlsx") # uncomment if you haven't installed it library(openxlsx) # 1. Create a new workbook wb <- createWorkbook() # 2. COVER PAGE addWorksheet(wb, "Cover") # Title style titleStyle <- createStyle(fontSize = 24, textDecoration = "bold", halign = "CENTER") dateStyle <- createStyle(fontSize = 12, italic = TRUE, halign = "CENTER") # Merge cells for title and date mergeCells(wb, "Cover", cols = 1:6, rows = 1:2) # Write title and date writeData(wb, "Cover", x = "📊 My Excel Report", startCol = 1, startRow = 1, style = titleStyle) writeData(wb, "Cover", x = format(Sys.Date(), "%B %d, %Y"), startCol = 1, startRow = 2, style = dateStyle) # 3. TABLE OF CONTENTS addWorksheet(wb, "TOC") # define sheets & friendly titles toc <- data.frame( sheet = c("Cover", "TOC", "Table", "Chart"), title = c("Cover Page", "Table of Contents", "Data Table", "Chart") ) # write headers hdrStyle <- createStyle(textDecoration = "bold", fgFill = "#D9E1F2") writeData(wb, "TOC", x = as.list(names(toc)), startRow = 1, startCol = 1, style = hdrStyle) # write hyperlinks + descriptions for(i in seq_len(nrow(toc))) { # column A: clickable sheet name link <- sprintf('HYPERLINK("#\'%s\'!A1","%s")', toc$sheet[i], toc$sheet[i]) writeFormula(wb, "TOC", x = link, startCol = 1, startRow = i + 1) # column B: human-friendly title writeData(wb, "TOC", x = toc$title[i], startCol = 2, startRow = i + 1) } setColWidths(wb, "TOC", cols = 1:2, widths = c(20, 30)) # 4. DATA TABLE addWorksheet(wb, "Table") # for demo, we'll use the first 10 rows of mtcars data <- head(mtcars, 10) writeDataTable( wb, "Table", data, tableName = "SampleTable", withFilter = TRUE, tableStyle = "TableStyleMedium2" ) # 5. CHART addWorksheet(wb, "Chart") # build a small summary library(dplyr) cyl_summary <- mtcars %>% count(cyl) %>% rename(Cylinders = cyl, Frequency = n) # write the summary table (optional) writeData(wb, "Chart", cyl_summary, startCol = 1, startRow = 8, colNames = TRUE) # insert a bar‐plot of cylinders # – we draw it in R and then snapshot into the sheet insertPlot( wb, "Chart", startCol = 1, startRow = 1, width = 6, height = 5, fileType = "png", fun = function() { barplot( cyl_summary$Frequency, names.arg = cyl_summary$Cylinders, col = "#4F81BD", main = "Number of Cylinders", xlab = "Cylinders", ylab = "Count" ) } ) # 6. SAVE WORKBOOK saveWorkbook(wb, file = "My_Excel_Report.xlsx", overwrite = TRUE) message("✅ 'My_Excel_Report.xlsx' has been created in your working directory.")How it works:
- createWorkbook() & addWorksheet() define your sheets.
- mergeCells() + writeData() + createStyle() make the cover page nice and centered.
- writeFormula(..., HYPERLINK()) builds a clickable table of contents.
- writeDataTable() writes & styles your data table.
- insertPlot() captures an R‐plot (here a barplot) and embeds it.
- saveWorkbook() writes out the
.xlsx.
Feel free to swap in your own data, tweak styles, or expand the TOC. Enjoy! 🚀
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.