How to set the cell format of a date cell from "General" to "Date"?

[bjtu1318]

hi @troldal, how are you! I saw you pointed out that OpenXLSX does not support “format” for now. Does it include this scenario?

I imbedded OpenXLSX to export Excel. It works well, the data in Excel is correct. However, when I open the Excel file, select a date cell, right click and choose “Format Cells” menu, the format of the date cell is “General.” I expect the cell format to be “Date” rather than “General.” Is there any OpenXLSX method that allows me to set the cell format to “Date”? Is it possible?

If not, from the perspective of an Excel expert, what is your suggestion about my problem? How do I convert cell format from “General” to “Date”? Can I do this by creating an Excel macro or something else? Thanks a lot!

[aral-matrix]

Hi @bjtu1318 & sorry for the late response.

The development-aral branch has Styles support, if you want to try it out. What's still missing, because I focused on implementing everything on a base level, without too much attention to detail, is a functionality to auto-assign numFmtId values and return those, skipping reserved number formats. For now, the user has to define a number format ID themselves, like so:

    wks.cell("A25" ) = 42; // NEW functionality: XLCellAssignable now has a direct value assignment overload
    std::cout << "wks A25 value is " << wks.cell("A25" ) << std::endl;
    XLStyleIndex A25format = wks.cell("A25").cellFormat();      // get current format from A25
    A25format = cellFormats.create( cellFormats[ A25format ] ); // create a new cell format based on the format used in A25
    wks.cell("A25").setCellFormat( A25format );                 // assign the new cell format to A25

    bool predefinedFormat = true;
    if( predefinedFormat )
        cellFormats[ A25format ].setNumberFormatId( 22 );                       // assign a predefined format id
    else {                                                                      // OR define a custom format:
        XLStyleIndex A25numFmtId = 200;                                         // currently, user needs to specify a number format ID themselves, respecting reserved (built-in) number formats
        cellFormats[ A25format ].setNumberFormatId( A25numFmtId );              // assign the new number format to the new cell format
        XLStyleIndex A25numFmt = numberFormats.create();                        // create a new (default) number format
        numberFormats[ A25numFmt ].setNumberFormatId( A25numFmtId );            // assign the previously defined number format ID to the new number format
        numberFormats[ A25numFmt ].setFormatCode( "yyyy-mmmm-ddThh:MM:ss" );    // create a desired number format string
    }

[hopkinsa17]

I'm having a similar issue trying to apply accounting format to a cell by XLCellReference after assigning a double value to that cell, I can't seem to find a way to assign a numformat by existing index to the cell. According to the Microsoft documentation, the NumberingFormat class index for the format I'm trying to use is 4. I've tried repurposing the code you provided above to achieve this functionality but haven't had any success. Thank you!

[aral-matrix]

Hi @hopkinsa17, first of all - the functionality is now in the main project branch, no longer limited to testing. That said, the above functionality should work. Try this example program & let me know what it does for you:

#include <iostream>
#include <OpenXLSX.hpp>

using namespace OpenXLSX;

int main( void )
{
    XLDocument doc{};
    doc.create("./Demo-number-format.xlsx", XLForceOverwrite);

    XLCellFormats & cellFormats = doc.styles().cellFormats(); // get a handle on cell formats
    XLWorksheet wks = doc.workbook().worksheet(1); // get a handle on worksheet
    wks.cell("A1") = 2; // assign an example value to a cell
    XLStyleIndex cellFormatForNumber = cellFormats.create(); // create a new cell format
    cellFormats[ cellFormatForNumber ].setNumberFormatId( 4 ); // set number format id for new cell format
    wks.cell("A1").setCellFormat( cellFormatForNumber ); // assign new cell format to cell

    doc.save();
    doc.close();

    return 0;
}

It should display 2.00 using your desired number format 4 in cell A1.

[hopkinsa17]

Hi @aral-matrix, thank you so much for such a speedy response. That did the trick, I'm able to apply formatting to the spreadsheets I'm manipulating now. Thank you!!

[aral-matrix]

You are welcome - I am happy to hear that solved the issue for you. One thing I forgot to mention: When you create a new style (of any category), you can provide an existing style as a template to copy all properties. In your case, assume you want to apply number formatting to a cell that already has some other formatting, you would modify the line

    XLStyleIndex cellFormatForNumber = cellFormats.create(); // create a new cell format

to

    XLStyleIndex cellFormatForNumber = cellFormats.create( cellFormats[ wks.cell("A1").cellFormat() ] );

This will create a new cell format, using the existing format of cell A1 as a template. Note that if no format has been set for cell A1, XLCell::cellFormat() will return 0, where the XLStyles entry for index 0 should always exist as a default style (and OpenXLSX creates it if the /xl/styles.xml does not exist).

Further note: using an existing style as template will ONLY duplicate the properties of that particular style. If it refers to other style elements (which - in cellFormat - is the case for numberFormatId, fontIndex, fillIndex, borderIndex, xfId) those same style IDs will be referred in the copy-created style. If you want to modify those referred styles without touching the originals, you have to create new styles for each that requires modification, and assign the new ID using the setter functions.

For some usage examples, please refer to Demo10.cpp, line 155 and following: https://github.com/troldal/OpenXLSX/blob/master/Examples/Demo10.cpp#L155