Recent

Author Topic: [SOLVED via svn!] MS Excel created xml file support  (Read 1785 times)

wp

  • Hero Member
  • *****
  • Posts: 6485
Re: [SOLVED via svn!] MS Excel created xml file support
« Reply #15 on: July 14, 2019, 11:17:31 pm »
r7034 raised exception too, see picture.
Hmmm... You are using the excelxmlwrite demo which comes with this revision? Is your Lazarus 32 bit or 64 bit? You seem to be on Windows 7?

Please try again with r7036, it displays more information in the exception message.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

totya

  • Hero Member
  • *****
  • Posts: 577
Re: [SOLVED via svn!] MS Excel created xml file support
« Reply #16 on: July 14, 2019, 11:19:39 pm »
Windows 7 x64, Lazarus x86.

wp

  • Hero Member
  • *****
  • Posts: 6485
Re: [SOLVED via svn!] MS Excel created xml file support
« Reply #17 on: July 14, 2019, 11:23:50 pm »
I'm sorry, but I have a new question, but I think the answer is "no". :)

If I open an office xml file with your great component, and after save it (without any changes), the result xml file is different from the original (like as MS Excel). My queston is, is possible anyhow, the saved file let be same as the original file?

For example, if I changed only one cell, then the best for me, if only one line changed in the xml... and only the data value, between > <

But I think It's not "normal" usage.  Sorry for this question.
No problem.

ATM, the Excel2003/XML reader/writers are not complete: There are still several nodes which the reader ignores. Therefore, the writer (which is more complete) cannot write them back. And even when I'll be finished it is still possible that the file will change after write-back because there a many details in these files which I don't understand. The same is true for the standard formats such as xlsx or ods.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

wp

  • Hero Member
  • *****
  • Posts: 6485
Re: [SOLVED via svn!] MS Excel created xml file support
« Reply #18 on: July 14, 2019, 11:27:28 pm »
Windows 7 x64, Lazarus x86.
I see. What is "de./du."? Something like "AM/PM"? In this case, this format modifier is not supported ATM, and you should switch to a 24-hour format.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

totya

  • Hero Member
  • *****
  • Posts: 577
Re: [SOLVED via svn!] MS Excel created xml file support
« Reply #19 on: July 14, 2019, 11:29:25 pm »
And even when I'll be finished it is still possible that the file will change after write-back because there a many details in these files which I don't understand. The same is true for the standard formats such as xlsx or ods.

I tought it, but now I'm sure. Thanks for the answer! I continue to write my low level "parser", but your component is very good for example for a compare (position check)... etc...

totya

  • Hero Member
  • *****
  • Posts: 577
Re: [SOLVED via svn!] MS Excel created xml file support
« Reply #20 on: July 14, 2019, 11:30:53 pm »
Windows 7 x64, Lazarus x86.
I see. What is "de./du."? Something like "AM/PM"? In this case, this format modifier is not supported ATM, and you should switch to a 24-hour format.

de = AM
du = PM

This is the default language setting by the OS, in my country (Hungary).

wp

  • Hero Member
  • *****
  • Posts: 6485
Re: [SOLVED via svn!] MS Excel created xml file support
« Reply #21 on: July 14, 2019, 11:40:44 pm »
What does this program write to the screen?
Code: Pascal  [Select]
  1. program Project1;
  2.  
  3. uses
  4.   SysUtils;
  5.  
  6. begin
  7.   WriteLn('ShortTimeFormat: ', DefaultFormatSettings.LongTimeFormat);
  8.   WriteLn('LongTimeFormat:  ', DefaultFormatSettings.ShortTimeFormat);
  9.   WriteLn('TimeAMString:    ', DefaultFormatSettings.TimeAMString);
  10.   WriteLn('TimePMString:    ', DefaultFormatSettings.TimePMString);
  11.   ReadLn;
  12. end.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

totya

  • Hero Member
  • *****
  • Posts: 577
Re: [SOLVED via svn!] MS Excel created xml file support
« Reply #22 on: July 15, 2019, 12:24:12 am »
ops... your sample is buggy...  O:-) The corrected code:

Code: Pascal  [Select]
  1. begin
  2.   WriteLn('LongTimeFormat: ', DefaultFormatSettings.LongTimeFormat);
  3.   WriteLn('ShortTimeFormat: ', DefaultFormatSettings.ShortTimeFormat);
  4.   WriteLn('TimeAMString:    ', DefaultFormatSettings.TimeAMString);
  5.   WriteLn('TimePMString:    ', DefaultFormatSettings.TimePMString);
  6.   ReadLn;
  7. end.

The result:

Quote
LongTimeFormat: h:nn:ss
ShortTimeFormat: h:nn
TimeAMString:    de.
TimePMString:    du.

« Last Edit: July 15, 2019, 12:50:37 am by totya »

totya

  • Hero Member
  • *****
  • Posts: 577
Re: [SOLVED via svn!] MS Excel created xml file support
« Reply #23 on: July 16, 2019, 08:48:18 am »
Windows 7 x64, Lazarus x86.
I see. What is "de./du."? Something like "AM/PM"? In this case, this format modifier is not supported ATM, and you should switch to a 24-hour format.

Hi master :)

I cant switch to the 24 hours format, because my country time default format the 24 hours format... BUT if I change manually "de" sign to AM, and "du' sign to PM, your excel xml demo executed without any error.
« Last Edit: July 16, 2019, 10:24:55 am by totya »

wp

  • Hero Member
  • *****
  • Posts: 6485
Re: [SOLVED via svn!] MS Excel created xml file support
« Reply #24 on: July 17, 2019, 12:14:47 am »
I had never seen this issue before your report because my DefaultFormatSettings contain empty strings for TimeAMString and TimePMString.

Please try r7043. Should be fixed now.

-----------------

The Excel2003/XML reader is now rather complete. What is missing is rich-text cell formatting. The reason is the same isue for which you posted a question somewhere else: Using laz2_dom and laz2_xmlread, how can I retrieve the undecoded xml child nodes of that strange <ss:Data> node?
Code: XML  [Select]
  1.  <Cell ss:StyleID="s112"><ss:Data ss:Type="String><B>TODO: </B><Font>some text</Font></ss:Data></Cell>
« Last Edit: July 17, 2019, 12:26:38 am by wp »
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

totya

  • Hero Member
  • *****
  • Posts: 577
Re: [SOLVED via svn!] MS Excel created xml file support
« Reply #25 on: July 17, 2019, 06:37:52 am »
Hi master!

First time, your demo write executed without any error. See attached file.

The reason is the same isue for which you posted a question somewhere else: Using laz2_dom and laz2_xmlread, how can I retrieve the undecoded xml child nodes of that strange <ss:Data> node

I created topic for it... but no answer  O:-)

I process these files manaully as I said... with regex... the code under development... I hate regex, because all regex work differently (from same patternt), some buggy, some not, hopefully the trunk sorokin regexpr works with utt8 without any magic... (for ex. unicode mode).

Have a nice day!

totya

  • Hero Member
  • *****
  • Posts: 577
Re: [SOLVED via svn!] MS Excel created xml file support
« Reply #26 on: July 22, 2019, 08:03:09 pm »
Hi wp master!

I see in svn watcher, your working hardly for this filetype support.

Today I about finished my own "parser", I can read and write the all cell data with raw value. Besides I can write back the data to the file, and these recreated files same as the original (hash checked!). This is mean, I can simulate the original office xml line-breaking system too. I test it with about 150 file, and all hash is equal. This is exactly, what I want (raw value, and reproduce the same xml structure).

Anyway, thank you wp master for your many help and support!

totya

  • Hero Member
  • *****
  • Posts: 577
Re: [SOLVED via svn!] MS Excel created xml file support
« Reply #27 on: August 13, 2019, 10:04:41 pm »
Hi master!

Thanks again for your many job and advice!

Now I write to you, because when I read with my low-level app your test.xml file (created by excelxmlwrite with the latest fps svn), I got an error (from my app):

Quote
Invalid index value in Row node: 11 (before: 11)

This isn't a big error, but... I think my checking code is okay, and I don't want to modify it, because it's okay ;)

Your created xml code:

Quote
<Row ss:Index="11" ss:AutoFitHeight="1">
        <Cell>
          <Data ss:Type="String">Writing current date/time:</Data>
        </Cell>
      </Row>

The problem is, the ss:Index="11" is unnecessary, because this row index is 11 anyway. If I resave your xml file with the excel, the ss:Index="11" is certainly disappear.

Can you correct this small unnecessary row index "problem"?

wp

  • Hero Member
  • *****
  • Posts: 6485
Re: [SOLVED via svn!] MS Excel created xml file support
« Reply #28 on: August 13, 2019, 11:24:54 pm »
Quote
<Row ss:Index="11" ss:AutoFitHeight="1">
        <Cell>
          <Data ss:Type="String">Writing current date/time:</Data>
        </Cell>
      </Row>

The problem is, the ss:Index="11" is unnecessary, because this row index is 11 anyway. If I resave your xml file with the excel, the ss:Index="11" is certainly disappear.
Assuming that we are talking of the same file the 'ss:Index="11"' cannot be removed because there is an empty row before #11. Row indexes can only be skipped when they follow each other without gaps. Of course, I could add empty row nodes to keep the indexes in sequence, but Excel is not doing it, so why should fpspreadsheet do it?

When I re-save the file in Excel the ss:Index="11" stays in the file. So maybe your file is somehow different from mine? Can you zip the file and post it?
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

totya

  • Hero Member
  • *****
  • Posts: 577
Re: [SOLVED via svn!] MS Excel created xml file support
« Reply #29 on: August 14, 2019, 12:02:11 am »
Assuming that we are talking of the same file the 'ss:Index="11"' cannot be removed because there is an empty row before #11.

This isn't an really empty row, because this line
<Row ss:AutoFitHeight="1"/>
be in the xml. Look, I read about 150 real excel xml file, and this checking passed with all files.

Really empty row example from the original excel:
Quote
  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="5" x:FullColumns="1"
   x:FullRows="1">
   <Row>
    <Cell><Data ss:Type="String">a</Data></Cell>
   </Row>
   <Row ss:Index="5">
    <Cell><Data ss:Type="String">b</Data></Cell>
   </Row>
  </Table>

Do you see the differents?

When I re-save the file in Excel the ss:Index="11" stays in the file. So maybe your file is somehow different from mine? Can you zip the file and post it?

Okay. Resaved with Excel 2003.