1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
|
/* ====================================================================
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
==================================================================== */
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> [output-file]"
return 1
}
File f = new File(args[0])
DataFormatter formatter = new DataFormatter()
WorkbookFactory.create(f,null,true).withCloseable { workbook ->
println "Has ${workbook.getNumberOfSheets()} sheets"
// 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() != CellType.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))
}
|
