Databases
StackOverflow2013
Postsdbo
POReading dates from OpenXml Excel files
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POReading dates from OpenXml Excel files
    primarykey
    data
    text
    <p>I'm trying to read data from the .xlsx files using SharpZipLib to unpack it (in memory) and reading the inner xml files. Everything is fine but recognizing the dates - they're stored in julean format and I need to somehow recognize if a number is a date or only a number. In another topic (unfortunately it died and I need quick answer) I got to know some things from Mark Baker, but it's still not enough...</p> <blockquote> <p>"Excel stores dates as a float value... the integer part being the number of days since 1/1/1900 (or 1/1/1904 depending on which calendar is being used), the fractional part being the proportion of a day (ie the time part)... made slightly more awkward by the fact that 1900 is considered a leap year.</p> <p>The only thing that differentiates a data from a number is the number format mask. If you can read the format mask, you can use that to identify the value as a date rather than a number... then calculate the date value/formatting from the base date."</p> <p><em><strong>"But doesn't the attribute "s" for dates has always the value of "1"? I know it defines style, but maybe? ;)"</em></strong> </p> <p>The s attribute references a style xf entry in styles.xml, and it won't always be entry 1 for dates... it all depends how many different styles are being used in the workbook. The style xf in turn references a number format mask. To identify a cell that contains a date, you need to perform the style xf -> numberformat lookup, then identify whether that numberformat mask is a date/time numberformat mask (rather than, for example, a percentage or an accounting numberformat mask)</p> <p><em><strong>"One more question - I'm now looking at the style.xml's content and in the section I see elements like: "&lt;xf numFmtId="14" ... applyNumberFormat="1" /&gt;", "&lt;xf numFmtId="1" ... applyNumberFormat="1" /&gt;", etc. but there is no &lt;numFmts&gt; section... Are there any "standard" formats? Or am I just missing something?"</em></strong></p> </blockquote> <p>Can someone please help me out? Thanks in advance.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USbrovar
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POReading dates from OpenXml Excel files
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POReading dates from OpenXml Excel files
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POReading dates from OpenXml Excel files
      1. USSam Watkins
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload