fpspreadsheet large file

[wp]

I can't help much with Oracle to which I don't have access to. But I guess this is still another facet of that string conversion horror.

Did you try to open the Oracle database with one of the tools that certainly come with Oracle? Do you see the square as well? I cannot believe that a control-character has made its way into the original database.

What I learn for fpspreadsheet is that it is required to add some checks for faulty UTF8 strings to the xlsx writer because Excel seems to be quite fussy with them. Showing a "?" in place of the faulty characters will not help you getting the string conversion right, but at least you'll be able to load the file into Excel.

To the experts: How can I detect a faulty UTF8 character?

[EDIT] Found it: UTF8FixBroken()

[EDIT] No - the string #19'Standaard' is classified as being correct... What about ignoring all characters <32 (except for CR, LF, TAB)?

[wp]

r3611 no longer writes non-printable characters (except for #13, #10, #9) to xlsx, ods and wikitable files. Now you should be able to create an Excel-readable file from the Oracle database table (even if the presence of the character #19 is highly suspicious that the export has not been correct).

[Hansvb]

I tried to export an Oracle file to xlsx. A table with 8000 records took almost 10 minutes. (yesterday, with the previos svn it took only a few seconds). Now i am tying to export a result of 100.000+ records. Yesterday it took like 2 minutes. Now the export is already a hour bussy and still not ready. (this is the one i want to export to see if it's really working).

The solution which is made after my post yesterday for Oracle is not good for the performance. (When i export fom access it is as fast as yeserday so that is still good).

 

[wp]

Oh - what happened here? I see it in the speedtest which I did not run yesterday. Which revision was still fast?

[Hansvb]

I think it was 3603. My last update was 24-09-2014. (until  today)

The export is finally ready.  The file is smaller then yesterday.  My old Excel does not open it with the conversion component. I wil try it tomorrow in excel 2007.

[wp]

Recent changes with the merged cells and shared formulas have introduced a stupid bug: "for i := 0 to GetLastOccupiedColIndex" - I always forget whether the limits of a for loop are calculated only once at entry, or continuously during the loop... In this case, it seems that GetlastOccupiedColIndex is recalculated with every loop, and this costs a LOT!

After pre-calculating the the loop limits, speed is back. But there are some other strange things which I want to figure out before uploading a new version. Please be patient.

[wp]

Ok - should be fast again (r3615)... Speed test shows reasonable numbers, and units tests are all ok.

[Hansvb]

On Access it is fast. On Oracle it is verry slow.
The Excel file opens good in Excel 2007. In doesn't open in Excel 2003. I haven't looked wich record stops it from opening. If you want to know then ik can look after it this week.

For me this is good enough, i'm gonna use fpspreadsheet.

Thanks a lot!

[wp]

Code: [Select]

On Oracle it is verry slow.The Oracle database is probably in the internet, while the Access file is local. This can make a huge difference, in particular if many records are loaded. Run the following code which simply loops over all records and fields, but does not write anything to the spreadsheet. Measure the time. It should roughly be equal to the difference between the Oracle and Access versions.

Code: [Select]

var
  i, j: Integer;
  s: String;
begin
  DBGrid1.DataSource.DataSet.First;
  j := 0;
  while not DBGrid1.DataSource.DataSet.EOF do
  begin
    for i := 0 to DBGrid1.DataSource.DataSet.Fields.Count - 1 do
      s := DBGrid1.DataSource.DataSet.Fields[i].AsString;
    DBGrid1.DataSource.DataSet.Next;
    Inc(j);
  end;
end;

In doesn't open in Excel 2003. I haven't looked wich record stops it from opening. If you want to know then ik can look after it this week.

If you don't mind, yes, please look for the record, and upload here the data of that record, as well as the xlsx file which is not readable by Excel2003. I want to find out what is wrong here.