Results 1 to 6 of 6

Thread: LibXL - Some questions

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Join Date
    Aug 2019
    Location
    Cantabria, Spain
    Posts
    43

    Question LibXL - Some questions

    Hello guys!

    We have been working with LibXL recently in order to export data on our applications and it seems to be working nicely. However we have a lot to learn about it!


    1. First question is about format. When we write dates on a cell, we generally write it as a number (WriteNumberWithFormat + AddCustomNumFormat with a custom mask) and the Excel data type combo recognises it properly as a "Date" and everything is shown properly. The problem here is that the minimum date value in Excel is 00/01/1900. In fact, if you write a 0 on a cell with that format, 00/01/1900 will be shown when you tab out. It would be nice to show an empty date without altering the format (I know if I write it as "General" type or just use "WriteBlankWithFormat" it won't show a date). Do you know if that is possible?
    2. Second question is about dynamic/pivot tables. Would it be possible to add those kind of tables to Excel spreadsheets using this library? I think adding dynamic tables and charts will be really useful for our clients, but I haven't found any code example for that.


    Thank you very much

  2. #2
    Join Date
    Feb 2009
    Location
    Adelaide, South Australia, Down under
    Posts
    2,216

    Default Re: LibXL - Some questions

    We use another technology (softartisans excelwriter), where writing a null has the desired effect.
    Is that possible in LibXL?
    Marco Kuipers
    DataFlex Consultant
    28 IT Pty Ltd - DataFlex Specialist Consultancy
    Adelaide, South Australia
    www.28it.com.au

  3. #3
    Join Date
    Aug 2019
    Location
    Cantabria, Spain
    Posts
    43

    Default Re: LibXL - Some questions

    Marco, I have just tried it. If I check the date value before writing it and it has an empty value or a date older than 00/01/1900, I write NullDateTime() instead of that date in the given cell. Surprisingly the library converts it to "General" data type and ignores the given format (I use the WriteNumberWithFormat function with a peNumFormat with a date mask). It might work that way, at least it doesn't show that default date. The only downside is that it doesn't have a "Date" type.

    Sorry if some of the Excel terms I'm using are not accurate enough, the language we use on Office products is not English.
    Last edited by Alberto Gutiérrez Arroyo; 16-Oct-2019 at 03:31 AM.

  4. #4
    Join Date
    Feb 2009
    Location
    Castlegar, BC Canada
    Posts
    3,679

    Default Re: LibXL - Some questions

    Hi Alberto

    Maybe you have to change the format on that cell so that it is formatted as "General" and then leave the value blank.

  5. #5
    Join Date
    Aug 2019
    Location
    Cantabria, Spain
    Posts
    43

    Default Re: LibXL - Some questions

    Hello Mike,

    Quote Originally Posted by Mike Cooper View Post
    Maybe you have to change the format on that cell so that it is formatted as "General" and then leave the value blank.
    Yes, this is indeed what my code does right now (even though it uses "number" format functions) and it seems to work fine. I hope it won't be a problem for our clients (perhaps they won't notice). If that causes them some trouble with ordering or filters on Excel they can always select every cell on that column and format it that way I think.

    I will leave it as it is right now. LibXL is a pretty useful library, I really like it!

    Thank you very much!

  6. #6
    Join Date
    Feb 2009
    Location
    Castlegar, BC Canada
    Posts
    3,679

    Default Re: LibXL - Some questions

    I too found it very useful and used to to create a fairly complex Excel workbook with multiple sheets.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •