Inserting text is handled as General category in Excel about libxlsxwriter HOT 8 CLOSED

Hi Alfonso,

we are using the library from a wrapper in ruby and it's amazing the speed and well structured it is.

Thanks. By the way there is a Ruby port of the same Perl/Python modules that libxlsxwriter is ported from: https://github.com/cxn03651/write_xlsx

I was making a little example using worksheet_write_string function and I noticed excel handles the cell's category as General instead Text

That is the same behaviour as Excel: if you create some text is a cell the format is General.

can it be changed to Text in code?

Yes by adding a number format of @ (this is how Excel does it). For example:

#include "xlsxwriter.h"

int main() {

    lxw_workbook  *workbook  = new_workbook("test.xlsx");
    lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL);
    lxw_format    *format    = workbook_add_format(workbook);

    format_set_num_format(format, "@");

    worksheet_write_string(worksheet, 0, 0, "Hello", NULL);
    worksheet_write_string(worksheet, 1, 0, "Hello", format);

    workbook_close(workbook);

    return 0;
}

for your info, I'm using optimize by default, I've seen in code when you write a string cell is handled in a different way STRING_CELL or INLINE_STRING_CELL, why this?

By default Excel stores strings in a shared strings table (a bit like a Ruby hash) and uses an index in the file to refer to the string. However, it also allows an "inline" option where the shared string table isn't used and the strings are written inline. This allow the file to be written slightly faster and without additional storage memory.

P.S. If you need the constant_memory mode then the above Ruby module won't work for you since it doesn't support it.

John

from libxlsxwriter.

@jmcnamara thanks so much!, I'll use the @ format for string to format as text.

We aren't using that port because I need constant_memory for bigger exports with XLSX file so that's the reason we built a simple wrapper

from libxlsxwriter.

@jmcnamara works perfect! thanks again and awesome library ;)

from libxlsxwriter.

We aren't using that port because I need constant_memory for bigger exports with XLSX file so that's the reason we built a simple wrapper

Cool, I'll point people in the direction of that module when they ask questions.

from libxlsxwriter.

I didn't find existing wrapper and made my own https://github.com/Paxa/fast_excel
Actually following same idea as https://github.com/bebanjo/xlsx_writer_wrapper

from libxlsxwriter.

@Paxa

Thanks for letting me know.

Out of curiosity, how much faster are these Ruby wrapper modules in comparison to the pure Ruby write_xlsx module.

from libxlsxwriter.

@jmcnamara

I never used write_xlsx, we were using axlsx. My measurements:

20,000 rows
- query and preparations only: 1.9 sec
- generate CSV: 4.5 sec
- generate xslx with axlsx: 15 sec
- generate xslx with fast_excel: 5.4 sec

So only file generation times: CSV: 2.6, axlsx: 13.1, fast_excel: 3.5

I got best excel generation times when converting unix time to excel format by myself and write it with worksheet_write_number (converting timezones and creating objects is expensive in ruby)

  XLSX_DATE_DAY = 86400.0 # seconds in 1 day
  XLSX_DATE_EPOCH_DIFF = 25567 # days between 1970-jan-01 and 1900-jan-01
  XLSX_DATE_TZ = Time.zone.utc_offset / XLSX_DATE_DAY
  # Convert seconds to days, and change beginning point from 1st jan 1970 to 1st jan 1900
  # https://support.microsoft.com/en-us/help/214330/differences-between-the-1900-and-the-1904-date-system-in-excel
  def xlsx_date(time)
    time.to_f / XLSX_DATE_DAY + XLSX_DATE_EPOCH_DIFF + XLSX_DATE_TZ
  end

from libxlsxwriter.

Just tested write_xlsx and fastest I can get is 88 seconds (for same 20k report as my previous comment)

from libxlsxwriter.