< sign

[krzynio]

Hello!

I use this code for exporting data from firebird db to xls worksheet (a part of code)

Code: [Select]

  MyDatabase.DisableControls;
  MyDatabase.First;
  j := 2;
  while not MyDatabase.EOF do begin
    for i:=0 to MyDatabase.Fields.Count-1 do begin
    if MyDatabase.Fields[i].DataType in [ftDateTime, ftDate, ftTime, ftTimeStamp] then
            // MyWorksheet.WriteDateTime(j, i, MyDatabase.Fields[i].AsDateTime)
           MyWorksheet.WriteUTF8Text(j, i, DateTimeToStr(MyDatabase.Fields[i].AsDateTime))
      else if MyDatabase.Fields[i].DataType in [ftInteger, ftSmallint, ftLargeint, ftAutoInc, ftWord, ftBoolean] then
       MyWorksheet.WriteNumber(j, i, MyDatabase.Fields[i].AsInteger)
      else if MyDatabase.Fields[i].DataType in [ftFloat, ftCurrency] then
           MyWorksheet.WriteNumber(j, i, RoundN(MyDatabase.Fields[i].AsFloat, 2))
      // else if MyDatabase.Fields[i].DataType <> ftBlob then
      else if MyDatabase.Fields[i].DataType in [ftString] then begin
        // s := RemoveBracket( MyDatabase.Fields[i].AsString );
        s := MyDatabase.Fields[i].AsString;
        MyWorksheet.WriteUTF8Text(j, i, s);
           {
           MyWorksheet.WriteUTF8Text(j, i, MyDatabase.Fields[i].AsString);
           }
end;
    end;
    MyDatabase.Next;
    Inc(j);
  end;

I noticed that data is exported until it didn't find <brak> value.
When this value is found export stops. Next fields are not exported.
When I process the string value by a procedure RemoveBracket.

Code: [Select]

s := RemoveBracket( MyDatabase.Fields[i].AsString );

Which simply replaces <brak> with empty string '' all data is exported corecty.

Is it a bug or < > are restricted signs for spreadsheet in text field?
Is there any workaround to fix it?

[BigChimp]

No idea but are you aware of the dataset export functionality delivered with recent fpspreadsheet builds?
http://wiki.lazarus.freepascal.org/FPSpreadsheet#Dataset_export

Looking at your code, it seems quite similar to the code used in TFPSExport though... so it may contain the same bug.


What version of fpspreadsheet are you using?

[krzynio]

Thanks for quick answer.
I use 1.2 but I noticed the same behaviour in previous version also (1.1 or 1.0).
I am going to test the proc. you sent me.
I tried to add ' before the text but without success also.

[wp]

I just checked if it is possible to read xls and csv files which contain "<" and ">" characters. No problem.

I am not experienced with Firebird. Could it be that these characters have a special meaning for the DB?

Instead of writing the strings to an xls file write them to other formats suppored by fpspreadsheet. Or bypass fpspreadsheet at all, and write them to a text file. Do you see the same issue?

What is the full string which causes the problem? We had issues with character encoding, and I am not sure if they are completely solved.

[BigChimp]

I am not experienced with Firebird. Could it be that these characters have a special meaning for the DB?

No, they don't. In SQL queries, it is an operator like in Pascal but in fields it has no special significance.

[BigChimp]

Just added test for string data containing > in dbexport test in revision 3729... and it passes/works ok...

[krzynio]

What is the full string which causes the problem? We had issues with character encoding, and I am not sure if they are completely solved.

Full text is: <brak>

Could it be that these characters have a special meaning for the DB

No, but <> are used in formulas of spreadsheets but formulas usually starts from = sign but not in my case.

I though about code page therefore I wrote a simple procedure and I exported do Excel only chars from #20 to #128 but with the same result.
This didn't work also and I started to reduce symbols and when I had exported only letters and digits worksheet opened.
I tested this behaviour with application compiled on Windows with Windows-1250 cp and on Debian with UTF-8. Data in db is in UTF-8 format.
In both cases export stopped at field containing <brak> value.
Removing <> from <brak> makes export working. National characters exports fine.

[wp]

I looked at your code again and see now that the bug happens when the spreadsheet is filled, it has nothing to do with the file format.

To check your argument that fpspreadsheet fails when a string "<brak>" is written to it please replace the line "s := MyDatabase.Fields.AsString" in your code by "s := '<brak>'". Is it still failing? Is it failing at the same occurence as before or earlier - now every string will be written as "<brak>", and if the <> are the root cause of the trouble the crash should happen at the very first string written. If, on the other hand, the crash is at the original position then you could suffer from some kind of memory corruption or similar that was initiated before.

What's the size of the DB? How many records have been written to the spreadsheet before it fails? How many fields per record? FPSpreadsheet is rather memory-consumptive in direct mode. You might try virtual mode if there's a risk of running out of memory - see the wiki for explanation and examples.

I exported do Excel only chars from #20 to #128 but with the same result. This didn't work also

Why are you beginning at character #20? The printable characters begin at #32. Characters less than #32 should be handled by fpspreadsheed such that the workbook is readable. Please be more specific in what you did, in particular which file format you used. Or post that test procedure here.

[krzynio]

I did like you suggested

Code: [Select]

else if MyDatabase.Fields[i].DataType in [ftString] then begin
        //s := RemoveBracket( MyDatabase.Fields[i].AsString );
        //s := MyDatabase.Fields[i].AsString;
        s := '<brak>';
        MyWorksheet.WriteUTF8Text(j, i, s);
end;
and the result is the same. cells in these columns are empty.

The worksheet has 26 columns and about 25'000 rows. All records are written, no error is displayed but cells where should be "<brak>" are simple empty. Sometimes I need to export about 100'000 rows. I use sfOOXML as output format.
Replacing s := 'brak' with i.e. s := 'abcąćńłóꯏŁÓŃĘĆ etc.' works fine.
 

[krzynio]

I noticed that s := 'brak>' and 'xxx>' also works fine. It doesn't work only when string contains < char i.e. s := '<' or s := 'x<'

[wp]

You mention ooxml and have version 1.2... There was a bug with incorrectly converted special xml characters, the "<" is one of them...

I usually work with trunk and don't see these old bugs any more. Therefore, I switched to version 1.2 and now can confirm your bug with the ooxlmwritedemo: if I modify one of the written demo strings to contain a "<" Excel refuses to load the created file due to an xml error.

The bug is fixed in fpspreadsheet trunk. If you don't want to switch to trunk wait for one or two weeks until we publish a new release. Or modify your xlsxooxml.pas in the following way:

• Goto procedure "TsSpreadOOXMLWriter.WriteLabel"
• A few lines down the page there is an assignment "FSharedStrings := FSharedStrings + ..."
• Find the "Resultingvalue" two lines below
• Use it as an argument of "UTF8TextToXMLText"

In total, The FSharedStrings assignment should be (modification in red/bold)

  FSharedStrings := FSharedStrings +
          '  <si>' + LineEnding +
   Format('    <t>%s</t>', [UTF8TextToXMLText(ResultingValue)]) + LineEnding + 
          '  </si>' + LineEnding;

This fixed the bug in my test with version 1.2.

[krzynio]

Thank you now works fine.
And thank you for info about virtual mode. I'll try it soon.

[wp]

Of course - I forgot to mention - you need trunk to use the virtual mode.

[BigChimp]

Ehrm... I wonder/doubt whether virtual mode will work for iterating datasets for export... (Same problem in fpspreadsheet's dbexport code... didn't we discuss this, wp?)..

[wp]

Yes export is working in virtual mode if the OnWrite event handler attached to the workbook advances the active record. But in your dbexport component the active record is advanced by the export component, therefore virtual mode is not working there.