Frequently Asked Questions

  • How to add multiple lines of text to a cell ?

    You should use a line-feed characters \n in a string and add a wrap attribute to the cell's format: 

      
  Format* format = book->addFormat();
  format->setWrap(true);

  sheet->writeStr(1, 1, L"line one\nline two\nline three", format);

  • How to lock some cells in a sheet ?

    All cells are locked by default, but you should unlock necessary cells and turn on a protection with the Sheet::setProtect() method: 

    
  Format* format = book->addFormat();
  format->setLocked(false);

  sheet->writeStr(5, 1, L"this cell can be changed !", format);
  sheet->writeNum(6, 1, 100, format);

  sheet->setProtect(true);

  • What to do with "cannot convert 'const char*' to 'const wchar_t*' in initialization" compilation error ?

    Switch to Unicode Character Set in project settings in Visual Studio or just add _UNICODE preprocessor variable to your project settings. Also you can replace all "wchar_t" to "char" types in your code.

  • How to use multibyte characters with LibXL (char* instead of wchar_t*) ?

    Switch to Use Muli-Byte Character Set in project settings in Visual Studio or just remove _UNICODE preprocessor variable from your project settings.

  • How to use UTF-8 encoding with LibXL ?

    Switch to Use Muli-Byte Character Set in project settings in Visual Studio or just remove _UNICODE preprocessor variable from your project settings. Also add the following line to your code: 

    
  book->setLocale("UTF-8");

  • What to do with "unresolved external symbol __imp__xlCreateBookW" linker error ?

    Just add "libxl.lib" to "Additional Dependencies" field in your linker settings.

  • Can libxl freeze some rows or columns and how to do it ?

    You can freeze panes with Sheet::split() method: 

    
  sheet->split(1, 0);    // freezes the first row
  sheet->split(0, 1);    // freezes the first column

  • How to show 3 decimal digits for numbers ?

    Just add a new custom format: 

    
  format->setNumFormat(book->addCustomNumFormat(L"0.000"));
  sheet->writeNum(1, 1, 100, format);

  • How to add URL to a cell ?

    First of all, it is needed to define an URL format: 

    
  Font* linkFont = book->addFont();
  linkFont->setColor(COLOR_BLUE);
  linkFont->setUnderline(UNDERLINE_SINGLE);

  Format* linkFormat = book->addFormat();
  linkFormat->setFont(linkFont);
    The first way is using HYPERLINK in formula expression: 
    
  sheet->writeFormula(1, 1, L"HYPERLINK(\"http://www.libxl.com\")",
      linkFormat);
    The second way is defining a specified area with a link: 
    
  sheet->writeStr(1, 1, L"http://www.libxl.com", linkFormat);
  sheet->addHyperlink(L"http://www.libxl.com", 1, 1, 1, 1);

  • Can LibXL calculate formula expressions ?

    Unfortunately LibXL doesn't have a calculation engine, so the library can only read and write formula expressions.
    It can't calculate them. If you want to get formula results, you should open an output file in Microsoft Excel and
    save it back.

  • How to convert Excel column width to pixels ?

    Column width is measured as the number of characters of the maximum digit width of the numbers 0, 1, 2, ..., 9 as rendered in the normal style's font. To translate the value of width into the column width in pixels, use this calculation:

    px = ([100*{width}-0.5]*{Maximum Digit Width})/100 + 5,

    where {Maximum Digit Width} = 7 for a default font.

    Now it is better to use the Sheet::colWidthPx() method. It returns a column width in pixels.

  • How to add a drop-down list to a cell ?

    Please use the Sheet::addDataValidation() method with VALIDATION_TYPE_LIST type of data validation.
    You can use cell references as list here: 

    
  sheet->addDataValidation(VALIDATION_TYPE_LIST, VALIDATION_OP_EQUAL,
  5, 5, 5, 5, L"A2:A5");
    Or specify a list of values in quotes here: 
    
  sheet->addDataValidation(VALIDATION_TYPE_LIST, VALIDATION_OP_EQUAL,
  6, 6, 6, 6, L"\"first,second,third\"");

  • Is there a way to specify a color using RGB values ?

    Yes, you can use RGB colors fully for xlsx files and limited for xls files (no more than 56 colors in the same time and without mixing with indexed colors): 

    
 book->setRgbMode(true);
 format->setBorderColor(book->colorPack(red, green, blue));

  • Do you have any additional questions?

    Please contact us by email or visit our forum here: https://forum.libxl.com/


Home Index Book Sheet Format Font AutoFilter FilterColumn RichString FormControl ConditionalFormatting ConditionalFormat Examples