github-starred/libxlsxwriter

Fork 0

mirror of https://github.com/jmcnamara/libxlsxwriter.git synced 2026-05-15 14:15:54 -06:00

[GH-ISSUE #54] Inserting text is handled as General category in Excel #45

New issue

Closed

opened 2026-05-05 11:28:33 -06:00 by gitea-mirror · 8 comments

gitea-mirror commented

2026-05-05 11:28:33 -06:00

Owner

Originally created by @AlfonsoUceda on GitHub (Jun 23, 2016).
Original GitHub issue: https://github.com/jmcnamara/libxlsxwriter/issues/54

Originally assigned to: @jmcnamara on GitHub.

Hi:

@jmcnamara congrats for the work, we are using the library from a wrapper in ruby and it's amazing the speed and well structured it is.

I was making a little example using worksheet_write_string function and I noticed excel handles the cell's category as General instead Text, can it be changed to Text in code?

Thank you
Cheers!

Originally created by @AlfonsoUceda on GitHub (Jun 23, 2016). Original GitHub issue: https://github.com/jmcnamara/libxlsxwriter/issues/54 Originally assigned to: @jmcnamara on GitHub. Hi: @jmcnamara congrats for the work, we are using the library from a wrapper in ruby and it's amazing the speed and well structured it is. I was making a little example using [worksheet_write_string](http://libxlsxwriter.github.io/worksheet_8h.html#a0c3e081792631511c9ee3f7507afb78c) function and I noticed excel handles the cell's category as General instead Text, can it be changed to Text in code? Thank you Cheers!

gitea-mirror

2026-05-05 11:28:33 -06:00

closed this issue
added the
question

ready to close
labels

gitea-mirror commented

2026-05-05 11:28:44 -06:00

Author

Owner

@jmcnamara commented on GitHub (Jun 23, 2016):

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

@jmcnamara commented on GitHub (Jun 23, 2016): 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: ``` C #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

gitea-mirror commented

2026-05-05 11:28:49 -06:00

Author

Owner

@AlfonsoUceda commented on GitHub (Jun 23, 2016):

@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

@AlfonsoUceda commented on GitHub (Jun 23, 2016): @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](https://github.com/bebanjo/xlsx_writer_wrapper)

gitea-mirror commented

2026-05-05 11:28:53 -06:00

Author

Owner

@AlfonsoUceda commented on GitHub (Jun 23, 2016):

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

@AlfonsoUceda commented on GitHub (Jun 23, 2016): @jmcnamara works perfect! thanks again and awesome library ;)

gitea-mirror commented

2026-05-05 11:28:55 -06:00

Author

Owner

@jmcnamara commented on GitHub (Jun 23, 2016):

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.

@jmcnamara commented on GitHub (Jun 23, 2016): > 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.

gitea-mirror commented

2026-05-05 11:28:58 -06:00

Author

Owner

@Paxa commented on GitHub (Feb 24, 2017):

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

@Paxa commented on GitHub (Feb 24, 2017): 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

gitea-mirror commented

2026-05-05 11:29:00 -06:00

Author

Owner

@jmcnamara commented on GitHub (Feb 24, 2017):

@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.

@jmcnamara commented on GitHub (Feb 24, 2017): @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.

gitea-mirror commented

2026-05-05 11:29:02 -06:00

Author

Owner

@Paxa commented on GitHub (Feb 25, 2017):

@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

@Paxa commented on GitHub (Feb 25, 2017): @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) ```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 ```

gitea-mirror commented

2026-05-05 11:29:05 -06:00

Author

Owner

@Paxa commented on GitHub (Feb 26, 2017):

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

@Paxa commented on GitHub (Feb 26, 2017): Just tested `write_xlsx` and fastest I can get is 88 seconds (for same 20k report as my previous comment)