Class: XlsxWriter::Workbook

Inherits:
Object
  • Object
show all
Defined in:
ext/xlsxwriter/workbook.c,
ext/xlsxwriter/chart.c,
ext/xlsxwriter/workbook.c

Overview

Workbook is the main class exposed by XlsxWriter. It represents the workbook (.xlsx) file.

Defined Under Namespace

Classes: Chart, Properties

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(*args) ⇒ Object

:nodoc:



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
109
110
111
112
113
114
115
116
117
118
119
120
 
# File 'ext/xlsxwriter/workbook.c', line 80

VALUE
workbook_init(int argc, VALUE *argv, VALUE self) {
  struct workbook *ptr;
  lxw_workbook_options options = {
    .constant_memory = 0,
    .tmpdir = NULL,
    .use_zip64 = 0
  };

  if (argc < 1 || argc > 2) {
    rb_raise(rb_eArgError, "wrong number of arguments");
    return self;
  } else if (argc == 2) {
    VALUE const_mem = rb_hash_aref(argv[1], ID2SYM(rb_intern("constant_memory")));
    if (!NIL_P(const_mem) && const_mem) {
      options.constant_memory = 1;
      VALUE tmpdir = rb_hash_aref(argv[1], ID2SYM(rb_intern("tmpdir")));
      if (!NIL_P(tmpdir))
        options.tmpdir = RSTRING_PTR(tmpdir);
    }
    VALUE use_zip64_ = rb_hash_aref(argv[1], ID2SYM(rb_intern("use_zip64")));
    if (!NIL_P(use_zip64_) && use_zip64_) {
      options.use_zip64 = 1;
    }
  }

  TypedData_Get_Struct(self, struct workbook, &workbook_type, ptr);

  size_t len = RSTRING_LEN(argv[0]);
  ptr->path = malloc(len + 1);
  strncpy(ptr->path, RSTRING_PTR(argv[0]), len + 1);
  if (options.constant_memory) {
    ptr->workbook = workbook_new_opt(ptr->path, &options);
  } else {
    ptr->workbook = workbook_new(ptr->path);
  }
  ptr->properties = NULL;
  rb_iv_set(self, "@font_sizes", rb_hash_new());

  return self;
}

Instance Attribute Details

#font_sizes ⇒ Object (readonly)

This attribute contains effective font widths used for automatic column widths of workbook columns.

Class Method Details

.XlsxWriter::Workbook.new(path, constant_memory: false, tmpdir: nil, use_zip64: false) ⇒ Object .XlsxWriter::Workbook.new(path, constant_memory: false, tmpdir: nil, use_zip64: false) {|wb| ... } ⇒ nil .XlsxWriter::Workbook.open(path, constant_memory: false, tmpdir: nil, use_zip64: false) {|wb| ... } ⇒ nil

Creates a new Xlsx workbook in file path and returns a new Workbook object.

If constant_memory is set to true workbook data is stored in temporary files in tmpdir, considerably reducing memory consumption for large documents.

If use_zip64 is set to truthy value zip64 extensions are enabled for the resulting xlsx file. It allows for files in the archive to have size >4GB.

XlsxWriter::Workbook.open('/tmp/test.xlsx', constant_memory: true) do |wb|
  # ... populate the workbook with data ...
end

Overloads:

  • .XlsxWriter::Workbook.new(path, constant_memory: false, tmpdir: nil, use_zip64: false) {|wb| ... } ⇒ nil

    Yields:

    • (wb)

    Returns:

    • (nil)
  • .XlsxWriter::Workbook.open(path, constant_memory: false, tmpdir: nil, use_zip64: false) {|wb| ... } ⇒ nil

    Yields:

    • (wb)

    Returns:

    • (nil) 


68
69
70
71
72
73
74
75
76
77
 
# File 'ext/xlsxwriter/workbook.c', line 68

VALUE
workbook_new_(int argc, VALUE *argv, VALUE self) {
  VALUE workbook = rb_call_super(argc, argv);
  if (rb_block_given_p()) {
    rb_yield(workbook);
    workbook_release(workbook);
    return Qnil;
  }
  return workbook;
}

Instance Method Details

#add_chart(type) {|chart| ... } ⇒ Object #add_chert(type) ⇒ Object

Adds a chart of type type to the workbook.

type is expected to be one of XlsxWriter::Workbook::Chart::{NONE, AREA, AREA_STACKED, AREA_STACKED_PERCENT, BAR, BAR_STACKED, BAR_STACKED_PERCENT, COLUMN, COLUMN_STACKED, COLUMN_STACKED_PERCENT, DOUGHNUT, LINE, PIE, SCATTER, SCATTER_STRAIGHT, SCATTER_STRAIGHT_WOTH_MARKERS, SCATTER_SMOOTH, SCATTER_SMOOTH_WITH_MARKERS, RADAR, RADAR_WITH_MARKERS, RADAR_FILLED}.

wb.add_chart(XlsxWriter::Workbook::Chart::PIE) do |chart|
  chart.add_series 'A1:A10', 'B1:B10'
  ws.insert_chart('D2', chart)
end

Overloads:

  • #add_chart(type) {|chart| ... } ⇒ Object

    Yields:

    • (chart)

    Returns:

    • (Object) 


330
331
332
333
334
335
336
337
338
 
# File 'ext/xlsxwriter/workbook.c', line 330

VALUE
workbook_add_chart_(VALUE self, VALUE type) {
  VALUE chart = rb_funcall(cChart, rb_intern("new"), 2, self, type);
  if (rb_block_given_p()) {
    VALUE res = rb_yield(chart);
    return res;
  }
  return chart;
}

#add_chartsheet([name]) ⇒ Object #add_chartsheet([name]) {|ws| ... } ⇒ Object

Adds a chartsheet named name to the workbook.

If a block is passed, the last statement is returned.

wb.add_chartsheet('Cool chart') do |cs|
  cs.chart = chart
end

Overloads:

  • #add_chartsheet([name]) {|ws| ... } ⇒ Object

    Yields:

    • (ws)

    Returns:

    • (Object) 


235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
 
# File 'ext/xlsxwriter/workbook.c', line 235

VALUE
workbook_add_chartsheet_(int argc, VALUE *argv, VALUE self) {
  VALUE chartsheet = Qnil;

  rb_check_arity(argc, 0, 1);

  struct workbook *ptr;
  TypedData_Get_Struct(self, struct workbook, &workbook_type, ptr);
  if (ptr->workbook) {
    chartsheet = rb_funcall(cChartsheet, rb_intern("new"), argc + 1, self, argv[0]);
  }

  if (rb_block_given_p()) {
    VALUE res = rb_yield(chartsheet);
    return res;
  }

  return chartsheet;
}

#add_format(key, definition) ⇒ Object

Adds a format identified as key with parameters set from definition to the workbook.

definition should be an object and may contain the following options:

:font_name

Font family to be used to display the cell content (like Arial, Dejavu or Helvetica).

:font_size

Font size.

:font_color

Text color.

:bold, :italic, underline

Bold, italic, underlined text.

:font_strikeout

Striked out text.

:font_script

Superscript (XlsxWriter::Format::FONT_SUPERSCRIPT) or subscript (XlsxWriter::Format::FONT_SUBSCRIPT).

:num_format

Defines numerical format with mask, like 'd mmm yyyy' or '#,##0.00'.

:num_format_index

Defines numerical format from special pre-defined set.

:unlocked

Allows modifications of protected cells.

:hidden
:align, :vertical_align
:text_wrap
:rotation
:indent
:shrink
:pattern
:bg_color
:fg_color
:border
:bottom, :top, :left, :right
:border_color, :bottom_color, :top_color, :left_color, :right_color


286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
 
# File 'ext/xlsxwriter/workbook.c', line 286

VALUE
workbook_add_format_(VALUE self, VALUE key, VALUE opts) {
  struct workbook *ptr;
  lxw_format *format;
  TypedData_Get_Struct(self, struct workbook, &workbook_type, ptr);

  if (!ptr->formats) {
    ptr->formats = st_init_numtable();
  }

  format = workbook_add_format(ptr->workbook);
  st_insert(ptr->formats, rb_to_id(key), (st_data_t)format);
  format_apply_opts(format, opts);

  VALUE font_size = rb_hash_aref(opts, ID2SYM(rb_intern("font_size")));
  if (!NIL_P(font_size)) {
    VALUE bold = rb_hash_aref(opts, ID2SYM(rb_intern("bold")));
    if (!NIL_P(bold) && bold) {
      rb_hash_aset(rb_iv_get(self, "@font_sizes"), key, rb_float_new(NUM2DBL(font_size) * 1.5));
    } else {
      rb_hash_aset(rb_iv_get(self, "@font_sizes"), key, font_size);
    }
  }

  return self;
}

#add_vba_project(filename) ⇒ Object

Adds a vba project to the workbook (has to be extracted from a xlsm file). Only one file per workbook is allowed. Documents with vba projects should have extension “xlsm” rather than “xlsx” to avoid reader/editor software warnings.



347
348
349
350
351
 
# File 'ext/xlsxwriter/workbook.c', line 347

VALUE
workbook_add_vba_project_(VALUE self, VALUE filename) {
  LXW_ERR_RESULT_CALL(workbook, add_vba_project, StringValueCStr(filename));
  return self;
}

#add_worksheet([name]) ⇒ Object #add_worksheet([name]) {|ws| ... } ⇒ Object

Adds a worksheet named name to the workbook.

If a block is passed, the last statement is returned.

wb.add_worksheet('Sheet1') do |ws|
  ws.add_row(['test'])
end

Overloads:

  • #add_worksheet([name]) {|ws| ... } ⇒ Object

    Yields:

    • (ws)

    Returns:

    • (Object) 


203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
 
# File 'ext/xlsxwriter/workbook.c', line 203

VALUE
workbook_add_worksheet_(int argc, VALUE *argv, VALUE self) {
  VALUE worksheet = Qnil;

  rb_check_arity(argc, 0, 1);

  struct workbook *ptr;
  TypedData_Get_Struct(self, struct workbook, &workbook_type, ptr);
  if (ptr->workbook) {
    worksheet = rb_funcall(cWorksheet, rb_intern("new"), argc + 1, self, argv[0]);
  }

  if (rb_block_given_p()) {
    VALUE res = rb_yield(worksheet);
    return res;
  }

  return worksheet;
}

#close ⇒ nil

Dumps the workbook content to the file and closes the worksheet. To be used only for workbooks opened with XlsxWriter::Workbook.new without block.

No methods should be called on the worksheet after it is colsed.

wb = XlsxWriter::Workbook.new('/tmp/test.xlsx')
wb.close

Returns:

  • (nil) 


134
135
136
137
138
139
140
141
 
# File 'ext/xlsxwriter/workbook.c', line 134

VALUE
workbook_release(VALUE self) {
  struct workbook *ptr;
  TypedData_Get_Struct(self, struct workbook, &workbook_type, ptr);

  workbook_clear(ptr);
  return self;
}

#define_name(name, formula) ⇒ Object

Create a defined name in the workbook to use as a variable defined in formula.

wb.define_name 'Sales', '=Sheet1!$G$1:$H$10'



392
393
394
395
396
397
 
# File 'ext/xlsxwriter/workbook.c', line 392

VALUE
workbook_define_name_(VALUE self, VALUE name, VALUE formula) {
  LXW_ERR_RESULT_CALL(workbook, define_name, StringValueCStr(name), StringValueCStr(formula));

  return self;
}

#max_url_length ⇒ Integer

Returns:

  • (Integer) 


415
416
417
418
419
420
 
# File 'ext/xlsxwriter/workbook.c', line 415

VALUE
workbook_max_url_length_(VALUE self) {
  struct workbook *ptr;
  TypedData_Get_Struct(self, struct workbook, &workbook_type, ptr);
  return INT2NUM(ptr->workbook->max_url_length);
}

#max_url_length=(255) ⇒ Object 



424
425
426
427
428
429
430
431
432
433
 
# File 'ext/xlsxwriter/workbook.c', line 424

VALUE
workbook_max_url_length_set_(VALUE self, VALUE value) {
  struct workbook *ptr;

  TypedData_Get_Struct(self, struct workbook, &workbook_type, ptr);

  ptr->workbook->max_url_length = NUM2INT(value);

  return value;
}

#properties ⇒ Object

Returns workbook properties accessor object.

wb.properties.title = 'My awesome sheet'



379
380
381
382
383
384
 
# File 'ext/xlsxwriter/workbook.c', line 379

VALUE
workbook_properties_(VALUE self) {
  VALUE props = rb_obj_alloc(cWorkbookProperties);
  rb_obj_call_init(props, 1, &self);
  return props;
}

#set_default_xf_indices ⇒ Object

:nodoc:



354
355
356
357
358
359
360
 
# File 'ext/xlsxwriter/workbook.c', line 354

VALUE
workbook_set_default_xf_indices_(VALUE self) {
  struct workbook *ptr;
  TypedData_Get_Struct(self, struct workbook, &workbook_type, ptr);
  lxw_workbook_set_default_xf_indices(ptr->workbook);
  return self;
}

#sst.string_count ⇒ 0

Returns special accessor object for shared strings table.

Returns:

  • (0) 


440
441
442
443
444
445
446
447
 
# File 'ext/xlsxwriter/workbook.c', line 440

VALUE
workbook_sst_(VALUE self) {
  struct workbook *ptr;

  TypedData_Get_Struct(self, struct workbook, &workbook_type, ptr);

  return alloc_shared_strings_table_by_ref(ptr->workbook->sst);
}

#unset_default_url_format ⇒ Object

Unsets default url format



403
404
405
406
407
408
409
410
411
 
# File 'ext/xlsxwriter/workbook.c', line 403

VALUE
workbook_unset_default_url_format_(VALUE self) {
  struct workbook *ptr;

  TypedData_Get_Struct(self, struct workbook, &workbook_type, ptr);
  workbook_unset_default_url_format(ptr->workbook);

  return self;
}

#validate_sheet_name(name) ⇒ true #validate_worksheet_name(name) ⇒ true

Validates a worksheet name. Returns true or raises an exception (not implemented yet).

Overloads:

  • #validate_sheet_name(name) ⇒ true

    Returns:

    • (true)
  • #validate_worksheet_name(name) ⇒ true

    Returns:

    • (true) 


457
458
459
460
461
 
# File 'ext/xlsxwriter/workbook.c', line 457

VALUE
workbook_validate_sheet_name_(VALUE self, VALUE name) {
  LXW_ERR_RESULT_CALL(workbook, validate_sheet_name, StringValueCStr(name));
  return Qtrue;
}

#validate_sheet_name(name) ⇒ true #validate_worksheet_name(name) ⇒ true

Validates a worksheet name. Returns true or raises an exception (not implemented yet).

Overloads:

  • #validate_sheet_name(name) ⇒ true

    Returns:

    • (true)
  • #validate_worksheet_name(name) ⇒ true

    Returns:

    • (true) 


457
458
459
460
461
 
# File 'ext/xlsxwriter/workbook.c', line 457

VALUE
workbook_validate_sheet_name_(VALUE self, VALUE name) {
  LXW_ERR_RESULT_CALL(workbook, validate_sheet_name, StringValueCStr(name));
  return Qtrue;
}

#vba_name=(name) ⇒ Object

Set the VBA name for the workbook.



367
368
369
370
371
 
# File 'ext/xlsxwriter/workbook.c', line 367

VALUE
workbook_set_vba_name_(VALUE self, VALUE name) {
  LXW_ERR_RESULT_CALL(workbook, set_vba_name, StringValueCStr(name));
  return name;
}