From 269ea2617fc6ede67a591b7a08b92da8851a31f4 Mon Sep 17 00:00:00 2001
From: Nick Burch <nick@apache.org>
Date: Thu, 15 Feb 2018 22:16:07 +0000
Subject: [PATCH] Add some write/change code as well for Groovy

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1824378 13f79535-47bb-0310-9956-ffa450edef68
---
 src/examples/groovy/SpreadSheetDemo.groovy | 82 ++++++++++++++++++++--
 1 file changed, 78 insertions(+), 4 deletions(-)

diff --git a/src/examples/groovy/SpreadSheetDemo.groovy b/src/examples/groovy/SpreadSheetDemo.groovy
index 379cb9f7d3..dc683b1434 100644
--- a/src/examples/groovy/SpreadSheetDemo.groovy
+++ b/src/examples/groovy/SpreadSheetDemo.groovy
@@ -1,17 +1,91 @@
 import org.apache.poi.ss.usermodel.*
+import org.apache.poi.ss.util.*
 import java.io.File
 
 if (args.length == 0) {
    println "Use:"
-   println "   SpreadSheetDemo [excel-file]"
+   println "   SpreadSheetDemo <excel-file> [output-file]"
    return 1
 }
 
-File f = new File(args[0]);
+File f = new File(args[0])
+DataFormatter formatter = new DataFormatter()
 WorkbookFactory.create(f,null,true).withCloseable { workbook ->
    println "Has ${workbook.getNumberOfSheets()} sheets"
-   0.step workbook.getNumberOfSheets(), 1, { sheetNum ->
-     println "Sheet ${sheetNum} is called ${workbook.getSheetName(sheetNum)}"
+
+   // Dump the contents of the spreadsheet
+   (0..<workbook.getNumberOfSheets()).each { sheetNum ->
+      println "Sheet ${sheetNum} is called ${workbook.getSheetName(sheetNum)}"
+
+      def sheet = workbook.getSheetAt(sheetNum)
+      sheet.each { row ->
+         def nonEmptyCells = row.grep { c -> c.getCellType() != Cell.CELL_TYPE_BLANK }
+         println " Row ${row.getRowNum()} has ${nonEmptyCells.size()} non-empty cells:"
+         nonEmptyCells.each { c ->
+            def cRef = [c] as CellReference
+            println "  * ${cRef.formatAsString()} = ${formatter.formatCellValue(c)}"
+         }
+      }
    }
+
+   // Add two new sheets and populate
+   CellStyle headerStyle = makeHeaderStyle(workbook)
+   Sheet ns1 = workbook.createSheet("Generated 1")
+   exportHeader(ns1, headerStyle, null, ["ID","Title","Num"] as String[])
+   ns1.createRow(1).createCell(0).setCellValue("TODO - Populate with data")
+
+   Sheet ns2 = workbook.createSheet("Generated 2")
+   exportHeader(ns2, headerStyle, "This is a demo sheet", 
+                ["ID","Title","Date","Author","Num"] as String[])
+   ns2.createRow(2).createCell(0).setCellValue(1)
+   ns2.createRow(3).createCell(0).setCellValue(4)
+   ns2.createRow(4).createCell(0).setCellValue(1)
+
+   // Save
+   File output = File.createTempFile("output-", (f.getName() =~ /(\.\w+$)/)[0][0])
+   output.withOutputStream { os -> workbook.write(os) }
+   println "Saved as ${output}"
 }
 
+CellStyle makeHeaderStyle(Workbook wb) {
+   int HEADER_HEIGHT = 18
+   CellStyle style = wb.createCellStyle()
+
+   style.setFillForegroundColor(IndexedColors.AQUA.getIndex())
+   style.setFillPattern(FillPatternType.SOLID_FOREGROUND)
+
+   Font font = wb.createFont()
+   font.setFontHeightInPoints((short)HEADER_HEIGHT)
+   font.setBold(true)
+   style.setFont(font)
+
+   return style
+}
+void exportHeader(Sheet s, CellStyle headerStyle, String info, String[] headers) {
+   Row r
+   int rn = 0
+   int HEADER_HEIGHT = 18
+   // Do they want an info row at the top?
+   if (info != null && !info.isEmpty()) {
+      r = s.createRow(rn)
+      r.setHeightInPoints(HEADER_HEIGHT+1)
+      rn++
+
+      Cell c = r.createCell(0)
+      c.setCellValue(info)
+      c.setCellStyle(headerStyle)
+      s.addMergedRegion(new CellRangeAddress(0,0,0,headers.length-1))
+   }
+   // Create the header row, of the right size
+   r = s.createRow(rn)
+   r.setHeightInPoints(HEADER_HEIGHT+1)
+   // Add the column headings
+   headers.eachWithIndex { col, idx ->
+      Cell c = r.createCell(idx)
+      c.setCellValue(col)
+      c.setCellStyle(headerStyle)
+      s.autoSizeColumn(idx)
+   }
+   // Make all the columns filterable
+   s.setAutoFilter(new CellRangeAddress(rn, rn, 0, headers.length-1))
+}
-- 
2.39.5