openxml-xlsx/example at master · openxml/openxml-xlsx · GitHub

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
#!/usr/bin/env ruby

$:.push Dir.pwd + "/lib"
require "openxml/xlsx"

package = OpenXml::Xlsx::Package.new
workbook = package.workbook
worksheet = workbook.worksheets[0]

include OpenXml::Xlsx::Elements

workbook.add_defined_names({name: "Alpha", formula:"Sheet1!$A$1"}, {name: "Charlie", formula:"Sheet1!$C$1"})

worksheet.merge_cells("B1:G1", "A8:G8")

currency = NumberFormat.new("\"$\"#,##0.00")

heading = Font.new("Calibri", 25, {bold: true, italic: true, underline: true, color: IndexedColor.new(55)})

fill2 = PatternFill.new("solid",
  ThemeColor.new(0, -4.9989318521683403E-2),
  IndexedColor.new(65))
fill3 = PatternFill.new("solid",
  ThemeColor.new(8, 0.79998168889431442),
  IndexedColor.new(65))
fill4 = PatternFill.new("solid",
  ThemeColor.new(8, 0.59999389629810485),
  IndexedColor.new(65))

left = Alignment.new("left", "center")
center = Alignment.new("center", "center")
right = Alignment.new("right", "center")

border1 = Border.new(top: BorderComponent.new(BorderStyle::THICK))
border2 = Border.new(
  left: BorderComponent.new(BorderStyle::HAIR, IndexedColor.new(12)),
  right: BorderComponent.new(BorderStyle::SLANT_DASH_DOT, IndexedColor.new(45))
  )
border3 = Border.new(bottom: BorderComponent.new(BorderStyle::DOUBLE, IndexedColor.new(13)))

worksheet.column_widths(
  1 => 3.83203125,
  2 => 11.1640625,
  5 => 36.1640625
)


worksheet.add_rows(
  { number: 1,
    cells: [
      { column: 2, value: "Test", style: {font: heading, alignment: left} }] },
  { number: 2,
    cells: [
      { column: 2, value: "Column 1", style: {fill: fill2, alignment: left, border: border2} },
      { column: 3, value: "Column 2", style: {fill: fill2, alignment: center, border: border3} },
      { column: 4, value: "Column 3", style: {fill: fill2, alignment: right, border: border1} },
      { column: 5, value: "Column 4", style: {fill: fill4, alignment: right} }] },
  { number: 3,
    cells: [
      { column: 2, value: 1, style: {alignment: left, border: border2} },
      { column: 3, value: Time.new(2014, 6, 20, 8, 30), style: {format: NumberFormat::DATETIME, alignment: center, border: border3} },
      { column: 4, value: 65, style: {format: currency, alignment: right, border: border1} },
      { column: 5, value: "hi", style: {format: currency, fill: fill3, alignment: right} }] },
  { number: 4,
    hidden: true,
    cells: [
      { column: 2, value: 2, style: {alignment: left, border: border2} },
      { column: 3, value: Date.new(2014, 7, 21), style: {format: NumberFormat::DAY_MONTH, alignment: center, border: border3} },
      { column: 4, value: 14, style: {format: currency, alignment: right, border: border1} },
      { column: 5, value: "hi", style: {format: currency, fill: fill3, alignment: right} }] },
  { number: 5,
    cells: [
      { column: 2, value: 3, style: {alignment: left, border: border2} },
      { column: 3, value: Date.new(2014, 8, 24), style: {format: NumberFormat::DAY_MONTH, alignment: center, border: border3} },
      { column: 4, value: 13, style: {format: currency, alignment: right, border: border1} },
      { column: 5, value: "hi", style: {format: currency, fill: fill3, alignment: right} }] },
  { number: 6,
    cells: [
      { column: 2, value: 4, style: {alignment: left, border: border2} },
      { column: 3, value: Date.new(2014, 8, 24), style: {format: NumberFormat::DAY_MONTH, alignment: center, border: border3} },
      { column: 4, value: 9, style: {format: currency, alignment: right, border: border1} },
      { column: 5, value: "hi", style: {format: currency, fill: fill3, alignment: right} }] })

worksheet.add_table 1, "Table1", "B2:E6", [
  TableColumn.new("Column 1"),
  TableColumn.new("Column 2"),
  TableColumn.new("Column 3"),
  TableColumn.new("Column 4")
]

filename = "gemtest1.xlsx"
system "rm ~/Desktop/#{filename}"
package.save File.expand_path("~/Desktop/#{filename}")
exec "open ~/Desktop/#{filename}"